Диагностика работы БД

На продуктивной среде достаточно часто возникает необходимость в диагностике проблем работы системы. Это проблемные запросы, блокировки, сессии в состоянии rollback и т.п.
Приведу несколько полезных запросов для 11 версии Oracle и кластерной среды.

Запрос по поиску проблемных запросов:

select DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => sqlm.sql_id, type=>'TEXT', report_level=>'ALL', sql_plan_hash_value=>sqlm.sql_plan_hash_value, sql_exec_id=>sqlm.sql_exec_id) stat,
sqlm.* from gv$sql_monitor sqlm
where last_refresh_time > sysdate-10/(60*24)
and status = 'DONE (ERROR)'
order by sql_exec_start desc;

Поиск блокирующей сущность сессию

select a.inst_id, a.sid, a.serial#
from gv$session a, gv$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and a.inst_id = b.inst_id
and OBJECT_NAME= :pObjectName;

Поиск сущности с блокировкой enq TM - contention:

SELECT l.sid, s.blocking_session blocker, SUBSTR(s.event,1,20),
l.type, l.lmode, l.request, o.object_name, o.object_type
FROM gv$lock l, dba_objects o, gv$session s
WHERE UPPER(s.username) = UPPER(:puser)
and l.type = 'TM'
AND l.id1 = o.object_id (+)
AND l.sid = s.sid and s.sid = :psid
ORDER BY sid, type;

Поиск блокирующих сессий:

select distinct
       o.object_name,
       sh.username || '(' || sh.sid || ',' || sh.serial# || ')' Holder,
       sh.osuser,
       sw.username || '(' || sw.sid || ',' || sw.serial# || ')' Waiter,
decode(lh.lmode,
              1,'null',
              2,'row share',
              3,'row exclusive',
              4,'share',
              5,'share row exclusive',
              6,'exclusive') Lock_Type
from v$session   sw,
     v$lock      lw,
     all_objects o,
     v$session   sh,
     v$lock      lh
where lh.id1 = o.object_id
      and lh.id1 = lw.id1
      and sh.sid = lh.sid
      and sw.sid = lw.sid
      and sh.lockwait is null
      and sw.lockwait is not null
      and lh.type = 'TM'
      and lw.type = 'TM';

Поиск взаимоблокирующих сессий:

select dbs.inst_id,
dbs.sid holding_sid, dbs.serial# holding_serial#, dbs.username holding_user,
       (select count(sid) from gv$session where blocking_session = dbs.sid and blocking_instance = dbs.inst_id) sessions_blocked,
       dbs.sql_id holding_sql_id, dbs.wait_class holding_class, dbs.event holding_event, dbs.seconds_in_wait holding_secs,
       dws.sid waiting_sid, dws.serial# waiting_serial#, dws.username waiting_user, dws.sql_id waiting_sql_id, dws.wait_class waiting_class, dws.event waiting_event, dws.seconds_in_wait waiting_secs
from gv$session dbs, gv$session dws
where dws.blocking_session = dbs.sid
and dws.blocking_instance = dbs.inst_id
order by dbs.sid, dws.seconds_in_wait desc

Поиск rollback-сессий:

SELECT s.username,
s.sid,
s.inst_id,
start_time,
used_ublk,
CASE
WHEN BITAND (flag, POWER (2, 7)) > 0 THEN 'RB in Progress'
ELSE 'Not Rolling Back'
END
AS "F Status",
sa.sql_text
FROM GV$TRANSACTION d, gv$session s, gv$sqlarea sa
WHERE s.saddr = D.SES_ADDR AND sa.sql_id = s.sql_id;

Запрос для определения последовательности операций выполняемых с кластера Weblogic. Полезен для оптимизации пользовательских запросов, когда разработчик БД не знает тонкостей алгоритмов работы приложения:

WITH ss
AS
(
SELECT s.* FROM gv$session s WHERE 0=0 AND s.machine LIKE 'prod-asr-servic%.serbao.fe'
)
SELECT t.* FROM gv$active_session_history t, ss WHERE 0=0 AND t.session_id = ss.sid
--AND t.sql_id IN ('1fab1xh4nb9xh','38319htpnu5q7')
--AND t.session_id IN ('307','64')
--AND to_date(t.sql_exec_start,'dd.mm.yyyy hh24:mi:ss') > to_date('04.08.2014 11:30:00','dd.mm.yyyy hh24:mi:ss')
ORDER BY t.sample_time DESC;

Запрос для извлечения кода SQL-запросов:

SELECT t.* FROM DBA_HIST_ACTIVE_SESS_HISTORY t
WHERE 0=0 AND t.sql_id LIKE '%status_id = (select s.status_id from dictstatus s where %';
SELECT t.* FROM DBA_HIST_ACTIVE_SESS_HISTORY t WHERE 0=0 AND t.sql_id IN ('bzz7bp7ju6pad');
SELECT t.* FROM gv$sqltext t WHERE 0=0 AND t.sql_id = 'bzz7bp7ju6pad' ORDER BY t.inst_id, t.piece;
SELECT t.* FROM gv$sqltext t WHERE 0=0 AND t.sql_text LIKE '%update contract%';
SELECT t.* FROM Dba_Hist_Sqltext t WHERE 0=0 AND t.sql_id IN ('bzz7bp7ju6pad');

Запрос выдаёт блокирующего и заблокированного пользователей:

SELECT /*+ rule */
s1.username blocker, s1.sid sid_blocker, s1.serial#, s1.MACHINE, s1.MODULE, s1.ACTION,
s2.username is_blocked, s2.sid sid_blocked, s2.serial#, s2.MACHINE, s2.MODULE, s2.ACTION,
s1.SQL_HASH_VALUE sql_blocker,
s2.SQL_HASH_VALUE sql_blocked
FROM v$lock a, v$lock b, v$session s1, v$session s2
WHERE a.id1 = b.id1 AND a.id2 = b.id2 AND b.request > 0
AND a.sid=s1.sid AND b.sid=s2.sid AND a.sid<>b.sid;