215187.1 SQLT XTRACT 11.4.5.4 Report: sqlt_s53240_readme.html

Instructions to perform the following:


Export SQLT repository

Steps:

  1. Unzip sqlt_s53240_driver.zip in order to get sqlt_s53240_export_parfile.txt.
  2. Copy sqlt_s53240_export_parfile.txt to SOURCE server (TEXT).
  3. Execute export on server:
  4. exp sqltxplain parfile=sqlt_s53240_export_parfile.txt


Import SQLT repository

Steps:

  1. Unzip sqlt_s53240_tc.zip in order to get sqlt_s53240_expdp.dmp.
  2. Copy sqlt_s53240_exp.dmp to the server (BINARY).
  3. Execute import on server:
  4. imp sqltxplain FILE=sqlt_s53240_exp.dmp TABLES=sqlt% IGNORE=Y

You can execute sqlt_s53240_import.sh instead.


Using SQLT COMPARE

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:

  1. Unzip sqlt_s53240_tc.zip from this SOURCE in order to get sqlt_s53240_expdp.dmp.
  2. Copy sqlt_s53240_exp.dmp to the server (BINARY).
  3. Execute import on server:
  4. imp sqltxplain FILE=sqlt_s53240_exp.dmp TABLES=sqlt% IGNORE=Y

  5. Perform the equivalent steps for the 2nd SOURCE if needed. You may want to follow its readme file.
  6. Execute the COMPARE method connecting into SQL*Plus as SYS. You will be asked to enter which 2 statements you want to compare.
  7. START sqlt/run/sqltcompare.sql


Restore CBO schema statistics

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 TC53240 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:

  1. Execute restore script connecting as SYSDBA:
  2. START sqlt/utl/sqltimp.sql s53240_v1123_host01 TC53240


Restore CBO system statistics

Steps:

  1. Execute restore script connecting as SYSDBA:
  2. START sqlt_s53240_system_stats.sql


Implement SQLT Test Case (TC)

SOURCE and TARGET systems should be similar. Proceed with Preparation followed by Express or Custom mode.

Preparation

  1. Unzip sqlt_s53240_tc.zip in server and navigate to TC directory.
  2. unzip sqlt_s53240_tc.zip -d TC53240

    cd TC53240

Express (XPRESS) mode

  1. Review and execute xpress.sh from OS or xpress.sql from sqlplus.
  2. Option 1: ./xpress.sh

    Option 2: sqlplus / as sysdba @xpress.sql

Custom mode

  1. Create test case user and schema objects connecting as SYSDBA:
  2. sqlplus / as sysdba

    START sqlt_s53240_metadata.sql

  3. Purge pre-existing s53240 from local SQLT repository connected as SYSDBA:
  4. START sqlt_s53240_purge.sql

  5. Import SQLT repository for s53240 (provide SQLTXPLAIN password):
  6. HOS imp sqltxplain FILE=sqlt_s53240_exp.dmp LOG=sqlt_s53240_imp.log TABLES=sqlt% IGNORE=Y

  7. Restore CBO schema statistics for test case user connected as SYSDBA:
  8. START sqlt_s53240_restore.sql

  9. Restore CBO system statistics connected as SYSDBA:
  10. START sqlt_s53240_system_stats.sql

  11. Set the CBO environment connecting as test case user TC53240 (include optional test case user suffix):
  12. CONN TC53240/TC53240

    START sqlt_s53240_set_cbo_env.sql

  13. Execute test case:
  14. START tc.sql


Create TC with no SQLT dependencies

After creating a local test case using SQLT files, you can create a stand-alone TC with no dependencies on SQLT.

Steps:

  1. Export TC schema object statistics to staging table within TC schema:
  2. DELETE TC53240.CBO_STAT_TAB_4TC;
    EXEC SYS.DBMS_STATS.EXPORT_SCHEMA_STATS(ownname => 'TC53240', stattab => 'CBO_STAT_TAB_4TC');

  3. Export TC schema object statistics from staging table:
  4. HOS exp TC53240/TC53240 FILE=cbo_stat_tab_4tc.dmp LOG=cbo_stat_tab_4tc.log TABLES=cbo_stat_tab_4tc STATISTICS=NONE

  5. Review setup.sql script and adjust if needed.
  6. Review readme.txt file and adjust if needed.
  7. Create and zip a new directory with the following files:
  8. 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_s53240_metadata.sql
    OPatch (if needed):                sqlt_s53240_opatch.zip
    Set CBO env script (if needed):    sqlt_s53240_set_cbo_env.sql
    System statistics setup:           sqlt_s53240_system_stats.sql
    Test case script:                  tc.sql
    
  9. Test your new stand-alone TC following your own readme.txt in another system.

Note: You may want to use tc_pkg.sql to execute commands above.


Restore SQL Set

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:

  1. Import SQLT repository (only if you haven't done so as part of another operation like TC creation):
  2. imp sqltxplain FILE=sqlt_s53240_exp.dmp TABLES=sqlt% IGNORE=Y

  3. Copy one SQL Set by following corresponding syntaxt below.
-- 001 s53240_v1123_host01_f995z9antmhxn_24296316_mem (et:0.275s, cpu:0.046s, buffers:5066, rows:42)
BEGIN
  SYS.DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
    sqlset_name          => 's53240_24296316_mem',
    sqlset_owner         => 'SQLTXADMIN',
    replace              => TRUE,
    staging_table_name   => 'SQLT$_STGTAB_SQLSET',
    staging_schema_owner => 'SQLTXPLAIN' );
END;
/
-- 002 s53240_v1123_host01_f995z9antmhxn_3935795003_mem (et:1.548s, cpu:1.135s, buffers:6572, rows:42)
BEGIN
  SYS.DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
    sqlset_name          => 's53240_3935795003_mem',
    sqlset_owner         => 'SQLTXADMIN',
    replace              => TRUE,
    staging_table_name   => 'SQLT$_STGTAB_SQLSET',
    staging_schema_owner => 'SQLTXPLAIN' );
END;
/
-- 003 s53240_v1123_host01_f995z9antmhxn_3267771367_mem (et:2.418s, cpu:0.228s, buffers:21116, rows:144)
BEGIN
  SYS.DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
    sqlset_name          => 's53240_3267771367_mem',
    sqlset_owner         => 'SQLTXADMIN',
    replace              => TRUE,
    staging_table_name   => 'SQLT$_STGTAB_SQLSET',
    staging_schema_owner => 'SQLTXPLAIN' );
END;
/
-- 004 s53240_v1123_host01_f995z9antmhxn_657302870_mem (et:26.536s, cpu:25.876s, buffers:51544, rows:9114)
BEGIN
  SYS.DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
    sqlset_name          => 's53240_657302870_mem',
    sqlset_owner         => 'SQLTXADMIN',
    replace              => TRUE,
    staging_table_name   => 'SQLT$_STGTAB_SQLSET',
    staging_schema_owner => 'SQLTXPLAIN' );
END;
/
-- 005 s53240_v1123_host01_f995z9antmhxn_1637264670_mem (et:28.635s, cpu:27.353s, buffers:61331, rows:9114)
BEGIN
  SYS.DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
    sqlset_name          => 's53240_1637264670_mem',
    sqlset_owner         => 'SQLTXADMIN',
    replace              => TRUE,
    staging_table_name   => 'SQLT$_STGTAB_SQLSET',
    staging_schema_owner => 'SQLTXPLAIN' );
END;
/
-- 006 s53240_v1123_host01_f995z9antmhxn_2816325939_mem (et:31.374s, cpu:30.075s, buffers:19554, rows:635)
BEGIN
  SYS.DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
    sqlset_name          => 's53240_2816325939_mem',
    sqlset_owner         => 'SQLTXADMIN',
    replace              => TRUE,
    staging_table_name   => 'SQLT$_STGTAB_SQLSET',
    staging_schema_owner => 'SQLTXPLAIN' );
END;
/
-- 007 s53240_v1123_host01_f995z9antmhxn_142578110_mem (et:33.486s, cpu:32.434s, buffers:111227, rows:21727)
BEGIN
  SYS.DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
    sqlset_name          => 's53240_142578110_mem',
    sqlset_owner         => 'SQLTXADMIN',
    replace              => TRUE,
    staging_table_name   => 'SQLT$_STGTAB_SQLSET',
    staging_schema_owner => 'SQLTXPLAIN' );
END;
/
-- 008 s53240_v1123_host01_f995z9antmhxn_2883761925_mem (et:42.703s, cpu:32.932s, buffers:111266, rows:36164)
BEGIN
  SYS.DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
    sqlset_name          => 's53240_2883761925_mem',
    sqlset_owner         => 'SQLTXADMIN',
    replace              => TRUE,
    staging_table_name   => 'SQLT$_STGTAB_SQLSET',
    staging_schema_owner => 'SQLTXPLAIN' );
END;
/

Create SQL Plan Baseline from SQL Set

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 s53240_v1123_host01_f995z9antmhxn_24296316_mem (et:0.275s, cpu:0.046s, buffers:5066, rows:42)
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  => 's53240_24296316_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 = 1178211779310957485
       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 s53240_v1123_host01_f995z9antmhxn_24296316_mem (et:0.275s, cpu:0.046s, buffers:5066, rows:42)') );
    x := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
      sql_handle      => l_sql_handle,
      plan_name       => l_planame,
      attribute_name  => 'PLAN_NAME',
      attribute_value => UPPER('s53240_24296316_mem') );
    SYS.DBMS_OUTPUT.PUT_LINE('Renamed: '||x||' '||UPPER('s53240_24296316_mem'));
  END IF;
END;
/
SET SERVEROUT OFF;
-- 002 s53240_v1123_host01_f995z9antmhxn_3935795003_mem (et:1.548s, cpu:1.135s, buffers:6572, rows:42)
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  => 's53240_3935795003_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 = 1178211779310957485
       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('002 s53240_v1123_host01_f995z9antmhxn_3935795003_mem (et:1.548s, cpu:1.135s, buffers:6572, rows:42)') );
    x := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
      sql_handle      => l_sql_handle,
      plan_name       => l_planame,
      attribute_name  => 'PLAN_NAME',
      attribute_value => UPPER('s53240_3935795003_mem') );
    SYS.DBMS_OUTPUT.PUT_LINE('Renamed: '||x||' '||UPPER('s53240_3935795003_mem'));
  END IF;
END;
/
SET SERVEROUT OFF;
-- 003 s53240_v1123_host01_f995z9antmhxn_3267771367_mem (et:2.418s, cpu:0.228s, buffers:21116, rows:144)
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  => 's53240_3267771367_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 = 1178211779310957485
       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('003 s53240_v1123_host01_f995z9antmhxn_3267771367_mem (et:2.418s, cpu:0.228s, buffers:21116, rows:144)') );
    x := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
      sql_handle      => l_sql_handle,
      plan_name       => l_planame,
      attribute_name  => 'PLAN_NAME',
      attribute_value => UPPER('s53240_3267771367_mem') );
    SYS.DBMS_OUTPUT.PUT_LINE('Renamed: '||x||' '||UPPER('s53240_3267771367_mem'));
  END IF;
END;
/
SET SERVEROUT OFF;
-- 004 s53240_v1123_host01_f995z9antmhxn_657302870_mem (et:26.536s, cpu:25.876s, buffers:51544, rows:9114)
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  => 's53240_657302870_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 = 1178211779310957485
       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('004 s53240_v1123_host01_f995z9antmhxn_657302870_mem (et:26.536s, cpu:25.876s, buffers:51544, rows:9114)') );
    x := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
      sql_handle      => l_sql_handle,
      plan_name       => l_planame,
      attribute_name  => 'PLAN_NAME',
      attribute_value => UPPER('s53240_657302870_mem') );
    SYS.DBMS_OUTPUT.PUT_LINE('Renamed: '||x||' '||UPPER('s53240_657302870_mem'));
  END IF;
END;
/
SET SERVEROUT OFF;
-- 005 s53240_v1123_host01_f995z9antmhxn_1637264670_mem (et:28.635s, cpu:27.353s, buffers:61331, rows:9114)
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  => 's53240_1637264670_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 = 1178211779310957485
       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('005 s53240_v1123_host01_f995z9antmhxn_1637264670_mem (et:28.635s, cpu:27.353s, buffers:61331, rows:9114)') );
    x := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
      sql_handle      => l_sql_handle,
      plan_name       => l_planame,
      attribute_name  => 'PLAN_NAME',
      attribute_value => UPPER('s53240_1637264670_mem') );
    SYS.DBMS_OUTPUT.PUT_LINE('Renamed: '||x||' '||UPPER('s53240_1637264670_mem'));
  END IF;
END;
/
SET SERVEROUT OFF;
-- 006 s53240_v1123_host01_f995z9antmhxn_2816325939_mem (et:31.374s, cpu:30.075s, buffers:19554, rows:635)
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  => 's53240_2816325939_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 = 1178211779310957485
       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('006 s53240_v1123_host01_f995z9antmhxn_2816325939_mem (et:31.374s, cpu:30.075s, buffers:19554, rows:635)') );
    x := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
      sql_handle      => l_sql_handle,
      plan_name       => l_planame,
      attribute_name  => 'PLAN_NAME',
      attribute_value => UPPER('s53240_2816325939_mem') );
    SYS.DBMS_OUTPUT.PUT_LINE('Renamed: '||x||' '||UPPER('s53240_2816325939_mem'));
  END IF;
END;
/
SET SERVEROUT OFF;
-- 007 s53240_v1123_host01_f995z9antmhxn_142578110_mem (et:33.486s, cpu:32.434s, buffers:111227, rows:21727)
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  => 's53240_142578110_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 = 1178211779310957485
       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('007 s53240_v1123_host01_f995z9antmhxn_142578110_mem (et:33.486s, cpu:32.434s, buffers:111227, rows:21727)') );
    x := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
      sql_handle      => l_sql_handle,
      plan_name       => l_planame,
      attribute_name  => 'PLAN_NAME',
      attribute_value => UPPER('s53240_142578110_mem') );
    SYS.DBMS_OUTPUT.PUT_LINE('Renamed: '||x||' '||UPPER('s53240_142578110_mem'));
  END IF;
END;
/
SET SERVEROUT OFF;
-- 008 s53240_v1123_host01_f995z9antmhxn_2883761925_mem (et:42.703s, cpu:32.932s, buffers:111266, rows:36164)
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  => 's53240_2883761925_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 = 1178211779310957485
       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('008 s53240_v1123_host01_f995z9antmhxn_2883761925_mem (et:42.703s, cpu:32.932s, buffers:111266, rows:36164)') );
    x := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
      sql_handle      => l_sql_handle,
      plan_name       => l_planame,
      attribute_name  => 'PLAN_NAME',
      attribute_value => UPPER('s53240_2883761925_mem') );
    SYS.DBMS_OUTPUT.PUT_LINE('Renamed: '||x||' '||UPPER('s53240_2883761925_mem'));
  END IF;
END;
/
SET SERVEROUT OFF;

Gather CBO statistics without Histograms (using SYS.DBMS_STATS)

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       => '"QTUNE"',
    tabname       => '"CUSTOMER"'
  );
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
    ownname          => '"QTUNE"',
    tabname          => '"CUSTOMER"',
    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       => '"QTUNE"',
    tabname       => '"ORDER_LINE"'
  );
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
    ownname          => '"QTUNE"',
    tabname          => '"ORDER_LINE"',
    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       => '"QTUNE"',
    tabname       => '"PART"'
  );
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
    ownname          => '"QTUNE"',
    tabname          => '"PART"',
    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       => '"QTUNE"',
    tabname       => '"SALES_ORDER"'
  );
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
    ownname          => '"QTUNE"',
    tabname          => '"SALES_ORDER"',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE 1',
    cascade          => TRUE,
    no_invalidate    => FALSE
  );
END;
/

Gather CBO statistics with Histograms (using SYS.DBMS_STATS)

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       => '"QTUNE"',
    tabname       => '"CUSTOMER"'
  );
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
    ownname          => '"QTUNE"',
    tabname          => '"CUSTOMER"',
    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       => '"QTUNE"',
    tabname       => '"ORDER_LINE"'
  );
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
    ownname          => '"QTUNE"',
    tabname          => '"ORDER_LINE"',
    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       => '"QTUNE"',
    tabname       => '"PART"'
  );
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
    ownname          => '"QTUNE"',
    tabname          => '"PART"',
    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       => '"QTUNE"',
    tabname       => '"SALES_ORDER"'
  );
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
    ownname          => '"QTUNE"',
    tabname          => '"SALES_ORDER"',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE,
    no_invalidate    => FALSE
  );
END;
/

List generated files

Files generated under current SQL*Plus directory.
Not all files may be available.

sqlt_s53240_main.html
sqlt_s53240_metadata.sql
sqlt_s53240_metadata1.sql
sqlt_s53240_metadata2.sql
sqlt_s53240_system_stats.sql
sqlt_s53240_schema_stats.sql
sqlt_s53240_set_cbo_env.sql
sqlt_s53240_lite.html
sqlt_s53240_readme.html
sqlt_s53240_readme.txt
sqlt_s53240_tc_script.sql
sqlt_s53240_tc_sql.sql
sqlt_s53240_tcb_driver.sql
sqlt_s53240_tcb.zip
sqlt_s53240_remote_driver.sql
sqlt_s53240_tkprof_px_driver.sql
sqlt_s53240_export_parfile.txt
sqlt_s53240_export_parfile2.txt
sqlt_s53240_export_driver.sql
sqlt_s53240_import.sh
sqlt_s53240_export.zip
sqlt_s53240_tc.zip
sqlt_s53240_log.zip
sqlt_s53240_opatch.zip
sqlt_s53240_remote.zip
sqlt_s53240_sta_report_mem.txt
sqlt_s53240_sta_script_mem.sql
sqlt_s53240_sql_detail_active.html
sqlt_s53240_sql_monitor_active.html
sqlt_s53240_sql_monitor.html
sqlt_s53240_sql_monitor.txt
sqlt_s53240_10053_explain.trc
sqlt_s53240_10053_i1_c0_extract.trc
sqlt_s53240_xtract.log
sqltxtract.log
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_4571_s53240_10053.trc
V1123_ora_4571_s53240_10053_i1_c0.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.

*_s53240_*.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_s53240_tcb_*
README.txt

215187.1 sqlt_s53240_readme.html 2013-02-04/10:55:06