oracle Kglhd,oracle常用的监控语句

--根据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);