经常使用的Oracle监控语句

转载：

?

经常使用的Oracle监控语句

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

客户有时会说服务器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.sid = a.session_id
?? AND s.paddr = p.addr
?? AND (a.mode_held = 'Exclusive' OR a.mode_requested = 'Exclusive')
?ORDER BY s.USERNAME, a.NAME;

--查询事务使用的回滚段
SELECT s.username, s.sid, s.serial#, t.ubafil "UBA filenum",
?????? t.ubablk "UBA Block number", t.used_ublk "Number of undo Blocks Used",
?????? t.start_time, t.status, t.start_scnb, t.xidusn rollid, r.name rollname
? FROM v$session s, v$transaction t, v$rollname r
?WHERE s.saddr = t.ses_addr AND t.xidusn = r.usn;
####################################################################################################


--查询LIBRARY CACHE PIN等待事件等待的对象
--视图缩写:[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject
SELECT /*+ RULE*/ addr, kglhdadr, kglhdpar, kglnaobj, kglnahsh, kglhdobj
? FROM x$kglob
?WHERE kglhdadr IN (SELECT p1raw
????????????????????? FROM v$session_wait
???????????????????? WHERE event LIKE '%library%');

--查询LIBRARY CACHE PIN等待事件中持有被等待对象的SESSION信息
--视图缩写:[K]ernel [G]eneric [L]ibrary Cache Manager Object [P]i[N]s
SELECT /*+ RULE*/ a.SID, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse,
?????? b.kglpnses, b.kglpnhdl, b.kglpnlck, b.kglpnmod, b.kglpnreq
? FROM v$session a, x$kglpn b
?WHERE a.saddr = b.kglpnuse
?? AND b.kglpnmod <> 0
?? AND b.kglpnhdl IN (SELECT p1raw
??????????????????????? FROM v$session_wait
?????????????????????? WHERE event LIKE '%library%');

--查询LIBRARY CACHE PIN等待事件中持有被等待对象的SESSION执行的SQL语句
SELECT sql_text
? FROM v$sqlarea
?WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN (
????????? SELECT sql_address, sql_hash_value
??????????? FROM v$session
?????????? WHERE SID IN (
??????????????????? SELECT /*+ RULE*/ SID
????????????????????? FROM v$session a, x$kglpn b
???????????????????? WHERE a.saddr = b.kglpnuse
?????????????????????? AND b.kglpnmod <> 0
?????????????????????? AND b.kglpnhdl IN (SELECT p1raw
??????????????????????????????????????????? FROM v$session_wait
?????????????????????????????????????????? WHERE event LIKE '%library%')));

--查询哪个SESSION正在使用某个对象(LIBRARY CACHE)
SELECT DISTINCT s.sid,
??????????????? s.username,
??????????????? s.logon_time,
??????????????? s.osuser,
??????????????? s.program,
??????????????? b.kglnahsh as SQL_HASH_VALUE,
??????????????? b.kglnaobj as SQL_TEXT
? FROM v$session s, x$kglpn n, x$kglob b
?WHERE n.kglpnuse = s.saddr
?? AND upper(b.kglnaobj) LIKE upper('%&OBJECT_NAME%')
?? AND n.kglpnhdl = b.kglhdadr;

--查询V$SESSION_WAIT用户PIN住了哪些对象(LIBRARY CACHE)
SELECT DISTINCT s.sid,
??????????????? s.username,
??????????????? s.logon_time,
??????????????? s.osuser,
??????????????? s.program,
??????????????? n.kglpnmod,
??????????????? b.kglnahsh AS SQL_HASH_VALUE,
??????????????? b.kglnaobj AS SQL_TEXT
? FROM v$session s, x$kglpn n, x$kglob b
?WHERE n.kglpnuse = s.saddr
?? AND n.kglpnhdl = b.kglhdadr
?? AND s.sid IN (SELECT sid
?????????????????? 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 s.username;

--查询哪些大对象被载入SHARED POOL时导致其它对象被老化
SELECT s.sid, s.username, s.logon_time, s.osuser, s.program,
?????? k.ksmlrcom, k.ksmlrsiz, k.ksmlrnum, k.ksmlrhon, k.ksmlrses
? FROM x$ksmlru k, v$session s
?WHERE s.saddr = k.ksmlrses
?? AND ksmlrsiz > 0;
####################################################################################################


--查询Schema哪些表是全表扫描
SELECT o.name, x.tch
? FROM obj$ o, x$bh x, dba_users u
?WHERE x.obj = o.dataobj#
?? AND STANDARD.bitand(x.flag, 524288) > 0
?? AND u.username = UPPER('&username')
?ORDER BY x.tch DESC;

--查询低效率的SQL(BUFFER_GETS排序)
SELECT *
? FROM (SELECT s.sid,
?????????????? b.spid,
?????????????? s.sql_hash_value,
?????????????? q.sql_text,
?????????????? q.executions,
?????????????? q.buffer_gets,
?????????????? ROUND(q.buffer_gets / q.executions) AS buffer_per_exec,
?????????????? ROUND(q.elapsed_time / q.executions) AS cpu_time_per_exec,
?????????????? q.cpu_time,
?????????????? q.elapsed_time,
?????????????? q.disk_reads,
?????????????? q.rows_processed
????????? FROM v$session s, v$process b, v$sql q
???????? WHERE s.sql_hash_value = q.hash_value
?????????? AND s.paddr = b.addr
?????????? AND s.status = 'ACTIVE'
?????????? AND s.TYPE = 'USER'
?????????? AND q.buffer_gets > 0
?????????? AND q.executions > 0
???????? ORDER BY buffer_per_exec DESC)
?WHERE ROWNUM <= 10;
####################################################################################################


--监控BufferCache命中率
SELECT a.value + b.value logical_reads, c.value phys_reads,
?????? ROUND (100 * (1 - c.value / (a.value + b.value)), 4) hit_ratio
? FROM v$sysstat a, v$sysstat b, v$sysstat c
?WHERE a.NAME = 'db block gets'
?? AND b.NAME = 'consistent gets'
?? AND c.NAME = 'physical reads';

--监控LibraryCache命中率
SELECT SUM (pins) total_pins, SUM (reloads) total_reloads,
?????? SUM (reloads) / SUM (pins) * 100 libcache_reload_ratio
? FROM v$librarycache;

--查询产生的跟踪文件名
SELECT p1.VALUE || '/' || p2.VALUE || '_ora_' || p.spid || '.trc' filename
? FROM v$process p, v$session s, v$parameter p1, v$parameter p2
?WHERE p1.NAME = 'user_dump_dest'
?? AND p2.NAME = 'db_name'
?? AND p.addr = s.paddr
?? AND s.audsid = USERENV ('SESSIONID');

--删除表中的重复记录
DELETE FROM table_name a
????? WHERE ROWID >
?????????????? (SELECT MIN (ROWID)
????????????????? FROM table_name b
???????????????? WHERE b.pk_column_1 = a.pk_column_1
?????????????????? AND b.pk_column_2 = a.pk_column_2);