ASH and related queries
PLSQL Shell
Latest commit 9866ca2 Apr 6, 2016 @khailey Merge pull request #6 from TimNanos/patch-3
Typos, typos, typos
Permalink
Failed to load latest commit information.
.gitattributes added ASH SQL Elapsed time queries Aug 31, 2012
.gitignore
README.md added Tanel Poders URL to Readme Jun 13, 2014
act.sql added io scripts and tcf.sql May 17, 2013
ash_bbw.sql added dbid, fixed ash_top_sql_w_top_obj for multi DBID AWR May 9, 2013
ash_enq.sql Just some suggestions to tidy the output Dec 22, 2015
ash_graph.sql took out total time v_days Feb 6, 2014
ash_graph_histash_by_dbid.sql adding top_procedure,. top_session, bbw plus file renaming Sep 7, 2012
ash_graph_histash_by_dbid_program.sql adding ash_graph queries plus latency and io_size Sep 7, 2012
ash_graph_histash_by_dbid_sqlid.sql adding ash_graph queries plus latency and io_size Sep 7, 2012
ash_graph_waits.sql Typos fix Apr 6, 2016
ash_graph_waits_histash.sql modified readme with ash_graph file renames Feb 6, 2014
ash_io_sizes.sql adding top_procedure,. top_session, bbw plus file renaming Sep 7, 2012
ash_io_top_obj.sql added ash_io_top_obj_advanced Jan 11, 2013
ash_io_top_obj_advanced.sql More typos Apr 6, 2016
ash_manual.sql
ash_plan_line.sql add link to Tanel's blog for ash_wait_chains Jun 12, 2014
ash_sql_elapsed.sql added dbid, fixed ash_top_sql_w_top_obj for multi DBID AWR May 9, 2013
ash_sql_elapsed_hist.sql added dbid, fixed ash_top_sql_w_top_obj for multi DBID AWR May 9, 2013
ash_sql_elapsed_hist_longestid.sql added dbid, fixed ash_top_sql_w_top_obj for multi DBID AWR May 9, 2013
ash_top_procedure.sql adding top_procedure,. top_session, bbw plus file renaming Sep 7, 2012
ash_top_session.sql adding top_procedure,. top_session, bbw plus file renaming Sep 7, 2012
ash_top_sql.sql added ash_top_sql_w_top_obj and ash_enq.sql Dec 21, 2012
ash_top_sql_w_top_obj.sql added dbid, fixed ash_top_sql_w_top_obj for multi DBID AWR May 9, 2013
ash_wait_chains.sql add link to Tanel's blog for ash_wait_chains Jun 12, 2014
ash_wait_chains_hist.sql add link to Tanel's blog for ash_wait_chains Jun 12, 2014
ash_xplan.sql Create ash_xplan.sql Apr 15, 2015
ash_xplan_example.txt Update ash_xplan_example.txt Apr 15, 2015
awr_change_dbid.sql added dbid, fixed ash_top_sql_w_top_obj for multi DBID AWR May 9, 2013
cpu_consumed_verses_cpuwait.sql updated blog URL Feb 27, 2014
cpu_consumed_verses_cpuwait_RAC.sql added cpu_consumed_verses_cpuwait_RAC.sql Mar 17, 2015
dbid.sql added dbid, fixed ash_top_sql_w_top_obj for multi DBID AWR May 9, 2013
import_awr.sql added dbid, fixed ash_top_sql_w_top_obj for multi DBID AWR May 9, 2013
io.sql added io scripts and tcf.sql May 17, 2013
io_dpr.sql added io scripts and tcf.sql May 17, 2013
io_pqo.sql added io scripts and tcf.sql May 17, 2013
io_scat.sql added io scripts and tcf.sql May 17, 2013
io_seq.sql added io scripts and tcf.sql May 17, 2013
io_sql.sql added io scripts and tcf.sql May 17, 2013
io_timestamps.sql added io scripts and tcf.sql May 17, 2013
ioag.sql added io scripts and tcf.sql May 17, 2013
ioag_orig.sql added io scripts and tcf.sql May 17, 2013
iodfpr.sql added io scripts and tcf.sql May 17, 2013
ionoobj.sql added io scripts and tcf.sql May 17, 2013
ionosql.sql
ioobj.sql added io scripts and tcf.sql May 17, 2013
ioobjdec.sql added io scripts and tcf.sql May 17, 2013
iop.sql added io scripts and tcf.sql May 17, 2013
iosql.sql added io scripts and tcf.sql May 17, 2013
iosql_aas.sql added io scripts and tcf.sql May 17, 2013
iotbs.sql added io scripts and tcf.sql May 17, 2013
iotbs1.sql added io scripts and tcf.sql May 17, 2013
iotx.sql added io scripts and tcf.sql May 17, 2013
jb.sql Update jb.sql Apr 6, 2016
latency_eventmetric.sql adding top_procedure,. top_session, bbw plus file renaming Sep 7, 2012
latency_system_event.sql added io scripts and tcf.sql May 17, 2013
latency_waitclassmetric.sql adding top_procedure,. top_session, bbw plus file renaming Sep 7, 2012
pstack.sh added dbid, fixed ash_top_sql_w_top_obj for multi DBID AWR May 9, 2013
tcf.sql added io scripts and tcf.sql May 17, 2013

README.md

see http://ashmasters.com for information on
Oracle Active Session History (ASH)

also see Tanel Poder's ash scripts at
http://blog.tanelpoder.com/files/scripts/ash/

queries contained in repository

  • ash_bbw.sql - buffer busy wait analysis
  • ash_enq.sql - enqueue wait analays
  • ash_graph.sql - basic ASH load chart in ASCII art more for demo purposes
  • ash_graph_waits.sql - basic ASH load chart in ASCII art with top 2 waits per bucket
  • ash_graph_waits_histash.sql - ASH load chart from DBA_HIST_ACTIVE_SESS_HISTORY only
  • ash_graph_histash_by_dbid.sql - ASH load chart from DBA_HIST_ACTIVE_SESS_HISTORY only, input DBID
  • ash_graph_histash_by_dbid_program.sql - ASH load chart from DBA_HIST_ACTIVE_SESS_HISTORY only, input DBID and PROGRAM
  • ash_graph_histash_by_dbid_sqlid.sql - ASH load chart from DBA_HIST_ACTIVE_SESS_HISTORY only, input DBID and a SQL_ID
  • ash_io_sizes.sql - I/O sizes from ASH
  • ash_sql_elapsed.sql - use ASH to find longest running SQL
  • ash_sql_elapsed_hist.sql - use ASH to find longest running SQL, give histogram of execution times
  • ash_sql_elapsed_hist_longestid.sql - use ASH to find longest running SQL, give histogram of execution times and execution id of longest running query
  • ash_top_procedure.sql - top procedures and who they call
  • ash_top_session.sql - top SESSION from ASH ordered by time broke into wait, I/O and CPU time
  • ash_top_sql.sql - top SQL from ASH ordered by time broke into wait, I/O and CPU time
  • ash_top_sql_w_top_obj.sql - top SQL from ASH ordered by time broke into wait, I/O and CPU time, include top object for I/O waits
  • latency_eventmetric.sql - wait event latency from V$EVENTMETRIC, ie last 60 seconds
  • latency_system_event.sql - wait event latency from DBA_HIST_SYSTEM_EVENT
  • latency_waitclassmetric.sql - User I/O latency from V$WAITCLASSMETRIC, ie over last 60 seconds