*********************************查询session信息以及process信息********************************* --根据username查询sid和后台spid select a.username,a.sid,a.serial#,b.spid,a.STATUS,a.OSUSER,a.MACHINE,a.PROGRAM from v$session a,v$process b where a.PADDR=b.ADDR and a.username=&USERNAME; --根据后台spid查询sid select username,sid,serial#,STATUS,OSUSER,MACHINE,PROGRAM from v$session where paddr = (select addr from v$process where spid = &SPID); --根据sid查询后台spid select spid from v$process where addr = (select paddr from v$session where sid = &SID); --查询自己session的后台spid select a.username,a.sid,a.serial#,b.spid,a.STATUS,a.OSUSER,a.MACHINE,a.PROGRAM from v$session a,v$process b where a.PADDR=b.ADDR and a.sid=(select distinct sid from v$mystat); ************************************************************************************************ *********************************查找产生大量物理读的进程********************************* select * from (select * from (select st.sid,st.value,sn.name,s.username,s.logon_time from v$sesstat st,v$statname sn ,v$session s where st.sid=s.sid AND st.statistic#=sn.statistic# and st.value>100000 and s.username is not null and sn.name like '%physical read%' order by 2 desc)); ************************************************************************************** *********************************查找产生redo log过快的进程********************************* col machine format a20 col osuser format a20 set lines 150 select sysdate, se.username, se.sid, se.serial#, se.SQL_HASH_VALUE, se.status, se.machine, se.osuser, round(st.value / 1024 / 1024) redosize, sa.sql_text from v$session se, v$sesstat st, v$sqlarea sa where se.sid = st.sid and st.STATISTIC# = (select STATISTIC# from v$statname where NAME = 'redo size') --and se.username is not null and st.value > 10 * 1024 * 1024 and se.SQL_ADDRESS = sa.ADDRESS and se.SQL_HASH_VALUE = sa.HASH_VALUE order by redosize; ******************************************************************************************** *********************************获取大批量数据操作测试的耗费时间和产生redo量********************************* set serveroutput on; DECLARE start_time NUMBER; end_time NUMBER; start_redo_size NUMBER; end_redo_size NUMBER; BEGIN start_time := dbms_utility.get_time; SELECT VALUE INTO start_redo_size FROM v$mystat m,v$statname s WHERE m.STATISTIC#=s.STATISTIC# AND s.NAME='redo size'; --transaction start 用户脚本 --transaction end end_time := dbms_utility.get_time; SELECT VALUE INTO end_redo_size FROM v$mystat m,v$statname s WHERE m.STATISTIC#=s.STATISTIC# AND s.NAME='redo size'; dbms_output.put_line('Escape Time:'||to_char(end_time-start_time)||' centiseconds'); dbms_output.put_line('Redo Size:'||to_char(end_redo_size-start_redo_size)||' bytes'); END; / --如果用户的脚本无法嵌入到如上的匿名块中,则使用如下脚本获取时间点和redo值前后相减即可: SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),VALUE||' bytes' FROM v$mystat m,v$statname s WHERE m.STATISTIC#=s.STATISTIC# AND s.NAME='redo size'; --transaction start 执行用户脚本 --transaction end SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),VALUE||' bytes' FROM v$mystat m,v$statname s WHERE m.STATISTIC#=s.STATISTIC# AND s.NAME='redo size'; ************************************************************************************************************** *********************************查看某用户当前执行的sql语句********************************* set pages 500 set lines 160 break on sid nodup on serial# nodup on user nodup on machine nodup on logontime nodup col machine format a20 col text format a64 col user format a10 col logontime format a10 col sid format 99999 col serial# format 99999 select b.sql_text text,a.sid sid,a.serial# serial#,a.username "user",a.machine machine,to_char(logon_time,'dd/hh24:mi') "logontime" from v$session a,v$sqltext b where a.username like upper('&1') and b.address = a.sql_address and b.hash_value = a.sql_hash_value order by a.sid,a.serial#,b.piece; ********************************************************************************************* *********************************查询执行计划并格式化输出********************************* --直接查询library cache中的sql真实的执行计划(9i以上),sql_hash_value 从 v$session 中查到: select '| Operation | PHV/Object Name | Rows | Bytes| Cost |' as "Optimizer Plan:" from dual union all select rpad('| '||substr(lpad(' ',1*(depth-1))||operation|| decode(options, null,'',' '||options), 1, 35), 36, ' ')||'|'|| rpad(decode(id, 0, '------------- ' , substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name) ||' ',1, 30)), 31, ' ')||'|'|| lpad(decode(cardinality,null,' ', decode(sign(cardinality-1000), -1, cardinality||' ', decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K', decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M', trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' || lpad(decode(bytes,null,' ', decode(sign(bytes-1024), -1, bytes||' ', decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K', decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M', trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' || lpad(decode(cost,null,' ', decode(sign(cost-10000000), -1, cost||' ', decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M', trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan" from v$sql_plan sp where sp.hash_value=&SQL_HASH_VALUE; --或者预生成执行计划: EXPLAIN PLAN set statement_id='MYSQL1' FOR --(表示为以下sql语句生成执行计划,不会执行该语句) &SQL语句 --格式化输出: select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial')); --查找执行计划版本超过10个的sql语句 select sa.sql_text,sa.version_count ,ss.*from v$sqlarea sa,v$sql_shared_cursor ss where sa.address=ss.KGLHDPAR and sa.version_count > 10 order by sa.version_count ; ****************************************************************************************** *********************************根据sid或spid查询执行的sql语句********************************* --根据sid查询执行的sql语句 select se.username, se.sid, se.serial#, se.osuser, se.machine, se.program, se.logon_time, sa.sql_text from v$session se, v$sqlarea sa where se.SQL_ADDRESS = sa.ADDRESS and se.SQL_HASH_VALUE = sa.HASH_VALUE and se.sid = '&SID'; --根据spid查询执行的sql语句 select se.username, se.sid, se.serial#, se.osuser, se.machine, se.program, se.logon_time, sa.sql_text from v$session se, v$sqlarea sa, v$process pr where se.SQL_ADDRESS = sa.ADDRESS and se.SQL_HASH_VALUE = sa.HASH_VALUE and se.PADDR=pr.ADDR and pr.spid = '&SPID'; ************************************************************************************************ *********************************查询各种等待事件对应的sql语句********************************* set pages 500 set lines 160 break on sid nodup on serial# nodup on user nodup on machine nodup on logontime nodup col machine format a20 col text format a64 col user format a10 col logontime format a10 col sid format 99999 col serial# format 99999 select b.sql_text text, a.sid sid, a.serial# serial#, a.username "user", a.machine machine, to_char(logon_time,'dd/hh24:mi') "logontime" from v$session a,v$sqltext b, v$session_wait c where a.sid = c.sid and c.event = '&EVENT_NAME' and b.address = a.sql_address and b.hash_value = a.sql_hash_value order by a.sid,a.serial#,b.piece; *********************************************************************************************** #######################################关于锁####################################### 补充:latch 到对象 *******************************************查询锁**************************************** --查看整个instance的锁情况 select * from dba_locks; --查看整个instance的dml锁情况 select * from v$lock where type in ('TX','TM'); select * from v$lock where type in ('TX','TM') and sid='&SID'; --查看session锁定的对象 select * from v$locked_object; --查询锁的holder和waiter: select decode(request, 0, 'Holder:', 'Waiter:') || sid, id1, id2, lmode, request, type from v$lock where (id1, id2, type) in (select id1, id2, type from v$lock where request > 0) order by id1, request; --查询表是否有锁: select oracle_username,owner,object_name,object_type,session_id,locked_mode from v$locked_object v, dba_objects d where v.object_id = d.object_id and object_name=upper('&1') order by object_name ; --查找所有被锁的对象: select oracle_username,owner,object_name,object_type,session_id,locked_mode,l.type,l.block from v$locked_object v, dba_objects d,v$lock l where l.block>0 and v.session_id=l.sid and d.object_id=v.object_id order by object_name,l.block ; --查看DML LOCK情况和锁定的对象情况: select a.sid, decode(a.type, 'MR', 'Media Recovery', 'RT', 'Redo Thread', 'UN', 'User Name', 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', 'DX', 'Distributed Xaction', 'CF', 'Control File', 'IS', 'Instance State', 'FS', 'File Set', 'IR', 'Instance Recovery', 'ST', 'Disk Space Transaction', 'IR', 'Instance Recovery', 'ST', 'Disk Space Transaction', 'TS', 'Temp Segment', 'IV', 'Library Cache Invalidation', 'LS', 'Log Start or Switch', 'RW', 'Row Wait', 'SQ', 'Sequence Number', 'TE', 'Extend Table', 'TT', 'Temp Table', a.type) lock_type, decode(a.lmode, 0, 'None', /* Mon Lock equivalent */ 1, 'Null', /* N */ 2, 'Row-S (SS)', /* L */ 3, 'Row-X (SX)', /* R */ 4, 'Share', /* S */ 5, 'S/Row-X (SSX)', /* C */ 6, 'Exclusive', /* X */ to_char(a.lmode)) mode_held, decode(a.request, 0, 'None', /* Mon Lock equivalent */ 1, 'Null', /* N */ 2, 'Row-S (SS)', /* L */ 3, 'Row-X (SX)', /* R */ 4, 'Share', /* S */ 5, 'S/Row-X (SSX)', /* C */ 6, 'Exclusive', /* X */ to_char(a.request)) mode_requested, a.ctime lock_time, to_char(a.id1) lock_id1, c.object_name lock_object_name, c.object_type lock_object_type, to_char(a.id2) lock_id2 from v$lock a,dba_objects c where (id1,id2) in (select b.id1, b.id2 from v$lock b where b.id1=a.id1 and b.id2=a.id2 ) and a.type in ('TX','TM') and a.id1=c.object_id(+); --存在多个BLOCKER时,查出源头的BLOCKER: SELECT * FROM V$LOCK WHERE SID IN (SELECT SID SESSION_ID FROM V$LOCK WHERE BLOCK > 0 MINUS SELECT W.SID SESSION_ID FROM V$SESSION_WAIT W WHERE W.EVENT = 'enqueue'); --查看BLOCKER对应的SESSION的状态和等待事件: SELECT S.SID, S.USERNAME, S.STATUS, W.EVENT, L.TYPE, L.ID1, L.ID2, L.LMODE, L.CTIME, L.BLOCK FROM V$SESSION S, V$SESSION_WAIT W, V$LOCK L WHERE S.SID = W.SID AND S.SID = L.SID AND L.BLOCK > 0; --查出WAITER等待的记录行: --首先查出WAITER等待的资源: SELECT ROW_WAIT_OBJ# , ROW_WAIT_FILE# , ROW_WAIT_BLOCK# , ROW_WAIT_ROW# FROM V$SESSION WHERE SID IN (SELECT DISTINCT SID FROM V$LOCK WHERE REQUEST > 0 ) AND ROW_WAIT_OBJ# <> -1; --再根据OBJECT_ID得出具体的对象属主和名称: SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_ID=< ROW_WAIT_OBJ#> --根据以上得到的OBJECT_ID,FILE_ID,BLOCK_ID,ROW#,就构成标准的ROWID,查出记录行: SELECT * FROM < OWNER > . < OBJECT_NAME > WHERE ROWID = DBMS_ROWID.ROWID_CREATE(1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#); ****************************************************************************************** #######################################关于等待事件####################################### *******************************************查询等待事件**************************************** select sw.seq#, sw.sid || ',' || s.serial# sids, s.username, sw.event, sw.P1, sw.p2, sw.p3, sw.wait_time "WAIT", sw.state, sw.seconds_in_wait sec, s.status, to_char(s.logon_time, 'dd/hh24:mi:ss') logon_time, s.MACHINE, s.TERMINAL, s.PROGRAM --,sa.SQL_TEXT from v$session s, v$session_wait sw --,v$sqlarea sa where sw.sid = s.sid --and s.SQL_ADDRESS=sa.ADDRESS --and s.SQL_HASH_VALUE=sa.HASH_VALUE and s.username is not null and sw.event not like '%SQL*Net%' and sw.event not like 'PX Deq%' and sw.event not like 'rdbms ipc message' order by sw.event, s.username; ************************************************************************************************* *********************************cache buffers chains等待事件的处理********************************* 查询等待事件的类型是否是latch free: select sw.sid || ',' || s.serial# sids, s.username, sw.event, sw.P1, sw.p2, sw.p3, sw.p1raw, sw.wait_time "WAIT", sw.state, sw.seconds_in_wait sec, s.status, to_char(s.logon_time, 'dd/hh24:mi:ss') log_time from v$session s, v$session_wait sw where s.username is not null and sw.sid = s.sid and sw.event not like '%SQL*Net%' and sw.event not like 'PX Deq%' order by sw.event; 如果是latch free,则其中p2字段的值表示latch number,据此可以查出是什么原因引起的latch free: select * from v$latchname where latch#=&P2; 如果等待的latch是cache buffers chains,则需要根据p1raw查出被争用的hot block和segment名称: --在后台sys用户下执行,查找热块 select /*+ RULE */ e.owner || '.' || e.segment_name segment_name, e.extent_id extent#, x.dbablk - e.block_id + 1 block#, x.tch, l.child# from sys.v$latch_children l, sys.x$bh x, sys.dba_extents e where x.hladdr = '&P1RAW' and e.file_id = x.file# and x.hladdr = l.addr and x.dbablk between e.block_id and e.block_id + e.blocks - 1 order by x.tch desc; column segment_name format a30 select distinct e.owner,e.segment_name,e.segment_type from dba_extents e, (select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc )where rownum<11) b where e.relative_fno=b.dbarfil and e.block_id<=b.dbablk and e.block_id+e.blocks>b.dbablk; --查找产生热块的sql: column segment_name format a35 select /*+ rule */ hash_value,sql_text from v$sqltext where (hash_value,address ) in ( select a.hash_value,a.address from v$sqltext a , (select distinct e.owner,e.segment_name,e.segment_type from dba_extents e, (select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc )where rownum<11) b where e.relative_fno=b.dbarfil and e.block_id<=b.dbablk and e.block_id+e.blocks>b.dbablk ) b where a.sql_text like '%'||b.segment_name||'%' and b.segment_type='TABLE') order by hash_value,address,piece; 找到latch holder所在session的sid和serial#,考虑是否可以kill掉,缓解数据库的压力: --这个latchhold变化得非常快,每刷新一次都会变化 select a.username, a.sid, a.serial#, a.status, b.pid, b.laddr, b.name from v$session a, v$latchholder b where a.sid = b.sid; *************************************************************************************************** *********************************db file sequential read等待事件的分析********************************* --当等待事件为db file sequential read时,P1对应file_id,P2对应&block_id --通过下面这个语句可以查询到正在等待什么对象 select owner,segment_name,segment_type from dba_extents where file_id = &file_id and &block_id between block_id and block_id+blocks-1; ******************************************************************************************************* *********************************db file scattered read等待事件的分析********************************* --当等待事件是db file scattered read时,用以下语句检查执行计划: select hash_value,child_number, lpad(' ',2*depth)||operation||' '||options||decode(id,0,substr(optimizer,1,6)||' Cost='||to_char(cost)) operation, object_name object,cost,cardinality,round(bytes/1024) kbytes from v$sql_plan where hash_value in (select a.sql_hash_value from v$session a,v$session_wait b where a.sid=b.sid and b.event='db file scattered read') order by hash_value,child_number,id; ******************************************************************************************************* *********************************mon_rbs 占用大量回滚段的监控********************************* select s.sid, s.serial#, s.machine, s.OSUSER, s.username, s.status, round(s.last_call_et / 60) "IDLE_Min", round((sysdate - to_date(t.start_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60) "Trans_Min", r.usn, round(r.RSSIZE / 1024 / 1024) rbssize_M, round(r.OPTSIZE / 1024 / 1024) optsize_M, s.logon_time, s.program, q.sql_text, q.hash_value FROM V$session s, V$transaction t, V$rollstat r,v$sqlarea q WHERE s.saddr = t.ses_addr AND t.xidusn = r.usn AND s.sql_address=q.address AND s.sql_hash_value=q.hash_value AND ((((r.curext = t.start_uext - 1) OR ((r.curext = r.extents - 1) AND t.start_uext = 0)) and s.last_call_et /60 > 30 and r.rssize>r.optsize and r.rssize > 50*1024*1024) or r.rssize >100*1024*1024) order by last_call_et desc; ********************************************************************************************** *********************************mon_xatrans 分布式事务锁的监控********************************* select a.local_tran_id,statu from dba_2pc_pending a where state='prepared'; 处理: rollback force '&LOCAL_TRAN_ID'; commit; execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('&LOCAL_TRAN_ID'); commit; ************************************************************************************************ *********************************mon_swait 等待事件的监控********************************* select sw.seq#,sw.sid||','||s.serial# sids,s.username,sw.event,sw.P1,sw.p2,sw.p3,sw.wait_time "WAIT", sw.state,sw.seconds_in_wait sec,s.status,to_char(s.logon_time,'dd/hh24:mi:ss') logon_time from v$session s,v$session_wait sw where sw.sid =s.sid and s.username is not null and sw.event not like '%SQL*Net%' and sw.event not like 'PX Deq%' and sw.event not like 'rdbms ipc message' and sw.event not like 'queue messages' order by sw.event,s.username ; ****************************************************************************************** *********************************mon_sqlarea 未使用绑定变量的sql监控********************************* select substr(sql_text, 1, 50) "SQL", count(*) cnt, sum(sharable_mem) "TotExecs" FROM v$sqlarea WHERE executions =1 GROUP BY substr(sql_text, 1, 50) HAVING count(*) > 5000 ORDER BY 2; ***************************************************************************************************** *********************************mon_sharepool 占用大量内存的sql监控********************************* select se.sid,se.SERIAL#,pr.SPID,se.osuser,se.MACHINE,sq.SHARABLE_MEM/1024/1024 ,se.PROGRAM,sq.SQL_TEXT from v$sqlarea sq,v$session se,v$process pr where se.PADDR=pr.ADDR and ((se.SQL_ADDRESS=sq.ADDRESS and se.SQL_HASH_VALUE=sq.HASH_VALUE) or (se.PREV_SQL_ADDR=sq.ADDRESS and se.PREV_HASH_VALUE=sq.HASH_VALUE)) and sq.SHARABLE_MEM>20*1024*1024 order by sq.SHARABLE_MEM/1024/1024; ***************************************************************************************************** *********************************mon_redo 产生大量redo log的session和sql监控********************************* select se.username, se.sid, se.serial#, pr.spid, se.status, se.machine, se.osuser, round(st.value / 1024 / 1024) redosize, sa.sql_text from v$session se, v$sesstat st, v$sqlarea sa ,v$process pr where se.sid = st.sid and st.STATISTIC# = (select STATISTIC# from v$statname where NAME = 'redo size') and se.username is not null and st.value > 50 * 1024 * 1024 and se.SQL_ADDRESS = sa.ADDRESS and se.SQL_HASH_VALUE = sa.HASH_VALUE and se.paddr=pr.addr order by redosize; ************************************************************************************************************* *********************************mon_temp 占用大量temp表空间的session和sql监控********************************* select su.extents, su.segtype, su.sqlhash, se.sid, se.serial#, se.last_call_et, se.username, se.machine ,sa.sql_text from v$sort_usage su, v$session se ,v$sqlarea sa where su.session_addr=se.saddr and se.SQL_ADDRESS = sa.ADDRESS and se.SQL_HASH_VALUE = sa.HASH_VALUE and su.extents>10; select su.segtype, sum(su.extents) from v$sort_usage su group by su.segtype; ***************************************************************************************************************
|