如何進(jìn)行Oracle AWR報(bào)告指標(biāo)的解析

發(fā)布時(shí)間：2021-11-30 10:28:00 來源：億速云閱讀：237 作者：柒染欄目：關(guān)系型數(shù)據(jù)庫

這篇文章將為大家詳細(xì)講解有關(guān)如何進(jìn)行Oracle AWR報(bào)告指標(biāo)的解析，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對(duì)相關(guān)知識(shí)有一定的了解。

【性能調(diào)優(yōu)】Oracle AWR報(bào)告指標(biāo)全解析

2013/08/31 BY 26條評(píng)論

physical read bytes

physical read total bytes	物理讀的吞吐量/秒
physical read IO requests	physical read total IO requests	物理讀的IOPS
physical write bytes	physical write total bytes	物理寫的吞吐量/秒
physical write IO requests	physical write total IO requests	物理寫的IOPS

總的物理吞吐量/秒=physical read total bytes+physical write total bytes

總的物理IOPS= physical read total IO requests+ physical write total IO requests

IO的主要指標(biāo) 吞吐量、IOPS和延遲均可以從AWR中獲得了， IO延遲的信息可以從 User I/O的Wait Class Avg Wait time獲得，也可以參考11g出現(xiàn)的IOStat by Function summary

Instance Activity Stats有大量的指標(biāo)，但是對(duì)于這些指標(biāo)的介紹沒有那一份文檔有完整詳盡的描述，即便在Oracle原廠內(nèi)部要沒有(或者是Maclean沒找到)，實(shí)際是開發(fā)人員要引入某一個(gè)Activity Stats是比較容易的，并不像申請(qǐng)引入一個(gè)新后臺(tái)進(jìn)程那樣麻煩，Oracle對(duì)于新版本中新后臺(tái)進(jìn)程的引入有嚴(yán)格的要求，但Activity Stats卻很容易，往往一個(gè)one-off patch中就可以引入了，實(shí)際上Activity Stats在源代碼層僅僅是一些計(jì)數(shù)器?！?

較為基礎(chǔ)的statistics，大家可以參考官方文檔的Statistics Descriptions描述，地址在這里。

對(duì)于深入的指標(biāo) 例如 “Batched IO (space) vector count”這種由于某些新特性被引入的，一般沒有很詳細(xì)的材料，需要到源代碼中去閱讀相關(guān)模塊才能總結(jié)其用途，對(duì)于這個(gè)工作一般原廠是很延遲去完成的，所以沒有一個(gè)完整的列表。如果大家有對(duì)此的疑問，請(qǐng)去t.askmaclean.com 發(fā)一個(gè)帖子提問。

Instance Activity Stats - Absolute Values  Snaps: 7071
-> Statistics with absolute values (should not be diffed)

Statistic                            Begin Value       End Value
-------------------------------- --------------- ---------------
session pga memory max           1.157882826E+12 1.154290304E+12
session cursor cache count           157,042,373     157,083,136
session uga memory               5.496429019E+14 5.496775467E+14
opened cursors current                   268,916         265,694
workarea memory allocated                827,704         837,487
logons current                             2,609           2,613
session uga memory max           1.749481584E+13 1.749737418E+13
session pga memory               4.150306913E+11 4.150008177E+11

Instance Activity Stats – Absolute Values是顯示快照起點(diǎn) 和終點(diǎn)的一些指標(biāo)的絕對(duì)值

logon current 當(dāng)前時(shí)間點(diǎn)的登錄數(shù)
opened cursors current 當(dāng)前打開的游標(biāo)數(shù)
session cursor cache count 當(dāng)前存在的session緩存游標(biāo)數(shù)

Instance Activity Stats - Thread ActivityDB/Inst: G10R25/G10R25  Snaps: 3663-3
-> Statistics identified by '(derived)' come from sources other than SYSSTAT 

Statistic                                     Total  per Hour  
-------------------------------- ------------------ ---------  
log switches (derived)                           17  2,326.47

log switches (derived) 日志切換次數(shù) ，見《理想的在線重做日志切換時(shí)間是多長(zhǎng)？》

5 IO 統(tǒng)計(jì)

5-1 Tablespace IO Stats 基于表空間分組的IO信息

Tablespace IO Stats               DB/Inst: ITSCMP/itscmp2  Snaps: 70719-70723
-> ordered by IOs (Reads + Writes) desc

Tablespace
------------------------------
                 Av       Av     Av                       Av     Buffer  Av Buf
         Reads Reads/s  Rd(ms) Blks/Rd       Writes Writes/s      Waits  Wt(ms)
-------------- ------- ------- ------- ------------ -------- ---------- -------
DATA_TS
    17,349,398   4,801     2.3     1.5      141,077       39  4,083,704     5.8
INDEX_TS
     9,193,122   2,544     2.0     1.0      238,563       66  3,158,187    46.1
UNDOTBS1
     1,582,659     438     0.7     1.0            2        0     12,431    69.0

reads : 指該表空間上發(fā)生的物理讀的次數(shù)(單位不是塊，而是次數(shù))

Av Reads/s : 指該表空間上平均每秒的物理讀次數(shù) (單位不是塊，而是次數(shù))

Av Rd(ms): 指該表空間上每次讀的平均讀取延遲

Av Blks/Rd: 指該表空間上平均每次讀取的塊數(shù)目，因?yàn)橐淮挝锢碜x可以讀多個(gè)數(shù)據(jù)塊；如果Av Blks/Rd>>1 則可能系統(tǒng)有較多db file scattered read 可能是診斷FULL TABLE SCAN或FAST FULL INDEX SCAN，需要關(guān)注table scans (long tables) 和index fast full scans (full) 2個(gè)指標(biāo)

Writes : 該表空間上發(fā)生的物理寫的次數(shù) ; 對(duì)于那些Writes總是等于0的表空間不妨了解下是否數(shù)據(jù)為只讀，如果是可以通過read only tablespace來解決 RAC中的一些性能問題。

Av Writes/s : 指該表空間上平均每秒的物理寫次數(shù)

buffer Waits: 該表空間上發(fā)生buffer busy waits和read by other session的次數(shù)( 9i中buffer busy waits包含了read by other session)。

Av Buf Wt(ms): 該表空間上發(fā)生buffer Waits的平均等待時(shí)間，單位為ms

5-2 File I/O

File IO Stats                    Snaps: 70719-70723
-> ordered by Tablespace, File

Tablespace               Filename
------------------------ ----------------------------------------------------
                 Av       Av     Av                       Av     Buffer  Av Buf
         Reads Reads/s  Rd(ms) Blks/Rd       Writes Writes/s      Waits  Wt(ms)
-------------- ------- ------- ------- ------------ -------- ---------- -------
AMG_ALBUM_IDX_TS         +DATA/itscmp/plugged/data2/amg_album_idx_ts01.dbf
        23,298       6     0.6     1.0            2        0          0     0.0
AMG_ALBUM_IDX_TS         +DATA/itscmp/plugged/data3/amg_album_idx_ts02.dbf
         3,003       1     0.6     1.0            2        0          0     0.0

Tablespace 表空間名

FileName 數(shù)據(jù)文件的路徑

Reads: 該數(shù)據(jù)文件上累計(jì)發(fā)生過的物理讀次數(shù)，不是塊數(shù)

Av Reads/s: 該數(shù)據(jù)文件上平均每秒發(fā)生過的物理讀次數(shù)，不是塊數(shù)

Av Rd(ms): 該數(shù)據(jù)文件上平均每次物理讀取的延遲，單位為ms

Av Blks/Rd: 該數(shù)據(jù)文件上平均每次讀取涉及到的塊數(shù)，OLTP環(huán)境該值接近 1

Writes : 該數(shù)據(jù)文件上累計(jì)發(fā)生過的物理寫次數(shù)，不是塊數(shù)

Av Writes/s: 該數(shù)據(jù)文件上平均每秒發(fā)生過的物理寫次數(shù)，不是塊數(shù)

buffer Waits: 該數(shù)據(jù)文件上發(fā)生buffer busy waits和read by other session的次數(shù)( 9i中buffer busy waits包含了read by other session)。

Av Buf Wt(ms): 該數(shù)據(jù)文件上發(fā)生buffer Waits的平均等待時(shí)間，單位為ms

若某個(gè)表空間上有較高的IO負(fù)載，則有必要分析一下是否其所屬的數(shù)據(jù)文件上的IO 較為均勻還是存在傾斜，是否需要結(jié)合存儲(chǔ)特征來將數(shù)據(jù)均衡分布到不同磁盤上的數(shù)據(jù)文件上，以優(yōu)化 I/O

6 緩沖池統(tǒng)計(jì) Buffer Pool Statistics

Buffer Pool Statistics              Snaps: 70719-70723
-> Standard block size Pools  D: default,  K: keep,  R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

                                                            Free   Writ   Buffer
     Number of Pool       Buffer     Physical    Physical   Buff   Comp     Busy
P      Buffers Hit%         Gets        Reads      Writes   Wait   Wait    Waits
--- ---------- ---- ------------ ------------ ----------- ------ ------ --------
16k     15,720  N/A            0            0           0      0      0        0
D    2,259,159   98 2.005084E+09   42,753,650     560,460      0      1 8.51E+06

該環(huán)節(jié)的數(shù)據(jù)主要來源于WRH$_BUFFER_POOL_STATISTICS，而WRH$_BUFFER_POOL_STATISTICS是定期匯總v$SYSSTAT中的數(shù)據(jù)

P pool池的名字 D: 默認(rèn)的緩沖池 default buffer pool , K : Keep Pool , R: Recycle Pool ; 2k 4k 8k 16k 32k: 代表各種非標(biāo)準(zhǔn)塊大小的緩沖池

Number of buffers: 實(shí)際的緩沖塊數(shù)目，約等于池的大小 / 池的塊大小

Pool Hit % : 該緩沖池的命中率

Buffer Gets: 對(duì)該緩沖池的中塊的訪問次數(shù) 包括 consistent gets 和 db block gets

Physical Reads: 該緩沖池Buffer Cache引起了多少物理讀，其實(shí)是physical reads cache ，單位為塊數(shù)*次數(shù)

Physical Writes ：該緩沖池中Buffer cache被寫的物理寫，其實(shí)是physical writes from cache，單位為塊數(shù)*次數(shù)

Free Buffer Waits: 等待空閑緩沖的次數(shù)，可以看做該buffer pool 發(fā)生free buffer waits 等待的次數(shù)

Write Comp Wait: 等待DBWR寫入臟buffer到磁盤的次數(shù)，可以看做該buffer pool發(fā)生write complete waits等待的次數(shù)

Buffer Busy Waits: 該緩沖池發(fā)生buffer busy wait 等待的次數(shù)

7-1 Checkpoint Activity 檢查點(diǎn)與 Instance Recovery Stats 實(shí)例恢復(fù)

Checkpoint Activity         Snaps: 70719-70723
-> Total Physical Writes:                      590,563

                                          Other    Autotune      Thread
       MTTR    Log Size    Log Ckpt    Settings        Ckpt        Ckpt
     Writes      Writes      Writes      Writes      Writes      Writes
----------- ----------- ----------- ----------- ----------- -----------
          0           0           0           0      12,899           0
          -------------------------------------------------------------

Instance Recovery Stats     Snaps: 70719-70723
-> B: Begin Snapshot,  E: End Snapshot

                                                                            Estd
  Targt  Estd                                     Log Ckpt Log Ckpt    Opt   RAC
  MTTR   MTTR Recovery  Actual   Target   Log Sz   Timeout Interval    Log Avail
   (s)    (s) Estd IOs RedoBlks RedoBlks RedoBlks RedoBlks RedoBlks  Sz(M)  Time
- ----- ----- -------- -------- -------- -------- -------- -------- ------ -----
B     0     6    12828   477505  1786971  5096034  1786971      N/A    N/A     3
E     0     7    16990   586071  2314207  5096034  2314207      N/A    N/A     3
          -------------------------------------------------------------

該環(huán)節(jié)的數(shù)據(jù)來源于WRH$_INSTANCE_RECOVERY

MTTR Writes : 為了滿足FAST_START_MTTR_TARGET 指定的MTTR值而做出的物理寫 WRITES_MTTR

Log Size Writes ：由于最小的redo log file而做出的物理寫 WRITES_LOGFILE_SIZE

Log Ckpt writes: 由于 LOG_CHECKPOINT_INTERVAL 和 LOG_CHECKPOINT_TIMEOUT 驅(qū)動(dòng)的增量檢查點(diǎn)而做出的物理寫 WRITES_LOG_CHECKPOINT_SETTINGS

Other Settings Writes ：由于其他設(shè)置(例如FAST_START_IO_TARGET）而引起的物理寫， WRITES_OTHER_SETTINGS

Autotune Ckpt Writes : 由于自動(dòng)調(diào)優(yōu)檢查點(diǎn)而引起的物理寫， WRITES_AUTOTUNE

Thread Ckpt Writes ：由于thread checkpoint而引起的物理寫，WRITES_FULL_THREAD_CKPT
B 代表開始點(diǎn)， E 代表結(jié)尾

Targt MTTR (s) : 目標(biāo)MTTR (mean time to recover)意為有效恢復(fù)時(shí)間，單位為秒。 TARGET_MTTR 的計(jì)算基于給定的參數(shù)FAST_START_MTTR_TARGET，而TARGET_MTTR作為內(nèi)部使用。實(shí)際在使用中 Target MTTR未必能和FAST_START_MTTR_TARGET一樣。如果FAST_START_MTTR_TARGET過小，那么TARGET_MTTR 將是系統(tǒng)條件所允許的最小估算值；如果FAST_START_MTTR_TARGET過大，則TARGET_MTTR以保守算法計(jì)算以獲得完成恢復(fù)的最長(zhǎng)估算時(shí)間。

estimated_mttr (s): 當(dāng)前基于臟buffer和重做日志塊的數(shù)量，而評(píng)估出的有效恢復(fù)時(shí)間。它的估算告訴用戶以當(dāng)下系統(tǒng)的負(fù)載若發(fā)生實(shí)例crash，則需要多久時(shí)間來做crash recovery的前滾操作，之后才能打開數(shù)據(jù)庫。

Recovery Estd IOs ：實(shí)際是當(dāng)前buffer cache中的臟塊數(shù)量，一旦實(shí)例崩潰這些臟塊要被前滾

Actual RedoBlks ：當(dāng)前實(shí)際需要恢復(fù)的redo重做塊數(shù)量

Target RedoBlks ：是 Log Sz RedoBlks 、Log Ckpt Timeout RedoBlks、 Log Ckpt Interval RedoBlks 三者的最小值

Log Sz RedoBlks : 代表必須在log file switch日志切換之前完成的 checkpoint 中涉及到的redo block，也叫max log lag；數(shù)據(jù)來源select LOGFILESZ from X$targetrba; select LOG_FILE_SIZE_REDO_BLKS from v$instance_recovery;

Log Ckpt Timeout RedoBlks ：為了滿足LOG_CHECKPOINT_TIMEOUT 所需要處理的redo block數(shù)，lag for checkpoint timeout ；數(shù)據(jù)來源select CT_LAG from x$targetrba;

Log Ckpt Interval RedoBlks ：為了滿足LOG_CHECKPOINT_INTERVAL 所需要處理的redo block數(shù)， lag for checkpoint interval；數(shù)據(jù)來源select CI_LAG from x$targetrba;

Opt Log Sz(M) : 基于FAST_START_MTTR_TARGET 而估算出來的redo logfile 的大小，單位為MB 。 Oracle官方推薦創(chuàng)建的重做日志大小至少大于這個(gè)估算值

Estd RAC Avail Time ：指評(píng)估的 RAC中節(jié)點(diǎn)失敗后集群從凍結(jié)到部分可用的時(shí)間，這個(gè)指標(biāo)僅在RAC中可用，單位為秒。 ESTD_CLUSTER_AVAILABLE_TIME

7-2 Buffer Pool Advisory 緩沖池建議

Buffer Pool Advisory                      DB/Inst: ITSCMP/itscmp2  Snap: 70723
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate

                                    Est
                                   Phys      Estimated                  Est
    Size for   Size      Buffers   Read     Phys Reads     Est Phys %DBtime
P    Est (M) Factor  (thousands) Factor    (thousands)    Read Time for Rds
--- -------- ------ ------------ ------ -------------- ------------ -------
D      1,920     .1          227    4.9  1,110,565,597            1 1.0E+09
D      3,840     .2          454    3.6    832,483,886            1 7.4E+08
D      5,760     .3          680    2.8    634,092,578            1 5.6E+08
D      7,680     .4          907    2.2    500,313,589            1 4.3E+08
D      9,600     .5        1,134    1.8    410,179,557            1 3.5E+08
D     11,520     .6        1,361    1.5    348,214,283            1 2.9E+08
D     13,440     .7        1,588    1.3    304,658,441            1 2.5E+08
D     15,360     .8        1,814    1.2    273,119,808            1 2.2E+08
D     17,280     .9        2,041    1.1    249,352,943            1 2.0E+08
D     19,200    1.0        2,268    1.0    230,687,206            1 1.8E+08
D     19,456    1.0        2,298    1.0    228,664,269            1 1.8E+08
D     21,120    1.1        2,495    0.9    215,507,858            1 1.7E+08
D     23,040    1.2        2,722    0.9    202,816,787            1 1.6E+08
D     24,960    1.3        2,948    0.8    191,974,196            1 1.5E+08
D     26,880    1.4        3,175    0.8    182,542,765            1 1.4E+08
D     28,800    1.5        3,402    0.8    174,209,199            1 1.3E+08
D     30,720    1.6        3,629    0.7    166,751,631            1 1.2E+08
D     32,640    1.7        3,856    0.7    160,002,420            1 1.2E+08
D     34,560    1.8        4,082    0.7    153,827,351            1 1.1E+08
D     36,480    1.9        4,309    0.6    148,103,338            1 1.1E+08
D     38,400    2.0        4,536    0.6    142,699,866            1 1.0E+08

緩沖池的顆粒大小可以參考 SELECT * FROM V$SGAINFO where name like(‘Granule%’);
P 指緩沖池的名字可能包括有 D default buffer pool , K Keep Pool , R recycle Pool

Size For Est(M): 指以該尺寸的buffer pool作為評(píng)估的對(duì)象，一般是目前current size的 10% ~ 200%，以便了解 buffer pool 增大 ~減小對(duì)物理讀的影響

Size Factor : 尺寸因子，只對(duì)應(yīng)buffer pool 大小對(duì) 當(dāng)前設(shè)置的比例因子，例如current_size是 100M ，則如果評(píng)估值是110M 那么 size Factor 就是 1.1

Buffers (thousands) ：指這個(gè)buffer pool 尺寸下的buffer 數(shù)量，要乘以1000才是實(shí)際值

Est Phys Read Factor ：評(píng)估的物理讀因子，　例如當(dāng)前尺寸的buffer pool 會(huì)引起100個(gè)物理讀，則別的尺寸的buffer pool如果引起 120個(gè)物理讀，那么對(duì)應(yīng)尺寸的Est Phys Read Factor就是1.2

Estimated Phys Reads (thousands)：評(píng)估的物理讀數(shù)目，　要乘以　1000才是實(shí)際值，顯然不同尺寸的buffer pool對(duì)應(yīng)不同的評(píng)估的物理讀數(shù)目

Est Phys Read Time ：評(píng)估的物理讀時(shí)間

Est %DBtime for Rds：評(píng)估的物理讀占DB TIME的比率

我們看buffer pool advisory 一般有2個(gè)目的：

在物理讀較多的情況下，希望通過增加buffer pool 大小來緩解物理讀等待，這是我們關(guān)注Size Factor > 1的buffer pool尺寸是否能共有效減少Est Phys Read Factor，如果Est Phys Read Factor隨著Size Factor 增大而顯著減少，那么說明增大buffer cache 是可以有效減少物理讀的。
在內(nèi)存緊張的情況下，希望從buffer pool中勻出部分內(nèi)存來移作他用，但是又不希望 buffer cache變小導(dǎo)致物理讀增多性能下降，則此時(shí) 觀察Est Phys Read Factor 是否隨著Size Factor 減小而顯著增大，如果不是則說明減少部分buffer cache 不會(huì)導(dǎo)致物理讀大幅增加，也就可以安心減少 buffer cache

注意 Size Factor 和 Est Phys Read Factor之間不是簡(jiǎn)單的線性關(guān)系，所以需要人為介入評(píng)估得失

7-3 PGA Aggr Summary

PGA Aggr Summary                 Snaps: 70719-70723
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory

PGA Cache Hit %   W/A MB Processed  Extra W/A MB Read/Written
--------------- ------------------ --------------------------
           99.9            412,527                        375

PGA Cache Hit % : 指 W/A WorkArea工作區(qū)的數(shù)據(jù)僅在內(nèi)存中處理的比率， PGA緩存命中率

workarea是PGA中負(fù)責(zé)處理排序、哈希連接和位圖合并操作的區(qū)域; workarea 也叫做 SQL 作業(yè)區(qū)域

W/A MB processes: 指在Workarea中處理過的數(shù)據(jù)的量，單位為MB

Extra W/A MB Read/Written : 指額外從磁盤上讀寫的工作區(qū)數(shù)據(jù)，單位為 MB

7-4 PGA Aggr Target Stats

Warning:  pga_aggregate_target was set too low for current workload, as this
          value was exceeded during this interval.  Use the PGA Advisory view
          to help identify a different value for pga_aggregate_target.
PGA Aggr Target Stats       Snaps: 70719-70723
-> B: Begin Snap   E: End Snap (rows dentified with B or E contain data
   which is absolute i.e. not diffed over the interval)
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used    - amount of memory used for all Workareas (manual + auto)
-> %PGA W/A Mem    - percentage of PGA memory allocated to workareas
-> %Auto W/A Mem   - percentage of workarea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem    - percentage of workarea memory under manual control

                                                %PGA  %Auto   %Man
    PGA Aggr   Auto PGA   PGA Mem    W/A PGA     W/A    W/A    W/A Global Mem
   Target(M)  Target(M)  Alloc(M)    Used(M)     Mem    Mem    Mem   Bound(K)
- ---------- ---------- ---------- ---------- ------ ------ ------ ----------
B      8,192        512   23,690.5      150.1     .6  100.0     .0    838,860
E      8,192        512   23,623.6      156.9     .7  100.0     .0    838,860
          -------------------------------------------------------------

此環(huán)節(jié)的數(shù)據(jù)來源主要是 WRH$_PGASTAT

PGA Aggr Target(M) ：本質(zhì)上就是pga_aggregate_target ，當(dāng)然在AMM(memory_target)環(huán)境下這個(gè)值可能會(huì)自動(dòng)變化

Auto PGA Target(M) : 在自動(dòng)PGA 管理模式下實(shí)際可用的工作區(qū)內(nèi)存 “aggregate PGA auto target “，因?yàn)镻GA還有其他用途，不能全部作為workarea memory

PGA Mem Alloc(M) ：目前已分配的PGA內(nèi)存，　alloc 不等于 inuse 即分配的內(nèi)存不等于在使用的內(nèi)存，理論上PGA會(huì)將確實(shí)不使用的內(nèi)存返回給OS(PGA memory freed back to OS) ，但是存在PGA占用大量?jī)?nèi)存而不釋放的場(chǎng)景

在上例中 pga_aggregate_target 僅為8192M ，而實(shí)際processes 在 2,615~ 8000之間，如果一個(gè)進(jìn)程耗費(fèi)5MB的PGA 也需要 10000M的PGA ，而實(shí)際這里 PGA Mem Alloc(M)是23,690 M ，這說明存在PGA 的過載，需要調(diào)整pga_aggregate_target

W/A PGA Used(M) ：所有的工作區(qū)workarea(包括manual和 auto)使用的內(nèi)存總和量，單位為MB

%PGA W/A Mem: 分配給workarea的內(nèi)存量占總的PGA的比例， (W/A PGA Used)/PGA Mem Alloc

%Auto W/A Mem : AUTO 自動(dòng)工作區(qū)管理所控制的內(nèi)存(workarea_size_policy=AUTO) 占總的workarea內(nèi)存的比例

%Man W/A Mem : MANUAL 手動(dòng)工作區(qū)管理所控制的內(nèi)存(workarea_size_policy=MANUAL)占總的workarea內(nèi)存的比例

Global Mem Bound(K) : 指在自動(dòng)PGA管理模式下一個(gè)工作區(qū)所能分配的最大內(nèi)存(注意一個(gè)SQL執(zhí)行過程中可能有多個(gè)工作區(qū)workarea)。 Global Mem Bound(K)這個(gè)指標(biāo)在實(shí)例運(yùn)行過程中將被持續(xù)性的修正，以反應(yīng)數(shù)據(jù)庫當(dāng)時(shí)工作區(qū)的負(fù)載情況。顯然在有眾多活躍工作區(qū)的系統(tǒng)負(fù)載下相應(yīng)地Global Mem Bound將會(huì)下降。但應(yīng)當(dāng)保持global bound值不要小于1 MB ，否則建議調(diào)高pga_aggregate_target

7-5 PGA Aggr Target Histogram

PGA Aggr Target Histogram           Snaps: 70719-70723
-> Optimal Executions are purely in-memory operations

  Low     High
Optimal Optimal    Total Execs  Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- -------------- ------------ ------------
     2K      4K        262,086        262,086            0            0
    64K    128K            497            497            0            0
   128K    256K            862            862            0            0
   256K    512K            368            368            0            0
   512K   1024K        440,585        440,585            0            0
     1M      2M         68,313         68,313            0            0
     2M      4M            169            161            8            0
     4M      8M             50             42            8            0
     8M     16M             82             82            0            0
    16M     32M              1              1            0            0
    32M     64M             12             12            0            0
   128M    256M              2              0            2            0
          -------------------------------------------------------------

數(shù)據(jù)來源：WRH$_SQL_WORKAREA_HISTOGRAM

Low Optimal：此行所包含工作區(qū)workarea最適合內(nèi)存要求的下限

High Optimal：此行所包含工作區(qū)workarea最適合內(nèi)存要求的上限

Total Execs: 在 Low Optimal~High Optimal 范圍工作區(qū)內(nèi)完成的總執(zhí)行數(shù)

Optimal execs: optimal 執(zhí)行是指完全在PGA內(nèi)存中完成的執(zhí)行次數(shù)

1-pass Execs : 指操作過程中僅發(fā)生1次磁盤讀取的執(zhí)行次數(shù)

M-pass Execs: 指操作過程中發(fā)生了1次以上的磁盤讀取，頻發(fā)磁盤讀取的執(zhí)行次數(shù)

7-6 PGA Memory Advisory

PGA Memory Advisory                  Snap: 70723
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
   where Estd PGA Overalloc Count is 0

                                       Estd Extra    Estd P Estd PGA
PGA Target    Size           W/A MB   W/A MB Read/    Cache Overallo    Estd
  Est (MB)   Factr        Processed Written to Disk   Hit %    Count    Time
---------- ------- ---------------- ---------------- ------ -------- -------
     1,024     0.1  2,671,356,938.7    387,531,258.9   87.0 1.07E+07 7.9E+11
     2,048     0.3  2,671,356,938.7    387,529,979.1   87.0 1.07E+07 7.9E+11
     4,096     0.5  2,671,356,938.7    387,518,881.8   87.0 1.07E+07 7.9E+11
     6,144     0.8  2,671,356,938.7    387,420,749.5   87.0 1.07E+07 7.9E+11
     8,192     1.0  2,671,356,938.7     23,056,196.5   99.0 1.07E+07 6.9E+11
     9,830     1.2  2,671,356,938.7     22,755,192.6   99.0 6.81E+06 6.9E+11
    11,469     1.4  2,671,356,938.7     20,609,438.5   99.0 4.15E+06 6.9E+11
    13,107     1.6  2,671,356,938.7     19,021,139.1   99.0  581,362 6.9E+11
    14,746     1.8  2,671,356,938.7     18,601,191.0   99.0  543,531 6.9E+11
    16,384     2.0  2,671,356,938.7     18,561,361.1   99.0  509,687 6.9E+11
    24,576     3.0  2,671,356,938.7     18,527,422.3   99.0  232,817 6.9E+11
    32,768     4.0  2,671,356,938.7     18,511,872.6   99.0  120,180 6.9E+11
    49,152     6.0  2,671,356,938.7     18,500,815.3   99.0    8,021 6.9E+11
    65,536     8.0  2,671,356,938.7     18,498,733.0   99.0        0 6.9E+11

PGA Target Est (MB) 用以評(píng)估的 PGA_AGGREGATE _TARGET值

Size Factr ，當(dāng)前用以評(píng)估的PGA_AGGREGATE _TARGET 和當(dāng)前實(shí)際設(shè)置的PGA_AGGREGATE _TARGET 之間的比例因子 PGA Target Est / PGA_AGGREGATE_TARGE

W/A MB Processed ：workarea中要處理的數(shù)據(jù)量，單位為MB

Estd Extra W/A MB Read/ Written to Disk : 以 one-pass 、M-Pass方式處理的數(shù)據(jù)量預(yù)估值，單位為MB

Estd P Cache Hit % : 預(yù)估的PGA緩存命中率

Estd PGA Overalloc Count: 預(yù)估的PGA過載量，如上文所述PGA_AGGREGATE _TARGET僅是一個(gè)目標(biāo)值，無法真正限制PGA內(nèi)存的使用，當(dāng)出現(xiàn) PGA內(nèi)存硬性需求時(shí)會(huì)產(chǎn)生PGA overallocate 過載(When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0)

7-7 Shared Pool Advisory

Shared Pool Advisory                Snap: 70723
-> SP: Shared Pool     Est LC: Estimated Library Cache   Factr: Factor
-> Note there is often a 1:Many correlation between a single logical object
   in the Library Cache, and the physical number of memory objects associated
   with it.  Therefore comparing the number of Lib Cache objects (e.g. in
   v$librarycache), with the number of Lib Cache Memory Objects is invalid.

                                       Est LC Est LC  Est LC Est LC   
  Shared    SP   Est LC                  Time   Time    Load   Load       Est LC
    Pool  Size     Size       Est LC    Saved  Saved    Time   Time      Mem Obj
 Size(M) Factr      (M)      Mem Obj      (s)  Factr     (s)  Factr     Hits (K)
-------- ----- -------- ------------ -------- ------ ------- ------ ------------
     304    .8       56        3,987    7,728    1.0      61    1.4          332
     352    .9      101        6,243    7,745    1.0      44    1.0          334
     400   1.0      114        7,777    7,745    1.0      44    1.0          334
     448   1.1      114        7,777    7,745    1.0      44    1.0          334
     496   1.2      114        7,777    7,745    1.0      44    1.0          334
     544   1.4      114        7,777    7,745    1.0      44    1.0          334
     592   1.5      114        7,777    7,745    1.0      44    1.0          334
     640   1.6      114        7,777    7,745    1.0      44    1.0          334
     688   1.7      114        7,777    7,745    1.0      44    1.0          334
     736   1.8      114        7,777    7,745    1.0      44    1.0          334
     784   2.0      114        7,777    7,745    1.0      44    1.0          334
     832   2.1      114        7,777    7,745    1.0      44    1.0          334
          -------------------------------------------------------------

Shared Pool Size(M) : 用以評(píng)估的shared pool共享池大小，在AMM /ASMM環(huán)境下 shared_pool 大小都可能浮動(dòng)

SP Size Factr ：共享池大小的比例因子，?。⊿hared Pool Size for Estim / SHARED_POOL_SIZE）

Estd LC Size(M) : 評(píng)估的 library cache 大小，單位為MB ，因?yàn)槭莝hared pool中包含 library cache 當(dāng)然還有其他例如row cache

Est LC Mem Obj 指評(píng)估的指定大小的共享池內(nèi)的library cache memory object的數(shù)量 ESTD_LC_MEMORY_OBJECTS

Est LC Time Saved(s): 指在指定的共享池大小情況下可找到需要的library cache memory objects，從而節(jié)約的解析時(shí)間。這些節(jié)約的解析時(shí)間也是花費(fèi)在共享池內(nèi)重復(fù)加載需要的對(duì)象(reload)，這些對(duì)象可能因?yàn)楣蚕沓貨]有足夠的free memory而被aged out. ESTD_LC_TIME_SAVED

Est LC Time Saved Factr : Est LC Time Saved(s)的比例因子，( Est LC Time Saved(s)/ Current LC Time Saved(s) ) ESTD_LC_TIME_SAVED_FACTOR

Est LC Load Time (s): 在指定的共享池大小情況下解析的耗時(shí)

Est LC Load Time Factr：Est LC Load Time (s)的比例因子， (Est LC Load Time (s)/ Current LC Load Time (s)) ESTD_LC_LOAD_TIME_FACTOR

Est LC Mem Obj Hits (K) : 在指定的共享池大小情況下需要的library cache memory object正好在共享池中被找到的次數(shù) ESTD_LC_MEMORY_OBJECT_HITS；

對(duì)于想縮小 shared_pool_size 共享池大小的需求，可以關(guān)注Est LC Mem Obj Hits (K) ，如上例中共享池為352M時(shí)Est LC Mem Obj Hits (K) 就為334且之后不動(dòng)，則可以考慮縮小shared_pool_size到該值，但要注意每個(gè)版本/平臺(tái)上對(duì)共享池的最低需求，包括RAC中g(shù)cs resource 、gcs shadow等資源均駐留在shared pool中，增大db_cache_size時(shí)要對(duì)應(yīng)關(guān)注。

7-8 SGA Target Advisory

SGA Target Advisory    Snap: 70723

SGA Target   SGA Size       Est DB     Est Physical
  Size (M)     Factor     Time (s)            Reads
---------- ---------- ------------ ----------------
     3,752        0.1 1.697191E+09 1.4577142918E+12
     7,504        0.3 1.222939E+09  832,293,601,354
    11,256        0.4 1.000162E+09  538,390,923,784
    15,008        0.5  895,087,191  399,888,743,900
    18,760        0.6  840,062,594  327,287,716,803
    22,512        0.8  806,389,685  282,881,041,331
    26,264        0.9  782,971,706  251,988,446,808
    30,016        1.0  765,293,424  228,664,652,276
    33,768        1.1  751,135,535  210,005,616,650
    37,520        1.3  739,350,016  194,387,820,900
    41,272        1.4  733,533,785  187,299,216,679
    45,024        1.5  732,921,550  187,299,216,679
    48,776        1.6  732,691,962  187,299,216,679
    52,528        1.8  732,538,908  187,299,216,679
    56,280        1.9  732,538,917  187,299,216,679
    60,032        2.0  732,462,391  187,299,458,716
          -------------------------------------------------------------

該環(huán)節(jié)數(shù)據(jù)來源于WRH$_SGA_TARGET_ADVICE

SGA target Size : 用以評(píng)估的sga target大小 (sga_target)

SGA Size Factor: SGA Size的比例因子， (est SGA target Size / Current SGA target Size )

Est DB Time (s): 評(píng)估對(duì)應(yīng)于該指定sga target size會(huì)產(chǎn)生多少量的DB TIME，單位為秒

Est Physical Reads：評(píng)估對(duì)應(yīng)該指定的sga target size 會(huì)產(chǎn)生多少的物理讀

7-9 Streams Pool Advisory

Streams Pool Advisory                     DB/Inst: ITSCMP/itscmp2  Snap: 70723

  Size for      Size   Est Spill   Est Spill Est Unspill Est Unspill
  Est (MB)    Factor       Count    Time (s)       Count    Time (s)
---------- --------- ----------- ----------- ----------- -----------
        64       0.5           0           0           0           0
       128       1.0           0           0           0           0
       192       1.5           0           0           0           0
       256       2.0           0           0           0           0
       320       2.5           0           0           0           0
       384       3.0           0           0           0           0
       448       3.5           0           0           0           0
       512       4.0           0           0           0           0
       576       4.5           0           0           0           0
       640       5.0           0           0           0           0
       704       5.5           0           0           0           0
       768       6.0           0           0           0           0
       832       6.5           0           0           0           0
       896       7.0           0           0           0           0
       960       7.5           0           0           0           0
     1,024       8.0           0           0           0           0
     1,088       8.5           0           0           0           0
     1,152       9.0           0           0           0           0
     1,216       9.5           0           0           0           0
     1,280      10.0           0           0           0           0

該環(huán)節(jié)只有當(dāng)使用了Streams 流復(fù)制時(shí)才會(huì)有必要數(shù)據(jù)，數(shù)據(jù)來源 WRH$_STREAMS_POOL_ADVICE

Size for Est (MB) : 用以評(píng)估的 streams pool大小

Size Factor ：streams pool大小的比例因子

Est Spill Count ：評(píng)估出的當(dāng)使用該大小的流池時(shí) message溢出到磁盤的數(shù)量 ESTD_SPILL_COUNT

Est Spill Time (s)：評(píng)估出的當(dāng)使用該大小的流池時(shí) message溢出到磁盤的耗時(shí)，單位為秒 ESTD_SPILL_TIME

Est Unspill Count：評(píng)估的　當(dāng)使用該大小的流池時(shí)　message unspill 即從磁盤上讀取的數(shù)量 ESTD_UNSPILL_COUNT

Est Unspill Time (s) ：評(píng)估的　當(dāng)使用該大小的流池時(shí) message unspill 即從磁盤上讀取的耗時(shí)，單位為秒 ESTD_UNSPILL_TIME

7-10 Java Pool Advisory

java pool的相關(guān)指標(biāo)與shared pool相似，不再鏖述

8 Wait Statistics

8-1 Buffer Wait Statistics

Buffer Wait Statistics          Snaps: 70719-70723
-> ordered by wait time desc, waits desc

Class                    Waits Total Wait Time (s)  Avg Time (ms)
------------------ ----------- ------------------- --------------
data block           8,442,041             407,259             48
undo header             16,212               1,711            106
undo block              21,023                 557             26
1st level bmb            1,038                 266            256
2nd level bmb              540                 185            342
bitmap block                90                  25            276
segment header             197                  13             66
file header block          132                   6             43
bitmap index block          18                   0              1
extent map                   2                   0              0

數(shù)據(jù)來源： WRH$_WAITSTAT

該環(huán)節(jié)是對(duì) 緩沖池中各類型(class) 塊等待的匯總信息， wait的原因一般是 buffer busy waits 和 read by other session

class 數(shù)據(jù)塊的class，一個(gè)oracle數(shù)據(jù)塊即有class 屬性還有type 屬性，數(shù)據(jù)塊中記錄type屬性(KCBH)，而在buffer header里存有class屬性(X$BH.class)

Waits: 該類型數(shù)據(jù)塊的等待次數(shù)

Total Wait Time (s) : 該類型數(shù)據(jù)塊的合計(jì)等待時(shí)間單位為秒

Avg Time (ms) : 該類型數(shù)據(jù)塊平均每次等待的耗時(shí)，單位 ms

如果用戶正使用 undo_management=AUTO 的SMU 則一般不會(huì)因?yàn)閞ollback segment過少而引起undo header block類塊的等待

對(duì)于INSERT 而引起的 buffer爭(zhēng)用等待：

1、對(duì)于手動(dòng)segment 管理MSSM 考慮增加Freelists、Freelist Groups

2、使用ASSM ，當(dāng)然ASSM本身沒什么參數(shù)可調(diào)

對(duì)于INSERT ON INDEX 引起的爭(zhēng)用：

使用反向索引key
使用HASH分區(qū)和本地索引
可能的情況下減少index的density

8-2 Enqueue Activity

enqueue 隊(duì)列鎖等待

Enqueue Activity  Snaps: 70719-70723
-> only enqueues with waits are shown
-> Enqueue stats gathered prior to 10g should not be compared with 10g data
-> ordered by Wait Time desc, Waits desc

Enqueue Type (Request Reason)
------------------------------------------------------------------------------
    Requests    Succ Gets Failed Gets       Waits  Wt Time (s) Av Wt Time(ms)
------------ ------------ ----------- ----------- ------------ --------------
TX-Transaction (index contention)
     201,270      201,326           0     193,948       97,517         502.80
TM-DML
     702,731      702,681           4       1,081       46,671      43,174.08
SQ-Sequence Cache
      28,643       28,632           0      17,418       35,606       2,044.19
HW-Segment High Water Mark
       9,210        8,845         376       1,216       12,505      10,283.85
TX-Transaction (row lock contention)
       9,288        9,280           0       9,232       10,486       1,135.80
CF-Controlfile Transaction
      15,851       14,094       1,756       2,798        4,565       1,631.64
TX-Transaction (allocate ITL entry)
         471          369         102         360          169         469.28

Enqueue Type (Request Reason) enqueue 隊(duì)列的類型，大家在研究 enqueue 問題前至少搞清楚enqueue type 和enqueue mode ， enqueue type是隊(duì)列鎖所要保護(hù)的資源如 TM 表鎖 CF 控制文件鎖， enqueue mode 是持有隊(duì)列鎖的模式 (SS、SX 、S、SSX、X)

Requests : 申請(qǐng)對(duì)應(yīng)的enqueue type資源或者隊(duì)列轉(zhuǎn)換(enqueue conversion 例如 S 轉(zhuǎn) SSX ) 的次數(shù)

Succ Gets ：對(duì)應(yīng)的enqueue被成功申請(qǐng)或轉(zhuǎn)換的次數(shù)

Failed Gets ：對(duì)應(yīng)的enqueue的申請(qǐng)　或者轉(zhuǎn)換失敗的次數(shù)

Waits?。河蓪?duì)應(yīng)的enqueue的申請(qǐng)或者轉(zhuǎn)換而造成等待的次數(shù)

Wt Time (s) ：由對(duì)應(yīng)的enqueue的申請(qǐng)或者轉(zhuǎn)換而造成等待的等待時(shí)間

Av Wt Time(ms) ：由對(duì)應(yīng)的enqueue的申請(qǐng)或者轉(zhuǎn)換而造成等待的平均等待時(shí)間， Wt Time (s) / Waits ,單位為ms

主要的enqueue 等待事件：

enq: TX – row lock/index contention、allocate ITL等待事件

enq: TM – contention等待事件

Oracle隊(duì)列鎖enq:TS,Temporary Segment (also TableSpace)

9-1 Undo Segment Summary

Undo Segment Summary            Snaps: 70719-70723
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count,  OOS - Out of Space count
-> Undo segment block stats:
-> uS - unexpired Stolen,   uR - unexpired Released,   uU - unexpired reUsed
-> eS - expired   Stolen,   eR - expired   Released,   eU - expired   reUsed

Undo   Num Undo       Number of  Max Qry   Max Tx Min/Max   STO/     uS/uR/uU/
 TS# Blocks (K)    Transactions  Len (s) Concurcy TR (mins) OOS      eS/eR/eU
---- ---------- --------------- -------- -------- --------- ----- --------------
   4       85.0         200,127   55,448      317 1040.2/10 0/0   0/0/0/0/0/0
          -------------------------------------------------------------

Undo Segment Stats                 Snaps: 70719-70723
-> Most recent 35 Undostat rows, ordered by Time desc

                Num Undo    Number of Max Qry  Max Tx Tun Ret STO/    uS/uR/uU/
End Time          Blocks Transactions Len (s)   Concy  (mins) OOS     eS/eR/eU
------------ ----------- ------------ ------- ------- ------- ----- ------------
29-Aug 05:52      11,700       35,098  55,448     234   1,070 0/0   0/0/0/0/0/0
29-Aug 05:42      12,203       24,677  54,844     284   1,065 0/0   0/0/0/0/0/0
29-Aug 05:32      14,132       37,826  54,241     237   1,060 0/0   0/0/0/0/0/0
29-Aug 05:22      14,379       32,315  53,637     317   1,050 0/0   0/0/0/0/0/0
29-Aug 05:12      15,693       34,157  53,033     299   1,045 0/0   0/0/0/0/0/0
29-Aug 05:02      16,878       36,054  52,428     250   1,040 0/0   0/0/0/0/0/0

數(shù)據(jù)來源： WRH$_UNDOSTAT ， undo相關(guān)的使用信息每10分鐘刷新到v$undostat中

Undo Extent有三種狀態(tài) active 、unexpired 、expired

active => extent中包括了活動(dòng)的事務(wù) ，active的undo extent 一般不允許被其他事務(wù)重用覆蓋

unexpired => extent中沒有活動(dòng)的事務(wù)，但相關(guān)undo 記錄從inactive到目前還未經(jīng)過undo retention(注意 auto undo retention的問題因?yàn)檫@個(gè)特性可能在觀察dba_undo_extents時(shí)看到大部分block都是unexpired，這是正常的) 指定的時(shí)間，所以為unexpired。對(duì)于沒有g(shù)uarantee retention的undo tablespace而言，unexpired extent可能被 steal 為其他事物重用

expired => extent中沒有活動(dòng)事務(wù)，且超過了undo retention的時(shí)間

Undo TS# 在使用的這個(gè)undo 表空間的表空間號(hào)，一個(gè)實(shí)例同一時(shí)間只能用1個(gè)undo tablespace ， RAC不同節(jié)點(diǎn)可以用不同的undo tablespace

Num Undo Blocks (K) 指被消費(fèi)的 undo 數(shù)據(jù)塊的數(shù)量, (K)代表要乘以1000才是實(shí)際值；可以用該指標(biāo)來評(píng)估系統(tǒng)對(duì)undo block的消費(fèi)量，以便基于實(shí)際負(fù)載情況來評(píng)估UNDO表空間的大小

Number of Transactions 指該段時(shí)間內(nèi)該undo表空間上執(zhí)行過的事務(wù)transaction總量

Max Qry Len (s) 該時(shí)段內(nèi) 持續(xù)最久的查詢時(shí)間，單位為秒

Max Tx Concy 該時(shí)段內(nèi) 最大的事務(wù)并發(fā)量

Min/Max TR (mins) 最小和最大的tuned undo retention ，單位為分鐘； tuned undo retention 是自動(dòng)undo調(diào)優(yōu)特性，見undo自動(dòng)調(diào)優(yōu)介紹。

STO/ OOS STO 指 ORA-01555 Snapshot Too Old錯(cuò)誤出現(xiàn)的次數(shù)； OOS – 指Out of Space count 錯(cuò)誤出現(xiàn)的次數(shù)

uS – unexpired Stolen 嘗試從未過期的undo extent中偷取undo space的次數(shù)

uR – unexpired Released 從未過期的undo extent中釋放的塊數(shù)目

uU – unexpired reUsed 未過期的undo extent中的block被其他事務(wù)重用的塊數(shù)目

eS – expired Stolen 嘗試從過期的undo extent中偷取undo space的次數(shù)

eR – expired Released 從過期的undo extent中釋放的塊數(shù)目

eU – expired reUsed 過期的undo extent中的block被其他事務(wù)重用的塊數(shù)目

UNXPSTEALCNT	NUMBER	Number of attempts to obtain undo space by stealing unexpired extents from other transactions
UNXPBLKRELCNT	NUMBER	Number of unexpired blocks removed from certain undo segments so they can be used by other transactions
UNXPBLKREUCNT	NUMBER	Number of unexpired undo blocks reused by transactions
EXPSTEALCNT	NUMBER	Number of attempts to steal expired undo blocks from other undo segments
EXPBLKRELCNT	NUMBER	Number of expired undo blocks stolen from other undo segments
EXPBLKREUCNT	NUMBER	Number of expired undo blocks reused within the same undo segments
SSOLDERRCNT	NUMBER	Identifies the number of times the error ORA-01555 occurred. You can use this statistic to decide whether or not the UNDO_RETENTION initialization parameter is set properly given the size of the undo tablespace. Increasing the value of UNDO_RETENTION can reduce the occurrence of this error.

10-1 Latch Activity

Latch Activity        Snaps: 70719-70723
-> "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
   willing-to-wait latch get requests
-> "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
-> "Pct Misses" for both should be very close to 0.0

                                           Pct    Avg   Wait                 Pct
                                    Get    Get   Slps   Time       NoWait NoWait
Latch Name                     Requests   Miss  /Miss    (s)     Requests   Miss
------------------------ -------------- ------ ------ ------ ------------ ------
AQ deq hash table latch               4    0.0             0            0    N/A
ASM Keyed state latch             9,048    0.1    0.2      0            0    N/A
ASM allocation                   15,017    0.2    0.8      1            0    N/A
ASM db client latch              72,745    0.0             0            0    N/A
ASM map headers                   5,860    0.6    0.6      1            0    N/A
ASM map load waiting lis          1,462    0.0             0            0    N/A
ASM map operation freeli         63,539    0.1    0.4      1            0    N/A
ASM map operation hash t     76,484,447    0.1    1.0     66            0    N/A

latch name Latch閂的名字

Get Requests latch被以willing-to-wait模式申請(qǐng)并獲得的次數(shù)

Pct Get Miss miss是指latch被以willing-to-wait 模式申請(qǐng)但是申請(qǐng)者必須等待的次數(shù)， Pct Get Miss = Miss/Get Requests ; miss可以從后面的Latch Sleep Breakdown 獲得

Avg Slps /Miss Sleep 是指latch被以willing-to-wait模式申請(qǐng)最終導(dǎo)致session需要sleep以等待該latch的次數(shù) ； Avg Slps /Miss = Sleeps/ Misses ; Sleeps可以從后面的Latch Sleep Breakdown 獲得

Wait Time (s) 指花費(fèi)在等待latch上的時(shí)間，單位為秒

NoWait Requests 指latch被以no-wait模式來申請(qǐng)的次數(shù)

Pct NoWait Miss 以no-wait模式來申請(qǐng)latch但直接失敗的次數(shù)

對(duì)于高并發(fā)的latch例如cache buffers chains，其Pct Misses應(yīng)當(dāng)十分接近于0

一般的調(diào)優(yōu)原則：

如果latch : cache buffers chains是 Top 5 事件，則需要考慮優(yōu)化SQL減少全表掃描并減少Top buffer gets SQL語句的邏輯讀

如果latch : redo copy 、redo allocation 等待較多，則可以考慮增大LOG_BUFFER

如果latch:library cache 發(fā)生較多，則考慮增大shared_pool_size

10-2 Latch Sleep Breakdown

Latch Sleep Breakdown             DB/Inst: ITSCMP/itscmp2  Snaps: 70719-70723
-> ordered by misses desc

                                       Get                                 Spin
Latch Name                        Requests       Misses      Sleeps        Gets
-------------------------- --------------- ------------ ----------- -----------
cache buffers chains         3,365,097,866   12,831,875     130,058  12,683,450
row cache objects               69,050,058      349,839       1,320     348,649
session idle bit               389,437,460      268,285       2,768     265,752
enqueue hash chains              8,698,453      239,880      22,476     219,950
ges resource hash list           8,388,730      158,894      70,728      91,104
gc element                     100,383,385      135,759       6,285     129,742
gcs remastering latch           12,213,169       72,373           1      72,371
enqueues                         4,662,545       46,374         259      46,155
ASM map operation hash tab      76,484,447       46,231      45,210       1,952
Lsod array latch                    72,598       24,224      24,577       1,519

latch name Latch閂的名字

Get Requests latch被以willing-to-wait模式申請(qǐng)并獲得的次數(shù)

misses 是指latch被以willing-to-wait 模式申請(qǐng)但是申請(qǐng)者必須等待的次數(shù)

9i以后miss之后一般有2種情況 spin gets了或者sleep一睡不醒直到被post，具體見全面解析9i以后Oracle Latch閂鎖原理；

8i以前的latch算法可以參考：Oracle Latch:一段描繪Latch運(yùn)作的偽代碼

所以一般來說9i以后的 misses= Sleeps+ Spin Gets ，雖然不是絕對(duì)如此

Sleeps 是指latch被以willing-to-wait模式申請(qǐng)最終導(dǎo)致session需要sleep以等待該latch的次數(shù)

Spin Gets 以willing-to-wait模式去申請(qǐng)latch，在miss之后以spin方式獲得了latch的次數(shù)

10-3 Latch Miss Sources

Latch Miss Sources           Snaps: 70719-70723
-> only latches with sleeps are shown
-> ordered by name, sleeps desc

                                                     NoWait              Waiter
Latch Name               Where                       Misses     Sleeps   Sleeps
------------------------ -------------------------- ------- ---------- --------
ASM Keyed state latch    kfksolGet                        0          1        1
ASM allocation           kfgpnSetDisks2                   0         17        0
ASM allocation           kfgpnClearDisks                  0          5        0
ASM allocation           kfgscCreate                      0          4        0
ASM allocation           kfgrpGetByName                   0          1       26
ASM map headers          kffmUnidentify_3                 0          7        8
ASM map headers          kffmAllocate                     0          6        0
ASM map headers          kffmIdentify                     0          6       11
ASM map headers          kffmFree                         0          1        0
ASM map operation freeli kffmTranslate2                   0         15        8
ASM map operation hash t kffmUnidentify                   0     44,677   36,784
ASM map operation hash t kffmTranslate                    0        220    3,517

數(shù)據(jù)來源為DBA_HIST_LATCH_MISSES_SUMMARY

latch name Latch閂的名字
where : 指哪些代碼路徑內(nèi)核函數(shù)持有過這些該latch ，而不是哪些代碼路徑要申請(qǐng)這些latch；例如kcbgtcr函數(shù)的作用是Get a block for Consistent read，其持有l(wèi)atch :cache buffers chain是很正常的事情

NoWait Misses: 以no-wait模式來申請(qǐng)latch但直接失敗的次數(shù)

Sleeps: 指latch被以willing-to-wait模式申請(qǐng)最終導(dǎo)致session需要sleep以等待該latch的次數(shù) time of sleeps resulted in making the latch request

Waiter Sleeps：等待者休眠的次數(shù) times of sleeps that waiters did for each where; Sleep 是阻塞者等待的次數(shù) ， Waiter Sleeps是被阻塞者等待的次數(shù)

10-4 Mutex Sleep Summary

Mutex Sleep Summary       Snaps: 70719-70723
-> ordered by number of sleeps desc

                                                                         Wait
Mutex Type            Location                               Sleeps    Time (ms)
--------------------- -------------------------------- ------------ ------------
Cursor Pin            kksfbc [KKSCHLFSP2]                     4,364       14,520
Cursor Pin            kkslce [KKSCHLPIN2]                     2,396        2,498
Library Cache         kglpndl1  95                              903          475
Library Cache         kglpin1   4                               800          458
Library Cache         kglpnal2  91                              799          259
Library Cache         kglget1   1                               553        1,697
Library Cache         kglpnal1  90                              489           88
Library Cache         kgllkdl1  85                              481        1,528
Cursor Pin            kksLockDelete [KKSCHLPIN6]                410          666
Cursor Stat           kkocsStoreBindAwareStats [KKSSTA          346          497
Library Cache         kglhdgn2 106                              167          348
Library Cache         kglhdgh2  64                               26           84
Library Cache         kgldtin1  42                               19           55
Cursor Pin            kksfbc [KKSCHLPIN1]                        13           34
Library Cache         kglhdgn1  62                               11           13
Library Cache         kgllkal1  80                                9           12
Library Cache         kgllkc1   57                                6            0
Cursor Pin            kksSetBindType [KKSCHLPIN3]                 5            5
Library Cache         kglGetHandleReference 124                   4           20
Library Cache         kglUpgradeLock 119                          4            0
Library Cache         kglget2   2                                 3            0
Library Cache         kglati1   45                                1            0
Library Cache         kglini1   32                                1            0
Library Cache         kglobld1  75                                1            0
Library Cache         kglobpn1  71                                1            0

Mutex是10.2.0.2以后引入的新的內(nèi)存鎖機(jī)制，具體對(duì)Mutex的描述見《深入理解Oracle中的Mutex》：http://www.askmaclean.com/archives/understanding-oracle-mutex.html

Mutex Type

Mutex的類型其實(shí)就是 mutex對(duì)應(yīng)的客戶的名字，在版本10.2中基本只有KKS使用Mutex，所以僅有3種:

Cursor Stat (kgx_kks1)
Cursor Parent (kgx_kks2)
Cursor Pin (kgx_kks3)

11g中增加了Library Cache

Location 發(fā)起對(duì)該Mutex申請(qǐng)的代碼路徑code location，而不是還持有該Mutex的代碼路徑或曰內(nèi)核函數(shù)

10.2中最常見的下面的幾個(gè)函數(shù)

kkspsc0 -負(fù)責(zé)解析游標(biāo) – 檢測(cè)我們正在解析的游標(biāo)是否有對(duì)象的parent cursor heap 0存在

kksfbc – 負(fù)責(zé)找到合適的子游標(biāo) 或者創(chuàng)建一個(gè)新的子游標(biāo)

kksFindCursorstat

Sleeps:

Mutex的Get和Sleep

當(dāng)一個(gè)Mutex被申請(qǐng)時(shí)，一般稱為一個(gè)get request。若初始的申請(qǐng)未能得到授權(quán)，則該進(jìn)程會(huì)因?yàn)榇舜紊暾?qǐng)而進(jìn)入到255次SPIN中(_mutex_spin_count Mutex spin count)，每次SPIN循環(huán)迭代過程中該進(jìn)程都會(huì)去看看Mutex被釋放了嗎。

若該Mutex在SPIN之后仍未被釋放，則該進(jìn)程針對(duì)申請(qǐng)的mutex進(jìn)入對(duì)應(yīng)的mutex wait等待事件中。實(shí)際進(jìn)程的等待事件和等待方式由mutex的類型鎖決定，例如 Cursor pin、Cursor Parent。舉例來說，這種等待可能是阻塞等待，也可以是sleep。

但是請(qǐng)注意在V$MUTEX_SLEEP_*視圖上的sleep列意味著等待的次數(shù)。相關(guān)代碼函數(shù)在開始進(jìn)入等待時(shí)自加這個(gè)sleep字段。

等待計(jì)時(shí)從進(jìn)程進(jìn)入等待前開始計(jì)算等待時(shí)間，當(dāng)一個(gè)進(jìn)程結(jié)束其等待，則等待的時(shí)間加入都總和total中。該進(jìn)程再次嘗試申請(qǐng)之前的Mutex，若該Mutex仍不可用，則它再次進(jìn)入spin/wait的循環(huán)。

V$MUTEX_SLEEP_HISTORY視圖的GETS列僅在成功申請(qǐng)到一個(gè)Mutex時(shí)才增加。

Wait Time (ms) 類似于latch，spin time 不算做mutex的消耗時(shí)間，它只包含等待消耗的時(shí)間。

=====================================================================
11 segment statistics 段級(jí)統(tǒng)計(jì)

11-1 Segments by Logical Reads

Segments by Logical Reads         DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> Total Logical Reads:   2,021,476,421
-> Captured Segments account for   83.7% of Total

           Tablespace                      Subobject  Obj.       Logical
Owner         Name    Object Name            Name     Type         Reads  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CONTENT_OW INDEX_TS   MZ_PRODUCT_ATTRIBUTE            INDEX  372,849,920   18.44
CONTENT_OW INDEX_TS   MZ_PRODUCT__LS_PK               INDEX  329,829,632   16.32
CONTENT_OW DATA_TS    MZ_PRODUCT_ATTRIBUTE            TABLE  218,419,008   10.80
CONTENT_OW PLAYLIST_A MZ_PLAYLIST_ARTIST              TABLE  182,426,240    9.02
CONTENT_OW DATA_TS    MZ_PRODUCT                      TABLE  108,597,376    5.37

owner : 數(shù)據(jù)段的所有者

Tablespace Name: 數(shù)據(jù)段所在表空間名

Object Name : 對(duì)象名

Subobject Name：子對(duì)象名，例如一個(gè)分區(qū)表的某個(gè)分區(qū)

obj Type: 對(duì)象類型一般為TABLE /INDEX 或者分區(qū)或子分區(qū)

Logical Reads ：該數(shù)據(jù)段上發(fā)生過的邏輯讀，單位為塊數(shù)*次數(shù)

%Total : 占總的邏輯讀的百分比， (當(dāng)前對(duì)象上發(fā)生過的邏輯讀/ Total DB 邏輯讀)

11-2 Segments by Physical Reads

Segments by Physical Reads         DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> Total Physical Reads:      56,839,035
-> Captured Segments account for   51.9% of Total

           Tablespace                      Subobject  Obj.      Physical
Owner         Name    Object Name            Name     Type         Reads  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CONTENT_OW SONG_TS    MZ_SONG                         TABLE    7,311,928   12.86
CONTENT_OW DATA_TS    MZ_CS_WORK_PENDING_R            TABLE    4,896,554    8.61
CONTENT_OW DATA_TS    MZ_CONTENT_PROVIDER_            TABLE    3,099,387    5.45
CONTENT_OW DATA_TS    MZ_PRODUCT_ATTRIBUTE            TABLE    1,529,971    2.69
CONTENT_OW DATA_TS    MZ_PUBLICATION                  TABLE    1,391,735    2.45

Physical Reads: 該數(shù)據(jù)段上發(fā)生過的物理讀，單位為塊數(shù)*次數(shù)

%Total : 占總的物理讀的百分比， (當(dāng)前對(duì)象上發(fā)生過的邏輯讀/ Total DB 邏輯讀)

11-3 Segments by Physical Read Requests

Segments by Physical Read Requests DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> Total Physical Read Requests:      33,936,360
-> Captured Segments account for   45.5% of Total

           Tablespace                      Subobject  Obj.     Phys Read
Owner         Name    Object Name            Name     Type      Requests  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CONTENT_OW DATA_TS    MZ_CONTENT_PROVIDER_            TABLE    3,099,346    9.13
CONTENT_OW DATA_TS    MZ_PRODUCT_ATTRIBUTE            TABLE    1,529,950    4.51
CONTENT_OW DATA_TS    MZ_PRODUCT                      TABLE    1,306,756    3.85
CONTENT_OW DATA_TS    MZ_AUDIO_FILE                   TABLE      910,537    2.68
CONTENT_OW INDEX_TS   MZ_PRODUCT_ATTRIBUTE            INDEX      820,459    2.42

Phys Read Requests ：物理讀的申請(qǐng)次數(shù)

%Total ：　(該段上發(fā)生的物理讀的申請(qǐng)次數(shù)/ physical read IO requests)

11-4 Segments by UnOptimized Reads

Segments by UnOptimized Reads      DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> Total UnOptimized Read Requests:         811,466
-> Captured Segments account for   58.5% of Total

           Tablespace                      Subobject  Obj.   UnOptimized
Owner         Name    Object Name            Name     Type         Reads  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CONTENT_OW DATA_TS    MZ_CONTENT_PROVIDER_            TABLE      103,580   12.76
CONTENT_OW SONG_TS    MZ_SONG                         TABLE       56,946    7.02
CONTENT_OW DATA_TS    MZ_IMAGE                        TABLE       47,017    5.79
CONTENT_OW DATA_TS    MZ_PRODUCT_ATTRIBUTE            TABLE       40,950    5.05
CONTENT_OW DATA_TS    MZ_PRODUCT                      TABLE       30,406    3.75

UnOptimized Reads UnOptimized Read Reqs = Physical Read Reqts – Optimized Read Reqs

Optimized Read Requests是指哪些滿足Exadata Smart Flash Cache ( or the Smart Flash Cache in OracleExadata V2 (Note that despite same name, concept and use of
‘Smart Flash Cache’ in Exadata V2 is different from ‘Smart Flash Cache’ in Database Smart Flash Cache)).的物理讀次數(shù) 。滿足從smart flash cache走的讀取申請(qǐng)唄認(rèn)為是optimized ，因?yàn)檫@些讀取要比普通從磁盤走快得多。

此外通過smart scan 讀取storage index的情況也被認(rèn)為是’optimized read requests’ ，源于可以避免讀取不相關(guān)的數(shù)據(jù)。

當(dāng)用戶不在使用Exadata時(shí)，則UnOptimized Read Reqs總是等于 Physical Read Reqts

%Total : (該段上發(fā)生的物理讀的UnOptimized Read Reqs / ( physical read IO requests – physical read requests optimized ))
11-5 Segments by Optimized Reads

Segments by Optimized Reads        DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> Total Optimized Read Requests:      33,124,894
-> Captured Segments account for   45.2% of Total

           Tablespace                      Subobject  Obj.     Optimized
Owner         Name    Object Name            Name     Type         Reads  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CONTENT_OW DATA_TS    MZ_CONTENT_PROVIDER_            TABLE    2,995,766    9.04
CONTENT_OW DATA_TS    MZ_PRODUCT_ATTRIBUTE            TABLE    1,489,000    4.50
CONTENT_OW DATA_TS    MZ_PRODUCT                      TABLE    1,276,350    3.85
CONTENT_OW DATA_TS    MZ_AUDIO_FILE                   TABLE      890,775    2.69
CONTENT_OW INDEX_TS   MZ_AM_REQUEST_IX3               INDEX      816,067    2.46

關(guān)于optimizerd read 上面已經(jīng)解釋過了，這里的單位是 request 次數(shù)

%Total : (該段上發(fā)生的物理讀的 Optimized Read Reqs/ physical read requests optimized )

11-6 Segments by Direct Physical Reads

Segments by Direct Physical Reads  DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> Total Direct Physical Reads:      14,118,552
-> Captured Segments account for   94.2% of Total

           Tablespace                      Subobject  Obj.        Direct
Owner         Name    Object Name            Name     Type         Reads  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CONTENT_OW SONG_TS    MZ_SONG                         TABLE    7,084,416   50.18
CONTENT_OW DATA_TS    MZ_CS_WORK_PENDING_R            TABLE    4,839,984   34.28
CONTENT_OW DATA_TS    MZ_PUBLICATION                  TABLE    1,361,133    9.64
CONTENT_OW DATA_TS    SYS_LOB0000203660C00            LOB          5,904     .04
CONTENT_OW DATA_TS    SYS_LOB0000203733C00            LOB          1,656     .01

Direct reads 直接路徑物理讀，單位為塊數(shù)*次數(shù)
%Total (該段上發(fā)生的direct path reads /Total physical reads direct )

11-7 Segments by Physical Writes

Segments by Physical Writes        DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> Total Physical Writes:         590,563
-> Captured Segments account for   38.3% of Total

           Tablespace                      Subobject  Obj.      Physical
Owner         Name    Object Name            Name     Type        Writes  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CONTENT_OW DATA_TS    MZ_CS_WORK_PENDING_R            TABLE       23,595    4.00
CONTENT_OW DATA_TS    MZ_PODCAST                      TABLE       19,834    3.36
CONTENT_OW INDEX_TS   MZ_IMAGE_IX2                    INDEX       16,345    2.77
SYS        SYSAUX     WRH$_ACTIVE_SESSION_ 1367_70520 TABLE       14,173    2.40
CONTENT_OW INDEX_TS   MZ_AM_REQUEST_IX3               INDEX        9,645    1.63

Physical Writes ，物理寫單位為塊數(shù)*次數(shù)

Total % (該段上發(fā)生的物理寫 /Total physical writes )

11-9 Segments by Physical Write Requests

Segments by Physical Write Requests   DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> Total Physical Write Requestss:         436,789
-> Captured Segments account for   43.1% of Total

           Tablespace                      Subobject  Obj.    Phys Write
Owner         Name    Object Name            Name     Type      Requests  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CONTENT_OW DATA_TS    MZ_CS_WORK_PENDING_R            TABLE       22,581    5.17
CONTENT_OW DATA_TS    MZ_PODCAST                      TABLE       19,797    4.53
CONTENT_OW INDEX_TS   MZ_IMAGE_IX2                    INDEX       14,529    3.33
CONTENT_OW INDEX_TS   MZ_AM_REQUEST_IX3               INDEX        9,434    2.16
CONTENT_OW DATA_TS    MZ_AM_REQUEST                   TABLE        8,618    1.97

Phys Write Requests 物理寫的請(qǐng)求次數(shù) ，單位為次數(shù)

%Total (該段上發(fā)生的物理寫請(qǐng)求次數(shù) /physical write IO requests )

11-10 Segments by Direct Physical Writes

Segments by Direct Physical Writes DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> Total Direct Physical Writes:          29,660
-> Captured Segments account for   18.3% of Total

           Tablespace                      Subobject  Obj.        Direct
Owner         Name    Object Name            Name     Type        Writes  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SYS        SYSAUX     WRH$_ACTIVE_SESSION_ 1367_70520 TABLE        4,601   15.51
CONTENT_OW DATA_TS    SYS_LOB0000203733C00            LOB            620    2.09
CONTENT_OW DATA_TS    SYS_LOB0000203660C00            LOB            134     .45
CONTENT_OW DATA_TS    SYS_LOB0000203779C00            LOB             46     .16
CONTENT_OW DATA_TS    SYS_LOB0000203796C00            LOB             41     .14

Direct Writes 直接路徑寫，單位額為塊數(shù)*次數(shù)
%Total 為(該段上發(fā)生的直接路徑寫 /physical writes direct )

11-11 Segments by Table Scans

Segments by Table Scans            DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> Total Table Scans:          10,713
-> Captured Segments account for    1.0% of Total

           Tablespace                      Subobject  Obj.         Table
Owner         Name    Object Name            Name     Type         Scans  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CONTENT_OW DATA_TS    MZ_PUBLICATION                  TABLE           92     .86
CONTENT_OW DATA_TS    MZ_CS_WORK_PENDING_R            TABLE           14     .13
CONTENT_OW SONG_TS    MZ_SONG                         TABLE            3     .03
CONTENT_OW DATA_TS    MZ_AM_REQUEST                   TABLE            1     .01

Table Scans 來源為dba_hist_seg_stat.table_scans_delta 不過這個(gè)指標(biāo)并不十分精確

11-12 Segments by DB Blocks Changes

Segments by DB Blocks Changes      DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> % of Capture shows % of DB Block Changes for each top segment compared
-> with total DB Block Changes for all segments captured by the Snapshot

           Tablespace                      Subobject  Obj.      DB Block    % of
Owner         Name    Object Name            Name     Type       Changes Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
CONTENT_OW INDEX_TS   MZ_AM_REQUEST_IX8               INDEX      347,856   10.21
CONTENT_OW INDEX_TS   MZ_AM_REQUEST_IX3A              INDEX      269,504    7.91
CONTENT_OW INDEX_TS   MZ_AM_REQUEST_PK                INDEX      251,904    7.39
CONTENT_OW DATA_TS    MZ_AM_REQUEST                   TABLE      201,056    5.90
CONTENT_OW INDEX_TS   MZ_PRODUCT_ATTRIBUTE            INDEX      199,888    5.86

DB Block Changes ，單位為塊數(shù)*次數(shù)

%Total : (該段上發(fā)生block changes / db block changes )

11-13 Segments by Row Lock Waits

Segments by Row Lock Waits        DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> % of Capture shows % of row lock waits for each top segment compared
-> with total row lock waits for all segments captured by the Snapshot

                                                                     Row
           Tablespace                      Subobject  Obj.          Lock    % of
Owner         Name    Object Name            Name     Type         Waits Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
CONTENT_OW LOB_8K_TS  MZ_ASSET_WORK_EVENT_            INDEX       72,005   43.86
CONTENT_OW LOB_8K_TS  MZ_CS_WORK_NOTE_RE_I _2013_1_36 INDEX       13,795    8.40
CONTENT_OW LOB_8K_TS  MZ_CS_WORK_INFO_PART _2013_5_35 INDEX       12,383    7.54
CONTENT_OW INDEX_TS   MZ_AM_REQUEST_IX3A              INDEX        8,937    5.44
CONTENT_OW DATA_TS    MZ_AM_REQUEST                   TABLE        8,531    5.20

Row Lock Waits 是指行鎖的等待次數(shù) 數(shù)據(jù)來源于 dba_hist_seg_stat.ROW_LOCK_WAITS_DELTA

11-14 Segments by ITL WAITS

Segments by ITL Waits              DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> % of Capture shows % of ITL waits for each top segment compared
-> with total ITL waits for all segments captured by the Snapshot

           Tablespace                      Subobject  Obj.           ITL    % of
Owner         Name    Object Name            Name     Type         Waits Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
CONTENT_OW LOB_8K_TS  MZ_ASSET_WORK_EVENT_            INDEX           95   30.16
CONTENT_OW LOB_8K_TS  MZ_CS_WORK_NOTE_RE_I _2013_1_36 INDEX           48   15.24
CONTENT_OW LOB_8K_TS  MZ_CS_WORK_INFO_PART _2013_5_35 INDEX           21    6.67
CONTENT_OW INDEX_TS   MZ_SALABLE_FIRST_AVA            INDEX           21    6.67
CONTENT_OW DATA_TS    MZ_CS_WORK_PENDING_R            TABLE           20    6.35

關(guān)于 ITL的介紹詳見： http://www.askmaclean.com/archives/enqueue-tx-row-lock-index-itl-wait-event.html

ITL Waits 等待 ITL 的次數(shù)，數(shù)據(jù)來源為 dba_hist_seg_stat.itl_waits_delta

11-14 Segments by Buffer Busy Waits

Segments by Buffer Busy Waits      DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> % of Capture shows % of Buffer Busy Waits for each top segment compared
-> with total Buffer Busy Waits for all segments captured by the Snapshot

                                                                  Buffer
           Tablespace                      Subobject  Obj.          Busy    % of
Owner         Name    Object Name            Name     Type         Waits Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
CONTENT_OW LOB_8K_TS  MZ_ASSET_WORK_EVENT_            INDEX      251,073   57.07
CONTENT_OW LOB_8K_TS  MZ_CS_WORK_NOTE_RE_I _2013_1_36 INDEX       36,186    8.23
CONTENT_OW LOB_8K_TS  MZ_CS_WORK_INFO_PART _2013_5_35 INDEX       31,786    7.23
CONTENT_OW INDEX_TS   MZ_AM_REQUEST_IX3A              INDEX       15,663    3.56
CONTENT_OW INDEX_TS   MZ_CS_WORK_PENDING_R            INDEX       11,087    2.52

Buffer Busy Waits 該數(shù)據(jù)段上發(fā)生 buffer busy wait的次數(shù) 數(shù)據(jù)來源 dba_hist_seg_stat.buffer_busy_waits_delta

11-15 Segments by Global Cache Buffer

Segments by Global Cache Buffer BusyDB/Inst: MAC/MAC2  Snaps: 70719-7072
-> % of Capture shows % of GC Buffer Busy for each top segment compared
-> with GC Buffer Busy for all segments captured by the Snapshot

                                                                      GC
           Tablespace                      Subobject  Obj.        Buffer    % of
Owner         Name    Object Name            Name     Type          Busy Capture
---------- ---------- -------------------- ---------- ----- ------------ -------
CONTENT_OW INDEX_TS   MZ_AM_REQUEST_IX3               INDEX    2,135,528   50.07
CONTENT_OW DATA_TS    MZ_CONTENT_PROVIDER_            TABLE      652,900   15.31
CONTENT_OW LOB_8K_TS  MZ_ASSET_WORK_EVENT_            INDEX      552,161   12.95
CONTENT_OW LOB_8K_TS  MZ_CS_WORK_NOTE_RE_I _2013_1_36 INDEX      113,042    2.65
CONTENT_OW LOB_8K_TS  MZ_CS_WORK_INFO_PART _2013_5_35 INDEX       98,134    2.30

GC Buffer Busy 數(shù)據(jù)段上發(fā)揮僧gc buffer busy的次數(shù)，數(shù)據(jù)源 dba_hist_seg_stat.gc_buffer_busy_delta

11-15 Segments by CR Blocks Received

Segments by CR Blocks Received    DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> Total CR Blocks Received:         763,037
-> Captured Segments account for   40.9% of Total

                                                                   CR
           Tablespace                      Subobject  Obj.       Blocks
Owner         Name    Object Name            Name     Type      Received  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CONTENT_OW DATA_TS    MZ_AM_REQUEST                   TABLE       69,100    9.06
CONTENT_OW DATA_TS    MZ_CS_WORK_PENDING_R            TABLE       44,491    5.83
CONTENT_OW INDEX_TS   MZ_AM_REQUEST_IX3A              INDEX       36,830    4.83
CONTENT_OW DATA_TS    MZ_PODCAST                      TABLE       36,632    4.80
CONTENT_OW INDEX_TS   MZ_AM_REQUEST_PK                INDEX       19,646    2.57

CR Blocks Received ：是指RAC中本地節(jié)點(diǎn)接收到global cache CR blocks 的數(shù)量；數(shù)據(jù)來源為 dba_hist_seg_stat.gc_cu_blocks_received_delta

%Total : (該段上在本節(jié)點(diǎn)接收的Global CR blocks / gc cr blocks received )

11-16 Segments by Current Blocks Received

Segments by Current Blocks ReceivedDB/Inst: MAC/MAC2  Snaps: 70719-70723
-> Total Current Blocks Received:         704,731
-> Captured Segments account for   61.8% of Total

                                                                 Current
           Tablespace                      Subobject  Obj.       Blocks
Owner         Name    Object Name            Name     Type      Received  %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
CONTENT_OW INDEX_TS   MZ_AM_REQUEST_IX3               INDEX       56,287    7.99
CONTENT_OW INDEX_TS   MZ_AM_REQUEST_IX3A              INDEX       45,139    6.41
CONTENT_OW DATA_TS    MZ_AM_REQUEST                   TABLE       40,350    5.73
CONTENT_OW DATA_TS    MZ_CS_WORK_PENDING_R            TABLE       22,808    3.24
CONTENT_OW INDEX_TS   MZ_AM_REQUEST_IX8               INDEX       13,343    1.89

Current Blocks Received :是指RAC中本地節(jié)點(diǎn)接收到global cache Current blocks 的數(shù)量，數(shù)據(jù)來源DBA_HIST_SEG_STAT.gc_cu_blocks_received_delta

%Total : (該段上在本節(jié)點(diǎn)接收的 global cache current blocks / gc current blocks received)

12 Dictionary Cache Stats

Dictionary Cache Stats            DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> "Pct Misses"  should be very low (< 2% in most cases) -> "Final Usage" is the number of cache entries being used

                                   Get    Pct    Scan   Pct      Mod      Final
Cache                         Requests   Miss    Reqs  Miss     Reqs      Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_awr_control                      87    2.3       0   N/A        6          1
dc_global_oids                   1,134    7.8       0   N/A        0         13
dc_histogram_data            6,119,027    0.9       0   N/A        0     11,784
dc_histogram_defs            1,898,714    2.3       0   N/A        0      5,462
dc_object_grants                   175   26.9       0   N/A        0          4
dc_objects                  10,254,514    0.2       0   N/A        0      3,807
dc_profiles                      8,452    0.0       0   N/A        0          2
dc_rollback_segments         3,031,044    0.0       0   N/A        0      1,947
dc_segments                  1,812,243    1.4       0   N/A       10      3,595
dc_sequences                    15,783   69.6       0   N/A   15,782         20
dc_table_scns                       70    2.9       0   N/A        0          1
dc_tablespaces               1,628,112    0.0       0   N/A        0         37
dc_users                     2,037,138    0.0       0   N/A        0         52
global database name             7,698    0.0       0   N/A        0          1
outstanding_alerts                 264   99.6       0   N/A        8          1
sch_lj_oids                         51    7.8       0   N/A        0          1

Dictionary Cache 字典緩存也叫row cache

數(shù)據(jù)來源為dba_hist_rowcache_summary

Cache 字典緩存類名kqrstcid <=> kqrsttxt cid=3(dc_rollback_segments)

Get Requests 申請(qǐng)獲取該數(shù)據(jù)字典緩存對(duì)象的次數(shù) gets

Miss : GETMISSES 申請(qǐng)獲取該數(shù)據(jù)字典緩存對(duì)象但 miss的次數(shù)

Pct Miss ：　GETMISSES /Gets ， Miss的比例，這個(gè)pct miss應(yīng)當(dāng)非常低小于2%，否則有出現(xiàn)大量row cache lock的可能

Scan Reqs：掃描申請(qǐng)的次數(shù) ，kqrssc 、kqrpScan 、kqrpsiv時(shí)發(fā)生scan 會(huì)導(dǎo)致掃描數(shù)增加 kqrstsrq++(scan requests) ，例如migrate tablespace 時(shí)調(diào)用 kttm2b函數(shù) 為了安全刪除uet$中的記錄會(huì)callback kqrpsiv (used extent cache)，實(shí)際很少見

Pct Miss：SCANMISSES/SCANS

Mod Reqs: 申請(qǐng)修改字典緩存對(duì)象的次數(shù)，從上面的數(shù)據(jù)可以看到dc_sequences的mod reqs很高，這是因?yàn)閟equence是變化較多的字典對(duì)象

Final Usage ：包含有有效數(shù)據(jù)的字典緩存記錄的總數(shù) 也就是正在被使用的row cache記錄 USAGE Number of cache entries that contain valid data

Dictionary Cache Stats (RAC)       DB/Inst: MAC/MAC2  Snaps: 70719-70723

                                   GES          GES          GES
Cache                         Requests    Conflicts     Releases
------------------------- ------------ ------------ ------------
dc_awr_control                      14            2            0
dc_global_oids                      88            0          102
dc_histogram_defs               43,518            0       43,521
dc_objects                      21,608           17       21,176
dc_profiles                          1            0            1
dc_segments                     24,974           14       24,428
dc_sequences                    25,178       10,644          347
dc_table_scns                        2            0            2
dc_tablespaces                     165            0          166
dc_users                           119            0          119
outstanding_alerts                 478            8          250
sch_lj_oids                          4            0            4

GES Request kqrstilr total instance lock requests ，通過全局隊(duì)列服務(wù)GES 來申請(qǐng)instance lock的次數(shù)

GES request 申請(qǐng)的原因可能是 dump cache object、kqrbfr LCK進(jìn)程要background free some parent objects釋放一些parent objects 等

GES Conflicts kqrstifr instance lock forced-releases ， LCK進(jìn)程以AST方式釋放鎖的次數(shù) ，僅出現(xiàn)在kqrbrl中

GES Releases kqrstisr instance lock self-releases ，LCK進(jìn)程要background free some parent objects釋放一些parent objects 時(shí)可能自增

上述數(shù)據(jù)中可以看到僅有dc_sequences 對(duì)應(yīng)的GES Conflicts較多，對(duì)于sequence 使用ordered和non-cache選項(xiàng)會(huì)導(dǎo)致RAC中的一個(gè)邊際效應(yīng)，即”row cache lock”等待源于DC_SEQUENCES ROW CACHE。 DC_SEQUENCES 上的GETS request、modifications 、GES requests和GES conflict 與引發(fā)生成一個(gè)新的 sequence number的特定SQL執(zhí)行頻率相關(guān)。

在Oracle 10g中，ORDERED Sequence還可能在高并發(fā)下造成大量DFS lock Handle 等待，由于bug 5209859

13 Library Cache Activity

Library Cache Activity             DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> "Pct Misses"  should be very low

                         Get    Pct            Pin    Pct             Invali-
Namespace           Requests   Miss       Requests   Miss    Reloads  dations
--------------- ------------ ------ -------------- ------ ---------- --------
ACCOUNT_STATUS         8,436    0.3              0    N/A          0        0
BODY                   8,697    0.7         15,537    0.7         49        0
CLUSTER                  317    4.7            321    4.7          0        0
DBLINK                 9,212    0.1              0    N/A          0        0
EDITION                4,431    0.0          8,660    0.0          0        0
HINTSET OBJECT         1,027    9.5          1,027   14.4          0        0
INDEX                    792   18.2            792   18.2          0        0
QUEUE                     10    0.0          1,733    0.0          0        0
RULESET                    0    N/A              8   87.5          7        0
SCHEMA                 8,169    0.0              0    N/A          0        0
SQL AREA             533,409    4.8 -4,246,727,944  101.1     44,864      576
SQL AREA BUILD        71,500   65.5              0    N/A          0        0
SQL AREA STATS        41,008   90.3         41,008   90.3          1        0
TABLE/PROCEDURE      320,310    0.6      1,033,991    3.6     25,378        0
TRIGGER                  847    0.0         38,442    0.3        110        0

NameSpace library cache 的命名空間

GETS Requests 該命名空間所包含對(duì)象的library cache lock被申請(qǐng)的次數(shù)

GETHITS 對(duì)象的 library cache handle 正好在內(nèi)存中被找到的次數(shù)

Pct Misses : ( 1- ( GETHITS /GETS Requests)) *100

Pin Requests 該命名空間所包含對(duì)象上pin被申請(qǐng)的次數(shù)

PINHITS 要pin的對(duì)象的heap metadata正好在shared pool中的次數(shù)

Pct Miss ( 1- ( PINHITS /Pin Requests)) *100

Reloads 指從object handle 被重建開始不是第一次PIN該對(duì)象的PIN ，且該次PIN要求對(duì)象從磁盤上讀取加載的次數(shù) ;Reloads值較高的情況建議增大shared_pool_size

INVALIDATIONS 由于以來對(duì)象被修改導(dǎo)致該命名空間所包含對(duì)象被標(biāo)記為無效的次數(shù)

Library Cache Activity (RAC)       DB/Inst: MAC/MAC2  Snaps: 70719-70723

                    GES Lock      GES Pin      GES Pin   GES Inval GES Invali-
Namespace           Requests     Requests     Releases    Requests     dations
--------------- ------------ ------------ ------------ ----------- -----------
ACCOUNT_STATUS         8,436            0            0           0           0
BODY                       0       15,497       15,497           0           0
CLUSTER                  321          321          321           0           0
DBLINK                 9,212            0            0           0           0
EDITION                4,431        4,431        4,431           0           0
HINTSET OBJECT         1,027        1,027        1,027           0           0
INDEX                    792          792          792           0           0
QUEUE                      8        1,733        1,733           0           0
RULESET                    0            8            8           0           0
SCHEMA                 4,226            0            0           0           0
TABLE/PROCEDURE      373,163      704,816      704,816           0           0
TRIGGER                    0       38,430       38,430           0           0

GES Lock Request: dlm_lock_requests Lock instance-lock ReQuests 申請(qǐng)獲得lock instance lock的次數(shù)

GES PIN request : DLM_PIN_REQUESTS Pin instance-lock ReQuests 申請(qǐng)獲得pin instance lock的次數(shù)

GES Pin Releases DLM_PIN_RELEASES release the pin instance lock 釋放pin instance lock的次數(shù)

GES Inval Requests DLM_INVALIDATION_REQUESTS get the invalidation instance lock 申請(qǐng)獲得invalidation instance lock的次數(shù)

GES Invali- dations DLM_INVALIDATIONS 接收到其他節(jié)點(diǎn)的invalidation pings次數(shù)

14 Process Memory Summary

Process Memory Summary            DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> B: Begin Snap   E: End Snap
-> All rows below contain absolute values (i.e. not diffed over the interval)
-> Max Alloc is Maximum PGA Allocation size at snapshot time
-> Hist Max Alloc is the Historical Max Allocation for still-connected processes
-> ordered by Begin/End snapshot, Alloc (MB) desc

                                                            Hist
                                    Avg  Std Dev     Max     Max
               Alloc      Used    Alloc    Alloc   Alloc   Alloc    Num    Num
  Category      (MB)      (MB)     (MB)     (MB)    (MB)    (MB)   Proc  Alloc
- -------- --------- --------- -------- -------- ------- ------- ------ ------
B Other     16,062.7       N/A      6.1     66.6   3,370   3,370  2,612  2,612
  SQL        5,412.2   4,462.9      2.2     89.5   4,483   4,483  2,508  2,498
  Freeable   2,116.4        .0       .9      6.3     298     N/A  2,266  2,266
  PL/SQL        94.0      69.8       .0       .0       1       1  2,610  2,609
E Other     15,977.3       N/A      6.1     66.9   3,387   3,387  2,616  2,616
  SQL        5,447.9   4,519.0      2.2     89.8   4,505   4,505  2,514  2,503
  Freeable   2,119.9        .0       .9      6.3     297     N/A  2,273  2,273
  PL/SQL        93.2      69.2       .0       .0       1       1  2,614  2,613

數(shù)據(jù)來源為dba_hist_process_mem_summary，這里是對(duì)PGA 使用的一個(gè)小結(jié)，幫助我們了解到底誰用掉了PGA

B: 開始快照 E: 結(jié)束快照

該環(huán)節(jié)列出 PGA中各分類的使用量

Category 分類名，包括”SQL”, “PL/SQL”, “OLAP” 和”JAVA”. 特殊分類是 “Freeable” 和”O(jiān)ther”. Free memory是指哪些 OS已經(jīng)分配給進(jìn)程，但沒有分配給任何分類的內(nèi)存。 “Other”是已經(jīng)分配給分類的內(nèi)存，但不是已命名的分類

Alloc (MB) allocated_total 該分類被分配的總內(nèi)存

Used (MB) used_total 該分類已使用的內(nèi)存

Avg Alloc (MB) allocated_avg 平均每個(gè)進(jìn)程中該分類分配的內(nèi)存量

Std Dev Alloc (MB) ：該分類分配的內(nèi)存在每個(gè)進(jìn)程之間的標(biāo)準(zhǔn)差

Max　Alloc　(MB) ALLOCATED_MAX?。涸诳煺諘r(shí)間內(nèi)單個(gè)進(jìn)程該分類最大分配過的內(nèi)存量：Max Alloc is Maximum PGA Allocation size at snapshot time

Hist Max Alloc (MB) MAX_ALLOCATED_MAX: 目前仍鏈接著的進(jìn)程該分類最大分配過的內(nèi)存量：Hist Max Alloc is the Historical Max Allocation for still-connected processes

Num Proc num_processes 進(jìn)程數(shù)目

Num Alloc NON_ZERO_ALLOCS 分配了該類型內(nèi)存的進(jìn)程數(shù)目

14 SGA信息

14 -1 SGA Memory Summary

SGA Memory Summary                 DB/Inst: MAC/MAC2  Snaps: 70719-70723

                                                      End Size (Bytes)
SGA regions                     Begin Size (Bytes)      (if different)
------------------------------ ------------------- -------------------
Database Buffers                    20,669,530,112
Fixed Size                               2,241,880
Redo Buffers                           125,669,376
Variable Size                       10,536,094,376
                               -------------------
sum                                 31,333,535,744

粗粒度的sga區(qū)域內(nèi)存使用信息， End Size僅在于begin size不同時(shí)打印

14-2 SGA breakdown difference

SGA breakdown difference           DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> ordered by Pool, Name
-> N/A value for Begin MB or End MB indicates the size of that Pool/Name was
   insignificant, or zero in that snapshot

Pool   Name                                 Begin MB         End MB  % Diff
------ ------------------------------ -------------- -------------- -------
java   free memory                              64.0           64.0    0.00
large  PX msg pool                               7.8            7.8    0.00
large  free memory                             247.8          247.8    0.00
shared Checkpoint queue                        140.6          140.6    0.00
shared FileOpenBlock                         2,459.2        2,459.2    0.00
shared KGH: NO ACCESS                        1,629.6        1,629.6    0.00
shared KGLH0                                   997.7          990.5   -0.71
shared KKSSP                                   312.2          308.9   -1.06
shared SQLA                                    376.6          370.6   -1.61
shared db_block_hash_buckets                   178.0          178.0    0.00
shared dbktb: trace buffer                     156.3          156.3    0.00
shared event statistics per sess               187.1          187.1    0.00
shared free memory                           1,208.9        1,220.6    0.97
shared gcs resources                           435.0          435.0    0.00
shared gcs shadows                             320.6          320.6    0.00
shared ges enqueues                            228.9          228.9    0.00
shared ges resource                            118.3          118.3    0.00
shared init_heap_kfsg                        1,063.6        1,068.1    0.43
shared kglsim object batch                     124.3          124.3    0.00
shared ksunfy : SSO free list                  174.7          174.7    0.00
stream free memory                             128.0          128.0    0.00
       buffer_cache                         19,712.0       19,712.0    0.00
       fixed_sga                                 2.1            2.1    0.00
       log_buffer                              119.8          119.8    0.00
          -------------------------------------------------------------

Pool 內(nèi)存池的名字

Name 內(nèi)存池中細(xì)分組件的名字例如KGLH0 存放KEL Heap 0 、SQLA存放SQL執(zhí)行計(jì)劃等

Begin MB 快照開始時(shí)該組件的內(nèi)存大小

End MB 快照結(jié)束時(shí)該組件的內(nèi)存大小

% Diff 差異百分比

特別注意由于AMM /ASMM引起的shared pool收縮一般在sga breakdown中可以提現(xiàn) 例如SQLA 、KQR等組件大幅縮小，可能導(dǎo)致一系列的解析等待 cursor: Pin S on X 、row cache lock等

此處的free memory信息也值得我們關(guān)注，一般推薦shared pool應(yīng)當(dāng)有300~400 MB 的free memory為宜

15 Streams統(tǒng)計(jì)

Streams CPU/IO Usage                      DB/Inst: ORCL/orcl1  Snaps: 556-559
-> Streams processes ordered by CPU usage
-> CPU and I/O Time in micro seconds

Session Type                    CPU Time  User I/O Time   Sys I/O Time
------------------------- -------------- -------------- --------------
QMON Coordinator                 101,698              0              0
QMON Slaves                       63,856              0              0
          -------------------------------------------------------------

Streams Capture                           DB/Inst: CATGT/catgt  Snaps: 911-912 
-> Lag Change should be small or negative (in seconds)

                         Captured Enqueued      Pct            Pct        Pct       Pct
                         Per        Per         Lag RuleEval  Enqueue     RedoWait  Pause
Capture Name   Second    Second     Change      Time           Time       Time      Time 
------------ -------- -------- -------- -------- -------- -------- -------- 
CAPTURE_CAT       650          391       93             0          23         0          71   
------------------------------------------------------------- 

Streams Apply                             DB/Inst: CATGT/catgt  Snaps: 911-912 
-> Pct DB is the percentage of all DB transactions that this apply handled 
-> WDEP is the wait for dependency 
-> WCMT is the wait for commit 
-> RBK is rollbacks -> MPS is messages per second 
-> TPM is time per message in milli-seconds 
-> Lag Change should be small or negative (in seconds)

                    Applied  Pct  Pct   Pct  Pct  Applied  Dequeue     Apply        Lag 
Apply Name           TPS   DB  WDEP WCMT RBK        MPS      TPM          TPM    Change 
------------ -------- ---- ---- ---- --- -------- -------- -------- -------- 
APPLY_CAT           0         0     0     0     0        0            0            0          0
           -------------------------------------------------------------

Capture Name : Streams捕獲進(jìn)程名

Captured Per Second ：每秒挖掘出來的message 條數(shù)

Enqueued Per Second: 每秒入隊(duì)的message條數(shù)

lag change: 指日志生成的時(shí)間到挖掘到該日志生成 message的時(shí)間延遲

Pct Enqueue Time：入隊(duì)時(shí)間的比例

Pct redoWait Time : 等待redo的時(shí)間比例

Pct Pause Time : Pause 時(shí)間的比例

Apply Name Streams 應(yīng)用Apply進(jìn)程的名字

Applied TPS : 每秒應(yīng)用的事務(wù)數(shù)

Pct DB: 所有的DB事務(wù)中 apply處理的比例

Pct WDEP: 由于等待依賴的數(shù)據(jù)而耗費(fèi)的時(shí)間比例

Pct WCMT: 由于等待commit而耗費(fèi)的時(shí)間比例

Pct RBK: 事務(wù)rollback 回滾的比例

Applied MPS: 每秒應(yīng)用的message 數(shù)

Dequeue TPM: 每毫秒出隊(duì)的message數(shù)

Lag Change:指最新message生成的時(shí)間到其被Apply收到的延遲

16 Resource Limit

Resource Limit Stats                     DB/Inst: MAC/MAC2  Snap: 70723
-> only rows with Current or Maximum Utilization > 80% of Limit are shown
-> ordered by resource name

                                  Current      Maximum     Initial
Resource Name                   Utilization  Utilization Allocation   Limit
------------------------------ ------------ ------------ ---------- ----------
ges_procs                             2,612        8,007      10003      10003
processes                             2,615        8,011      10000      10000

數(shù)據(jù)源于dba_hist_resource_limit

注意這里僅列出當(dāng)前使用或最大使用量>80% *最大限制的資源名，如果沒有列在這里則說明資源使用量安全
Current Utilization 當(dāng)前對(duì)該資源(包括Enqueue Resource、Lock和processes)的使用量

Maximum Utilization 從最近一次實(shí)例啟動(dòng)到現(xiàn)在該資源的最大使用量

Initial Allocation 初始分配值，一般等于參數(shù)文件中指定的值

Limit 實(shí)際上限值

17 init.ora Parameters

init.ora Parameters               DB/Inst: MAC/MAC2  Snaps: 70719-70723

                                                                End value
Parameter Name                Begin value                       (if different)
----------------------------- --------------------------------- --------------
_compression_compatibility    11.2.0
_kghdsidx_count               4
_ksmg_granule_size            67108864
_shared_pool_reserved_min_all 4100
archive_lag_target            900
audit_file_dest               /u01/app/oracle/admin/MAC/adum
audit_trail                   OS
cluster_database              TRUE
compatible                    11.2.0.2.0
control_files                 +DATA/MAC/control01.ctl, +RECO
db_16k_cache_size             268435456
db_block_size                 8192
db_cache_size                 19327352832
db_create_file_dest           +DATA

Parameter Name 參數(shù)名

Begin value 開始快照時(shí)的參數(shù)值

End value 結(jié)束快照時(shí)的參數(shù)值 (僅在發(fā)生變化時(shí)打印)

18 Global Messaging Statistics

Global Messaging Statistics       DB/Inst: MAC/MAC2  Snaps: 70719-70723

Statistic                                    Total   per Second    per Trans
--------------------------------- ---------------- ------------ ------------
acks for commit broadcast(actual)           53,705         14.9          0.2
acks for commit broadcast(logical          311,182         86.1          1.3
broadcast msgs on commit(actual)           317,082         87.7          1.3
broadcast msgs on commit(logical)          317,082         87.7          1.3
broadcast msgs on commit(wasted)           263,332         72.9          1.1
dynamically allocated gcs resourc                0          0.0          0.0
dynamically allocated gcs shadows                0          0.0          0.0
flow control messages received                 267          0.1          0.0
flow control messages sent                     127          0.0          0.0
gcs apply delta                                  0          0.0          0.0
gcs assume cvt                              55,541         15.4          0.2

全局通信統(tǒng)計(jì)信息，數(shù)據(jù)來源WRH$_DLM_MISC;

20 Global CR Served Stats

Global CR Served Stats            DB/Inst: MAC/MAC2  Snaps: 70719-70723

Statistic                                   Total
------------------------------ ------------------
CR Block Requests                         403,703
CURRENT Block Requests                    444,896
Data Block Requests                       403,705
Undo Block Requests                        94,336
TX Block Requests                         307,896
Current Results                           652,746
Private results                            21,057
Zero Results                              104,720
Disk Read Results                          69,418
Fail Results                                  508
Fairness Down Converts                    102,844
Fairness Clears                            15,207
Free GC Elements                                0
Flushes                                   105,052
Flushes Queued                                  0
Flush Queue Full                                0
Flush Max Time (us)                             0
Light Works                                71,793
Errors                                        117

LMS傳輸CR BLOCK的統(tǒng)計(jì)信息，數(shù)據(jù)來源WRH$_CR_BLOCK_SERVER

21 Global CURRENT Served Stats

Global CURRENT Served Stats        DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> Pins    = CURRENT Block Pin Operations
-> Flushes = Redo Flush before CURRENT Block Served Operations
-> Writes  = CURRENT Block Fusion Write Operations

Statistic         Total   % <1ms  % <10ms % <100ms    % <1s   % <10s
---------- ------------ -------- -------- -------- -------- --------
Pins             73,018    12.27    75.96     8.49     2.21     1.08
Flushes          79,336     5.98    50.17    14.45    19.45     9.95
Writes          102,189     3.14    35.23    19.34    33.26     9.03

數(shù)據(jù)來源dba_hist_current_block_server

Time to process current block request = (pin time + flush time + send time)

Pins CURRENT Block Pin Operations ， PIN的內(nèi)涵是處理一個(gè)BAST 不包含對(duì)global current block的flush和實(shí)際傳輸

The pin time represents how much time is required to process a BAST. It does not include the flush time and
the send time. The average pin time per block served should be very low because the processing consists
mainly of code path and should never be blocked.

Flush 指臟塊被LMS進(jìn)程傳輸出去之前，其相關(guān)的redo必須由LGWR已經(jīng)flush 到磁盤上

Write 指fusion write number of writes which were mediated；節(jié)點(diǎn)之間寫臟塊需求相互促成的行為 KJBL.KJBLREQWRITE gcs write request msgs 、gcs writes refused

% <1ms % <10ms % <100ms % <1s % <10s 分別對(duì)應(yīng)為pin、flush、write行為耗時(shí)的比例

例如在上例中flush和 write 在1s 到10s之間的有9%，在100ms 和1s之間的有19%和33%，因?yàn)閒lush和write都是IO操作所以這里可以預(yù)見IO存在問題，延遲較高

22 Global Cache Transfer Stats

Global Cache Transfer Stats        DB/Inst: MAC/MAC2  Snaps: 70719-70723
-> Immediate  (Immed) - Block Transfer NOT impacted by Remote Processing Delays
-> Busy        (Busy) - Block Transfer impacted by Remote Contention
-> Congested (Congst) - Block Transfer impacted by Remote System Load
-> ordered by CR + Current Blocks Received desc

                               CR                         Current
                 ----------------------------- -----------------------------
Inst Block         Blocks      %      %      %   Blocks      %      %      %
  No Class       Received  Immed   Busy Congst Received  Immed   Busy Congst
---- ----------- -------- ------ ------ ------ -------- ------ ------ ------
   1 data block   133,187   76.3   22.6    1.1  233,138   75.2   23.0    1.7
   4 data block   143,165   74.1   24.9    1.0  213,204   76.6   21.8    1.6
   3 data block   122,761   75.9   23.0    1.1  220,023   77.7   21.0    1.3
   1 undo header  104,219   95.7    3.2    1.1      941   93.4    5.8     .7
   4 undo header   95,823   95.2    3.7    1.1      809   93.4    5.3    1.2
   3 undo header   95,592   95.6    3.3    1.1      912   94.6    4.5     .9
   1 undo block    25,002   95.8    3.4     .9        0    N/A    N/A    N/A
   4 undo block    23,303   96.0    3.1     .9        0    N/A    N/A    N/A
   3 undo block    21,672   95.4    3.7     .9        0    N/A    N/A    N/A
   1 Others         1,909   92.0    6.8    1.2    6,057   89.6    8.9    1.5
   4 Others         1,736   92.4    6.1    1.5    5,841   88.8    9.9    1.3
   3 Others         1,500   92.4    5.9    1.7    4,405   87.7   10.8    1.6

數(shù)據(jù)來源DBA_HIST_INST_CACHE_TRANSFER

Inst No 節(jié)點(diǎn)號(hào)

Block Class 塊的類型

CR Blocks Received 該節(jié)點(diǎn)上該類型CR 塊的接收數(shù)量

CR Immed %: CR塊請(qǐng)求立即接收到的比例

CR Busy%：CR塊請(qǐng)求由于遠(yuǎn)端爭(zhēng)用而沒有立即接收到的比例

CR Congst%: CR塊請(qǐng)求由于遠(yuǎn)端負(fù)載高而沒有立即接收到的比例

Current Blocks Received 該節(jié)點(diǎn)上該類型Current 塊的接收數(shù)量

Current Immed %: Current塊請(qǐng)求立即接收到的比例

Current Busy%：Current塊請(qǐng)求由于遠(yuǎn)端爭(zhēng)用而沒有立即接收到的比例

Current Congst%: Current塊請(qǐng)求由于遠(yuǎn)端負(fù)載高而沒有立即接收到的比例

Congst%的比例應(yīng)當(dāng)非常低不高于2%， Busy%很大程度受到IO的影響，如果超過10% 一般會(huì)有嚴(yán)重的gc buffer busy acquire/release

參考文檔

Statistics Descriptions http://docs.oracle.com/cd/B19306_01/server.102/b14237/stats002.htm

Memory Configuration and Use http://docs.oracle.com/cd/B19306_01/server.102/b14211/memory.htm

Library Cache Hit (%) http://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_instance_efficiency_libcache_hit_pct.html

Oracle? Database Performance Tuning Guide 12c Release 1 (12.1)

How to Interpret the “SQL ordered by Physical Reads (UnOptimized)” Section in AWR Reports (11.2 onwards) [ID 1466035.1]

FILED UNDER: ORACLE, ORACLE SQL性能調(diào)優(yōu)