Oracle SQL Tuning Tools and Tips

SQL Tuning, eAdam, eDB360, SQLT, SQLHC

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

with 10 comments

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:

  1. The level of details: (SQLT provides a lot more).
  2. 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:

  1. Oracle Pack License: Diagnostics Pack or Tuning Pack (D|T).
  2. Output Type: HTML, CSV or BOTH (H|C|B).
  3. 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

10 Responses

Subscribe to comments with RSS.

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

    • 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

    • Perhaps your PHP script or PHP server is calling java?

      Rich Headrick

      January 7, 2013 at 12:52 pm

  2. Carlos,

    Where would one most likely need to use SQLDX?

    Rich Headrick

    January 7, 2013 at 12:53 pm

    • 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

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

    • 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

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

    • 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


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

Follow

Get every new post delivered to your Inbox.

Join 1,430 other followers

%d bloggers like this: