查询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

从V$SQLAREA中查询最占用资源的查询

select b.username username,a.disk_reads reads,

a.executions exec,a.disk_reads/decode(a.executions,0,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,executions,

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,disk_reads,

dense_rank() over

(order by disk_reads desc) disk_reads_rank

from v$sql)

where disk_reads_rank <=5;

找出需要大量缓冲读取（逻辑读）操作的查询：

select buffer_gets,sql_text

from (select sql_text,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

来源：http://blog.csdn.net/sxhong/article/details/18262663

有很多种方法可以用来找出哪些SQL语句需要优化，但是很久以来，最简单的方法都是分析保存在V$SQL视图中的缓存的SQL信息。通过V$SQL视图，可以确定具有高消耗时间、CUP和IO读取的SQL语句。

1.查看总消耗时间最多的前10条SQL语句

select *

from (select v.sql_id,

v.child_number,

v.sql_text,

v.elapsed_time,

v.cpu_time,

v.disk_reads,

rank() over(order by v.elapsed_time desc) elapsed_rank

from v$sql v) a

where elapsed_rank <= 10;

2.查看CPU消耗时间最多的前10条SQL语句

select *

from (select v.sql_id,

v.child_number,

v.sql_text,

v.elapsed_time,

v.cpu_time,

v.disk_reads,

rank() over(order by v.cpu_time desc) elapsed_rank

from v$sql v) a

where elapsed_rank <= 10;

3.查看消耗磁盘读取最多的前10条SQL语句

select *

from (select v.sql_id,

v.child_number,

v.sql_text,

v.elapsed_time,

v.cpu_time,

v.disk_reads,

rank() over(order by v.disk_reads desc) elapsed_rank

from v$sql v) a

where elapsed_rank <= 10;

select *

from (select sa.SQL_TEXT,

sa.SQL_FULLTEXT,

sa.EXECUTIONS "执行次数",

round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",

round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",

sa.COMMAND_TYPE,

sa.PARSING_USER_ID "用户ID",

u.username "用户名",

sa.HASH_VALUE

from v$sqlarea sa

left join all_users u

on sa.PARSING_USER_ID = u.user_id

where sa.EXECUTIONS > 0

order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)

where rownum <= 50;

查询次数最多的 sql

select *

from (select s.SQL_TEXT,

s.EXECUTIONS "执行次数",

s.PARSING_USER_ID "用户名",

rank() over(order by EXECUTIONS desc) EXEC_RANK

from v$sql s

left join all_users u

on u.USER_ID = s.PARSING_USER_ID) t

where exec_rank <= 100;