cboStatsPost.txt
2005-09-13 Bikle
------------------

This file contains a short discussion about a simple use of the the Oracle 10g 
dbms_stats package.



The dbms_stats.gather_table_stats() procedure allows the DBA to gather CBO statistics for a particular table.

A demonstration of running the procedure is displayed below:

SQL> exec dbms_stats.gather_table_stats(tabname=>'CARS', ownname=>'BIKLE', estimate_percent=>'11')

PL/SQL procedure successfully completed.

SQL> SELECT last_analyzed FROM dba_tables WHERE owner='BIKLE' AND table_name='CARS';

LAST_ANAL
---------
08-SEP-05

Once the statistics are gathered, the DBA may then copy them into a
table via not one, but two other procedures:

dbms_stats.create_stat_table()
dbms_stats.export_table_stats()

A demonstration of the above two procedures is displayed below:


SQL> 
SQL> desc STATTAB10
ERROR:
ORA-04043: object STATTAB10 does not exist


SQL> EXEC dbms_stats.create_stat_table ('DBADBIKLE', 'STATTAB10')

PL/SQL procedure successfully completed.

SQL> desc STATTAB10
 Name				  Null?    Type
 -------------------------------- -------- -----------------------
 STATID 				   VARCHAR2(30)
 TYPE					   CHAR(1)
 VERSION				   NUMBER
 FLAGS					   NUMBER
 C1					   VARCHAR2(30)
 C2					   VARCHAR2(30)
 C3					   VARCHAR2(30)
 C4					   VARCHAR2(30)
 C5					   VARCHAR2(30)
 N1					   NUMBER
 N2					   NUMBER
 N3					   NUMBER
 N4					   NUMBER
 N5					   NUMBER
 N6					   NUMBER
 N7					   NUMBER
 N8					   NUMBER
 N9					   NUMBER
 N10					   NUMBER
 N11					   NUMBER
 N12					   NUMBER
 D1					   DATE
 R1					   RAW(32)
 R2					   RAW(32)
 CH1					   VARCHAR2(1000)

SQL> 

SQL> EXEC dbms_stats.export_table_stats ('BIKLE','CARS',NULL,'STATTAB10','bikle-cars-stats-2005-09-08',TRUE,'DBADBIKLE')

PL/SQL procedure successfully completed.

At this point the statistics are safely stored in an Oracle table.

This means of course we may export them to an export file:


$ exp dbadbikle/tiger@p file=stattab10-2005-09-08.dmp tables=stattab10

Export: Release 10.1.0.3.0 - Production on Thu Sep 09 17:41:18 2005

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                      STATTAB10       2281 rows exported
Export terminated successfully without warnings.



Should we want to restore the statistics to a place where the CBO could use them again,
we would make use of two procedures:

dbms_stats.delete_table_stats()
dbms_stats.import_table_stats()

These are demonstrated below:

SQL> EXEC dbms_stats.delete_table_stats(tabname=>'CARS', ownname=>'BIKLE')

PL/SQL procedure successfully completed.

SQL> SELECT last_analyzed FROM dba_tables WHERE owner='BIKLE' AND table_name='CARS';

LAST_ANAL
---------


1 row selected.

SQL> EXEC dbms_stats.import_table_stats('BIKLE','CARS',NULL,'STATTAB10','bikle-cars-stats-2005-09-08',TRUE,'DBADBIKLE')

PL/SQL procedure successfully completed.

SQL> SELECT last_analyzed FROM dba_tables WHERE owner='BIKLE' AND table_name='CARS';

LAST_ANAL
---------
08-SEP-05

1 row selected.


The fact that the value of the last_analyzed column changed from NULL to 08-SEP-05,
should convince you that CBO statistics had just been restored to this table.