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 ROWNUM10 ;

（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,'');