--根据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); |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9537053/viewspace-730685/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9537053/viewspace-730685/