Tuning Oracle with SQLT – Part # 2
Here’s the 2nd part to the series covering SQLT as an important tool for SQL and Oracle Tuning for Oracle DBA’s.
In the last post of the series, we covered how to install this simple tool. Today we would see how we can run reports from it. There are various options of report available in SQLT tool but there are 2 of them which are used widely, and prove to be helpful in most of the situations.
Now that we’ve have installed it, we would go back to the run directory under the SQLT Installation directory. In Linux/Windows we can use the cd command to hover into the run directory. Here we find many scripts provided by this tool for our use.
Sample SQLT Report for an SQL Statement
What we are about to run is the SQLT Xtract tool provided as part of SQLT. This tool would require the SQL_ID or HASH_VALUE of an SQL we would want to investigate upon. To do so, we do the following:
- Go to the run directory of SQLT Installation directory using the cd command.
- Log on to SQL*Plus: sqlplus / as sysdba
- Now, we would run our query which we would investigate. Let’s take an example query to see the objects in the database:
- select object_name from dba_objects;
- Now, we query the V$SQL dynamic view to find the SQL_ID.
- select sql_id from v$sql where sql_text like ‘select object_name from dba_objects%’;
- Once we have the SQL_ID in place, we would run the SQLT Xtract Report for this SQL using the below command:
- @sqltxtract <SQL_ID>;
- When we run the above script it would ask for the SQLT Username and Password which we have specified during the installation of SQLT tool. You can quickly refer the installation steps here.
- Once we provide the same, it runs successfully, and generates a ZIP file as output with all the relevant files which we would check now!
- Once the report is completed, it would show a message on the SQL*Plus prompt which says:
- SQLTXTRACT completed
- Now we exit from the SQL*Plus session.
That’s it! We have now run the SQLT Xtract Report/Tool and would like to see what it delivers to us regarding to this SQL.
Once we have unzipped it, open the file named sqlt_s <nnnnn> _main.html. The symbols “nnnnn” represent numbers created to make all SQLT reports unique on your machine.
The 2 most important SQLT Reports ?
Like the SQLT Xtract Report, we have another useful report which is SQLT Xecute Report. Let’s quick glance through the differences between them before going any further.
SQLT XTRACT is the easiest report to create because it does not require the execution of the SQL at the time of the report generation. The report can be collected after the statement has been executed. SQLTXECUTE, on the other hand, executes the SQL statement and thus has better run-time information and access to the actual rows returned. SQLTXECUTE will get you more information, but it is not always possible to use this method, perhaps because you are in a production environment or perhaps the SQL statement is currently taking three days to run, which is why you are investigating this in the first place.
We will look at both SQLTXECUTE and SQLTXTRACT report (and other SQLT options also).
Sample SQLT XTRACT Output
A sample of the SQLTXECUTE report looks like the below:
- SQLT XTRACT Output – Main Page – Top Section
What does each of these links mean, and what information can they be able to provide us for better understanding of our SQL Statement and its execution? We’d see it in the next post. Stay tuned!