Translate this Page

 

Sometime we got difficulties to get actual result of the explain plan when we want to see the cost of the query that we made since the number of records in the development server have significant differential with production server. To solve this problem we can transfer the Optimizer Statistics from Production server to Development Server.

Other concern why we need to transfer the Optimizer Statistics, because it will take long time to gather a big schema instead to transfer the Optimizer Statistics.

This is the Step-By-Step to transfering the Optimizer Statistics, Good Luck ;)

1. Connect to Oracle

SQL> conn apps/apps
Connected.

2. Create Stat Table

using : DBMS_STATS.CREATE_STAT_TABLE ( ownname varchar2,
stattab varchar2,
tblspace varchar2 default null);
 
SQL> exec dbms_stats.create_stat_table(NULL,'APPS_STAT');
PL/SQL procedure successfully completed.

3. Export Schema Stat to Table Stat

using : DBMS_STATS.EXPORT_SCHEMA_STATS ( ownname varchar2,
stattab varchar2,
statid varchar2 default null,
statown varchar2 default null);
SQL> exec dbms_stats.export_schema_stats(NULL,'APPS_STAT');
PL/SQL procedure successfully completed.

 If we want to export other schema (eg. PO) to the same stat table :

SQL> exec dbms_stats.export_schema_stats('PO','APPS_STAT',NULL,'APPS');
PL/SQL procedure successfully completed.

If we want to export a stat of table :

SQL> exec dbms_stats.export_table_stats('INV','MTL_SYSTEM_ITEMS_B',NULL,'INV_STAT',NULL,TRUE,'APPS');
PL/SQL procedure successfully completed.

4. Export to dumpfile the Stat Table

$ exp apps@tranprod buffer=65000 tables=APPS_STAT file=apps_stat.dmp
Export: Release 9.2.0.3.0 - Production on Wed Jan 16 11:45:39 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table APPS_STAT 35914 rows exported
Export terminated successfully without warnings.

5. Copy the dump file from to the target server

$ sftp user@servertarget
Connecting to servertarget...
Password:
sftp> put apps_stat.dmp
Uploading apps_stat.dmp to /home/fincl/apps_stat.dmp
apps_stat.dmp 100% 4261KB 4.2MB/s 00:00
sftp> exit

6. Import dumpfile to target server

$ imp system@transprd file=apps_stat.dmp fromuser=apps touser=apps
 
Import: Release 9.2.0.3.0 - Production on Wed Jan 16 11:28:18 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
Warning: the objects were exported by APPS, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing APPS's objects into APPS
. . importing table "APPS_STAT" 35914 rows imported
Import terminated successfully without warnings.

 7. Import statistic from stat table to dictionary

$ sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.3.0 - Production on Wed Jan 16 11:30:59 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL> exec dbms_stats.import_schema_stats('PO','APPS_STAT',NULL,'APPS');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.import_schema_stats('APPS','APPS_STAT',NULL,'APPS');
PL/SQL procedure successfully completed.

 If you want only import specific table :

SQL> exec dbms_stats.import_table_stats('MRP','MRP_FORECAST_DATES',NULL,'INV_STAT',NULL,TRUE,'APPS');

8. You have done these steps completely, now you can using the same optimizer statistic without same amount of records.

  One Response to “HOW TO Transfering The Optimizer Statistics”

  1. Hi,

    Very interesting. Nice article and discussion.
    Thanks a lot for same.

    Regards,
    Gitesh Trivedi
    http://www.dbametrix.com

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

   
© 2012 LevICORP's webnote Suffusion theme by Sayontan Sinha