Oracle DBA优化数据库性能心得体会

很多的时侯，做Oracle DBA的我们，当应用管理员向我们通告现在应用很慢、数据库很慢的时侯，我们到数据库时做几个示例的Select也发现同样的问题时，有些时侯我们会无从下手，因为我们认为数据库的各种命种率都是满足Oracle文档的建议。实际上如今的优化己经向优化等待(waits)转型了，实际中性能优化最根本的出现点也都集中在IO，这是影响性能最主要的方面，由系统中的等待去发现Oracle库中的不足、操作系统某些资源利用的不合理是一个比较好的办法，下面把我的一点实践经验与大家分享一下，本文测重于Unix环境。
J6j`v&m0
`q)Xqn{|,`{0　　一、通过操作系统的一些工具检查系统的状态，比如CPU、内存、交换、磁盘的利用率，根据经验或与系统正常时的状态相比对，有时系统表面上看起来看空闲这也可能不是一个正常的状态，因为cpu可能正等待IO的完成。除此之外我们还应观注那些占用系统资源(cpu、内存)的进程。ITPUB个人空间q2FUz^zui
ITPUB个人空间U#Q(|L|Z |V(cE
　　1、如何检查操作系统是否存在IO的问题?使用的工具有sar,这是一个比较通用的工具。ITPUB个人空间0Vc2ZQ#HtR)ja
ITPUB个人空间 td tk5D@
　　Rp1#sar -u 2 10
@_f~ACx0ITPUB个人空间4SMX4Cf$AT%[I
　　即每隔2秒检察一次，共执行20次，当然这些都由你决定了。
6hBF,Y$YGAD0
S2r+_RbY?UM0　　示例返回：ITPUB个人空间^'w4r7_Q4A

`kS3Zm!\S(L0　　HP-UX hpn2 B.11.00 U 9000/800 08/05/03
,o6ssoP ]4v0
B-z,?"?.SH]4Ae*x s0　　18:26:32 %usr %sys %wio %idle
j#Kw)e VN\_7B x0
/z*g#WM9NR/u9k"U0　　注：我在redhat下查看是这种结果，不知%system就是所谓的%wio。
0T:f/d$Ml}}+CD0
XBCcE t6M&q{+^D0　　Linux 2.4.21-20.ELsmp (YY075) 05/19/2005ITPUB个人空间m6Z*@,B"mm
ITPUB个人空间%h%KCa:`
　　10:36:07 AM CPU %user %nice %system %idle
uw9w)hM0X/v8i0
;kws,wMk_[,b0　　10:36:09 AM all 0.00 0.00 0.13 99.87
n"F r_3v Cn(?0ITPUB个人空间;~EL3t|I{0l!x
　　10:36:11 AM all 0.00 0.00 0.00 100.00
9lEBM/P?`0
.l/hd"`6m0　　10:36:13 AM all 0.25 0.00 0.25 99.49
5q'e6EK"fcY%r,Tw0ITPUB个人空间
ULs7sKA
　　10:36:15 AM all 0.13 0.00 0.13 99.75
g Nd$[/Xqn+b0ITPUB个人空间:@q.b'^Fvn
a}V
　　10:36:17 AM all 0.00 0.00 0.00 100.00

u;C2C||%] m0
G!y2MEf^3bc0　　10:36:17 AM CPU %user %nice %system %idle
Sa-pvfmR$M;FR0ITPUB个人空间:xne(T\"q6HQ
　　10:36:19 AM all 0.00 0.00 0.00 100.00
4Tq0MDP'^3\S0ITPUB个人空间*?r-j m)M8i9Szw S
　　10:36:21 AM all 0.00 0.00 0.00 100.00
k3|(l)X*?7x9a$g8cO4{0ITPUB个人空间,]\H#`Jk
　　10:36:23 AM all 0.00 0.00 0.00 100.00
8| TY Z+HA0ITPUB个人空间`@.A-S\\:k.U
　　10:36:25 AM all 0.00 0.00 0.00 100.00ITPUB个人空间SR*~&lO:xp YiY)p
ITPUB个人空间E7z-csET3F~
　　其中的%usr指的是用户进程使用的cpu资源的百分比，%sys指的是系统资源使用cpu资源的百分比，%wio指的是等待io完成的百分比，这是值得我们观注的一项，%idle即空闲的百分比。如果wio列的值很大，如在35%以上，说明你的系统的IO存在瓶颈，你的CPU花费了很大的时间去等待IO的完成。Idle很小说明系统CPU很忙。像我的这个示例，可以看到wio平均值为11说明io没什么特别的问题，而我的idle值为零，说明我的cpu已经满负荷运行了。
Z'?/hp7MPq0　　当你的系统存在IO的问题，可以从以下几个方面解决:ITPUB个人空间%VJ6XorM$p

'O N\(k#b8|0　　*联系相应的操作系统的技术支持对这方面进行优化，比如hp-ux在划定卷组时的条带化等方面。ITPUB个人空间3KxK~ ]

~A#NNG \0　　*查找Oracle中不合理的sql语句，对其进行优。ITPUB个人空间?8nS7wF$l/xI*e
ITPUB个人空间S's#kx7s?
　　*对Oracle中访问量频繁的表除合理建索引外，再就是把这些表分表空间存放以免访问上产生热点，再有就是对表合理分区。
y/?C1P6k4t-wUi0ITPUB个人空间|
ZF+XzGi)e"lpxHM
　　常用的工具便是vmstat，对于hp-unix来说可以用glance,Aix来说可以用topas,当你发现vmstat中pi列非零，memory中的free列的值很小，glance,topas中内存的利用率多于80%时，这时说明你的内存方面应该调节一下了，方法大体有以下几项。ITPUB个人空间.bP^h?1hxy

KG~(F7g^1h^[0　　*划给Oracle使用的内存不要超过系统内存的1/2,一般保在系统内存的40%为益。
%q-und6hO0ITPUB个人空间7c RnKY1{
　　*为系统增加内存。ITPUB个人空间
\,T PeC"Y6b
ITPUB个人空间dige
^*bl8I|
　　*如果你的连接特别多，可以使用MTS的方式。ITPUB个人空间
T6c1s ~1nu pg6w

-] NBXj3}9L;cg0　　*打全补丁，防止内存漏洞。
vf9u]"}n5R0
S;}5FtST9Oy0　　3、如何找到点用系用资源特别大的Oracle的session及其执行的语句。ITPUB个人空间H/AaFn8d(u
ITPUB个人空间y+Oh\[#K Y7Q+|
　　Hp-unix可以用glance,top，IBM AIX可以用topas，此外可以使用ps的命令。通过这些程序我们可以找到点用系统资源特别大的这些进程的进程号，我们就可以通过以下的sql语句发现这个pid正在执行哪个sql，这个sql最好在pl/sql developer,toad等软件中执行, 把<>中的spid换成你的spid就可以了。
8M-f:V["j
O
G3a@0ITPUB个人空间.i$m.UH0jr|b
    SELECT a.username, a.machine, a.program, a.sid, a.serial#, 　　a.status,ITPUB个人空间z)r-E#DQYRI
           c.piece, c.sql_textITPUB个人空间?Te{X qP4c^#S
      FROM v$session a, v$process b, 　　v$sqltext cITPUB个人空间"c1u2ib]z$X
     WHERE b.spid = 'ORCL'
3S/E-V]D'wW0       AND b.addr = a.paddr
_R!iNc$y0QRB0r0       AND 　　a.sql_address = c.address(+)ITPUB个人空间Vx.?!LvO
     ORDER BY c.piece;

　　我们就可以把得到的这个sql分析一下，看一下它的执行计划是否走索引，对其优化避免全表扫描，以减少IO等待，从而加快语句的执行速度。ITPUB个人空间/fyj+oIi[*P/kp(_
ITPUB个人空间0Yu!w"@&nr"cn8I
　　提示：我在做优化sql时，经常碰到使用in的语句，这时我们一定要用exists把它给换掉，因为Oracle在处理In时是按Or的方式做的，即使使用了索引也会很慢。
`2h2D6\T0ITPUB个人空间+xh ?@5F
　　比如：ITPUB个人空间-lU1mz^d_
ITPUB个人空间 QB7xT,^ik
　　SELECT col1,col2,col3ITPUB个人空间#C%|,X)Hf
c.k&Cx
        FROM table1 aITPUB个人空间 Xf;h#B-w{q
　　WHERE a.col1 not in (SELECT col1 FROM table2);

ITPUB个人空间ms a;]"O0QVc
　　可以换成：
{;m9qegH0ITPUB个人空间-y{3cA\'n[%I|
　　SELECT col1,col2,col3ITPUB个人空间a!v"r)@.b8Q2ji`
    FROM table1 a
RY/^0S.N KP*}0　　WHERE not existsITPUB个人空间R$UV7NU2z"];O
　　(SELECT 'x' FROM table2 b WHERE a.col1=b.col1)
!o-i [
k%f{0
M,he.rxc$M8E0　　4、另一个有用的脚本：查找前十条性能差的sql。
yB ]g*vjK0    SELECT *ITPUB个人空间$d y5C1BF?"y$~JR
      FROM (SELECT parsing_user_id, executions, sorts, command_type,       disk_reads,sql_text
}7@[&[y9Wl"[+M0               FROM v$sqlarea 　order BY disk_reads DESC)
g`;~+L%o0     WHERE rownum < 10;
1S"c+n4nc1ag.XR0　　二、迅速发现Oracle Server的性能问题的成因，我们可以求助于v$session_wait这个视图，看系统的这些session在等什么，使用了多少的IO。以下是我提供的参考脚本：ITPUB个人空间
_saG}?
h
ITPUB个人空间g6O+N` H(d"BY4@k C`U
　　脚本说明：查看占io较大的正在运行的session。ITPUB个人空间E@^x-nd

9s1c;[ z7J7Y o0ON`0SELECT se.sid, se.serial#, pr.spid, se.username, se.status, se.terminal,
#b3[R.l'I0           se.program, se.module,se.sql_address, st.event, st.p1text,
w}3TC|l`X0           si.physical_reads, 　　si.block_changesITPUB个人空间x#h"s-pLR
      FROM v$session se, v$session_wait st, 　　v$sess_io si, v$process pr
2b;mHZ.A%m0     WHERE st.sid = se.sid
7HAb"gr,h\S0       AND st. 　　sid = si.sidITPUB个人空间X1^`/X-W%~%o'Ds9E{
       AND se.paddr = pr.addrITPUB个人空间M:P9DI3VYO}5j?
       AND se.sid > 6ITPUB个人空间*f*YjR p"O#v&g~;b
       AND st. 　　wait_time = 0
rU:gO$LUo0       AND st.event NOT LIKE '%SQL%'ITPUB个人空间 p&|Z2t Y;A5X$G8t
     ORDER BY physical_reads DESCITPUB个人空间'x,LK\Jn2e6|

:^:ER%_]6p;K0　　对检索出的结果的几点说明：
.C+R/I#B6z3`g-WD0ITPUB个人空间 iII9Y&b%wp4s,Y
　　1、我是按每个正在等待的session已经发生的物理读排的序，因为它与实际的IO相关。ITPUB个人空间2AJa:Yq8nu
ITPUB个人空间9n4j(Kc.^'T&i.M
　　2、你可以看一下这些等待的进程都在忙什么，语句是否合理?
+S8| bE)F%t7d0
9c;ckYtG0　　Select sql_address from v$session where sid=;
/Bs1[.j!i d5T/F1y}0
]U!ow-Cr#J:c)hE0　　Select * from v$sqltext where address=;ITPUB个人空间L*nc tL:`!`
ITPUB个人空间Xm H9U(s2S+Ef+~m
　　执行以上两个语句便可以得到这个session的语句。你也以用alter system kill session 'sid,serial#';把这个session杀掉。
)Q [_{ECwM3@4@+s0ITPUB个人空间xV,U:Qet!\ aO
　　3、应观注一下event这列，这是我们调优的关键一列，下面对常出现的event做以简要的说明：ITPUB个人空间5a@+[7E.rxW
ITPUB个人空间"jp#k9YCB(r
xZ$i
　　a、buffer busy waits，free buffer waits这两个参数所标识是dbwr是否够用的问题，与IO很大相关的，当v$session_wait中的free buffer wait的条目很小或没有的时侯，说明你的系统的dbwr进程决对够用，不用调整;free buffer wait的条目很多，你的系统感觉起来一定很慢，这时说明你的dbwr已经不够用了，它产生的wio已经成为你的数据库性能的瓶颈，这时的解决办法如下：
'@td%S dd \5m%gl0ITPUB个人空间f w$VD `.KG X6{
　　a.1增加写进程，同时要调整db_block_lru_latches参数。
m4Owd8A|Ed0ITPUB个人空间-{2h6LqS?|L'K4gz*s(x
　　示例：修改或添加如下两个参数
Vz0B8[ p-B.[@4t0
!ex'Lu)WyAE"j |-Y0　　db_writer_processes=4ITPUB个人空间&H7P!Oi;e%N2P

2Ri0o~T k PKg0　　db_block_lru_latches=8
v4jt4h&]0ITPUB个人空间k+NHh&L.R&n
Y4Fu
　　a、2开异步IO，IBM这方面简单得多，hp则麻烦一些，可以与Hp工程师联系。
}`%E.xu ]2v1a+_0ITPUB个人空间EQ-Dw)S:l
　　b、db file sequential read，指的是顺序读，即全表扫描，这也是我们应该尽量减少的部分，解决方法就是使用索引、sql调优，同时可以增大db_file_multiblock_read_count这个参数。ITPUB个人空间 j v5tk2[,@Z i-F
ITPUB个人空间3o|
x+ENX,k\4rSx
　　c、db file scattered read,这个参数指的是通过索引来读取，同样可以通过增加db_file_multiblock_read_count这个参数来提高性能。
OQMK}U0
7vF;cY _+W*[N0　　d、latch free,与栓相关的了，需要专门调节。
J:W4wb2Q`m&q0
2T)vo,Y'UxCL0　　e、其他参数可以不特别观注。
4o;vF(zk"`&C8VQ ^-KK0ITPUB个人空间[5~b:yhx(W(r On3L
　　其他的优化手段似乎主要集中在SQL查询语句上面，Oracle本身也提供了优化器。看来DBA的学问不少啊。