Note:215187.1 SQLT 11.2.9.8 Aug 31, 2009 by Carlos Sierra

Enhanced Explain Plan and related diagnostic information for one SQL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQLT (SQLTXPLAIN) is a tool that inputs one SQL statement and outputs
a set of comprehensive diagnostic files for SQL performance analysis
and tuning.

SQLT inputs one SQL DML statement provided as one of these methods:
  1. XPLAIN: As a stand-alone SQL in a flat text file.
  2. XTRACT: As a memory-resident SQL, identified by its hash_value
     or sql_id.
  3. XECUTE: As a stand-alone script that contains one SQL, together
     with the declaration and values of its bind variables.
  4. XTRXEC: As a combination of XTRACT and XECUTE, this method gets
     a SQL from memory and executes both XTRACT and XECUTE.

The recommended methods are XECUTE and XTRACT. Try avoiding the
XPLAIN method since the Explain Plan generated by it may not be
accurate if your SQL contains bind variables.

Given one SQL according to one of the 4 supplied methods, the tool
provides the following:
  1. Comprehensive HTML report that includes an enhanced explain
     plan, details about the tables accessed by the SQL, their
     indexes, their columns, CBO statistics including Histograms,
     partitions and subpartitions if applicable, initialization
     parameters in effect, stored outlines associated to the SQL,
     SQL profiles if any, child plans, metrics of SQL execution
     performance, observations, etc.
  2. Metadata script with all the schema objects the SQL depends on.
     This metadata script, together with exported CBO statistics, is
     a valuable aid to assemble a test case on a similar system.
  3. Automatic packaging of all CBO statistics related to the SQL
     provided.
  4. An easy procedure to migrate packaged CBO statistics among
     similar systems for the purpose of reproduction of good/bad
     plans, thus performance (i.e. from prod to test, from test to
     prod, from customer to Oracle).
  5. Specialized 10046 and 10053 traces, with performance details as
     well as CBO tracing for detailed query analysis.
  6. A comparison report showing the differences of CBO statistics
     and initialization parameters associated to two prior executions
     of the SQLT from the same or distinct systems.
  7. A lite report, set of log files, a frame HTML report, etc.

SQLT provides the following benefits:
  1. Consistent snapshot of most commonly used pieces of information
     associated to a SQL analysis and tuning effort, like CBO
     statistics and performance dynamic views. For most of these
     objects, inconsistent snapshots of different objects would
     render the analysis inaccurate or inconclusive.
  2. Reduce the number of trips (thus overall time) between people
     involved in the SQL analysis and tuning. This is specially
     relevant in systems where the person doing the analysis is
     external to the organization or does not have full access to the
     system experiencing poor performance.
  3. Preserves CBO statistics and initialization parameters in effect
     at the time of the analysis, making it easier to reverse the
     environment to a known state. Further more, the associated CBO
     statistics can easily be migrated to a similar system in order
     to try to replicate the same plan good or bad, thus the query
     performance.
  4. Since the tool is installed into its own schema and makes use of
     temporary objects for most of its data structures, it is lite
     and moderate intrusive. The schema owner SQLTXPLAIN only gets
     granted a small subset of roles and attributes in order to
     generate SQL analysis results. This SQLTXPLAIN schema owner
     does not read application data itself.

The rest of this document is divided into the following sections:
  1. Upgrading SQLT
  2. Installing SQLT
  3. Uninstalling SQLT
  4. Known RDBMS Bugs that may affect SQLT
  5. Using SQLT
     5.1 XPLAIN method
     5.2 XTRACT method
     5.3 XECUTE method
     5.4 XTRXEC method
  6. Tool Configuration
  7. References and feedback

/********************************************************************/

1.  Upgrading SQLT
    ~~~~~~~~~~~~~~
    If you are using an old version of SQLT (11.0.1 or higher), you
    can upgrade to the latest version while preserving old SQLT data
    captured by your old version. This migrated SQLT data can be used
    later to restore CBO stats or to COMPARE old and new plans while
    using the latest version of SQLT.

    To upgrade from SQLT 11.0.1 (or higher) to the latest version of
    SQLT, carefully follow these 6 steps:

    1. Remove your old sqlt OS directory

    # rm -r sqlt

    2. Unzip latest sqlt.zip, creating new sqlt directory

    # unzip sqlt.zip

    3. Backup your old SQLT tables:

    # cd sqlt/install
    # exp sqltxplain tables=sqlt% file=sqlt.dmp statistics=none

    4. Install latest SQLT as per instructions in following section.

    5. Restore 5 SQLT tables from backup file sqlt.dmp created by
       step 3:

    # imp sqltxplain file=sqlt.dmp ignore=y tables='sqlt$_stattab'
    # imp sqltxplain file=sqlt.dmp ignore=y tables='sqlt$_statement'
    # imp sqltxplain file=sqlt.dmp ignore=y tables='sqlt$_plan_table'
    # imp sqltxplain file=sqlt.dmp ignore=y tables='sqlt$_parameter2'
    # imp sqltxplain file=sqlt.dmp ignore=y tables='sqlt$_indexes'

    6. Restore a SQLT sequence and recompile packages:

    # cd sqlt/install
    # sqlplus sqltxplain
    SQL> start sqreseq.sql

/********************************************************************/

2.  Installing SQLT
    ~~~~~~~~~~~~~~~
    Characteristics:
      o  This SQLT tool installs into its own schema SQLTXPLAIN
      o  It does not install any objects into application schemas
      o  Its footprint is very small
      o  The RDBMS version can be 9i, 10g or 11g
      o  The OS can be UNIX, LINUX or WINDOWS
      o  Source is not wrapped (SQL and PL/SQL)
      o  It can be installed in RAC systems
      o  It installs by executing just one script sqcreate.sql

    The set of installation scripts is driven by sqcreate.sql, and it
    generates several objects into schema SQLTXPLAIN: tables,
    indexes, sequences, views, grants and packages.

    During the installation you will be prompted for the SQLTXPLAIN
    user's password, as well as default and temporary tablespaces.
    If installing on 11g, password is case sensitive.

    The default tablespace will be used to create some SQLTXPLAIN
    objects (such as tables and indexes). The temporary tablespace
    will be used for large objects and sort-type activities.

    SQLT generates a metadata script containing all objects
    referenced by one given SQL, and it uses the DBMS_METADATA
    package. This package requires the application schema to be
    granted the SELECT_CATALOG_ROLE, thus the creation script asks
    for the schema name of the main application. For example, if
    installed on an APPS instance, you have to provide APPS as the
    main application schema. This main application schema owner is
    also granted ADMINISTER SQL TUNING SET and ADVISOR roles, which
    are needed by the SQL Tuning Advisor (from 10g upwards).
    If you need to grant another user: install/sqguser.sql

    You will be asked also for the host string to be used (if any).
    This string corresponds to the TNS alias. If you connect to that
    instance using a command like "connect appl/appl@PROD", the host
    string to be entered would be @PROD. If this host string is not
    used in your system, then leave blank when asked and just hit the
    enter key.

    SQLT uses two server directories, one is the udump where traces
    are created, and the second is an staging directory where SQLT
    generates its output files before copying them into the local
    SQL*Plus directory. By default both directories point to the
    server udump. If you need to change the pointer of the staging
    directory to something else than udump, you can use provided
    script sqltcdirs.slq located in the sqlt/utl directory

    Documentation for the sqcreate.sql script follows. Please read it
    before executing the script.

REM
REM SCRIPT
REM   sqcreate.sql
REM
REM DESCRIPTION
REM   This script installs the SQLT tool into its own schema.
REM
REM PRE-REQUISITES
REM   1. This script must be executed connected INTERNAL (SYS) as
REM      SYSDBA
REM   2. During the installation you will be asked to enter the
REM      follwing:
REM        o  SQLTXPLAIN password - Required and it has no default.
REM           Case sensitive on 11g.
REM        o  Optional Host String (TNS Alias) - Optional parameter
REM           to specify TNS alias connection including "@", for
REM           example @PROD. If not applicable, just hit enter when
REM           asked for this value.
REM        o  Schema name of main application that will use the
REM           SQLT tool. For example APPS. If there are more than one
REM           application schema owners, enter the main one
REM        o  SQLTXPLAIN default tablespace - You will be presented
REM           with a list, then you will have to enter one tablespace
REM           name from that list
REM        o  SQLTXPLAIN temporary tablespace - Similar as above
REM
REM PARAMETERS
REM   1. None inline. During the installation you will be asked for
REM      the values of the 5 parameters described under
REM      pre-requisites section above
REM
REM EXECUTION
REM   1. Navigate to sqlt/install directory
REM   2. Start SQL*Plus connecting INTERNAL (SYS) as SYSDBA
REM   3. Execute script sqcreate.sql and respond to values requested
REM
REM EXAMPLE
REM   # cd sqlt/install
REM   # sqlplus /nolog
REM   SQL> connect / as sysdba
REM   SQL> start sqcreate.sql
REM
REM NOTES
REM   1. For possible errors see *.lis files
REM

/********************************************************************/

3.  Uninstalling SQLT
    ~~~~~~~~~~~~~~~~~
    To uninstall SQLT version 10.0 or higher, connect INTERNAL as
    SYSDBA i.e. as SYS, and execute sqdrop.sql under sqlt/install
    directory.

    Documentation for the sqdrop.sql script follows. Please read it
    before executing the script.

REM
REM SCRIPT
REM   sqdrop.sql
REM
REM DESCRIPTION
REM   This script uninstalls a prior version of the SQLT tool dropping
REM   first existing SQLTXPLAIN objects, then the SQLTXPLAIN user
REM   itself.
REM
REM PRE-REQUISITES
REM   1. This script must be executed connected INTERNAL (SYS) as
REM      SYSDBA
REM
REM PARAMETERS
REM   1. None
REM
REM EXECUTION
REM   1. Navigate to sqlt/install directory
REM   2. Start SQL*Plus connecting INTERNAL (SYS) as SYSDBA
REM   3. Execute script sqdrop.sql
REM
REM EXAMPLE
REM   # cd sqlt/install
REM   # sqlplus /nolog
REM   SQL> connect / as sysdba
REM   SQL> start sqdrop.sql
REM
REM NOTES
REM   1. This script is executed automatically by sqcreate.sql
REM

/********************************************************************/

4.  Known RDBMS Bugs that may affect SQLT
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    SQLT uses several V$ and GV$ views in order to extract
    performance information about the SQL being analyzed. Some of the
    bugs below are known to have affected the use of SQLT in some
    systems. For versions affected and further details please query
    them in Metalink. Latest version of SQLT works arround most of
    them.

    If you get an ORA-600 or ORA-7475, please disable the use of
    GV$SQL RAC Views using tool configuration parameter:
    SQL> EXEC sqltxplain.sqlt$d.set_param('use_gv$sql_views', 'N');

    If you get an "ORA-07445" or "ORA-03114: not connected to ORACLE",
    then try this command before executing SQLT (ref 6356566):

    ALTER SESSION SET "_cursor_plan_unparse_enabled" = FALSE;

    4540971 Hash group by significantly over allocates memory and
            breaks V$SQL_XX statistics.
            This is not actually a memory leak but an excess amount
            of memory may be used for the hash group by operation.

    4566991 Hash group-by is not reported in v$sql_workarea_active
            and so sessions using HASH GROUP BY may use more memory
            than they are supposed to.

    4634662 ORA-600[kolaslGetLength-1] selecting from V$SQL on a RAC
            db with varying width character set.

    5017909 V$SQLAREA.SQL_FULLTEXT / V$SQL.SQL_FULLTEXT can contain
            an incorrect/corrupt SQL statement in a multibyte
            database. eg: If the 1000th byte of the SQL is a
            multibyte character the statement will show incorrectly
            in SQL_FULLTEXT.
            This can affect tools such as SQL tuning advisor which
            can fail with errors like ORA-904, ORA-911, ORA-1740.

    5692368 Poor performance possible from queries on library cache
            or shared cursors-related fixed views (GV$ / V$ views)
            even when the lookup is expected to be an index lookup
            based on hash value.
            Workaround: Modify the fixed view query so that hash
            value 0 is not explicitly passed in.

    6005525 Some SQL statements in [G]V$SQL / [G]V$SQLAREA can have a
            null PARSING_SCHEMA_NAME column.
            This can cause DBMS_SQLTUNE to returns an ORA-13644

    6356566 Selecting from [G]V$SQL_PLAN can leak memory resulting in
            poor performance and increased latch contention.
            Workaround: Setting "_cursor_plan_unparse_enabled" to
            FALSE can help avoid the problem but can incur some loss
            of information.

    7413836 ksedmp: internal or fatal error ORA-07445 SQLTXTRACT.SQL:
            ORA-07445: exception encountered: core dump [msqsub()+4]
            [SIGSEGV] [Address not mapped to object]
            [0x35B000000000070] [] []
            Current SQL statement for this session:
            SELECT * FROM SQLT$_GV$SQL_PLAN WHERE ((HASH_VALUE = :B4
            AND ADDRESS = :B3 ) OR SQL_ID = :B2 )
            AND CHILD_NUMBER = :B1 ORDER BY ID

    Some Notes that may affect SQLT
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    422983.1 Script to create user OUTLN in 10.2
             If you get error below when installing SQLT then you may
             need to read this Note.
             ERROR at line 1:
             ORA-00904: "H"."HINT_STRING": invalid identifier

/********************************************************************/

5.  Using SQLT
    ~~~~~~~~~~
    There are 4 different methods to use SQLT:

    5.1. XPLAIN - Given one SQL, it generates its explain plan
                  without executing the SQL

    5.2. XTRACT - Given an id for a known SQL, it extracts the SQL
                  and its actual execution plan from memory

    5.3. XECUTE - Given a script that contains one SQL and its bind
                  variables (declaration and values), it  executes
                  the SQL and extracts its execution plan form memory

    5.4. XTRXEC - Performs the XTRACT method followed by XECUTE.

    Use SQLT only after installing the tool as explained under the
    installation section. Review *.lis files generated during the
    installation in case of errors.

    The use of any of the 4 methods requires the user to connect as
    the application schema that originated the SQL to be analyzed.

    Use this tool on the system that reports the poor performance for
    the SQL to be analyzed. If a similar system performs well on the
    same SQL, install and use this tool on it as well, so the
    diagnostic files can be compared.

    Uncompress sqlt.zip file into a dedicated directory in the server
    or client where it will be used. Navigate to the run directory.
    Invoke SQL*Plus connecting as the application user that originated
    the SQL to be analyzed.

    All files generated by this tool will be generated directly into
    the SQL*Plus default directory, or into a server directory and
    copied automatically to the SQL*Plus default directory. Treat the
    set of generated files as a bundle.

    Before submitting the generated files to a requestor, do a sanity
    check of their content. If there were errors, please take
    corrective action and re-generate. Some errors imply the tool was
    not installed or the installation was incomplete.

/********************************************************************/

5.1 XPLAIN method
    ~~~~~~~~~~~~~
    This method of execution of the SQLT tool inputs one SQL statement
    with literals and without executing that SQL, it produces a set of
    diagnostic files.

    Method XPLAIN can accept a query that has bind variables, but it
    cannot do bind peeking. Thus, it is recommended to use XTRACT or
    XECUTE instead.

    Since XPLAIN method is blind to bind peeking, you may want to
    replace the binds in the SQL with corresponding literals in order
    to emulate bind peeking. Just be aware of implicit data types
    conversions.

    The file that contains the SQL should be carefully reviewed, and
    it must not have empty lines (lines with no text at all). The SQL
    should end with a semicolon ";". Column names and strings should
    not be split into the next line rendering the SQL incorrect.

    The SQL should be well formed, and without any modification. The
    only exception is data type obligatory modifications due to the
    fact that SQL*Plus does not recognize all types of binds
    variables. For example, in some cases where the SQL errors while
    explained, a "date_column" would have to be changed into
    "TO_DATE(date_column)". These cases don't happen often but they
    do occur.

    An example is provided as file sample_sql1.sql. Your SQL should
    follow this example. The filename can be any valid filename like
    bad.sql or invoice1.sql, etc.

    The main benefit of using this XPLAIN method over the other two,
    is that the SQL provided is not executed. The downside is that
    because this method uses the EXPLAIN PLAN command from SQL*Plus
    it does not do bind peeking, thus the plan generated may or may
    not be the same than if the SQL were actually executed. Thus, for
    better plan accuracy use the XTRACT or the XECUTE methods instead
    if at all possible.

    This XPLAIN method is implemented using the sqltxplain.sql script
    documented below. Documentation for the sqltxplain.sql script
    follows. Please read it before using the script.

REM
REM SCRIPT
REM   sqltxplain.sql
REM
REM DESCRIPTION
REM   This script generates a set of comprehensive reports with query
REM   tuning diagnostic details. It requires as input the filename of
REM   a plain text file that contains one valid SQL statement.
REM
REM PRE-REQUISITES
REM   1. Install the SQLT tool as per instructions.txt provided
REM   2. Create a text file that contains the ONE valid SQL statement
REM      to be explained. The SQL in this file must comply with the
REM      following:
REM        o  SQL must be valid (semantics and syntax)
REM        o  It can contain bind variables (like :b1)
REM        o  Should be the same SQL that performs poorly
REM           (no changes other than fixing implicit type conversion)
REM        o  The file must be placed into local SQL*Plus directory
REM        o  A sample file with one SQL is run/input/sample_sql1.sql
REM   3. The user that executes this method must comply with:
REM        o  Be the application schema owner that originated the SQL
REM        o  Must be granted those privileges shown in script
REM           sqlt/install/sqguser.sql
REM
REM PARAMETERS
REM   1. Name of file that contains SQL to be explained (required)
REM
REM EXECUTION
REM   1. Place your file with one SQL into sqlt/run/input directory
REM   2. Navigate to sqlt/run server directory
REM   3. Start SQL*Plus in server connecting as application user
REM   4. Execute script sqltxplain.sql passing directory path and
REM      name of file with SQL
REM   5. Provide all generated files to the requestor
REM
REM EXAMPLE
REM   # cd sqlt/run
REM   # sqlplus [apps user]/[apps pwd]
REM   SQL> start sqltxplain.sql [name of your file with one SQL]
REM   SQL> start sqltxplain.sql input/sample_sql1.sql  <== your file
REM
REM NOTES
REM   1. For a very lite version use sqltxplite.sql instead (10g+)
REM   2. For possible errors see sqltxplain.log
REM

/********************************************************************/

5.2 XTRACT method
    ~~~~~~~~~~~~~
    This method inputs the id of a SQL statement that has been
    executed recently and that still resides in memory (v$sql) or
    in the Automatic Workload Repository AWR (10g or 11g).

    The user of this method should be able to determine the id for
    the SQL to be extracted and analyzed. The id can be the SQL
    hash_value, sql_id, plan_hash_value, or any other piece of the
    SQL that identifies it uniquely. Most common is sql_id or the
    hash_value.

    The hash_value for example can be found in a SQL trace where the
    desired SQL is being parsed as show below (hv=1250854193).

    PARSING IN CURSOR #7 len=2008 ... hv=1250854193 ad='4cf494ec'
    select comps.component_item_id ,bill.organization_id ,bill.as...

    Statspack or AWR reports also display the sql_id or hash_value of
    expensive SQL.

    Another method to find these ids is by selecting them from v$sql
    as illustrated in examples below for 10g and 9i:

    SELECT /* 10g example */ substr(sql_text, 1, 80) sql_text,
           sql_id, hash_value, address, child_number, plan_hash_value
      FROM v$sql
     WHERE sql_text LIKE 'select comps.component_item_id ,bill.orga%'
       AND sql_text NOT LIKE '%10g example%';

    SELECT /* 9i example */ substr(sql_text, 1, 80) sql_text,
           hash_value, address, child_number, plan_hash_value
      FROM v$sql
     WHERE sql_text LIKE 'select comps.component_item_id ,bill.orga%'
       AND sql_text NOT LIKE '%9i example%';

    When using this XTRACT method, The SQL requested is extracted
    from memory, together with its actual execution plan and
    memory-resident statistics associated to it execution plan.

    Besides producing a comprehensive SQL analysis report, as the
    other methods do, this XTRACT method also invokes the SQL Tuning
    Advisor DBMS_SQLTUNE and includes in the main HTML report the
    findings and recommendations from the Advisor. This valuable
    option can be turned off if the system does not have a license
    for the Oracle Tuning Pack.

    XTRACT is a preferred method since it gets the actual execution
    plan directly from memory. That means the plan reported is more
    accurate since it was created using bind peeking when the SQL was
    executed. If initialization parameter STATISTICS_LEVEL was set to
    ALL, execution plan statistics are also available and presented
    in main report. If there are multiple live plans for the same SQL
    they are found in memory or AWR and reported as well.

    If the SQL to be analyzed is not resident in memory (or AWR) at
    the time of the analysis, the XTRACT method will quit. There are
    two options then:
      1. Execute the SQL from the application, find its hash_value or
         sql_id from this or prior executions, and retry the XTRACT
      2. Use the XECUTE method.

    This XTRACT method is implemented using the sqltxtract.sql script
    documented below. Documentation for the sqltxtract.sql script
    follows. Please read it before using the script.

REM
REM SCRIPT
REM   sqltxtract.sql
REM
REM DESCRIPTION
REM   This script generates a set of comprehensive reports with query
REM   tuning diagnostic details. It requires as input the hash_value
REM   or the sql_id of one memory-resident (gv$sql) SQL statement.
REM
REM PRE-REQUISITES
REM   1. Install the SQLT tool as per instructions.txt provided
REM   2. Determine the hash_value or sql_id of one SQL that still
REM      resides in memory (v$sql or gv$sql).
REM   3. To get plan statistics, STATISTICS_LEVEL must be set to ALL
REM      before the desired SQL is executed
REM   4. The user that executes this method must comply with:
REM        o  Be the application schema owner that originated the SQL
REM        o  Must be granted those privileges shown in script
REM           sqlt/install/sqguser.sql
REM
REM PARAMETERS
REM   1. Unique id of the SQL to be extracted (required)
REM      (hash_value or sql_id would work)
REM
REM EXECUTION
REM   1. Navigate to sqlt/run server directory
REM   2. Start SQL*Plus in server connecting as application user
REM   3. Execute script sqltxtract.sql passing hash_value or sql_id
REM      of one memory-resident SQL
REM   4. Provide all generated files to the requestor
REM
REM EXAMPLE
REM   # cd sqlt/run
REM   # sqlplus [apps user]/[apps pwd]
REM   SQL> start sqltxtract.sql [hash_value or sql_id for SQL]
REM   SQL> start sqltxtract.sql 2524255098  <== SQL hash_value
REM   SQL> start sqltxtract.sql 0w6uydn50g8cx  <== SQL sql_id
REM
REM NOTES
REM   1. For a very lite version use sqltxtlite.sql instead (10g+)
REM   2. For alternative parameters use sqltxtract2.sql instead
REM   3. For possible errors see sqltxtract.log
REM

/********************************************************************/

5.3 XECUTE method
    ~~~~~~~~~~~~~
    This method is very similar to XTRACT, but instead of providing
    the id of a known SQL, it takes as input a custom SCRIPT file
    that contains the actual SQL (see sample_script1.sql). Next, it
    executes the SQL, and produces a set of comprehensive diagnostic
    files using the XTRACT method as a subroutine.

    One thing this XECUTE method has in particular is that it
    produces a combined trace with events 10046 and 10053. This trace
    contains both, performance details, as well as a detailed trace
    of the CBO. The 10053 is generated for the actual execution of
    the SQL, and not just for the parse of the EXPLAIN PLAN FOR it,
    as the XPLAIN and XTRACT methods do. The 10046 is then processed
    through TKPROF and the Trace Analyzer for further analysis of
    WAITs

    Compared to XPLAIN and XTRACT, this XECUTE is the most
    comprehensive. It requires an extra effort although, which is the
    creation of the custom script whose name is passed as the input
    parameter. This is also a preferred method. In other words, if
    you can use any methods, try using XTRACT or XECUTE, else XPLAIN.

    This XECUTE method is implemented using the sqltxecute.sql script
    documented below. Documentation for the sqltxecute.sql script
    follows. Please read it before using the script.

REM
REM SCRIPT
REM   sqltxecute.sql
REM
REM DESCRIPTION
REM   This sqltxecute.sql takes as input the name of a SCRIPT file
REM   and proceeds to execute the one SQL contained in it, then it
REM   generates a set of comprehensive reports with query tuning
REM   diagnostic details.
REM
REM   The SCRIPT file whose name is provided as an inline execution
REM   parameter to sqltxecute.sql, is a custom script similar to
REM   provided sample_script1.sql. Besides the one SQL that will be
REM   executed and explained, it also contains the bind variables
REM   referenced by the SQL (declaration and value assignment).
REM
REM PRE-REQUISITES
REM   1. Install the SQLT tool as per instructions.txt provided
REM   2. Create a custom script using sample_script1.sql.
REM   3. The user that executes this method must comply with:
REM        o  Be the application schema owner that originated the SQL
REM        o  Must be granted those privileges shown in script
REM           sqlt/install/sqguser.sql
REM   4. Install Trace Analyzer (Note:224270.1) Highly recommended
REM      but not mandatory
REM
REM PARAMETERS
REM   1. Name of SCRIPT that has the SQL to be executed and analyzed
REM      (required)
REM
REM EXECUTION
REM   1. Place your file with one SQL into sqlt/run/input directory
REM   2. Navigate to sqlt/run server directory
REM   3. Start SQL*Plus in server connecting as application user
REM   4. Execute script sqltxecute.sql passing directory path and
REM      name of file with one SQL and its bind variables
REM   5. Provide all generated files to the requestor
REM
REM EXAMPLE
REM   # cd sqlt/run
REM   # sqlplus [apps user]/[apps pwd]
REM   SQL> start sqltxecute.sql [name of script with one SQL]
REM   SQL> start sqltxecute.sql input/sample_script1.sql  <== script
REM
REM NOTES
REM   1. It invokes Trace Analyzer (Note:224270.1) if previously
REM      installed (recommended)
REM   2. For possible errors see sqltxecute.log
REM

/********************************************************************/

5.4 XTRXEC method
    ~~~~~~~~~~~~~
    This method uses XTRACT and XECUTE combined. It inputs the id of
    a SQL in memory or in AWR as the XTRACT method does, then calls
    XTRACT which generates a script with the SQL and its binds, and
    calls last XECUTE passing this created script.

    The advantage of this method XTRXEC over stand-alone XTRACT and
    XECUTE is that it gets the ouput of both, so for example, the
    10053 generated is accurate (generated using bind peeking).

    The downside of this XTRXEC method is that since SQL only
    recognizes a very small subset of variable types (NUMBER, CHAR,
    CLOB and VARCHAR2), not all variables can be passed correctly,
    so the invoked XECUTE method may fail in some cases.

    This XTRXEC method is implemented using script sqltxtrxec.sql
    documented below. Please read it before using this method.

REM
REM SCRIPT
REM   sqltxtrxec.sql
REM
REM DESCRIPTION
REM   This script concatenates the execution of the methods XTRACT
REM   and XECUTE. It requires as input the hash_value or the sql_id
REM   of one memory-resident (gv$sql) SQL statement.
REM
REM PRE-REQUISITES
REM   1. Install the SQLT tool as per instructions.txt provided
REM   2. Determine the hash_value or sql_id of one SQL that still
REM      resides in memory (v$sql or gv$sql).
REM   3. To get plan statistics, STATISTICS_LEVEL must be set to ALL
REM      before the desired SQL is executed
REM   4. The user that executes this method must comply with:
REM        o  Be the application schema owner that originated the SQL
REM        o  Must be granted those privileges shown in script
REM           sqlt/install/sqguser.sql
REM
REM PARAMETERS
REM   1. Unique id of the SQL to be extracted (required)
REM      (hash_value or sql_id would work)
REM
REM EXECUTION
REM   1. Navigate to sqlt/run server directory
REM   2. Start SQL*Plus in server connecting as application user
REM   3. Execute script sqltxtrxec.sql passing hash_value or sql_id
REM      of one memory-resident SQL
REM   4. Provide all generated files to the requestor
REM
REM EXAMPLE
REM   # cd sqlt/run
REM   # sqlplus [apps user]/[apps pwd]
REM   SQL> start sqltxtrxec.sql [hash_value or sql_id for SQL]
REM   SQL> start sqltxtrxec.sql 2524255098  <== SQL hash_value
REM   SQL> start sqltxtrxec.sql 0w6uydn50g8cx  <== SQL sql_id
REM
REM NOTES
REM   1. XTRACT method generates a test case script which is then
REM      input to XECUTE.
REM   2. If XTRACT and XECUTE complete with no errors, a zip file
REM      with output of both is generated.
REM   3. If XECUTE exists by closing the session, review test case
REM      generated by XTRACT since it may need manual adjustments.
REM   4. This script is for 10g and 11g. If used on 9i and the SQL
REM      contains binds, it may fail on XECUTE phase.
REM

/********************************************************************/

6.  Tool Configuration
    ~~~~~~~~~~~~~~~~~~
    SQLT provides several thresholds and flags that control the
    configuration of the tool. Some reduce the size of the HTML
    comprehensive report by disabling some features.

    For an explanation of configuration parameters and the syntax to
    change their default seeded values, please refer to the
    sqlt/utl/sqltconfig.sql script.

    The main HTML report also includes a section that shows current
    values of tool configuration parameters and the syntax to change
    their (default) value.

/********************************************************************/

 7. References and feedback
    ~~~~~~~~~~~~~~~~~~~~~~~
    References
      o  Trace Analyzer - Note: 224270.1
         Interpreting Raw SQL Traces with Binds and/or Waits
         generated by EVENT 10046

    Feedback or errors during installation or use?
      o  Contact author Carlos Sierra by email:
         carlos.sierra@oracle.com

/********************************************************************/
