SET ECHO ON TERM ON LIN 32767 TRIMS ON SERVEROUT ON SIZE 1000000 TIM OFF SQLP SQL>;
SPO sqltimpfo.log;
REM
REM $Header: 215187.1 sqltimpfo.sql 11.2.1 2008/09/21 csierra $
REM
REM Copyright (c) 2008, Oracle Corporation.  All rights reserved.
REM
REM SCRIPT
REM   sqltimpfo.sql
REM
REM DESCRIPTION
REM   This script restores into the data dictionary the set of CBO
REM   statistics for all fixed objects.
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
REM EXECUTION
REM   1. Navigate to sqlt/run directory
REM   2. Start SQL*Plus connecting as SQLTXPLAIN or application user
REM   3. Execute script sqltimpfo.sql passing statement id
REM      (parameter can be passed inline or until requested)
REM
REM EXAMPLE
REM   # cd sqlt/run
REM   # sqlplus sqltxplain/[sqltxplain pwd]
REM   SQL> start sqltimpfo.sql [statement id];
REM   SQL> start sqltimpfo.sql f2263_olc510_apperf02;
REM   SQL> start sqltimpfo.sql f2263;
REM   SQL> start sqltimpfo.sql;
REM
REM NOTES
REM   1. For possible errors see sqltimpfo.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 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 'f%'
 GROUP BY g.statid
 ORDER BY g.statid;
PRO
PRO Parameter 1: Statement id to restore fixed objects CBO stats FROM (required)
PRO
DEF statement_id = '&1';
PRO
PRO ... restoring fixed objects cbo stats FROM statement id &&statement_id
PRO
EXEC sqltxplain.sqlt$d.import_cbo_stats_fixed_objects(p_statement_id => '&&statement_id');
PRO
SPOOL OFF;
PRO NOTE:
PRO   SQLTIMP complete. Please check sqltimpfo.log for any errors.
PRO
CL COL
UNDEFINE 1
REM