Alejandro Vargas

Oracle Israel

A critical application suddenly seems to hang, wait events show long table scans running on the OLTP environment. It comes out that the DBA in charge of this system did run statistics on the tables of the user that owns the application. The gather statistics got stuck and the process was killed. Since this moment the application started to perform. extremely slowly.

The production database has several clones; we decide to export back statistics from one of these clones, to the production database.

1) Create a table to hold statistics on the source database

2) Generate a script. that export table statistics on the clone database

3) Generate a script. that import statistics on the clone database

4) Export statistics on clone database

5) Export table containing the exported statistics from clone database

6) Ftp export file with clone statistics table, and the script. to import statistics from clone server to production server

7) Import table containing clone statistics into production database

8) Import statistics on production server using the script. to import statisctics generated on the clone server

-- On Clone Database

SQL> execute DBMS_STATS.create_stat_table('INV','OLD_STATS');

PL/SQL procedure successfully completed.

SQL> grant SELECT,INSERT,UPDATE,DELETE on INV.OLD_STATS to public;

Grant succeeded.

The purpose of this script. is to generate one export statistics command per table, the export is directed into the table created on step 1.

Variables:

&tabname = the table created on the previous step to hold the statistics

&usrname = The name of the owner of &tabname

---- script. to generate export table stats start here ----------

set linesize 130 pagesize 0

spool expstat.sql

select 'exec dbms_stats.export_table_stats('||chr(39)||owner||chr(39)||','||chr(39)||table_name||chr(39)||',null,'||chr(39)||'&tabname'

||chr(39)||',null,true,'||chr(39)||'INV'||chr(39)||')'

from dba_tables where wner ='&usrname'

/

spool off

---- script. to generate export table stats end here ----------

Note: you may also use instead of the script. this command:

exec DBMS_STATS.export_schema_stats('&usrname','&tabname')

This syntax will run in 10g. It may fail on 8i – 9i databases with some objects. That’s why I prefer the script. on these versions.

The purpose of this script. is to generate one import statistics command per table, the source is the table created on step 1.

&tabname = the table created on the previous step to hold the statistics

&usrname = The name of the owner of &tabname

---- script. to generate import table stats start here ----------

set linesize 130 pagesize 0

spool impstats.sql

select 'exec dbms_stats.import_table_stats('||chr(39)||owner||chr(39)||','||chr(39)||table_name||chr(39)||',null,'||chr(

39)||'&tabname'||chr(39)||',null,true,'||chr(39)||'&usrname'||chr(39)||')'

from dba_tables where wner ='&usrname'

/

spool off

---- script. to generate import table stats end here ----------

Execute this script. to generate impstats.sql that will import the statistics on the production database.

Using the script. expstat.sql; generated on step 2, export statistics into the statistics table created on step 1.

vmractest:/oradisk/av/expstats>exp avargas file=exp_stats_from_clone tables=avr.old_stats feedback=1000

Export: Release 9.2.0.5.0 - Production on Tue Feb 20 11:57:02 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 - Production

Export done in IW8ISO8859P8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

Current user changed to AVR

. . exporting table OLD_STATS

....

4115 rows exported

Export terminated successfully without warnings.

Execute FTP session from target server, get both the table that contains the exported statistics and the script. to import them, generated on step :

proddb > ftp vmractest

Connected to vmractest

220 vmractest FTP server (SunOS 5.8) ready.

Name (vmractest:oracle): oracle

331 Password required for oracle.

Password:

230 User oracle logged in.

ftp> cd /oradisk/av/expstats

250 CWD command successful.

ftp> get exp_stats_from_clone.dmp

200 PORT command successful.

150 ASCII data connection for exp_stats_from_clone.dmp (10.5.180.72,64082) (473088 bytes).

226 ASCII Transfer complete.

local: exp_stats_from_clone.dmp remote: exp_stats_from_clone.dmp

478390 bytes received in 0.17 seconds (2680.69 Kbytes/s)

ftp> get impstats.sql

200 PORT command successful.

150 ASCII data connection for impstats.sql (10.5.180.72,64776) (31461 bytes).

226 ASCII Transfer complete.

local: impstats.sql remote: impstats.sql

31704 bytes received in 0.033 seconds (947.63 Kbytes/s)

ftp> bye

221 Goodbye.

On the production database import the table that contains the exported statistics.

proddb >imp avargas file= exp_stats_from_clone.dmp full =y

Import: Release 9.2.0.5.0 - Production on Tue Feb 20 12:19:11 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path

import done in UTF8 character set and UTF8 NCHAR character set

export client uses IW8ISO8859P8 character set (possible charset conversion)

export server uses AL16UTF16 NCHAR character set (possible ncharset conversion)

. importing AVARGAS's objects into AVARGAS

. importing AVR's objects into AVR

. . importing table "OLD_STATS" 4115 rows imported

Import terminated successfully without warnings.

Using the script. impstats.sql; generated on step 3, import statistics into the production database.