Oracle optimizer

Many times we need to gather statistics to let optimizer get best plan for accessing the rows. With earlier versions that was done by ANALYAZE command but for now Oracle provide the DBMS_STATS pl/sql package as a preferred way to gather statistics.

Also, from Oracle 10G Oracle does automatic job for gathering statistics for the tables needed. But which process does this job, how to stop, start this process and when oracle knows which table statistics need to be gathered.

SQL> select banner from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for 32-bit Windows: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> select client_name,status from Dba_Autotask_Client;

CLIENT_NAME                                                      STATUS

---------------------------------------------------------------- --------

auto optimizer stats collection                                  ENABLED

auto space advisor                                               ENABLED

sql tuning advisor                                               ENABLED

SQL>

So, above are the automatic jobs and  ‘auto optimizer stats collection’  is the one which is responsible for gathering statistics on the needed tables.

Statistics on a table are considered to be stale when more than 10% of the rows are changed (DML’s). Oracle does this monitoring from the *_tab_modification view. Before 11g, this stale percent was fixed at 10% but now we can even change this percent.

SQL> select dbms_stats.get_prefs ('ESTIMATE_PERCENT','JAY', 'TEST') ESTIMATE_PERCENT from dual;

ESTIMATE_PERCENT

--------------------------------------------------------------------------------

DBMS_STATS.AUTO_SAMPLE_SIZE

So it is default value i.e. 10%.  Now changing this to 15%.

SQL> exec DBMS_STATS.SET_TABLE_PREFS('JAY', 'EMP','ESTIMATE_PERCENT','15');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs ('ESTIMATE_PERCENT','JAY', 'EMP') ESTIMATE_PERCENT from dual;

ESTIMATE_PERCENT

--------------------------------------------------------------------------------

15

So we have changed the stale percent for EMP table under JAY schema. Now lets work to stop this automated job. This can be done by dbms_auto_tas_admin procedure. 

SQL> begin

  2  dbms_auto_task_admin.disable (

  3  client_name => 'auto optimizer stats collection',

  4  operation => NULL,

  5  window_name => NULL);

  6  end;

  7  /

PL/SQL procedure successfully completed.

Lets check,

SQL> select client_name,status from Dba_Autotask_Client;

CLIENT_NAME                                                      STATUS

---------------------------------------------------------------- --------

auto optimizer stats collection                                  DISABLED

auto space advisor                                               ENABLED

sql tuning advisor                                               ENABLED

Now my auto optimizer job is stopped. Let's again start using same package. 

SQL> begin

  2  dbms_auto_task_admin.enable (

  3  client_name => 'auto optimizer stats collection',

  4  operation => NULL,

  5  window_name => NULL);

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> select client_name,status from Dba_Autotask_Client;

CLIENT_NAME                                                      STATUS

---------------------------------------------------------------- --------

auto optimizer stats collection                                  ENABLED

auto space advisor                                               ENABLED

sql tuning advisor                                               ENABLED

SQL>

Thanks.