Instructions to perform the following:
Steps:
sqlt_s53237_driver.zip
in order to get sqlt_s53237_export_parfile.txt
.sqlt_s53237_export_parfile.txt
to SOURCE server (TEXT).exp sqltxplain parfile=sqlt_s53237_export_parfile.txt
Steps:
sqlt_s53237_tc.zip
in order to get sqlt_s53237_expdp.dmp
.sqlt_s53237_exp.dmp
to the server (BINARY).imp sqltxplain FILE=sqlt_s53237_exp.dmp TABLES=sqlt% IGNORE=Y
You can execute sqlt_s53237_import.sh
instead.
You need to have a set of SQLT files (sqlt_sNNNNN_method.zip) from two executions of the SQLT tool. They can be from any method (XTRACT, XECUTE or XPLAIN) and they can be from the same or different systems. They do not have to be from same release or platform. For example, a SQLT from 10g on Linux and a SQLT from 11g on Unix can be compared.
To use the COMPARE method you need 3 systems: SOURCE1, SOURCE2 and COMPARE. The 3 could all be different, or all the same. For example, SOURCE1 could be PROD, SOURCE2 DEV and COMPARE DEV. In other words, you could do the COMPARE in one of the sources. Or the COMPARE could be done on a 3rd and remote system.
Basically you need to restore the SQLT repository from both SOURCES into the COMPARE system. In most cases it means "restoring" the SQLT repository from at least one SOURCE into the COMPARE. Once you have both SQLT repositories into the COMPARE system, then you can execute this method.
Steps:
sqlt_s53237_tc.zip
from this SOURCE in order to get sqlt_s53237_expdp.dmp
.sqlt_s53237_exp.dmp
to the server (BINARY).imp sqltxplain FILE=sqlt_s53237_exp.dmp TABLES=sqlt% IGNORE=Y
START sqlt/run/sqltcompare.sql
CBO schema object statistics can be restored from the local SQLT repository, or from an imported repository. Restoring CBO statistics associates them to existing and compatible schema objects. These objects can be owned by the original schema owner or by a different one. For example, table T is owned by user U in SOURCE and by user TC53237 in TARGET.
When using restore script below, the second parameter allows to remap the schema object statistics to a different user. Be aware that target user and schema objects must exist before executing this script. To restore CBO schema object statistics into the original schema owner(s) pass "null" (or just hit the "Enter" key) when the second parameter is requested.
Steps:
START sqlt/utl/sqltimp.sql s53237_v1123_host01 TC53237
Steps:
START sqlt_s53237_system_stats.sql
SOURCE and TARGET systems should be similar. Proceed with Preparation followed by Express or Custom mode.
sqlt_s53237_tc.zip
in server and navigate to TC directory.unzip sqlt_s53237_tc.zip -d TC53237
cd TC53237
xpress.sh
from OS or xpress.sql
from sqlplus.Option 1: ./xpress.sh
Option 2: sqlplus / as sysdba @xpress.sql
sqlplus / as sysdba
START sqlt_s53237_metadata.sql
START sqlt_s53237_purge.sql
HOS imp sqltxplain FILE=sqlt_s53237_exp.dmp LOG=sqlt_s53237_imp.log TABLES=sqlt% IGNORE=Y
START sqlt_s53237_restore.sql
START sqlt_s53237_system_stats.sql
CONN TC53237/TC53237
START sqlt_s53237_set_cbo_env.sql
START tc.sql
After creating a local test case using SQLT files, you can create a stand-alone TC with no dependencies on SQLT.
Steps:
DELETE TC53237.CBO_STAT_TAB_4TC;
EXEC SYS.DBMS_STATS.EXPORT_SCHEMA_STATS(ownname => 'TC53237', stattab => 'CBO_STAT_TAB_4TC');
HOS exp TC53237/TC53237 FILE=cbo_stat_tab_4tc.dmp LOG=cbo_stat_tab_4tc.log TABLES=cbo_stat_tab_4tc STATISTICS=NONE
setup.sql
script and adjust if needed.readme.txt
file and adjust if needed.CBO schema object statistics dump: cbo_stat_tab_4tc.dmp Plan script: plan.sql Query script: q.sql Instructions: readme.txt Setup script: setup.sql Metadata script: sqlt_s53237_metadata.sql OPatch (if needed): sqlt_s53237_opatch.zip Set CBO env script (if needed): sqlt_s53237_set_cbo_env.sql System statistics setup: sqlt_s53237_system_stats.sql Test case script: tc.sql
readme.txt
in another system.Note: You may want to use tc_pkg.sql to execute commands above.
SOURCE and TARGET systems should be similar.
SQLT exported from SOURCE at least one SQL Set with a plan associated to your query. The SQL Set name below includes the plan hash value and its source (memory or awr).
You can copy a SQL Set into your TARGET system following these steps. After a SQL Set with one plan is restored, you can proceed to load it as a SQL Plan into its SQL Baseline.
Steps:
imp sqltxplain FILE=sqlt_s53237_exp.dmp TABLES=sqlt% IGNORE=Y
-- 001 s53237_v1123_host01_bwqa2yh268578_491627931_mem (et:4.296s, cpu:4.048s, buffers:1940, rows:2) BEGIN SYS.DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET ( sqlset_name => 's53237_491627931_mem', sqlset_owner => 'SQLTXADMIN', replace => TRUE, staging_table_name => 'SQLT$_STGTAB_SQLSET', staging_schema_owner => 'SQLTXPLAIN' ); END; /
You can load one SQL Plan into its SQL Plan Baseline from a SQL Set created by SQLT for each plan found in memory or AWR.
This method only works on the same system where SQLT was executed. Unless you first restore a SQL Set from a different source.
The SQL Set name below includes the plan hash value and its source (memory or awr). You can load one or more plans into a SQL Plan Baseline.
-- 001 s53237_v1123_host01_bwqa2yh268578_491627931_mem (et:4.296s, cpu:4.048s, buffers:1940, rows:2) SET SERVEROUT ON; DECLARE x NUMBER; time DATE := SYSDATE; l_planame sys.dba_sql_plan_baselines.plan_name%TYPE; l_sql_handle sys.dba_sql_plan_baselines.sql_handle%TYPE; BEGIN DBMS_LOCK.SLEEP(5); x := SYS.DBMS_SPM.LOAD_PLANS_FROM_SQLSET ( sqlset_name => 's53237_491627931_mem', sqlset_owner => 'SQLTXADMIN' ); SYS.DBMS_OUTPUT.PUT_LINE('Plans: '||x); IF x = 1 THEN SELECT plan_name, sql_handle INTO l_planame, l_sql_handle FROM sys.dba_sql_plan_baselines WHERE signature = 6036130775085112306 AND created >= time; x := DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( sql_handle => l_sql_handle, plan_name => l_planame, attribute_name => 'DESCRIPTION', attribute_value => TRIM('001 s53237_v1123_host01_bwqa2yh268578_491627931_mem (et:4.296s, cpu:4.048s, buffers:1940, rows:2)') ); x := DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( sql_handle => l_sql_handle, plan_name => l_planame, attribute_name => 'PLAN_NAME', attribute_value => UPPER('s53237_491627931_mem') ); SYS.DBMS_OUTPUT.PUT_LINE('Renamed: '||x||' '||UPPER('s53237_491627931_mem')); END IF; END; / SET SERVEROUT OFF;
Use commands below to generate a fresh set of CBO statistics for the schema objects accessed by your SQL. Histograms will be dropped.
BEGIN -- generated by SQLT SYS.DBMS_STATS.UNLOCK_TABLE_STATS ( ownname => '"SH"', tabname => '"PRODUCTS"' ); SYS.DBMS_STATS.GATHER_TABLE_STATS ( ownname => '"SH"', tabname => '"PRODUCTS"', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE 1', cascade => TRUE, no_invalidate => FALSE ); SYS.DBMS_STATS.UNLOCK_TABLE_STATS ( ownname => '"SH"', tabname => '"SALES"' ); SYS.DBMS_STATS.GATHER_TABLE_STATS ( ownname => '"SH"', tabname => '"SALES"', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE 1', granularity => 'GLOBAL AND PARTITION', cascade => TRUE, no_invalidate => FALSE ); END; /
Use commands below to generate a fresh set of CBO statistics for the schema objects accessed by your SQL. Histograms will be generated for some columns.
BEGIN -- generated by SQLT SYS.DBMS_STATS.UNLOCK_TABLE_STATS ( ownname => '"SH"', tabname => '"PRODUCTS"' ); SYS.DBMS_STATS.GATHER_TABLE_STATS ( ownname => '"SH"', tabname => '"PRODUCTS"', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE, no_invalidate => FALSE ); SYS.DBMS_STATS.UNLOCK_TABLE_STATS ( ownname => '"SH"', tabname => '"SALES"' ); SYS.DBMS_STATS.GATHER_TABLE_STATS ( ownname => '"SH"', tabname => '"SALES"', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', granularity => 'GLOBAL AND PARTITION', cascade => TRUE, no_invalidate => FALSE ); END; /
Files generated under current SQL*Plus directory.
Not all files may be available.
sqlt_s53237_main.html sqlt_s53237_metadata.sql sqlt_s53237_metadata1.sql sqlt_s53237_metadata2.sql sqlt_s53237_system_stats.sql sqlt_s53237_schema_stats.sql sqlt_s53237_set_cbo_env.sql sqlt_s53237_lite.html sqlt_s53237_readme.html sqlt_s53237_readme.txt sqlt_s53237_tcb_driver.sql sqlt_s53237_tcb.zip sqlt_s53237_remote_driver.sql sqlt_s53237_script_output_driver.sql sqlt_s53237_tkprof_px_driver.sql sqlt_s53237_export_parfile.txt sqlt_s53237_export_parfile2.txt sqlt_s53237_export_driver.sql sqlt_s53237_import.sh sqlt_s53237_export.zip sqlt_s53237_tc.zip sqlt_s53237_log.zip sqlt_s53237_opatch.zip sqlt_s53237_remote.zip sqlt_s53237_sta_report_mem.txt sqlt_s53237_sta_script_mem.sql sqlt_s53237_sta_report_txt.txt sqlt_s53237_sta_script_txt.sql sqlt_s53237_sql_detail_active.html sqlt_s53237_sql_monitor_active.html sqlt_s53237_trca_e16301.html sqlt_s53237_trca_e16301.txt sqlt_s53237_trca_e16301.log sqlt_s53237_10046_execute.trc sqlt_s53237_10053_execute.trc sqlt_s53237_10046_10053_execute.trc sqlt_s53237_10053_explain.trc sqlt_s53237_tkprof_sort.txt sqlt_s53237_tkprof_nosort.txt sqlt_s53237_xecute.log sqltxecute.log input/sample/script1.sql sqltxhost.log plan.sql 10053.sql flush.sql purge.sql restore.sql del_hgrm.sql tc.sql tc_pkg.sql xpress.sql xpress.sh setup.sql q.sql sel.sql sel_aux.sql install.sql install.sh tcx_pkg.sql
Files generated under SQLT$UDUMP directory.
To locate SQLT$UDUMP: SELECT directory_path FROM sys.dba_directories WHERE directory_name = 'SQLT$UDUMP';
Not all files may be available.
V1123_ora_3610_s53237_10046_10053.trc V1123_ora_3610_s53237_10053.trc
Files generated under SQLT$BDUMP directory.
To locate SQLT$BDUMP: SELECT directory_path FROM sys.dba_directories WHERE directory_name = 'SQLT$BDUMP';
Not all files may be available.
*_s53237_*.trc
Files generated under SQLT$STAGE directory.
To locate SQLT$STAGE: SELECT directory_path FROM sys.dba_directories WHERE directory_name = 'SQLT$STAGE';
Not all files may be available.
sqlt_s53237_tcb_* README.txt