Oracle 排查慢SQL
select * from v
s
q
l
a
r
e
a
w
h
e
r
e
r
o
w
n
u
m
<
10
;
s
e
l
e
c
t
∗
f
r
o
m
v
sqlarea where rownum<10; select * from v
sqlareawhererownum<10;select∗fromvsql where rownum<10;
select * from dba_hist_sqltext where rownum<10;
select * from dba_hist_active_sess_history where rownum<10;
select * from v$sql_plan where rownum<10;
select sa.sql_fulltext,
sa.sql_id,
u.username,
sa.sorts as “排序次数”,
sa.executions as “执行次数”,
sa.px_servers_executions as “并行器执行次数”,
sa.fetches as “sql语句提取次数”,
sa.rows_processed as “sql语句的处理行数”
from v$sqlarea sa
left join all_users u on sa.PARSING_USER_ID=u.user_id
where rownum<=20 order by sa.executions desc;
select sql_fulltext,sql_id,
u.username,
sorts as “排序次数”,
fetches as “sql语句提取次数”,
executions as “执行次数”,
px_servers_executions as “并行器执行次数”,
round(cpu_time/1000000,2) as “SQL语句占用CPU的时间”,
round(elapsed_time/1000000,2) as “SQL语句执行的总时间”,
round((elapsed_time/executions)/1000000,2) as “平均执行时长”,
object_status as “对象状态”,
last_active_time as “最后的活动时间”
from v$sql s
left join all_users u on s.PARSING_USER_ID=u.USER_ID
where to_char(last_active_time,‘YYYYMMDD’)>'20240612’and executions >0 and round((elapsed_time/executions)/1000000,2)>5
order by round((elapsed_time/executions)/1000000,2) desc;
#db_hist_active_sess_history 视图是Oracle数据库中的一个视图，用于存储活动会话历史的快照数据
select session_id,session_serial#,user_id,sql_id,
sample_time as “采样时间”,
instance_number as “实例编号”,
sql_opname as “SQL操作名称”,
sql_exec_start as “SQL执行开始时间”,
wait_time as “等待时长/0.01s”,
session_state as “会话状态”,
blocking_session_status as “阻塞会话状态”,
blocking_session as “阻塞当前会话的会话ID”,
blocking_session_serial# as “阻塞当前会话的会话序列号”
from dba_hist_active_sess_history;
select sql_id,
timestamp as “执行计划的时间”,
operation as “操作类型”,
options as “操作类型”,
object_node as “分布式节点”,
object_owner as “执行的用户”,
object_name as “对象名称”,
cost as “估算成本”,cardinality as “估算输出行数”,time as “估算时间微妙”,bytes as “估算输出的字节数”,cpu_cost as “估算的CPU花费”,io_cost as “估算的IO花费”
from v$sql_plan where operation=‘TABLE ACCESS’;
select sql_id,sql_text,session_serial#,status,
elapsed_time as “花费时间微妙”,
cpu_time as “cpu占用时间”,buffer_gets as “缓存获取次数”,disk_reads as “磁盘读取次数”,user_io_wait_time as “IO等待时长”,plsql_exec_time as “plsql执行时长”
from v$sql_monitor;
set autotrace on
执行sql 查看执行计划
set autotrace off
explain plan for
select * from table(DBMS_EPLAN.DISPLAY);