轉：查詢oracle比較慢的session和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

 --查詢對應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;
SELECT sql_address FROM V$SESSION SS,V$SQLTEXT TT
WHERE SS.SQL_HASH_VALUE=TT.HASH_VALUE AND SID=439;
 

v$sqltext：存儲的是完整的SQL,SQL被分割

v$sqlarea：存儲的SQL 和一些相關的信息，比如累計的執行次數，邏輯讀，物理讀等統計信息（統計）

v$sql：記憶體共用SQL區域中已經解析的SQL語句。（即時）

根據sid查找完整sql語句：

select sql_text from v$sqltext a where a.hash_value = (select sql_hash_value from v$session b where b.sid = '&sid'    )
order by piece asc

select a.CPU_TIME,--CPU時間 百萬分之一（微秒）
       a.OPTIMIZER_MODE,--優化方式
       a.EXECUTIONS,--執行次數
       a.DISK_READS,--讀盤次數
       a.SHARABLE_MEM,--占用shared pool的記憶體多少
       a.BUFFER_GETS,--讀取緩衝區的次數
       a.COMMAND_TYPE,--命令類型(3:select,2:insert;6:update;7delete;47:pl/sql程式單元)
       a.SQL_TEXT,--Sql語句
       a.SHARABLE_MEM,
       a.PERSISTENT_MEM,
       a.RUNTIME_MEM,
       a.PARSE_CALLS,
       a.DISK_READS,
       a.DIRECT_WRITES,
       a.CONCURRENCY_WAIT_TIME,
       a.USER_IO_WAIT_TIME
  from SYS.V_$SQLAREA a
 WHERE PARSING_SCHEMA_NAME = 'CHEA_FILL'--表空間
 order by a.CPU_TIME desc

引用：http://jenniferok.iteye.com/blog/700985