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/
Learn. Share. Repeat. 