How I Install and Use SQL Query Performance Troubleshooting Tool SQLT (SQLTXPLAIN) in 6 Steps ?

Is your SQL Query suddenly started performing poor even no change in database ?

SQL Query has changed Execution Plan after Database Upgrade ?


SQL query is not giving result, earlier it was working fine ?


Query change plan after Statistic gathering ?


Execution Plan switch by a query or Unstable Plan  ?


You are also facing these challenges, then you are at right place. SLQT (SQLTXPLAIN) is the answer of all these questions. SQLT is a tool provided by Oracle to trouble shoot SQL query Performance related issues. SLQT (SQLTXPLAIN) is an HTML report which has each any every details about a single query like Performance Statistics, Execution Plans, SQL Statistics, Histograms etc. I would say SLQT is a one spot solution for all SQL query related issue and information. 

In this post I will explain how to Install and execute SQLT Tool.

How to Install SQLT (SQLTXPLAIN) ?

1. Download SLQT from Oracle Support: To get SQLT DBA has to download it from Oracle Support Article ID [ID 215187.1]. On this page you will find a link "10.2, 11.1 and 11.2 download" click on this link and SQLT will download on your local machine as a zip file. Extract zip file and Copy to Database Server at which you want to install SLQT.

2. Install SQLT: If you have already installed SQLT on system and wants to uninstall:

Command to uninstall SQLT.

# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqdrop.sql

For Installing SQLT use below steps:

# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqcreate.sql

During installation SQLTXPLAIN will ask for following inputs:

1. Optional Connect Identifier.
In some restricted-access systems you may need to specify a connect identifier like @PROD. If a connect identifier is not needed, enter nothing and just hit the "Enter" key. Entering nothing is the most common setup.

2. SQLTXPLAIN password.
Case sensitive in most systems.

3. SQLTXPLAIN Default Tablespace.
Select from a list of available permanent tablespaces which one should be used by SQLTXPLAIN for the SQLT repository. It must have more than 50MB of free space.

4. SQLTXPLAIN Temporary Tablespace.
Select from a list of available temporary tablespaces which one should be used by SQLTXPLAIN for volatile operations and objects.

5. Optional Application User.
This is the user that issued the SQL statement to be analyzed. For example, if this were an EBS system specify APPS, on Siebel you would specify SIEBEL and on People Soft SYSADM. You won't be asked to enter the password for this user. You can add additional SQLT users after the tool is installed, by granting them role SQLT_USER_ROLE.

6. Licensed Oracle Pack. (T, D or N)
You can specify T for Oracle Tuning, D for Oracle Diagnostic or N for none. If T or D is selected, SQLT may include licensed content within the diagnostics files it produces. Default is T. If N is selected, SQLT installs with limited functionality.

After providing these inputs DBA can find SQLT installed. SO, Now SQLTXPLAIN is ready to use.

Methods of using SQLTXPLAIN.

SQLT provides 7 main methods that generate diagnostics details for one SQL statement: XTRACT, XECUTE, XTRXEC, XTRSBY, XPLAIN, XPREXT and XPREXC. We will use few of them with are more useful for Database Administrator.

1. XTRACT Method: DBA can use this method to find sql query expecution detail. If you know sql id or Hash value for sql query. Remote DBA can find sql id from v$sqltext or from dba_hist_sqltext views.

Database administrator has to provide SQL ID/Hash value and SQLTEXPLAIN password as input for this method.

# cd sqlt/run

Connect to the user for which sql you want to get sqltxplain. Here I am using scott user

# sqlplus scott
SQL> START sqltxtract.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]
SQL> START sqltxtract.sql 0w6uydn5xtzse sqltxplain_password
SQL> START sqltxtract.sql 2524212345 sqltxplain_password

Here is the output when I execute this query for having sql id dr97nyf16pkhv and Password SQLTEXPLAIN
SQL> start sqltxtract.sql dr97nyf16pkhv SQLTEXPLAIN

PL/SQL procedure successfully completed.

Parameter 1:
SQL_ID or HASH_VALUE of the SQL to be extracted (required)
Paremeter 2:
SQLTXPLAIN password (required)

PL/SQL procedure successfully completed.

Value passed:
SQL_ID_OR_HASH_VALUE: "dr97nyf16pkhv"

PL/SQL procedure successfully completed.
***
*** NOTE:
*** If you get error below it means SQLTXPLAIN is not installed:
***   PLS-00201: identifier 'SQLTXADMIN.SQLT$A' must be declared.
** In such case look for errors in NN_*.log files created during install.

----------------------------------------------------
-----------------------------------------------------
-----------------------------------------------------

Archive:  sqlt_s16027_xtract_dr97nyf16pkhv.zip
  Length      Date    Time    Name
---------  ---------- -----   ----
   175896  06-17-2013 01:30   sqlt_s16027_10053_explain.trc
   106950  06-17-2013 01:30   sqlt_s16027_10053_i1_c0_extract.trc
     5521  06-17-2013 01:30   sqlt_s16027_driver.zip
    24477  06-17-2013 01:30   sqlt_s16027_lite.html
    37027  06-17-2013 01:31   sqlt_s16027_log.zip
   783005  06-17-2013 01:30   sqlt_s16027_main.html
   544718  06-17-2013 01:30   sqlt_s16027_opatch.zip
    16607  06-17-2013 01:30   sqlt_s16027_readme.html
   40968  06-17-2013 01:30   sqlt_s16027_sql_detail_active.html
   168449  06-17-2013 01:31   sqlt_s16027_sqldx.zip
     8078  06-17-2013 01:30   sqlt_s16027_sta_report_mem.txt
     1172  06-17-2013 01:30   sqlt_s16027_tc_script.sql
      352  06-17-2013 01:30   sqlt_s16027_tc_sql.sql
   584912  06-17-2013 01:30   sqlt_s16027_tc.zip
    27037  06-17-2013 01:30   sqlt_s16027_tcb.zip
    36356  06-17-2013 01:30   sqlt_s16027_tcx.zip
   149477  06-17-2013 01:30   sqlt_s16027_trc.zip
---------                     -------
 2711002                     17 files

File sqlt_s16027_xtract_dr97nyf16pkhv.zip for dr97nyf16pkhv has been created.

SQLTXTRACT completed.

SQL>! ls sqlt_s16027_xtract_dr97nyf16pkhv.zip

sqlt_s16027_xtract_dr97nyf16pkhv.zip
Database Administrator can download this file and start analyzing this.

2. XECUTE Method: This method provides more detailed analysis to DBA than XTRACT. As the name XECUTE means, it executes the SQL being analyzed, then it produces a set of diagnostics files. Its major drawback is that if the SQL being analyzed takes long to execute, this method will also take long.

As a rule of thumb, use this method only if the SQL takes less than 1hr to execute, else use XTRACT. If your SQL requires binds with data types not allowed by SQL*Plus, or if it uses collections, you may be restricted to embed your SQL into an anonymous PL/SQL block. In such case use sqlt/input/sample/plsql1.sql as an input example to this method.

It is mandatory to user .sql script to execute this method. This doesn't take sql ID or Hash value as an input.

Syntax:

# cd sqlt
# sqlplus user_name
SQL> START [path]sqltxecute.sql [path]scriptname [sqltxplain_password]
SQL> START run/sqltxecute.sql input/sample/script1.sql sqltxplain_password

Below is an sql script which I execute for Demo purpose.
$ cd /export/home/oracle/sqlt/sqlt/run

$ vi dba_objects.sql

$ cat dba_objects.sql

select * from dba_objects;

SQL>  start sqltxecute.sql dba_objects.sql SQLTEXPLAIN

PL/SQL procedure successfully completed.
Parameter 1:
SCRIPT name which contains SQL and its binds (required)

Paremeter 2:
SQLTXPLAIN password (required)

PL/SQL procedure successfully completed.

Value passed to sqltxecute:
SCRIPT_WITH_SQL: "dba_objects.sql"

PL/SQL procedure successfully completed.
----------------------------------------
----------------------------------------
----------------------------------------

Archive:  sqlt_s16030_xecute.zip
 Length      Date    Time    Name
---------  ---------- -----   ----
      30  06-17-2013 02:29   dba_objects.sql
  1575114  06-17-2013 02:31   sqlt_s16030_10046_10053_execute.trc
  1079054  06-17-2013 02:31   sqlt_s16030_10046_execute.trc
   470784  06-17-2013 02:31   sqlt_s16030_10053_execute.trc
   302376  06-17-2013 02:31   sqlt_s16030_10053_explain.trc
   182766  06-17-2013 02:32   sqlt_s16030_cell_state.zip
     6279  06-17-2013 02:31   sqlt_s16030_driver.zip
    54314  06-17-2013 02:31   sqlt_s16030_lite.html
    42545  06-17-2013 02:32   sqlt_s16030_log.zip
  1861315  06-17-2013 02:31   sqlt_s16030_main.html
   544718  06-17-2013 02:31   sqlt_s16030_opatch.zip
    18273  06-17-2013 02:31   sqlt_s16030_readme.html
    53284  06-17-2013 02:31   sqlt_s16030_sql_detail_active.html
   255836  06-17-2013 02:32   sqlt_s16030_sqldx.zip
    10240  06-17-2013 02:31   sqlt_s16030_sta_report_mem.txt
    10316  06-17-2013 02:31   sqlt_s16030_sta_report_txt.txt
   584456  06-17-2013 02:31   sqlt_s16030_tc.zip
   28377  06-17-2013 02:31   sqlt_s16030_tcb.zip
    35822  06-17-2013 02:31   sqlt_s16030_tcx.zip
  2179888  06-17-2013 02:31   sqlt_s16030_trc.zip
   715458  06-17-2013 02:31   sqlt_s16030_trca_e85520.html
    18850  06-17-2013 02:31   sqlt_s16030_trca_e85520.log
   388182  06-17-2013 02:31   sqlt_s16030_trca_e85520.txt
---------                     -------
 10418277                     23 files

File sqlt_s16030_xecute.zip for dba_objects.sql has been created.

SQLTXECUTE completed.

Now, Remote DBA can download sqlt_s16030_xecute.zip and analyze the output.

3 XTRXEC Method: This method combines the features of XTRACT and XECUTE for DBA. Actually, XTRXEC executes both methods serially. The XTRACT phase generates a script that contains the extracted SQL together with the binds declaration and assignment for an expensive plan found for the requested SQL statement. XTRXEC then executes the XECUTE phase using the script created by the first.

The selection of the values of the bind variables used by XTRACT to create the script is based on the peeked values at the moment the most expensive plans in memory were generated. Expensive plans are selected according to their average elapsed time.

Again, This method only need sql Id/Hash Value and sqltxplain_password. This method is most commonly and recommended method for SQL Performance related tuning issues.

Syntax:

# cd sqlt/run
# sqlplus scott
SQL> START sqltxtrxec.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]
SQL> START sqltxtrxec.sql 0w6uydn50g8cx sqltxplain_password
SQL> START sqltxtrxec.sql 2524255098 sqltxplain_password

Here is the test case...
#start sqltxtrxec.sql dr97nyf16pkhv SQLTEXPLAIN

 adding: sqlt_s16032_cell_state_begin_and_end.txt (deflated 97%)
  adding: sqlt_s16032_cell_state_begin.txt (deflated 94%)
  adding: sqlt_s16032_cell_state_end.txt (deflated 94%)
  adding: sqlt_s16032_xecute.log (deflated 80%)
  adding: sqltxhost.log (deflated 55%)
  adding: sqlt_s16032_10046_10053_execute.trc (deflated 79%)
  adding: sqlt_s16032_10053_explain.trc (deflated 78%)
  adding: sqlt_s16032_cell_state.zip (stored 0%)
  adding: sqlt_s16032_driver.zip (stored 0%)
  adding: sqlt_s16032_lite.html (deflated 83%)
  adding: sqlt_s16032_log.zip (stored 0%)
  adding: sqlt_s16032_main.html (deflated 87%)
  adding: sqlt_s16032_opatch.zip (stored 0%)
  adding: sqlt_s16032_readme.html (deflated 72%)
  adding: sqlt_s16032_sql_detail_active.html (deflated 90%)
  adding: sqlt_s16032_sqldx.zip (stored 0%)
  adding: sqlt_s16032_tc.zip (stored 0%)
  adding: sqlt_s16032_tcx.zip (stored 0%)
  adding: sqlt_s16032_trc.zip (stored 0%)
unzip:  cannot find or open sqlt_s16032, sqlt_s16032.zip or sqlt_s16032.ZIP.
File sqlt_s16032_xecute.zip for sqlt_s16031_tc_script.sql has been created.

SQLTXECUTE completed.

updating: sqlt_s16031_tc_script.sql (deflated 51%)
  adding: sqlt_s16031_xtract_dr97nyf16pkhv.zip (stored 0%)
  adding: sqlt_s16032_xecute.zip (stored 0%)
  adding: sqltxtrxec.log (deflated 76%)

PL/SQL procedure successfully completed.

SQLTXTRXEC completed.

Once DBA has collected SQLTEXPLAIN, He has to analyze these file. Soon, I will publish an article about how to Analyze SQLTEXPLAIN. Event tracing is another way to find out execution plan and other stats related to SQL queries.

During analysis, If you realize that the issue is not with sql query only, This is with whole database. In that case AWR Report and OS Watcher are the tool which can help Remote DBA to find RCA.

If you are installing and using SQLTEXPLAIN and facing some challenge, Share as comment on this post. Let's see if I can help you.

4 comments: