SQL Dynamic eXtractor (SQLDX) is now available under SQLT and SQLHC

Background

As most of you know SQLTXPLAIN (SQLT) and SQL Health-Check (SQLHC) include lots of information about one SQL statement. SQLT requires to install an schema and some objects while SQLHC requires no installation. The main difference between the two are:

SQLT is always the preferred method, while SQLHC is the fall back when SQLT cannot be installed in a timely manner.

Both SQLT and SQLHC work on 10g and 11g databases. SQLT presents details about many GV$ and DBA views referencing your SQL statement. These views include both 10g and 11g. In other words, SQLT is not restricted to the minimum common denominator (10g) but it expects to extract information from any relevant view referencing a SQL_ID on 11g. There is one small limitation although: SQLT does not show in its main report all views and tables referencing a SQL_ID, but most of what is usually needed for SQL Tuning.

What is the SQL Dynamic eXtractor (SQLDX)?

SQL Dynamic eXtractor (SQLDX) is a new stand-alone SQL script (sqldx.sql) that requires no installation. It dynamically generates another script that selects from all SYS views and tables which reference a column SQL_ID, extracting all rows matching the SQL_ID passed. It then computes the SIGNATURE for the SQL’s text and proceeds to generate dynamic SQL to select from all SYS tables and views referencing this SIGNATURE. For SIGNATURE it uses exact and force matching.

SQLDX input and output

SQLDX output is a ZIP file that contains the output of all dynamic SELECTs on all SYS tables and views referencing your SQL_ID and its SIGNATURE (exact and force). The output of every dynamic SELECT is a file on its own. Their format is HTML and Coma Separated Values (CSV). The latter can be opened in Excel to ease aggregations, finer selections, sorts, etc.

SQLDX takes as input 3 parameters:

Oracle Pack License: Diagnostics Pack or Tuning Pack (D|T).

Output Type: HTML, CSV or BOTH (H|C|B).

SQL_ID: resident in memory or AWR.

SQLDX is included on SQLT and SQLHC 11.4.5.3 and higher (released on December 31, 2012). SQLDX is automatically executed at the end of any SQLT main method and at the end of SQLHC. Keep in mind that SQLDX can also be executed stand-alone and it requires no installation whatsoever. So it can be used in a Production environment or in a read-only database.

Where do you get SQLDX from?

SQLT (MOS 215187.1) includes sqlt/run/sqldx.sql and SQLHC (MOS 1366133.1) includes sqlhc/sqldx.sql. It is the same sqldx.sql script. When sqldx.sql is executed from within SQLT or SQLHC it generates a ZIP file identified as “sqldx”. Look for it next time you use SQLT or SQLHC (version 11.4.5.3 or higher). Or you may want to try sqldx.sql stand-alone.

14 Responses

Great job Carlos! I am utilizing TRCA for a long, long time from my little PHP script which is available from here: http://mgogala.byethost5.com/dba_helper_1.0.30.zip
The only objection is that it is using copious amounts of CPU. From the looks of it, it looks like some kind of Java based parser is loaded and executed to parse the trace file. Would it be possible to create lex/yacc based shared library? That would alleviate the need for creating a Java virtual machine, decrease the overhead and increase performance.
Last, but not least, I wish you a happy New Year.

Mladen, Trace Analyzer does not use Java. I am considering a re-write, but it would be PL/SQL again. Reason is ease for data mining on parsed output. Having it inside the DB makes it easier to aggregate and slice data in any dimension. Cheers — Carlos

GP,
I only use SQL Developer to make a SQL statement look pretty. So, I do not know if SQL Developer allows you to run a SQL script. But even if I knew I’d rather use SQL*Plus, this to keep things easier. No need to add an extra GUI layer that can only do one thing in this case: make it slower!
Cheers — Carlos