oracle Kglhd,oracle常用的监控语句

我就是我 2023-01-18 15:29 157阅读 0赞

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

oracle Kglhd,oracle常用的监控语句

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 oracle Kglhd,oracle常用的监控语句

相关常用的oracle语句

相关 [Oracle] Oracle性能监控语句

相关常用的SQL语句

相关 oracle学习：记录几条常用的语句

相关 oracle数据库常用的99条查询语句（转）

相关常用的sql语句

相关常用的sql语句

相关常用的DML语句

相关常用的DDL语句