SET ECHO ON TERM ON LIN 2000 TRIMS ON SERVEROUT ON SIZE 1000000 NUMF "" TIM OFF SQLP SQL>;
SPO sqltimp.log;
REM
REM $Header: 215187.1 sqltimp.sql 11.2.9.6 2009/07/23 csierra $
REM
REM Copyright (c) 2000-2009, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM   carlos.sierra@oracle.com
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 SQLT in the same or
REM   different system
REM
REM PRE-REQUISITES
REM   1. Install the SQLT tool in both, source and destination systems
REM      as per instructions.txt provided
REM   2. Execute SQLT (any method) in source system for one SQL
REM      statement
REM   3. Transfer BINARY file sqlt.dmp from source to destination
REM      system
REM   4. Import CBO stats into destination system using command below:
REM      # imp sqltxplain/[pwd] tables='sqlt$_stattab' file=sqlt.dmp ignore=y
REM   5. 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 SQLT for SQL in source system (required),
REM      A list of statement ids is presented to the user executing
REM      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/utl 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/utl
REM   # sqlplus 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 FEED OFF HEA ON LIN 200 PAGES 100 TRIMS ON TIM OFF;
PRO
SELECT g.statid statement_id,
       COUNT(*) stats_rows,
       (SELECT COUNT(*) FROM sqltxplain.sqlt$_stattab t WHERE t.statid = g.statid AND t.type = 'T' AND t.c2 IS NULL) tables,
       (SELECT COUNT(*) FROM sqltxplain.sqlt$_stattab i WHERE i.statid = g.statid AND i.type = 'I' AND i.c2 IS NULL) indexes,
       (SELECT COUNT(*) FROM sqltxplain.sqlt$_stattab c WHERE c.statid = g.statid AND c.type = 'C' AND c.c2 IS NULL) columns,
       (SELECT TO_CHAR(s.sqlt_date, 'DD-MON-YY HH24:MI:SS') FROM sqltxplain.sqlt$_statement s WHERE s.statid = g.statid) sqlt_date
  FROM sqltxplain.sqlt$_stattab g
 WHERE g.statid LIKE 's%'
 GROUP BY g.statid
 ORDER BY g.statid;
PRO
PRO Parameters:
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
PRO Enter partial or complete id to be restored, and destination user or null
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$r.initialization;
EXEC sqltxplain.sqlt$d.import_cbo_stats(p_statement_id => '&&statement_id.', p_schema_owner => '&&schema_owner');
PRO
SPOOL OFF;
CL COL
UNDEFINE 1 2 statement_id schema_owner;
PRO SQLTIMP completed. Please check sqltimp.log for any errors.
