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 11.2.0.1. 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 12.1.0.1.0 - 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;

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)

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

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

2 Comments »

  1. Mohamed,
    My good friend Mauro Pagano took the responsibility to support, maintain and enhance SQLT. Mauro is doing a great job. He is very talented and humble.
    Mauro has been working on SQLT for 12c. Yes, there are some limitations, like you mentioned here. Still, SQLT runs fine on 12c.
    I share your frustration about limited access to a customer production database. It is a process. The immediate reaction is: no, we cannot install anything. Buy after they comprehend the need and value, they usually agree to put SQLT on their maintenance window, so we get it installed and used. I have seen this in all kind of business, from small shops to big banks and government. So, it is a matter of selling it. 2nd best option is to use SQLHC.
    Any ways, it is good to see you are working with Mauro and that you are reading Stelios book. Stellios is also very approachable.
    Cheers — Carlos

    Comment by Carlos Sierra — November 18, 2013 @ 11:03 am | Reply

  2. Hi Carlos,

    Thanks for your comment. Indeed I’ve highly appreciated the prompt reaction of Mauro Pagano in helping me installing SQLT under Oracle 12c
    I hope one day I will easily convince customer to let me install SQLT and snapper. They are not only free but very very helpful.

    Best regards

    Comment by hourim — November 18, 2013 @ 1:41 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

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.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

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

%d bloggers like this: