Skip to content

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