DBMS_STATS.GATHER_SCHEMA_STATS Failing in 10.2.0.3

After a migration of 9.2.0.8 database to 10.2.0.3 major performance problem(slowness) was observed.When the database was on 9i, stats were gathered weekly using dbms_stats.gather_schema_stats package for the application specific schemas.

exec dbms_stats.gather_schema_stats(ownname =>'ABC',cascade => true,degree =>3);

The above was scheduled in the crontab to be run every saturday.After the database was migrated successfully, the crontab job was disabled, as from 10g automatic stats gathering job is enabled.

One of the major difference between stats default stats gathering job in 9i and above versions, is the METHOD_OPT parameter value.In 9i ,it defaults to ‘FOR ALL COLUMNS SIZE 1′ whereas in 10g it is ‘FOR ALL COLUMN SIZE AUTO’.

‘FOR ALL COLUMNS SIZE AUTO’ means that Oracle will automatically decide for us which columns need histograms and which columns don’t based on what it considers to be the distribution of values within a column and based on the “workload” associated with the table.

On analyzing the performance issue, it was decided to :-

1. Disable the automatic stats gathering job.
2. Schedule application schema stats gathering with METHOD_OPT set to ‘FOR ALL COLUMNS SIZE 1′.

To disable the automatic stats gathering job

EXEC DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

New scheduler job was created for schema stats gathering and scheduled for weekly run (on every sunday).

On monday morning when i checked the last_analyzed date for the application schema’s table, i could see some older dates (than sunday) which meant something went wrong.So comes the time to analyze the issue :-

12:43:56 SQL> @scheduler
Enter value for job_name: stats
old   1: select OWNER,JOB_NAME,to_char(last_start_date,'DD-MM-YY HH24:MI:SS') Last_date_time, to_char(next_run_date,'DD-MM-YY HH24:MI:SS') Next_Date_Time, JOB_ACTION from DBA_SCHEDULER_JOBS where job_name like UPPER('%&job_name%')
new   1: select OWNER,JOB_NAME,to_char(last_start_date,'DD-MM-YY HH24:MI:SS') Last_date_time, to_char(next_run_date,'DD-MM-YY HH24:MI:SS') Next_Date_Time, JOB_ACTION from DBA_SCHEDULER_JOBS where job_name like UPPER('%stats%')

OWNER                          JOB_NAME                       LAST_DATE_TIME       NEXT_DATE_TIME       JOB_ACTION                                                                                      
------------------------------ ------------------------------ -------------------- -------------------- --------------------------------------------------------------------------------                
SYS                            GATHER_STATS_JOB               20-11-07 22:01:06                                                                                                                         
SYS                            ABC_STATS_REFRESH              15-08-10 17:30:00    22-08-10 17:30:00    BEGIN  DBMS_STATS.gather_schema_stats (ownname => 'ABC', cascade =>true,method_opt=>'for all columns size 1',degree=>3);END;

Lets check the job log

12:44:28 SQL>  SELECT
12:44:29   2             log_id
12:44:29   3            ,job_name
12:44:29   4            ,job_subname
12:44:29   5            ,operation
12:44:29   6            ,status
12:44:29   7        FROM dba_scheduler_job_log where JOB_NAME like 'ABC_%';

    LOG_ID JOB_NAME             JOB_SUBNAME                                                     OPERATION                      STATUS                                                                 
---------- -------------------- --------------------------------------------------------------- ------------------------------ ------------------------------                                         

    25319   ABC_STATS_REFRESH                                                                    RUN                            FAILED                                                                 

As the above showed “FAILED” , it was the time to find out the reason for it.And the best things of the scheduler is, one can check the job run details to know the reasons for the failure.The “DBA_SCHEDULER_JOB_RUN_DETAILS” view has a ERROR# column which gives the ORA Error number.

12:45:44 SQL> select LOG_DATE,OWNER,JOB_NAME,STATUS,ERROR#,ACTUAL_START_DATE,RUN_DURATION from DBA_SCHEDULER_JOB_RUN_DETAILS where JOB_NAME like 'ABC_%';

LOG_DATE                  OWNER                          JOB_NAME                                                          STATUS                             ERROR# ACTUAL_START_DATE                  
------------------------- ------------------------------ ----------------------------------------------------------------- ------------------------------ ---------- -------------------------          
RUN_DURATION                                                                                                                                                                                            
---------------------------------------------------------------------------                                                                                                                             
15-AUG-10 08.35.33.832636 SYS                              ABC_STATS_REFRESH                                               FAILED                               1476 15-AUG-10 05.30.00.375357          
 AM -05:00                                                                                                                                                            PM +05:30                         
+000 01:35:33                                                                                   

The reason for the failure of the job was ORA-1476.Little search on the metalink showed it as BUG on 10.2.0.3.The Doc Id — 6319761.8 confirms that ORA-1476 from DBMS_STATS.GATHER_SCHEMA_STATS is Bug 6319761.Doc Id 464440.1 , provides a workaround that i need to test.

As gather_schema_stats was failing, we consulted application team, and made a gather_table_stats script for all the majorly accessed tables in the application schema, which is working fine.

References :-http://richardfoote.wordpress.com/2008/01/04/dbms_stats-method_opt-default-behaviour-changed-in-10g-be-careful/

About these ads
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 463 other followers

%d bloggers like this: