详解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方面的内容，感兴趣的朋友可以关注下~