Analyze Oracle 10g

20 pts.
Tags:
Oracle 10g
Oracle 10g Database
I've been a little out of the loop on Oracle since Oracle 7.

When we upgraded to 10g, we were led to believe that oracle would automatically handle regular analyzing of the database.

This past week we discovered that not all tables are being analyzed regularly.

What are we missing?  Is there a parameter that needs to be adjusted?

How do we fix this issue?

Thanks. 



Software/Hardware used:
Dell PowerEdge 6850 running Red Hat Enterprise Linux v4 with Oracle 10.2.0.3.0

Answer Wiki

Thanks. We'll let you know when a new response is added.

Although the analyze command still exists, it is no longer relevant to the Cost Based Optimizer (CBO).
The collection of statistics to be used by the CBO in Oracle 10g is now done automatically unless changed.
An internal procedure called GATHER_DATABASE_STATS_JOB_PROC is run by a program named GATHER_STATS_PROG.

I created a shell script to validate this job ran and to verify times and groups associated with it.
—————————————————————————————————————————–
SQL> @ck_auto_stats.sql
SQL> set linesize 80
SQL> col job_name format a17
SQL> col program_name format a18
SQL> col schedule_name format a25
SQL> col job_class format a20
SQL> col job_type format a8
SQL> col state format a9
SQL> col PROGRAM_ACTION format a41
SQL> show parameter instance_name

NAME TYPE VALUE
———————————— ———– ——————————
instance_name string MYDATABASE

SQL> select job_name,enabled, state, program_name, schedule_name, job_class
2 from dba_scheduler_jobs
3 where job_name = ‘GATHER_STATS_JOB’;

JOB_NAME ENABL STATE PROGRAM_NAME
—————– —– ——— ——— SCHEDULE_NAME JOB_CLASS
————————- ——————–
GATHER_STATS_JOB TRUE SCHEDULED GATHER_STATS_PROG MAINTENANCE_WINDOW_GROUP AUTO_TASKS_JOB_CLASS

SQL> select PROGRAM_ACTION, ENABLED
2 from dba_scheduler_programs
3 where PROGRAM_NAME = ‘GATHER_STATS_PROG’;

PROGRAM_ACTION ENABL
—————————————– —–
dbms_stats.gather_database_stats_job_proc TRUE

SQL> select *
2 from DBA_SCHEDULER_WINGROUP_MEMBERS
3 where WINDOW_GROUP_NAME = ‘MAINTENANCE_WINDOW_GROUP’;

WINDOW_GROUP_NAME WINDOW_NAME
—————————— ——————————
MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP WEEKEND_WINDOW

SQL> select window_name, repeat_interval, duration
2 from dba_scheduler_windows
3 where window_name in (‘WEEKNIGHT_WINDOW’, ‘WEEKEND_WINDOW’);

WINDOW_NAME
——————————
REPEAT_INTERVAL
——————————————————————————–
DURATION
—————————————————————————
WEEKNIGHT_WINDOW
freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0
+000 08:00:00

WEEKEND_WINDOW
freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0
+002 00:00:00

—————————————————————————————————————————–
The REPEAT_INTERVAL shows the job will run at 10:00 PM Mon – Fri. (byhour=22).
Since we bring the databases down just after midnight, I wasn’t sure if this was running to completion, therefore I changed the time to start at 6:00 PM Mon – Fri. I used this script to make this change:
—————————————————————————————————————————–
begin
dbms_scheduler.set_attribute(name=>’WEEKNIGHT_WINDOW’, attribute=>’REPEAT_INTERVAL’,
value=>’freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=18;byminute=0; bysecond=0′
);
end;
/
—————————————————————————————————————————–
The DURATION shows it to have an 8 hour window to run. Since I am starting at 6 PM and bringing the database down approx 6 hours later, I changed the DURATION to be 6 hours with this script:
—————————————————————————————————————————–
begin
dbms_scheduler.set_attribute(name=>’WEEKNIGHT_WINDOW’, attribute=>’duration’,
value=> ‘+000 06:00:00′
);
end;
/
—————————————————————————————————————————–
I later found out that the job does not run that long. I ran the following and found the job to run approx 38 seconds.
—————————————————————————————————————————–
SQL> col start_time format a35
SQL> col operation format a27
SQL> col end_time format a35
SQL> select operation, start_time, end_time
2 from dba_optstat_operations
3* order by 2

OPERATION START_TIME END_TIME
————————— ———————————– ———————————–
gather_database_stats(auto) 10-SEP-09 06.00.03.245920 PM -04:00 10-SEP-09 06.00.41.921488 PM -04:00
—————————————————————————————————————————–
I was confused when I ran the following and only saw a few tables had been updated. Then I found that this information is cleaned out every 30 days. The MYDATABASE database is not active and is almost 30 days old.
—————————————————————————————————————————–
SQL> col owner format a8
SQL> col table_name format a8
SQL> col stats_update_time format a36
SQL> select owner, table_name, stats_update_time
2 from dba_tab_stats_history
3 where owner not in (‘SYS’,’SYSMAN’,’WKSYS’)
4* order by 3;

OWNER TABLE_NA STATS_UPDATE_TIME
———– —————- ————————————————-
G1 TAB1 12-AUG-09 10.00.05.863135 PM -04:00
G1 TAB2 12-AUG-09 10.00.06.409508 PM -04:00
G1 TAB3 12-AUG-09 10.00.09.497606 PM -04:00
C1 TAB4 31-AUG-09 10.00.02.999454 PM -04:00
C1 TAB5 31-AUG-09 10.00.03.088383 PM -04:00
—————————————————————————————————————————–
By all my investigation on this process, I believe that the statistics gathering is happening successfully.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following