Improve Gather Stats in Oracle E-Business Suite

One important aspect of a healthy Oracle database is database statistics. It’s the main data on which CBO feeds to generate good execution plans for SQLs. Implementing a periodic and consistent gather statistics procedure in Oracle E-Business Suite is every Applications DBA’s duty. But often I see that they are not set up properly to take advantage of the latest database features.  In the last couple of years, we have seen a lot of Oracle E-Business Suite customers upgrade their database from version 10gR2 to 11gR2, but often times they don’t review their procedure and take advantage of new 11gR2 features around gathering database statistics. This is like upgrading to a new Audi with blind spot assist & rear view camera, but driving it with them disabled.

One of the improved features of 11gR2 is the new DBMS_STATS.AUTO_SAMPLE_SIZE, which yields a significant reduction in the time it takes to collect highly accurate statistics.

Here is a quick demo. Here are the actual number of rows in the table


SQL> select count(*) from PAY_RUN_RESULT_VALUES;

COUNT(*)
———-
33222845

Gather Stats with 40% estimate, which most Oracle support analysts recommend for better stats. It took about 4 minutes to complete, with close to accurate stats, but not perfect.


SQL> exec fnd_stats.GATHER_TABLE_STATS( OWNNAME => 'HR', TABNAME=> 'PAY_RUN_RESULT_VALUES', PERCENT => 40, DEGREE => 4 );

PL/SQL procedure successfully completed.

Elapsed: 00:03:59.09

SQL> select num_rows from dba_tables where table_name = ‘PAY_RUN_RESULT_VALUES’;

NUM_ROWS
———-
33222068

Now Gather Stats with Auto Sampling enabled. Note that for the Gather Stats program to use 11g Auto Sampling feature, we either need to give the Estimate percent as Zero or leave it blank. This run completed in 1 min 18 seconds, about 30% of the time it took to run with an estimate percent of 40% and the estimated number of rows is an exact match to the actual number of rows in the table.


SQL> exec fnd_stats.GATHER_TABLE_STATS( OWNNAME => 'HR', TABNAME=> 'PAY_RUN_RESULT_VALUES', PERCENT => 0, DEGREE => 4 );

PL/SQL procedure successfully completed.

Elapsed: 00:01:18.86
SQL> select num_rows from dba_tables where table_name = ‘PAY_RUN_RESULT_VALUES’;

NUM_ROWS
———-
33222845

So why wait to take advantage of 11gR2 improved Auto sampling feature. Just configure your Gather Schema Statistics program to use the parameters below and chug along the freeway in your new Audi !!!