Technote

PURPOSE

-------



Provide simplified instructions on how to transfer CBO statistics to support.



 

SCOPE & APPLICATION

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



DBAs, Developers and Support personnel





Transferring Optimizer Statistics to Support

============================================



Step Checklist:

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



1. Create a statistics table in the  schema

2. Transfer the statistics to this table

3. Export the STAT table

4. Export the affected tables structure

5. Check the export log

6. Upload the export 'dmp' file to metalink



Step detail:

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



1. Create a statistics table in the  schema :

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



N.B.  is the owner of the tables for which support requests CBO statistics.



        SQL> connect /password



        SQL> exec dbms_stats.create_stat_table(NULL,'STAT');

        PL/SQL procedure successfully completed.







2. Transfer the statistics to this table :

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



Transfer of statistics is achieved using the 'dbms_stats.export_table_stats'

procedure.



N.B.  is the table for which CBO statistics will be transferred.

        

        Transferring statistics of multiple tables:

        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        

        Run the package once for each set of statistics to transfer.

        In the following example there are 2 tables:



        SQL> exec dbms_stats.export_table_stats(NULL,'',NULL,'STAT');

        PL/SQL procedure successfully completed.

        SQL> exec dbms_stats.export_table_stats(NULL,'',NULL,'STAT');

        PL/SQL procedure successfully completed.



        Transferring statistics for a complete user schema:

        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        In some cases it might be required to transfer the statistics of a complete

        user schema. In this situation use the following statement:



        SQL> exec dbms_stats.export_schema_stats('','STAT');

        PL/SQL procedure successfully completed.







3. Export the STAT table :

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



        exp system/manager tables=.STAT file=STAT.dmp log=STAT.log







4. Export the affected tables structure :

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

         

        In order to reproduce an explain plan, Oracle Support Services only

        needs the table structure and the statistics. There is no need to 

        transfer any table data to Oracle Support Services. 

        Exporting the table with the 'rows=n' and 'statistics=none' command 

        can be used to create a export dump that contains the required DDL commands.



        Exporting a single table:

        ~~~~~~~~~~~~~~~~~~~~~~~~~

        exp system/ file=table.dmp tables=. rows=n statistics=none


        Exporting a complete user schema:

        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        exp system/ file=schema.dmp owner= rows=n statistics=none







5. Check the export log : 

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



Please ensure that the export has completed successfully.

The log should look something like this:



        Current user changed to 

        . . exporting table                           STAT          N rows exported







6. Upload the export 'dmp' file to metalink :

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



        Upload both the table structure and the statistic table exports.



        If using 'ftp', remember that 'dmp' files must be transferred in 

        binary mode as otherwise they will be corrupted and unuseable.

        



RELATED DOCUMENTS

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

Oracle9i Supplied PL/SQL Packages and Types Reference, Release 2 (9.2) Part Number A96612-01



Note 117203.1 How to Use DBMS_STATS to Move Statistics to a Different Database