?
Solved

GATHER_STATS_JOB: updating statistics on a daily basis

Posted on 2011-10-06
19
Medium Priority
?
1,816 Views
Last Modified: 2013-12-18
Hi,

This job is said to be scheduled on my oracle instance, but it never starts. I can compute statistics for all my database by hand, no pb. But I'd like to run it automatically, choosing the time.
I'm not very familiar with this stored proc. The last time I had to deal with oracle statistics, many years ago, it was through a SAP instance, running Oracle 8 then 9. I had to schedule it from SAP and that was enough.

I'm running Oracle x64 10.2.0.2.0 and what I can say is that it seems to be scheduled, but no "next run date" :
select owner,program_name,schedule_name,schedule_type,start_date,repeat_interval,job_class,enabled,state,next_run_date from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';

gives :

SYS,GATHER_STATS_JOB,MAINTENANCE_WINDOW_GROUP,WINDOW_GROUP,<nothing>,<nothing>,AUTO_TASKS_JOB_CLASS,TRUE,SHCEDULED,<nothing>

Thank you


How would I schedule the update of some tables only, on a daily basis?
0
Comment
Question by:mchkorg
  • 10
  • 6
  • 3
19 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 36923954
first - I recommend upgrading ,  if not to 11g  at least 10.2.0.4 or 10.2.0.5 if possible.


Your job has not started (start_date is null) so it has no next.
Next is calculated when a run completes. So, if it never starts, it never has a next.
Try running the job itself once to "kick start" it


begin
   dbms_scheduler.run_job('GATHER_STATS_JOB');
end;
0
 
LVL 7

Author Comment

by:mchkorg
ID: 36923983
I'll try this a bit later (don't want to slow it all down right now)
I understand this kick start idea, but I can see 1486 RUN_COUNT (and 5 FAILURE_COUNT). I think it's been kickstarted...


About the upgrade, it'll be when the dev team is OK............ 11g scheduled "one day"....... :(
even if 10.2.0.2 to .05 is a really minor upgrade. I know it.


I'll try to run this by the end of the afternoon, I'll you. But I'm not here tomorrow, so it'll be on monday I guess
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36924009
also check what your job_queue_processes count is.  If it's 0, no jobs will run.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 7

Author Comment

by:mchkorg
ID: 36924032
I have 10, according to select value from v$parameter where name ='job_queue_processes'

By the way, how do you choose the start time?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36924105
if you use RUN_JOB procedure as shown above,  it will run immediately.

otherwise it's set when you create the job.

however,  in your job's case,  it's running on a schedule MAINTENANCE_WINDOW_GROUP
which should be defining when it runs
0
 
LVL 7

Author Comment

by:mchkorg
ID: 36924183
OK
I guess my time window is OK:
SELECT * FROM   dba_scheduler_window_groups;
=> MAINTENANCE_WINDOW_GROUP;TRUE;2;06/10/11 22:00:00.300000 EUROPE/PARIS

At that time, my DB is still running (there's an offline backup later)

So why doesn't it start?
0
 
LVL 7

Author Comment

by:mchkorg
ID: 36940653
Hi, maybe an hint. Despiste all I can see in "dba_scheduler_jobs" , the job seems to be missing:

begin
   dbms_scheduler.run_job('GATHER_STATS_JOB');
end;

=> gives:

Error at line 1
ORA-27475: "CNAS.GATHER_STATS_JOB" doit être un job
ORA-06512: à "SYS.DBMS_ISCHED", ligne 150
ORA-06512: à "SYS.DBMS_SCHEDULER", ligne 441
ORA-06512: à ligne 2

Any idea?
0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36956585
why "CNAS.GATHER_STATS_JOB" and not "SYS.GATHER_STATS_JOB"
0
 
LVL 7

Author Comment

by:mchkorg
ID: 36960269
CNAS is my schema/user, also company name

Why do I have this answer when I try to tun 'GATHER...', not 'CNAS.GATHER...'?

I suppose someone here might have modified something, but everyone's innocent here.
0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36961593
Only SYS can do things in the SYS schema, so try again using the SYS account and run

begin
   dbms_scheduler.run_job('GATHER_STATS_JOB');
end;

0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36961605
Rules and Limits
The following rules apply when using the DBMS_SCHEDULER package:
    •Only SYS can do anything in SYS schema.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sched.htm#i1013037
0
 
LVL 7

Author Comment

by:mchkorg
ID: 36961609
oops, sorry I didn't realize that

Now it's running, we'll see tomorrow if this launch kickstarts something, as we supposed earlier
Thank you
0
 
LVL 7

Author Comment

by:mchkorg
ID: 36986282
Hi,
I still have an empty "NEXT_RUN_DATE" for this job in dba_scheduler_jobs after this successful manual start.

Any idea to schedule it periodically?

Thank you
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36986368
your next_run_date isn't fixed to the job

as noted above,  your schedule is defined by MAINTENANCE_WINDOW_GROUP
0
 
LVL 7

Author Comment

by:mchkorg
ID: 36986439
OK, then how do I check this "maintenance window"? I just know it's enabled ; why doesn't it make my GATHER... job start?

SQL> SELECT window_group_name, enabled, number_of_windowS
  2  FROM   dba_scheduler_window_groups;

WINDOW_GROUP_NAME              ENABL NUMBER_OF_WINDOWS
------------------------------ ----- -----------------
MAINTENANCE_WINDOW_GROUP       TRUE                  2



I'm not familiar with this job scheduling stuff with Oracle, sorry
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36986473
what is the next_start_date in dba_scheduler_window_groups?
0
 
LVL 7

Author Comment

by:mchkorg
ID: 36986862
it's set to this evening :

SELECT window_group_name, enabled, number_of_windowS, next_start_date
FROM   dba_scheduler_window_groups;

gives:

MAINTENANCE_WINDOW_GROUP
TRUE
2
18/10/11 22:00:00.600000 EUROPE/PARIS

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 36986958
then your job should run sometime tonight after 10pm

when you say it's not running, what are you using to determine that?

Last analyzed_date?  If so, that's not reliable.
By default the statistics collection looks at table changes to determine if it should gather new statistics.
So, it might be running every night but simply not finding anything to do.

What is last_start_date in dba_scheduler_jobs  ?
0
 
LVL 7

Author Comment

by:mchkorg
ID: 36990817
You're right, my last_start_date is yesterday, some seconds after 10pm
It's strange I didn't see it at the beginning.

I understand the "Last analyzed_date" is not reliable, but, before I ran some "analyze table... compute statistics" just in case, I think the last analyzed date was some months old, same date, for every table. Probably another manual "analyze table" request.
I can admit sometimes (maybe most of the time) the system won't compute anything, but not for every table, including some temporary tables deleted by batch processes.

I guess I just can trust the last_start_date and say statistics are up-to-date. Is that it?
Is there a way to get some details of the analysis? My boss told me our application ran way faster after he ran some "analyze table compute statistics" requests. Maybe. So I'd like to read some activity log of the GATHER... job, if possible. I can see a LOG_LEVEL=RUNS parameter. Can I raise it somehow? (and where to check)

Anyway thank you, after this I guess I'll close this question
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question