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


上一篇:记一次Oracle故障:磁盘空间满


下一篇:Nginx调整(一)