asked on

GATHER_STATS_JOB: updating statistics on a daily basis

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?

Sean Stuber

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;

mchkorg

ASKER

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

Sean Stuber

also check what your job_queue_processes count is. If it's 0, no jobs will run.

mchkorg

ASKER

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?

Sean Stuber

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

mchkorg

ASKER

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?

mchkorg

ASKER

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?

Jacobfw

why "CNAS.GATHER_STATS_JOB" and not "SYS.GATHER_STATS_JOB"

mchkorg

ASKER

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.

Jacobfw

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;

Jacobfw

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

mchkorg

ASKER

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

mchkorg

ASKER

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

Sean Stuber

your next_run_date isn't fixed to the job

as noted above, your schedule is defined by MAINTENANCE_WINDOW_GROUP

mchkorg

ASKER

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

Sean Stuber

what is the next_start_date in dba_scheduler_window_groups?

mchkorg

ASKER

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

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mchkorg

ASKER

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