Using Oracle SQLTXPLAIN (SQLT)

Oracle 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.

Start Installing

C:\sqlt\install>sqlplus sys/[email protected] as sysdba
 
SQL> select DBID, name, CDB, CON_ID, CON_DBID from v$database;
 
DBID        NAME      CDB     CON_ID   CON_DBID
---------- --------- --- ---------- ----------
1352104669 ORCL      YES          0 1352104669

Install Oracle SQLTXPLAIN on the container DB

SQL> start sqcreate
 
…
 
SQUTLTEST completed.
adding: 131117093318_10_squtltest.log (160 bytes security) (deflated 61%)
no rows selected
 
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> start sqcreate
 
SQLT users must be granted SQLT_USER_ROLE before using this tool.
 
SQCREATE completed. Installation completed successfully.

Create user with grant on SQLT_USER_ROLE

SQL> create user Test identified by Test123;
 
User created.
 
SQL> grant SQLT_USER_ROLE to Test;
 
Grant succeeded.

C:\sqlplus Test/[email protected]:1521/pdborcl
 
SQL> create table t1 as select rownum n1 from dual connect by level<=10;
 
SQL> create index i1 on t1(n1);
 
SQL> select * from t1 where rownum<=1;
 
N1
----------
1
 
SQL> select * from table(dbms_xplan.display_cursor);
 
SQL_ID  7yzrbhp4b6vhr, child number 0
Plan hash value: 3836375644
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |
|*  1 |  COUNT STOPKEY     |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |     1 |     3 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)

Apply Oracle SQLTXTRACT to above simple sql query

SQL> start c:\sqlt\run\sqltxtract 7yzrbhp4b6vhr My_Password
 
…/…
 
SQLDX files have been created.
Archive:  sqlt_s86941_sqldx.zip
Length  Date       Time    Name
------- ---------- ----- ----
4631   17/11/2013 10:20  sqlt_s86941_sqldx_7yzrbhp4b6vhr_csv.zip
28048  17/11/2013 10:20  sqlt_s86941_sqldx_global_csv.zip
4363   17/11/2013 10:20  sqlt_s86941_sqldx_7yzrbhp4b6vhr_log.zip
---------                -------
37042                     3 files
 
adding: sqlt_s86941_sqldx.zip (160 bytes security) (stored 0%)
 
SQLTXTRACT completed.

The following SQL can be used to check wheater SQLT is already installed or not. ( run as SYS or the SQLTXPLAIN user ) This will provide version information if it is installed.

COL sqlt_version FOR A40;
SELECT
‘SQLT version number: ‘||sqltxplain.sqlt$a.get_param(‘tool_version’)||CHR(10)||
‘SQLT version date  : ‘||sqltxplain.sqlt$a.get_param(‘tool_date’)||CHR(10)||
‘Installation date  : ‘||sqltxplain.sqlt$a.get_param(‘install_date’) sqlt_version
FROM DUAL
/

Also See:

ORAchk