19. Using SQLT to Boost Query Performance
Oracle DBAs can be divided into two broad groups: those of us who are lucky enough to have installed, configured, and leveraged the SQLT utility to help improve SQL query performance and those of us who have never even heard of the SQLT utility. Carlos Sierra created SQLT while working for Oracle many years ago, well before many of today’s Oracle database performance analysis tools that are built into more current versions of the database kernel—SQL Tuning Advisor, SQL Access Advisor, SQL Performance Analyzer, and SQL Monitor—even existed.
Because it focuses on the best practices for tuning SQL statements effectively and with minimum effort, SQLT is also a great introduction for a “newbie” DBA to learn how to isolate, diagnose, and tune just about any SQL statement to improve database application performance. Best of all, SQLT captures and displays an abundance of information regarding the SQL statement identified by its sql_id because it generates a detailed report containing information about the related optimizer statistics, initialization parameters, object metadata, execution plans, and much more.
This chapter focuses on several topics, including installing SQLT, using the XTRACT and XECUTE methods, and leveraging other SQLT methods. Finally, it provides a real-world example for using SQLT to improve query performance.
We only briefly review the necessary steps to install the SQLT utility; further, this chapter does not attempt to explain the details of how SQLT actually works, as that is beyond the scope of this chapter. For a deeper look into how to leverage the full capacity of SQLT for performance tuning, be sure to review the several excellent My Oracle Support (MOS) documents listed at the end of this chapter.
1. Download the sqlt.zip file based on the instructions in MOS Note 215187.1, “All about the SQLT diagnostic tool.”
2. Navigate to the selected installation directory and run the sqcreate.sql script as follows:
$> cd sqlt/install
$> sqlplus / as sysdba
SQL> START sqcreate.sql
zip warning: name not matched: *_sq*.log
zip error: Nothing to do! (SQLT_installation_logs_archive.zip)
zip warning: name not matched: *_ta*.log
zip error: Nothing to do! (SQLT_installation_logs_archive.zip)
PL/SQL procedure successfully completed.
. . .
(many lines of output suppressed for sake of brevity)
. . .
SQUTLTEST completed.
adding: 150523163243_10_squtltest.log (deflated 59%)
SQLT users must be granted SQLT_USER_ROLE before using this tool.
SQCREATE completed. Installation completed successfully.
3. Provide the necessary information about the appropriate connect identifier, SQLTXPLAIN password, default tablespace, and temporary tablespace during the execution of sqcreate.sql.
For example, if SQLT is being installed within an Oracle E-Business Suite (EBS) environment, it will be necessary to provide the APPS username. This is optional, and it is the schema owner of the statement’s SQL_ID that will be analyzed.
4. If queries are going to be executed by more than one database user, grant the SQLT_USER_ROLE to those users to permit those accounts to leverage SQLT.
5. When the installation script prompts, indicate whether the database is currently licensed for either the Oracle SQL (T)uning Pack, (D)iagnostic Pack, or (N)either Diagnostic nor Tuning packs.
SQLT can also be installed silently by modifying the contents of sqdefparams.sql and then running script sqcsilent.sql as follows:
$> cd sqlt/install
$> sqlplus / as sysdba
SQL> START sqdefparams.sql
SQL> START sqcsilent.sql
Note that it’s also possible to supply the desired parameters within a single command string, as follows:
$> cd sqlt/install
$> sqlplus / as sysdba
SQL> START sqcsilent2.sql '' sqltxplain USERS TEMP '' T
Finally, when an Oracle DBA is prohibited from installing anything additional on an existing database because of specific regulatory restrictions or tightened security arrangements, consider using the sqlhc.sql utility, which invokes the Oracle database Health Check script that can also collect information about a specific sql_id, albeit with a limited diagnostic range.
Using the XTRACT Method
SQLT has numerous methods that can be leveraged, but this chapter focuses on just two key methods: XTRACT and XECUTE. The main difference between the two methods is that, as the name suggests, the XECUTE method actually executes the statement identified by sql_id, and the XTRACT method does not execute the statement. As we will see in the next section, the XECUTE script generates much more information about the statement.
The point here is to show how simple it is to create a report that contains all the information that we need to tune a specific query. The report created as output of these methods of SQLT contains easy-to-use, crucial information and also contains some suggestions about what to do in some specific cases to improve the statement’s performance—for example, regathering stale statistics, defragmenting fragmented tables or indexes, and dealing with known bugs.
Here is an example of how with one simple command we can generate a complete SQLT report using the XTRACT method:
1. Make sure the database’s STATISTIC_LEVEL initialization parameter has been set to ALL. This setting ensures that SQLT will collect even deeper information about the statement.
2. Connect to a SQL*Plus session using an appropriate application database user. For example, in EBS, this would be the APPS user.
3. Run the SQLT script using either the sql_id or the SQL hash value of the statement that is experiencing performance problems. These identifiers are easily obtained—say, for example, either by an AWR report or an ASH report:
# cd sqlt
# sqlplus apps
SQL> START [path]sqltxecute.sql [path]scriptname [sqltxplain_password]
SQL> START run/sqltxecute.sql input/sample/script1.sql sqltxplain_password
At the end, a .ZIP file will be created containing all the information available about the statement corresponding to the supplied sql_id or SQL hash value. Opening the sqlt_sXXX_main.html file will display something like what is shown in Figure 19.1.
The resulting output contains an almost overwhelming amount of information divided into specific areas like Global, Plans, Tables, Plan Control, Cursors, SQL Tuning Advisors, Objects, and SQL Execution. Navigating between report sections is as easy as clicking on the desired HTML link in the report. Without this script, it could take significant time to gather this level of detailed information about a single statement, but the XTRACT method dramatically reduces that effort.
Using the XECUTE Method
As mentioned previously, the XECUTE method generates a much more detailed report because it actually executes the SQL statement that corresponds to the specified sql_id. Therefore, if the SQL statement takes more than one hour to complete during its execution, be aware that this may have a deleterious effect on the corresponding database environment, especially if the statement is known to consume large amounts of CPU or I/O bandwidth.
Executing the XECUTE method is almost as simple as executing the XTRACT method but requires slightly different steps as shown:
1. Prepare a separate file containing the SQL statement itself.
2. If the statement being analyzed uses bind variables, declare them in the same file as the SQL statement. The following example demonstrates how to accomplish this:
-- execute sqlt xecute as sh passing script name
-- cd sqlt
-- #sqlplus sh
-- SQL> start run/sqltxecute.sql input/sample/script1.sql
REM Optional ALTER SESSION commands
REM ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--ALTER SESSION SET statistics_level = ALL;
REM Optional Binds
REM ~~~~~~~~~~~~~~
VAR b1 NUMBER;
EXEC :b1 := 10;
REM SQL statement to be executed
REM ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT /*+ GATHER_PLAN_STATISTICS MONITOR BIND_AWARE */
/* ^^unique_id */
s1.channel_id,
SUM(p.prod_list_price) price
FROM products p,
sales s1,
sales s2
WHERE s1.cust_id = :b1
AND s1.prod_id = p.prod_id
AND s1.time_id = s2.time_id
GROUP BY
s1.channel_id;
/
/
REM Notes:
REM 1. SQL must contain token: /* ^^unique_id */
REM 2. Do not replace ^^unique_id with your own tag.
REM 3. SQL may contain CBO Hints, like:
REM /*+ gather_plan_statistics monitor bind_aware */
Note
The sqlt.zip file contains several examples of how to accomplish bind variable declarations.
For SQL statements that contain data manipulation language (DML) commands, the XECUTE method will create a SAVEPOINT prior to the statement’s execution; at the end of the transaction, XECUTE issues a rollback to return the database’s state prior to the SAVEPOINT.
3. Run the XECUTE script, passing the text file created that contains the SQL statement and it’s bind variables declared in it:
# cd sqlt
# sqlplus apps
SQL> START [path]sqltxecute.sql [path]scriptname [sqltxplain_password]
16:43:56 APPS@BWEBSPR5 > START run/sqltxecute.sql input/input_pp.sql f2cb2w08
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
Parameter 1:
SCRIPT name which contains SQL and its binds (required)
. . .
When using the XECUTE method, you will be prompted for the SQLXPLAIN password that was provided during the installation of SQLT. Also note that if the XECUTE method is being invoked by an application user, that user account must have been GRANTed the SQLT_USER_ROLE.
4. Monitor the progress of the XECUTE method by querying the contents of view SQLTXADMIN.SQLT$_LOG_V, as the following example demonstrates:
SQL> SELECT * FROM SQLTXADMIN.sqlt$_log_v;
TIME LINE
-------- ------------------------------------------------------------
16:49:11 sqlt$a: tool version: 12.1.11
16:49:11 sqlt$a: script version: 12.1.11
16:49:11 sqlt$a: -> common_initialization
16:49:11 sqlt$a: ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ".,"
16:49:11 sqlt$a: ALTER SESSION SET NLS_SORT = BINARY
. . .
16:49:37 sqlt$d: task name = "sqlt_s93766_mem"
16:49:37 sqlt$d: <- create_tuning_task_text
16:49:37 sqlt$d: -> collect_tuning_sets_mem
16:49:37 sqlt$d: sqlt$_gv$sql_plan: 1518346075
181 rows selected.
SQL>
Once XECUTE has completed, it will produce a complete and detailed report, as shown in Figure 19.2.
Just as with the XTRACT method, the resulting XECUTE report output contains an almost overwhelming amount of information, including significant recommendations for improving the performance of the targeted SQL statement.
Leveraging Other SQLT Methods
SQLT offers several other methods that could be more valuable than XTRACT and XECUTE depending on the specific use case. A brief explanation for each of these methods follows:
XTRXEC method: If a statement has bind variables and an Oracle DBA wants to identify the best variables to analyze in terms of which one(s) influenced the worst performance for the statement, she would use the XTRXEC method. This method will first run XTRACT to determine the best bind variables to use in the report; afterwards, the DBA can use the results from XTRXEC to analyze the statement with the XECUTE method to get the complete picture about the statement’s execution with those bind variables.
XTRSBY method: This SQLT method makes it possible to analyze the statement by executing it on a physical standby database, thus limiting the impact on the primary production database’s performance. Using XTRSBY requires the creation of a database link on the primary database so that SQLT can write information into SQLT tables on that database over the link.
XPLAIN method: This method is essentially the same as executing an EXPLAIN PLAN FOR command for the statement; however, be aware that it cannot view the statement’s bind variables.
XPREXC method: This method is a faster way to run the XECUTE method because it disables some of XECUTE’s features; it’s therefore faster and will save at least some resource utilization.
COMPARE method: This method is useful if a SQL statement is executing in two different database environments, but it is running much faster on one database than on the other. COMPARE does require the importation of the SQLT repository from one of the database environments to the other environment so that the comparison can be executed; it’s also possible to export and then import the SQLT repositories from two different database environments to a third environment just for the purposes of running the comparison.
TRCANLZR method: This method essentially offers the same functionality as the Oracle Trace Analyzer utility; however, this method offers the capability to analyze several traces concurrently when the traces resulted from a parallel execution.
TRCAXTR method: The TRCAXTR method essentially works the same as TRCANLZR, except that it will call the XTRACT method for the worst-performing SQL statement found in the Trace Analyzer output.
TRCASPLIT method: If a trace is created using EVENT 10046, the TRCASPLIT method can be used to analyze the output. This method creates two different files: one with the actual lines generated by EVENT 10046 and one with the lines not generated by setting the event. This makes it much simpler to identify just the key output from that SQL trace method.
XTRSET method: This method is excellent for locating the worst-executing SQL statement in an AWR report and then executing XTRACT for that particular statement.
A Real-World Example
Since the goal of this book is to help Oracle DBAs find and fix almost any kind of problem in their database environments, let’s turn our attention to how we can leverage the main methods of SQLT in a real-world environment. The following example demonstrates how simple it is to quickly tune a poorly performing query in a database in just a few minutes without requiring the intervention of an Oracle performance DBA:
1. If the offending query was submitted by an application user, find its sql_id using the GV$SQLTEXT or DBA_HIST_SQLTEXT view:
select sql_id
from DBA_HIST_SQLTEXT
where sql_text like '%where DATE_VALUE > sysdate -180% ';
Alternatively, to retrieve the sql_id for a poorly performing query from an Automatic Workload Repository (AWR) snapshot period, run the AWR report first to isolate the query (or use the XTRSET method explained earlier).
2. After obtaining the sql_id, run the XECUTE method for that sql_id, as follows:
$> START run/sqltxecute.sql input/input_pp.sql f2cb2w08
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
3. Once the XECUTE method completes, review the resulting output’s Plan Hash Values report to find which possible execution plan hash value(s) may relate to the reported performance issues. As the report sample in Figure 19.3 shows, there is one execution plan that offers superior performance.
4. To repair this query’s poor performance, create a SQL Profile to give the query optimizer the ability to choose the better plan (in Oracle Database 11g, recall that a SQL plan baseline could also be created):
–- This creates the script below that must be run to create the profile:
START coe_xfr_sql_profile.sql gqmv2hr133bjv 163726467
–-This script finally creates the profile:
@coe_xfr_sql_profile_gqmv2hr133bjv_4271579545.sql
My Oracle Support Reference Documents
The following My Oracle Support (MOS) documents offer excellent reference information on how to best leverage the SQLT utility and its related feature set:
215187.1: “All about the SQLT diagnostic tool”
1454160.1: “FAQ: SQLT (SQLTXPLAIN) frequently asked questions”
1465741.1: “How to use SQLT (SQLTXPLAIN) to create a testcase containing application data”
1470811.1: “How to use SQLT (SQLTXPLAIN) to create a testcase without row data”
1614107.1: “SQLT usage instructions”
1614201.1: “SQLT changes”
1670677.1: “FAQ: Common SQLT (SQLTXPLAIN) runtime/installation errors”
1683772.1: “How to collect standard diagnostic information using SQLT for SQL issues”
1922234.1: “SQLT main report: Usage suggestions”
1951018.1: “SQLT utility and partitioned tables”
Summary
This chapter showed how easy is to tune query performance using SQLT. The methods that SQLT employs were briefly explained, and a real-world test case demonstrated how to leverage this utility with just a few simple steps. Without SQLT, an Oracle DBA would most likely have to manually run several individual queries and pursue several erroneous false leads before isolating the actual root cause (or causes) of the statement’s poor performance.