SQL Tuning, futó lekérdezések memória és temp használatának megjelenítése végrehajtási lépésenként.

Ambrus Gábor kollégámtól kaptam az alábbi, praktikus script - et, melynek segítségével megtekinthető egy éppen futó sql végrehajtási terve, s a végrehajtási tervhez kapcsolódó egyes lépések aktuális memória és temp használata. Ez a lekérdezés nagyon megtetszett nekem, mert segítségével betekintést nyerhetünk lekérdezésünk végrehajtási állapotába, a memória és temp értékek vizsgálatával könnyen kiszúrhatjuk, ha valahol "hiba" csúszott a végrehajtási tervbe.

A lekérdezés második sorába kell beilleszteni a futtatás előtt a vizsgálandó futó lekérdezés sql_id azonosítóját.


with sqlid as (
    select '9c9qwv489rxfk' sql_id from dual
)
select
    woac.temp_mb,
    woac.mem_mb,
    substr(translate(
        substr(sys_connect_by_path(branch, ','), 1, length(sys_connect_by_path(branch, ',')) - 2) ||
            case when branch = '. ' then '`-' else '|-' end,
        ',.',
        '  '
    ), 4) || operation operation,
    plta.options,
    plta.object_owner,
    plta.object_name,
    plta.object_alias,
    plta.cardinality,
    plta.other_tag,
    plta.access_predicates,
    plta.filter_predicates,
    plta.partition_start,
    plta.partition_stop,
    plta.partition_id
from
    (
        select
            sql_hash_value,
            sql_id,
            operation_id,
            sum(tempseg_size/1048576) temp_mb,
            sum(actual_mem_used/1024/1024) mem_mb
        from
            v$sql_workarea_active
        group by
            sql_hash_value,
            sql_id,
            operation_id
    ) woac,
    (
        select
            pl.*,
            nvl2(lead(pl.id) over (partition by pl.parent_id order by pl.position), '| ', '. ') branch
        from
            v$sql_plan pl,
            sqlid
        where
            pl.sql_id = sqlid.sql_id and
            pl.child_number = 0
    ) plta
where
    woac.sql_hash_value (+) = plta.hash_value and
    woac.sql_id (+) = plta.sql_id and
    woac.operation_id (+) = plta.id
connect by
    prior plta.id = plta.parent_id
start with
    plta.parent_id is null
order siblings by plta.position;