In Oracle, there are various ways, one can diagnose bad or offensive sql statements using tools like tkprof, AWR reports, trace files, event level tracing etc. We have covered a lot of those tools in our previous blog posts.
There is one another tool called sqltxplain developed by Carlos Sierra. For collecting detailed diagnostic information about specific single sql statement so that it can be tuned correctly. Apart from explain plan, it gives 10046 and 10053 event tracing, init.ora parameter settings, indexes on the objects used in the query and so many other important CBO statistics. More over it writes all these information into log files and trace files that can be shared with Oracle for further investigation and analysis. In this blog, we will briefly discuss how to use SQLTXPLAIN.
SQLTXPLAIN gets installed into separate schema called SQLTXPLAIN. It can be installed on RAC and on any version greater than 9i. When it is installed, it will ask for application schema so make sure that installation schema has SELECT_CATALOG_ROLE privilege. In order to run the script to create SQLTXPLAIN schema, one need to connect as sysdba.
There are three different ways or methods; we can collect data for a given SQL statement. For executing these methods one has to connect as the application schema against which the sql in question is being run. All the scripts are zipped into sqlt.zip file. Unzip it in your local directory and change the directory in which all sql resides. Let us start with methods.
XPLAIN: This method takes sql as an argument. Script to run is sqltxplain.sql. This method does not run the sql. If sql is using bind variable then don’t change the bind variables with value but keep it as is. We can save the sql in text file i.e. test.txt and pass this file as an input to the scripts. Syntax to execute this method is as follows.
SQL>start sqltxplain.sql test.txt
XTRACT: This method takes id of sql which is executed recently and still in memory. Using v$sql we can get the sql_id or hash_value of the sql statement and pass it as an input. SQL to run in this case is sqltxtract.sql. This method extracts sql from the memory along with runtime execution plan.
SQL>start sqltxtract.sql <hash_value> or
SQL>start sqltxtract.sql <sql_id>
XECUTE: This method is most comprehensive method. Instead of taking sql_id it takes actual sql statement as an input just like in XPLAIN method. It also generates the 10046 and 10053 trace as well. We have to make sure that bind values in the sql are declared and replaced with actual value. Following is the syntax, how we execute it. Replace test.txt with your file name.
SQL>start sqltxecute.sql test.txt
For complete details on how to install, uninstall, how to use SQLTXPLAIN, examples on how to create text files which contains sql statement and bugs affecting this utility, please visit metalink article 215187.1 written by Carlos Sierra. He is also the creator of this most useful script.