SQL Monitor and SQL ASH Scripts are now available
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.
Carlos Sierra's Tools and Tips 
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
Hi Carlos, I do not see any scripts in the google drive… Can you please let me know the link for download of scripts?
Nanda
August 19, 2014 at 10:59 am
Nanda,
I just updated the list. Thanks for pointing this out. Also, on the right hand side I have some links. The Scripts link is there.
Cheers
Carlos Sierra
August 20, 2014 at 6:38 am
Hi carlos,
I can’t find sqlash script. Can you help to get the script by providing link?
Thanks
Alpesh
Alpesh
November 17, 2014 at 10:51 am
Alpesh,
Look at the right hand side, under “Downloads”. If it does not work for you please let me know.
Carlos
Carlos Sierra
November 18, 2014 at 9:36 am
Thank you Carlos…
Nanda
September 4, 2014 at 3:56 pm
sure. i just moved them to github
Carlos Sierra
September 4, 2014 at 4:18 pm
HI, I did not found the scripts, can you please provide me download link
thx. in advance
Amit Midha
August 3, 2016 at 3:04 pm
Sure. Just click on the cscripts (Jul 16, 2016) on the right-hand side. Then loo inside the sql directory. Please ping me again if you don’t find them.
Carlos Sierra
August 3, 2016 at 5:48 pm