SQLTXPLAN generate comprehensive info of sql statements

Note:215187.1 SQLT 11.3.1.1 Dec 3, 2009 by Carlos Sierra

SQLT 11.3.1.1

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 within a text file.

2. XTRACT: As a memory or AWR 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 assignment of its bind variables.

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 and bind peeking is
enabled in your system.

Given one SQL according to one of the 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. A set of scripts and files to produce a test case if one were
needed.

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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9466564/viewspace-623763/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9466564/viewspace-623763/