Oracle性能优化视频学习笔记－诊断和调优工具

诊断和工具ITPUB个人空间7_8}6z%gV wQ
n
show parameter background_dump_dest;
9gP.U?Qq-G0F2d0指出告警日志文件的目录ITPUB个人空间lMXaz"|9`y
log_checkpoint_to_alert=trueITPUB个人空间0Z!U\E j0m.CjMp#B
记录检查点信息和未完成的检查点ITPUB个人空间&f$t.uO0F8c$Ns
记录归档时间ITPUB个人空间cMp C~%}
用户跟踪文档ITPUB个人空间M:{#Y&k&u"qT E
跟踪用户发布的SQL信息ITPUB个人空间_x2a8y2zFP
alter session set sql_trace=true;ITPUB个人空间ib.N]^$C%D.kp
能够在会话一级进行SQL操作跟踪
B_:qbwaU[D0execute dbms_system.set_sql_trace(sid号,子序列号，true)ITPUB个人空间V8RO5\DB;c$x
跟踪会话中相应的SQL
9Z&V8@]g~ B:q0使用tkprof解析跟踪的SQL跟踪文件
Keo;K5Kb$R#}-H0catproc.sql用来生成创建跟踪的包

视图和工具
(['Zy2Sa.`Yv.?+J P0V$XXX，动态性能视图ITPUB个人空间(Bz/zz'Fxl$t yZ
v$lock,v$..ITPUB个人空间2}L(k xP3O-TDrXxL(m
base on x$table and listed in v$fixed_table
;e8c#Iwa5|,}0is v_$xxx的同义词
`Y9`5B!g)o0当前状态视图V$session,v$open_cursor,v$session_wait
JY?8X2t0累加视图V$sys_event,...ITPUB个人空间!N
rId.S#Vi!|
动态视图v$parameter
["{X(jl)_{|/AVn0DBA_XXX，数据字典
3aS-B"{F*D0DBA_tables,DBA_Segments,DBA_Tab_Columns,DBA_Clusters,DBA_INDEXES,INDEX_STATS,INDEX_HISTOGRAM,DBA_TAB_HISTOGRAMS
u*|EuU3l3j.J6B0以上统计信息是静态的，需要dbms_stats和analyze来完成更新ITPUB个人空间d rRnL%A
StatSpackITPUB个人空间.V&eB9q7a r%t w&i[
Oracle9i新的功能包
w z Jq
g!djy%T0UTLBstat.sql,UtlEstat.sql script取两者脚本执行之间的性能比较ITPUB个人空间|5QdA%yWz
企业管理器ITPUB个人空间5z~,h)t}2|
等待事件ITPUB个人空间2DzwOu;f,I1~f5i
Oracle诊断和优化packs

v@sysstat启动之后所有的系统统计
Lx P^[X\a0select name,value from v@sysstat
(G5Jh0DsN J0where name in ('db block gets','consistent gets','physical reads');
H#cA7o$O\7DT?u0hitrate=(db block gets+consistent gets)/(physical reads'+db block gets+consistent gets)

v@sgastat SGA区的统计信息ITPUB个人空间zZ A)@P:Xca
select * from v@sgastatITPUB个人空间h(m-A?_zBbMS
where pool='shared pool' and name='free memory'

v@event_name,v@system_eventITPUB个人空间f}6L]N5~+g
事件信息详细清单和统计

select sum(pins-reloads)/sum(pins) "lib cache" from v$libraryITPUB个人空间h2h7m8x j,W5M5[ }
库缓存命中率

select (sum(gets-getmisses))/sum(gets) "direc_hit" from v$rowcacheITPUB个人空间1uS gR%\*g`x7Y)s,@
数据字典命中率

v$db_cache_advice预计缓冲区的大小和影响ITPUB个人空间%uLa q'Q w/J
v$pgastatITPUB个人空间*n'h4G1u(w;wZ`.h)F
DiskITPUB个人空间Artqc.P"H)l
v$dbfile
h3ym;s+X0v$tempstatITPUB个人空间-T5R2u&}+wF0s&oPi4P
v$tempfileITPUB个人空间g O P/E|*c
v$filestat
3SI8Y;vF0v$datafile
Mb%hog0_
qr5I:Na0v$segment_statistITPUB个人空间?*nh/K
GFC
v$log,v$log_historyITPUB个人空间f'RRK+K6{ ^]0d
Contention
(H${/l q]7S
`[0v$latch,v$lock,v$rollname,v$rollstat,v$waitstat

关于会话统计信息ITPUB个人空间1AZE z:vXG|
select
+Mtd%JKL8V+|0a.statistic#,a.name,a.class,
+Ep-[ p&Ufu0b.sid,b.statistic#,b.value,
)baH6[ t X#}0c.sid,c.serial#,c.username,c.osuserITPUB个人空间(x8bG&L+S Bh-sz'L
from v$statname a,v$sesstat b,v$session cITPUB个人空间%k9v#?Ci.r
where a.statistic#=b.statistic#
4nu/R-AW4V0 and b.sid=c.sid

关于会话等待事件的统计 ITPUB个人空间c4`[9q9GEJ
select
m }T\YO:n7~"k!t0a.sid,a.event,a.total_waits,a.total_timeouts,a.time_waited,a.averate_wait,a.max_wait,
5}0m{
r w0b.event#,b.name,b.parameter1,b.parameter2,b.parameter3,ITPUB个人空间2B/D+pT0J{u
c.sid,c.seq#,c.event,c.p1/2/3 c.p1/2/2 text,c.p1/2/3 raw,c.wait time,c.seconds_in_wait,c.state
f:_ fL&m,Ba0from v$session_event a,v$event_name b,v$session_wait c
(uf/U`N_P0where a.sid=c.sid
4_ c!a'Q7x R0 and a.event=b.name
9~0b[R/X0 and b.name=c.event
.}Le$e ymH2t8q
t0 ITPUB个人空间hI1x&biQ#fF_
Statspack
F?gG$O0需要100兆空间（8i开始提供）ITPUB个人空间G!|!Y@&RL
且TIMED_STATISTICS=true
&K/^s F _v Lz0alter system set timed_statistics=trueITPUB个人空间;JM.J?#?&_^
spcreate.sql安装Statspack
2ribZ)_C%aJO0statspack.snap手工收集统计信息ITPUB个人空间R` c0Y:NU1R0g
spauto.sql自动收集统计信息ITPUB个人空间%sE-I\i4GX5Ter
spreport.sql产生统计信息报告

show parameter job_queue_processes>0方可自动收集统计信息ITPUB个人空间0laOm:]4Q
T
job_queue_processes>0即OK

第一步,创建statpack统计表空间

Q$G1c7?
Yr/Q1\W1c0create tablespace perfstat datafile '......' size 100M extent management local;
UJ5[o^;AdcS:N8`0第二步
:t6@q'N9hk1I0@%ORACLE_HOME%RDBMSADMINSPCREATE.SQLITPUB个人空间n |:F'l!OC'JB
创建statpack数据字典和用户
S#@|]/I"t0并且输入刚才创建的表空间perfstat
%V8h |R)Cm6e0第三步，切换用户ITPUB个人空间V~l/cH4X]qHH
connect perfstat/perfstatITPUB个人空间w1a rB5^
手工执行拍照
k#Fd#w`&P-h)V]0execute statpack.snap
"]{aTp"q H0第四步，创建自动拍照脚本，默认1个小时执行一次
){/x} {gJa0@%ORACLE_HOME%RDBMSADMINSPAUTO.SQLITPUB个人空间 N?*^lv
select job,log_user,last_date,next_date,interval from user_jobs
-|1E,i0C$k:rH S0第五步，运行spreport.sqlITPUB个人空间8Z%UI @;@y)d
select snap_id from stats$snapshot;

report内容19部分ITPUB个人空间u4W"e.L/G(eV"g
数据库和实例名称
#O/k8`^.y0|`&`0快照时间起至时间ITPUB个人空间#tVZC;bP.e4m(w
s
各当前缓存大小
#e2f3z!M
[ T
ff0负载load profile
!RhS o&O8O*Vm0实例的效率百分比ITPUB个人空间[7r+t2nd}g Nz,e t
共享池统计信息ITPUB个人空间2F*KZMs@%i
Top5事件ITPUB个人空间|6d!|
D3D/D6mI
性能较差的SQL by Diks,By CPU,By memoryITPUB个人空间T `o YD8j8{.?.}&V`z
表空间I/O统计数据ITPUB个人空间o~,S r h3K
数据文件I/O统计数据ITPUB个人空间7wov&I~
缓冲池统计ITPUB个人空间,^eY4x6q`
?R&c
实例恢复统计
*_4X$vmK^$Io)o0PGA内存统计
M"^ g`sg6W0回滚段统计
*s6E!W3~+X0锁存器活动的统计ITPUB个人空间pOE0f|!Z-@2Kp
数据字典缓冲区统计
-_
B,[Z;g3d QB0库缓冲区统计
\?"N\-A0SGA内存概要统计ITPUB个人空间K!k$tl7N
初始化参数值列表

@sppurge.sql可以删除快照信息ITPUB个人空间5DxvY'_Z
使用DBA删除快照和统计信息ITPUB个人空间*N/Vi-d)_&o6x`5L
执行spdrop即可

X)DPgHC6m0f0UTLBSTAT，UTLESTAT脚本可以做简单的快照统计
S:OVZs;ez0TIMED_STATSTICS=TRUE
*M c8q5V;NP)X0run UTLBSTATITPUB个人空间*J(ZFH O$E d4Y
run UTLESTAT
v1KgPWt1M9Rq:?0查看report.txt报告

企业管理器

Oracle Performance Manager

调优方法论
,Tl:Wf6p?6q]0明确调优范围ITPUB个人空间*GkL&vzwm8Q
收集数据
_!U2x!@HW~0编辑数据和规则
#@.|a1N%?8f6KT0分析数据ITPUB个人空间W3j3NVI1e2T
评估方案ITPUB个人空间L1^,d@K%S1N6^ ?
验证方案