Oracle性能问题一般排查方法

alter session set statistics_level=ALL;

col plan_table_output format A160
set linesize 300\r
col plan_table_output format A160\r
select * from table(dbms_xplan.DISPLAY_CURSOR('&sql_id', &child_number, 'ALL LAST'));


（4）硬解析等情况
select to_char(FORCE_MATCHING_SIGNATURE) as FORCE_MATCHING_SIGNATURE,
count(1) as counts
from v$sql
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE <> EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > 50
order by 2 desc;


select substr(sql_text,0,50),count(1)
FROM v$sqlarea
group by substr(sql_text,0,50)
order by 2 desc;

（5）其他情况等

2、等待事件(历史)

提取awr或者ash观察，或者直接用dba_hist_*视图查看如下：

--查看dba_hist_snapshot视图查看snap_id和时间的对应关系
select * from dba_hist_snapshot;

--创建快照
exec dbms_workload_repository.create_snapshot();

--查看等待事件的sql_id排行
col event for a50
select * from (
select event,sql_id,count(1) from dba_hist_active_sess_history t
where snap_id between &snap_id_start and &snap_id_end
and event is not null
and sql_id is not null
group by event,sql_id
order by count(1) desc
)
where rownum<20;

----历史等待事件及sql
with tmp as
(
select t.instance_number,t.event,t.sql_id,count(1) cnt
from dba_hist_active_sess_history t
where t.snap_id between 7104 and 7105
and t.event like '%ITL%'
and t.sql_id is not null
group by instance_number,event,t.sql_id
)
select t1.*,t2.sql_text
from tmp t1,dba_hist_sqltext t2
where t1.sql_id = t2.sql_id
order by cnt desc

--查看sql绑定变量
set linesize 400
col NAME for a10
col value_string for a70
select distinct instance_number,
sql_id,
name,
datatype_string,
last_captured,
value_string
from dba_hist_sqlbind t
where sql_id = '&slq_id'
and t.SNAP_ID between &snap_id_start and &snap_id_end
and instance_number = &inst_id
order by LAST_CAPTURED;

-----快照时间的sql执行时间及逻辑读物理读的统计
SELECT T.SQL_ID,
T.EXECUTIONS_DELTA EXEC_CNT, --快照时间内执行总次数
ROUND(ELAPSED_TIME_DELTA / 1000000, 2) EXEC_TIME, --快照时间内执行总时间 秒
ROUND(ELAPSED_TIME_DELTA /
DECODE(T.EXECUTIONS_DELTA, 0, 1, T.EXECUTIONS_DELTA) /
1000000,
2) EXEC_PER, --快照时间内执行平均时间 秒
PARSING_SCHEMA_NAME SCHEMA,
ROUND(DISK_READS_DELTA ,2) DISK_READS, ----快照时间内 磁盘读 io高
ROUND(BUFFER_GETS_DELTA , 2) BUFFER_GETS, ----快照时间内 逻辑读 cpu高
T1.SQL_TEXT,
to_char(substr(T1.SQL_TEXT,0,80))
FROM DBA_HIST_SQLSTAT T
JOIN DBA_HIST_SQLTEXT T1
ON T.SQL_ID = T1.SQL_ID
WHERE T.SNAP_ID = 6825 --快照时间
AND T.PARSING_SCHEMA_NAME NOT IN ('SYS', 'SYSTEM')
AND T.INSTANCE_NUMBER = 1 --节点
AND T.EXECUTIONS_DELTA >= 0 --快照时间内 sql执行总次数
————————————————
版权声明：本文为CSDN博主「翔之天空」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/fly43108622/article/details/86595006