dbms_stats.gather_system_stats and SQL optimizer behavior

The note below shows how the output from dbms_stats.gather_system_stats can change the cost-based optimizers choice of execution plans.  Oracle MOSC has detailed script listings in Note 149560.1 “Collect and Display System Statistics (CPU and IO) for CBO usage”:

/* e.g. activate the DAY statistics each day at 7:00 am */

DECLARE

   I NUMBER;

BEGIN

   DBMS_JOB.SUBMIT (I, 'DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => ''mystats'', s

tatown => ''SYSTEM'', statid => ''DAY'');', trunc(sysdate) + 1 + 7/24, 'sysdate

+ 1');

END;

/ 

/* e.g. activate the NIGHT statistics each day at 9:00 pm */

DECLARE

   I NUMBER;

BEGIN

   DBMS_JOB.SUBMIT (I, 'DBMS_STATS.IMPORT_SYSTEM_STATS(stattab => ''mystats'', s

tatown => ''SYSTEM'', statid => ''NIGHT'');', trunc(sysdate) + 1 + 21/24, 'sysdate

+ 1');

END;

/ 

*** ********************************************************

*** Initialize the OLTP System Statistics for the CBO

*** ********************************************************

1. Delete any existing system statistics from dictionary:

   SQL> execute DBMS_STATS.DELETE_SYSTEM_STATS;

   PL/SQL procedure successfully completed.

2. Transfer the OLTP statistics from OLTP_STATS table to the dictionary tables:

   SQL> execute DBMS_STATS.IMPORT_SYSTEM_STATS(-  

   >         stattab => 'OLTP_stats', statid => 'OLTP', statown => 'SYS');

   PL/SQL procedure successfully completed.

3. All system statistics are now visible in the data dictionary table:

   SQL> select * from sys.aux_stats$;

   SQL> select * from aux_stats$;

   SNAME                PNAME                   PVAL1 PVAL2

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

   SYSSTATS_INFO        STATUS                        COMPLETED

   SYSSTATS_INFO        DSTART                        08-09-2001 16:40

   SYSSTATS_INFO        DSTOP                         08-09-2001 16:42

   SYSSTATS_INFO        FLAGS                       0

   SYSSTATS_MAIN        SREADTIM                7.581

   SYSSTATS_MAIN        MREADTIM               56.842

   SYSSTATS_MAIN        CPUSPEED                  117

   SYSSTATS_MAIN        MBRC                        9

   where 

   => sreadtim : wait time to read single block, in milliseconds

   => mreadtim : wait time to read a multiblock, in milliseconds

   => cpuspeed : cycles per second, in millions 

*** ********************************************************

*** CPU_COST and IO_COST in PLAN_TABLE table

*** ********************************************************

   SQL> explain plan for select * from oltp.test where c='AAAHxGAABAAAJS1AEZ';

   Explained.

   SQL> select operation, options, object_name, cpu_cost, io_cost

     2  from plan_table;

   OPERATION          OPTIONS              OBJECT_NAME    CPU_COST    IO_COST

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

   SELECT STATEMENT                                          10500          1

   INDEX              UNIQUE SCAN          SYS_C002218       10500          1

   SQL> truncate table plan_table;

   SQL> explain plan for select * from oltp.test;

   Explained.

   SQL> select operation, options, object_name, cpu_cost, io_cost

     2  from plan_table;

   OPERATION          OPTIONS              OBJECT_NAME    CPU_COST    IO_COST

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

   SELECT STATEMENT                                        2677480         27

Next, re-gather statistics on the table and collect the extended statistics on the expression upper(cust_name).

begin

  dbms_stats.gather_table_stats (

     ownname    => 'ARUP',

     tabname    => 'CUSTOMERS',

     method_opt => 'for all columns size skewonly for columns (upper(cust_name))'

  );

end;

Alternatively you can define the column group as part of the gather statistics command.

You do that by placing these columns in the method_opt parameter of the gather_table_stats procedure in dbms_stats as shown below:
 

begin
   dbms_stats.gather_table_stats (
      ownname         => 'ARUP',
      tabname         => 'BOOKINGS',
      estimate_percent=> 100,
      method_opt  => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS(HOTEL_ID,RATE_CATEGORY)',
       cascade         => true