Oracle SQLTXPLAIN (SQLT) Tips and Tricks | Oracle Database Performance Tuning Tutorial -18

I will explain Oracle SQLTXPLAIN (SQLT) Tips and Tricks in this article. Let’s review the Oracle SQLTXPLAIN (SQLT) and SQLT Install

 

Oracle SQLTXPLAIN (SQLT) and SQLT Install

 

You can read the previous article of Oracle Database Performance Tuning Tutorial series with the following link.

What is the SQL Performance Analyzer and SPA Report in Oracle | Oracle Database Performance Tuning Tutorial -17

 

 

 

SQLTXPLAIN ( SQLT ) Install

SQLTXPLAIN is a tool developed by Carlos Sierra that used to diagnose SQL statements performing poorly , it is also known as SQLT. Now it is very popular tool for Query Performance Tuning.

SQLT tool connects to the database and collects execution plans, performance statistics, Cost-based Optimizer statistics, schema objects metadata, configuration parameters and etc..  that influence the performance of the SQL being analyzed.

 

Download the SQLTXPLAIN (SQLT) from this link.

Upload the database server, unzip it as follows.

 

[oracle@msdbadbadm01 ~]$ unzip sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip 
Archive: sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip
creating: sqlt/
creating: sqlt/doc/
inflating: sqlt/doc/sqlt_changes.html 
creating: sqlt/input/
creating: sqlt/input/sample/
inflating: sqlt/input/sample/db_link.sql 
creating: sqlt/input/sample/minidb/
inflating: sqlt/input/sample/minidb/create_order_entry_db.sql 
inflating: sqlt/input/sample/minidb/create_user.sql 
inflating: sqlt/input/sample/minidb/large_test.sql 
inflating: sqlt/input/sample/minidb/medium_test.sql 
inflating: sqlt/input/sample/minidb/small_test.sql 
inflating: sqlt/input/sample/plsql1.sql 
inflating: sqlt/input/sample/script1.sql 
inflating: sqlt/input/sample/script2.sql 
inflating: sqlt/input/sample/script3.sql 
inflating: sqlt/input/sample/script4.sql 
inflating: sqlt/input/sample/sql1.sql 
inflating: sqlt/input/sample/sql2.sql 
inflating: sqlt/input/sample/sql3.sql 
inflating: sqlt/input/sample/sql4.sql 
inflating: sqlt/input/sample/sql5.sql 
creating: sqlt/install/
extracting: sqlt/install/install.sql 
extracting: sqlt/install/setup.sql 
inflating: sqlt/install/sqcinternal.sql 
inflating: sqlt/install/sqcobj.sql 
inflating: sqlt/install/sqcommon1.sql 
inflating: sqlt/install/sqcparameters.sql 
inflating: sqlt/install/sqcpkg.sql 
inflating: sqlt/install/sqcpkga.pkb 
inflating: sqlt/install/sqcpkga.pks 
inflating: sqlt/install/sqcpkgc.pkb 
inflating: sqlt/install/sqcpkgc.pks 
inflating: sqlt/install/sqcpkgd.pkb 
inflating: sqlt/install/sqcpkgd.pks 
inflating: sqlt/install/sqcpkge.pkb 
inflating: sqlt/install/sqcpkge.pks 
inflating: sqlt/install/sqcpkgh.pkb 
inflating: sqlt/install/sqcpkgh.pks 
inflating: sqlt/install/sqcpkgi.pkb 
inflating: sqlt/install/sqcpkgi.pks 
inflating: sqlt/install/sqcpkgm.pkb 
inflating: sqlt/install/sqcpkgm.pks 
inflating: sqlt/install/sqcpkgr.pkb 
inflating: sqlt/install/sqcpkgr.pks 
inflating: sqlt/install/sqcpkgs.pkb 
inflating: sqlt/install/sqcpkgs.pks 
inflating: sqlt/install/sqcpkgt.pkb 
inflating: sqlt/install/sqcpkgt.pks 
inflating: sqlt/install/sqcreate.sql 
inflating: sqlt/install/sqcsilent.sql 
inflating: sqlt/install/sqcsilent2.sql 
inflating: sqlt/install/sqcusr.sql 
inflating: sqlt/install/sqcval0.sql 
inflating: sqlt/install/sqcval1.sql 
inflating: sqlt/install/sqcval2.sql 
inflating: sqlt/install/sqcval3.sql 
inflating: sqlt/install/sqcval4.sql 
inflating: sqlt/install/sqcval5.sql 
inflating: sqlt/install/sqcval6.sql 
inflating: sqlt/install/sqcvw.sql 
inflating: sqlt/install/sqdefparams.sql 
inflating: sqlt/install/sqdobj.sql 
inflating: sqlt/install/sqdold.sql 
inflating: sqlt/install/sqdrop.sql 
inflating: sqlt/install/sqdusr.sql 
inflating: sqlt/install/sqplcodetype.sql 
inflating: sqlt/install/sqseed.sql 
inflating: sqlt/install/squtltest.sql 
inflating: sqlt/install/tacobj.sql 
inflating: sqlt/install/tacommon1.sql 
inflating: sqlt/install/tacpkg.sql 
inflating: sqlt/install/tacpkge.pkb 
inflating: sqlt/install/tacpkge.pks 
inflating: sqlt/install/tacpkgg.pkb 
inflating: sqlt/install/tacpkgg.pks 
inflating: sqlt/install/tacpkgi.pkb 
inflating: sqlt/install/tacpkgi.pks 
inflating: sqlt/install/tacpkgp.pkb 
inflating: sqlt/install/tacpkgp.pks 
inflating: sqlt/install/tacpkgr.pkb 
inflating: sqlt/install/tacpkgr.pks 
inflating: sqlt/install/tacpkgt.pkb 
inflating: sqlt/install/tacpkgt.pks 
inflating: sqlt/install/tacpkgx.pkb 
inflating: sqlt/install/tacpkgx.pks 
inflating: sqlt/install/tacreate.sql 
inflating: sqlt/install/tacusr.sql 
inflating: sqlt/install/tacvw.sql 
inflating: sqlt/install/tadobj.sql 
inflating: sqlt/install/tadrop.sql 
inflating: sqlt/install/tadusr.sql 
inflating: sqlt/install/tasqdirset.sql 
inflating: sqlt/install/tautltest.sql 
extracting: sqlt/install/uninstall.sql 
creating: sqlt/run/
inflating: sqlt/run/sqldx.sql 
inflating: sqlt/run/sqlhc.sql 
inflating: sqlt/run/sqlhcxec.sql 
inflating: sqlt/run/sqltcommon1.sql 
inflating: sqlt/run/sqltcommon10.sql 
inflating: sqlt/run/sqltcommon11.sql 
inflating: sqlt/run/sqltcommon12.sql 
inflating: sqlt/run/sqltcommon13.sql 
inflating: sqlt/run/sqltcommon2.sql 
inflating: sqlt/run/sqltcommon3.sql 
inflating: sqlt/run/sqltcommon4.sql 
inflating: sqlt/run/sqltcommon5.sql 
inflating: sqlt/run/sqltcommon6.sql 
inflating: sqlt/run/sqltcommon7.sql 
inflating: sqlt/run/sqltcommon8.sql 
inflating: sqlt/run/sqltcommon9.sql 
inflating: sqlt/run/sqltcompare.sql 
inflating: sqlt/run/sqltgetfile.sql 
inflating: sqlt/run/sqltrcanlzr.sql 
inflating: sqlt/run/sqltrcaset.sql 
inflating: sqlt/run/sqltrcasplit.sql 
inflating: sqlt/run/sqltrcaxtr.sql 
inflating: sqlt/run/sqltxecute.sql 
inflating: sqlt/run/sqltxplain.sql 
inflating: sqlt/run/sqltxprexc.sql 
inflating: sqlt/run/sqltxprext.sql 
inflating: sqlt/run/sqltxtract.sql 
inflating: sqlt/run/sqltxtrone.sql 
inflating: sqlt/run/sqltxtrsby.sql 
inflating: sqlt/run/sqltxtrset.sql 
inflating: sqlt/run/sqltxtrxec.sql 
inflating: sqlt/sqlt_instructions.html 
inflating: sqlt/sqlt_instructions.txt 
creating: sqlt/utl/
inflating: sqlt/utl/10053.sql 
inflating: sqlt/utl/bde_chk_cbo.sql 
inflating: sqlt/utl/coe_gen_sql_patch.sql 
inflating: sqlt/utl/coe_gen_sql_profile.sql 
inflating: sqlt/utl/coe_load_sql_baseline.sql 
inflating: sqlt/utl/coe_load_sql_profile.sql 
inflating: sqlt/utl/coe_xfr_sql_profile.sql 
inflating: sqlt/utl/flush.sql 
inflating: sqlt/utl/flush_cursor.sql 
creating: sqlt/utl/mon/
inflating: sqlt/utl/mon/0_mon_readme.txt 
inflating: sqlt/utl/mon/1_mon_repository.sql 
inflating: sqlt/utl/mon/2_mon_capture.sql 
inflating: sqlt/utl/mon/3_mon_reports.sql 
inflating: sqlt/utl/mvhcdr.sql 
inflating: sqlt/utl/plan.sql 
inflating: sqlt/utl/profiler.sql 
inflating: sqlt/utl/pxhcdr.sql 
inflating: sqlt/utl/sel.sql 
inflating: sqlt/utl/sel_aux.sql 
creating: sqlt/utl/spm/
inflating: sqlt/utl/spm/alter_spb.sql 
inflating: sqlt/utl/spm/coe_load_sql_baseline.sql 
inflating: sqlt/utl/spm/create_spb_from_awr.sql 
inflating: sqlt/utl/spm/create_spb_from_cur.sql 
inflating: sqlt/utl/spm/create_spb_from_sts.sql 
inflating: sqlt/utl/spm/create_sts_from_awr.sql 
inflating: sqlt/utl/spm/create_sts_from_cur.sql 
inflating: sqlt/utl/spm/display_awr.sql 
inflating: sqlt/utl/spm/display_cur.sql 
inflating: sqlt/utl/spm/display_spb.sql 
inflating: sqlt/utl/spm/display_sts.sql 
inflating: sqlt/utl/spm/drop_cur.sql 
inflating: sqlt/utl/spm/drop_spb.sql 
inflating: sqlt/utl/spm/drop_sts.sql 
inflating: sqlt/utl/spm/evolve_spb.sql 
inflating: sqlt/utl/spm/imp_and_unpack_spb.sql 
inflating: sqlt/utl/spm/imp_and_unpack_sts.sql 
inflating: sqlt/utl/spm/pack_and_exp_spb.sql 
inflating: sqlt/utl/spm/pack_and_exp_sts.sql 
inflating: sqlt/utl/spm/readme.txt 
inflating: sqlt/utl/sqltcdirs.sql 
inflating: sqlt/utl/sqlthistfile.sql 
inflating: sqlt/utl/sqlthistpurge.sql 
inflating: sqlt/utl/sqltimp.sql 
inflating: sqlt/utl/sqltimpdict.sql 
inflating: sqlt/utl/sqltimpfo.sql 
inflating: sqlt/utl/sqltlite.sql 
inflating: sqlt/utl/sqltmain.sql 
inflating: sqlt/utl/sqltprofile.sql 
inflating: sqlt/utl/sqltq.sql 
inflating: sqlt/utl/sqltresetstatementid.sql 
inflating: sqlt/utl/sqltupgdbahistash.sql 
inflating: sqlt/utl/sqlt_parameters.sql 
creating: sqlt/utl/xgram/
inflating: sqlt/utl/xgram/sqlt_common1.sql 
inflating: sqlt/utl/xgram/sqlt_delete_column_hgrm.sql 
inflating: sqlt/utl/xgram/sqlt_delete_hgrm_bucket.sql 
inflating: sqlt/utl/xgram/sqlt_delete_schema_hgrm.sql 
inflating: sqlt/utl/xgram/sqlt_delete_table_hgrm.sql 
inflating: sqlt/utl/xgram/sqlt_display_column_stats.sql 
inflating: sqlt/utl/xgram/sqlt_insert_hgrm_bucket.sql 
inflating: sqlt/utl/xgram/sqlt_set_bucket_size.sql 
inflating: sqlt/utl/xgram/sqlt_set_column_hgrm.sql 
inflating: sqlt/utl/xgram/sqlt_set_min_max_values.sql 
creating: sqlt/utl/xhume/
inflating: sqlt/utl/xhume/create_xhume_script.sql 
inflating: sqlt/utl/xhume/drop_user_objects.sql 
inflating: sqlt/utl/xhume/install.sql 
inflating: sqlt/utl/xhume/readme.txt 
inflating: sqlt/utl/xhume/sqltdiffstats.sql 
inflating: sqlt/utl/xhume/sqltrstobj.sql 
inflating: sqlt/utl/xhume/uninstall.sql 
inflating: sqlt/utl/xhume/user_objects.sql 
inflating: sqlt/utl/xhume/xhume.pkb 
inflating: sqlt/utl/xhume/xhume.pks 
creating: sqlt/utl/xplore/
inflating: sqlt/utl/xplore/create_xplore_script.sql 
inflating: sqlt/utl/xplore/drop_sys_views.sql 
inflating: sqlt/utl/xplore/drop_user_objects.sql 
inflating: sqlt/utl/xplore/install.sql 
inflating: sqlt/utl/xplore/readme.txt 
inflating: sqlt/utl/xplore/sys_views.sql 
inflating: sqlt/utl/xplore/uninstall.sql 
inflating: sqlt/utl/xplore/user_objects.sql 
inflating: sqlt/utl/xplore/xplore.pkb 
inflating: sqlt/utl/xplore/xplore.pks 
[oracle@msdbadbadm01 ~]$ 
[oracle@msdbadbadm01 ~]$ 
[oracle@msdbadbadm01 ~]$ cd sqlt
[oracle@msdbadbadm01 sqlt]$ 
[oracle@msdbadbadm01 sqlt]$ ls -ltr
total 120
drwxr-xr-x 3 oracle oinstall 4096 Jun 5 10:37 input
drwxr-xr-x 2 oracle oinstall 4096 Jun 5 10:37 doc
drwxr-xr-x 2 oracle oinstall 4096 Jun 5 10:38 run
drwxr-xr-x 2 oracle oinstall 4096 Jun 5 10:38 install
drwxr-xr-x 7 oracle oinstall 4096 Jun 5 10:38 utl
-rw-r--r-- 1 oracle oinstall 58466 Jun 5 10:41 sqlt_instructions.html
-rw-r--r-- 1 oracle oinstall 39368 Jun 5 10:43 sqlt_instructions.txt
[oracle@msdbadbadm01 sqlt]$

 

 

Run SQLT (  SQLTXPLAIN )

Now install it on your database as follows.

[oracle@msdbadbadm01 install]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 24 15:29:52 2020

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> START sqcreate.sql
adding: 200824152912_01_sqcreate.log (deflated 84%)
zip warning: name not matched: *_ta*.log

zip error: Nothing to do! (SQLT_installation_logs_archive.zip)

Ignore errors from here until @@@@@ marker as this is to test for NATIVE PLSQL Code Type
@@@@ marker . You may ignore prior errors about NATIVE PLSQL Code Type
old 1: ALTER SESSION SET PLSQL_CODE_TYPE = &&plsql_code_type
new 1: ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE

Session altered.


Specify optional Connect Identifier (as per Oracle Net)
Include "@" symbol, ie. @PROD
If not applicable, enter nothing and hit the "Enter" key.
You *MUST* provide a connect identifier when installing
SQLT in a Pluggable Database in 12c
This connect identifier is only used while exporting SQLT
repository everytime you execute one of the main methods.

Optional Connect Identifier (ie: @PROD): @PROD


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Define SQLTXPLAIN password (hidden and case sensitive).





Password for user SQLTXPLAIN: 
Re-enter password:


PL/SQL procedure successfully completed.


The next step is to choose the tablespaces to be used by SQLTXPLAIN

The Tablespace name is case sensitive.

Do you want to see the free space of each tablespace [YES]
or is it ok just to show the list of tablespace [NO]?

Type YES or NO [Default NO]:

... please wait

TABLESPACE FREE_SPACE_MB
------------------------------ -------------
------------------
------------------

USERS

16 rows selected.


Specify PERMANENT tablespace to be used by SQLTXPLAIN.

Tablespace name is case sensitive.

Default tablespace [UNKNOWN]: USERS

PL/SQL procedure successfully completed.

... please wait

TABLESPACE
------------------------------
TEMP
TEMP1


Specify TEMPORARY tablespace to be used by SQLTXPLAIN.

Tablespace name is case sensitive.

Temporary tablespace [UNKNOWN]: TEMP

PL/SQL procedure successfully completed.


The main application user of SQLT is the schema
owner that issued the SQL to be analyzed.
For example, on an EBS application you would
enter APPS.
You will not be asked to enter its password.
To add more SQLT users after this installation
is completed simply grant them the SQLT_USER_ROLE
role.

Main application user of SQLT: MehmetSalih

PL/SQL procedure successfully completed.


SQLT can make extensive use of licensed features
provided by the Oracle Diagnostic and the Oracle
Tuning Packs, including SQL Tuning Advisor (STA),
SQL Monitoring and Automatic Workload Repository
(AWR).
To enable or disable access to these features
from the SQLT tool enter one of the following
values when asked:

"T" if you have license for Diagnostic and Tuning
"D" if you have license only for Oracle Diagnostic
"N" if you do not have these two licenses

Oracle Pack license [T]: T

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

TADOBJ completed.

SQDOLD completed. Ignore errors from this script
adding: 200824153003_01_sqcreate.log (deflated 90%)








SQCUSR completed. Some errors are expected.

Procedure created.

No errors.
adding: 200824153053_02_sqcusr.log (deflated 85%)

TAUTLTEST completed.
adding: 200824153055_09_tautltest.log (deflated 60%)








SQUTLTEST completed.
adding: 200824153055_10_squtltest.log (deflated 61%)








no rows selected

TACOBJ completed.
adding: 200824153055_03_tacobj.log (deflated 86%)

SQL> PRO Dropping Libraries for TRCA
Dropping Libraries for TRCA
SQL> SET TERM OFF;
tool_repository_schema: "SQLTXPLAIN"
tool_administer_schema: "SQLTXADMIN"
role_name: "SQLT_USER_ROLE"
Creating Procedures
Creating Package Specs TRCA$G
No errors.
Creating Package Specs TRCA$P
No errors.
Creating Package Specs TRCA$T
No errors.
Creating Package Specs TRCA$I
No errors.
Creating Package Specs TRCA$E
No errors.
Creating Package Specs TRCA$R
No errors.
Creating Package Specs TRCA$X
No errors.
Creating Views
Creating Package Body TRCA$G
No errors.
Creating Package Body TRCA$P
No errors.
Creating Package Body TRCA$T
No errors.
Creating Package Body TRCA$I
No errors.
Creating Package Body TRCA$E
No errors.
Creating Package Body TRCA$R
No errors.
Creating Package Body TRCA$X
No errors.
Creating Grants on Libraries

Tool Version
----------------
19.1.200226

Install Date
----------------
20200824

Directories
--------------------------------------------------------------------------------------------------------------------------------
TRCA$INPUT1(VALID) /u01/app/oracle/diag/rdbms/msdba/msdba1/trace
TRCA$INPUT2(VALID) /u01/app/oracle/diag/rdbms/msdba/msdba1/trace
TRCA$STAGE(VALID) /u01/app/oracle/diag/rdbms/msdba/msdba1/trace
user_dump_dest
background_dump_dest

Libraries
--------------------------------------------------------------------------------------------------------------------------------
VALID PACKAGE TRCA$I /* $Header: 224270.1 tacpkgi.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$E /* $Header: 224270.1 tacpkge.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$G /* $Header: 224270.1 tacpkgg.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$P /* $Header: 224270.1 tacpkgp.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$R /* $Header: 224270.1 tacpkgr.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$T /* $Header: 224270.1 tacpkgt.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE TRCA$X /* $Header: 224270.1 tacpkgx.pks 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE BODY TRCA$I /* $Header: 224270.1 tacpkgi.pkb 12.1.14 2015/12/06 carlos.sierra mauro.pagano [email protected]
$ */

VALID PACKAGE BODY TRCA$E /* $Header: 224270.1 tacpkge.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE BODY TRCA$G /* $Header: 224270.1 tacpkgg.pkb 12.1.14 2015/12/06 carlos.sierra mauro.pagano [email protected]
$ */

VALID PACKAGE BODY TRCA$P /* $Header: 224270.1 tacpkgp.pkb 11.4.5.8 2013/05/10 carlos.sierra $ */
VALID PACKAGE BODY TRCA$R /* $Header: 224270.1 tacpkgr.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */
VALID PACKAGE BODY TRCA$T /* $Header: 224270.1 tacpkgt.pkb 12.1.160429 2016/04/29 carlos.sierra [email protected]$ */
VALID PACKAGE BODY TRCA$X /* $Header: 224270.1 tacpkgx.pkb 11.4.5.0 2012/11/21 carlos.sierra $ */
TACPKG completed.

PL/SQL procedure successfully completed.

adding: 200824153059_04_tacpkg.log (deflated 80%)








SQCOBJ completed. Some errors are expected.
adding: 200824153109_05_sqcobj.log (deflated 93%)


SQLT can make extensive use of licensed features
provided by the Oracle Diagnostic and the Oracle
Tuning Packs, including SQL Tuning Advisor (STA),
SQL Monitoring, Automatic Workload Repository
(AWR) and SQL Tuning Sets (STS).
To enable or disable access to these features
from the SQLT tool enter one of the following
values when asked:

"T" if you have license for Diagnostic and Tuning
"D" if you have license only for Oracle Diagnostic
"N" if you do not have these two licenses

pack_license: "T"
enable_tuning_pack_access

PL/SQL procedure successfully completed.


Specify optional Connect Identifier (as per Oracle Net)
Include "@" symbol, ie. @PROD
If not applicable, enter nothing and hit the "Enter" key

connect_identifier: "@PROD"

PL/SQL procedure successfully completed.


Table truncated.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Procedure created.

No errors.

Table truncated.


PL/SQL procedure successfully completed.


Procedure dropped.


Commit complete.


SQSEED completed.
adding: 200824153152_07_sqseed.log (deflated 80%)

... dropping packages for SQLT
... creating package specs for SQLT$A
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$a;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$C
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$c;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$D
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$d;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$E
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$e;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$H
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$h;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$I
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$i;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$M
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$m;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$R
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$r;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$S
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$s;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package specs for SQLT$T
SQL> SHOW ERRORS PACKAGE &&tool_administer_schema..sqlt$t;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating views
... creating package body for SQLT$A
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$a;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$C
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$c;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$D
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$d;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$E
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$e;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$H
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$h;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$I
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$i;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$M
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$m;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$R
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$r;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$S
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$s;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;
... creating package body for SQLT$T
SQL> SHOW ERRORS PACKAGE BODY &&tool_administer_schema..sqlt$t;
No errors.
SQL> --
SQL> SET TERM ON ECHO OFF;

Creating Grants on Packages ...


LIBRARIES
---------------------------------------------------------------------------
VALID PACKAGE 12.2.171004 SQLT$A [email protected]
VALID PACKAGE 12.1.10 SQLT$C carlos.sierra
VALID PACKAGE 12.1.11 SQLT$D carlos.sierra
VALID PACKAGE 12.1.10 SQLT$E carlos.sierra
VALID PACKAGE 12.1.10 SQLT$H carlos.sierra
VALID PACKAGE 12.1.10 SQLT$I carlos.sierra
VALID PACKAGE 12.1.10 SQLT$M carlos.sierra
VALID PACKAGE 12.1.160429 SQLT$R carlos.sierra
VALID PACKAGE 12.1.10 SQLT$S carlos.sierra
VALID PACKAGE 12.1.10 SQLT$T carlos.sierra
VALID PACKAGE 11.4.5.0 TRCA$E carlos.sierra
VALID PACKAGE 11.4.5.0 TRCA$G carlos.sierra
VALID PACKAGE 11.4.5.0 TRCA$I carlos.sierra
VALID PACKAGE 11.4.5.0 TRCA$P carlos.sierra
VALID PACKAGE 11.4.5.0 TRCA$R carlos.sierra
VALID PACKAGE 11.4.5.0 TRCA$T carlos.sierra
VALID PACKAGE 11.4.5.0 TRCA$X carlos.sierra
VALID PACKAGE BODY 19.0.180825 SQLT$A [email protected]
VALID PACKAGE BODY 12.1.14 SQLT$C carlos.sierra
VALID PACKAGE BODY 19.0.190426 SQLT$D [email protected]
VALID PACKAGE BODY 12.1.160429 SQLT$E carlos.sierra

LIBRARIES
---------------------------------------------------------------------------
VALID PACKAGE BODY 19.0.190110 SQLT$H [email protected]
VALID PACKAGE BODY 12.2.171004 SQLT$I [email protected]
VALID PACKAGE BODY 19.1.200129 SQLT$M [email protected]
VALID PACKAGE BODY 12.2.181004 SQLT$R [email protected]
VALID PACKAGE BODY 12.1.10 SQLT$S carlos.sierra
VALID PACKAGE BODY 12.2.171004 SQLT$T [email protected]
VALID PACKAGE BODY 11.4.5.0 TRCA$E carlos.sierra
VALID PACKAGE BODY 12.1.14 TRCA$G carlos.sierra
VALID PACKAGE BODY 12.1.14 TRCA$I carlos.sierra
VALID PACKAGE BODY 11.4.5.8 TRCA$P carlos.sierra
VALID PACKAGE BODY 11.4.5.0 TRCA$R carlos.sierra
VALID PACKAGE BODY 12.1.160429 TRCA$T carlos.sierra
VALID PACKAGE BODY 11.4.5.0 TRCA$X carlos.sierra

Deleting CBO statistics for SQLTXPLAIN objects ...

15:32:18 0 sqlt$a: -> delete_sqltxplain_stats
15:32:21 3 sqlt$a: <- delete_sqltxplain_stats

PL/SQL procedure successfully completed.


SQCPKG completed.
adding: 200824153152_08_sqcpkg.log (deflated 79%)








TAUTLTEST completed.
adding: 200824153221_09_tautltest.log (deflated 59%)








SQUTLTEST completed.
adding: 200824153221_10_squtltest.log (deflated 59%)


SQLT users must be granted SQLT_USER_ROLE before using this tool.

SQCREATE completed. Installation completed successfully.
SQL>

 

SQLTXPLAIN ( SQLT ) Methods 

There are 7 main methods of SQLTXPLAIN ( SQLT ) as follows.

1. XTRACT
2. XECUTE
3. XTRXEC
4. XPLAIN
5. XTRSBY
6. XPREXT
7. XPREXC

 

 

XTRACT, XECUTE, XTRXEC, XTRSBY, XPREXT and XPREXC methods can handle bind variables and understand about bind peeking, but XPLAIN does not. This is because XPLAIN is based on the EXPLAIN PLAN FOR command which is blind to bind peeking. So you should avoid using XPLAIN if possible.

Besides the bind peeking limitation on XPLAIN all 7 main methods provide enough diagnostics details to make an initial assessment of a SQL performing poorly or generating wrong results. If the SQL still resides in memory or in the Automatic Workload Repository (AWR) use then XTRACT or XTRXEC, else use XECUTE. For Data Guard or standby read-only databases use XTRSBY. Use XPLAIN only if the other methods are not feasible. XPREXT and XPREXC are similar to XTRACT and XECUTE but they disable some SQLT features in order to improve SQLT performance.

 

SQLT methods usage are as follows.

 

XTRACT Method

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

XECUTE Method

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

XTRXEC Method

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

 

XTRSBY Method

# cd sqlt/run
# sqlplus apps
SQL> START sqltxtrsby.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password] [DB_LINK]
SQL> START sqltxtrsby.sql 0w6uydn50g8cx sqltxplain_password V1123
SQL> START sqltxtrsby.sql 2524255098 sqltxplain_password v1123

XPLAIN Method

# cd sqlt
# sqlplus apps
SQL> START [path]sqltxplain.sql [path]filename [sqltxplain_password]
SQL> START run/sqltxplain.sql input/sample/sql1.sql sqltxplain_password

XPREXT Method

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

XPREXC Method

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

 

 

I will explain how to perform SQL Performance Tuning with SQLT methods in the next article.

 

 

Next post link about Performance Tuning Tutorial is as follows.

Performance Tuning with SQLTXPLAIN (SQLT) with Examples in Oracle | Oracle Database Performance Tuning Tutorial -19

 

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then Click this link.