Identifier la requête problématique

Ici, rien de particulier, on va aller chercher dans V$SESSION. Généralement, la requête suivante permet de trouver le SQL_ID :

select * from v$session where type!='BACKGROUND' and status = 'ACTIVE';

V$SESSION permet de trouver le SQL_ID et son plan d'exécution ; par contre, aucune information sur le détail du plan d'exécution ni sur les attentes.
Pour récupérer le plan d'exécution, nous allons utiliser V$SQL_PLAN.
Pour les événements d'attente, ça se complique. Ici, nous avons besoin de la vue V$ACTIVE_SESSION_HISTORY ; attention, cette vue est soumise à la licence Tuning Pack Diagnostic Pack (merci Ahmed pour la correction). Donc le code SQL que je vous donne ne doit être utilisé uniquement sur les bases qui ont la licence appropriée.

Active Session History (ASH)

ASH est l'élément clé du suivi de performance sur le moteur sur Oracle. Il va collecter des métriques d'exécution par session et les enregistre dans les vues système. Ces vues sont ensuite utilisées pour créer les snapshots AWR (et donc les rapport AWR). Un process interne à la base de donnée collecte toutes les attentes de toutes les sessions selon un intervalle court (1s) puis les enregistre. La vue V$ACTIVE_SESSION_HISTORY contient 1 ligne par session et par collecte. Puisqu'il y a une collecte environ toutes les secondes, le nombre de ligne est impressionnant.
La vue est organisée en plusieurs parties. Vous avez notamment les infos sur l’événement en attente et le temps dans cet événement.
Ce qui nous intéresse nous se sont les infos en "DELTA" (colonnes de fin). Ici, ASH enregistre le nombre de milliseconde passées dans la CPU, le nombre d'i/o, etc... depuis la collecte précédente.

Le plan d’exécution avec les attentes en cours

Voici la requête finale : En Oracle 12c, nous avons la statistique sur les lectures mémoire : DELTA_READ_MEM_BYTES qui n'existait pas en jusque là.

alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

define SQL_ID = 'b1j76xa4nsqak'

with sub as (
select ash.session_id,ash.SESSION_SERIAL#,ash.sql_exec_start,ash.SQL_ID,ash.SQL_PLAN_HASH_VALUE,ash.SQL_CHILD_NUMBER,ash.SQL_PLAN_LINE_ID,ash.SQL_PLAN_OPERATION,
round(sum(ash.DELTA_READ_IO_BYTES)/1024/1024,2) as READ_IO_MBYTES,
round(sum(ash.DELTA_READ_MEM_BYTES)/1000000,2) as READ_MEM_BYTES,
round(sum(ash.DELTA_WRITE_IO_BYTES)/1024/1024,2) as WRITE_IO_MBYTES,
round(sum(ash.TM_DELTA_CPU_TIME)/1000000,2) as CPU_TIME,
round(sum(ash.TM_DELTA_DB_TIME)/1000000,2) as DB_TIME,
rank() over (order by sql_exec_start desc) as rank
from V$ACTIVE_SESSION_HISTORY ash
where ash.SQL_ID=:SQL_ID
and ash.session_id = 377
group by ash.session_id,ash.SESSION_SERIAL#,ash.sql_exec_start,ash.SQL_ID,ash.SQL_PLAN_HASH_VALUE,ash.SQL_CHILD_NUMBER,ash.SQL_PLAN_LINE_ID,ash.SQL_PLAN_OPERATION
)
select sub.sql_exec_start,sp.sql_id,sp.plan_hash_value,sp.id,sp.operation,sp.options,sp.object_owner,sp.object_name as object_name,
sp.FILTER_PREDICATES,sp.PROJECTION,
sub.READ_IO_MBYTES, sub.READ_MEM_BYTES, sub.WRITE_IO_MBYTES, sub.CPU_TIME, sub.DB_TIME
from sub
right outer join V$SQL_PLAN sp
on (sub.SQL_ID=sp.SQL_ID and sub.SQL_CHILD_NUMBER=sp.CHILD_NUMBER and sub.SQL_PLAN_LINE_ID=sp.id and sub.rank=1)
where
sp.sql_id=:SQL_ID
order by sp.id;

A vous de jouer sur les colonnes dont vous avez besoin. Notez que la requête renvoie les statistiques de l'exécution la plus récente (ou en cours) via : rank() over (order by sql_exec_start desc) as rank