Oracle SQLTXPLAIN SQLT Tutorial Example Part 5

We discussed XTRACT method of SQLTXPLAIN utility with an example earlier. We discussed the elements of the report produced by this tool (with the output we got). It was comprehensive enough with recommendations. In this post, we will see XPLAIN method of this tool, and see how to make use of that. I read the documentation for this tool and found that XPLAIN requires an SQL input from an SQL file. So, let us put an SQL into an SQL file and try to invoke this method. We will use the SQL provided below for demonstration purposes;

SELECT COUNT(*) FROM BIG_TABLE WHERE FIRST_CHAR='I';

Now, I will put this SQL into a SQL file, demo.sql and copy it to sqlt/run folder so that I can provide the path to the tool during execution easily. To use XPLAIN method, use the following command at run time

Execute XPLAIN method - Example

When I executed this, I got some notes from this tool, which could be worth mentioning here

WARNING:
~~~~~~~
You are about to use SQLT XPLAIN method.

If you were requested by Oracle Support to use XTRACT or XECUTE, then do not use this XPLAIN method.

Be aware that XPLAIN method cannot perform bind peeking.Replacing bind variables with literal values does not guarantee the generated plan to be the same than the one produced by XTRACT or XECUTE. Thus the plan generated by XPLAIN might not be useful to progress your issue.

If you still want to proceed with the XPLAIN method,enter the keyword XPLAIN when asked for "sqlt_method".

Enter value for sqlt_method:

Now, you have to enter XPLAIN as the method and the system will start getting the diagnostic details for your SQL. At one point, you will be prompted for the SQLTXPLAIN user password. This is required to export statistical information gathered on your SQL. It generated a big report, similar to XTRACT. All the report specific files were bundled into a zip file again. A snapshot of the report contents is provided below;(good thing, the report also contains your input file)

The report contents are almost the same as the case of XTRACT (refer related post below). So, when should I use XPLAIN method?



The help documentation mentions that this is based on EXPLAIN PLAN FOR command. It does not execute the SQL you provide, but provides you the statistics based on the information available. It is easy to use, the plan should be accurate if there are no bind variables on your SQL or bind peeking is disabled. The Plan may not be accurate if you have bind variables on your SQL or if bind peeking is enabled. I also came to know that it generates less diagnostic information when compared with XTRACT method. Further, as the SQL is not executed it only shows estimated number of rows that can be returned and not actuals. (in my case the estimated cardinality column in the report was blank)

More to follow..