最近项目需要，性能、数据准确性等问题一个接一个解决，很累，但我是很看好这种机遇。

客户有时会说服务器CPU占用100%，妈啊，小型机啊，为了要证明是程序问题还是SQL语句的问题，或者是程序死锁问题，通过各种方式进行检查，发现有一个明细表的查询出问题了，真是大快人心!于是把一些在监控过程中学到的知识记录下来，做个标记，日后有用啊，现在的DBA身价不菲。

?

--根据FILE_ID & BLOCK_ID获得对象名称
SELECT /*+ RULE*/ owner, segment_name, segment_type
? FROM dba_extents
?WHERE file_id = &file_id
?? AND &block_id BETWEEN block_id AND block_id + blocks - 1;

--根据操作系统PID,查询SESSION信息
SELECT a.sid, a.serial#, b.spid, a.terminal, a.machine, a.program, a.osuser
? FROM v$session a, v$process b
?WHERE a.paddr = b.addr AND b.spid = '&SPID';

--根据SESSION SID,查询操作系统PID
SELECT a.sid, a.serial#, b.spid, a.terminal, a.machine, a.program, a.osuser
? FROM v$session a, v$process b
?WHERE a.paddr = b.addr AND a.sid = '&SID';

--查询用户正在执行的SQL
SELECT sql_text
? FROM v$sqltext
?WHERE hash_value = (SELECT sql_hash_value
?????????????????????? FROM v$session
????????????????????? WHERE sid = &sid)
?ORDER BY piece;

--查询当前的系统等待事件
SELECT *
? FROM v$session_wait
?WHERE event NOT LIKE '%SQL*Net%'
?? AND event NOT LIKE '%rdbms%'
?? AND event NOT LIKE '%timer%'
?? AND event NOT LIKE '%jobq%'
?ORDER BY event, seconds_in_wait;

--查询详细的当前系统等待事件
SELECT s.sid, s.username, w.seq#, w.event, w.p1text, w.p1, w.p2text, w.p2, w.p3text, w.p3,
?????? w.seconds_in_wait, w.state, s.logon_time, s.osuser, s.program
? FROM v$session s, v$session_wait w
?WHERE s.sid = w.sid
?? AND w.event NOT LIKE '%SQL*Net%'
?? AND w.event NOT LIKE '%rdbms%'
?? AND w.event NOT LIKE '%timer%'
?? AND w.event NOT LIKE '%jobq%'
?ORDER BY w.event, w.seconds_in_wait;

--查询等待db file sequential/scattered read的Session正在执行的SQL
SELECT s.sid, s.username, t.hash_value, t.piece, t.sql_text
? FROM v$session s, v$session_wait w, v$sqltext t
?WHERE s.sid = w.sid
?? AND s.sql_hash_value = t.hash_value
?? AND w.event IN ('db file sequential read', 'db file scattered read')
?ORDER BY s.sid, t.piece;

--查询等待db file sequential/scattered read对应的数据库对象
SELECT /*+ RULE*/ s.sid, s.username, w.seq#, w.event,
?????? d.segment_type, d.owner || '.' || d.segment_name AS segment_name,
?????? w.seconds_in_wait, w.state, s.logon_time
? FROM v$session s, v$session_wait w, dba_extents d
?WHERE s.sid = w.sid
?? AND d.file_id = w.p1
?? AND w.p2 BETWEEN d.block_id AND d.block_id + d.blocks - 1
?? AND w.event IN ('db file sequential read', 'db file scattered read')
?ORDER BY w.event, segment_name;

--查询导致LOCK的SID,SPID,LOCKED_OBJECT,LOCK_TYPE等信息
SELECT /*+ RULE*/
?????? l.sid, p.spid, s.username,s.logon_time, s.osuser, s.program, l.type,
?????? CASE l.TYPE WHEN 'TM' THEN O.object_name WHEN 'TX' THEN '' END as OBJECT_NAME,
?????? DECODE (l.lmode, 0, '0=NONE', 1, '1=NULL', 2, '2=RS', 3, '3=RX', 4, '4=S', 5, '5=SRX', 6, '6=X') lmode,
?????? CASE l.request WHEN 0 THEN '' ELSE 'BLOCKED BY ' || l.id2 END as BLOCKED,
?????? CASE l.block WHEN 0 THEN '' ELSE l.id2 || ' IS BLOCKING' END as BLOCKING,
?????? l.request, l.ctime
? FROM v$lock l, v$session s, dba_objects o, v$process p
?WHERE l.type in ('TX', 'TM')
?? AND s.paddr = p.addr
?? AND l.sid = s.sid
?? AND l.id1 = o.object_id(+)
?ORDER BY s.username, l.sid, l.ctime;

--查询导致DDL LOCK的详细信息
SELECT s.sid, p.spid, s.username, a.owner || '.' || a.NAME AS OBJECT_NAME,
?????? a.TYPE, a.mode_held, a.mode_requested, s.osuser, s.logon_time, s.program
? FROM dba_ddl_locks a, v$session s, v$process p
?WHERE s.si