引用1：http://www.jb51.net/article/104380.htm
引用2：http://jenniferok.iteye.com/blog/700985
一、前言
在Oracle数据库应用调优中，一个SQL的执行次数/频率也是常常需要关注的，因为某个SQL执行太频繁，要么是由于应用设计有缺陷，需要在业务逻辑上做出优化处理，要么是业务特殊性所导致。如果执行频繁的SQL，往往容易遭遇一些并发性的问题。
二、涉及的V$视图
动态性能视图(v$session_longops)：
该视图记录了执行时间长于6秒的某个操作(这些操作可能是备份、恢复、收集统计信息、Hash Join、Sort、Nested loop、Table Scan、Index Scan 等)，这个视图通常用来分析SQL运行缓慢的原因，配合v$session视图。
资源消耗(v$sqltext)：
按sql语句资源消耗，存储的是完整的SQL，SQL被分割
资源消耗-聚合(v$sqlarea)：
按sql语句资源消耗，存储的SQL 和一些相关的信息，比如累计的执行次数，逻辑读，物理读等统计信息（统计）
资源消耗(v$sql)：
内存共享SQL区域中已经解析的SQL语句。（即时）
三、查看SQL的执行频率/次数
下面来看看完整的示例代码。
1、查询执行最慢的SQL
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;
2、查询次数最多的 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;
查找执行效率低下的SQL
select
opname,
target,
to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
elapsed_seconds elapsed,
executions execs,
buffer_gets/decode(executions, 0, 1, executions) bufgets,
module,
sql_text
from v$session_longops sl, v$sqlarea sa
where
sl.sql_hash_value = sa.hash_value
and upper(SUBSTR(module, 1, 4)) <> 'RMAN'
and substr(opname, 1, 4) <> 'RMAN'
and sl.start_time > trunc (sysdate)
order by start_time;
3、根据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
4、查询最占用资源的查询
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、列出使用频率最高的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;
6、消耗磁盘读取最多的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;
7、找出需要大量缓冲读取（逻辑读）操作的查询
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
