Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

SQLTXPLAIN (SQLT) is now available

with 4 comments

SQLT XTRSBY new method

If you have a poorly-performing SQL statement on a read-only standby Data Guard database, now you can analyze it with SQLT. Just need to install SQLT on Primary, create a database link into the read-only and use new method SQLT XTRSBY passing SQL_ID and db link name. Read sqlt/sqlt_instructions.html for more details.

In addition, there are two stand-alone scripts (with no SQLT dependencies)  that you can use directly on any read-only database. They are sqlt/utl/roxtract.sql and sqlt/utl/roxecute.sql. They mimic SQLT XTRACT and XECUTE but in a smaller scale. Notice that you can also use these two stand-alone scripts safely on any production environment since they do not execute any DDL and they perform queries only (no inserts, updates or deletes). What do you get from them? You get the basics: execution plans; peeked binds; CBO statistics for Tables, Table Columns, Indexes and Index Columns; SQL Monitor Active report; 10053 trace; 10046 trace and TKPROF on roxecute.sql.

If using SQLT XTRSBY on a Data Guard Primary database, you may also want to use roxtract.sql on the read-only standby database. Combined you will get more about your SQL. XTRSBY focuses on GV$* from standby plus DBA* from Primary. Script roxtract.sql only looks at standby.

As always, download latest version of SQLT from MOS 215187.1.

SQLT changes:


  1. Removed annoying message on main “sqlt$a: *** t:plan_operation%: ORA-01403: no data found”.
  2. Test Case (TC) script q.sql now takes values of NULL on binds instead of continue searching for not NULL values. It also handles string “NULL” as NULL on dba_hist_sqlbind.


  1. SQLT is now Data Guard aware. There is a new method SQLT XTRSBY which is executed on Primary and extracts from stand-by almost everything XTRACT does. Please refer to instructions before using this new method.
  2. In addition to new SQLT XTRSBY which runs in Data Guard Primary, there are two new scripts sqlt/utl/roxtract.sql and sqlt/utl/roxecute.sql which are super lite versions of SQLT XTRACT and XECUTE. The new read-only (RO) scripts can be used on any read-only database since they do not install anything and do not update anything. They provide a quick and safe view of the environment around one SQL statement.
  3. New instrumentation on SQLT XTRACT, XECUTE, XTRXEC, XTRSBY and XPLAIN. All these methods produce now a TKPROF for the actual execution of the tool. The new TKPROF is located in the log zip file. It helps to diagnose SQLT taking longer than expected.
  4. Skip MDSYS objects from metadata script. Since MDSYS is part of the data dictionary, it makes sense to skip it from Metadata as we do with similar schemas.
  5. Column remap on SQLT TC now considers map by QUALIFIED_COL_NAME when name is other than SYS%. This helps to match more columns when we are restoring CBO schema statistics while remapping to new TC user.
  6. Metadata includes now PL/SQL library which executed the SQL being analyzed. This is useful to actually see the piece of program that invoked the SQL being analyzed.

Written by Carlos Sierra

May 3, 2012 at 7:56 am


Tagged with ,

4 Responses

Subscribe to comments with RSS.

  1. Thanks for a great tool which I have been using for sometime now. I have a SQL running for more than 12 hours. Does the SQL have to complete before using XTRACT? Should the SQL needs to run to completion, then how to best handle situations when sql_id is loss? Would XPLAIN with gather_plan_statistics suffice?


    May 23, 2012 at 10:25 pm

    • You can use SQLT XTRACT on a SQL even if it has not completed yet. If the SQL_ID is lost, use SQLT XECUTE. Either XTRACT or XECUTE are much better than XPLAIN.

      Carlos Sierra

      May 24, 2012 at 7:24 am

      • My apologies. I did not know the commented the same question in two different posts, thinking the first did not commit. Thanks for the fast response.


        May 24, 2012 at 9:16 am

      • no problem 😉

        Carlos Sierra

        May 24, 2012 at 2:39 pm

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

%d bloggers like this: