oracle性能优化学习系列(三)
自动化收集性能统计信息
Automatic Performance Statistics
1,统计信息存储在v$sysstat and v$sesstat
2,重启库后，统计信息会重置
3，awr会定时收集统计，存储在相应的表中
4，metric度量是另一种统计信息的类型；
4，度量即数据库调用次数；事务数；
如每秒的数据库调用次数即为一个度量
6，度量存储在v$或awr中
7，度量值间隔一般为60秒
8，这是另一种统计信息类型；叫采样数据；即由ash采样器进行采样；
9,ash sampler对当前所有的活动会话进行采样；然后把信息存储在内存
10，可通过v$访问这些采样数据
11，awr会把它存储到硬盘中
12，baseline是oracle提供的一种解决性能问题的强大利器；
13，baseline是在高负荷采集的一系列统计规则；
14,通过比较出现性能题的纺计与基线；即可发现问题
15，awr支持获取baseline data;
16,awr可以指定某个时间范围的awr snapshot作为baseline;所指定的baseline一定要具有代表性；
统计信息的类型：
1，数据库统计
2，操作系统统计
3，解读统计信息
1,数据库统计
SQL> select class from v$waitstat;
CLASS
------------------
data block
sort block
save undo block
segment header
save undo header
free list
extent map
1st level bmb
2nd level bmb
3rd level bmb
bitmap block
bitmap index block
file header block
unused
system undo header
system undo block
undo header
undo block
18 rows selected
--oracle的等待事件的分类
SQL> select distinct wait_class from v$system_event;
WAIT_CLASS
----------------------------------------------------------------
Concurrency
User I/O
System I/O
Administrative
Other
Application
Idle
Commit
Network
9 rows selected
1，比如排它tx locks一般是application引起；而hw locks则由配置confiration导致
如下列表列举一些分类的等待
1,application:由行级锁产或显式lock命令引发的lock waits
2,commit:提交命令后等待redo log write确认
3，idle:标明会话处于inactive;比如sql*net message from client
4,network:等待经过网络传送数据
5,user i/0:等待数据块写入到磁盘中
等待事件统计分为前台和后台
一般我们把精力集中在前台；
--与实例相关的等待事件统计，各种等待事件
SQL> select * from v$system_event;
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO TOTAL_WAITS_FG TOTAL_TIMEOUTS_FG TIME_WAITED_FG AVERAGE_WAIT_FG TIME_WAITED_MICRO_FG EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------------------------------------------------------------- ----------- -------------- ----------- ------------ ----------------- -------------- ----------------- -------------- --------------- -------------------- ---------- ------------- ----------- ----------------------------------------------------------------
pmon timer 4118 3999 1199637 291.32 11996373606 0 0 0 0 0 3539483025 2723168908 6 Idle
Parameter File I/O 64 0 9 0.14 89132 0 0 0 0 0 1179235204 1740759767 8 User I/O
rdbms ipc message 83569 82631 14238038 170.37 142380381333 0 0 0 0 0 866018717 2723168908 6 Idle
Disk file operations I/O 744 0 92 0.12 921972 45 0 6 0.14 62094 166678035 1740759767 8 User I/O
class slave wait 63 0 1 0.02 12675 0 0 0 0 0 1055154682 2723168908 6 Idle
os thread startup 140 0 252 1.8 2519381 20 0 28 1.38 275545 86156091 3875070507 4 Concurrency
DIAG idle wait 23992 23992 2399110 100 23991101695 0 0 0 0 0 3176176482 2723168908 6 Idle
control file sequential read 14730 0 3919 0.27 39191804 154 0 42 0.27 420958 3213517201 4108307767 9 System I/O
control file parallel write 4201 0 5574 1.33 55735138 44 0 6 0.14 62002 4078387448 4108307767 9 System I/O
read by other session 87 0 94 1.09 944319 53 0 18 0.35 183950 3056446529 1740759767 8 User I/O
log file sequential read 8 0 0 0.06 4870 0 0 0 0 0 549236675 4108307767 9 System I/O
log file single write 8 0 1 0.16 12584 0 0 0 0 0 215477332 4108307767 9 System I/O
log file parallel write 903 0 93 0.1 931010 0 0 0 0 0 3999721902 4108307767 9 System I/O
log file sync 41 0 15 0.37 151884 31 0 14 0.45 140941 1328744198 3386400367 5 Commit
db file sequential read 4973 0 2848 0.57 28484542 2516 0 1044 0.42 10444109 2652584166 1740759767 8 User I/O
db file scattered read 485 0 275 0.57 2751126 35 0 22 0.62 218336 506183215 1740759767 8 User I/O
db file parallel write 1706 0 355 0.21 3551164 0 0 0 0 0 1620694733 4108307767 9 System I/O
db file parallel read 36 0 150 4.16 1497481 14 0 19 1.37 191867 834992820 1740759767 8 User I/O
direct path read 30 0 22 0.72 215311 9 0 21 2.33 209763 3926164927 1740759767 8 User I/O
direct path write 19 0 1 0.05 9136 0 0 0 0 0 885859547 1740759767 8 User I/O
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO TOTAL_WAITS_FG TOTAL_TIMEOUTS_FG TIME_WAITED_FG AVERAGE_WAIT_FG TIME_WAITED_MICRO_FG EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------------------------------------------------------------- ----------- -------------- ----------- ------------ ----------------- -------------- ----------------- -------------- --------------- -------------------- ---------- ------------- ----------- ----------------------------------------------------------------
direct path write temp 9 0 0 0.06 4999 0 0 0 0 0 38438084 1740759767 8 User I/O
smon timer 46 39 1183238 25722.57 11832381767 0 0 0 0 0 1403232821 2723168908 6 Idle
Space Manager: slave idle wait 2336 2329 1165746 499.04 11657461892 0 0 0 0 0 2942611488 2723168908 6 Idle
cursor: pin S wait on X 1 0 1 0.77 7652 1 0 1 0.77 7652 1729366244 3875070507 4 Concurrency
virtual circuit wait 247 0 11 0.05 112583 247 0 11 0.05 112583 2900469894 2000153315 7 Network
shared server idle wait 775 385 1197762 1545.5 11977620989 0 0 0 0 0 1786390478 2723168908 6 Idle
dispatcher timer 1696 580 3591511 2117.64 35915106742 0 0 0 0 0 4090013609 2723168908 6 Idle
jobq slave wait 3276 3274 163799 50 1637988874 3276 3274 163799 50 1637988874 782339817 2723168908 6 Idle
SQL*Net message to client 545 0 0 0 2496 522 0 0 0 2415 2067390145 2000153315 7 Network
SQL*Net more data to client 72 0 0 0 432 72 0 0 0 432 554161347 2000153315 7 Network
SQL*Net message from client 930 0 2000916 2151.52 20009162693 921 0 2000915 2172.55 20009151638 1421975091 2723168908 6 Idle
SQL*Net break/reset to client 4 0 0 0 184 4 0 0 0 184 1963888671 4217450380 1 Application
Streams AQ: qmn coordinator idle wait 577 428 1196960 2074.45 11969602413 0 0 0 0 0 989870553 2723168908 6 Idle
Streams AQ: qmn slave idle wait 431 0 1195925 2774.77 11959249844 0 0 0 0 0 1830121438 2723168908 6 Idle
JS coord start wait 1 1 49 49.11 491062 0 0 0 0 0 2190647165 4166625743 3 Administrative
latch free 309 0 0 0 4813 0 0 0 0 0 3474287957 1893977003 0 Other
latch: call allocation 1 0 0 0 30 0 0 0 0 0 139039345 1893977003 0 Other
latch: messages 20 0 0 0.01 2102 0 0 0 0 0 1973577887 1893977003 0 Other
rdbms ipc reply 16 0 92 5.74 917967 14 0 91 6.49 909278 2587381521 1893977003 0 Other
asynch descriptor resize 316 316 1 0 9104 10 10 0 0 211 2505166323 1893977003 0 Other
reliable message 4 0 0 0.06 2375 2 0 0 0.09 1868 906644781 1893977003 0 Other
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO TOTAL_WAITS_FG TOTAL_TIMEOUTS_FG TIME_WAITED_FG AVERAGE_WAIT_FG TIME_WAITED_MICRO_FG EVENT_ID WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------------------------------------------------------------- ----------- -------------- ----------- ------------ ----------------- -------------- ----------------- -------------- --------------- -------------------- ---------- ------------- ----------- ----------------------------------------------------------------
control file heartbeat 1 1 399 399.45 3994456 1 1 399 399.45 3994456 40893507 1893977003 0 Other
LGWR wait for redo copy 24 0 0 0 1005 0 0 0 0 0 4266849434 1893977003 0 Other
instance state change 2 0 0 0.04 834 2 0 0 0.04 834 1525267497 1893977003 0 Other
ADR file lock 26 0 0 0.01 3410 0 0 0 0 0 660190475 1893977003 0 Other
ADR block file read 98 0 48 0.49 480410 40 0 25 0.62 249023 1780066010 1893977003 0 Other
ADR block file write 21 0 2 0.11 23976 0 0 0 0 0 4092822979 1893977003 0 Other
Streams AQ: qmn coordinator waiting for slave to start 2 0 1 0.42 8353 0 0 0 0 0 1565566389 1893977003 0 Other
48 rows selected
--上述数据根据等待事件分类的汇总
SQL> select * from v$system_wait_class;
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS TOTAL_WAITS TIME_WAITED TOTAL_WAITS_FG TIME_WAITED_FG
------------- ----------- ---------------------------------------------------------------- ----------- ----------- -------------- --------------
1893977003 0 Other 1317 630 83 593
4217450380 1 Application 4 0 4 0
3875070507 4 Concurrency 145 260 21 28
3386400367 5 Commit 42 15 32 14
2723168908 6 Idle 127115 30866936 4337 2337721
2000153315 7 Network 873 12 850 12
1740759767 8 User I/O 6468 3492 2672 1131
4108307767 9 System I/O 22406 10410 198 48
8 rows selected
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-755788/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-755788/
386
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
