How I Install and Use SQL Query Performance Troubleshooting Tool SQLT (SQLTXPLAIN) in 6 Steps ?
Execution Plan switch by a query or Unstable Plan ?
How to Install SQLT (SQLTXPLAIN) ?
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.
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)
After providing these inputs DBA can find SQLT installed. SO, Now SQLTXPLAIN is ready to use.
Methods of using SQLTXPLAIN.
# 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
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.
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...
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.zipDatabase 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.
