SET ECHO ON TERM ON LIN 2000 TRIMS ON SERVEROUT ON SIZE 1000000 NUMF "" TIM OFF SQLP SQL>;
SPO sqltimpfo.log;
REM
REM $Header: 215187.1 sqltimpfo.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   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 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. 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 SQLT for SQL in source system (required),
REM      A list of statement ids is presented to the user executing
REM      this script.
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 sqltimpfo.sql passing statement id
REM      (parameter can be passed inline or until requested)
REM
REM EXAMPLE
REM   # cd sqlt/utl
REM   # sqlplus 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 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 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 'f%'
 GROUP BY g.statid
 ORDER BY g.statid;
PRO
PRO Parameters:
PRO ~~~~~~~~~~
PRO Parameter 1: Statement id to restore fixed objects CBO stats from (required)
PRO
PRO Enter partial or complete id to be restored
PRO
DEF statement_id = '&1';
PRO
PRO ... restoring fixed objects cbo stats from statement id &&statement_id.
PRO
EXEC sqltxplain.sqlt$r.initialization;
EXEC sqltxplain.sqlt$d.import_cbo_stats_fixed_objects(p_statement_id => '&&statement_id.');
PRO
SPOOL OFF;
CL COL
UNDEFINE 1 statement_id;
PRO SQLTIMPFO completed. Please check sqltimpfo.log for any errors.
