SET ECHO OFF TERM ON HEA ON LIN 2000 PAGES 1000 TRIMS ON TIM OFF NUMF "";
SPO sqltconfig.lis;
REM
REM $Header: 215187.1 sqltconfig.sql 11.2.9.8 2009/08/31 csierra $
REM
REM Copyright (c) 2000-2009, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM   carlos.sierra@oracle.com
REM
REM SCRIPT
REM   sqltconfig.sql
REM
REM DESCRIPTION
REM   This script displays the SQLTXPLAIN configuration.
REM
REM PRE-REQUISITES
REM   1. Install SQLTXPLAIN tool as per instructions.txt provided
REM
REM PARAMETERS
REM   1. None
REM
REM EXECUTION
REM   1. Navigate to sqlt/utl directory
REM   2. Start SQL*Plus connecting as any user
REM   3. Execute script sqltconfig.sql
REM
REM EXAMPLE
REM   # cd sqlt/utl
REM   # sqlplus [any user]/[pwd]
REM   SQL> start sqltconfig.sql
REM
REM NOTES
REM   1. This script creates file sqltconfig.lis with SQLTXPLAIN configuration
REM
PRO
PRO To set a parameter:
PRO SQL> EXEC sqltxplain.sqlt$d.set_param('Name', 'Value');
PRO SQL> EXEC sqltxplain.sqlt$d.set_param('use_gv$sql_views', 'N');
PRO
PRO +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO |
PRO | SQLT Configuration
PRO |
PRO +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO |
PRO | To set any parameter below, connect as any user and execute:
PRO | SQL> EXEC sqltxplain.sqlt$d.set_param('Name', 'Value');
PRO |
PRO | Skip Predicates from Plan:
PRO | ~~~~~~~~~~~~~~~~~~~~~~~~~
PRO |   To workaround bug 6356566 an alter session command needs to
PRO |   be issued, which disables the display of access and filter
PRO |   predicates in plan table. In case of an ORA-07445, SQLT
PRO |   skips automatically these plan predicates by setting this
PRO |   parameter to Y
PRO |
PRO |   Name:skip_predicates_from_plan  Values:N/Y  Default:N
PRO |
PRO | Skip COUNT(*):
PRO | ~~~~~~~~~~~~~
PRO |   Section "Tables" of report includes actual COUNT(*) of all
PRO |   Tables accessed by SQL statement. This parameter enables or
PRO |   disables display of COUNT(*).
PRO |
PRO |   Name:skip_count_star  Values:N/Y  Default:N
PRO |
PRO | COUNT(*) Threshold:
PRO | ~~~~~~~~~~~~~~~~~~
PRO |   Tables with number of rows less than this threshold, will
PRO |   have a COUNT(*) as per parameter "Skip COUNT(*)". Set to 1M
PRO |   in order to avoid slow executions of the SQLT due to large
PRO |   tables.
PRO |
PRO |   Name:count_star_th  Values:0-999999999  Default:1000000
PRO |
PRO | Skip Segment Stats:
PRO | ~~~~~~~~~~~~~~~~~~
PRO |   Method XECUTE includes dependent segment statitics. The
PRO |   display of this report section is controlled by this
PRO |   parameter. Segment statistics are not linked to one single
PRO |   session, thus other sessions accessing same segments would
PRO |   affect the statistics presented.
PRO |
PRO |   Name:skip_segment_stats  Values:N/Y  Default:N
PRO |
PRO | Skip Session Stats:
PRO | ~~~~~~~~~~~~~~~~~~
PRO |   Session Statistics are included in method XECUTE. This
PRO |   parameter controls corresponding report section.
PRO |   Session stats snaphots are taken right before and after
PRO |   the SQL provided is executed, so the difference between
PRO |   them is the actual usage by the SQL.
PRO |
PRO |   Name:skip_session_stats  Values:N/Y  Default:N
PRO |
PRO | Skip Session Wait Events:
PRO | ~~~~~~~~~~~~~~~~~~~~~~~~
PRO |   Method XECUTE reports Session Wait Events using this
PRO |   parameter. Similar to session stats, these stats are
PRO |   measured right before and after the SQL provided is
PRO |   executed. The difference is presented, and it corresponds
PRO |   to the wait events caused by the SQL.
PRO |
PRO |   Name:skip_session_wait_events  Values:N/Y  Default:N
PRO |
PRO | Skip Constraints:
PRO | ~~~~~~~~~~~~~~~~
PRO |   This parameter controls the display of Constraints asociated
PRO |   to the set of Tables the SQL accesses
PRO |
PRO |   Name:skip_constraints  Values:N/Y  Default:N
PRO |
PRO | Skip Column Stats:
PRO | ~~~~~~~~~~~~~~~~~
PRO |   Disabling this parameter these report sections are skipped:
PRO |   Tables, Columns, Column Histograms, Table Partition Columns,
PRO |   Table Partition, Histograms, Table Subpartition Columns, and
PRO |   Table Subpartition Histograms.
PRO |
PRO |   Name:skip_column_stats  Values:N/Y  Default:N
PRO |
PRO | Skip DBA Statistics Views:
PRO | ~~~~~~~~~~~~~~~~~~~~~~~~~
PRO |   Allows to disable gathering of related records from DBA views
PRO |   DBA_TAB_STATISTICS, DBA_IND_STATISTICS and
PRO |   DBA_TAB_COL_STATISTICS for Fixed Objects, Tables, Indexes,
PRO |   Partitions and Subpartitions.
PRO |
PRO |   Name:skip_dba_statistics_views  Values:N/Y  Default:N
PRO |
PRO | Skip Histograms:
PRO | ~~~~~~~~~~~~~~~
PRO |   Indicates if these sections are included in report: Column
PRO |   Histograms, Table Partition Histograms, and Table
PRO |   Subpartition Histograms.
PRO |
PRO |   Name:skip_histograms  Values:N/Y  Default:N
PRO |
PRO | Skip Histograms History:
PRO | ~~~~~~~~~~~~~~~~~~~~~~~
PRO |   Columns for which their number of histogram endpoints are
PRO |   mutating, are selected to display the history of their
PRO |   histogram. This parameter allows to suppress them from main
PRO |   report.
PRO |
PRO |   Name:skip_histograms_history  Values:N/Y  Default:N
PRO |
PRO | Skip Partitions:
PRO | ~~~~~~~~~~~~~~~
PRO |   Controls whether or not Table and Index Partitions are
PRO |   included in report.
PRO |
PRO |   Name:skip_partitions  Values:N/Y  Default:N
PRO |
PRO | Skip Subpartitions:
PRO | ~~~~~~~~~~~~~~~~~~
PRO |   Controls whether or not Table and Index Subpartitions are
PRO |   included in report.
PRO |
PRO |   Name:skip_subpartitions  Values:N/Y  Default:Y
PRO |
PRO | Skip Partition Column Stats:
PRO | ~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO |   Disabling this parameter these report sections are skipped:
PRO |   Table Partition Columns, Table Partition Histograms, Table
PRO |   Subpartition Columns, and Table Subpartition Histograms.
PRO |
PRO |   Name:skip_part_column_stats  Values:Y/N  Default:Y
PRO |
PRO | Skip Subpartition Column Stats:
PRO | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO |   Disabling this parameter these report sections are skipped:
PRO |   Table Columns, Table Subpartition Columns, and Table
PRO |   Subpartition Histograms.
PRO |
PRO |   Name:skip_subpart_column_stats  Values:Y/N  Default:Y
PRO |
PRO | Skip Storage:
PRO | ~~~~~~~~~~~~
PRO |   This parameter controls if Tablespaces are listed in report.
PRO |
PRO |   Name:  Values:N/Y  Default:N
PRO |
PRO | Skip Metadata:
PRO | ~~~~~~~~~~~~~
PRO |   Section "Metadata" of report includes DDL to recreate all
PRO |   objects referenced by SQL provided. This parameter enables or
PRO |   disables the "Metadata" section.
PRO |
PRO |   Name:skip_storage  Values:N/Y  Default:N
PRO |
PRO | Skip Initialization Parameters:
PRO | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO |   For APPS instances, the list of required and recommended
PRO |   init.ora parameters is included in report. For non-APPS
PRO |   instances, the init.ora parameters with non-default values
PRO |   are included. This parameter controls this report section.
PRO |
PRO |   Name:skip_init_ora  Values:N/Y  Default:N
PRO |
PRO | Skip Stored Outlines:
PRO | ~~~~~~~~~~~~~~~~~~~
PRO |   This parameter enables or disables the display of Stored
PRO |   Outlines associated with analyzed SQL when method XTRACT is
PRO |   used.
PRO |
PRO |   Name:skip_stored_outlines  Values:N/Y  Default:N
PRO |
PRO | Skip Tuning Advisor:
PRO | ~~~~~~~~~~~~~~~~~~~
PRO |   Starting on 10g a Tuning Advisor DBMS_SQLTUNE is made
PRO |   available. This parameter enables or disables the execution
PRO |   of the Advisor for methods XTRACT and XECUTE.
PRO |
PRO |   Name:skip_tuning_advisor  Values:N/Y  Default:N
PRO |
PRO |   Note: Be aware that using DBMS_SQLTUNE requires a license for
PRO |         the  Oracle Tuning Pack.
PRO |
PRO | Skip Join Order:
PRO | ~~~~~~~~~~~~~~~
PRO |   Main and Compare reports can include a join-order section.
PRO |   This parameter controls the display of such section.
PRO |
PRO |   Name:skip_join_order  Values:N/Y  Default:N
PRO |
PRO | Skip 10053 Trace:
PRO | ~~~~~~~~~~~~~~~~
PRO |   XPLAIN, XTRACT and XECUTE can generated 10053 traces. Only
PRO |   XECUTE is 100% accurate. Since XTRACT and XPLAIN use
PRO |   EXPLAIN PLAN FOR <statement>, bind peeking could cause the
PRO |   10053 trace to show a different plan. This parameter allows
PRO |   to disable 10053 for all methods.
PRO |
PRO |   Name: skip_10053_trace  Values:N/Y  Default:N
PRO |
PRO | Skip 10046 Trace:
PRO | ~~~~~~~~~~~~~~~~
PRO |   XECUTE generates 10046, 10053 and other traces. This
PRO |   parameter allows to disable 10046 traces.
PRO |
PRO |   Name: skip_10046_trace  Values:N/Y  Default:N
PRO |
PRO | Skip Other Traces:
PRO | ~~~~~~~~~~~~~~~~~
PRO |   XECUTE generates 10046, 10053 and other traces. This
PRO |   parameter allows to disable other traces (10241, 10032,
PRO |   10033, 10104, 10730, 46049)
PRO |
PRO |   Name: skip_other_traces  Values:N/Y  Default:N
PRO |
PRO | DBMS_SQLTUNE Scope
PRO | ~~~~~~~~~~~~~~~~~~
PRO |   Defines the scope used by DBMS_SQLTUNE.CREATE_TUNING_TASK
PRO |   when the SQL Tuning Advisor is enabled.
PRO |
PRO |   Name:dbms_sqltune_scope  Values:COMPREHENSIVE/LIMITED
PRO |   Default:COMPREHENSIVE
PRO |
PRO |   Note: Be aware that using DBMS_SQLTUNE requires a license for
PRO |         the  Oracle Tuning Pack.
PRO |
PRO | DBMS_SQLTUNE Time Limit Secs
PRO | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO |   Restricts the time the DBMS_SQLTUNE.CREATE_TUNING_TASK is
PRO |   allowd to execute (in seconds).
PRO |
PRO |   Name:dbms_sqltune_time_limit  Values:30-86400  Default:1800
PRO |
PRO |   Note: Be aware that using DBMS_SQLTUNE requires a license for
PRO |         the  Oracle Tuning Pack.
PRO |
PRO | DBMS_SQLTUNE Report Level
PRO | ~~~~~~~~~~~~~~~~~~~~~~~~~
PRO |   Report level used by DBMS_SQLTUNE.REPORT_TUNING_TASK.
PRO |
PRO |   Name:dbms_sqltune_report_level  Values:ALL/TYPICAL/BASIC
PRO |   Default:ALL
PRO |
PRO |   Note: Be aware that using DBMS_SQLTUNE requires a license for
PRO |         the  Oracle Tuning Pack.
PRO |
PRO | Skip AWR Access:
PRO | ~~~~~~~~~~~~~~~
PRO |   Starting on 10g an Automatic Workload Repository is generated
PRO |   AWR contains performance metrics for expensive SQL. This
PRO |   parameter enables or disables AWR access by the XTRACT method
PRO |
PRO |   Name:skip_awr_access  Values:N/Y  Default:N
PRO |
PRO |   Note: Be aware that accessing AWR requires a license for
PRO |         the  Oracle Tuning Pack.
PRO |
PRO | Skip SQL Monitor:
PRO | ~~~~~~~~~~~~~~~~
PRO |   Starting on 11g it is possible to display performance metrics,
PRO |   like rows per operation in execution plan, even if SQL is
PRO |   still executing. This parameter enables or disables the
PRO |   display of such plan.
PRO |
PRO |   Name:skip_sql_monitor  Values:N/Y  Default:N
PRO |
PRO | Skip Test Case Builder:
PRO | ~~~~~~~~~~~~~~~~~~~~~~
PRO |   Starting on 11g a Test Case Builder in DBMS_SQLDIAG is made
PRO |   available. This parameter enables or disables the execution
PRO |   of the Test Case Builder for methods XTRACT and XECUTE.
PRO |
PRO |   Name:skip_test_case_builder  Values:N/Y  Default:N
PRO |
PRO | DBMS_SQLDIAG Time Limit Secs
PRO | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO |   Restricts the time the DBMS_SQLDIAG.EXPORT_SQL_TESTCASE is
PRO |   allowd to execute (in seconds).
PRO |
PRO |   Name:dbms_sqldiag_time_limit  Values:30-86400  Default:1800
PRO |
PRO | Restrict User:
PRO | ~~~~~~~~~~~~~
PRO |   This tool must be executed connected as the application user.
PRO |   Under some special cases you may need to execute as SYSTEM or
PRO |   SQLTXPLAIN. In that case you can remove the user restriction
PRO |   by using this parameter.
PRO |
PRO |   Name:restrict_user  Values:Y/N  Default:Y
PRO |
PRO | Gather CBO Stats Staging Objs (%)
PRO | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO |   Estimate percent for CBO stats gathering on staging tables.
PRO |   Zero means no gathering of stats.
PRO |
PRO |   Name:gather_cbo_stats  Values:0-100  Default:100 (%)
PRO |
PRO | Use GV$SQL RAC Views:
PRO | ~~~~~~~~~~~~~~~~~~~~
PRO |   This flag allows to disaable the use of GV$SQL RAC views in
PRO |   place of V$SQL views. Disable the GV$SQL views in case you
PRO |   hit any known bugs in these views causing ORA-600 or ORA-7445
PRO |
PRO |   Name:use_gv$sql_views  Values:Y/N  Default:Y
PRO |
PRO | Statistics Level for Linux:
PRO | ~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO |   Initialization parameter statistics_level when set to ALL, it
PRO |   may increase execution time of SQL in Linux. This parameter
PRO |   allows to set dynamically when method XECUTE is used.
PRO |
PRO |   Name:linux_statistics_level  Values:TYPICAL/ALL/BASIC
PRO |   Default: TYPICAL
PRO |
PRO | Create File History:
PRO | ~~~~~~~~~~~~~~~~~~~
PRO |   Stores into SQLT repository a copy of each report generated
PRO |   by SQLT.
PRO |
PRO |   Name:create_file_hist  Values:Y/N  Default:Y
PRO |
PRO | Index Compare by Columns:
PRO | ~~~~~~~~~~~~~~~~~~~~~~~~
PRO |   COMPARE method uses indexed columns as signature to compare.
PRO |   This parameter allows to use index name instead.
PRO |
PRO |   Name:index_compare_by_columns  Values:Y/N  Default:Y
PRO |
PRO | Repeat Rate for Titles:
PRO | ~~~~~~~~~~~~~~~~~~~~~~
PRO |   Specifies in lines how often titles are displayed in reports.
PRO |
PRO |   Name:title_repeat_rate  Values:10-1000  Default:30
PRO |
PRO +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO
PRO To set a parameter:
PRO SQL> EXEC sqltxplain.sqlt$d.set_param('Name', 'Value');
PRO SQL> EXEC sqltxplain.sqlt$d.set_param('use_gv$sql_views', 'N');
PRO
EXEC sqltxplain.sqlt$r.initialization;
SPO
COL library FOR A64 HEA 'Libraries';
SELECT object_type||' '||object_name||' ('||status||')' library
  FROM all_objects
 WHERE owner = 'SQLTXPLAIN'
   AND object_type IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
 ORDER BY
       object_type, object_name;
PRO
COL column_value FOR A128 HEA 'Directories';
SELECT column_value FROM TABLE(sqltxplain.sqlt$d.directories);
PRO
COL column_value FOR A128 HEA 'Tool Parameters';
SELECT column_value FROM TABLE(sqltxplain.sqlt$d.tool_parameters);
PRO
SPO OFF;
PRO
PRO
PRO Verify generated *.lis files for possible errors.
PRO
PRO NOTE:
PRO
PRO To set a parameter:
PRO SQL> EXEC sqltxplain.sqlt$d.set_param('Name', 'Value');
PRO SQL> EXEC sqltxplain.sqlt$d.set_param('use_gv$sql_views', 'N');
PRO
PRO If you need to contact Support regarding concerns during the
PRO installation of SQLTXPLAIN, be sure to supply all *.lis files
PRO generated
PRO
CL COL;
