SQLT Diagnostic Tool
Tool that helps to diagnose SQL statements performing poorly
SQLT Overview
SQLTXPLAIN,
also known as SQLT, is a tool provided by Oracle Server Technologies
Center of Expertise - ST CoE. SQLT inputs one SQL statement and outputs a
set of diagnostics files. These files are commonly used to diagnose SQL
statements performing poorly. SQLT connects to the database and
collects execution plans, Cost-based Optimizer CBO statistics, schema
objects metadata, performance statistics, configuration parameters, and
similar elements that influence the performance of the SQL being
analyzed.Install Instructions :
Download sqlt_latest.zip to the server and unzip its contents into the staging area .
During the installation you will be asked to enter values for these parameters:
- Optional Connect Identifier (mandatory when installing in a Pluggable Database) In some restricted-access systems you may need to specify a connect identifier like
SQLTXPLAINpassword.
Case sensitive in most systems.SQLTXPLAINDefault Tablespace.
Select from a list of available permanent tablespaces which one should be used by SQLTXPLAINTemporary Tablespace.
Select from a list of available temporary tablespaces which one should be used by - Optional Application User. This is the user that issued the SQL statement to be analyzed. For example, if this were an EBS system specify
- Licensed Oracle Pack. (T, D or N) You can specify
@PROD. If a connect identifier is not needed, enter nothing and just hit the "Enter" key. Entering nothing is the most common setup.The Connect Identifier is a mandatory parameter when installing SQLT in a Pluggable Database.
SQLTXPLAIN for the SQLT repository. It must have more than 50MB of free space.SQLTXPLAIN for volatile operations and objects.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.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.
/home/oracle/staging/sqlt/install=> sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 17 10:52:15 2018
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, OLAP, Advanced Analytics and Real Application Testing options
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)
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):
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
------------------------------ -------------
MGMT_AD4J_TS
MGMT_ECM_DEPOT_TS
MGMT_TABLESPACE
SQLT_DATA
STATSPACK_DATA
USERS
6 rows selected.
Specify PERMANENT tablespace to be used by SQLTXPLAIN.
Tablespace name is case sensitive.
Default tablespace [UNKNOWN]: SQLT_DATA
PL/SQL procedure successfully completed.
... please wait
TABLESPACE
------------------------------
TEMP
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: SYSMAN
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]: N
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: 180117105436_01_sqcreate.log (deflated 90%)
SQCUSR completed. Some errors are expected.
Procedure created.
No errors.
adding: 180117105913_02_sqcusr.log (deflated 85%)
TAUTLTEST completed.
adding: 180117105917_09_tautltest.log (deflated 60%)
SQUTLTEST completed.
adding: 180117105917_10_squtltest.log (deflated 61%)
no rows selected
TACOBJ completed.
adding: 180117105917_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
----------------
12.2.171004
Install Date
----------------
20180117
Directories
--------------------------------------------------------------------------------------------------------------------------------
TRCA$INPUT1(VALID) /app/oracle/diag/rdbms/cld1prd/cld1prd/trace
TRCA$INPUT2(VALID) /app/oracle/diag/rdbms/cld1prd/cld1prd/trace
TRCA$STAGE(VALID) /app/oracle/diag/rdbms/cld1prd/cld1prd/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 abel.macias@oracle.com
$ */
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 abel.macias@oracle.com
$ */
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 abel.macias@oracle.com$ */
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: 180117105932_04_tacpkg.log (deflated 80%)
SQCOBJ completed. Some errors are expected.
adding: 180117105955_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: "N"
disable_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: ""
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: 180117110217_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> --
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 OSQLT$A
VALID PACKAGE 12.1.10 SQLT$C
VALID PACKAGE 12.1.11 SQLT$D
VALID PACKAGE 12.1.10 SQLT$E
VALID PACKAGE 12.1.10 SQLT$H
VALID PACKAGE 12.1.10 SQLT$I
VALID PACKAGE 12.1.10 SQLT$M
VALID PACKAGE 12.1.160429 SQLT$R
VALID PACKAGE 12.1.10 SQLT$S
VALID PACKAGE 12.1.10 SQLT$T
VALID PACKAGE 11.4.5.0 TRCA$E
VALID PACKAGE 11.4.5.0 TRCA$G
VALID PACKAGE 11.4.5.0 TRCA$I
VALID PACKAGE 11.4.5.0 TRCA$P
VALID PACKAGE 11.4.5.0 TRCA$R
VALID PACKAGE 11.4.5.0 TRCA$T
VALID PACKAGE 11.4.5.0 TRCA$X
VALID PACKAGE BODY 12.2.171004 OSQLT$A
VALID PACKAGE BODY 12.1.14 SQLT$C
VALID PACKAGE BODY 12.2.171004 OSQLT$D
VALID PACKAGE BODY 12.1.160429 SQLT$E
LIBRARIES
----------------------------------------------------------------
VALID PACKAGE BODY 12.1.160429 SQLT$H
VALID PACKAGE BODY 12.2.171004 OSQLT$I
VALID PACKAGE BODY 12.2.171004 OSQLT$M
VALID PACKAGE BODY 12.2.171004 OSQLT$R
VALID PACKAGE BODY 12.1.10 SQLT$S
VALID PACKAGE BODY 12.2.171004 OSQLT$T
VALID PACKAGE BODY 11.4.5.0 TRCA$E
VALID PACKAGE BODY 12.1.14 TRCA$G
VALID PACKAGE BODY 12.1.14 TRCA$I
VALID PACKAGE BODY 11.4.5.8 TRCA$P
VALID PACKAGE BODY 11.4.5.0 TRCA$R
VALID PACKAGE BODY 12.1.160429 TRCA$T
VALID PACKAGE BODY 11.4.5.0 TRCA$X
Deleting CBO statistics for SQLTXPLAIN objects ...
11:03:28 0 sqlt$a: -> delete_sqltxplain_stats
11:03:36 8 sqlt$a: <- delete_sqltxplain_stats
PL/SQL procedure successfully completed.
SQCPKG completed.
adding: 180117110220_08_sqcpkg.log (deflated 79%)
TAUTLTEST completed.
adding: 180117110336_09_tautltest.log (deflated 59%)
SQUTLTEST completed.
adding: 180117110336_10_squtltest.log (deflated 59%)
SQLT users must be granted SQLT_USER_ROLE before using this tool.
SQCREATE completed. Installation completed successfully.
SQL>
Execute SQLT for sql id
In my case the sql which is poorly running belongs to Sysman schema
Here it goes...
Comments