DBA不常用但及时可用的SQL语句集锦（2）

最新推荐文章于 2022-03-05 09:34:58 发布

Seven_Ginna

最新推荐文章于 2022-03-05 09:34:58 发布

阅读量1.6k

点赞数 1

分类专栏： Oracle performance tuning

本文链接：https://blog.csdn.net/sunjiapeng/article/details/8968317

版权

Oracle performance tuning 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

本文（偏于调优）介绍一些DBA日常运维中不常使用，但是遇到特殊情况可以可以及时使用的SQL语句；

注：本文主要针对Oracle 11g版本

Oracle检测死锁的Sql：

SELECT SID, DECODE (BLOCK, 0, 'NO', 'YES') blocker, DECODE (request, 0, 'NO', 'YES') waiter

FROM v$lock

WHERE request > 0 OR BLOCK > 0

ORDER BY BLOCK DESC

SELECT bs.username "Blocking User", bs.username "DB User",
ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",
bs.serial# "Serial#", bs.sql_address "address",
bs.sql_hash_value "Sql hash", bs.program "Blocking App",
ws.program "Waiting App", bs.machine "Blocking Machine",
ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
ws.osuser "Waiting OS User", bs.serial# "Serial#", ws.serial# "WSerial#",
DECODE (wk.TYPE, 'MR', 'Media Recovery', 'RT', 'Redo Thread', 'UN', 'USER Name', 'TX',
         'Transaction', 'TM', 'DML', 'UL', 'PL/SQL USER LOCK', 'DX', 'Distributed Xaction',
         'CF', 'Control FILE', 'IS', 'Instance State', 'FS', 'FILE SET', 'IR', 'Instance Recovery',
         'ST', 'Disk SPACE Transaction', 'TS', 'Temp Segment', 'IV', 'Library Cache Invalidation',
         'LS', 'LOG START OR Switch', 'RW', 'ROW Wait', 'SQ', 'Sequence Number', 'TE', 'Extend TABLE',
         'TT', 'Temp TABLE', wk.TYPE ) lock_type, DECODE (hk.lmode, 0, 'None', 1, 'NULL', 2,
         'ROW-S (SS)', 3, 'ROW-X (SX)', 4, 'SHARE', 5, 'S/ROW-X (SSX)', 6, 'EXCLUSIVE', TO_CHAR (hk.lmode) ) mode_held,
DECODE (wk.request, 0, 'None', 1, 'NULL', 2, 'ROW-S (SS)', 3, 'ROW-X (SX)', 4, 'SHARE', 5, 'S/ROW-X (SSX)', 6,
         'EXCLUSIVE', TO_CHAR (wk.request) ) mode_requested, TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
DECODE (hk.BLOCK, 0, 'NOT Blocking', /**//* Not blocking any other processes */ 1, 'Blocking',
/**//* This lock blocks other processes */ 2, 'Global', /**//* This lock is global, so we can't tell */ TO_CHAR (hk.BLOCK) ) blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.BLOCK = 1
AND hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE(+) = hk.TYPE
AND wk.id1(+) = hk.id1
AND wk.id2(+) = hk.id2
AND hk.SID = bs.SID(+)
AND wk.SID = ws.SID(+)
AND (bs.username IS NOT NULL)
AND (bs.username <> 'SYSTEM')
AND (bs.username <> 'SYS')
ORDER BY 1

检查应用系统中无效对象情况（产生无效对象编译脚本情况）：

SELECT 'PROMPT Compile VIEW '||OBJECT_NAME||' ...' || CHR(10)||

'ALTER VIEW '||OBJECT_NAME || ' COMPILE;'||CHR(10)

||'SHOW ERRORS;'||CHR(10) "--COMPILE VIEW "

FROM DBA_OBJECTS

WHERE STATUS <> 'VALID'

AND OBJECT_TYPE = 'VIEW'

ORDER BY OBJECT_NAME

SELECT 'PROMPT Compile TRIGGER '||OBJECT_NAME||' ...'||CHR(10)||

'ALTER TRIGGER '||OBJECT_NAME||' COMPILE;'||CHR(10)

||'SHOW ERRORS;'||CHR(10) "--COMPILE TRIGGER "

FROM DBA_OBJECTS

WHERE STATUS <> 'VALID'

AND OBJECT_TYPE = 'TRIGGER'

ORDER BY OBJECT_NAME

查询Oracle隐藏参数：

select x.ksppinm, y.ksppstvl, x.ksppdesc

from x$ksppi x , x$ksppcv y

where x.indx = y.indx

and y.inst_id = userenv('Instance')

and x.inst_id = userenv('Instance')

and x.ksppinm like '\_%' escape '\'

或：

SELECT ksppinm, ksppstvl, ksppdesc FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND ksppinm = ' _serial_direct_read'
/

查询数据库SCN的headroom（是指Maximum Reasonable SCN 与当前数据库SCN的差值）是多少：

select
     ((((
      ((to_number(to_char(cur_date,'YYYY'))-1988)*12*31*24*60*60) +
      ((to_number(to_char(cur_date,'MM'))-1)*31*24*60*60) +
      (((to_number(to_char(cur_date,'DD'))-1))*24*60*60) +
      (to_number(to_char(cur_date,'HH24'))*60*60) +
      (to_number(to_char(cur_date,'MI'))*60) +
      (to_number(to_char(cur_date,'SS')))
      ) * (16*1024)) - 12821569053984)
     / (16*1024*60*60*24)
     ) headroom
     from (select to_date('2012-05-30 15:09:57','yyyy-mm-dd hh24:mi:ss') cur_date from dual)

查询数据库当前最大的可能SCN“最大合理SCN”：

col for 999,999,999,999,999,999

select
(
    (
        (
            (
                (
                    (
                        to_char(sysdate,'YYYY')-1988
                    )*12+
                to_char(sysdate,'mm')-1
                )*31+to_char(sysdate,'dd')-1
            )*24+to_char(sysdate,'hh24')
        )*60+to_char(sysdate,'mi')
    )*60+to_char(sysdate,'ss')
) * to_number('ffff','XXXXXXXX')/4 scn
from dual
/

根据SCN查询表T更多的历史记录：

Select versions_xid,versions_startscn,versions_endscn,
DECODE(versions_operation,'I','Insert','U','Update','D','Delete', 'Original') "Operation",owner
from t versions between scn minvalue and maxvalue
/

查看正在执行的SQL语句的执行时间和SQL语句的等待事件的信息：

select a.sql_text,b.status,b.last_call_et,b.event
from v$sql a,v$session b
where a.sql_id=b.sql_id and b.sid= 41
/

或

select event,p1,p1text,p2,p2text,p3,p3text,wait_time,seconds_in_wait,state

from v$session_wait

where sid=41

查看session自运行以来的各种资源消耗统计数据：

select a.sid,b.name,a.value

from v$sesstat a,v$statname b

where a.sid=41

and a.statistic#=b.statistic#

and b.name in ('consistent gets','physical reads','parse count(total)','parse count(hard)')