Mohamed Houri’s Oracle Notes

November 18, 2013

SQLTXPLAIN under Oracle 12c

Filed under: Tuning tools — hourim @ 8:14 am

I like very much Tanel Poder snapper and Carlos Sierra SQLTXPLAIN . They represent valuable performance diagnostic tools. Unfortunately I am still waiting to find a customer site where I will be allowed or granted necessary privileges to install and to use them. There are client sites where I have been asked to tune queries without having the possibility to execute dbms_xplan.display_cursor. Let alone installing SQLTXPLAIN under SYS user or having grant select on x$ tables.

This is why I have installed them in my personal laptop and I am using them very often in my personal Oracle Research & Developments (R&D). Although, personal work are peanuts when compared with onsite oracle consultancy work, I didn’t renounce to ”home” use them.

I have already successfully installed SQLTXPLAIN on Oracle My first work on SQLTXPLAIN was to go back to personal engineered performance problems ”traditionally” solved and ask myself  “What would I have pointed out using SQLTXPLAIN in such a performance issue?’’. Up to now this is my sole strategy of using this tool like what I have published here and here

My second step in deepening my SQLTXPLAIN R&D was to buy Stelios Charalambides book Oracle SQL Tuning with Oracle SQLTXPLAIN. In the meantime Oracle 12c has been released and naturally I have installed this release after having uninstalled the last one.

I am still reviewing this book. Last week I finished reviewing Chapter 8 and thought that it is time now to devote few time on this tool again because it makes no sense to review this book without having at least the main html report produced by SQLTXTRACT module of the SQLT tool. So I decided to start installing it on my personal Oracle 12c database.

C:\sqlt\install>sqlplus sys/sys@orcl 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

As you can see from the above select I am going to install 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 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Oops!! I have been abruptly disconnected; something went wrong for sure. After having tried several times without success I decided to contact Mauro Pagano from the Oracle support. Thanks to the generated installation log file I have sent him, he immediately answered me that I am trying to install SQLT on a container DB which is impossible for this moment. He kindly suggested me to install it on a pluggable DB and to let him know the results of this new installation. So I embarked again on a new installation

First I have figured out how to connect to the pluggable data base

C:\sqlt\install>sqlplus sys/sys@localhost:1521/pdborcl as sysdba;

SQL> select name, con_id from v$active_services;

NAME               CON_ID
------------------ --------
pdborcl              3

If the pluggable data base is not already open then open it

SQL> alter database pdborcl open;

alter database pdborcl open
ERROR at line 1:
ORA-65019: pluggable database PDBORCL already open

And finally I launched the sqcreate which I have preceded by the sqdrop for a clean starting situation

SQL> start sqcreate


SQLT users must be granted SQLT_USER_ROLE before using this tool.

SQCREATE completed. Installation completed successfully.

Hopefully this time the installation finished successfully with the last two above instructions which I have religiously followed

SQL> create user mohamed identified by mohamed;

User created.

SQL> grant SQLT_USER_ROLE to mohamed;

Grant succeeded.

Am I now ready to use this tool under 12c pluggable db? Let’s test

C:\sqlplus mohamed/mohamed@localhost: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;


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)

And what if I apply SQLTXTRACT to my above simple sql query?

SQL> start c:\sqlt\run\sqltxtract 7yzrbhp4b6vhr My_Password


SQLDX files have been created.
Length  Date       Time    Name
------- ---------- ----- ----
4631   17/11/2013 10:20
28048  17/11/2013 10:20
4363   17/11/2013 10:20
---------                -------
37042                     3 files

adding: (160 bytes security) (stored 0%)

SQLTXTRACT completed.

And finally it works as shown via this pdf file sqlt_s86941_main

Bottom line

  1. The SQLTXPLAIN plan is currently available to be installed only on a pluggable db in Oracle 12c
  2.  You have on Mauro Pagano a very modest person always ready to help you trouble shooting SQLT installation or using SQLT different modules
  3.  You need to start exploring this tool. It is worth the investigation believe me

Blog at

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog


Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)