詳解Oracle數據庫如何準確定位 SQL 執行計劃異常過程

很多時候我們找到一些異常的sql，想要去看他的執行計劃是不是最優的或者歷史執行計劃情況，這時候可以怎么獲得呢？

select sql_id, event, count(*)

from v$session

where wait_class <> 'Idle'

group by sql_id, event

order by 3 desc;

對Idle的等待事件也不要過濾掉，比如SQL開啟并行后的等待事件PX Deq: Execution Msg 等

select sql_id,event ,count(*) from v$session group by sql_id,event order by 3 desc;

揚州數據恢復

通過抓取的 TOP SQL_ID，再通過歷史的單次執行的影響時間、物理讀、邏輯讀、返回行數等來初步判斷該 SQL_ID 的執行計劃是否是最高效的。

抓取 top sql 的辦法很多，還可以通過 ash、awr、addm 和 v$類系視圖來判斷，下面的一種獲取系統等待的 SQL 也可以參考：

select a.sid,

a.username,

a.terminal,

a.machine,

a.module,

a.event,

a.status,

b.spid,

c.sql_id,

to_char(LAST_CALL_ET) as seconds,

c.last_active_time

from v$session a, v$process b, v$sqlarea c

where a.paddr = b.addr(+)

and a.sql_hash_value = c.hash_value(+)

and a.sql_address = c.address(+)

and a.type = 'USER'

and a.event not like 'SQL*Net%'

order by c.sql_id, a.machine

select sql_id,

sql_profile， executions,

plan_hash_value,

常州數據恢復

elapsed_time / DECODE(executions, 0, 1, EXECUTIONS) / 1000 elasp_time_ms,

buffer_gets / DECODE(executions, 0, 1, EXECUTIONS),

disk_reads / DECODE(executions, 0, 1, EXECUTIONS),

cpu_time / DECODE(executions, 0, 1, EXECUTIONS) / 1000 cpu_time_ms,

last_load_time,

last_active_time,

sql_text,

child_number

from v$sql

where SQL_ID IN ('&sql_id');

檢查 SQL 每次執行時間是否過長，邏輯讀，物理讀是否很大？建議對 elasped_time 和cpu_time 時間粒度取 ms，不要取 s，因為對于有些高并發的 SQL，業務要求響應時間都是幾ms 到幾十 ms，如果用 s 作為單位則不利于判斷 SQL 的性能變化。

如果查看某個 SQL 的歷史性能，需要考慮的視圖是 DBA_HIST_SQLSTAT 視圖，如下查看某個 SQL 的歷史執行信息：

select * from table(dbms_xplan.display_awr('&sql_id'));

select to_char(begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') begin_time,

a.sql_id,

a.plan_hash_value,

a.instance_number,

module,

plan_hash_value,

EXECUTIONS_DELTA exec,

decode(EXECUTIONS_DELTA,

0,

buffer_gets_deltA,

round(BUFFER_GETS_DELTA / EXECUTIONS_DELTA)) per_get,

decode(EXECUTIONS_DELTA,

0,

東臺數據恢復

ROWS_PROCESSED_DELTA,

round(ROWS_PROCESSED_DELTA / EXECUTIONS_DELTA, 3)) per_rows,

decode(EXECUTIONS_DELTA,

0,

ELAPSED_TIME_DELTA,

round(ELAPSED_TIME_DELTA / EXECUTIONS_DELTA / 1000, 2)) time_ms,

decode(EXECUTIONS_DELTA,

0,

DISK_READS_DELTA,

round(DISK_READS_DELTA / EXECUTIONS_DELTA, 2)) per_read

from dba_hist_sqlstat a, DBA_HIST_SNAPSHOT b

where a.snap_id = b.snap_id

and a.instance_number = b.instance_number

and a.sql_id = '&sql_id'

order by 1 desc

通過 dba_hist_sqlstat 視圖可以得知該 SQL_ID 的歷史執行信息，單次邏輯讀、物理讀、返回行數、單次響應時間，每個 awr 周期內的執行次數、執行計劃 plan hash value 等變化趨勢

后面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注下~