Oracle SQL Tuning Tools and Tips

SQL Tuning, eAdam, eDB360, SQLT, SQLHC

SQL Monitor and SQL ASH Scripts are now available

with 4 comments

I just uploaded two new scripts to my set of “Shared Scripts“. These are the sqlmon.sql and the sqlash.sql. They both input a SQL_ID and produce some txt/html/zip output.

The sqlmon.sql produces SQL Monitor Reports for all executions available on SQL Monitor for a given SQL. It is RAC aware and its output includes both text and “active” reports. You must have an Oracle Tuning Pack license on the system where you may want to use it.

The sqlash.sql produces Active Session History (ASH) Reports for one SQL_ID. It is also RAC aware and output includes both text and html formats. It produces these ASH reports from both memory and AWR. You must have an Oracle Diagnostics Pack license on the system where you may want to use it.

These two scripts are light weight. Install nothing, change nothing. If you need a more robust diagnostic tool in these areas, please use SQLHC and/or SQLT. In the other hand, for a quick understanding of one SQL you may want to use the trio: planx.sql, sqlash.sql and sqlmon.sql.

Written by Carlos Sierra

October 15, 2013 at 9:03 pm

4 Responses

Subscribe to comments with RSS.

  1. Hi Carlos,
    Thanks for script
    I tried to test it on 11.2.0.2 DB for 2 different SQL_ID ,

    Although it run good for few sql_id but I find few issues ,
    1) It seems even if SQL_ID is V$SQL , but if last_active_time is from yesterday (1 day old)
    , it throws error like below

    SQL> @sqlash.sql.txt

    1. Enter Oracle Diagnostics Pack License Flag [ Y | N ] (required)
    Enter value for 1: Y

    2. Enter SQL_ID (required)
    Enter value for 2: acaxa5769rdcd
    zip warning: name not matched: sqlash_acaxa5769rdcd_*.html

    zip error: Nothing to do! (sqlash_acaxa5769rdcd.zip)

    zip warning: name not matched: sqlash_acaxa5769rdcd_*.txt

    zip error: Nothing to do! (sqlash_acaxa5769rdcd.zip)

    adding: sqlash_acaxa5769rdcd_driver.sql (stored 0%)

    deleting: sqlash_acaxa5769rdcd_driver.sql
    zip warning: zip file empty

    sqlash_acaxa5769rdcd.zip contains text and html reports

    2) I tried with another SQL_ID , it ive me error like below

    2. Enter SQL_ID (required)
    Enter value for 2: g073mt2z25asa
    SPO sqlash_g073mt2z25asa_1_20131016_001204_20131016_001204_mem.txt;
    SELECT output FROM TABLE(SYS.DBMS_WORKLOAD_REPOSITORY.ash_report_text(:dbid, 1, TO_DATE(‘20131016001204′, ‘YYYYMMDDHH24MISS’), TO_DATE(‘20131016001204′, ‘YYYYMMDDHH24MISS’), 0, 0, TO_NUMBER(NULL), :sql_id));
    SPO OFF;
    SPO sqlash_g073mt2z25asa_1_20131016_001204_20131016_001204_mem.html;
    SELECT output FROM TABLE(SYS.DBMS_WORKLOAD_REPOSITORY.ash_report_html(:dbid, 1, TO_DATE(‘20131016001204′, ‘YYYYMMDDHH24MISS’), TO_DATE(‘20131016001204′, ‘YYYYMMDDHH24MISS’), 0, 0, TO_NUMBER(NULL), :sql_id));
    SPO OFF;
    SELECT output FROM TABLE(SYS.DBMS_WORKLOAD_REPOSITORY.ash_report_text(:dbid, 1, TO_DATE(‘20131016001204′, ‘YYYYMMDDHH24MISS’), TO_DATE(‘20131016001204′, ‘YYYYMMDDHH24MISS’), 0, 0, TO_NUMBER(NULL), :sql_id))
    *
    ERROR at line 1:
    ORA-20550: -> Error: begin time must be strictly less than end time
    ORA-06512: at “SYS.DBMS_SWRF_REPORT_INTERNAL”, line 10321
    ORA-06512: at “SYS.DBMS_SWRF_REPORT_INTERNAL”, line 10408
    ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 1512
    ORA-06512: at line 1

    Please suggest
    Thanks,
    Mayank

    mayank

    October 16, 2013 at 5:00 am

    • Mayank,
      Case #1: please verify your SQL has some rows in gv$active_session_history or dba_hist_active_sess_history.
      Case #2: it seems your SQL has one row in dba_hist_active_sess_history. Thus begin and end date are the same. In such corner case the API from DBMS_WORKLOAD_REPOSITORY fails.
      Cheers — Carlos

      Carlos Sierra

      October 16, 2013 at 6:11 am

      • Case 1 ; You are correct SQL is missing from both gv$active_session_history and dba_hist_active_sess_history.

        Case 2 : SQL is only present in gv$active_session_history, and not moved to dba_hist_active_sess_history , is it still valid script to fail ?

        So it means if SQL is flushed out(aged) from shared pool , sqlash.sql is not effective and its only good for active statements in Shared pool

        How this situation can be handled with your scripts
        How can retention of SQL_ID can be incresed in gv$active_session_history and dba_hist_active_sess_history

        Thanks,
        Mayak

        mayanksi

        October 16, 2013 at 8:18 am

      • Case 2: I uploaded a new version right now that would avoid the error. New version will skip those corner cases where the begin date and the end date for a range are the same. In prior version it looks like you got an error but if there were any valid ranges where begin date and end date are different, a report would be produced.

        This sqlash.sql would work if the cursor has aged out. It would also work if there is no longer any records for it on the V$ ASH view. As long as there are some records on AWR ASH for the SQL it would work. In other words, as long as there are records on ASH on memory and/or AWR it would work.

        If you want to increase retention time on ASH, you would have to increase the size of the ASH circular buffer. There are some parameters for that (I would have to google them).

        Thanks for your feedback Mayak!

        Carlos Sierra

        October 16, 2013 at 10:56 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,413 other followers

%d bloggers like this: