oracle狀態查詢（補）

wangwenan6發表於2014-03-08
Oracle
---正在執行的sql語句
select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
  from v$session a, v$sqlarea b 
where a.sql_address = b.address 
---執行過的sql語句
select b.SQL_TEXT,b.FIRST_LOAD_TIME,b.SQL_FULLTEXT
  from v$sqlarea b
where b.FIRST_LOAD_TIME between '2014-03-08/09:00:00' and
       '2014-03-08/10:00:00' order by b.FIRST_LOAD_TIME 
（此方法好處可以檢視某一時間段執行過的sql，並且 SQL_FULLTEXT 包含了完整的 sql 語句） 


查詢效能差的sql. 
SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS, 
COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea 
order BY disk_reads DESC )where ROWNUM<10 ;
（sql數目） 


檢視佔io較大的正在執行的session 
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status, 
se.terminal,se.program,se.MODULE,、se.sql_address,st.event,st. 
p1text,si.physical_reads, 
si.block_changes FROM v$session se,v$session_wait st, 
v$sess_io si,v$process pr WHERE st.sid=se.sid AND st. 
sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st. 
wait_time=0 AND st.event NOT LIKE '%SQL%'
ORDER BY physical_reads DESC

查詢oracle被鎖的表：
SELECT A.OWNER,A.OBJECT_NAME,B.XIDUSN,B.XIDSLOT,B.XIDSQN,B.SESSION_ID,B.ORACLE_USERNAME, 
   B.OS_USER_NAME,B.PROCESS, B.LOCKED_MODE,C.MACHINE,C.STATUS,C.SERVER,C.SID,
   C.SERIAL#,C.PROGRAM
FROM ALL_OBJECTS A,V$LOCKED_OBJECT B,SYS.GV_$SESSION C 
WHERE ( A.OBJECT_ID = B.OBJECT_ID )
  AND (B.PROCESS = C.PROCESS )
  ORDER BY 1,2

釋放session Sql:
alter system kill session 'sid, serial#'

檢視死鎖的指令碼：
SELECT substr(v$lock.sid,1,4) "SID",
       substr(username,1,12) "UserName",
       substr(object_name,1,25) "ObjectName",
       v$lock.type "LockType",
       decode(rtrim(substr(lmode,1,4)),
       '2','Row-S (SS)','3','Row-X (SX)',
       '4','Share',     '5','S/Row-X (SSX)',
       '6','Exclusive', 'Other' ) "LockMode",
       substr(v$session.program,1,25) "ProgramName"
FROM V$LOCK,SYS.DBA_OBJECTS,V$SESSION
WHERE (OBJECT_ID = v$lock.id1
      AND v$lock.sid = v$session.sid
      AND username IS NOT NULL
      AND username NOT IN ('SYS','SYSTEM')
      AND SERIAL# != 1);

如何定位重要(消耗資源多)的SQL:

     select sql_text 
     from v$sql 
     where disk_reads > 1000 or (executions > 0 and buffer_gets/executions > 30000);

如何跟蹤某個session的SQL:
     exec dbms_system.set_sql_trace_in_session(sid,serial#,&sql_trace);
     查詢系統檢視:     
     select sid,serial# from v$session where sid = (select sid from v$mystat where rownum = 1);
     10046的trace:
     exec dbms_system.set_ev(&sid,&serial#,&event_10046,&level_12,'');


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29510932/viewspace-1103533/，如需轉載，請註明出處，否則將追究法律責任。

相關文章