oracle performance tuning效能優化學習系列(三)
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle.Performance.Tuning筆記OracleORM筆記
- Oracle Advanced Performance Tuning Scripts(轉)OracleORM
- Oracle Performance Tuning 11g2 (2)OracleORM
- Oracle效能優化-SQL優化(案例三)Oracle優化SQL
- Oracle SQL優化之sql tuning advisorOracleSQL優化
- 《java學習三》jvm效能優化-------調優JavaJVM優化
- 效能優化篇 - Performance（工具 & api）優化ORMAPI
- 《java學習三》jvm效能優化------jconsulJavaJVM優化
- oracle 效能優化Oracle優化
- Spark學習——效能調優（三）Spark
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- Oracle效能優化-SQL優化(案例四)Oracle優化SQL
- MySQL系列：效能優化MySql優化
- 效能優化的過程學習優化
- Oracle優化案例-關閉auto space advisor和sql tuning advisor（十九）Oracle優化SQL
- MySQL優化學習手札(三)MySql優化
- Oracle Tuning (Oracle 效能調整)的一些總結(轉)Oracle
- Oracle 效能優化-expdp備份速度優化02Oracle優化
- Oracle 效能優化-expdp備份速度優化03Oracle優化
- Oracle Tuning (Oracle 效能調整)的一些總結(轉)2Oracle
- 《金三銀四面試系列》— jvm與效能優化面試JVM優化
- Oracle自帶工具sql優化集-SQL Tuning Advisor （使用心得體會）OracleSQL優化
- MySQL調優效能監控之performance schemaMySqlORM
- redis學習(六) 排序（sort,by，store,效能優化）Redis排序優化
- Oracle效能優化方法論的發展之三：基於響應時間分析的效能優化方法論Oracle優化
- oracle SQL最佳化器SQL Tuning Advisor （STA)OracleSQL
- Go 高效能系列教程之三：編譯器優化Go編譯優化
- Nginx效能優化(學習筆記二十五)Nginx優化筆記
- 《java學習二》jvm效能優化-----認識jvmJavaJVM優化
- iOS效能優化系列篇之“列表流暢度優化”iOS優化
- iOS效能優化系列篇之“優化總體原則”iOS優化
- 資料庫例項效能調優利器：Performance Insights資料庫ORM
- Android效能優化相關的學習記錄(1)Android優化
- 【推薦】Java效能優化系列集錦Java優化
- 推薦：Java效能優化系列集錦Java優化
- Oracle案例10——HWM（高水位線）效能優化Oracle優化
- Oracle效能優化使用度量和預警Oracle優化
- Oracle SQL效能優化的40條軍規OracleSQL優化