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:

The level of details: (SQLT provides a lot more).
The capability of automatic Test Case (TC) extraction (SQLT does this).

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.

Written by Carlos Sierra

January 2, 2013 at 8:05 am

Posted in SQL Healh-Check (SQLHC), SQL Tuning, SQLTXPLAIN (SQLT)

14 Responses

Subscribe to comments with RSS.

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 Gogala

January 2, 2013 at 2:44 pm

Reply
- 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
  
  Carlos Sierra
  
  January 3, 2013 at 7:16 am
  
  Reply
- Perhaps your PHP script or PHP server is calling java?
  
  Rich Headrick
  
  January 7, 2013 at 12:52 pm
  
  Reply
  - I do not use PHP nor Java on my scripts…
    
    Carlos Sierra
    
    January 8, 2013 at 5:15 am
Carlos,

Where would one most likely need to use SQLDX?

Rich Headrick

January 7, 2013 at 12:53 pm

Reply
- Rich,
  I think as part of SQLT and SQLHC. It runs also stand-alone, but it makes sense just to use it from within the first two.
  
  Carlos Sierra
  
  January 8, 2013 at 5:17 am
  
  Reply
Hi,

How can I call SQLHC from within Sql Developer , Can i use it in User Defined Reports as a Child report.
Thanks
GP Singh

GP Singh

March 6, 2013 at 3:36 pm

Reply
- 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
  
  Carlos Sierra
  
  March 7, 2013 at 5:15 am
  
  Reply
Hi Carlos,
When running sqlt, below queries from sqldx.sql are taking too long to complete

SELECT * FROM LOGSTDBY_SUPPORT_TAB_11_2B is taking 580 seconds per execution.

SELECT COUNT(*) FROM DBA_LOGSTDBY_EDS_SUPPORTED WHERE (OWNER, table_name) IN ((‘OBIS’, ‘WC_OPTY_PROD_D’), (‘OBIS’, ‘WC_OPTY_REGN_D’), (‘OBIS’, ‘WC_REVN_F’), (‘OBIS’, ‘WC_REVN_MONTH_F’), (‘OBIS’, ‘W_DAY_D’), (‘OBIS’, ‘W_MONTH_D’), (‘OBIS’, ‘W_OPTY_D’), (‘OBIS’, ‘W_OPTY_DX’), (‘OBIS’, ‘W_SSTAGE_D’)) is taking 800 seconds per execution.

SELECT COUNT(*) FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE (OWNER, table_name) IN ((‘OBIS’, ‘WC_OPTY_PROD_D’), (‘OBIS’, ‘WC_OPTY_REGN_D’), (‘OBIS’, ‘WC_REVN_F’), (‘OBIS’, ‘WC_REVN_MONTH_F’), (‘OBIS’, ‘W_DAY_D’), (‘OBIS’, ‘W_MONTH_D’), (‘OBIS’, ‘W_OPTY_D’), (‘OBIS’, ‘W_OPTY_DX’), (‘OBIS’, ‘W_SSTAGE_D’)) is taking 532 seconds per execution.

SELECT COUNT(*) FROM DBA_LOGSTDBY_UNSUPPORTED_TABLE WHERE (OWNER, table_name) IN ((‘OBIS’, ‘WC_OPTY_PROD_D’), (‘OBIS’, ‘WC_OPTY_REGN_D’), (‘OBIS’, ‘WC_REVN_F’), (‘OBIS’, ‘WC_REVN_MONTH_F’), (‘OBIS’, ‘W_DAY_D’), (‘OBIS’, ‘W_MONTH_D’), (‘OBIS’, ‘W_OPTY_D’), (‘OBIS’, ‘W_OPTY_DX’), (‘OBIS’, ‘W_SSTAGE_D’)) is taking 405 seconds per execution.

Please advise.

Vishal

December 6, 2013 at 11:49 am

Reply
- Vishal,
  
  Usually when you get some queries on data dictionary taking long, that is because of stale stats in your data dictionary. When was the last time they were gathered?
  
  Cheers — Carlos
  
  Carlos Sierra
  
  December 7, 2013 at 8:17 am
  
  Reply
Hi,

I have exactly the same problem with SELECT * FROM LOGSTDBY_SUPPORT_TAB_11_2B

For my first DB 5execution time 6 hours)
SYS@XXX SQL> select count(1) from col$;
895,849
SYS@XXX SQL> select count(1) from seg$;
149,116
SYS@XXX SQL> select count(1) from tab$;
79,410
SYS@XXX SQL> select count(1) from obj$;
168,713
SYS@XXX SQL> select count(1) from user$;
109

for my second (execution time 4 days)
SYS@XXX SQL> select count(1) from col$;
2,955,942
SYS@XXX SQL> select count(1) from seg$;
134,386
SYS@XXX SQL> select count(1) from tab$;
125,629
SYS@XXX SQL> select count(1) from obj$;
540,538
SYS@XXX SQL> select count(1) from user$;
533

my third DB (1min 24 secs)
SYS@XXX SQL> select count(1) from col$;
92,838
SYS@XXX SQL> select count(1) from seg$;
10,198
SYS@XXX SQL> select count(1) from tab$;
4,601
SYS@XXX SQL> select count(1) from obj$;
66,340
SYS@XXX SQL> select count(1) from user$;
533

All my statistics are up to date

Loic beyou

November 5, 2014 at 9:05 am

Reply
- Loic,
  About your comment. What is your question?
  Cheers — Carlos
  
  Carlos Sierra
  
  November 5, 2014 at 9:45 am
  
  Reply
  - My question is why does it take me up to 4 days to run a sqltxtract.sql?
    
    Loic beyou
    
    November 5, 2014 at 9:49 am
  - To answer your question please send me the zip generated by sqlt xtract. email: carlos.sierra.usa@gmail.com
    
    Carlos Sierra
    
    November 10, 2014 at 5:16 pm

Carlos Sierra's Tools and Tips