192.100.100.90 資料庫效能綜合分析報告

詳細解讀 100.90 STATSPACK 報告

1 、報表頭資訊---------------------------------------------------------------2

2 、例項負載檔資訊-----------------------------------------------------------2

3 、例項有效性資訊-----------------------------------------------------------3

4 、TOP 5及其他等待事件資訊-------------------------------------------------5

5 、SQL統計資訊-------------------------------------------------------------7

5.1 SQL 統計資訊－邏輯讀-------------------------------------------------7

5.2 SQL 統計資訊－物理讀------------------------------------------------13

5.3 SQL 統計資訊－執行次數----------------------------------------------14

5.4 SQL 統計資訊－呼叫、解析次數----------------------------------------17

5.5 SQL 統計資訊－共享記憶體佔用------------------------------------------21

5.6 SQL 統計資訊－多版本快取--------------------------------------------21

6 、例項的活動資訊----------------------------------------------------------21

7 、I/O統計資訊------------------------------------------------------------25

8 、Buffer Pool統計資訊----------------------------------------------------29

9 、例項的恢復情況統計資訊--------------------------------------------------29

10 、Buffer Pool調整的Advisory---------------------------------------------30

11 、Buffer Pool等待情況統計------------------------------------------------30

12 、PGA統計資訊 -----------------------------------------------------------30

13 、PGA調整的Advisory-----------------------------------------------------30

14 、佇列的統計資訊---------------------------------------------------------30

15 、回滾段統計資訊---------------------------------------------------------31

16 、閂鎖統計資訊-----------------------------------------------------------32

17 、共享池統計資訊---------------------------------------------------------34

18 、SGA記憶體分配 -----------------------------------------------------------36

19 、資源限制統計資訊-------------------------------------------------------38

20 、初始化統計資訊---------------------------------------------------------38

1 、報表頭資訊

總結：資料庫名 ORCL,例項名 orcl,資料庫版本號 9.0.1.0.0，是否叢集 NO，主機名 server1,報告產生時間20分鐘，併發數53，buffer cache大小92M，Shared pool size大小128M；

STATSPACK report for

DB Name DB Id Instance Inst Num Release Cluster Host

------------ ----------- ------------ -------- ----------- ------- ------------

ORCL 1278871612 orcl 1 9.0.1.0.0 NO server1

Snap Id Snap Time Sessions Curs/Sess Comment

------- ------------------ -------- --------- -------------------

Begin Snap: 11 05-Sep-14 09:52:32 54 6.3

End Snap: 12 05-Sep-14 10:12:38 53 6.0

Elapsed: 20.10 (mins)

Cache Sizes (end)

~~~~~~~~~~~~~~~~~

Buffer Cache: 92M Std Block Size: 4K

Shared Pool Size: 128M Log Buffer: 400K

2 、例項負載檔資訊

總結：1 redo size:（標誌資料變更頻率, 資料庫任務的繁重與否）每秒產生日誌5.9K，每秒產生11個事物（反映資料庫任務繁重與否）；

2 Logical reads:平均每秒產生邏輯讀8099個block； Logical Reads= Consistent Gets + DB Block Gets

3 block changes:每秒block變化30個

4 physical reads:平均每秒從磁碟讀取 1268個block，數值過大， 效能極差，應該增大buffer cache的值，提高效能

5 physical writes:平均每秒資料庫寫磁碟166個block;

6 user calls:每秒使用者呼叫42次

7 parses:每秒解析23次

8 hard parses:每秒產生硬解析0.41次

9 sorts:每秒產生0.90次排序

10 logons:每秒登入次數為0

11 executes:每秒執行28.32次

12 transactions:每秒產生0.54次事務

13 % Blocks changed per Read ：在每一次邏輯讀中更改的塊的百分比為0.37。

14 Rollback per transaction %:看回滾率是不是很高，因為回滾很耗資源 ,如果回滾率過高,可能說明你的資料庫經歷了太多的無效操作 ,過多的回滾可能還會帶來Undo Block的競爭該引數計算公式如下: Round(User rollbacks / (user commits + user rollbacks) ,4)* 100% 。

15 Recursive Call %:遞迴呼叫的百分比，如果有很多PL/SQL，那麼這個值就會比較高。

16 Rows per Sort:平均每次排序操作的行數為1924。

Load Profile

~~~~~~~~~~~~ Per Second Per Transaction

--------------- ---------------

Redo size: 6,057.96 11,291.96

Logical reads: 8,099.27 15,096.93

Block changes: 30.10 56.10

Physical reads: 1,268.93 2,365.26

Physical writes: 166.22 309.83

User calls: 42.33 78.90

Parses: 23.20 43.25

Hard parses: 0.41 0.77

Sorts: 0.90 1.67

Logons: 0.00 0.00

Executes: 28.32 52.79

Transactions: 0.54

% Blocks changed per Read: 0.37 Recursive Call %: 68.59

Rollback per transaction %: 7.57 Rows per Sort: 1924.14

3 、例項有效性資訊

總結：

/* 例項的有效性，這部分值越接近100越好，分項內容詳細說明如下：

1)Buffer Nowait % ：在緩衝區中獲取Buffer的未等待比率 99.99。Buffer Nowait的這個值一般需要大於99%。否則可能存在爭用，可以在後面的等待事件中進一步確認。

2)Redo NoWait % ：在Redo緩衝區獲取Buffer空間的未等待比率100。當redo buffer達到1M時，就需要寫到redo log檔案，所以一般當redo buffer設定超過1M，不太可能存在等待buffer空間分配的情況。當前，一般設定為2M的redo buffer，對於記憶體總量來說，應該不是一個太大的值。

3)Buffer Hit % ：資料塊在資料緩衝區中的命中率 84.66，通常應在 95%以上。否則，小於95%，需要調整重要的引數，小於90%可能是要加db_cache_size。一個高的命中率，不一定代表這個系統的效能是最優的，比如大量的非選擇性的索引被頻繁訪問，就會造成命中率很高的假相（大量的db file sequential read），但是一個比較低的命中率，一般就會對這個系統的效能產生影響，需要調整。命中率的突變，往往是一個不好的資訊。如果命中率突然增大，可以檢查top buffer get SQL，檢視導致大量邏輯讀的語句和索引，如果命中率突然減小，可以檢查top physical reads SQL，檢查產生大量物理讀的語句，主要是那些沒有使用索引或者索引被刪除的。

4)In-memory Sort % ：在記憶體中的排序率 97.23。如果低於 95%，可以透過適當調大初始化引數PGA_AGGREGATE_TARGET或者SORT_AREA_SIZE來解決，注意這兩個引數設定作用的範圍時不同的，SORT_AREA_SIZE是針對每個session設定的，PGA_AGGREGATE_TARGET則時針對所有的sesion的。

5)Library Hit % ：STATEMENT在共享區的命中率 99.36，通常應該保持在95%以上，否則需要要考慮：加大共享池；使用繫結變數；修改cursor_sharing等引數。

6)Soft Parse % ：sql在共享區的命中率98.23，小於<95%,需要考慮繫結，如果低於80%，那麼就可以認為sql基本沒有被重用。

7)Execute to Parse % ：一個語句執行和分析了多少次的度量18.07。計算公式為：Execute to Parse =100 * (1 - Parses/Executions)。本例中，差不多每execution 5次需要一次parse。所以如果系統Parses > Executions，就可能出現該比率小於0的情況。該值<0通常說明shared pool設定或者語句效率存在問題，造成反覆解析，reparse可能較嚴重,或者是可能同snapshot有關，通常說明資料庫效能存在問題。

8)Latch Hit % ：100，要確保>99%，否則存在嚴重的效能問題。當該值出現問題的時候，我們可以藉助後面的等待時間和latch分析來查詢解決問題。

9)Parse CPU to Parse Elapsd % ：計算公式為：Parse CPU to Parse Elapsd %= 100*(parse time cpu / parse time elapsed)。即：解析實際執行時間/(解析實際執行時間+解析中等待資源時間)。如果該比率為100%，意味著CPU等待時間為0，沒有任何等待。

10)% Non-Parse CPU ：計算公式為：% Non-Parse CPU =round(100*1-PARSE_CPU/TOT_CPU),2)。如果這個值比較小，表示解析消耗的CPU時間過多。與PARSE_CPU相比，如果TOT_CPU很高，這個比值將接近100%，這是很好的，說明計算機執行的大部分工作是執行查詢的工作，而不是分析查詢的工作。

11)Memory Usage % ：正在使用的共享池的百分, 93.91。這個數字應該長時間穩定在75%～90%。如果這個百分比太低，表明共享池設定過大，帶來額外的管理上的負擔，從而在某些條件下會導致效能的下降。如果這個百分率太高，會使共享池外部的元件老化，如果SQL語句被再次執行，這將使得SQL語句被硬解析。在一個大小合適的系統中，共享池的使用率將處於 75%到略低於90%的範圍內。

12)% SQL with executions>1 ：這是在共享池中有多少個執行次數大於一次的SQL語句的度量。在一個趨向於迴圈執行的系統中，必須認真考慮這個數字。在這個迴圈系統中，在一天中相對於另一部分時間的部分時間裡執行了一組不同的SQL語句。在共享池中，在觀察期間將有一組未被執行過的SQL語句，這僅僅是因為要執行它們的語句在觀察期間沒有執行。只有系統連續執行相同的SQL語句組，這個數字才會接近100%。這裡顯示，在這個共享池中幾乎有80%的SQL語句在14分鐘的觀察視窗中執行次數多於一次。剩下的20%的語句可能已經在那裡了--系統只是沒有去執行。

13)% Memory for SQL w/exec>1 ：這是與不頻繁使用的SQL語句相比，頻繁使用的SQL語句消耗記憶體多少的一個度量。這個數字將在總體上與% SQL with executions>1非常接近，除非有某些查詢任務消耗的記憶體沒有規律。在穩定狀態下，總體上會看見隨著時間的推移大約有75%～85%的共享池被使用。如果Statspack報表的時間視窗足夠大到覆蓋所有的週期，執行次數大於一次的SQL語句的百分率應該接近於100%。這是一個受觀察之間持續時間影響的統計數字。可以期望它隨觀察之間的時間長度增大而增大。

小結：透過ORACLE的例項有效性統計資料，我們可以獲得大概的一個整體印象，然而我們並不能由此來確定資料執行的效能。當前效能問題的確定，我們主要還是依靠下面的等待事件來確認。我們可以這樣理解兩部分的內容，hit統計幫助我們發現和預測一些系統將要產生的效能問題，由此我們可以做到未雨綢繆。而wait事件，就是表明當前資料庫已經出現了效能問題需要解決，所以是亡羊補牢的性質。

接下來，開始檢視wait事件。

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Buffer Nowait %: 99.99 Redo NoWait %: 100.00

Buffer Hit %: 84.66 In-memory Sort %: 97.23

Library Hit %: 99.36 Soft Parse %: 98.23

Execute to Parse %: 18.07 Latch Hit %: 100.00

Parse CPU to Parse Elapsd %: % Non-Parse CPU:

Shared Pool Statistics Begin End

------ ------

Memory Usage %: 93.91 93.39

% SQL with executions>1: 83.82 85.67

% Memory for SQL w/exec>1: 79.22 80.51

4 、TOP 5及其他等待事件資訊

總結：

1 db file sequential read 該事件說明在單個資料塊上大量等待，該值過高通常是由於表間連線順序很糟糕（沒有正確選擇驅動行源），或者使用了非選擇性索引。透過將這種等待與statspack報表中已知其它問題聯絡起來(如效率不高的sql),透過檢查確保索引掃描是必須的，並確保多表連線的連線順序來調整。

2 db file scattered read Top 5 Wait Events該事件通常與全表掃描或者fast full index scan有關。因為全表掃描是被放入記憶體中進行的進行的，通常情況下基於效能的考慮，有時候也可能是分配不到足夠長的連續記憶體空間，所以會將資料塊分散(scattered)讀入Buffer Cache中。該等待過大可能是 缺少索引或者 沒有合適的索引(可以調整optimizer_index_cost_adj) 。這種情況也可能是正常的，因為執行全表掃描可能比索引掃描效率更高。當系統存在這些等待時，需要透過檢查來確定全表掃描是否必需的來調整。因為全表掃描被置於LRU(Least Recently Used，最近最少適用)列表的冷端(cold end)，對於頻繁訪問的較小的資料表，可以選擇把他們Cache 到記憶體中，以避免反覆讀取。當這個等待事件比較顯著時，可以結合v$session_longops 動態效能檢視來進行診斷，該檢視中記錄了長時間(執行時間超過6 秒的)執行的事物，可能很多是全表掃描操作(不管怎樣，這部分資訊都是值得我們注意的)。

3 log file parallel write 從log buffer 寫redo 記錄到redo log 檔案，主要指常規寫操作(相對於log file sync)。如果你的Log group 存在多個組成員，當flush log buffer 時，寫操作是並行的，這時候此等待事件可能出現。儘管這個寫操作並行處理，直到所有I/O 操作完成該寫操作才會完成(如果你的磁碟支援非同步IO或者使用IO SLAVE，那麼即使只有一個redo log file member,也有可能出現此等待)。這個引數和log file sync 時間相比較可以用來衡量log file 的寫入成本。通常稱為同步成本率。改善這個等待的方法是 將redo logs放到I/O快的盤中，儘量不使用raid5，確保表空間不是處在熱備模式下，確保redo log和data的資料檔案位於不同的磁碟中。

4 log file sync 當一個使用者提交或回滾資料時，LGWR將會話的redo記錄從日誌緩衝區填充到日誌檔案中，使用者的程式必須等待這個填充工作完成。在每次提交時都出現，如果這個等待事件影響到資料庫效能，那麼就需要修改應用程式的提交頻率, 為減少這個等待事件，須一次提交更多記錄，或者將重做日誌REDO LOG 檔案訪在不同的物理磁碟上，提高I/O的效能。

5 control file parallel write 當server 程式更新所有控制檔案時，這個事件可能出現。如果等待很短，可以不用考慮。 如果等待時間較長，檢查存放控制檔案的物理磁碟 I/O 是否存在瓶頸。多個控制檔案是完全相同的複製，用於映象以提高安全性。對於業務系統，多個控制檔案應該存放在不同的磁碟上，一般來說三個是足夠的，如果只有兩個物理硬碟，那麼兩個控制檔案也是可以接受的。在同一個磁碟上儲存多個控制檔案是不具備實際意義的。減少這個等待，可以考慮如下方法:①減少控制檔案的個數(在確保安全的前提下)。②如果系統支援，使用非同步IO。③轉移控制檔案到IO 負擔輕的物理磁碟。

~~~~~~~~~~~~~~~~~ Wait % Total

Event Waits Time (s) Wt Time

-------------------------------------------- ------------ ----------- -------

db file sequential read 17,193 9 37.92

db file scattered read 198,888 5 19.68

log file parallel write 2,296 4 17.60

log file sync 1,428 2 10.47

control file parallel write 392 2 6.54

-------------------------------------------------------------

^LWait Events for DB: ORCL Instance: orcl Snaps: 11 -12

-> s - second

-> cs - centisecond - 100th of a second

-> ms - millisecond - 1000th of a second

-> us - microsecond - 1000000th of a second

-> ordered by wait time desc, waits desc (idle events last)

Avg

Total Wait wait Waits

Event Waits Timeouts Time (s) (ms) /txn

---------------------------- ------------ ---------- ---------- ------ --------

db file sequential read 17,193 0 9 1 26.6

db file scattered read 198,888 0 5 0 307.4

log file parallel write 2,296 0 4 2 3.5

log file sync 1,428 0 2 2 2.2

control file parallel write 392 0 2 4 0.6

direct path write 187,139 0 1 0 289.2

buffer busy waits 510 0 0 1 0.8

async disk IO 208,382 0 0 0 322.1

db file parallel read 2 0 0 110 0.0

latch free 11 8 0 8 0.0

log buffer space 5 0 0 9 0.0

direct path read 20,386 0 0 0 31.5

SQL*Net more data to client 984 0 0 0 1.5

control file sequential read 176 0 0 0 0.3

db file parallel write 358 358 0 0 0.6

SQL*Net break/reset to clien 4 0 0 0 0.0

LGWR wait for redo copy 2 0 0 0 0.0

row cache lock 1 0 0 0 0.0

SQL*Net message from client 50,961 0 46,227 907 78.8

SQL*Net message to client 50,959 0 0 0 78.8

SQL*Net more data from clien 558 0 0 0 0.9

-------------------------------------------------------------

^LBackground Wait Events for DB: ORCL Instance: orcl Snaps: 11 -12

-> ordered by wait time desc, waits desc (idle events last)

Avg

Total Wait wait Waits

Event Waits Timeouts Time (s) (ms) /txn

---------------------------- ------------ ---------- ---------- ------ --------

log file parallel write 2,296 0 4 2 3.5

control file parallel write 392 0 2 4 0.6

db file scattered read 252 0 0 1 0.4

db file sequential read 25 0 0 0 0.0

control file sequential read 117 0 0 0 0.2

db file parallel write 358 358 0 0 0.6

LGWR wait for redo copy 2 0 0 0 0.0

rdbms ipc message 4,713 1,167 4,686 994 7.3

pmon timer 402 402 1,178 2929 0.6

smon timer 4 4 1,081 ###### 0.0

-------------------------------------------------------------

5 、SQL統計資訊

5.1 SQL 統計資訊－邏輯讀

總結：

這一部分，透過Buffer Gets對SQL語句進行排序，即透過它執行了多少個邏輯I/O來排序。頂端的註釋表明一個PL/SQL單元的快取獲得(Buffer Gets)包括被這個程式碼塊執行的所有SQL語句的Buffer Gets。因此將經常在這個列表的頂端看到PL/SQL過程，因為儲存過程執行的單獨的語句的數目被總計出來。

在這裡的 Buffer Gets是一個累積值，所以這個值大並不一定意味著這條語句的效能存在問題。通常我們可以透過對比該條語句的 Buffer Gets和 physical reads值，如果這兩個比較接近，肯定這條語句是存在問題的，我們可以透過 執行計劃來分析，為什麼physical reads的值如此之高。另外，我們在這裡也可以關注 gets per exec的值，這個值如果太大，表明這條語句可能使用了一個比較 差的索引或者使用了 不當的表連線。

另外說明一點：大量的 邏輯讀往往伴隨著較高的 CPU消耗。所以很多時候我們看到的系統CPU將近100％的時候，很多時候就是SQL語句造成的，這時候我們可以分析一下這裡 邏輯讀大的SQL。

^LSQL ordered by Gets for DB: ORCL Instance: orcl Snaps: 11 -12

-> End Buffer Gets Threshold: 10000

-> Note that resources reported for PL/SQL includes the resources used by

all SQL statements called within the PL/SQL code. As individual SQL

statements are also reported, it is possible and valid for the summed

total % to exceed 100

CPU Elapsd

Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value

--------------- ------------ -------------- ------ -------- --------- ----------

907,935 3 302,645.0 9.3 0.00 0.00 1464371672

select sum(-s_txn_id.cr_flag*txn_amt) in_amt_sum from hist_trans

_m left join s_txn_id on hist_trans_m.txn_id=s_txn_id.txn_id lef

t join mch_info on mch_info.mid=hist_trans_m.mid where s_txn_id.

cr_flag=-1 and hist_trans_m.tran_status=:1 AND hist_trans_m.set

tle_date>=:2 AND hist_trans_m.settle_date<=:3 AND mch_info.mch

768,270 3 256,090.0 7.9 0.00 0.00 3312443568

select sum(s_txn_id.cr_flag*txn_amt) out_amt_sum from hist_trans

_m left join s_txn_id on hist_trans_m.txn_id=s_txn_id.txn_id lef

t join mch_info on mch_info.mid=hist_trans_m.mid where s_txn_id.

cr_flag=1 and hist_trans_m.tran_status=:1 AND hist_trans_m.sett

le_date>=:2 AND hist_trans_m.settle_date<=:3 AND mch_info.mch_

497,421 172 2,892.0 5.1 0.00 0.00 3766522002

select count(*) into :b0 from csr where ((teller=:b1 and org_c

ode=:b2) and tran_status=:b3)

345,823 28 12,350.8 3.5 0.00 0.00 2566304132

select count(*) from (select stat_mch.pay_date as pay_date,to_ch

ar( min(settle_date) ) || '-' || to_char( max(settle_date) ) as

period, u_issu_inst.issu_id,u_issu_inst.inst_name as inst_name,s

tat_mch.mid,mch_info.cname as cname , sum(cnt) as cnt,sum(txn_am

t) as txn_amt,sum(fee_amt) as fee_amt,sum(sett_amt) as sett_amt

345,823 28 12,350.8 3.5 0.00 0.00 4258184794

select * from (select a.*,rownum row_num from (select stat_mch.p

ay_date as pay_date,to_char( min(settle_date) ) || '-' || to_cha

r( max(settle_date) ) as period, u_issu_inst.issu_id,u_issu_inst

.inst_name as inst_name,stat_mch.mid,mch_info.cname as cname , s

um(cnt) as cnt,sum(txn_amt) as txn_amt,sum(fee_amt) as fee_amt,s

345,799 28 12,350.0 3.5 0.00 0.00 544447472

select sum(cnt) cnt_sum,sum(txn_amt) txn_amt_sum,sum(fee_amt) fe

e_amt_sum,sum(sett_amt) sett_amt_sum from stat_mch left join u

_issu_inst on u_issu_inst.issu_id=stat_mch.issu_id left join mch

_info on stat_mch.mid=mch_info.mid where mch_info.cname LIKE :1

AND stat_mch.pay_date IS NULL

208,938 9 23,215.3 2.1 0.00 0.00 4236780024

select * from (select a.*,rownum row_num from (select csr.acq_id

as acq_id,csr.audit_org as audit_org,csr.auditor as auditor,csr

.author as author,csr.card_amt as card_amt,card_client.add_date

as t01__add_date,card_client.auditor as t01__auditor,card_client

.cert_no as t01__cert_no,card_client.cli_addr as t01__cli_addr,c

141,760 4 35,440.0 1.5 0.00 0.00 2086847580

select count(*) from ((((((hist_trans_m left JOIN s_txn_id ON s_

txn_id.txn_id=hist_trans_m.txn_id) left JOIN u_issu_inst ON u_is

su_inst.issu_id=hist_trans_m.issu_id) left JOIN u_acq_inst ON u_

^LSQL ordered by Gets for DB: ORCL Instance: orcl Snaps: 11 -12

-> End Buffer Gets Threshold: 10000

-> Note that resources reported for PL/SQL includes the resources used by

all SQL statements called within the PL/SQL code. As individual SQL

statements are also reported, it is possible and valid for the summed

total % to exceed 100

CPU Elapsd

Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value

--------------- ------------ -------------- ------ -------- --------- ----------

acq_inst.acq_id=hist_trans_m.acq_id) left JOIN s_card_type ON s_

card_type.card_type=hist_trans_m.card_type) left JOIN s_sub_type

73,122 30 2,437.4 0.7 0.00 0.00 2048733752

BEGIN :1 := p_preopen_check(:2,:3,:4,:5,:6,:7); END;

55,822 36 1,550.6 0.6 0.00 0.00 2601970352

select * from mch_info ORDER BY mch_info.mid asc

54,774 9 6,086.0 0.6 0.00 0.00 870942463

select count(*) from (((csr left JOIN u_issu_inst ON u_issu_inst

.issu_id=csr.issu_id) left JOIN u_acq_inst ON u_acq_inst.acq_id=

csr.acq_id) left JOIN card_client ON card_client.cust_no=csr.cus

t_no) left JOIN s_vouch_type ON s_vouch_type.vouch_type=csr.vouc

h_type where csr.tran_status IN(:1,:2) AND csr.cleared<>:3 AND

54,774 9 6,086.0 0.6 0.00 0.00 3269985298

select sum(card_num) as cnt,sum(payed_amt) as amt_total,sum(fq_a

mt) as fee_amt_total from (((csr left JOIN u_issu_inst ON u_issu

_inst.issu_id=csr.issu_id) left JOIN u_acq_inst ON u_acq_inst.ac

q_id=csr.acq_id) left JOIN card_client ON card_client.cust_no=cs

r.cust_no) left JOIN s_vouch_type ON s_vouch_type.vouch_type=csr

43,382 1 43,382.0 0.4 0.00 0.00 3376831664

BEGIN statspack.snap; END;

37,874 30 1,262.5 0.4 0.00 0.00 2148194035

SELECT count(*) from mk_card where pan >= :b3 and pan <= :b2

and issu_id = :b1 and mkcrd_flag = 4

37,176 6 6,196.0 0.4 0.00 0.00 1604959415

select count(*) from (mch_info left JOIN auth_area ON auth_area.

area_code=mch_info.area_code) left JOIN mcc_code ON mcc_code.mcc

=mch_info.mcc

37,082 1 37,082.0 0.4 0.00 0.00 1474678425

INSERT into stats$sqltext ( hash_value

, text_subset , piece , sql_text

, address , command_type

, last_snap_id ) select st1.hash_value

, ss.text_subset , st1.piece

34,937 28 1,247.8 0.4 0.00 0.00 4005133339

SELECT min(pan) from mk_card where pan >= :b4 and pan <= :b3

and issu_id = :b2 and mkcrd_flag = 4 and recv_dept <> :b1

25,152 8 3,144.0 0.3 0.00 0.00 922806158

select count(*) from ((((((hist_trans_m left JOIN s_txn_id ON s_

txn_id.txn_id=hist_trans_m.txn_id) left JOIN u_issu_inst ON u_is

^LSQL ordered by Gets for DB: ORCL Instance: orcl Snaps: 11 -12

-> End Buffer Gets Threshold: 10000

-> Note that resources reported for PL/SQL includes the resources used by

all SQL statements called within the PL/SQL code. As individual SQL

statements are also reported, it is possible and valid for the summed

total % to exceed 100

CPU Elapsd

Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value

--------------- ------------ -------------- ------ -------- --------- ----------

-------------------------------------------------------------

^LSQL ordered by Reads for DB: ORCL Instance: orcl Snaps: 11 -12

-> End Disk Reads Threshold: 1000

CPU Elapsd

Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value

--------------- ------------ -------------- ------ -------- --------- ----------

312,343 28 11,155.1 20.4 0.00 0.00 2566304132

select count(*) from (select stat_mch.pay_date as pay_date,to_ch

ar( min(settle_date) ) || '-' || to_char( max(settle_date) ) as

period, u_issu_inst.issu_id,u_issu_inst.inst_name as inst_name,s

tat_mch.mid,mch_info.cname as cname , sum(cnt) as cnt,sum(txn_am

t) as txn_amt,sum(fee_amt) as fee_amt,sum(sett_amt) as sett_amt

310,650 28 11,094.6 20.3 0.00 0.00 4258184794

select * from (select a.*,rownum row_num from (select stat_mch.p

ay_date as pay_date,to_char( min(settle_date) ) || '-' || to_cha

r( max(settle_date) ) as period, u_issu_inst.issu_id,u_issu_inst

.inst_name as inst_name,stat_mch.mid,mch_info.cname as cname , s

um(cnt) as cnt,sum(txn_amt) as txn_amt,sum(fee_amt) as fee_amt,s

309,632 28 11,058.3 20.2 0.00 0.00 544447472

select sum(cnt) cnt_sum,sum(txn_amt) txn_amt_sum,sum(fee_amt) fe

e_amt_sum,sum(sett_amt) sett_amt_sum from stat_mch left join u

_issu_inst on u_issu_inst.issu_id=stat_mch.issu_id left join mch

_info on stat_mch.mid=mch_info.mid where mch_info.cname LIKE :1

AND stat_mch.pay_date IS NULL

4,281 8 535.1 0.3 0.00 0.00 922806158

select count(*) from ((((((hist_trans_m left JOIN s_txn_id ON s_

txn_id.txn_id=hist_trans_m.txn_id) left JOIN u_issu_inst ON u_is

su_inst.issu_id=hist_trans_m.issu_id) left JOIN u_acq_inst ON u_

acq_inst.acq_id=hist_trans_m.acq_id) left JOIN s_card_type ON s_

card_type.card_type=hist_trans_m.card_type) left JOIN s_sub_type

1,946 6 324.3 0.1 0.00 0.00 2116494957

select * from (select a.*,rownum row_num from (select mch_info.a

cc_name as acc_name,mch_info.area_code as area_code,auth_area.ar

ea_code as t01__area_code,auth_area.area_desc as t01__area_desc,

auth_area.area_name as t01__area_name,auth_area.parent_code as t

01__parent_code,mch_info.br_cname as br_cname,mch_info.bus_type

1,394 4 348.5 0.1 0.00 0.00 128694328

select * from (select a.*,rownum row_num from (select hist_trans

_m.acc_type as acc_type,hist_trans_m.account as account,hist_tra

ns_m.acq_amt as acq_amt,hist_trans_m.acq_dscnt as acq_dscnt,hist

_trans_m.acq_id as acq_id,hist_trans_m.auth_no as auth_no,hist_t

rans_m.author as author,hist_trans_m.bal as bal,hist_trans_m.bal

1,362 1 1,362.0 0.1 0.00 0.00 3376831664

BEGIN statspack.snap; END;

1,162 4 290.5 0.1 0.00 0.00 2052613816

select sum(-s_txn_id.cr_flag*txn_amt) in_amt_sum from hist_trans

_m left join s_txn_id on hist_trans_m.txn_id=s_txn_id.txn_id lef

t join mch_info on mch_info.mid=hist_trans_m.mid where s_txn_id.

cr_flag=-1 and hist_trans_m.tran_status=:1 AND hist_trans_m.mid

=:2 AND hist_trans_m.settle_date>=:3 AND hist_trans_m.settle_d

1,162 4 290.5 0.1 0.00 0.00 2631385599

select sum(s_txn_id.cr_flag*txn_amt) out_amt_sum from hist_trans

5.2 SQL 統計資訊－物理讀

總結：

這部分 透過物理讀對SQL語句進行排序。這顯示引起大部分對這個系統進行讀取活動的SQL，即物理I/O。當我們的系統如果存在 I/O瓶頸時，需要關注這裡 I/O操作比較多的語句。

^LSQL ordered by Reads for DB: ORCL Instance: orcl Snaps: 11 -12

-> End Disk Reads Threshold: 1000

CPU Elapsd

Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value

--------------- ------------ -------------- ------ -------- --------- ----------

_m left join s_txn_id on hist_trans_m.txn_id=s_txn_id.txn_id lef

t join mch_info on mch_info.mid=hist_trans_m.mid where s_txn_id.

cr_flag=1 and hist_trans_m.tran_status=:1 AND hist_trans_m.mid=

:2 AND hist_trans_m.settle_date>=:3 AND hist_trans_m.settle_da

1,162 4 290.5 0.1 0.00 0.00 3569792259

select sum(-s_txn_id.cr_flag*txn_amt) amt_sum from hist_trans_m

left join s_txn_id on hist_trans_m.txn_id=s_txn_id.txn_id left j

oin mch_info on mch_info.mid=hist_trans_m.mid where hist_trans_m

.tran_status=:1 AND hist_trans_m.mid=:2 AND hist_trans_m.settl

e_date>=:3 AND hist_trans_m.settle_date<=:4 AND hist_trans_m.t

1,162 4 290.5 0.1 0.00 0.00 3775988450

select count(*) cnt,sum(txn_amt) amt_total,sum(txn_fee) fee_amt_

total,sum(txn_fee+txn_amt) txn_amt_total from hist_trans_m left

join mch_info on mch_info.mid=hist_trans_m.mid where hist_trans

_m.tran_status=:1 AND hist_trans_m.mid=:2 AND hist_trans_m.set

tle_date>=:3 AND hist_trans_m.settle_date<=:4

788 24 32.8 0.1 0.00 0.00 1520624711

select count(*) from task_csr where task_csr.appl_card_no=:1 AN

D task_csr.appl_status=:2 AND (task_csr.appl_type=:3 OR task_c

sr.appl_type=:4 )

747 1 747.0 0.0 0.00 0.00 1212505517

select i.obj#, i.flags, u.name, o.name from sys.obj$ o, sys.

user$ u, sys.ind$ i where (bitand(i.flags, 256) = 256 or bit

and(i.flags, 512) = 512) and (not((i.type# = 9) and bi

tand(i.flags,8) = 8)) and o.obj#=i.obj# and o.owner# =

u.user#

632 9 70.2 0.0 0.00 0.00 4236780024

select * from (select a.*,rownum row_num from (select csr.acq_id

as acq_id,csr.audit_org as audit_org,csr.auditor as auditor,csr

.author as author,csr.card_amt as card_amt,card_client.add_date

as t01__add_date,card_client.auditor as t01__auditor,card_client

.cert_no as t01__cert_no,card_client.cli_addr as t01__cli_addr,c

-------------------------------------------------------------

5.3 SQL 統計資訊－執行次數

總結：

這部分告訴我們在這段時間中 執行次數最多的SQL語句。為了隔離某些頻繁執行的查詢，以觀察是否有某些更改邏輯的方法以避免必須如此頻繁的執行這些查詢，這可能是很有用的。

或許一個查詢正在一個迴圈的內部執行，而且它可能在迴圈的外部執行一次，可以設計簡單的演算法更改以減少必須執行這個查詢的次數。即使它執行的飛快，任何被執行幾百萬次的

操作都將開始耗盡大量的時間。

^LSQL ordered by Executions for DB: ORCL Instance: orcl Snaps: 11 -12

-> End Executions Threshold: 100

CPU per Elap per

Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value

------------ --------------- ---------------- ----------- ---------- ----------

2,385 1,975 0.8 0.00 0.00 94587681

select arg_value into :b0 from sys_arg where arg_name=:b1

1,350 0 0.0 0.00 0.00 2913840444

select length from fet$ where file#=:1 and block#=:2 and ts#=:3

1,156 1,156 1.0 0.00 0.00 438662453

select txn_name ,cr_flag ,msg_type ,ins_trans ,sett_flag ,prof_f

lag ,rev_flag ,txn_type into :b0,:b1,:b2,:b3,:b4,:b5,:b6,:b7 fr

om s_txn_id where txn_id=:b8

1,048 1,048 1.0 0.00 0.00 3947377888

select area_code ,inst_name ,inst_level ,parent_issu_id ,settle_

bank ,settle_accno ,acc_name ,rsv1 ,rsv2 ,rsv3 ,rsv4 ,rsv5 ,stat

us ,local_flag ,msg_id ,quota_flag ,mch_check_tag into :b0,:b1,:

b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14,:b15,:b1

6 from u_issu_inst where (issu_id=:b17 and status=0)

1,032 1,032 1.0 0.00 0.00 3902825103

select txn_name into :b0 from s_txn_id where txn_id=:b1

941 941 1.0 0.00 0.00 3230982141

insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)

939 939 1.0 0.00 0.00 1877781575

delete from fet$ where file#=:1 and block#=:2 and ts#=:3

793 793 1.0 0.00 0.00 1695353531

select resp_code_text into :b0 from u_resp_code where resp_code

=:b1

774 774 1.0 0.00 0.00 893736417

select mch_grp ,cname ,ename ,settle_bank ,settle_accno ,scape_f

lag ,scape_id ,area_code ,bus_type ,mcc into :b0,:b1,:b2,:b3,:b4

,:b5,:b6,:b7,:b8,:b9 from mch_info where (mid=:b10 and status=0

)

689 689 1.0 0.00 0.00 1705880752

select file# from file$ where ts#=:1

689 0 0.0 0.00 0.00 1839874543

select file#,block#,length from uet$ where ts#=:1 and segfile#=:

2 and segblock#=:3 and ext#=:4

689 689 1.0 0.00 0.00 3687396716

insert into uet$ (segfile#,segblock#,ext#,ts#,file#,block#,lengt

h)values (:1, :2, :3, :4, :5, :6, :7)

681 1,067 1.6 0.00 0.00 904892542

select file#,block#,length from fet$ where length>=:1 and

ts#=:2 and file#=:3

669 669 1.0 0.00 0.00 528349613

delete from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 a

^LSQL ordered by Executions for DB: ORCL Instance: orcl Snaps: 11 -12

-> End Executions Threshold: 100

CPU per Elap per

Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value

------------ --------------- ---------------- ----------- ---------- ----------

nd ext#=:4

636 636 1.0 0.00 0.00 3935516425

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,

order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:

1

587 554 0.9 0.00 0.00 4116986312

select * from lps_acc where (account=:b0 and issu_id=:b1) order

by acc_type

520 256 0.5 0.00 0.00 3250462428

select trk_m ,cvn2_m ,misc_flag into :b0,:b1,:b2 from s_txn_res

trict where (((issu_id=:b3 and sub_type=:b4) and txn_id=:b5) and

status=0)

519 519 1.0 0.00 0.00 1465481054

select count(*) into :b0 from s_txn_mask where (sub_type=:b1 a

nd txn_id=:b2)

519 357 0.7 0.00 0.00 2260264344

select * into :s1 ,:s2 ,:s3 ,:s4 ,:s5 ,:s6 ,:s7 ,:s8 ,:s9 ,:s10

,:s11 ,:s12 ,:s13 ,:s14 ,:s15 ,:s16 ,:s17 ,:s18 ,:s19 ,:s20 ,:s2

1 ,:s22 ,:s23 ,:s24 ,:s25 ,:s26 ,:s27 ,:s28 ,:s29 ,:s30 ,:s31 ,:

s32 ,:s33 ,:s34 ,:s35 ,:s36 ,:s37 from lps_mst where (pan=:b1

and issu_id=:b2)

519 519 1.0 0.00 0.00 3050780589

select count(*) into :b0 from s_txn_restrict where issu_id=:b1

494 494 1.0 0.00 0.00 2249281901

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts

=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13

, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh

int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1

and file#=:2 and block#=:3

489 489 1.0 0.00 0.00 543063470

select * into :s1 ,:s2 ,:s3 ,:s4 ,:s5 ,:s6 ,:s7 ,:s8 ,:s9 ,:s10

,:s11 ,:s12 from s_acc_type where acc_type=:b1

489 489 1.0 0.00 0.00 2752590023

select * into :s1 ,:s2 ,:s3 ,:s4 from s_card_type where card_t

ype=:b1

469 0 0.0 0.00 0.00 3459622164

call p_get_seq(:b0,:b1) into :b2

453 452 1.0 0.00 0.00 1412076633

select issu_id ,sub_type into :b0,:b1 from s_card_bin where ((S

UBSTR(:b2,1,LENGTH(card_bin))=card_bin and (card_len=:b3 or card

_len=0)) and status=0)

^LSQL ordered by Executions for DB: ORCL Instance: orcl Snaps: 11 -12

-> End Executions Threshold: 100

CPU per Elap per

Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value

------------ --------------- ---------------- ----------- ---------- ----------

451 451 1.0 0.00 0.00 2803605287

select card_type into :b0 from s_sub_type where sub_type=:b1

427 258 0.6 0.00 0.00 1442905447

select issu_id ,mch_id ,mng_id ,acq_id ,sett_id ,prof_mode ,sett

_mnr ,status into :b0,:b1,:b2,:b3,:b4,:b5,:b6,:b7 from mch_acq

where ((mch_id=:b8 and issu_id=:b9) and status=0)

411 411 1.0 0.00 0.00 848502162

select * into :s1 ,:s2 ,:s3 ,:s4 ,:s5 ,:s6 ,:s7 ,:s8 ,:s9 ,:s10

,:s11 ,:s12 ,:s13 ,:s14 ,:s15 ,:s16 ,:s17 ,:s18 ,:s19 ,:s20 ,:s2

-------------------------------------------------------------

5.4 SQL 統計資訊－呼叫、解析次數

總結：

在這一部分，主要顯示 PARSE與 EXECUTIONS的對比情況。如果 PARSE/EXECUTIONS>1，

往往說明這個語句可能存在問題：沒有使用繫結變數，共享池設定太小，cursor_sharing被設定為exact，沒有設定session_cached_cursors等等問題。

^LSQL ordered by Parse Calls for DB: ORCL Instance: orcl Snaps: 11 -12

-> End Parse Calls Threshold: 1000

% Total

Parse Calls Executions Parses Hash Value

------------ ------------ -------- ----------

1,350 1,350 0.05 2913840444

select length from fet$ where file#=:1 and block#=:2 and ts#=:3

941 941 0.03 3230982141

insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)

939 939 0.03 1877781575

delete from fet$ where file#=:1 and block#=:2 and ts#=:3

868 1,048 0.03 3947377888

select area_code ,inst_name ,inst_level ,parent_issu_id ,settle_

bank ,settle_accno ,acc_name ,rsv1 ,rsv2 ,rsv3 ,rsv4 ,rsv5 ,stat

us ,local_flag ,msg_id ,quota_flag ,mch_check_tag into :b0,:b1,:

b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14,:b15,:b1

6 from u_issu_inst where (issu_id=:b17 and status=0)

858 2,385 0.03 94587681

select arg_value into :b0 from sys_arg where arg_name=:b1

689 689 0.02 1705880752

select file# from file$ where ts#=:1

689 689 0.02 1839874543

select file#,block#,length from uet$ where ts#=:1 and segfile#=:

2 and segblock#=:3 and ext#=:4

689 689 0.02 3687396716

insert into uet$ (segfile#,segblock#,ext#,ts#,file#,block#,lengt

h)values (:1, :2, :3, :4, :5, :6, :7)

681 681 0.02 904892542

select file#,block#,length from fet$ where length>=:1 and

ts#=:2 and file#=:3

669 669 0.02 528349613

delete from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 a

nd ext#=:4

636 636 0.02 3935516425

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,

order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:

1

533 793 0.02 1695353531

select resp_code_text into :b0 from u_resp_code where resp_code

=:b1

494 494 0.02 2249281901

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts

=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13

, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh

int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1

and file#=:2 and block#=:3

^LSQL ordered by Parse Calls for DB: ORCL Instance: orcl Snaps: 11 -12

-> End Parse Calls Threshold: 1000

% Total

Parse Calls Executions Parses Hash Value

------------ ------------ -------- ----------

487 519 0.02 2260264344

select * into :s1 ,:s2 ,:s3 ,:s4 ,:s5 ,:s6 ,:s7 ,:s8 ,:s9 ,:s10

,:s11 ,:s12 ,:s13 ,:s14 ,:s15 ,:s16 ,:s17 ,:s18 ,:s19 ,:s20 ,:s2

1 ,:s22 ,:s23 ,:s24 ,:s25 ,:s26 ,:s27 ,:s28 ,:s29 ,:s30 ,:s31 ,:

s32 ,:s33 ,:s34 ,:s35 ,:s36 ,:s37 from lps_mst where (pan=:b1

and issu_id=:b2)

481 519 0.02 1465481054

select count(*) into :b0 from s_txn_mask where (sub_type=:b1 a

nd txn_id=:b2)

481 519 0.02 3050780589

select count(*) into :b0 from s_txn_restrict where issu_id=:b1

481 520 0.02 3250462428

select trk_m ,cvn2_m ,misc_flag into :b0,:b1,:b2 from s_txn_res

trict where (((issu_id=:b3 and sub_type=:b4) and txn_id=:b5) and

status=0)

480 489 0.02 2752590023

select * into :s1 ,:s2 ,:s3 ,:s4 from s_card_type where card_t

ype=:b1

381 411 0.01 848502162

select * into :s1 ,:s2 ,:s3 ,:s4 ,:s5 ,:s6 ,:s7 ,:s8 ,:s9 ,:s10

,:s11 ,:s12 ,:s13 ,:s14 ,:s15 ,:s16 ,:s17 ,:s18 ,:s19 ,:s20 ,:s2

1 ,:s22 ,:s23 ,:s24 ,:s25 ,:s26 from s_sub_type where sub_type

=:b1

363 363 0.01 609682960

select chrg_mnr ,fee_manner ,fee_rate ,srfs ,txn_fee ,fee_min ,f

ee_max ,rsv1 ,rsv2 into :b0,:b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8 fro

m u_cust_fee where ((issu_id=:b9 and sub_type=:b10) and txn_id=:

b11)

363 469 0.01 3459622164

call p_get_seq(:b0,:b1) into :b2

355 587 0.01 4116986312

select * from lps_acc where (account=:b0 and issu_id=:b1) order

by acc_type

345 345 0.01 775311761

select * from auth_org_info where auth_org_info.parent_code=:1

AND auth_org_info.delete_flag=:2

335 335 0.01 1753170949

select RRN.nextval from dual

325 489 0.01 543063470

select * into :s1 ,:s2 ,:s3 ,:s4 ,:s5 ,:s6 ,:s7 ,:s8 ,:s9 ,:s10

,:s11 ,:s12 from s_acc_type where acc_type=:b1

^LSQL ordered by Parse Calls for DB: ORCL Instance: orcl Snaps: 11 -12

-> End Parse Calls Threshold: 1000

% Total

Parse Calls Executions Parses Hash Value

------------ ------------ -------- ----------

299 453 0.01 1412076633

select issu_id ,sub_type into :b0,:b1 from s_card_bin where ((S

UBSTR(:b2,1,LENGTH(card_bin))=card_bin and (card_len=:b3 or card

_len=0)) and status=0)

299 451 0.01 2803605287

select card_type into :b0 from s_sub_type where sub_type=:b1

295 1,156 0.01 438662453

select txn_name ,cr_flag ,msg_type ,ins_trans ,sett_flag ,prof_f

lag ,rev_flag ,txn_type into :b0,:b1,:b2,:b3,:b4,:b5,:b6,:b7 fr

-------------------------------------------------------------

5.5 SQL 統計資訊－共享記憶體佔用

在這一部分，主要是針對shared memory佔用的情況進行排序。

5.6 SQL 統計資訊－多版本快取

在這一部分，主要是針對SQL語句的多版本進行排序。相同的SQL文字，但是不同屬性，比如物件owner不同，會話最佳化模式不同、型別不同、

長度不同和繫結變數不同等等的語句，他們是不能共享的，所以再快取中會存在多個不同的版本。這當然就造成了資源上的更多的消耗。

6 、例項的活動資訊

總結：

1 這部分資料主要是從 V$SYSSTAT表中統計出來的

由consistent gets，db block gets和physical reads這三個值，我們也可以計算得到 buffer hit ratio，計算的公式如下：

buffer hit ratio = 100*(1-physical reads /(consistent gets+ db block gets))，

例如在這裡，我們可以計算得到：buffer hit ratio =100*(1-1530325/(6365125+3402591))= 84.332827

2 髒資料從LRU列表中老化， dirty buffers inspected 139 0.1 0.2

如果這個值大於0，就需要考慮增加DBWRs。

3 free buffer inspected 145 0.1 0.2

值包含dirty，pinned，busy的buffer區域，如果free buffer inspected - dirty buffers inspected - buffer is pinned count=145-139-9,007,973的值還是比較大，

表明不能被重用的記憶體塊比較多，這將導致latch爭用，需要增大buffer cache。

4 透過parse count (hard)和parse count (total)，可以計算 soft parse率為：

100-100*(parse count (hard)/parse count (total)) =100-100*(1-496/27985)= 98.227

5 sort(disk)磁碟排序一般不能超過5％。如果超過5％，需要設定引數PGA_AGGREGATE_TARGET或者 SORT_AREA_SIZE，注意，這裡SORT_AREA_SIZE是分配給每個使用者的，

PGA_AGGREGATE_TARGET則是針對所有的session的一個總數設定。

6 table fetch by rowid 這是透過索引或者where rowid=語句來取得的行數，當然這個值越大越好

7 table fetch continued row 這是發生行遷移的行。當行遷移的情況比較嚴重時，需要對這部分進行最佳化。

8 table scans (long tables) longtables就是表的大小超過buffer buffer* _SMALL_TABLE_THRESHOLD的表。如果一個資料庫的大表掃描過多，那麼db file scattered read等待事件可能同樣非常顯著。

如果table scans (long tables)的per Trans值大於0，你可能需要增加適當的索引來最佳化你的SQL語句。

9 table scans (short tables) 是指表的長度低於buffer chache 2％（2％是有隱含引數_SMALL_TABLE_THRESHOLD定義的，這個引數在oracle不同的版本中，有不同的含義。在9i和10g中，該引數值定義

為2%，在8i中，該引數值為20個blocks，在v7中，該引數為5個blocks）的表。這些表將優先使用全表掃描。一般不使用索引。_SMALL_TABLE_THRESHOLD值的計算方法如下（9i,8K）： (db_cache_size/8192)*2％。

注意：_SMALL_TABLE_THRESHOLD引數修改是相當危險的操作。

^LInstance Activity Stats for DB: ORCL Instance: orcl Snaps: 11 -12

Statistic Total per Second per Trans

--------------------------------- ------------------ -------------- ------------

CR blocks created 2,731 2.3 4.2

DBWR buffers scanned 6,055 5.0 9.4

DBWR checkpoint buffers written 964 0.8 1.5

DBWR checkpoints 0 0.0 0.0

DBWR free buffers found 5,159 4.3 8.0

DBWR lru scans 56 0.1 0.1

DBWR make free requests 69 0.1 0.1

DBWR revisited being-written buff 0 0.0 0.0

DBWR summed scan depth 6,055 5.0 9.4

DBWR transaction table writes 5 0.0 0.0

DBWR undo block writes 823 0.7 1.3

SQL*Net roundtrips to/from client 50,950 42.3 78.8

background checkpoints completed 0 0.0 0.0

background checkpoints started 0 0.0 0.0

background timeouts 1,185 1.0 1.8

branch node splits 1 0.0 0.0

buffer is not pinned count 4,473,451 3,709.3 6,914.1

buffer is pinned count 9,007,973 7,469.3 13,922.7

bytes received via SQL*Net from c 16,994,371 14,091.5 26,266.4

bytes sent via SQL*Net to client 30,858,828 25,587.8 47,695.3

calls to get snapshot scn: kcmgss 40,734 33.8 63.0

calls to kcmgas 142,640 118.3 220.5

calls to kcmgcs 776 0.6 1.2

cleanouts and rollbacks - consist 40 0.0 0.1

cleanouts only - consistent read 34 0.0 0.1

cluster key scan block gets 13,744 11.4 21.2

cluster key scans 6,046 5.0 9.3

commit cleanout failures: block l 148 0.1 0.2

commit cleanout failures: buffer 0 0.0 0.0

commit cleanout failures: callbac 12 0.0 0.0

commit cleanout failures: cannot 0 0.0 0.0

commit cleanouts 7,732 6.4 12.0

commit cleanouts successfully com 7,572 6.3 11.7

consistent changes 4,356 3.6 6.7

consistent gets 6,365,125 5,277.9 9,837.9

consistent gets - examination 228,324 189.3 352.9

current blocks converted for CR 0 0.0 0.0

cursor authentications 364 0.3 0.6

data blocks consistent reads - un 2,826 2.3 4.4

db block changes 36,295 30.1 56.1

db block gets 3,402,591 2,821.4 5,259.0

deferred (CURRENT) block cleanout 4,840 4.0 7.5

dirty buffers inspected 139 0.1 0.2

enqueue conversions 0 0.0 0.0

enqueue releases 11,489 9.5 17.8

enqueue requests 11,498 9.5 17.8

enqueue timeouts 0 0.0 0.0

enqueue waits 0 0.0 0.0

execute count 34,157 28.3 52.8

free buffer inspected 145 0.1 0.2

free buffer requested 1,503,032 1,246.3 2,323.1

hot buffers moved to head of LRU 14,494 12.0 22.4

immediate (CR) block cleanout app 74 0.1 0.1

immediate (CURRENT) block cleanou 767 0.6 1.2

index fast full scans (full) 0 0.0 0.0

leaf node splits 159 0.1 0.3

^LInstance Activity Stats for DB: ORCL Instance: orcl Snaps: 11 -12

Statistic Total per Second per Trans

--------------------------------- ------------------ -------------- ------------

logons cumulative 2 0.0 0.0

messages received 3,906 3.2 6.0

messages sent 3,906 3.2 6.0

no buffer to keep pinned count 109,733 91.0 169.6

no work - consistent read gets 4,511,556 3,740.9 6,973.0

opened cursors cumulative 15,238 12.6 23.6

parse count (failures) 0 0.0 0.0

parse count (hard) 496 0.4 0.8

parse count (total) 27,985 23.2 43.3

physical reads 1,530,325 1,268.9 2,365.3

physical reads direct 31,533 26.2 48.7

physical writes 200,463 166.2 309.8

physical writes direct 198,468 164.6 306.8

physical writes non checkpoint 200,386 166.2 309.7

pinned buffers inspected 0 0.0 0.0

prefetched blocks 1,283,280 1,064.1 1,983.4

prefetched blocks aged out before 0 0.0 0.0

recursive calls 111,483 92.4 172.3

redo blocks written 15,816 13.1 24.5

redo buffer allocation retries 5 0.0 0.0

redo entries 18,596 15.4 28.7

redo log space requests 0 0.0 0.0

redo ordering marks 0 0.0 0.0

redo size 7,305,896 6,058.0 11,292.0

redo synch writes 1,431 1.2 2.2

redo wastage 508,268 421.5 785.6

redo writes 2,298 1.9 3.6

rollback changes - undo records a 2 0.0 0.0

rollbacks only - consistent read 2,690 2.2 4.2

rows fetched via callback 42,912 35.6 66.3

session logical reads 9,767,716 8,099.3 15,096.9

session uga memory max 406,588 337.1 628.4

shared hash latch upgrades - no w 21,049 17.5 32.5

shared hash latch upgrades - wait 0 0.0 0.0

sorts (disk) 30 0.0 0.1

sorts (memory) 1,053 0.9 1.6

sorts (rows) 2,083,842 1,727.9 3,220.8

summed dirty queue length 1,069 0.9 1.7

switch current to new buffer 53 0.0 0.1

table fetch by rowid 4,939,430 4,095.7 7,634.4

table fetch continued row 118,525 98.3 183.2

table scan blocks gotten 3,444,700 2,856.3 5,324.1

table scan rows gotten 72,141,486 59,818.8 111,501.5

table scans (long tables) 139 0.1 0.2

table scans (short tables) 1,684,127 1,396.5 2,603.0

transaction rollbacks 2 0.0 0.0

transaction tables consistent rea 1 0.0 0.0

user calls 51,050 42.3 78.9

user commits 598 0.5 0.9

user rollbacks 49 0.0 0.1

write clones created in backgroun 0 0.0 0.0

write clones created in foregroun 0 0.0 0.0

7 、I/O統計資訊

總結：

在這裡主要關注 Av Rd(ms)列 (reads per millisecond)的值，一般來說，大部分的磁碟系統的這個值都能調整到 14ms以下，oracle認為該值超過 20ms都是不必要的。如果該值超過 1000ms，基本可以肯定存在 I/O的效能瓶頸。如果在這一列上出現 ######，可能是你的系統存在嚴重的I/O問題，也可能是格式的顯示問題。

當出現上面的問題，我們可以考慮以下的方法：

1 ）最佳化操作該表空間或者檔案的相關的語句。

2 ）如果該表空間包含了索引，可以考慮壓縮索引，使索引的分佈空間減小，從而減小I/O。

3 ）將該表空間分散在多個邏輯卷中，平衡I/O的負載。

4 ）我們可以透過設定引數DB_FILE_MULTIBLOCK_READ_COUNT來調整讀取的並行度，這將提高全表掃描的效率。但是也會帶來一個問題，就是oracle會因此更多的使用全表掃描而放棄某些索引的使用。為解決這個問題，我們需要設定另外一個引數OPTIMIZER_INDEX_COST_ADJ=30（一般建議設定10－50）。

關於OPTIMIZER_INDEX_COST_ADJ＝n：該引數是一個百分比值，預設值為100，可以理解為FULL SCAN COST/INDEX SCAN COST。當n%* INDEX SCAN COST

可以對比這兩種情況的執行計劃不同的COST，從而設定一個更合適的值。

^LTablespace IO Stats for DB: ORCL Instance: orcl Snaps: 11 -12

->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)

-------------- ------- ------ ------- ------------ -------- ---------- ------

APP_DATA

213,556 177 0.1 7.0 650 1 510 0.0

APP_TEMP

20,654 17 0.0 1.5 189,778 157 0 0.0

INDEX14

1,381 1 1.5 1.0 9 0 0 0.0

APP_RBS

1 0 0.0 1.0 833 1 0 0.0

STATSPACK_CHEN

204 0 2.0 1.0 494 0 0 0.0

HIST14

611 1 0.7 1.0 0 0 0 0.0

TEMP

171 0 0.0 5.4 311 0 0 0.0

SYSTEM

341 0 1.1 4.2 24 0 0 0.0

HIST13

17 0 0.0 1.0 0 0 0 0.0

INDEX13

4 0 5.0 1.0 0 0 0 0.0

INDEX10

3 0 3.3 1.0 0 0 0 0.0

INDEX11

3 0 3.3 1.0 0 0 0 0.0

INDEX12

3 0 3.3 1.0 0 0 0 0.0

INDEX09

2 0 0.0 1.0 0 0 0 0.0

INDEX15

1 0 0.0 1.0 0 0 0 0.0

INDEX16

1 0 0.0 1.0 0 0 0 0.0

INDEX17

1 0 0.0 1.0 0 0 0 0.0

INDEX18

1 0 0.0 1.0 0 0 0 0.0

INDEX19

1 0 0.0 1.0 0 0 0 0.0

INDEX20

1 0 0.0 1.0 0 0 0 0.0

-------------------------------------------------------------

^LFile IO Stats for DB: ORCL Instance: orcl Snaps: 11 -12

->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)

-------------- ------- ------ ------- ------------ -------- ---------- ------

APP_DATA /home/oracle/oradata/orcl/appdata.dbf

213,556 177 0.1 7.0 650 1 510 0.0

APP_RBS /home/oracle/oradata/orcl/apprbs.dbf

1 0 0.0 1.0 833 1 0

APP_TEMP /home/oracle/oradata/orcl/apptemp.dbf

20,654 17 0.0 1.5 189,778 157 0

HIST13 /home/oracle/oradata/orcl/hist13.dbf

17 0 0.0 1.0 0 0 0

HIST14 /home/oracle/oradata/orcl/hist14.dbf

611 1 0.7 1.0 0 0 0

INDEX09 /home/oracle/oradata/orcl/index09.dbf

2 0 0.0 1.0 0 0 0

INDEX10 /home/oracle/oradata/orcl/index10.dbf

3 0 3.3 1.0 0 0 0

INDEX11 /home/oracle/oradata/orcl/index11.dbf

3 0 3.3 1.0 0 0 0

INDEX12 /home/oracle/oradata/orcl/index12.dbf

3 0 3.3 1.0 0 0 0

INDEX13 /home/oracle/oradata/orcl/index13.dbf

4 0 5.0 1.0 0 0 0

INDEX14 /home/oracle/oradata/orcl/index14.dbf

1,381 1 1.5 1.0 9 0 0

INDEX15 /home/oracle/oradata/orcl/index15.dbf

1 0 0.0 1.0 0 0 0

INDEX16 /home/oracle/oradata/orcl/index16.dbf

1 0 0.0 1.0 0 0 0

INDEX17 /home/oracle/oradata/orcl/index17.dbf

1 0 0.0 1.0 0 0 0

INDEX18 /home/oracle/oradata/orcl/index18.dbf

1 0 0.0 1.0 0 0 0

INDEX19 /home/oracle/oradata/orcl/index19.dbf

1 0 0.0 1.0 0 0 0

INDEX20 /home/oracle/oradata/orcl/index20.dbf

1 0 0.0 1.0 0 0 0

STATSPACK_CHEN /home/oracle/oradata/orcl/statspack_chen01.dbf

^LFile IO Stats for DB: ORCL Instance: orcl Snaps: 11 -12

->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)

-------------- ------- ------ ------- ------------ -------- ---------- ------

204 0 2.0 1.0 494 0 0

SYSTEM /home/oracle/oradata/orcl/oracle1.dbf

341 0 1.1 4.2 24 0 0

TEMP /home/oracle/oradata/orcl/temp01.dbf

171 0 0.0 5.4 311 0 0

8 、Buffer Pool統計資訊

總結：

這裡將 buffer poll細分，列舉default、keep、recycle三種型別的buffer的詳細情況。在這份報告中，我們的系統中只使用Default size的buffer pool。

這裡的3個waits統計，其實在前面的等待時間中已經包含，所以可以參考前面的描述。關於命中率也已經在前面討論。所以，其實這段資訊不需要怎麼關注。

-------------------------------------------------------------

^LBuffer Pool Statistics for DB: ORCL Instance: orcl Snaps: 11 -12

-> Standard block size Pools D: default, K: keep, R: recycle

-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

Free Write Buffer

Number of Cache Buffer Physical Physical Buffer Complete Busy

P Buffers Hit % Gets Reads Writes Waits Waits Waits

--- ---------- ----- ----------- ----------- ---------- ------- -------- ------

D 23,604 81.1 7,924,107 1,498,577 1,996 0 0 510

-------------------------------------------------------------

9 、例項的恢復情況統計資訊

總結：

這部分主要是關於例項的恢復的一些統計資訊，也不需要怎麼關注。

Instance Recovery Stats for DB: ORCL Instance: orcl Snaps: 11 -12

-> B: Begin snapshot, E: End snapshot

Targt Estd Log File Log Ckpt Log Ckpt

MTTR MTTR Recovery Actual Target Size Timeout Interval

(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks

- ----- ----- ---------- ---------- ---------- ---------- ---------- ----------

B 0 7 0 10554 10000 1843200 29576 10000

E 0 8 0 10716 10000 1843200 26957 10000

-------------------------------------------------------------

10 、Buffer Pool調整的Advisory

11 、Buffer Pool等待情況統計

總結：

這裡的buffer等待往往帶來 data block的比較大的等待。這部分等待的情況在前面等待事件中已經作過描述。

Buffer wait Statistics for DB: ORCL Instance: orcl Snaps: 11 -12

-> ordered by wait time desc, waits desc

Tot Wait Avg

Class Waits Time (s) Time (ms)

------------------ ----------- ---------- ---------

data block 510 0 0

-------------------------------------------------------------

12 、PGA統計資訊

13 、PGA調整的Advisory

總結：

PGA_AGGREGATE_TARGET引數的調整建議。

我們可以看到，在advisory中，當 PGA_AGGREGATE_TARGET達到 500M時，再增大PGA_AGGREGATE_TARGET，基本已經起不到提升效能的作用了。

PGA Memory Stats for DB: ORCL Instance: orcl Snaps: 11 -12

-> WorkArea (W/A) memory is used for: sort, bitmap merge, and hash join ops

Statistic Begin (M) End (M) % Diff

----------------------------------- ---------------- ---------------- ----------

maximum PGA allocated 50.776 50.776 .00

-------------------------------------------------------------

14 、佇列的統計資訊

15 、回滾段統計資訊

總結：

從9i開始，回滾段一般都是自動管理的，一般情況下，這裡我們不需要太重點關注。

在這裡，主要關注 pct waits，如果出現比較多的pct waits，那就需要增加回滾段的數量或者增大回滾段的空間。

另外，觀察一下各個回滾段使用的情況，比較理想的是各個回滾段上 Avg Active比較均衡。

在oracle 9i之前，回滾段時手工管理的，可以透過指定optimal值來設定一個回滾段收縮的值，如果不設定，

預設也應當為initial+(minextents-1)*next extents ，這個指定的結果，就是限制了回滾段不能無限制的增長，

當超過optimal的設定值後，在適當的時候，oracle會shrinks到optimal大小。但是9i之後，undo一般都設定為auto模式，

在這種模式下，我們無法指定optimal值，好像也沒有預設值，所以無法shrinks，回滾段就會無限制的增長，一直到表空間利

用率達到為100％，如果表空間設定為自動擴充套件的方式，這種情況下，就更糟糕，undo將無限制的增長。在這裡，我們也可以看到，

shrinks的值為0，也就是說，從來就沒收縮過。

^LRollback Segment Stats for DB: ORCL Instance: orcl Snaps: 11 -12

->A high value for "Pct Waits" suggests more rollback segments may be required

->RBS stats may not be accurate between begin and end snaps when using Auto Undo

managment, as RBS may be dynamically created and dropped as needed

Trans Table Pct Undo Bytes

RBS No Gets Waits Written Wraps Shrinks Extends

------ -------------- ------- --------------- -------- -------- --------

0 5.0 0.00 0 0 0 0

1 2,954.0 0.00 1,517,404 0 0 0

2 2,065.0 0.00 487,874 0 0 0

3 2,004.0 0.00 419,060 0 0 0

-------------------------------------------------------------

^LRollback Segment Storage for DB: ORCL Instance: orcl Snaps: 11 -12

->Optimal Size should be larger than Avg Active

RBS No Segment Size Avg Active Optimal Size Maximum Size

------ --------------- --------------- --------------- ---------------

0 364,544 0 364,544

1 83,881,984 21,255,353 104,857,600 83,881,984

2 94,367,744 19,280,229 104,857,600 94,367,744

3 73,396,224 23,183,321 104,857,600 73,396,224

-------------------------------------------------------------

^LUndo Segment Summary for DB: ORCL Instance: orcl Snaps: 11 -12

-> Undo segment block stats:

-> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed

-> eS - expired Stolen, eR - expired Released, eU - expired reUsed

Undo Undo Num Max Qry Max Tx Snapshot Out of uS/uR/uU/

TS# Blocks Trans Len (s) Concurcy Too Old Space eS/eR/eU

---- -------------- ---------- -------- ---------- -------- ------ -------------

0 0 0 0 0 0 0 0/0/0/0/0/0

-------------------------------------------------------------

Undo Segment Stats for DB: ORCL Instance: orcl Snaps: 11 -12

-> ordered by Time desc

Undo Num Max Qry Max Tx Snap Out of uS/uR/uU/

End Time Blocks Trans Len (s) Concy Too Old Space eS/eR/eU

------------ ------------ -------- ------- -------- ------- ------ -------------

05-Sep 10:12 0 0 0 0 0 0 0/0/0/0/0/0

-------------------------------------------------------------

16 、閂鎖統計資訊

總結：

Latch是一種低階排隊機制，用於防止對記憶體結構的並行訪問，保護系統全域性區(SGA)共享記憶體結構。

Latch是一種快速地被獲取和釋放的記憶體鎖。如果latch不可用，就會記錄latch free miss 。

大多數Latch問題都可以歸結為以下幾種:

　　 沒有很好的是用繫結變數(library cache latch和shared pool cache)、重作生成問題(redo allocation latch)、

緩衝儲存競爭問題(cache buffers LRU chain)，以及buffer cache中的存在"熱點"塊(cache buffers chain)。

另外也有一些latch等待與bug有關，應當關注Metalink相關bug的公佈及補丁的釋出。

當latch miss ratios大於0.5%時，就需要檢查latch的等待問題。

^LLatch Activity for DB: ORCL Instance: orcl Snaps: 11 -12

->"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

-> ordered by Wait Time desc, Avg Slps/Miss, Pct NoWait Miss desc

Pct Avg Wait Pct

Get Get Slps Time NoWait NoWait

Latch Requests Miss /Miss (s) Requests Miss

------------------------ -------------- ------ ------ ------ ------------ ------

multiblock read objects 3,650,504 0.0 0.0 0 0

redo allocation 23,291 0.0 0.0 0 0

shared pool 58,710 0.0 0.0 0 0

redo writing 11,270 0.0 0.0 0 0

cache buffers chains 19,527,806 0.0 0.0 0 2,848,400 0.0

hash table column usage 4 0.0 0 1,209 0.0

process allocation 2 0.0 0 2 0.0

row cache objects 72,989 0.0 0 1 0.0

redo copy 0 0 18,590 0.0

FOB s.o list latch 40 0.0 0 0

SQL memory manager worka 67 0.0 0 0

active checkpoint queue 2,054 0.0 0 0

channel handle pool latc 5 0.0 0 0

checkpoint queue latch 294,350 0.0 0 0

dml lock allocation 11,364 0.0 0 0

enqueues 33,214 0.0 0 0

file number translation 553 0.0 0 0

ncodef allocation latch 19 0.0 0 0

messages 13,686 0.0 0 0

longop free list 11 0.0 0 0

loader state object free 68 0.0 0 0

list of block allocation 5,444 0.0 0 0

library cache load lock 190 0.0 0 0

latch wait list 2 0.0 0 0

ktm global data 4 0.0 0 0

event group latch 2 0.0 0 0

user lock 6 0.0 0 0

undo global data 13,130 0.0 0 0

transaction branch alloc 19 0.0 0 0

transaction allocation 7,915 0.0 0 0

sort extent pool 425 0.0 0 0

session switching 19 0.0 0 0

session idle bit 106,190 0.0 0 0

session allocation 19,367 0.0 0 0

sequence cache 1,278 0.0 0 0

process group creation 5 0.0 0 0

post/wait queue latch 2,851 0.0 0 0

enqueue hash chains 22,981 0.0 0 0

child cursor hash table 4,480 0.0 0 0

channel operations paren 399 0.0 0 0

archive control 1 0.0 0 0

cache buffer handles 458 0.0 0 0

library cache 497,614 0.0 0.0 0 6 0.0

cache buffers lru chain 1,686,966 0.0 0.0 0 1,502,986 0.0

-------------------------------------------------------------

^LLatch Sleep breakdown for DB: ORCL Instance: orcl Snaps: 11 -12

-> ordered by misses desc

Get Spin &

Latch Name Requests Misses Sleeps Sleeps 1->4

-------------------------- -------------- ----------- ----------- ------------

cache buffers chains 19,527,806 417 5 0/0/0/0/0

cache buffers lru chain 1,686,966 332 5 327/5/0/0/0

library cache 497,614 33 1 32/1/0/0/0

-------------------------------------------------------------

^LLatch Miss Sources for DB: ORCL Instance: orcl Snaps: 11 -12

-> only latches with sleeps are shown

-> ordered by name, sleeps desc

NoWait Waiter

Latch Name Where Misses Sleeps Sleeps

------------------------ -------------------------- ------- ---------- --------

cache buffers chains kcbgtcr: kslbegin 0 4 5

cache buffers chains kcbzib: multi-block read: 0 1 0

library cache kglidp: parent 0 1 0

-------------------------------------------------------------

17 、共享池統計資訊

總結：

/* 庫快取詳細資訊，。

Get Requests ：get表示一種型別的鎖，語法分析鎖。這種型別的鎖在引用了一個物件的那條SQL語句的語法分析階段被設定在該物件上。每當一條語句被語法分析一次時，Get Requests的值就增加1。

pin requests ：pin也表示一種型別的鎖，是在執行發生的加鎖。每當一條語句執行一次，pin requests的值就增加1。

reloads ：reloads列顯示一條已執行過的語句因Library Cache使該語句的已語法分析版本過期或作廢而需要被重新語法分析的次數。

invalidations ：失效發生在一條已告訴快取的SQL語句即使已經在library cache中，但已被標記為無效並迎詞而被迫重新做語法分析的時候。每當已告訴快取的語句所引用的物件以某種方式被修改時，這些語句就被標記為無效。

pct miss應該不高於1％。

Reloads /pin requests <1 ％，否則應該考慮增大SHARED_POOL_SIZE。

該部分資訊透過v$librarycache檢視統計得到：

select namespace,gethitratio,pinhitratio,reloads,invalidations from v$librarycache

where namespace in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER', 'INDEX');

^LDictionary Cache Stats for DB: ORCL Instance: orcl Snaps: 11 -12

->"Pct Misses" should be very low (< 2% in most cases)

->"Cache Usage" is the number of cache entries being used

->"Pct SGA" is the ratio of usage to allocated size for that cache

Get Pct Scan Pct Mod Final Pct

Cache Requests Miss Reqs Miss Reqs Usage SGA

---------------------- ------------ ------ ------ ----- -------- ---------- ----

dc_free_extents 4,682 28.8 689 0.0 3,383 241 75

dc_histogram_defs 955 0.0 0 0 742 100

dc_object_ids 1,281 1.4 0 0 257 100

dc_objects 1,087 4.9 0 0 489 99

dc_profiles 1 0.0 0 0 1 17

dc_rollback_segments 32 0.0 0 0 5 83

dc_segments 2,190 9.0 0 882 424 99

dc_sequences 636 0.0 0 636 9 90

dc_tablespaces 3,012 0.0 0 0 33 97

dc_used_extents 1,358 50.7 0 1,358 213 62

dc_user_grants 4 0.0 0 0 14 25

dc_usernames 42 0.0 0 0 9 43

dc_users 429 0.0 0 0 15 88

-------------------------------------------------------------

Library Cache Activity for DB: ORCL Instance: orcl Snaps: 11 -12

->"Pct Misses" should be very low

Get Pct Pin Pct Invali-

Namespace Requests Miss Requests Miss Reloads dations

--------------- ------------ ------ -------------- ------ ---------- --------

BODY 5 0.0 5 0.0 0 0

SQL AREA 28,116 0.0 111,634 0.5 502 482

TABLE/PROCEDURE 2,647 2.3 6,817 3.4 69 0

-------------------------------------------------------------

18 、SGA記憶體分配

總結：

這部分是關於SGA記憶體分配的一個描述，我們可以透過show sga等命令也可以檢視到這裡的內容。

Fixed Size:

oracle 的不同平臺和不同版本下可能不一樣，但對於確定環境是一個固定的值，裡面儲存了SGA 各部分元件的資訊，可以看作引導建立SGA的區域。

Variable Size:

包含了shared_pool_size、java_pool_size、large_pool_size 等記憶體設定。

Database Buffers:

指資料緩衝區，在8i 中包含db_block_buffer*db_block_size、buffer_pool_keep、buffer_pool_recycle 三部分記憶體。在9i 中包含db_cache_size、db_keep_cache_size、db_recycle_cache_size、 db_nk_cache_size。

Redo Buffers:

指日誌緩衝區，log_buffer。對於logbuffer，我們會發現在v$parameter、v$sgastat、v$sga的值不一樣。v$parameter是我們可以自己設定的值，也可以設定為0，這時候，oracle降會以預設的最小值來設定v$sgastat的值，

同時v$sga也是最小的值。v$sgastat的值是基於引數log_buffer的設定值，再根據一定的計算公式得到的一個值。v$sga的值，則是根據v$sgastat的值，然後選擇再加上8k－11k的一個值，得到min(n*4k)的一個值。就是說得

到的結果是4k的整數倍，也就是說v$sga是以4k的單位遞增的。

^LSGA Memory Summary for DB: ORCL Instance: orcl Snaps: 11 -12

SGA regions Size in Bytes

------------------------------ ----------------

Database Buffers 100,663,296

Fixed Size 279,740

Redo Buffers 417,792

Variable Size 251,658,240

----------------

sum 353,019,068

-------------------------------------------------------------

SGA breakdown difference for DB: ORCL Instance: orcl Snaps: 11 -12

Pool Name Begin value End value % Diff

------ ------------------------------ ---------------- ---------------- -------

java free memory 33,554,432 33,554,432 0.00

shared 1M buffer 1,049,088 1,049,088 0.00

shared DML lock 196,380 196,380 0.00

shared FileIdentificatonBlock 127,884 127,884 0.00

shared FileOpenBlock 539,056 539,056 0.00

shared KGK heap 1,836 1,836 0.00

shared KGLS heap 2,210,856 2,217,588 0.30

shared KSXR pending messages que 225,836 225,836 0.00

shared KSXR receive buffers 1,058,000 1,058,000 0.00

shared PL/SQL DIANA 838,272 838,272 0.00

shared PL/SQL MPCODE 912,152 912,152 0.00

shared PLS non-lib hp 2,068 2,068 0.00

shared character set object 425,240 425,240 0.00

shared db_handles 186,000 186,000 0.00

shared dictionary cache 970,024 1,000,516 3.14

shared enqueue 311,660 311,660 0.00

shared enqueue resources 135,608 135,608 0.00

shared errors 191,884 191,884 0.00

shared event statistics per sess 2,692,060 2,692,060 0.00

shared fixed allocation callback 380 380 0.00

shared free memory 9,190,728 9,978,700 8.57

shared joxs heap init 4,220 4,220 0.00

shared ksm_file2sga region 148,652 148,652 0.00

shared ktlbk state objects 157,504 157,504 0.00

shared library cache 12,438,944 12,225,476 -1.72

shared long op statistics array 110,000 110,000 0.00

shared message pool freequeue 767,192 767,192 0.00

shared miscellaneous 5,083,364 5,069,528 -0.27

shared parameters 11,844 11,844 0.00

shared partitioning d 60,868 60,868 0.00

shared processes 250,800 250,800 0.00

shared sessions 778,540 778,540 0.00

shared simulator trace entries 786,432 786,432 0.00

shared sql area 108,768,424 108,169,592 -0.55

shared table definiti 2,632 3,572 35.71

shared temporary tabl 5,096 5,096 0.00

shared transaction 355,356 355,356 0.00

shared trigger inform 64 64 0.00

db_block_buffers 100,663,296 100,663,296 0.00

fixed_sga 279,740 279,740 0.00

log_buffer 409,600 409,600 0.00

-------------------------------------------------------------

19 、資源限制統計資訊

20 、初始化統計資訊

總結：

這是報表的最後一部分，是關於常用重要的一些系統的初始化引數設定的彙總情況。

^Linit.ora Parameters for DB: ORCL Instance: orcl Snaps: 11 -12

End value

Parameter Name Begin value (if different)

----------------------------- --------------------------------- --------------

compatible 8.1.0

control_files /home/oracle/oradata/orcl/control

db_block_buffers 23604

db_block_size 4096

db_file_multiblock_read_count 8

db_files 80

db_name orcl

global_names TRUE

instance_name orcl

java_pool_size 33554432

large_pool_size 61440000

log_archive_dest_1 location=/DS5020/orcl_archive

log_archive_format orcl_%t_%s.arc

log_archive_start TRUE

log_buffer 409600

log_checkpoint_interval 10000

log_checkpoint_timeout 1800

max_dump_file_size 10240

max_enabled_roles 30

open_cursors 100

parallel_max_servers 5

processes 300

rollback_segments r01, r02, r03

service_names dbname

shared_pool_size 134217728

sort_area_retained_size 262144

sort_area_size 262144

star_transformation_enabled false

-------------------------------------------------------------

End of Report

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

Oracle statspack綜合分析

Oracle statspack綜合分析

相關文章