Import CBO statistics for particular tables

On some occasions, we found a query having problem and suspect the execution plan has changed due to new stats. We thus want to test if we restore to previous CBO stats will help. However we may only have exported previous CBO statistics at schema level, can we import CBO statistics for the particular tables involved using the stats table obtained at schema level? I did a test and find the anwer is 'Yes'.

Test steps / observations:

1. Current analyze time for the table TAB_OT

col owner format a20
col table_name format a20
select owner, table_name, num_rows, last_analyzed from dba_tables where table_name='TAB_OT';
 
OWNER                TABLE_NAME             NUM_ROWS LAST_ANAL
-------------------- -------------------- ---------- ---------
SCH_RO              TAB_OT           449849200 21-FEB-09

2. DBMS_STATS.IMPORT_TABLE_STATS definition:

PROCEDURE IMPORT_TABLE_STATS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME                        VARCHAR2                IN
 TABNAME                        VARCHAR2                IN
 PARTNAME                       VARCHAR2                IN     DEFAULT
 STATTAB                        VARCHAR2                IN
 STATID                         VARCHAR2                IN     DEFAULT
 CASCADE                        BOOLEAN                 IN     DEFAULT
 STATOWN                        VARCHAR2                IN     DEFAULT
 NO_INVALIDATE                  BOOLEAN                 IN     DEFAULT

3. Import table states

begin
   DBMS_STATS.IMPORT_TABLE_STATS (
     'SCH_RO',
     'TAB_OT',
      NULL,
     'SCH_RO_STATS_20090209',
      NULL,
      TRUE,
      'OPS$ORACLE',
      TRUE
);
end;
/ 

Note: SCH_RO_STATS_20090209 is a stats table for the schema SCH_RO

4. Check analyze time again:

 
OWNER             TABLE_NAME          NUM_ROWS LAST_ANAL
------------------ ----------------- ---------- ---------
SCH_RO              TAB_OT           432460407 07-FEB-09

 
OWNER                INDEX_NAME               TABLE_NAME           LAST_ANAL
-------------------- ------------------------ -------------------- ---------
SCH_RO              TAB_OT_PK                 TAB_OT          07-FEB-09
SCH_RO              TAB_OT_IX2                TAB_OT          07-FEB-09
SCH_RO              TAB_OT_IX4                TAB_OT          08-FEB-09
SCH_RO              TAB_OT_IX20               TAB_OT
SCH_RO              TAB_OT_IX5                TAB_OT          08-FEB-09
SCH_RO              TAB_OT_FK8                TAB_OT          07-FEB-09
SCH_RO              TAB_OT_IX3                TAB_OT          07-FEB-09
SCH_RO              TAB_OT_FK4                TAB_OT          07-FEB-09
SCH_RO              TAB_OT_FK1                TAB_OT          07-FEB-09
 
9 rows selected.

5. Command to export schema stats
(1) Create stats tab
begin
DBMS_STATS.CREATE_STAT_TABLE (
'STATTABOWNER',
'SCH_RO_STATS_20090302'
);
end;
/


(2) Export stats
begin
DBMS_STATS.EXPORT_SCHEMA_STATS (
'SCH_RO',
'SCH_RO_STATS_20090302' ,
NULL,
'STATABOWNER');
end;
/