Statistics collection in oracle 11gR2

Oracle has addressed 2 major issues in statistics gathering. 1) Automatic statistics collection 2) Speed and accuracy by changing the sample data 1) Automatic statistics collection: In 11gR2 statistics collection is automatic by default.The statistics will be collected by using an auto job, if the statistics found to be stale for an object or if any DDL command used against the object. Please see the below for the job details. select client_name,status,consumer_group,attributes from DBA_AUTOTASK_CLIENT CLIENT_NAME STATUS CONSUMER_GROUP ------------ --------- -------------- auto optimzer stat collection ENABLED ORA$AUTOTASK_STATS_GROUP The stale percent is determined based on the number of DML operations occured on the table ie., the percentage of number of rows updated or inserted or deleted on the table. By default the threshold limit for the stale percentage is 10%. You can check this values by using the below query. select dbms_stats.GET_PREFS ('STALE_PERCENT','OWNER','TABLENAME') from dual. To check the objects considered for auto stats collection we can check the parameter AUTOSTATS_TARGET.The value of this parameter controls the objects considered for stats collection. The values: 'ALL' - Statistics collected for all objects in system 'ORACLE' - Statistics collected for all Oracle owned objects 'AUTO' - Oracle decides on which objects to collect statistics We can check this by using the below query select dbms_stats.GET_PREFS ('AUTOSTATS_TARGET') from dual; DBMS_STATS.GET_PREFS('AUTOSTATS_TARGET') ------------------------------------------- AUTO To check the percentage of rows modified or inserted or deleted on the tables you can use the below query select * from DBA_TAB_MODIFICATIONS where table_owner='SCOTT' If you need to check the information about stats collection on your tables, you can use the below query select * from DBA_TAB_STATS_HISTORY where owner='SCOTT' select client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB where client_name='%stats%'; To enable or disable the Auto stats job please use the below procs. To enable or disable the Auto stats job please use the below procs. ENABLE: BEGIN DBMS_AUTO_TASK_ADMIN.ENABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; / DISABLE: BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; / SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed FROM dba_autotask_client_history WHERE client_name like '%stats%'; SELECT * FROM dba_autotask_client_history WHERE client_name like '%stats%'; Select client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB where client_name='auto optimizer stats collection'; 2) Speed and accuracy by changing the sample data: Looking back to the previous major release (10.2.0) the methodology for statistics gathering relied basically on the sample size; the larger the sample size the better the statistics at the cost of increased run time to gather those statistics. Oracle also introduced the auto sample size default, which determined the sample size to use for each table. Using the default settings did produce statistics in a shorter length of time, but did so at the expense of inaccurate histograms, plainly wrong histograms, and inaccurate density values. Partitioned tables were the most often affected when sample sizes smaller than 90% were used; the auto sample size Oracle computed often hit the 30% mark leaving histogram values woefully in need of adjustment. Even though Oracle would adjust the sampled values up at the table level (to more accurately reflect the actual number of rows) the histogram values remained at the actual sampled size throwing density/selectivity calculations off by a considerable margin and adversely affecting performance based on cost calculations. Oracle 11.2.0 has corrected these issues with a better methodology that not only increases the speed of statistics collection but also provides greater accuracy of the results. An improved sampling algorithm along with a reduction of the full table scans executed has resulted in more accurate statistics in a shorter amount of time, and better statistics definitely mean better execution plans. That isn't the only improvement in 11.2.0 statistics as the histograms generated have accurate numbers, better reflecting the state of the table data distribution. Partitioned tables no longer cause Oracle to generate spurious histograms such as the frequency histograms on single-valued, non-null columns making CBO calculations more accurate. Better statistics mean better plans and less of a headache for the users and the DBAs. Notice also that the auto sample size in 11.2.0 generates statistics percentages very close to 100, indicating that the new auto sampling algorithm will run faster yet produce results rivaling a 100% sample size and do that in approximately 1/10th of the time. The less time a DBA spends on statistics is more time to address other, more pressing, issues. That's a good thing no matter how you look at it.