Oracle ASH use cases

Overview

Oracle takes a snapshot of active sessions once a second. We can analyze ASH data to solve real world problems, such as sql monitoring, sql stats, sql plan management, and find queries that are not using bind variables.

SQL monitoring

With ASH data, you can monitor the queries that are currently running.

SQL stats

Sometimes, you may want to check the snapshots of the past. You can get the similar data from ASH history table.

SQL plan management

In some cases, the execution plan changes and causes performance issue. Using ASH, we can see when plan changed, which plan is better,which plan is causing issue.

Find queries that are not using bind variables

Some applications use literals instead of bind variables. When the literal values are different, each sql_id is unique, even though they are the same query from the perspective of the application. With the ASH data, you can find a group of queries that share the same signature.

Example Queries

The following query can be used for sql monitoring

alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss";

col sample_time for a30
col machine for a40
col sid for 9999999
col force_matching_signature for 999999999999999999999999
set lines 1000


select v.inst_id, 
       v.sid, 
       v.blocking_session,
       v.blocking_inst_id,
       v.machine, 
       v.osuser, 
       v.schemaname,  
       v.state, 
       v.status, 
       v.sql_id, 
       v.phv, 
       v.force_matching_signature, 
       v.sql_exec_id, 
       v.sql_exec_start, 
       v.sample_time, 
       round((cast(v.sample_time as date) - v.sql_exec_start)*86400) seconds,
       v.session_state, 
       v.event,
       v.program,
       v.sql_text
from (
select 
       h.inst_id, 
       h.session_id sid, 
       h.blocking_session,
       h.blocking_inst_id,
       h.machine, 
       s.osuser, 
       s.schemaname,
       s.state, 
       s.status, 
       h.sql_id,
       h.sql_plan_hash_value phv,
       h.force_matching_signature,
       h.sql_exec_id,
       h.sql_exec_start,
       h.sample_time, 
       h.session_state, 
       h.event, 
       h.program,
       nvl2(t.sql_text, 
           replace(replace(t.sql_text, chr(10), ''), chr(13), ''), '') sql_text,
       row_number() over (partition by 
          h.inst_id, h.session_id, h.session_serial#, 
          h.sql_id, h.sql_exec_id order by h.sample_time desc) r 
from gv$active_session_history h
left join gv$session s on s.sid = h.session_id 
  and s.inst_id = h.inst_id and s.serial# = h.session_serial#
left join gv$sqltext t on t.inst_id = h.inst_id 
  and h.sql_id = t.sql_id and t.piece = 0
where h.sample_time > sysdate - 15/1440
and h.session_type = 'FOREGROUND'
) v
where v.r = 1
and (cast(v.sample_time as date) - v.sql_exec_start)*86400 > 5
order by v.inst_id, v.sample_time desc
;