--查询最慢的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
从V$SQLAREA中查询最占用资源的查询
select b.username username,a.disk_reads reads, a.executions exec,a.disk_reads/decode(a.executions,1,a.executions) rds_exec_ratio, a.sql_text Statement from v$sqlarea a,dba_users b where a.parsing_user_id=b.user_id and a.disk_reads > 100000 order by a.disk_reads desc;
用buffer_gets列来替换disk_reads列可以得到占用最多内存的sql语句的相关信息。
v$sql:内存共享SQL区域中已经解析的SQL语句。(即时)
列出使用频率最高的5个查询:
select sql_text,executions from (select sql_text, rank() over (order by executions desc) exec_rank from v$sql) where exec_rank <=5;
消耗磁盘读取最多的sql top5: select disk_reads,sql_text from (select sql_text, dense_rank() over (order by disk_reads desc) disk_reads_rank from v$sql) where disk_reads_rank <=5;
找出需要大量缓冲读取(逻辑读)操作的查询:
select buffer_gets,buffer_gets, dense_rank() over (order by buffer_gets desc) buffer_gets_rank from v$sql) where buffer_gets_rank<=5;
v$sqlarea字段定义:http://happyhou.blog.sohu.com/60494432.html
|