Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

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

From: Howard J. Rogers <howardjr_at_www.com>
Date: Fri, 5 Oct 2001 07:24:07 +1000
Message-ID: <3bbcd313@news.iprimus.com.au>


I've never tried it, but the procedure that Koert explained involves copying the table statistics to a holding table (using the "create_stat_table" procedure of the DBMS_STATS package).

I see no reason why, having done that, you couldn't simply query that holding table directly and (here's the untested part) perform regular DML on it to knock the statistics into whatever shape your heart desires.

Once they look as you want them to be, carry on as Koert explained (regular export, import, then 'import_table_stats').

Regards
HJR

--
Resources for OracleT: www.geocities.com/howardjr2000
=========================================


"Peter Shankey" <shankeyp@{NO-SPAM}its.charlestoncounty.org> wrote in
message news:20011004.17575100_at_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 - 16:24:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US