Oracle SQLTXPLAIN SQLT Tutorial Example Part 3

We executed XTRACT method of SQLTXPLAIN utility in the previous post and collected a big zip file in the output. The report is really exhaustive and will help a DBA to understand more about the system upon reading it. There will be three files main, lite and readme. The Main file is the one we will be looking into. In my case, the size of the main HTM file is about 500 kb. At a high level, let us see what are the sections of this report file;

It first displays a high level performance diagnostic information, which mentions a list of concerns identified by the health check module. These are the dominant ones that would need a review to fine tune and improve the performance.

This section mentions Type, Name, Observation and More..The More section proved to be highly useful as it even contained notes to Metalink section that explained how to fix the performance bottleneck. I would feel that some excellent work has been put behind this utility by Oracle to bring it this far.  As expected, we are told that our table BIG_TABLE lacks CBO statistics and no index has been sighted. (we did not create one). It also mentions on OPTIMIZER_DYNAMIC_SAMPLING, mentioning that it is set to a small value. As you may infer from above, a bird eye view information is available for us at the beginning of the report itself. We will move on and see what other sections are available in the report.

The SQL Text, SQL Identification and Environment Details follows next. SQL Identification section contains information like SQL ID, Hash Value, Signature for SQL Profiles. Environment contains a bunch of details that include Host Name, CPUs, RAC (True / False), Platform, Product Version, RDBMS Version, Language, Database Name and ID, Instance Name and ID, EBS, Siebel, User Name and ID, STATID. A sample screenshot of this section is provided below

All CBO Environment Details follows next. These include modified CBO parameter details and unmodified CBO parameter details. This is a huge list and we will not be providing them in this tutorial. CBO system statistics are presented after this step. These include:

a) Info System Statistics
b) Current System Statistics
c) Basis and Synthesized Values
d) System Statistics History

These statistics define the operating environment and ranges from CPU details to DB block details etc. DBMS_STATS follows next. Information provided in the report include DBMS_STATS.Preferences, Auto Task “auto optimizer stats collection”, Statistics for SYS Tables and DBMS_STATS Operations History. The performance report also contain Initialization Parameters section, which include Non-Default or Modified Session Parameters.[ Non-default or modified initialization parameters in effect for the session where SQLT XTRACT was executed ]. Historical values of non-default or modified initialization system-level parameters, captured by AWR during snapshots of the SQL statement being analyzed are also included as a part of the report. A huge report indeed! Hats off to the great work behind this tool.

Cursor Sharing specific performance parameters follows next in the report. A list of cursor information, with the SQL is also presented. If parallel processing is in effect, details specific to parallel processing are provided in the next section. These include PX instance groups, Active PX Servers, PX Processes, PX Sessions, PX System Statistics – Summary, PX Process System Statistics –Summary.

In the upcoming post, we will discuss the explain plan specific diagnostic information that gets loaded into the report.