Principaux événements d'attente et principaux SQL depuis ASH pour les dernières minutes

Commande

-- Last 30 min: wait event breakdown (each sample ~1 s of active session time)
SELECT NVL(event, 'ON CPU') AS event, wait_class,
       COUNT(*) AS samples,
       ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100, 1) AS pct
FROM   v$active_session_history
WHERE  sample_time > SYSTIMESTAMP - INTERVAL '30' MINUTE
GROUP  BY NVL(event, 'ON CPU'), wait_class
ORDER  BY samples DESC
FETCH FIRST 10 ROWS ONLY;

-- Last 30 min: top SQL (with CPU / User I/O breakdown)
SELECT sql_id, sql_opname,
       COUNT(*) AS samples,
       SUM(CASE WHEN session_state = 'ON CPU' THEN 1 ELSE 0 END) AS cpu,
       SUM(CASE WHEN wait_class = 'User I/O' THEN 1 ELSE 0 END)  AS user_io,
       ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100, 1) AS pct
FROM   v$active_session_history
WHERE  sample_time > SYSTIMESTAMP - INTERVAL '30' MINUTE
  AND  sql_id IS NOT NULL
GROUP  BY sql_id, sql_opname
ORDER  BY samples DESC
FETCH FIRST 10 ROWS ONLY;

-- Older periods: DBA_HIST_ACTIVE_SESS_HISTORY (one sample every 10 s; COUNT(*)*10 = seconds)

-- Ready-made ASH report (last 30 min):
SET LINESIZE 8000 PAGESIZE 0 TRIMSPOOL ON HEADING OFF FEEDBACK OFF
SELECT output
FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(
         l_dbid     => (SELECT dbid FROM v$database),
         l_inst_num => (SELECT instance_number FROM v$instance),
         l_btime    => SYSDATE - 30/1440,
         l_etime    => SYSDATE));
-- Interactive: @?/rdbms/admin/ashrpt.sql

Ce que fait la commande

Utilise les échantillons Active Session History pour montrer, en pourcentage, quels événements d'attente et quelles instructions SQL ont consommé du DB time au cours des dernières minutes ; diagnostique l'incident sans attendre un snapshot AWR.

Quand l'utiliser

Pendant un ralentissement en cours, pour les pics courts qui disparaissent dans les moyennes AWR, et pour voir ce qu'une session ou un SQL donné attendait.

Attention

Interroger V$ACTIVE_SESSION_HISTORY et DBA_HIST_ACTIVE_SESS_HISTORY exige la licence Diagnostics Pack. ASH est échantillonné : le nombre d'échantillons estime le temps, ce n'est pas une mesure exacte, et un SQL très court peut ne pas apparaître. Le buffer ASH en mémoire ne conserve que quelques heures sur les systèmes chargés.