oracle10g SQL性能调优

一：哪些查询需要调优？查询视图V$SQLAREA

v$sqlarea和v$sql是最常用的一些视图来查找性能低下的SQL。disk_reads的值定义了操作系统读取硬盘的次数。 disk_read和execution联合（disk_read/execution），返回每次执行的最大硬盘读取次数。任何这个参数排在最 前面的查询，都是需要进行优化饿。statspack也会列出最消耗资源的查询。

 

 二：从视图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;
USERNAME READS EXEC RDS_EXEC_RATIO STATEMENT
-------- ------- ---- -------------- --------------------
ADHOC1 7281934 1 7281934 select custno, ordno
from cust, orders
ADHOC5 4230044 4 1057511 select ordno
from orders where trunc(ordno) = 721305
ADHOC1 801716 2 400858 select custno,
ordno from cust where substr(custno,1,6) = ‘314159’

上面的查询列disk_reads换成buffer_gets，将查询出最消耗内存的sql语句

 

三：从视图V$SQL找出性能最差的SQL

select * from
(select sql_text,
rank() over ( order by buffer_gets desc ) as rank_bufgets,
to_char(100 * ratio_to_report(buffer_gets) over (), '999.99') pct_bufgets
from V$sql )
where rank_bufgets < 11;