Oracle SQLTXPLAIN SQLT Tutorial Example Part 4

In the previous tutorial, we covered the diagnostic information that gets generated out of SQLTXPLAIN utility for a given SQL. The report was highly comprehensive that I have end up writing another part of my blog dedicated to the contents of the report. It is important that you read and understand the report elements before taking a decision to use this utility. But, I'm sure that once you start using this, you will not feel giving up with this and going for another tool. The tuning report is that exhaustive and a great deal of work has been put up for this.

The next section of XTRACT report contains the SQL Plan Summary. It produces a list of plans found ordered by average elapsed time.This section contains the following useful information in the report;

Plan Hash Value1
Avg Elapsed Time in secs
Avg CPU Time in secs
Avg User IO Wait Time in secs
Avg Buffer Gets
Avg Disk Reads
Avg Direct Writes
Avg Rows Processed
Max Elaps Time in secs
Min Elaps Time in secs
Total Executions
Total Fetches
Total Version Count
Total Loads
Total Invalidations
Optimizer Cost
Estimated Cardinality
Estimated Time in secs
Plan Info
Plan Timestamp
First Load Time2
Last Load Time2
Source

This is followed by plan performance statistics per plan hash value. Execution plan specific information follows next. In our case, the report provided the information as shown in the screenshot below

XTRACT Utility Execution Plan Report

This is followed by Table statistics, Table physical properties. I could also find some interesting notes against table properties as our SQL involved a count(*) for tuning check;
(1) SELECT COUNT(*) performed in Table as per tool parameter "count_star_threshold" with current value of 1000000.
(2) CBO Statistics. 

Table constraints, column details and column specific details follow next. Column statistics, Column usage and column properties are also a part of this report.  Object specific information is also a part of the diagnostic report. This include;
Restricted list of objects related to the SQL being analyzed. Partitions and Subpartitions are excluded.
Further restricted up to 1000 rows as per tool parameter "r_rows_table_l". 

Finally object dependency, metadata and tablespace specific information find their place in the consolidated report. That is just a glimpse of what you can find in the report. For this single SQL I got a neatly connected 500 KB report, that was nearly complete in itself. It is not over yet. The report contains some more contents. But, we will first discuss the other available methods  to collect diagnostic information. They are XECUTE, XPLAIN and XTRXEC. We will then discuss how to compare the outputs we obtained.