ORACLE 查询影响性能的语法

E"uVf A0查找bad sql的方法:51Testing软件测试网f CO,zc]} t

A1C$rt2~5X{;K.Q3^0select * from (select buffer_gets, sql_text
J?f#DJ$r0from v$sqlarea
*^5R%mJD
_/q0where buffer_gets >500000
lW
}4F4Hn~7tljI0order by buffer_gets desc) where rownum<=30;
niQ1Y$@b!N5]*W0 

执行次数多的SQL

;f5g-IlV"P |w0
n,V`X x1`)E!p.o0select sql_text,executions from51Testing软件测试网S8m1S^]6Cv
(select sql_text,executions from v$sqlarea order by executions desc)51Testing软件测试网$gQ awD3M(spvH
where rownum<81;
4}/Q1~'^v2_0 

V q&nO {
~G0读硬盘多的SQL

51Testing软件测试网VB/w(F4P)q
select sql_text,disk_reads from51Testing软件测试网rv_})qkP0Ac
(select sql_text,disk_reads from v$sqlarea order by disk_reads desc)51Testing软件测试网Mn s-Z6N J3jN
where rownum<21;51Testing软件测试网e[g9R-p
 51Testing软件测试网N,I8[4\A\_c h-}R

排序多的SQL51Testing软件测试网3rG`)w&Br
|r

51Testing软件测试网MPo0K c
^
X
select sql_text,sorts from
V:n4px-fjm_0(select sql_text,sorts from v$sqlarea order by sorts desc)
B\%eK? e^%x_"M?0where rownum<21;
fQ!dEiWR'W@0?0 

分析的次数太多，执行的次数太少，要用绑变量的方法来写sql

#w#a-l _GG0
3E^8Z\2Z0set pagesize 600;51Testing软件测试网@*@pWRF P
set linesize 120;
.i"Bh
|#} u,SI*u0select substr(sql_text,1,80) "sql",51Testing软件测试网
J*S['Z] k g}d
count(*), sum(executions) "totexecs"
a{@ K!M n9B1N7G0from v$sqlarea51Testing软件测试网c \%dSn
where executions < 5
sjxP2X*j%]bML0group by substr(sql_text,1,80)
BJ(J.I zi3nA0having count(*) > 30
A.Ms9_;Z}0order by 2;