SET ECHO ON TERM ON LIN 32767 TRIMS ON SERVEROUT ON SIZE 1000000 TIM OFF SQLP SQL>;
SPO sqltimp.log;
REM
REM $Header: 215187.1 sqltimp.sql 11.2.1 2008/09/21 csierra $
REM
REM Copyright (c) 2008, Oracle Corporation.  All rights reserved.
REM
REM SCRIPT
REM   sqltimp.sql
REM
REM DESCRIPTION
REM   This script restores into the data dictionary the set of CBO
REM   statistics for all the tables referenced by one SQL statement,
REM   together with the CBO statistics for the indexes of those same
REM   tables, their partitions, subpartitions, columns and
REM   histograms.
REM   The CBO statistics to be restored, are those associated to one
REM   SQL statement previously analyzed by the SQLTXPLAIN in the same
REM   or different system
REM
REM PRE-REQUISITES
REM   1. Install SQLTXPLAIN tool in both, source and destination
REM      systems as per instructions.txt provided
REM   2. Execute SQLTXPLAIN (any method) in source system for one SQL
REM      statement
REM   3. Export CBO stats FROM source system using command below:
REM      # exp sqltxplain/[pwd] tables=sqlt% file=sqlt.dmp
REM   4. Transfer BINARY file sqlt.dmp FROM source to destination
REM      system
REM   5. Import CBO stats into destination system using command below:
REM      # imp sqltxplain/[pwd] tables='sqlt$_stattab' file=sqlt.dmp ignore=y
REM   6. The user that executes this script can be SQLTXPLAIN or the
REM      application user in source that owns the schema objects for
REM      which the CBO statistics are being restored
REM
REM PARAMETERS
REM   1. Statement ID as per SQLTXPLAIN for SQL in source system
REM      (required). A list of statement ids is presented to the user
REM      executing this script.
REM   2. Schema owner of those objects for which the CBO statistics
REM      are being restored. This parameter is required if the same
REM      objects in source instance belong to different schema owners
REM      and the destination instance consolidates all those objects
REM      into just one schema (this). If the owners in source and
REM      destination are the same (they are not being changed), then
REM      skip this parameter by entering NULL or just hit enter when
REM      asked for the schema owner. In other words, this parameter
REM      is for renaming schema owner of dependent objects.
REM
REM EXECUTION
REM   1. Navigate to sqlt/run directory
REM   2. Start SQL*Plus connecting as SQLTXPLAIN or application user
REM   3. Execute script sqltimp.sql passing statement id and schema
REM      (parameters can be passed inline or until requested)
REM
REM EXAMPLE
REM   # cd sqlt/run
REM   # sqlplus sqltxplain/[sqltxplain pwd]
REM   SQL> start sqltimp.sql [statement id] [new schema_owner];
REM   SQL> start sqltimp.sql s2263_olc510_apperf02 mytest;
REM   SQL> start sqltimp.sql s2263 NULL;
REM   SQL> start sqltimp.sql;
REM
REM NOTES
REM   1. For possible errors see sqltimp.log
REM
SET ECHO OFF;
EXEC sqltxplain.sqlt$r.initialization;
SELECT g.statid statement_id,
       COUNT(*) stats_rows,
       (SELECT COUNT(*) FROM sqltxplain.sqlt$_stattab t WHERE t.statid = g.statid AND t.type = 'T') tables,
       (SELECT COUNT(*) FROM sqltxplain.sqlt$_stattab i WHERE i.statid = g.statid AND i.type = 'I') indexes,
       (SELECT COUNT(*) FROM sqltxplain.sqlt$_stattab c WHERE c.statid = g.statid AND c.type = 'C') columns,
       (SELECT ROUND(AVG(SYSDATE - a.d1), 1) FROM sqltxplain.sqlt$_stattab a WHERE a.statid = g.statid AND a.type = 'T') avg_age_days
  FROM sqltxplain.sqlt$_stattab g
 WHERE statid LIKE 's%'
 GROUP BY g.statid
 ORDER BY g.statid;
PRO
PRO Parameter 1: Statement id to restore CBO stats FROM (required)
PRO Parameter 2: Renamed schema owner for which stats are restored (opt)
PRO
DEF statement_id = '&1';
DEF schema_owner = '&2';
PRO
PRO ... restoring cbo stats FROM statement id &&statement_id and schema &&schema_owner
PRO
EXEC sqltxplain.sqlt$d.import_cbo_stats(p_statement_id => '&&statement_id', p_schema_owner => '&&schema_owner');
PRO
SPOOL OFF;
PRO NOTE:
PRO   SQLTIMP complete. Please check sqltimp.log for any errors.
PRO
CL COL
UNDEFINE 1 2
REM