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

From: Peter Shankey <shankeyp_at_{NO-SPAM}its.charlestoncounty.org>
Date: Thu, 04 Oct 2001 17:57:51 GMT
Message-ID: <20011004.17575100@its.charlestoncounty.org>

but is there a way if no database has that many rows???

>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 10/3/01, 5:51:40 PM, "koert54" <koert54_at_nospam.com> wrote regarding Re: query modeling ... how to make Oracle believe it has 10K rows whenit has less:

> 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:

> 1) Create a table in your database to hold the statistics.

> 2) Move the statistics from the data dictionary to the table you created
> in step 1.

> 3) Use the Oracle export/import tools to move the data (statistics) from
> the holding table in your database to a second database.

> 4) 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:

> 1) 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 Thu Oct 04 2001 - 12:57:51 CDT