**–查询阻塞和被阻塞的session,支持RAC,按instance分组
SELECT a.INST_ID,
a.sid,
a.SERIAL#,
a.USERNAME,
a.SQL_ID,
a.PROGRAM,
a.EVENT,
a.BLOCKING_SESSION,
a.WAIT_TIME_MICRO
FROM GV$SESSION A
WHERE (A.INST_ID, a.SID) in
(select b.BLOCKING_INSTANCE, b.BLOCKING_SESSION from gv$session b)
union all
select a.INST_ID,
a.sid,
a.SERIAL#,
a.USERNAME,
a.SQL_ID,
a.PROGRAM,
a.EVENT,
a.BLOCKING_SESSION,
a.WAIT_TIME_MICRO
FROM GV$SESSION A
where a.BLOCKING_SESSION is not null
order by BLOCKING_SESSION nulls first;
–最近30分钟内ASH采样到的等待事件排名,支持RAC,按instance分组
select *
from (select inst_id,
rank() over(partition by inst_id order by cnt desc) rk,
event,
cnt
from (select ash.INST_ID, ash.EVENT, count(*) CNT
from gv$active_session_history ash
where ash.SAMPLE_TIME > sysdate - 1 / 24 / 60 * 30
and event is not null
group by ash.INST_ID, ash.EVENT))
where rk <= 10;
–某段事件内ASM采样到的等待事件排名,并查询采样中,产生此等待事件的SQL,支持rac,按 instance分组
select a.inst_id, a.event, a.sql_id, a.sql_cnt, b.cnt event_cnt, sql_rk
from (select inst_id,
event,
sql_id,
sql_cnt,
rank() over(partition by event order by sql_cnt desc) sql_rk
from (select ash.INST_ID, ash.EVENT, ash.SQL_ID, count(*) sql_cnt
from gv$active_session_history ash
where to_char(ash.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') >
'2021-07-25 14:00:00'
AND to_char(ash.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') <
'2021-07-25 16:00:00'
AND EVENT IS NOT NULL
GROUP BY ASH.INST_ID, ASH.EVENT, ASH.SQL_ID)) A,
(SELECT INST_ID, EVENT, CNT
FROM (SELECT INST_ID,
rank() over(partition by INST_ID order by cnt desc) rk,
EVENT,
CNT
FROM (SELECT ASH.INST_ID, ASH.EVENT, COUNT(*) CNT
FROM GV$ACTIVE_SESSION_HISTORY ASH
where to_char(ash.SAMPLE_TIME,
'YYYY-MM-DD HH24:MI:SS') >
'2021-07-25 14:00:00'
AND to_char(ash.SAMPLE_TIME,
'YYYY-MM-DD HH24:MI:SS') <
'2021-07-25 16:00:00'
AND EVENT IS NOT NULL
GROUP BY ASH.INST_ID, ASH.EVENT))
WHERE RK <= 3) B
WHERE A.INST_ID = B.INST_ID
AND A.EVENT = B.EVENT
AND A.SQL_RK <= 5
ORDER BY INST_ID, EVENT_CNT DESC, SQL_CNT DESC, SQL_RK;
–查看过去15天sql执行的hash_plan_id和资源消耗统计变化
select sql_id,
endtime,
plan_hash_value,
nvl2(exed, exed, 0) "执行次数",
nvl2(ems, ems, 0) "单次执行时间(ms)",
nvl2(cms, cms, 0) "平均CCWAIT(ms)",
nvl2(ams, ams, 0) "平均APWAIT(ms)",
nvl2(clms, clms, 0) "平均CMWAIT(ms)",
nvl2(ioms, ioms, 0) "平均IOWAIT(ms)",
nvl2(ctms, ctms, 0) "平均CPU_WAIT(ms)",
nvl2(bfgets, bfgets, 0) "平均buffer gets",
nvl2(drd, drd, 0) "平均disk reads",
nvl2(sd, sd, 0) "平均sort",
nvl2(fet, fet, 0) "平均fetch",
nvl2(rpd, rpd, 0) "平均rows process",
snap_id
from (select a.sql_Id,
to_char(b.end_interval_time, 'yyyymmdd hh24:mi:ss') endtime,
a.executions_delta exed,
a.plan_hash_value,
round(decode(a.executions_delta,0,0,a.elapsed_time_delta / a.executions_delta) / 1000,2) ems,
round(decode(a.executions_delta,0,0,a.ccwait_delta / a.executions_delta) / 1000,2) cms,
round(decode(a.executions_delta,0,0,a.apwait_delta / a.executions_delta) / 1000,2) ams,
round(decode(a.executions_delta,0, 0,a.clwait_delta / a.executions_delta) / 1000,2) clms,
round(decode(a.executions_delta,0, 0,a.iowait_delta / a.executions_delta) / 1000,2) ioms,
round(decode(a.executions_delta, 0,0, a.cpu_time_delta / a.executions_delta) / 1000,2) ctms,
round(decode(a.executions_delta, 0, 0,a.buffer_gets_delta / a.executions_delta),2) bfgets,
round(decode(a.executions_delta,0, 0, a.disk_reads_delta / a.executions_delta),2) drd,
round(decode(a.executions_delta,0,0, a.sorts_delta / a.executions_delta),2) sd,
round(decode(a.executions_delta,0, 0,a.fetches_delta / a.executions_delta),2) fet,
round(decode(a.executions_delta,0,0, a.rows_processed_delta / a.executions_delta), 4) rpd,
b.snap_id
from dba_hist_sqlstat a, dba_hist_snapshot b
where sql_id = '62qcz0vz20a3t'
and a.snap_id = b.snap_id
and a.instance_number = b.instance_number
--and b.instance_number=1
and a.executions_delta <> 0
and b.end_interval_time > sysdate - 15)
order by snap_id
–查询某个会话的锁
select /*+rule*/SESSION_ID,OBJECT_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS,LOCKED_MODE from gv$locked_object where session_id=&sid;
–查询TMTX锁
select /*+rule*/* from v$lock where ctime >100 and type in ('TX','TM') order by 3,9;
–查询数据库中的锁
select /*+rule*/s.sid,p.spid,l.type,round(max(l.ctime)/60,0) lock_min,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name from v$session s, v$process p,v$lock l,v$locked_object o,dba_objects b where o.SESSION_ID=s.sid and s.sid=l.sid and o.OBJECT_ID=b.OBJECT_ID and s.paddr = p.addr and l.ctime >100 and l.type in ('TX','TM','FB') group by s.sid,p.spid,l.type,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name order by 9,1,3,4;
说明:
dba_hist_active_sess_history/v$active_session_history查看SQL执行历史情况
v$session_wait/v$session_event查看数据库会话的等待事件
v$sga_target_advice数据库sga优化顾问视图
v$pga_target_advice数据库pga优化顾问视图
v$memory_target_advice内存优化顾问视图
dba_data_files查询表空间和对应数据文件
v$session_longops记录执行超过5s的sql,可以查询一个SQL语句执行了多长时间以及还要执行多长时间
v$fast_start_transactions/v$fast_start_servers可以查看所有回滚的事务
v$log视图可以查找到联机重做日志的日志序列号v$archived_log视图或
v$log_history视图中可以查找给定的归档重做日志的日志序列号
user_tab_columns视图将显示当前用户模式下的每张表的每一列的详细数据情况
gv$memory_dynamic_components记录内存变更的视图
gv$sql_monitor SQL监视视图
dba_tab_statistics表的信息统计**