Re: query modeling ... how to make Oracle believe it has 10K rows whenit has less

Home -> Community -> Usenet -> c.d.o.server -> Re: query modeling ... how to make Oracle believe it has 10K rows whenit has less

From: koert54 <koert54_at_nospam.com>
Date: Wed, 03 Oct 2001 21:51:40 GMT
Message-ID: <MLLu7.84481$6x5.18755672@afrodite.telenet-ops.be>

It is possible to move statistics from one DB to another using dbms_stats

Have fun,
Koert

taken from metalink :
How to Use DBMS_STATS to Move Statistics to a Different Database:

You want to copy database statistics from one database to another database.

For example, you want to test certain operations on a scaled-down copy of your production database and you need the statistics from the production database.

This article shows you how to use the DBMS_STATS package to copy statistics from one database to another database.

Summary of Steps:

There are four basic steps to copy the statistics from one database to another database using DBMS_STATS:

Create a table in your database to hold the statistics.
Move the statistics from the data dictionary to the table you created in step 1.
Use the Oracle export/import tools to move the data (statistics) from the holding table in your database to a second database.
Populate the data dictionary of the second database with the statistics from the holding table that were copied from the original database.

Step Details:

The following shows you the basic syntax to use for each of the above steps:

Create the holding table using DBMS_STATS:

SQL> exec dbms_stats.create_stat_table('SCOTT','STATS'); PL/SQL procedure successfully completed.

This command creates the holding table for statistics. The table that is created is owned by SCOTT and called STATS (SCOTT.STATS).

PROCEDURE CREATE_STAT_TABLE

    Argument Name                  Type                    In/Out Default?


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

    OWNNAME                        VARCHAR2                IN
    STATTAB                        VARCHAR2                IN
    TBLSPACE                       VARCHAR2                IN     DEFAULT

2) Move the statistics to the STATS holding table.

SQL> exec dbms_stats.export_table_stats('SCOTT','EMP',NULL,'STATS','emp stats',TRUE);
PL/SQL procedure successfully completed.

This populates the holding table SCOTT.STATS with statistics gathered on the SCOTT.EMP table, and includes any indexes and places them in the SCOTT.STATS table.

PROCEDURE EXPORT_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

3) Export and Import the data in the STATS table.

First, run the export:

%exp scott/tiger tables=STATS file=expstat.dmp

About to export specified tables via Conventional Path ...

   . . exporting table                          STATS  ...

Then on the new database, run import:

%imp scott/tiger file=expstat.dmp full=y log=implog.txt

4) Populate the data dictionary in the new database.

SQL> exec dbms_stats.import_table_stats('SCOTT','EMP',NULL,'STATS'); PL/SQL procedure successfully completed.

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

This populates the data dictionary with the statistics for the SCOTT.EMP table in the new database with the statistics of the SCOTT.EMP table from the original database.

If your init.ora parameters are the same in both databases, you expect the same explain plans on the old and new databases, regardless of the actual data. This is because the Cost-Based Optimizer makes its decisions on how to obtain the data based on statistics.

There are also procedures for performing this activity on the whole schema and database. For example,

IMPORT_SCHEMA_STATS, IMPORT_DATABASE_STATS (and EXPORT_DATABASE_STATS, EXPORT_SCHEMA_STATS) For more information on these prodedures, issue the command:

desc dbms_stats

This command describes the package and lists the procedures and arguments.

"Peter Shankey" <shankeyp_at_its.charlestoncounty.org> wrote in message news:20011003.21043500_at_its.charlestoncounty.org...

       oracle 8.1.7
       I seem to recall reading someplace it is possible to model how a
query will be optimized given different numbers of rows in the tables
       which make up the query. I know given the tables populated I can
do a analyze { Index|Table|Cluster } .... then perform an explain plan
       on the query, but I seem to recall I could make the optimizer
believe there are 10M rows instead of 10 rows. It seems to me some of the
       values in some of system tables would need to be changed. If this
is the case please point me in the correct direction to do something like
       this.

pete Received on Wed Oct 03 2001 - 16:51:40 CDT