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