AWR解析報告分析
WORKLOAD REPOSITORY report for
DB Name |
DB Id |
Instance |
Inst num |
Release |
RAC |
Host |
ICCI |
1314098396 |
ICCI1 |
1 |
10.2.0.3.0 |
YES |
HPGICCI1 |
|
Snap Id |
Snap Time |
Sessions |
Cursors/Session |
Begin Snap: |
2678 |
25-Dec-08 14:04:50 |
24 |
1.5 |
End Snap: |
2680 |
25-Dec-08 15:23:37 |
26 |
1.5 |
Elapsed: |
|
78.79 (mins) |
|
|
DB Time: |
|
11.05 (mins) |
|
|
DB Time不包括Oracle後臺程式消耗的時間。如果DB Time遠遠小於Elapsed時間,說明資料庫比較空閒。
在79分鐘裡(其間收集了3次快照資料),資料庫耗時11分鐘,RDA資料中顯示系統有8個邏輯CPU(4個物理CPU),平均每個CPU耗時1.4分鐘,CPU利用率只有大約2%(1.4/79)。說明系統壓力非常小。
可是對於批次系統,資料庫的工作負載總是集中在一段時間內。如果快照週期不在這一段時間內,或者快照週期跨度太長而包含了大量的資料庫空閒時間,所得出的分析結果是沒有意義的。這也說明選擇分析時間段很關鍵,要選擇能夠代表效能問題的時間段。
Report Summary
Cache Sizes
|
Begin |
End |
|
|
Buffer Cache: |
3,344M |
3,344M |
Std Block Size: |
8K |
Shared Pool Size: |
704M |
704M |
Log Buffer: |
14,352K |
顯示SGA中每個區域的大小(在AMM改變它們之後),可用來與初始引數值比較。
shared pool主要包括library cache和dictionary cache。library cache用來儲存最近解析(或編譯)後SQL、PL/SQL和Java classes等。library cache用來儲存最近引用的資料字典。發生在library cache或dictionary cache的cache miss代價要比發生在buffer cache的代價高得多。因此shared pool的設定要確保最近使用的資料都能被cache。
Load Profile
|
Per Second |
Per Transaction |
Redo size: |
918,805.72 |
775,912.72 |
Logical reads: |
3,521.77 |
2,974.06 |
Block changes: |
1,817.95 |
1,535.22 |
Physical reads: |
68.26 |
57.64 |
Physical writes: |
362.59 |
306.20 |
User calls: |
326.69 |
275.88 |
Parses: |
38.66 |
32.65 |
Hard parses: |
0.03 |
0.03 |
Sorts: |
0.61 |
0.51 |
Logons: |
0.01 |
0.01 |
Executes: |
354.34 |
299.23 |
Transactions: |
1.18 |
|
% Blocks changed per Read: |
51.62 |
Recursive Call %: |
51.72 |
Rollback per transaction %: |
85.49 |
Rows per Sort: |
######## |
顯示資料庫負載概況,將之與基線資料比較才具有更多的意義,如果每秒或每事務的負載變化不大,說明應用執行比較穩定。單個的報告資料只說明應用的負載情況,絕大多資料並沒有一個所謂“正確”的值,然而Logons大於每秒1~2個、Hard parses大於每秒100、全部parses超過每秒300表明可能有爭用問題。
Redo size:每秒/每事務產生的redo大小(單位位元組),可標誌資料庫任務的繁重程式。
Logical reads:每秒/每事務邏輯讀的塊數
Block changes:每秒/每事務修改的塊數
Physical reads:每秒/每事務物理讀的塊數
Physical writes:每秒/每事務物理寫的塊數
User calls:每秒/每事務使用者call次數
Parses:SQL解析的次數
Hard parses:其中硬解析的次數,硬解析太多,說明SQL重用率不高。
Sorts:每秒/每事務的排序次數
Logons:每秒/每事務登入的次數
Executes:每秒/每事務SQL執行次數
Transactions:每秒事務數
Blocks changed per Read:表示邏輯讀用於修改資料塊的比例
Recursive Call:遞迴呼叫佔所有操作的比率
Rollback per transaction:每事務的回滾率
Rows per Sort:每次排序的行數
注:
Oracle的硬解析和軟解析
提到軟解析(soft prase)和硬解析(hard prase),就不能不說一下Oracle對sql的處理過程。當你發出一條sql語句交付Oracle,在執行和獲取結果前,Oracle對此sql將進行幾個步驟的處理過程:
1、語法檢查(syntax check)
檢查此sql的拼寫是否語法。
2、語義檢查(semantic check)
諸如檢查sql語句中的訪問物件是否存在及該使用者是否具備相應的許可權。
3、對sql語句進行解析(prase)
利用內部演算法對sql進行解析,生成解析樹(parse tree)及執行計劃(execution plan)。
4、執行sql,返回結果(execute and return)
其中,軟、硬解析就發生在第三個過程裡。
Oracle利用內部的hash演算法來取得該sql的hash值,然後在library cache裡查詢是否存在該hash值;
假設存在,則將此sql與cache中的進行比較;
假設“相同”,就將利用已有的解析樹與執行計劃,而省略了最佳化器的相關工作。這也就是軟解析的過程。
誠然,如果上面的2個假設中任有一個不成立,那麼最佳化器都將進行建立解析樹、生成執行計劃的動作。這個過程就叫硬解析。
建立解析樹、生成執行計劃對於sql的執行來說是開銷昂貴的動作,所以,應當極力避免硬解析,儘量使用軟解析。
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: |
100.00 |
Redo NoWait %: |
100.00 |
Buffer Hit %: |
98.72 |
In-memory Sort %: |
99.86 |
Library Hit %: |
99.97 |
Soft Parse %: |
99.92 |
Execute to Parse %: |
89.09 |
Latch Hit %: |
99.99 |
Parse CPU to Parse Elapsd %: |
7.99 |
% Non-Parse CPU: |
99.95 |
本節包含了Oracle關鍵指標的記憶體命中率及其它資料庫例項操作的效率。其中Buffer Hit Ratio 也稱Cache Hit Ratio,Library Hit ratio也稱Library Cache Hit ratio。同Load Profile一節相同,這一節也沒有所謂“正確”的值,而只能根據應用的特點判斷是否合適。在一個使用直接讀執行大型並行查詢的DSS環境,20%的Buffer Hit Ratio是可以接受的,而這個值對於一個OLTP系統是完全不能接受的。根據Oracle的經驗,對於OLTPT系統,Buffer Hit Ratio理想應該在90%以上。
Buffer Nowait表示在記憶體獲得資料的未等待比例。
buffer hit表示程式從記憶體中找到資料塊的比率,監視這個值是否發生重大變化比這個值本身更重要。對於一般的OLTP系統,如果此值低於80%,應該給資料庫分配更多的記憶體。
Redo NoWait表示在LOG緩衝區獲得BUFFER的未等待比例。如果太低(可參考90%閥值),考慮增加LOG BUFFER。
library hit表示Oracle從Library Cache中檢索到一個解析過的SQL或PL/SQL語句的比率,當應用程式呼叫SQL或儲存過程時,Oracle檢查Library Cache確定是否存在解析過的版本,如果存在,Oracle立即執行語句;如果不存在,Oracle解析此語句,並在Library Cache中為它分配共享SQL區。低的library hit ratio會導致過多的解析,增加CPU消耗,降低效能。如果library hit ratio低於90%,可能需要調大shared pool區。
Latch Hit:Latch是一種保護記憶體結構的鎖,可以認為是SERVER程式獲取訪問記憶體資料結構的許可。要確保Latch Hit>99%,否則意味著Shared Pool latch爭用,可能由於未共享的SQL,或者Library Cache太小,可使用繫結變更或調大Shared Pool解決。
Parse CPU to Parse Elapsd:解析實際執行時間/(解析實際執行時間+解析中等待資源時間),越高越好。
Non-Parse CPU :SQL實際執行時間/(SQL實際執行時間+SQL解析時間),太低表示解析消耗時間過多。
Execute to Parse:是語句執行與分析的比例,如果要SQL重用率高,則這個比例會很高。該值越高表示一次解析後被重複執行的次數越多。
In-memory Sort:在記憶體中排序的比率,如果過低說明有大量的排序在臨時表空間中進行。考慮調大PGA。
Soft Parse:軟解析的百分比(softs/softs+hards),近似當作sql在共享區的命中率,太低則需要調整應用使用繫結變數。
Shared Pool Statistics
|
Begin |
End |
Memory Usage %: |
47.19 |
47.50 |
% SQL with executions>1: |
88.48 |
79.81 |
% Memory for SQL w/exec>1: |
79.99 |
73.52 |
Memory Usage %:對於一個已經執行一段時間的資料庫來說,共享池記憶體使用率,應該穩定在75%-90%間,如果太小,說明Shared Pool有浪費,而如果高於90,說明共享池中有爭用,記憶體不足。
SQL with executions>1:執行次數大於1的sql比率,如果此值太小,說明需要在應用中更多使用繫結變數,避免過多SQL解析。
Memory for SQL w/exec>1:執行次數大於1的SQL消耗記憶體的佔比。
Top 5 Timed Events
Event |
Waits |
Time(s) |
Avg Wait(ms) |
% Total Call Time |
Wait Class |
CPU time |
|
515 |
|
77.6 |
|
SQL*Net more data from client |
27,319 |
64 |
2 |
9.7 |
Network |
log file parallel write |
5,497 |
47 |
9 |
7.1 |
System I/O |
db file sequential read |
7,900 |
35 |
4 |
5.3 |
User I/O |
db file parallel write |
4,806 |
34 |
7 |
5.1 |
System I/O |
這是報告概要的最後一節,顯示了系統中最嚴重的5個等待,按所佔等待時間的比例倒序列示。當我們調優時,總希望觀察到最顯著的效果,因此應當從這裡入手確定我們下一步做什麼。例如如果‘buffer busy wait’是較嚴重的等待事件,我們應當繼續研究報告中Buffer Wait和File/Tablespace IO區的內容,識別哪些檔案導致了問題。如果最嚴重的等待事件是I/O事件,我們應當研究按物理讀排序的SQL語句區以識別哪些語句在執行大量I/O,並研究Tablespace和I/O區觀察較慢響應時間的檔案。如果有較高的LATCH等待,就需要察看詳細的LATCH統計識別哪些LATCH產生的問題。
在這裡,log file parallel write是相對比較多的等待,佔用了7%的CPU時間。
通常,在沒有問題的資料庫中,CPU time總是列在第一個。
更多的等待事件,參見本報告 的Wait Events一節。
RAC Statistics
|
Begin |
End |
Number of Instances: |
2 |
2 |
Global Cache Load Profile
|
Per Second |
Per Transaction |
Global Cache blocks received: |
4.16 |
3.51 |
Global Cache blocks served: |
5.97 |
5.04 |
GCS/GES messages received: |
408.47 |
344.95 |
GCS/GES messages sent: |
258.03 |
217.90 |
DBWR Fusion writes: |
0.05 |
0.05 |
Estd Interconnect traffic (KB) |
211.16 |
|
Global Cache Efficiency Percentages (Target local+remote 100%)
Buffer access - local cache %: |
98.60 |
Buffer access - remote cache %: |
0.12 |
Buffer access - disk %: |
1.28 |
Global Cache and Enqueue Services - Workload Characteristics
Avg global enqueue get time (ms): |
0.1 |
Avg global cache cr block receive time (ms): |
1.1 |
Avg global cache current block receive time (ms): |
0.8 |
Avg global cache cr block build time (ms): |
0.0 |
Avg global cache cr block send time (ms): |
0.0 |
Global cache log flushes for cr blocks served %: |
3.5 |
Avg global cache cr block flush time (ms): |
3.9 |
Avg global cache current block pin time (ms): |
0.0 |
Avg global cache current block send time (ms): |
0.0 |
Global cache log flushes for current blocks served %: |
0.4 |
Avg global cache current block flush time (ms): |
3.0 |
Global Cache and Enqueue Services - Messaging Statistics
Avg message sent queue time (ms): |
0.0 |
Avg message sent queue time on ksxp (ms): |
0.3 |
Avg message received queue time (ms): |
0.5 |
Avg GCS message process time (ms): |
0.0 |
Avg GES message process time (ms): |
0.0 |
% of direct sent messages: |
14.40 |
% of indirect sent messages: |
77.04 |
% of flow controlled messages: |
8.56 |
Main Report
· Instance Activity Statistics
· IO Stats
Wait Events Statistics
Time Model Statistics
· Total time in database user-calls (DB Time): 663s
· Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
· Ordered by % or DB time desc, Statistic name
Statistic Name |
Time (s) |
% of DB Time |
DB CPU |
514.50 |
77.61 |
sql execute elapsed time |
482.27 |
72.74 |
parse time elapsed |
3.76 |
0.57 |
PL/SQL execution elapsed time |
0.50 |
0.08 |
hard parse elapsed time |
0.34 |
0.05 |
connection management call elapsed time |
0.08 |
0.01 |
hard parse (sharing criteria) elapsed time |
0.00 |
0.00 |
repeated bind elapsed time |
0.00 |
0.00 |
PL/SQL compilation elapsed time |
0.00 |
0.00 |
failed parse elapsed time |
0.00 |
0.00 |
DB time |
662.97 |
|
background elapsed time |
185.19 |
|
background cpu time |
67.48 |
|
此節顯示了各種型別的資料庫處理任務所佔用的CPU時間。
Back to Wait Events Statistics
Wait Class
· 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
Wait Class |
Waits |
%Time -outs |
Total Wait Time (s) |
Avg wait (ms) |
Waits /txn |
User I/O |
66,837 |
0.00 |
120 |
2 |
11.94 |
System I/O |
28,295 |
0.00 |
93 |
3 |
5.05 |
Network |
1,571,450 |
0.00 |
66 |
0 |
280.72 |
Cluster |
210,548 |
0.00 |
29 |
0 |
37.61 |
Other |
81,783 |
71.82 |
28 |
0 |
14.61 |
Application |
333,155 |
0.00 |
16 |
0 |
59.51 |
Concurrency |
5,182 |
0.04 |
5 |
1 |
0.93 |
Commit |
919 |
0.00 |
4 |
4 |
0.16 |
Configuration |
25,427 |
99.46 |
1 |
0 |
4.54 |
Back to Wait Events Statistics
Wait Events
· 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)
Event |
Waits |
%Time -outs |
Total Wait Time (s) |
Avg wait (ms) |
Waits /txn |
SQL*Net more data from client |
27,319 |
0.00 |
64 |
2 |
4.88 |
log file parallel write |
5,497 |
0.00 |
47 |
9 |
0.98 |
db file sequential read |
7,900 |
0.00 |
35 |
4 |
1.41 |
db file parallel write |
4,806 |
0.00 |
34 |
7 |
0.86 |
db file scattered read |
10,310 |
0.00 |
31 |
3 |
1.84 |
direct path write |
42,724 |
0.00 |
30 |
1 |
7.63 |
reliable message |
355 |
2.82 |
18 |
49 |
0.06 |
SQL*Net break/reset to client |
333,084 |
0.00 |
16 |
0 |
59.50 |
db file parallel read |
3,732 |
0.00 |
13 |
4 |
0.67 |
gc current multi block request |
175,710 |
0.00 |
10 |
0 |
31.39 |
control file sequential read |
15,974 |
0.00 |
10 |
1 |
2.85 |
direct path read temp |
1,873 |
0.00 |
9 |
5 |
0.33 |
gc cr multi block request |
20,877 |
0.00 |
8 |
0 |
3.73 |
log file sync |
919 |
0.00 |
4 |
4 |
0.16 |
gc cr block busy |
526 |
0.00 |
3 |
6 |
0.09 |
enq: FB - contention |
10,384 |
0.00 |
3 |
0 |
1.85 |
DFS lock handle |
3,517 |
0.00 |
3 |
1 |
0.63 |
control file parallel write |
1,946 |
0.00 |
3 |
1 |
0.35 |
gc current block 2-way |
4,165 |
0.00 |
2 |
0 |
0.74 |
library cache lock |
432 |
0.00 |
2 |
4 |
0.08 |
name-service call wait |
22 |
0.00 |
2 |
76 |
0.00 |
row cache lock |
3,894 |
0.00 |
2 |
0 |
0.70 |
gcs log flush sync |
1,259 |
42.02 |
2 |
1 |
0.22 |
os thread startup |
18 |
5.56 |
2 |
89 |
0.00 |
gc cr block 2-way |
3,671 |
0.00 |
2 |
0 |
0.66 |
gc current block busy |
113 |
0.00 |
1 |
12 |
0.02 |
SQL*Net message to client |
1,544,115 |
0.00 |
1 |
0 |
275.83 |
gc buffer busy |
15 |
6.67 |
1 |
70 |
0.00 |
gc cr disk read |
3,272 |
0.00 |
1 |
0 |
0.58 |
direct path write temp |
159 |
0.00 |
1 |
5 |
0.03 |
gc current grant busy |
898 |
0.00 |
1 |
1 |
0.16 |
log file switch completion |
29 |
0.00 |
1 |
17 |
0.01 |
CGS wait for IPC msg |
48,739 |
99.87 |
0 |
0 |
8.71 |
gc current grant 2-way |
1,142 |
0.00 |
0 |
0 |
0.20 |
kjbdrmcvtq lmon drm quiesce: ping completion |
9 |
0.00 |
0 |
19 |
0.00 |
enq: US - contention |
567 |
0.00 |
0 |
0 |
0.10 |
direct path read |
138 |
0.00 |
0 |
1 |
0.02 |
enq: WF - contention |
14 |
0.00 |
0 |
9 |
0.00 |
ksxr poll remote instances |
13,291 |
58.45 |
0 |
0 |
2.37 |
library cache pin |
211 |
0.00 |
0 |
1 |
0.04 |
ges global resource directory to be frozen |
9 |
100.00 |
0 |
10 |
0.00 |
wait for scn ack |
583 |
0.00 |
0 |
0 |
0.10 |
log file sequential read |
36 |
0.00 |
0 |
2 |
0.01 |
undo segment extension |
25,342 |
99.79 |
0 |
0 |
4.53 |
rdbms ipc reply |
279 |
0.00 |
0 |
0 |
0.05 |
ktfbtgex |
6 |
100.00 |
0 |
10 |
0.00 |
enq: HW - contention |
44 |
0.00 |
0 |
1 |
0.01 |
gc cr grant 2-way |
158 |
0.00 |
0 |
0 |
0.03 |
enq: TX - index contention |
1 |
0.00 |
0 |
34 |
0.00 |
enq: CF - contention |
64 |
0.00 |
0 |
1 |
0.01 |
PX Deq: Signal ACK |
37 |
21.62 |
0 |
1 |
0.01 |
latch free |
3 |
0.00 |
0 |
10 |
0.00 |
buffer busy waits |
625 |
0.16 |
0 |
0 |
0.11 |
KJC: Wait for msg sends to complete |
154 |
0.00 |
0 |
0 |
0.03 |
log buffer space |
11 |
0.00 |
0 |
2 |
0.00 |
enq: PS - contention |
46 |
0.00 |
0 |
1 |
0.01 |
enq: TM - contention |
70 |
0.00 |
0 |
0 |
0.01 |
IPC send completion sync |
40 |
100.00 |
0 |
0 |
0.01 |
PX Deq: reap credit |
1,544 |
99.81 |
0 |
0 |
0.28 |
log file single write |
36 |
0.00 |
0 |
0 |
0.01 |
enq: TT - contention |
46 |
0.00 |
0 |
0 |
0.01 |
enq: TD - KTF dump entries |
12 |
0.00 |
0 |
1 |
0.00 |
read by other session |
1 |
0.00 |
0 |
12 |
0.00 |
LGWR wait for redo copy |
540 |
0.00 |
0 |
0 |
0.10 |
PX Deq Credit: send blkd |
17 |
5.88 |
0 |
0 |
0.00 |
enq: TA - contention |
14 |
0.00 |
0 |
0 |
0.00 |
latch: ges resource hash list |
44 |
0.00 |
0 |
0 |
0.01 |
enq: PI - contention |
8 |
0.00 |
0 |
0 |
0.00 |
write complete waits |
1 |
0.00 |
0 |
2 |
0.00 |
enq: DR - contention |
3 |
0.00 |
0 |
0 |
0.00 |
enq: MW - contention |
3 |
0.00 |
0 |
0 |
0.00 |
enq: TS - contention |
3 |
0.00 |
0 |
0 |
0.00 |
PX qref latch |
150 |
100.00 |
0 |
0 |
0.03 |
enq: MD - contention |
2 |
0.00 |
0 |
0 |
0.00 |
latch: KCL gc element parent latch |
11 |
0.00 |
0 |
0 |
0.00 |
enq: JS - job run lock - synchronize |
1 |
0.00 |
0 |
1 |
0.00 |
SQL*Net more data to client |
16 |
0.00 |
0 |
0 |
0.00 |
latch: cache buffers lru chain |
1 |
0.00 |
0 |
0 |
0.00 |
enq: UL - contention |
1 |
0.00 |
0 |
0 |
0.00 |
gc current split |
1 |
0.00 |
0 |
0 |
0.00 |
enq: AF - task serialization |
1 |
0.00 |
0 |
0 |
0.00 |
latch: object queue header operation |
3 |
0.00 |
0 |
0 |
0.00 |
latch: cache buffers chains |
1 |
0.00 |
0 |
0 |
0.00 |
latch: enqueue hash chains |
2 |
0.00 |
0 |
0 |
0.00 |
SQL*Net message from client |
1,544,113 |
0.00 |
12,626 |
8 |
275.83 |
gcs remote message |
634,884 |
98.64 |
9,203 |
14 |
113.41 |
DIAG idle wait |
23,628 |
0.00 |
4,616 |
195 |
4.22 |
ges remote message |
149,591 |
93.45 |
4,612 |
31 |
26.72 |
Streams AQ: qmn slave idle wait |
167 |
0.00 |
4,611 |
27611 |
0.03 |
Streams AQ: qmn coordinator idle wait |
351 |
47.86 |
4,611 |
13137 |
0.06 |
Streams AQ: waiting for messages in the queue |
488 |
100.00 |
4,605 |
9436 |
0.09 |
virtual circuit status |
157 |
100.00 |
4,596 |
29272 |
0.03 |
PX Idle Wait |
1,072 |
97.11 |
2,581 |
2407 |
0.19 |
jobq slave wait |
145 |
97.93 |
420 |
2896 |
0.03 |
Streams AQ: waiting for time management or cleanup tasks |
1 |
100.00 |
270 |
269747 |
0.00 |
PX Deq: Parse Reply |
40 |
40.00 |
0 |
3 |
0.01 |
PX Deq: Execution Msg |
121 |
26.45 |
0 |
0 |
0.02 |
PX Deq: Join ACK |
38 |
42.11 |
0 |
1 |
0.01 |
PX Deq: Execute Reply |
34 |
32.35 |
0 |
0 |
0.01 |
PX Deq: Msg Fragment |
16 |
0.00 |
0 |
0 |
0.00 |
Streams AQ: RAC qmn coordinator idle wait |
351 |
100.00 |
0 |
0 |
0.06 |
class slave wait |
2 |
0.00 |
0 |
0 |
0.00 |
db file scattered read等待事件是當SESSION等待multi-block I/O時發生的,透過是由於full table scans或index fast full scans。發生過多讀操作的Segments可以在“Segments by Physical Reads”和 “SQL ordered by Reads”節中識別(在其它版本的報告中,可能是別的名稱)。如果在OLTP應用中,不應該有過多的全掃描操作,而應使用選擇性好的索引操作。
DB file sequential read等待意味著發生順序I/O讀等待(通常是單塊讀取到連續的記憶體區域中),如果這個等待非常嚴重,應該使用上一段的方法確定執行讀操作的熱點SEGMENT,然後透過對大表進行分割槽以減少I/O量,或者最佳化執行計劃(透過使用儲存大綱或執行資料分析)以避免單塊讀操作引起的sequential read等待。透過在批次應用中,DB file sequential read是很影響效能的事件,總是應當設法避免。
Log File Parallel Write事件是在等待LGWR程式將REDO記錄從LOG 緩衝區寫到聯機日誌檔案時發生的。雖然寫操作可能是併發的,但LGWR需要等待最後的I/O寫到磁碟上才能認為並行寫的完成,因此等待時間依賴於OS完成所有請求的時間。如果這個等待比較嚴重,可以透過將LOG檔案移到更快的磁碟上或者條帶化磁碟(減少爭用)而降低這個等待。
Buffer Busy Waits事件是在一個SESSION需要訪問BUFFER CACHE中的一個資料庫塊而又不能訪問時發生的。緩衝區“busy”的兩個原因是:1)另一個SESSION正在將資料塊讀進BUFFER。2)另一個SESSION正在以排它模式佔用著這塊被請求的BUFFER。可以在“Segments by Buffer Busy Waits”一節中找出發生這種等待的SEGMENT,然後透過使用reverse-key indexes並對熱表進行分割槽而減少這種等待事件。
Log File Sync事件,當使用者SESSION執行事務操作(COMMIT或ROLLBACK等)後,會通知 LGWR程式將所需要的所有REDO資訊從LOG BUFFER寫到LOG檔案,在使用者SESSION等待LGWR返回安全寫入磁碟的通知時發生此等待。減少此等待的方法寫Log File Parallel Write事件的處理。
Enqueue Waits是序列訪問本地資源的本鎖,表明正在等待一個被其它SESSION(一個或多個)以排它模式鎖住的資源。減少這種等待的方法依賴於生產等待的鎖型別。導致Enqueue等待的主要鎖型別有三種:TX(事務鎖), TMD(ML鎖)和ST(空間管理鎖)。
Back to Wait Events Statistics
Background Wait Events
· ordered by wait time desc, waits desc (idle events last)
Event |
Waits |
%Time -outs |
Total Wait Time (s) |
Avg wait (ms) |
Waits /txn |
log file parallel write |
5,497 |
0.00 |
47 |
9 |
0.98 |
db file parallel write |
4,806 |
0.00 |
34 |
7 |
0.86 |
events in waitclass Other |
69,002 |
83.25 |
22 |
0 |
12.33 |
control file sequential read |
9,323 |
0.00 |
7 |
1 |
1.67 |
control file parallel write |
1,946 |
0.00 |
3 |
1 |
0.35 |
os thread startup |
18 |
5.56 |
2 |
89 |
0.00 |
direct path read |
138 |
0.00 |
0 |
1 |
0.02 |
db file sequential read |
21 |
0.00 |
0 |
5 |
0.00 |
direct path write |
138 |
0.00 |
0 |
0 |
0.02 |
log file sequential read |
36 |
0.00 |
0 |
2 |
0.01 |
gc cr block 2-way |
96 |
0.00 |
0 |
0 |
0.02 |
gc current block 2-way |
78 |
0.00 |
0 |
0 |
0.01 |
log buffer space |
11 |
0.00 |
0 |
2 |
0.00 |
row cache lock |
59 |
0.00 |
0 |
0 |
0.01 |
log file single write |
36 |
0.00 |
0 |
0 |
0.01 |
buffer busy waits |
151 |
0.66 |
0 |
0 |
0.03 |
gc current grant busy |
29 |
0.00 |
0 |
0 |
0.01 |
library cache lock |
4 |
0.00 |
0 |
1 |
0.00 |
enq: TM - contention |
10 |
0.00 |
0 |
0 |
0.00 |
gc current grant 2-way |
8 |
0.00 |
0 |
0 |
0.00 |
gc cr multi block request |
7 |
0.00 |
0 |
0 |
0.00 |
gc cr grant 2-way |
5 |
0.00 |
0 |
0 |
0.00 |
rdbms ipc message |
97,288 |
73.77 |
50,194 |
516 |
17.38 |
gcs remote message |
634,886 |
98.64 |
9,203 |
14 |
113.41 |
DIAG idle wait |
23,628 |
0.00 |
4,616 |
195 |
4.22 |
pmon timer |
1,621 |
100.00 |
4,615 |
2847 |
0.29 |
ges remote message |
149,591 |
93.45 |
4,612 |
31 |
26.72 |
Streams AQ: qmn slave idle wait |
167 |
0.00 |
4,611 |
27611 |
0.03 |
Streams AQ: qmn coordinator idle wait |
351 |
47.86 |
4,611 |
13137 |
0.06 |
smon timer |
277 |
6.50 |
4,531 |
16356 |
0.05 |
Streams AQ: waiting for time management or cleanup tasks |
1 |
100.00 |
270 |
269747 |
0.00 |
PX Deq: Parse Reply |
40 |
40.00 |
0 |
3 |
0.01 |
PX Deq: Join ACK |
38 |
42.11 |
0 |
1 |
0.01 |
PX Deq: Execute Reply |
34 |
32.35 |
0 |
0 |
0.01 |
Streams AQ: RAC qmn coordinator idle wait |
351 |
100.00 |
0 |
0 |
0.06 |
Back to Wait Events Statistics
Operating System Statistics
Statistic |
Total |
NUM_LCPUS |
0 |
NUM_VCPUS |
0 |
AVG_BUSY_TIME |
101,442 |
AVG_IDLE_TIME |
371,241 |
AVG_IOWAIT_TIME |
5,460 |
AVG_SYS_TIME |
25,795 |
AVG_USER_TIME |
75,510 |
BUSY_TIME |
812,644 |
IDLE_TIME |
2,971,077 |
IOWAIT_TIME |
44,794 |
SYS_TIME |
207,429 |
USER_TIME |
605,215 |
LOAD |
0 |
OS_CPU_WAIT_TIME |
854,100 |
RSRC_MGR_CPU_WAIT_TIME |
0 |
PHYSICAL_MEMORY_BYTES |
8,589,934,592 |
NUM_CPUS |
8 |
NUM_CPU_CORES |
4 |
NUM_LCPUS: 如果顯示0,是因為沒有設定LPARS
NUM_VCPUS: 同上。
AVG_BUSY_TIME: BUSY_TIME / NUM_CPUS
AVG_IDLE_TIME: IDLE_TIME / NUM_CPUS
AVG_IOWAIT_TIME: IOWAIT_TIME / NUM_CPUS
AVG_SYS_TIME: SYS_TIME / NUM_CPUS
AVG_USER_TIME: USER_TIME / NUM_CPUSar o
BUSY_TIME: time equiv of %usr+%sys in sar output
IDLE_TIME: time equiv of %idle in sar
IOWAIT_TIME: time equiv of %wio in sar
SYS_TIME: time equiv of %sys in sar
USER_TIME: time equiv of %usr in sar
LOAD: 未知
OS_CPU_WAIT_TIME: supposedly time waiting on run queues
RSRC_MGR_CPU_WAIT_TIME: time waited coz of resource manager
PHYSICAL_MEMORY_BYTES: total memory in use supposedly
NUM_CPUS: number of CPUs reported by OS
NUM_CPU_CORES: number of CPU sockets on motherboard
總的elapsed time也可以用以公式計算:
BUSY_TIME + IDLE_TIME + IOWAIT TIME
或:SYS_TIME + USER_TIME + IDLE_TIME + IOWAIT_TIME
(因為BUSY_TIME = SYS_TIME+USER_TIME)
Back to Wait Events Statistics
Service Statistics
· ordered by DB Time
Service Name |
DB Time (s) |
DB CPU (s) |
Physical Reads |
Logical Reads |
ICCI |
608.10 |
496.60 |
315,849 |
16,550,972 |
SYS$USERS |
54.70 |
17.80 |
6,539 |
58,929 |
ICCIXDB |
0.00 |
0.00 |
0 |
0 |
SYS$BACKGROUND |
0.00 |
0.00 |
282 |
38,990 |
Back to Wait Events Statistics
Service Wait Class Stats
· Wait Class info for services in the Service Statistics section.
· Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network
· Time Waited (Wt Time) in centisecond (100th of a second)
Service Name |
User I/O Total Wts |
User I/O Wt Time |
Concurcy Total Wts |
Concurcy Wt Time |
Admin Total Wts |
Admin Wt Time |
Network Total Wts |
Network Wt Time |
ICCI |
59826 |
8640 |
4621 |
338 |
0 |
0 |
1564059 |
6552 |
SYS$USERS |
6567 |
3238 |
231 |
11 |
0 |
0 |
7323 |
3 |
SYS$BACKGROUND |
443 |
115 |
330 |
168 |
0 |
0 |
0 |
0 |
Back to Wait Events Statistics
SQL Statistics
· SQL ordered by Sharable Memory
· SQL ordered by Version Count
· SQL ordered by Cluster Wait Time
本節按各種資源分別列出對資源消耗最嚴重的SQL語句,並顯示它們所佔統計期內全部資源的比例,這給出我們調優指南。例如在一個系統中,CPU資源是系統效能瓶頸所在,那麼最佳化buffer gets最多的SQL語句將獲得最大效果。在一個I/O等待是最嚴重事件的系統中,調優的目標應該是physical IOs最多的SQL語句。
在STATSPACK報告中,沒有完整的SQL語句,可使用報告中的Hash Value透過下面語句從資料庫中查到:
select sql_text
from stats$sqltext
where hash_value = &hash_value
order by piece;
SQL ordered by Elapsed Time
· Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
· % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
Elapsed Time (s) |
CPU Time (s) |
Executions |
Elap per Exec (s) |
% Total DB Time |
SQL Id |
SQL Module |
SQL Text |
93 |
57 |
1 |
93.50 |
14.10 |
cuidmain@HPGICCI1 (TNS V1-V3) |
insert into CUID select CUID_... |
|
76 |
75 |
172,329 |
0.00 |
11.52 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
insert into ICCICCS values (:... |
|
58 |
42 |
1 |
58.04 |
8.75 |
cumimain@HPGICCI1 (TNS V1-V3) |
insert into CUMI select CUSV_... |
|
51 |
42 |
1 |
50.93 |
7.68 |
cusmmain@HPGICCI1 (TNS V1-V3) |
insert into CUSM select CUSM_... |
|
38 |
36 |
166,069 |
0.00 |
5.67 |
|
select c.name, u.name from co... |
|
35 |
3 |
1 |
35.00 |
5.28 |
SQL*Plus |
SELECT F.TABLESPACE_NAME, TO_... |
|
23 |
23 |
172,329 |
0.00 |
3.46 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
insert into iccifnsact values... |
|
15 |
11 |
5 |
2.98 |
2.25 |
|
DECLARE job BINARY_INTEGER := ... |
|
14 |
14 |
172,983 |
0.00 |
2.16 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
update ICCIFNSACT set BORM_AD... |
|
13 |
13 |
172,337 |
0.00 |
2.00 |
load_oldnewact@HPGICCI1 (TNS V1-V3) |
insert into OLDNEWACT values ... |
|
13 |
13 |
166,051 |
0.00 |
1.89 |
icci_migact@HPGICCI1 (TNS V1-V3) |
insert into ICCICCS values (:... |
|
10 |
4 |
1 |
9.70 |
1.46 |
cuidmain@HPGICCI1 (TNS V1-V3) |
select CUID_CUST_NO , CUID_ID_... |
|
10 |
8 |
5 |
1.91 |
1.44 |
|
INSERT INTO STATS$SGA_TARGET_A... |
|
8 |
8 |
172,329 |
0.00 |
1.25 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
update ICCICCS set CCSMAXOVER... |
|
8 |
8 |
172,983 |
0.00 |
1.16 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
select * from ICCIPRODCODE wh... |
SQL ordered by CPU Time
· Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
· % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
CPU Time (s) |
Elapsed Time (s) |
Executions |
CPU per Exec (s) |
% Total DB Time |
SQL Id |
SQL Module |
SQL Text |
75 |
76 |
172,329 |
0.00 |
11.52 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
insert into ICCICCS values (:... |
|
57 |
93 |
1 |
57.31 |
14.10 |
cuidmain@HPGICCI1 (TNS V1-V3) |
insert into CUID select CUID_... |
|
42 |
51 |
1 |
42.43 |
7.68 |
cusmmain@HPGICCI1 (TNS V1-V3) |
insert into CUSM select CUSM_... |
|
42 |
58 |
1 |
42.01 |
8.75 |
cumimain@HPGICCI1 (TNS V1-V3) |
insert into CUMI select CUSV_... |
|
36 |
38 |
166,069 |
0.00 |
5.67 |
|
select c.name, u.name from co... |
|
23 |
23 |
172,329 |
0.00 |
3.46 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
insert into iccifnsact values... |
|
14 |
14 |
172,983 |
0.00 |
2.16 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
update ICCIFNSACT set BORM_AD... |
|
13 |
13 |
172,337 |
0.00 |
2.00 |
load_oldnewact@HPGICCI1 (TNS V1-V3) |
insert into OLDNEWACT values ... |
|
13 |
13 |
166,051 |
0.00 |
1.89 |
icci_migact@HPGICCI1 (TNS V1-V3) |
insert into ICCICCS values (:... |
|
11 |
15 |
5 |
2.23 |
2.25 |
|
DECLARE job BINARY_INTEGER := ... |
|
8 |
8 |
172,329 |
0.00 |
1.25 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
update ICCICCS set CCSMAXOVER... |
|
8 |
10 |
5 |
1.60 |
1.44 |
|
INSERT INTO STATS$SGA_TARGET_A... |
|
8 |
8 |
172,983 |
0.00 |
1.16 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
select * from ICCIPRODCODE wh... |
|
4 |
10 |
1 |
3.54 |
1.46 |
cuidmain@HPGICCI1 (TNS V1-V3) |
select CUID_CUST_NO , CUID_ID_... |
|
3 |
35 |
1 |
3.13 |
5.28 |
SQL*Plus |
SELECT F.TABLESPACE_NAME, TO_... |
SQL ordered by Gets
· Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
· Total Buffer Gets: 16,648,792
· Captured SQL account for 97.9% of Total
Buffer Gets |
Executions |
Gets per Exec |
%Total |
CPU Time (s) |
Elapsed Time (s) |
SQL Id |
SQL Module |
SQL Text |
3,305,363 |
172,329 |
19.18 |
19.85 |
74.57 |
76.41 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
insert into ICCICCS values (:... |
|
2,064,414 |
1 |
2,064,414.00 |
12.40 |
57.31 |
93.50 |
cuidmain@HPGICCI1 (TNS V1-V3) |
insert into CUID select CUID_... |
|
1,826,869 |
166,069 |
11.00 |
10.97 |
35.84 |
37.60 |
|
select c.name, u.name from co... |
|
1,427,648 |
172,337 |
8.28 |
8.58 |
12.97 |
13.29 |
load_oldnewact@HPGICCI1 (TNS V1-V3) |
insert into OLDNEWACT values ... |
|
1,278,667 |
172,329 |
7.42 |
7.68 |
22.85 |
22.94 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
insert into iccifnsact values... |
|
1,216,367 |
1 |
1,216,367.00 |
7.31 |
42.43 |
50.93 |
cusmmain@HPGICCI1 (TNS V1-V3) |
insert into CUSM select CUSM_... |
|
1,107,305 |
1 |
1,107,305.00 |
6.65 |
42.01 |
58.04 |
cumimain@HPGICCI1 (TNS V1-V3) |
insert into CUMI select CUSV_... |
|
898,868 |
172,983 |
5.20 |
5.40 |
14.28 |
14.34 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
update ICCIFNSACT set BORM_AD... |
|
711,450 |
166,051 |
4.28 |
4.27 |
12.52 |
12.55 |
icci_migact@HPGICCI1 (TNS V1-V3) |
insert into ICCICCS values (:... |
|
692,996 |
172,329 |
4.02 |
4.16 |
8.31 |
8.31 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
update ICCICCS set CCSMAXOVER... |
|
666,748 |
166,052 |
4.02 |
4.00 |
6.36 |
6.36 |
icci_migact@HPGICCI1 (TNS V1-V3) |
select NEWACTNO into :b0 from... |
|
345,357 |
172,983 |
2.00 |
2.07 |
7.70 |
7.71 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
select * from ICCIPRODCODE wh... |
|
231,756 |
51,633 |
4.49 |
1.39 |
5.75 |
5.83 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
insert into ICCIRPYV values (... |
SQL ordered by Reads
· Total Disk Reads: 322,678
· Captured SQL account for 66.1% of Total
Physical Reads |
Executions |
Reads per Exec |
%Total |
CPU Time (s) |
Elapsed Time (s) |
SQL Id |
SQL Module |
SQL Text |
66,286 |
1 |
66,286.00 |
20.54 |
57.31 |
93.50 |
cuidmain@HPGICCI1 (TNS V1-V3) |
insert into CUID select CUID_... |
|
50,646 |
1 |
50,646.00 |
15.70 |
3.54 |
9.70 |
cuidmain@HPGICCI1 (TNS V1-V3) |
select CUID_CUST_NO , CUID_ID_... |
|
24,507 |
1 |
24,507.00 |
7.59 |
42.01 |
58.04 |
cumimain@HPGICCI1 (TNS V1-V3) |
insert into CUMI select CUSV_... |
|
21,893 |
1 |
21,893.00 |
6.78 |
42.43 |
50.93 |
cusmmain@HPGICCI1 (TNS V1-V3) |
insert into CUSM select CUSM_... |
|
19,761 |
1 |
19,761.00 |
6.12 |
2.14 |
6.04 |
cumimain@HPGICCI1 (TNS V1-V3) |
select CUSV_CUST_NO from CUMI... |
|
19,554 |
1 |
19,554.00 |
6.06 |
1.27 |
3.83 |
SQL*Plus |
select count(*) from CUSVAA_T... |
|
6,342 |
1 |
6,342.00 |
1.97 |
3.13 |
35.00 |
SQL*Plus |
SELECT F.TABLESPACE_NAME, TO_... |
|
4,385 |
1 |
4,385.00 |
1.36 |
1.59 |
2.43 |
cusmmain@HPGICCI1 (TNS V1-V3) |
select CUSM_CUST_ACCT_NO from... |
|
63 |
5 |
12.60 |
0.02 |
11.17 |
14.91 |
|
DECLARE job BINARY_INTEGER := ... |
|
35 |
1 |
35.00 |
0.01 |
0.08 |
0.67 |
SQL*Plus |
BEGIN dbms_workload_repository... |
SQL ordered by Executions
· Total Executions: 1,675,112
· Captured SQL account for 99.8% of Total
Executions |
Rows Processed |
Rows per Exec |
CPU per Exec (s) |
Elap per Exec (s) |
SQL Id |
SQL Module |
SQL Text |
172,983 |
172,329 |
1.00 |
0.00 |
0.00 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
select * from ICCIPRODCODE wh... |
|
172,983 |
172,329 |
1.00 |
0.00 |
0.00 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
update ICCIFNSACT set BORM_AD... |
|
172,337 |
172,337 |
1.00 |
0.00 |
0.00 |
load_oldnewact@HPGICCI1 (TNS V1-V3) |
insert into OLDNEWACT values ... |
|
172,329 |
172,329 |
1.00 |
0.00 |
0.00 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
insert into iccifnsact values... |
|
172,329 |
172,329 |
1.00 |
0.00 |
0.00 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
update ICCICCS set CCSMAXOVER... |
|
172,329 |
6,286 |
0.04 |
0.00 |
0.00 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
insert into ICCICCS values (:... |
|
166,069 |
166,069 |
1.00 |
0.00 |
0.00 |
|
select c.name, u.name from co... |
|
166,052 |
166,052 |
1.00 |
0.00 |
0.00 |
icci_migact@HPGICCI1 (TNS V1-V3) |
select NEWACTNO into :b0 from... |
|
166,051 |
166,051 |
1.00 |
0.00 |
0.00 |
icci_migact@HPGICCI1 (TNS V1-V3) |
insert into ICCICCS values (:... |
|
51,740 |
51,740 |
1.00 |
0.00 |
0.00 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
select count(*) into :b0 fro... |
|
51,633 |
51,633 |
1.00 |
0.00 |
0.00 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
insert into ICCIRPYV values (... |
SQL ordered by Parse Calls
· Total Parse Calls: 182,780
· Captured SQL account for 99.0% of Total
Parse Calls |
Executions |
% Total Parses |
SQL Id |
SQL Module |
SQL Text |
166,069 |
166,069 |
90.86 |
|
select c.name, u.name from co... |
|
6,304 |
6,304 |
3.45 |
|
select type#, blocks, extents,... |
|
2,437 |
2,438 |
1.33 |
|
select file# from file$ where ... |
|
1,568 |
1,568 |
0.86 |
|
update seg$ set type#=:4, bloc... |
|
1,554 |
1,554 |
0.85 |
|
update tsq$ set blocks=:3, max... |
|
444 |
444 |
0.24 |
|
select blocks, maxblocks, gran... |
|
421 |
421 |
0.23 |
|
lock table sys.mon_mods$ in ex... |
|
421 |
421 |
0.23 |
|
update sys.mon_mods$ set inser... |
|
86 |
86 |
0.05 |
|
INSERT INTO sys.wri$_adv_messa... |
|
81 |
81 |
0.04 |
|
SELECT sys.wri$_adv_seq_msggro... |
SQL ordered by Sharable Memory
No data exists for this section of the report.
SQL ordered by Version Count
No data exists for this section of the report.
SQL ordered by Cluster Wait Time
Cluster Wait Time (s) |
CWT % of Elapsd Time |
Elapsed Time(s) |
CPU Time(s) |
Executions |
SQL Id |
SQL Module |
SQL Text |
10.96 |
11.72 |
93.50 |
57.31 |
1 |
cuidmain@HPGICCI1 (TNS V1-V3) |
insert into CUID select CUID_... |
|
4.21 |
7.25 |
58.04 |
42.01 |
1 |
cumimain@HPGICCI1 (TNS V1-V3) |
insert into CUMI select CUSV_... |
|
3.62 |
7.12 |
50.93 |
42.43 |
1 |
cusmmain@HPGICCI1 (TNS V1-V3) |
insert into CUSM select CUSM_... |
|
2.39 |
6.35 |
37.60 |
35.84 |
166,069 |
|
select c.name, u.name from co... |
|
2.38 |
3.12 |
76.41 |
74.57 |
172,329 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
insert into ICCICCS values (:... |
|
1.64 |
16.91 |
9.70 |
3.54 |
1 |
cuidmain@HPGICCI1 (TNS V1-V3) |
select CUID_CUST_NO , CUID_ID_... |
|
1.06 |
3.02 |
35.00 |
3.13 |
1 |
SQL*Plus |
SELECT F.TABLESPACE_NAME, TO_... |
|
0.83 |
13.76 |
6.04 |
2.14 |
1 |
cumimain@HPGICCI1 (TNS V1-V3) |
select CUSV_CUST_NO from CUMI... |
|
0.66 |
87.90 |
0.75 |
0.42 |
444 |
|
select blocks, maxblocks, gran... |
|
0.50 |
13.01 |
3.83 |
1.27 |
1 |
SQL*Plus |
select count(*) from CUSVAA_T... |
|
0.50 |
51.75 |
0.96 |
0.79 |
1,554 |
|
update tsq$ set blocks=:3, max... |
|
0.33 |
91.11 |
0.36 |
0.33 |
187 |
|
select obj#, type#, ctime, mti... |
|
0.33 |
2.47 |
13.29 |
12.97 |
172,337 |
load_oldnewact@HPGICCI1 (TNS V1-V3) |
insert into OLDNEWACT values ... |
|
0.29 |
1.26 |
22.94 |
22.85 |
172,329 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
insert into iccifnsact values... |
|
0.25 |
10.14 |
2.43 |
1.59 |
1 |
cusmmain@HPGICCI1 (TNS V1-V3) |
select CUSM_CUST_ACCT_NO from... |
|
0.21 |
27.92 |
0.74 |
0.74 |
1,568 |
|
update seg$ set type#=:4, bloc... |
|
0.20 |
3.49 |
5.83 |
5.75 |
51,633 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
insert into ICCIRPYV values (... |
|
0.17 |
1.39 |
12.55 |
12.52 |
166,051 |
icci_migact@HPGICCI1 (TNS V1-V3) |
insert into ICCICCS values (:... |
|
0.16 |
57.64 |
0.28 |
0.24 |
39 |
cusvaamain@HPGICCI1 (TNS V1-V3) |
BEGIN BEGIN IF (xdb.DBMS... |
|
0.14 |
74.58 |
0.19 |
0.14 |
121 |
|
select o.owner#, o.name, o.nam... |
|
0.11 |
64.72 |
0.18 |
0.15 |
80 |
cusvaamain@HPGICCI1 (TNS V1-V3) |
SELECT /*+ ALL_ROWS */ COUNT(*... |
|
0.11 |
94.54 |
0.12 |
0.01 |
17 |
|
delete from con$ where owner#=... |
|
0.11 |
80.26 |
0.14 |
0.14 |
327 |
|
select intcol#, nvl(pos#, 0), ... |
|
0.08 |
19.20 |
0.42 |
0.24 |
1 |
|
begin prvt_hdm.auto_execute( :... |
|
0.07 |
54.97 |
0.13 |
0.13 |
83 |
|
select i.obj#, i.ts#, i.file#,... |
|
0.06 |
5.22 |
1.13 |
0.72 |
77 |
|
select obj#, type#, flags, ... |
|
0.06 |
86.50 |
0.06 |
0.06 |
45 |
|
select owner#, name from con$... |
|
0.06 |
8.19 |
0.67 |
0.08 |
1 |
SQL*Plus |
BEGIN dbms_workload_repository... |
|
0.04 |
75.69 |
0.06 |
0.06 |
87 |
|
select pos#, intcol#, col#, sp... |
|
0.04 |
48.05 |
0.09 |
0.07 |
7 |
|
select file#, block# from seg... |
|
0.04 |
8.84 |
0.40 |
0.40 |
6,304 |
|
select type#, blocks, extents,... |
|
0.03 |
28.15 |
0.12 |
0.12 |
49 |
|
delete from RecycleBin$ ... |
|
0.03 |
66.23 |
0.05 |
0.05 |
85 |
|
select t.ts#, t.file#, t.block... |
|
0.03 |
67.03 |
0.05 |
0.05 |
38 |
DBMS_SCHEDULER |
update obj$ set obj#=:6, type#... |
|
0.02 |
66.73 |
0.04 |
0.04 |
86 |
|
INSERT INTO sys.wri$_adv_messa... |
|
0.02 |
26.94 |
0.09 |
0.09 |
38 |
|
delete from RecycleBin$ ... |
|
0.02 |
76.76 |
0.03 |
0.03 |
51 |
|
select con# from con$ where ow... |
|
0.02 |
51.91 |
0.05 |
0.05 |
84 |
|
select name, intcol#, segcol#,... |
|
0.02 |
0.15 |
14.91 |
11.17 |
5 |
|
DECLARE job BINARY_INTEGER := ... |
|
0.02 |
2.12 |
1.00 |
0.99 |
8,784 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
update ICCIFNSACT set BORM_FA... |
|
0.02 |
53.82 |
0.03 |
0.03 |
39 |
cusvaamain@HPGICCI1 (TNS V1-V3) |
SELECT count(*) FROM user_poli... |
|
0.01 |
0.10 |
14.34 |
14.28 |
172,983 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
update ICCIFNSACT set BORM_AD... |
|
0.01 |
8.29 |
0.16 |
0.13 |
421 |
|
update sys.mon_mods$ set inser... |
|
0.01 |
1.65 |
0.56 |
0.54 |
2 |
|
insert into wrh$_latch (snap... |
|
0.01 |
22.33 |
0.04 |
0.02 |
26 |
load_curmmast@HPGICCI1 (TNS V1-V3) |
insert into ICCICURMMAST valu... |
|
0.01 |
0.08 |
7.71 |
7.70 |
172,983 |
load_fnsact@HPGICCI1 (TNS V1-V3) |
select * from ICCIPRODCODE wh... |
Complete List of SQL Text
SQL Id |
SQL Text |
04xtrk7uyhknh |
select obj#, type#, ctime, mtime, stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null |
0hhmdwwgxbw0r |
select obj#, type#, flags, related, bo, purgeobj, con# from RecycleBin$ where ts#=:1 and to_number(bitand(flags, 16)) = 16 order by dropscn |
0k8h617b8guhf |
delete from RecycleBin$ where purgeobj=:1 |
0pvtkmrrq8usg |
select file#, block# from seg$ where type# = 3 and ts# = :1 |
0yv9t4qb1zb2b |
select CUID_CUST_NO , CUID_ID_TYPE , CUID_ID_RECNO from CUID_TMP where CHGFLAG='D' |
104pd9mm3fh9p |
select blocks, maxblocks, grantor#, priv1, priv2, priv3 from tsq$ where ts#=:1 and user#=:2 |
1crajpb7j5tyz |
INSERT INTO STATS$SGA_TARGET_ADVICE ( SNAP_ID , DBID , INSTANCE_NUMBER , SGA_SIZE , SGA_SIZE_FACTOR , ESTD_DB_TIME , ESTD_DB_TIME_FACTOR , ESTD_PHYSICAL_READS ) SELECT :B3 , :B2 , :B1 , SGA_SIZE , SGA_SIZE_FACTOR , ESTD_DB_TIME , ESTD_DB_TIME_FACTOR , ESTD_PHYSICAL_READS FROM V$SGA_TARGET_ADVICE |
1dm3bq36vu3g8 |
insert into iccifnsact values (:b0, :b1, :b2, null , null , :b3, :b4, GREATEST(:b5, :b6), null , :b7, :b8, null , :b9, :b10, :b6, null , null , null , null , null , :b12, null , null , null , :b13, :b14, null , null , :b15, :b16, :b17) |
1gu8t96d0bdmu |
select t.ts#, t.file#, t.block#, nvl(t.bobj#, 0), nvl(t.tab#, 0), t.intcols, nvl(t.clucols, 0), t.audit$, t.flags, t.pctfree$, t.pctused$, t.initrans, t.maxtrans, t.rowcnt, t.blkcnt, t.empcnt, t.avgspc, t.chncnt, t.avgrln, t.analyzetime, t.samplesize, t.cols, t.property, nvl(t.degree, 1), nvl(t.instances, 1), t.avgspc_flb, t.flbcnt, t.kernelcols, nvl(t.trigflag, 0), nvl(t.spare1, 0), nvl(t.spare2, 0), t.spare4, t.spare6, ts.cachedblk, ts.cachehit, ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+) |
1uk5m5qbzj1vt |
BEGIN dbms_workload_repository.create_snapshot; END; |
2ym6hhaq30r73 |
select type#, blocks, extents, minexts, maxexts, extsize, extpct, user#, iniexts, NVL(lists, 65535), NVL(groups, 65535), cachehint, hwmincr, NVL(spare1, 0), NVL(scanhint, 0) from seg$ where ts#=:1 and file#=:2 and block#=:3 |
350f5yrnnmshs |
lock table sys.mon_mods$ in exclusive mode nowait |
38apjgr0p55ns |
update ICCICCS set CCSMAXOVERDUE=GREATEST(:b0, CCSMAXOVERDUE) where FNSACTNO=:b1 |
38gak8u2qm11w |
select count(*) from CUSVAA_TMP |
3m8smr0v7v1m6 |
INSERT INTO sys.wri$_adv_message_groups (task_id, id, seq, message#, fac, hdr, lm, nl, p1, p2, p3, p4, p5) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13) |
44au3v5mzpc1c |
insert into ICCICURMMAST values (:b0, :b1, :b2) |
49ms69srnaxzj |
insert into ICCIRPYV values (:b0, :b1, :b2, :b3, :b4, :b5, :b6, :b7, :b8, :b9, :b10, :b11, :b12, :b13, :b14, :b15, :b16, :b17, :b18, :b19, :b20, :b21, :b22, :b23, :b24, :b25, :b26, :b27, :b28, :b29, :b30, :b31, :b32, :b33, :b34, :b35, :b36, :b37, :b38, :b39, :b40, :b41, :b42, :b43, :b44, :b45, :b46, :b47, :b48, :b49, :b50, :b51) |
4vja2k2gdtyup |
insert into ICCICCS values (:b0, '////////////////////////', 0, 0, 0, 0, 0, ' ', 0, 0, 0, ' ', '0', null ) |
501v412s13r4m |
update ICCIFNSACT set BORM_FACILITY_NO=:b0 where BORM_MEMB_CUST_AC=:b1 |
53saa2zkr6wc3 |
select intcol#, nvl(pos#, 0), col#, nvl(spare1, 0) from ccol$ where con#=:1 |
569r5k05drsj7 |
insert into CUMI select CUSV_CUST_NO , CUSV_EDUCATION_CODE , CHGDATE from CUMI_TMP where CHGFLAG<>'D' |
5c4qu2zmj3gux |
select * from ICCIPRODCODE where PRODCODE=to_char(:b0) |
5ngzsfstg8tmy |
select o.owner#, o.name, o.namespace, o.remoteowner, o.linkname, o.subname, o.dataobj#, o.flags from obj$ o where o.obj#=:1 |
6769wyy3yf66f |
select pos#, intcol#, col#, spare1, bo#, spare2 from icol$ where obj#=:1 |
6z06gcfw39pkd |
SELECT F.TABLESPACE_NAME, TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE), '999, 999') "USED (MB)", TO_CHAR (F.FREE_SPACE, '999, 999') "FREE (MB)", TO_CHAR (T.TOTAL_SPACE, '999, 999') "TOTAL (MB)", TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)), '999')||' %' PER_FREE FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BLOCKS*(SELECT VALUE/1024 FROM V$PARAMETER WHERE NAME = 'db_block_size')/1024) ) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES/1048576)) TOTAL_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME ) T WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME |
78m9ryygp65v5 |
SELECT /*+ ALL_ROWS */ COUNT(*) FROM ALL_POLICIES V WHERE V.OBJECT_OWNER = :B3 AND V.OBJECT_NAME = :B2 AND (POLICY_NAME LIKE '%xdbrls%' OR POLICY_NAME LIKE '%$xd_%') AND V.FUNCTION = :B1 |
7gtztzv329wg0 |
select c.name, u.name from con$ c, cdef$ cd, user$ u where c.con# = cd.con# and cd.enabled = :1 and c.owner# = u.user# |
7ng34ruy5awxq |
select i.obj#, i.ts#, i.file#, i.block#, i.intcols, i.type#, i.flags, i.property, i.pctfree$, i.initrans, i.maxtrans, i.blevel, i.leafcnt, i.distkey, i.lblkkey, i.dblkkey, i.clufac, i.cols, i.analyzetime, i.samplesize, i.dataobj#, nvl(i.degree, 1), nvl(i.instances, 1), i.rowcnt, mod(i.pctthres$, 256), i.indmethod#, i.trunccnt, nvl(c.unicols, 0), nvl(c.deferrable#+c.valid#, 0), nvl(i.spare1, i.intcols), i.spare4, i.spare2, i.spare6, decode(i.pctthres$, null, null, mod(trunc(i.pctthres$/256), 256)), ist.cachedblk, ist.cachehit, ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols, min(to_number(bitand(defer, 1))) deferrable#, min(to_number(bitand(defer, 4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj# |
7v9dyf5r424yh |
select NEWACTNO into :b0 from OLDNEWACT where OLDACTNO=:b1 |
7wwv1ybs9zguz |
update ICCIFNSACT set BORM_ADV_DATE=:b0, BOIS_MATURITY_DATE=:b1, BOIS_UNPD_BAL=:b2, BOIS_UNPD_INT=:b3, BOIS_BAL_FINE=:b4, BOIS_INT_FINE=:b5, BOIS_FINE_FINE=:b6, BORM_LOAN_TRM=:b7, BORM_FIVE_STAT=:b8, BOIS_ARREARS_CTR=:b9, BOIS_ARREARS_SUM=:b10 where BORM_MEMB_CUST_AC=:b11 |
83taa7kaw59c1 |
select name, intcol#, segcol#, type#, length, nvl(precision#, 0), decode(type#, 2, nvl(scale, -127/*MAXSB1MINAL*/), 178, scale, 179, scale, 180, scale, 181, scale, 182, scale, 183, scale, 231, scale, 0), null$, fixedstorage, nvl(deflength, 0), default$, rowid, col#, property, nvl(charsetid, 0), nvl(charsetform, 0), spare1, spare2, nvl(spare3, 0) from col$ where obj#=:1 order by intcol# |
84qubbrsr0kfn |
insert into wrh$_latch (snap_id, dbid, instance_number, latch_hash, level#, gets, misses, sleeps, immediate_gets, immediate_misses, spin_gets, sleep1, sleep2, sleep3, sleep4, wait_time) select :snap_id, :dbid, :instance_number, hash, level#, gets, misses, sleeps, immediate_gets, immediate_misses, spin_gets, sleep1, sleep2, sleep3, sleep4, wait_time from v$latch order by hash |
9qgtwh66xg6nz |
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), cachehint=:15, hwmincr=:16, spare1=DECODE(:17, 0, NULL, :17), scanhint=:18 where ts#=:1 and file#=:2 and block#=:3 |
9vtm7gy4fr2ny |
select con# from con$ where owner#=:1 and name=:2 |
a2any035u1qz1 |
select owner#, name from con$ where con#=:1 |
a7nh7j8zmfrzw |
select CUSV_CUST_NO from CUMI_TMP where CHGFLAG='D' |
ackxqhnktxnbc |
insert into CUSM select CUSM_CUST_ACCT_NO , CUSM_STAT_POST_ADD_NO , CHGDATE from CUSM_TMP where CHGFLAG<>'D' |
aq4js2gkfjru8 |
update tsq$ set blocks=:3, maxblocks=:4, grantor#=:5, priv1=:6, priv2=:7, priv3=:8 where ts#=:1 and user#=:2 |
b52m6vduutr8j |
delete from RecycleBin$ where bo=:1 |
bdv0rkkssq2jm |
SELECT count(*) FROM user_policies o WHERE o.object_name = :tablename AND (policy_name LIKE '%xdbrls%' OR policy_name LIKE '%$xd_%') AND o.function='CHECKPRIVRLS_SELECTPF' |
bsa0wjtftg3uw |
select file# from file$ where ts#=:1 |
btzq46kta67dz |
update obj$ set obj#=:6, type#=:7, ctime=:8, mtime=:9, stime=:10, status=:11, dataobj#=:13, flags=:14, oid$=:15, spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null) |
bu8tnqr3xv25q |
select count(*) into :b0 from ICCIFNSACT where BORM_MEMB_CUST_AC=:b1 |
bwt0pmxhv7qk7 |
delete from con$ where owner#=:1 and name=:2 |
chjmy0dxf9mbj |
insert into ICCICCS values (:b0, :b1, :b2, :b3, :b4, :b5, :b6, :b7, :b8, :b9, :b10, :b11, :b12, :b13) |
cn1gtsav2d5jh |
BEGIN BEGIN IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner)) THEN xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name); END IF; EXCEPTION WHEN OTHERS THEN null; END; BEGIN IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name); END IF; EXCEPTION WHEN OTHERS THEN null; END; END; |
cp5duhcsj72q0 |
select CUSM_CUST_ACCT_NO from CUSM_TMP where CHGFLAG='D' |
d8z0u8hgj8xdy |
insert into CUID select CUID_CUST_NO , CUID_ID_MAIN , CUID_ID_TYPE , CUID_ID_RECNO , CUID_ID_NUMBER , CHGDATE from CUID_TMP where CHGFLAG<>'D' |
d92h3rjp0y217 |
begin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end; |
djs2w2f17nw2z |
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN statspack.snap; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; |
f80h0xb1qvbsk |
SELECT sys.wri$_adv_seq_msggroup.nextval FROM dual |
g00cj285jmgsw |
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, deletes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where obj# = :objn |
gmn2w09rdxn14 |
insert into OLDNEWACT values (:b0, :b1) |
Instance Activity Statistics
· Instance Activity Stats - Absolute Values
· Instance Activity Stats - Thread Activity
Instance Activity Stats
Statistic |
Total |
per Second |
per Trans |
CPU used by this session |
23,388 |
4.95 |
4.18 |
CPU used when call started |
21,816 |
4.61 |
3.90 |
CR blocks created |
2,794 |
0.59 |
0.50 |
Cached Commit SCN referenced |
237,936 |
50.33 |
42.50 |
Commit SCN cached |
3 |
0.00 |
0.00 |
DB time |
583,424 |
123.41 |
104.22 |
DBWR checkpoint buffers written |
402,781 |
85.20 |
71.95 |
DBWR checkpoints |
9 |
0.00 |
0.00 |
DBWR fusion writes |
255 |
0.05 |
0.05 |
DBWR object drop buffers written |
0 |
0.00 |
0.00 |
DBWR thread checkpoint buffers written |
221,341 |
46.82 |
39.54 |
DBWR transaction table writes |
130 |
0.03 |
0.02 |
DBWR undo block writes |
219,272 |
46.38 |
39.17 |
DFO trees parallelized |
16 |
0.00 |
0.00 |
PX local messages recv'd |
40 |
0.01 |
0.01 |
PX local messages sent |
40 |
0.01 |
0.01 |
PX remote messages recv'd |
80 |
0.02 |
0.01 |
PX remote messages sent |
80 |
0.02 |
0.01 |
Parallel operations not downgraded |
16 |
0.00 |
0.00 |
RowCR - row contention |
9 |
0.00 |
0.00 |
RowCR attempts |
14 |
0.00 |
0.00 |
RowCR hits |
5 |
0.00 |
0.00 |
SMON posted for undo segment recovery |
0 |
0.00 |
0.00 |
SMON posted for undo segment shrink |
9 |
0.00 |
0.00 |
SQL*Net roundtrips to/from client |
1,544,063 |
326.62 |
275.82 |
active txn count during cleanout |
276,652 |
58.52 |
49.42 |
application wait time |
1,620 |
0.34 |
0.29 |
auto extends on undo tablespace |
0 |
0.00 |
0.00 |
background checkpoints completed |
7 |
0.00 |
0.00 |
background checkpoints started |
9 |
0.00 |
0.00 |
background timeouts |
21,703 |
4.59 |
3.88 |
branch node splits |
337 |
0.07 |
0.06 |
buffer is not pinned count |
1,377,184 |
291.32 |
246.01 |
buffer is pinned count |
20,996,139 |
4,441.37 |
3,750.65 |
bytes received via SQL*Net from client |
7,381,397,183 |
1,561,408.36 |
1,318,577.56 |
bytes sent via SQL*Net to client |
149,122,035 |
31,544.22 |
26,638.45 |
calls to get snapshot scn: kcmgss |
1,696,712 |
358.91 |
303.09 |
calls to kcmgas |
433,435 |
91.69 |
77.43 |
calls to kcmgcs |
142,482 |
30.14 |
25.45 |
change write time |
4,707 |
1.00 |
0.84 |
cleanout - number of ktugct calls |
282,045 |
59.66 |
50.38 |
cleanouts and rollbacks - consistent read gets |
55 |
0.01 |
0.01 |
cleanouts only - consistent read gets |
2,406 |
0.51 |
0.43 |
cluster key scan block gets |
21,886 |
4.63 |
3.91 |
cluster key scans |
10,540 |
2.23 |
1.88 |
cluster wait time |
2,855 |
0.60 |
0.51 |
commit batch/immediate performed |
294 |
0.06 |
0.05 |
commit batch/immediate requested |
294 |
0.06 |
0.05 |
commit cleanout failures: block lost |
2,227 |
0.47 |
0.40 |
commit cleanout failures: callback failure |
750 |
0.16 |
0.13 |
commit cleanout failures: cannot pin |
4 |
0.00 |
0.00 |
commit cleanouts |
427,610 |
90.45 |
76.39 |
commit cleanouts successfully completed |
424,629 |
89.82 |
75.85 |
commit immediate performed |
294 |
0.06 |
0.05 |
commit immediate requested |
294 |
0.06 |
0.05 |
commit txn count during cleanout |
111,557 |
23.60 |
19.93 |
concurrency wait time |
515 |
0.11 |
0.09 |
consistent changes |
1,716 |
0.36 |
0.31 |
consistent gets |
5,037,471 |
1,065.59 |
899.87 |
consistent gets - examination |
2,902,016 |
613.87 |
518.40 |
consistent gets direct |
0 |
0.00 |
0.00 |
consistent gets from cache |
5,037,471 |
1,065.59 |
899.87 |
current blocks converted for CR |
0 |
0.00 |
0.00 |
cursor authentications |
434 |
0.09 |
0.08 |
data blocks consistent reads - undo records applied |
1,519 |
0.32 |
0.27 |
db block changes |
8,594,158 |
1,817.95 |
1,535.22 |
db block gets |
11,611,321 |
2,456.18 |
2,074.19 |
db block gets direct |
1,167,830 |
247.03 |
208.62 |
db block gets from cache |
10,443,491 |
2,209.14 |
1,865.58 |
deferred (CURRENT) block cleanout applications |
20,786 |
4.40 |
3.71 |
dirty buffers inspected |
25,007 |
5.29 |
4.47 |
drop segment calls in space pressure |
0 |
0.00 |
0.00 |
enqueue conversions |
6,734 |
1.42 |
1.20 |
enqueue releases |
595,149 |
125.89 |
106.31 |
enqueue requests |
595,158 |
125.90 |
106.32 |
enqueue timeouts |
9 |
0.00 |
0.00 |
enqueue waits |
7,901 |
1.67 |
1.41 |
exchange deadlocks |
1 |
0.00 |
0.00 |
execute count |
1,675,112 |
354.34 |
299.23 |
free buffer inspected |
536,832 |
113.56 |
95.90 |
free buffer requested |
746,999 |
158.01 |
133.44 |
gc CPU used by this session |
9,099 |
1.92 |
1.63 |
gc cr block build time |
13 |
0.00 |
0.00 |
gc cr block flush time |
143 |
0.03 |
0.03 |
gc cr block receive time |
474 |
0.10 |
0.08 |
gc cr block send time |
36 |
0.01 |
0.01 |
gc cr blocks received |
4,142 |
0.88 |
0.74 |
gc cr blocks served |
10,675 |
2.26 |
1.91 |
gc current block flush time |
23 |
0.00 |
0.00 |
gc current block pin time |
34 |
0.01 |
0.01 |
gc current block receive time |
1,212 |
0.26 |
0.22 |
gc current block send time |
52 |
0.01 |
0.01 |
gc current blocks received |
15,502 |
3.28 |
2.77 |
gc current blocks served |
17,534 |
3.71 |
3.13 |
gc local grants |
405,329 |
85.74 |
72.41 |
gc remote grants |
318,630 |
67.40 |
56.92 |
gcs messages sent |
1,129,094 |
238.84 |
201.70 |
ges messages sent |
90,695 |
19.18 |
16.20 |
global enqueue get time |
1,707 |
0.36 |
0.30 |
global enqueue gets async |
12,731 |
2.69 |
2.27 |
global enqueue gets sync |
190,492 |
40.30 |
34.03 |
global enqueue releases |
190,328 |
40.26 |
34.00 |
global undo segment hints helped |
0 |
0.00 |
0.00 |
global undo segment hints were stale |
0 |
0.00 |
0.00 |
heap block compress |
108,758 |
23.01 |
19.43 |
hot buffers moved to head of LRU |
18,652 |
3.95 |
3.33 |
immediate (CR) block cleanout applications |
2,462 |
0.52 |
0.44 |
immediate (CURRENT) block cleanout applications |
325,184 |
68.79 |
58.09 |
index crx upgrade (positioned) |
4,663 |
0.99 |
0.83 |
index fast full scans (full) |
13 |
0.00 |
0.00 |
index fetch by key |
852,181 |
180.26 |
152.23 |
index scans kdiixs1 |
339,583 |
71.83 |
60.66 |
leaf node 90-10 splits |
34 |
0.01 |
0.01 |
leaf node splits |
106,552 |
22.54 |
19.03 |
lob reads |
11 |
0.00 |
0.00 |
lob writes |
83 |
0.02 |
0.01 |
lob writes unaligned |
83 |
0.02 |
0.01 |
local undo segment hints helped |
0 |
0.00 |
0.00 |
local undo segment hints were stale |
0 |
0.00 |
0.00 |
logons cumulative |
61 |
0.01 |
0.01 |
messages received |
20,040 |
4.24 |
3.58 |
messages sent |
19,880 |
4.21 |
3.55 |
no buffer to keep pinned count |
0 |
0.00 |
0.00 |
no work - consistent read gets |
1,513,070 |
320.06 |
270.29 |
opened cursors cumulative |
183,375 |
38.79 |
32.76 |
parse count (failures) |
1 |
0.00 |
0.00 |
parse count (hard) |
143 |
0.03 |
0.03 |
parse count (total) |
182,780 |
38.66 |
32.65 |
parse time cpu |
27 |
0.01 |
0.00 |
parse time elapsed |
338 |
0.07 |
0.06 |
physical read IO requests |
82,815 |
17.52 |
14.79 |
physical read bytes |
2,643,378,176 |
559,161.45 |
472,200.46 |
physical read total IO requests |
98,871 |
20.91 |
17.66 |
physical read total bytes |
2,905,491,456 |
614,607.04 |
519,023.13 |
physical read total multi block requests |
24,089 |
5.10 |
4.30 |
physical reads |
322,678 |
68.26 |
57.64 |
physical reads cache |
213,728 |
45.21 |
38.18 |
physical reads cache prefetch |
191,830 |
40.58 |
34.27 |
physical reads direct |
108,950 |
23.05 |
19.46 |
physical reads direct temporary tablespace |
108,812 |
23.02 |
19.44 |
physical reads prefetch warmup |
0 |
0.00 |
0.00 |
physical write IO requests |
223,456 |
47.27 |
39.92 |
physical write bytes |
14,042,071,040 |
2,970,360.02 |
2,508,408.55 |
physical write total IO requests |
133,835 |
28.31 |
23.91 |
physical write total bytes |
23,114,268,672 |
4,889,428.30 |
4,129,022.63 |
physical write total multi block requests |
116,135 |
24.57 |
20.75 |
physical writes |
1,714,120 |
362.59 |
306.20 |
physical writes direct |
1,276,780 |
270.08 |
228.08 |
physical writes direct (lob) |
0 |
0.00 |
0.00 |
physical writes direct temporary tablespace |
108,812 |
23.02 |
19.44 |
physical writes from cache |
437,340 |
92.51 |
78.12 |
physical writes non checkpoint |
1,673,703 |
354.04 |
298.98 |
pinned buffers inspected |
10 |
0.00 |
0.00 |
prefetch clients - default |
0 |
0.00 |
0.00 |
prefetch warmup blocks aged out before use |
0 |
0.00 |
0.00 |
prefetch warmup blocks flushed out before use |
0 |
0.00 |
0.00 |
prefetched blocks aged out before use |
0 |
0.00 |
0.00 |
process last non-idle time |
4,730 |
1.00 |
0.84 |
queries parallelized |
16 |
0.00 |
0.00 |
recursive calls |
1,654,650 |
350.01 |
295.58 |
recursive cpu usage |
2,641 |
0.56 |
0.47 |
redo blocks written |
8,766,094 |
1,854.32 |
1,565.93 |
redo buffer allocation retries |
24 |
0.01 |
0.00 |
redo entries |
4,707,068 |
995.70 |
840.85 |
redo log space requests |
34 |
0.01 |
0.01 |
redo log space wait time |
50 |
0.01 |
0.01 |
redo ordering marks |
277,042 |
58.60 |
49.49 |
redo size |
4,343,559,400 |
918,805.72 |
775,912.72 |
redo subscn max counts |
2,693 |
0.57 |
0.48 |
redo synch time |
408 |
0.09 |
0.07 |
redo synch writes |
6,984 |
1.48 |
1.25 |
redo wastage |
1,969,620 |
416.64 |
351.84 |
redo write time |
5,090 |
1.08 |
0.91 |
redo writer latching time |
1 |
0.00 |
0.00 |
redo writes |
5,494 |
1.16 |
0.98 |
rollback changes - undo records applied |
166,609 |
35.24 |
29.76 |
rollbacks only - consistent read gets |
1,463 |
0.31 |
0.26 |
rows fetched via callback |
342,159 |
72.38 |
61.12 |
session connect time |
1,461 |
0.31 |
0.26 |
session cursor cache hits |
180,472 |
38.18 |
32.24 |
session logical reads |
16,648,792 |
3,521.77 |
2,974.06 |
session pga memory |
37,393,448 |
7,909.94 |
6,679.79 |
session pga memory max |
45,192,232 |
9,559.64 |
8,072.92 |
session uga memory |
30,067,312,240 |
6,360,225.77 |
5,371,081.14 |
session uga memory max |
61,930,448 |
13,100.33 |
11,062.96 |
shared hash latch upgrades - no wait |
6,364 |
1.35 |
1.14 |
shared hash latch upgrades - wait |
0 |
0.00 |
0.00 |
sorts (disk) |
4 |
0.00 |
0.00 |
sorts (memory) |
2,857 |
0.60 |
0.51 |
sorts (rows) |
42,379,505 |
8,964.66 |
7,570.47 |
space was found by tune down |
0 |
0.00 |
0.00 |
space was not found by tune down |
0 |
0.00 |
0.00 |
sql area evicted |
7 |
0.00 |
0.00 |
sql area purged |
44 |
0.01 |
0.01 |
steps of tune down ret. in space pressure |
0 |
0.00 |
0.00 |
summed dirty queue length |
35,067 |
7.42 |
6.26 |
switch current to new buffer |
17 |
0.00 |
0.00 |
table fetch by rowid |
680,469 |
143.94 |
121.56 |
table fetch continued row |
0 |
0.00 |
0.00 |
table scan blocks gotten |
790,986 |
167.32 |
141.30 |
table scan rows gotten |
52,989,363 |
11,208.99 |
9,465.77 |
table scans (long tables) |
4 |
0.00 |
0.00 |
table scans (short tables) |
169,201 |
35.79 |
30.23 |
total number of times SMON posted |
259 |
0.05 |
0.05 |
transaction lock background get time |
0 |
0.00 |
0.00 |
transaction lock background gets |
0 |
0.00 |
0.00 |
transaction lock foreground requests |
0 |
0.00 |
0.00 |
transaction lock foreground wait time |
0 |
0.00 |
0.00 |
transaction rollbacks |
294 |
0.06 |
0.05 |
tune down retentions in space pressure |
0 |
0.00 |
0.00 |
undo change vector size |
1,451,085,596 |
306,952.35 |
259,215.00 |
user I/O wait time |
11,992 |
2.54 |
2.14 |
user calls |
1,544,383 |
326.69 |
275.88 |
user commits |
812 |
0.17 |
0.15 |
user rollbacks |
4,786 |
1.01 |
0.85 |
workarea executions - onepass |
1 |
0.00 |
0.00 |
workarea executions - optimal |
1,616 |
0.34 |
0.29 |
write clones created in background |
0 |
0.00 |
0.00 |
write clones created in foreground |
11 |
0.00 |
0.00 |
Back to Instance Activity Statistics
Instance Activity Stats - Absolute Values
· Statistics with absolute values (should not be diffed)
Statistic |
Begin Value |
End Value |
session cursor cache count |
3,024 |
3,592 |
opened cursors current |
37 |
39 |
logons current |
24 |
26 |
Back to Instance Activity Statistics
Instance Activity Stats - Thread Activity
· Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic |
Total |
per Hour |
log switches (derived) |
9 |
6.85 |
Back to Instance Activity Statistics
IO Stats
Tablespace IO Stats
· ordered by IOs (Reads + Writes) desc
Tablespace |
Reads |
Av Reads/s |
Av Rd(ms) |
Av Blks/Rd |
Writes |
Av Writes/s |
Buffer Waits |
Av Buf Wt(ms) |
ICCIDAT01 |
67,408 |
14 |
3.76 |
3.17 |
160,261 |
34 |
6 |
0.00 |
UNDOTBS1 |
10 |
0 |
12.00 |
1.00 |
57,771 |
12 |
625 |
0.02 |
TEMP |
15,022 |
3 |
8.74 |
7.24 |
3,831 |
1 |
0 |
0.00 |
USERS |
68 |
0 |
5.44 |
1.00 |
971 |
0 |
0 |
0.00 |
SYSAUX |
263 |
0 |
5.48 |
1.00 |
458 |
0 |
0 |
0.00 |
SYSTEM |
32 |
0 |
5.94 |
1.00 |
158 |
0 |
3 |
23.33 |
UNDOTBS2 |
6 |
0 |
16.67 |
1.00 |
6 |
0 |
0 |
0.00 |
顯示每個表空間的I/O統計。根據Oracle經驗,Av Rd(ms) [Average Reads in milliseconds]不應該超過30,否則認為有I/O爭用。
File IO Stats
· ordered by Tablespace, File
Tablespace |
Filename |
Reads |
Av Reads/s |
Av Rd(ms) |
Av Blks/Rd |
Writes |
Av Writes/s |
Buffer Waits |
Av Buf Wt(ms) |
ICCIDAT01 |
/dev/rora_icci01 |
5,919 |
1 |
4.30 |
3.73 |
15,161 |
3 |
1 |
0.00 |
ICCIDAT01 |
/dev/rora_icci02 |
7,692 |
2 |
4.12 |
3.18 |
16,555 |
4 |
0 |
0.00 |
ICCIDAT01 |
/dev/rora_icci03 |
6,563 |
1 |
2.59 |
3.80 |
15,746 |
3 |
0 |
0.00 |
ICCIDAT01 |
/dev/rora_icci04 |
8,076 |
2 |
2.93 |
3.11 |
16,164 |
3 |
0 |
0.00 |
ICCIDAT01 |
/dev/rora_icci05 |
6,555 |
1 |
2.61 |
3.31 |
21,958 |
5 |
0 |
0.00 |
ICCIDAT01 |
/dev/rora_icci06 |
6,943 |
1 |
4.03 |
3.41 |
20,574 |
4 |
0 |
0.00 |
ICCIDAT01 |
/dev/rora_icci07 |
7,929 |
2 |
4.12 |
2.87 |
18,263 |
4 |
0 |
0.00 |
ICCIDAT01 |
/dev/rora_icci08 |
7,719 |
2 |
3.83 |
2.99 |
17,361 |
4 |
0 |
0.00 |
ICCIDAT01 |
/dev/rora_icci09 |
6,794 |
1 |
4.79 |
3.29 |
18,425 |
4 |
0 |
0.00 |
ICCIDAT01 |
/dev/rora_icci10 |
211 |
0 |
5.31 |
1.00 |
6 |
0 |
0 |
0.00 |
ICCIDAT01 |
/dev/rora_icci11 |
1,168 |
0 |
4.45 |
1.00 |
6 |
0 |
0 |
0.00 |
ICCIDAT01 |
/dev/rora_icci12 |
478 |
0 |
4.23 |
1.00 |
6 |
0 |
0 |
0.00 |
ICCIDAT01 |
/dev/rora_icci13 |
355 |
0 |
5.13 |
1.00 |
6 |
0 |
0 |
0.00 |
ICCIDAT01 |
/dev/rora_icci14 |
411 |
0 |
4.91 |
1.00 |
6 |
0 |
1 |
0.00 |
ICCIDAT01 |
/dev/rora_icci15 |
172 |
0 |
5.29 |
1.00 |
6 |
0 |
1 |
0.00 |
ICCIDAT01 |
/dev/rora_icci16 |
119 |
0 |
7.23 |
1.00 |
6 |
0 |
1 |
0.00 |
ICCIDAT01 |
/dev/rora_icci17 |
227 |
0 |
6.26 |
1.00 |
6 |
0 |
1 |
0.00 |
ICCIDAT01 |
/dev/rora_icci18 |
77 |
0 |
8.44 |
1.00 |
6 |
0 |
1 |
0.00 |
SYSAUX |
/dev/rora_SYSAUX |
263 |
0 |
5.48 |
1.00 |
458 |
0 |
0 |
0.00 |
SYSTEM |
/dev/rora_SYSTEM |
32 |
0 |
5.94 |
1.00 |
158 |
0 |
3 |
23.33 |
TEMP |
/dev/rora_TEMP |
3,653 |
1 |
5.67 |
6.61 |
827 |
0 |
0 |
|
TEMP |
/dev/rora_TEMP2 |
2,569 |
1 |
4.42 |
6.70 |
556 |
0 |
0 |
|
TEMP |
/dev/rora_TEMP3 |
1,022 |
0 |
2.50 |
16.86 |
557 |
0 |
0 |
|
TEMP |
/dev/rora_TEMP5 |
7,778 |
2 |
12.43 |
6.46 |
1,891 |
0 |
0 |
|
UNDOTBS1 |
/dev/rora_UNDO0101 |
10 |
0 |
12.00 |
1.00 |
57,771 |
12 |
625 |
0.02 |
UNDOTBS2 |
/dev/rora_UNDO0201 |
6 |
0 |
16.67 |
1.00 |
6 |
0 |
0 |
0.00 |
USERS |
/dev/rora_USERS |
68 |
0 |
5.44 |
1.00 |
971 |
0 |
0 |
0.00 |
Buffer Pool Statistics
· Standard block size Pools D: default, K: keep, R: recycle
· Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
P |
Number of Buffers |
Pool Hit% |
Buffer Gets |
Physical Reads |
Physical Writes |
Free Buff Wait |
Writ Comp Wait |
Buffer Busy Waits |
D |
401,071 |
99 |
15,480,754 |
213,729 |
437,340 |
0 |
0 |
634 |
Advisory Statistics
Instance Recovery Stats
· B: Begin snapshot, E: End snapshot
|
Targt MTTR (s) |
Estd MTTR (s) |
Recovery Estd IOs |
Actual Redo Blks |
Target Redo Blks |
Log File Size Redo Blks |
Log Ckpt Timeout Redo Blks |
Log Ckpt Interval Redo Blks |
B |
0 |
11 |
369 |
2316 |
5807 |
1883700 |
5807 |
|
E |
0 |
98 |
116200 |
1828613 |
1883700 |
1883700 |
5033355 |
|
Buffer Pool Advisory
· Only rows with estimated physical reads >0 are displayed
· ordered by Block Size, Buffers For Estimate
P |
Size for Est (M) |
Size Factor |
Buffers for Estimate |
Est Phys Read Factor |
Estimated Physical Reads |
D |
320 |
0.10 |
38,380 |
1.34 |
10,351,726 |
D |
640 |
0.19 |
76,760 |
1.25 |
9,657,000 |
D |
960 |
0.29 |
115,140 |
1.08 |
8,365,242 |
D |
1,280 |
0.38 |
153,520 |
1.04 |
8,059,415 |
D |
1,600 |
0.48 |
191,900 |
1.02 |
7,878,202 |
D |
1,920 |
0.57 |
230,280 |
1.01 |
7,841,140 |
D |
2,240 |
0.67 |
268,660 |
1.01 |
7,829,141 |
D |
2,560 |
0.77 |
307,040 |
1.01 |
7,817,370 |
D |
2,880 |
0.86 |
345,420 |
1.01 |
7,804,884 |
D |
3,200 |
0.96 |
383,800 |
1.00 |
7,784,014 |
D |
3,344 |
1.00 |
401,071 |
1.00 |
7,748,403 |
D |
3,520 |
1.05 |
422,180 |
0.99 |
7,702,243 |
D |
3,840 |
1.15 |
460,560 |
0.99 |
7,680,429 |
D |
4,160 |
1.24 |
498,940 |
0.99 |
7,663,046 |
D |
4,480 |
1.34 |
537,320 |
0.99 |
7,653,232 |
D |
4,800 |
1.44 |
575,700 |
0.99 |
7,645,544 |
D |
5,120 |
1.53 |
614,080 |
0.98 |
7,630,008 |
D |
5,440 |
1.63 |
652,460 |
0.98 |
7,616,886 |
D |
5,760 |
1.72 |
690,840 |
0.98 |
7,614,591 |
D |
6,080 |
1.82 |
729,220 |
0.98 |
7,613,191 |
D |
6,400 |
1.91 |
767,600 |
0.98 |
7,599,930 |
PGA Aggr Summary
· 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 |
87.91 |
1,100 |
151 |
PGA Aggr Target Stats
· 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 Aggr Target(M) |
Auto PGA Target(M) |
PGA Mem Alloc(M) |
W/A PGA Used(M) |
%PGA W/A Mem |
%Auto W/A Mem |
%Man W/A Mem |
Global Mem Bound(K) |
B |
1,024 |
862 |
150.36 |
0.00 |
0.00 |
0.00 |
0.00 |
104,850 |
E |
1,024 |
860 |
154.14 |
0.00 |
0.00 |
0.00 |
0.00 |
104,850 |
PGA Aggr Target Histogram
· Optimal Executions are purely in-memory operations
Low Optimal |
High Optimal |
Total Execs |
Optimal Execs |
1-Pass Execs |
M-Pass Execs |
2K |
4K |
1,385 |
1,385 |
0 |
0 |
64K |
128K |
28 |
28 |
0 |
0 |
128K |
256K |
5 |
5 |
0 |
0 |
256K |
512K |
79 |
79 |
0 |
0 |
512K |
1024K |
108 |
108 |
0 |
0 |
1M |
2M |
7 |
7 |
0 |
0 |
8M |
16M |
1 |
1 |
0 |
0 |
128M |
256M |
3 |
2 |
1 |
0 |
256M |
512M |
1 |
1 |
0 |
0 |
PGA Memory Advisory
· When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0
PGA Target Est (MB) |
Size Factr |
W/A MB Processed |
Estd Extra W/A MB Read/ Written to Disk |
Estd PGA Cache Hit % |
Estd PGA Overalloc Count |
128 |
0.13 |
4,652.12 |
2,895.99 |
62.00 |
0 |
256 |
0.25 |
4,652.12 |
2,857.13 |
62.00 |
0 |
512 |
0.50 |
4,652.12 |
2,857.13 |
62.00 |
0 |
768 |
0.75 |
4,652.12 |
2,857.13 |
62.00 |
0 |
1,024 |
1.00 |
4,652.12 |
717.82 |
87.00 |
0 |
1,229 |
1.20 |
4,652.12 |
717.82 |
87.00 |
0 |
1,434 |
1.40 |
4,652.12 |
717.82 |
87.00 |
0 |
1,638 |
1.60 |
4,652.12 |
717.82 |
87.00 |
0 |
1,843 |
1.80 |
4,652.12 |
717.82 |
87.00 |
0 |
2,048 |
2.00 |
4,652.12 |
717.82 |
87.00 |
0 |
3,072 |
3.00 |
4,652.12 |
717.82 |
87.00 |
0 |
4,096 |
4.00 |
4,652.12 |
717.82 |
87.00 |
0 |
6,144 |
6.00 |
4,652.12 |
717.82 |
87.00 |
0 |
8,192 |
8.00 |
4,652.12 |
717.82 |
87.00 |
0 |
Shared Pool Advisory
· 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.
Shared Pool Size(M) |
SP Size Factr |
Est LC Size (M) |
Est LC Mem Obj |
Est LC Time Saved (s) |
Est LC Time Saved Factr |
Est LC Load Time (s) |
Est LC Load Time Factr |
Est LC Mem Obj Hits |
304 |
0.43 |
78 |
7,626 |
64,842 |
1.00 |
31 |
1.00 |
3,206,955 |
384 |
0.55 |
78 |
7,626 |
64,842 |
1.00 |
31 |
1.00 |
3,206,955 |
464 |
0.66 |
78 |
7,626 |
64,842 |
1.00 |
31 |
1.00 |
3,206,955 |
544 |
0.77 |
78 |
7,626 |
64,842 |
1.00 |
31 |
1.00 |
3,206,955 |
624 |
0.89 |
78 |
7,626 |
64,842 |
1.00 |
31 |
1.00 |
3,206,955 |
704 |
1.00 |
78 |
7,626 |
64,842 |
1.00 |
31 |
1.00 |
3,206,955 |
784 |
1.11 |
78 |
7,626 |
64,842 |
1.00 |
31 |
1.00 |
3,206,955 |
864 |
1.23 |
78 |
7,626 |
64,842 |
1.00 |
31 |
1.00 |
3,206,955 |
944 |
1.34 |
78 |
7,626 |
64,842 |
1.00 |
31 |
1.00 |
3,206,955 |
1,024 |
1.45 |
78 |
7,626 |
64,842 |
1.00 |
31 |
1.00 |
3,206,955 |
1,104 |
1.57 |
78 |
7,626 |
64,842 |
1.00 |
31 |
1.00 |
3,206,955 |
1,184 |
1.68 |
78 |
7,626 |
64,842 |
1.00 |
31 |
1.00 |
3,206,955 |
1,264 |
1.80 |
78 |
7,626 |
64,842 |
1.00 |
31 |
1.00 |
3,206,955 |
1,344 |
1.91 |
78 |
7,626 |
64,842 |
1.00 |
31 |
1.00 |
3,206,955 |
1,424 |
2.02 |
78 |
7,626 |
64,842 |
1.00 |
31 |
1.00 |
3,206,955 |
SGA Target Advisory
SGA Target Size (M) |
SGA Size Factor |
Est DB Time (s) |
Est Physical Reads |
1,024 |
0.25 |
9,060 |
9,742,760 |
2,048 |
0.50 |
7,612 |
7,948,245 |
3,072 |
0.75 |
7,563 |
7,886,258 |
4,096 |
1.00 |
7,451 |
7,748,338 |
5,120 |
1.25 |
7,423 |
7,713,470 |
6,144 |
1.50 |
7,397 |
7,680,927 |
7,168 |
1.75 |
7,385 |
7,666,980 |
8,192 |
2.00 |
7,385 |
7,666,980 |
Streams Pool Advisory
No data exists for this section of the report.
Java Pool Advisory
No data exists for this section of the report.
Wait Statistics
Buffer Wait Statistics
· ordered by wait time desc, waits desc
Class |
Waits |
Total Wait Time (s) |
Avg Time (ms) |
data block |
3 |
0 |
23 |
undo header |
616 |
0 |
0 |
file header block |
8 |
0 |
0 |
undo block |
7 |
0 |
0 |
Enqueue Activity
· 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) |
FB-Format Block |
14,075 |
14,075 |
0 |
7,033 |
3 |
0.43 |
US-Undo Segment |
964 |
964 |
0 |
556 |
0 |
0.32 |
WF-AWR Flush |
24 |
24 |
0 |
14 |
0 |
9.00 |
HW-Segment High Water Mark |
4,223 |
4,223 |
0 |
37 |
0 |
1.22 |
CF-Controlfile Transaction |
10,548 |
10,548 |
0 |
58 |
0 |
0.67 |
TX-Transaction (index contention) |
1 |
1 |
0 |
1 |
0 |
35.00 |
TM-DML |
121,768 |
121,761 |
6 |
70 |
0 |
0.43 |
PS-PX Process Reservation |
103 |
103 |
0 |
46 |
0 |
0.65 |
TT-Tablespace |
9,933 |
9,933 |
0 |
39 |
0 |
0.54 |
TD-KTF map table enqueue (KTF dump entries) |
12 |
12 |
0 |
12 |
0 |
1.42 |
TA-Instance Undo |
18 |
18 |
0 |
13 |
0 |
0.38 |
PI-Remote PX Process Spawn Status |
16 |
16 |
0 |
8 |
0 |
0.50 |
MW-MWIN Schedule |
3 |
3 |
0 |
3 |
0 |
0.67 |
DR-Distributed Recovery |
3 |
3 |
0 |
3 |
0 |
0.33 |
TS-Temporary Segment |
14 |
11 |
3 |
3 |
0 |
0.33 |
AF-Advisor Framework (task serialization) |
14 |
14 |
0 |
1 |
0 |
1.00 |
JS-Job Scheduler (job run lock - synchronize) |
2 |
2 |
0 |
1 |
0 |
1.00 |
UL-User-defined |
2 |
2 |
0 |
1 |
0 |
1.00 |
MD-Materialized View Log DDL |
6 |
6 |
0 |
2 |
0 |
0.00 |
Undo Statistics
Undo Segment Summary
· 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 TS# |
Num Undo Blocks (K) |
Number of Transactions |
Max Qry Len (s) |
Max Tx Concurcy |
Min/Max TR (mins) |
STO/ OOS |
uS/uR/uU/ eS/eR/eU |
1 |
219.12 |
113,405 |
0 |
6 |
130.95/239.25 |
0/0 |
0/0/0/13/24256/0 |
Undo Segment Stats
· Most recent 35 Undostat rows, ordered by Time desc
End Time |
Num Undo Blocks |
Number of Transactions |
Max Qry Len (s) |
Max Tx Concy |
Tun Ret (mins) |
STO/ OOS |
uS/uR/uU/ eS/eR/eU |
25-Dec 15:18 |
182,021 |
74,309 |
0 |
5 |
131 |
0/0 |
0/0/0/13/24256/0 |
25-Dec 15:08 |
57 |
170 |
0 |
3 |
239 |
0/0 |
0/0/0/0/0/0 |
25-Dec 14:58 |
68 |
31 |
0 |
2 |
229 |
0/0 |
0/0/0/0/0/0 |
25-Dec 14:48 |
194 |
4,256 |
0 |
4 |
219 |
0/0 |
0/0/0/0/0/0 |
25-Dec 14:38 |
570 |
12,299 |
0 |
5 |
209 |
0/0 |
0/0/0/0/0/0 |
25-Dec 14:28 |
36,047 |
21,328 |
0 |
6 |
200 |
0/0 |
0/0/0/0/0/0 |
25-Dec 14:18 |
70 |
907 |
0 |
3 |
162 |
0/0 |
0/0/0/0/0/0 |
25-Dec 14:08 |
91 |
105 |
0 |
3 |
154 |
0/0 |
0/0/0/0/0/0 |
Latch Statistics
Latch Activity
· "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
Latch Name |
Get Requests |
Pct Get Miss |
Avg Slps /Miss |
Wait Time (s) |
NoWait Requests |
Pct NoWait Miss |
ASM db client latch |
11,883 |
0.00 |
|
0 |
0 |
|
AWR Alerted Metric Element list |
18,252 |
0.00 |
|
0 |
0 |
|
Consistent RBA |
5,508 |
0.02 |
0.00 |
0 |
0 |
|
FOB s.o list latch |
731 |
0.00 |
|
0 |
0 |
|
JS broadcast add buf latch |
6,193 |
0.00 |
|
0 |
0 |
|
JS broadcast drop buf latch |
6,194 |
0.00 |
|
0 |
0 |
|
JS broadcast load blnc latch |
6,057 |
0.00 |
|
0 |
0 |
|
JS mem alloc latch |
8 |
0.00 |
|
0 |
0 |
|
JS queue access latch |
8 |
0.00 |
|
0 |
0 |
|
JS queue state obj latch |
218,086 |
0.00 |
|
0 |
0 |
|
JS slv state obj latch |
31 |
0.00 |
|
0 |
0 |
|
KCL gc element parent latch |
2,803,392 |
0.04 |
0.01 |
0 |
108 |
0.00 |
KJC message pool free list |
43,168 |
0.06 |
0.00 |
0 |
14,532 |
0.01 |
KJCT flow control latch |
563,875 |
0.00 |
0.00 |
0 |
0 |
|
KMG MMAN ready and startup request latch |
1,576 |
0.00 |
|
0 |
0 |
|
KSXR large replies |
320 |
0.00 |
|
0 |
0 |
|
KTF sga latch |
23 |
0.00 |
|
0 |
1,534 |
0.00 |
KWQMN job cache list latch |
352 |
0.00 |
|
0 |
0 |
|
KWQP Prop Status |
5 |
0.00 |
|
0 |
0 |
|
MQL Tracking Latch |
0 |
|
|
0 |
94 |
0.00 |
Memory Management Latch |
0 |
|
|
0 |
1,576 |
0.00 |
OS process |
207 |
0.00 |
|
0 |
0 |
|
OS process allocation |
1,717 |
0.00 |
|
0 |
0 |
|
OS process: request allocation |
73 |
0.00 |
|
0 |
0 |
|
PL/SQL warning settings |
226 |
0.00 |
|
0 |
0 |
|
SGA IO buffer pool latch |
20,679 |
0.06 |
0.00 |
0 |
20,869 |
0.00 |
SQL memory manager latch |
7 |
0.00 |
|
0 |
1,575 |
0.00 |
SQL memory manager workarea list latch |
439,442 |
0.00 |
|
0 |
0 |
|
Shared B-Tree |
182 |
0.00 |
|
0 |
0 |
|
Undo Hint Latch |
0 |
|
|
0 |
12 |
0.00 |
active checkpoint queue latch |
7,835 |
0.00 |
|
0 |
0 |
|
active service list |
50,936 |
0.00 |
|
0 |
1,621 |
0.00 |
archive control |
5 |
0.00 |
|
0 |
0 |
|
begin backup scn array |
72,901 |
0.00 |
0.00 |
0 |
0 |
|
business card |
32 |
0.00 |
|
0 |
0 |
|
cache buffer handles |
331,153 |
0.02 |
0.00 |
0 |
0 |
|
cache buffers chains |
48,189,073 |
0.00 |
0.00 |
0 |
1,201,379 |
0.00 |
cache buffers lru chain |
891,796 |
0.34 |
0.00 |
0 |
991,605 |
0.23 |
cache table scan latch |
0 |
|
|
0 |
10,309 |
0.01 |
channel handle pool latch |
99 |
0.00 |
|
0 |
0 |
|
channel operations parent latch |
490,324 |
0.01 |
0.00 |
0 |
0 |
|
checkpoint queue latch |
671,856 |
0.01 |
0.00 |
0 |
555,469 |
0.02 |
client/application info |
335 |
0.00 |
|
0 |
0 |
|
commit callback allocation |
12 |
0.00 |
|
0 |
0 |
|
compile environment latch |
173,428 |
0.00 |
|
0 |
0 |
|
dml lock allocation |
243,087 |
0.00 |
0.00 |
0 |
0 |
|
dummy allocation |
134 |
0.00 |
|
0 |
0 |
|
enqueue hash chains |
1,539,499 |
0.01 |
0.03 |
0 |
263 |
0.00 |
enqueues |
855,207 |
0.02 |
0.00 |
0 |
0 |
|
error message lists |
64 |
0.00 |
|
0 |
0 |
|
event group latch |
38 |
0.00 |
|
0 |
0 |
|
file cache latch |
4,694 |
0.00 |
|
0 |
0 |
|
gcs drop object freelist |
8,451 |
0.19 |
0.00 |
0 |
0 |
|
gcs opaque info freelist |
38,584 |
0.00 |
0.00 |
0 |
0 |
|
gcs partitioned table hash |
9,801,867 |
0.00 |
|
0 |
0 |
|
gcs remaster request queue |
31 |
0.00 |
|
0 |
0 |
|
gcs remastering latch |
1,014,198 |
0.00 |
0.33 |
0 |
0 |
|
gcs resource freelist |
1,154,551 |
0.03 |
0.00 |
0 |
771,650 |
0.00 |
gcs resource hash |
3,815,373 |
0.02 |
0.00 |
0 |
2 |
0.00 |
gcs resource scan list |
4 |
0.00 |
|
0 |
0 |
|
gcs shadows freelist |
795,482 |
0.00 |
0.00 |
0 |
779,648 |
0.00 |
ges caches resource lists |
209,655 |
0.02 |
0.00 |
0 |
121,613 |
0.01 |
ges deadlock list |
840 |
0.00 |
|
0 |
0 |
|
ges domain table |
366,702 |
0.00 |
|
0 |
0 |
|
ges enqueue table freelist |
487,875 |
0.00 |
|
0 |
0 |
|
ges group table |
543,887 |
0.00 |
|
0 |
0 |
|
ges process hash list |
59,503 |
0.00 |
|
0 |
0 |
|
ges process parent latch |
908,232 |
0.00 |
|
0 |
1 |
0.00 |
ges process table freelist |
73 |
0.00 |
|
0 |
0 |
|
ges resource hash list |
862,590 |
0.02 |
0.28 |
0 |
72,266 |
0.01 |
ges resource scan list |
534 |
0.00 |
|
0 |
0 |
|
ges resource table freelist |
135,406 |
0.00 |
0.00 |
0 |
0 |
|
ges synchronous data |
160 |
0.63 |
0.00 |
0 |
2,954 |
0.07 |
ges timeout list |
3,256 |
0.00 |
|
0 |
4,478 |
0.00 |
global KZLD latch for mem in SGA |
21 |
0.00 |
|
0 |
0 |
|
hash table column usage latch |
59 |
0.00 |
|
0 |
1,279 |
0.00 |
hash table modification latch |
116 |
0.00 |
|
0 |
0 |
|
job workq parent latch |
0 |
|
|
0 |
14 |
0.00 |
job_queue_processes parameter latch |
86 |
0.00 |
|
0 |
0 |
|
kks stats |
384 |
0.00 |
|
0 |
0 |
|
ksuosstats global area |
329 |
0.00 |
|
0 |
0 |
|
ktm global data |
296 |
0.00 |
|
0 |
0 |
|
kwqbsn:qsga |
182 |
0.00 |
|
0 |
0 |
|
lgwr LWN SCN |
6,547 |
0.18 |
0.00 |
0 |
0 |
|
library cache |
235,060 |
0.00 |
0.00 |
0 |
22 |
0.00 |
library cache load lock |
486 |
0.00 |
|
0 |
0 |
|
library cache lock |
49,284 |
0.00 |
|
0 |
0 |
|
library cache lock allocation |
566 |
0.00 |
|
0 |
0 |
|
library cache pin |
27,863 |
0.00 |
0.00 |
0 |
0 |
|
library cache pin allocation |
204 |
0.00 |
|
0 |
0 |
|
list of block allocation |
10,101 |
0.00 |
|
0 |
0 |
|
loader state object freelist |
108 |
0.00 |
|
0 |
0 |
|
longop free list parent |
6 |
0.00 |
|
0 |
6 |
0.00 |
message pool operations parent latch |
1,424 |
0.00 |
|
0 |
0 |
|
messages |
222,581 |
0.00 |
0.00 |
0 |
0 |
|
mostly latch-free SCN |
6,649 |
1.43 |
0.00 |
0 |
0 |
|
multiblock read objects |
29,230 |
0.03 |
0.00 |
0 |
0 |
|
name-service memory objects |
18,842 |
0.00 |
|
0 |
0 |
|
name-service namespace bucket |
56,712 |
0.00 |
|
0 |
0 |
|
name-service namespace objects |
15 |
0.00 |
|
0 |
0 |
|
name-service pending queue |
6,436 |
0.00 |
|
0 |
0 |
|
name-service request |
44 |
0.00 |
|
0 |
0 |
|
name-service request queue |
57,312 |
0.00 |
|
0 |
0 |
|
ncodef allocation latch |
77 |
0.00 |
|
0 |
0 |
|
object queue header heap |
37,721 |
0.00 |
|
0 |
7,457 |
0.00 |
object queue header operation |
2,706,992 |
0.06 |
0.00 |
0 |
0 |
|
object stats modification |
22 |
0.00 |
|
0 |
0 |
|
parallel query alloc buffer |
939 |
0.00 |
|
0 |
0 |
|
parallel query stats |
72 |
0.00 |
|
0 |
0 |
|
parallel txn reco latch |
630 |
0.00 |
|
0 |
0 |
|
parameter list |
193 |
0.00 |
|
0 |
0 |
|
parameter table allocation management |
68 |
0.00 |
|
0 |
0 |
|
post/wait queue |
4,205 |
0.00 |
|
0 |
2,712 |
0.00 |
process allocation |
46,895 |
0.00 |
|
0 |
38 |
0.00 |
process group creation |
73 |
0.00 |
|
0 |
0 |
|
process queue |
175 |
0.00 |
|
0 |
0 |
|
process queue reference |
2,621 |
0.00 |
|
0 |
240 |
62.50 |
qmn task queue latch |
668 |
0.15 |
1.00 |
0 |
0 |
|
query server freelists |
159 |
0.00 |
|
0 |
0 |
|
query server process |
8 |
0.00 |
|
0 |
7 |
0.00 |
queued dump request |
23,628 |
0.00 |
|
0 |
0 |
|
redo allocation |
21,206 |
0.57 |
0.00 |
0 |
4,706,826 |
0.02 |
redo copy |
0 |
|
|
0 |
4,707,106 |
0.01 |
redo writing |
29,944 |
0.01 |
0.00 |
0 |
0 |
|
resmgr group change latch |
69 |
0.00 |
|
0 |
0 |
|
resmgr:actses active list |
137 |
0.00 |
|
0 |
0 |
|
resmgr:actses change group |
52 |
0.00 |
|
0 |
0 |
|
resmgr:free threads list |
130 |
0.00 |
|
0 |
0 |
|
resmgr:schema config |
7 |
0.00 |
|
0 |
0 |
|
row cache objects |
1,644,149 |
0.00 |
0.00 |
0 |
321 |
0.00 |
rules engine rule set statistics |
500 |
0.00 |
|
0 |
0 |
|
sequence cache |
360 |
0.00 |
|
0 |
0 |
|
session allocation |
535,514 |
0.00 |
0.00 |
0 |
0 |
|
session idle bit |
3,262,141 |
0.00 |
0.00 |
0 |
0 |
|
session state list latch |
166 |
0.00 |
|
0 |
0 |
|
session switching |
77 |
0.00 |
|
0 |
0 |
|
session timer |
1,620 |
0.00 |
|
0 |
0 |
|
shared pool |
60,359 |
0.00 |
0.00 |
0 |
0 |
|
shared pool sim alloc |
13 |
0.00 |
|
0 |
0 |
|
shared pool simulator |
4,246 |
0.00 |
|
0 |
0 |
|
simulator hash latch |
1,862,803 |
0.00 |
|
0 |
0 |
|
simulator lru latch |
1,719,480 |
0.01 |
0.00 |
0 |
46,053 |
0.00 |
slave class |
2 |
0.00 |
|
0 |
0 |
|
slave class create |
8 |
12.50 |
1.00 |
0 |
0 |
|
sort extent pool |
1,284 |
0.00 |
|
0 |
0 |
|
state object free list |
4 |
0.00 |
|
0 |
0 |
|
statistics aggregation |
280 |
0.00 |
|
0 |
0 |
|
temp lob duration state obj allocation |
2 |
0.00 |
|
0 |
0 |
|
threshold alerts latch |
202 |
0.00 |
|
0 |
0 |
|
transaction allocation |
211 |
0.00 |
|
0 |
0 |
|
transaction branch allocation |
77 |
0.00 |
|
0 |
0 |
|
undo global data |
779,759 |
0.07 |
0.00 |
0 |
0 |
|
user lock |
102 |
0.00 |
|
0 |
0 |
|
Latch Sleep Breakdown
· ordered by misses desc
Latch Name |
Get Requests |
Misses |
Sleeps |
Spin Gets |
Sleep1 |
Sleep2 |
Sleep3 |
cache buffers lru chain |
891,796 |
3,061 |
1 |
3,060 |
0 |
0 |
0 |
object queue header operation |
2,706,992 |
1,755 |
3 |
1,752 |
0 |
0 |
0 |
KCL gc element parent latch |
2,803,392 |
1,186 |
11 |
1,176 |
0 |
0 |
0 |
cache buffers chains |
48,189,073 |
496 |
1 |
495 |
0 |
0 |
0 |
ges resource hash list |
862,590 |
160 |
44 |
116 |
0 |
0 |
0 |
enqueue hash chains |
1,539,499 |
79 |
2 |
78 |
0 |
0 |
0 |
gcs remastering latch |
1,014,198 |
3 |
1 |
2 |
0 |
0 |
0 |
qmn task queue latch |
668 |
1 |
1 |
0 |
0 |
0 |
0 |
slave class create |
8 |
1 |
1 |
0 |
0 |
0 |
0 |
Latch Miss Sources
· only latches with sleeps are shown
· ordered by name, sleeps desc
Latch Name |
Where |
NoWait Misses |
Sleeps |
Waiter Sleeps |
KCL gc element parent latch |
kclrwrite |
0 |
8 |
0 |
KCL gc element parent latch |
kclnfndnewm |
0 |
4 |
6 |
KCL gc element parent latch |
KCLUNLNK |
0 |
1 |
1 |
KCL gc element parent latch |
kclbla |
0 |
1 |
0 |
KCL gc element parent latch |
kclulb |
0 |
1 |
1 |
KCL gc element parent latch |
kclzcl |
0 |
1 |
0 |
cache buffers chains |
kcbnew: new latch again |
0 |
2 |
0 |
cache buffers chains |
kclwrt |
0 |
1 |
0 |
cache buffers lru chain |
kcbzgws |
0 |
1 |
0 |
enqueue hash chains |
ksqcmi: if lk mode not requested |
0 |
2 |
0 |
event range base latch |
No latch |
0 |
1 |
1 |
gcs remastering latch |
69 |
0 |
1 |
0 |
ges resource hash list |
kjlmfnd: search for lockp by rename and inst id |
0 |
23 |
0 |
ges resource hash list |
kjakcai: search for resp by resname |
0 |
13 |
0 |
ges resource hash list |
kjrmas1: lookup master node |
0 |
5 |
0 |
ges resource hash list |
kjlrlr: remove lock from resource queue |
0 |
2 |
33 |
ges resource hash list |
kjcvscn: remove from scan queue |
0 |
1 |
0 |
object queue header operation |
kcbo_switch_q_bg |
0 |
3 |
0 |
object queue header operation |
kcbo_switch_mq_bg |
0 |
2 |
4 |
object queue header operation |
kcbw_unlink_q |
0 |
2 |
0 |
object queue header operation |
kcbw_link_q |
0 |
1 |
0 |
slave class create |
ksvcreate |
0 |
1 |
0 |
Parent Latch Statistics
No data exists for this section of the report.
Child Latch Statistics
No data exists for this section of the report.
Segment Statistics
· Segments by Buffer Busy Waits
· Segments by Global Cache Buffer Busy
· Segments by CR Blocks Received
· Segments by Current Blocks Received
Segments by Logical Reads
· Total Logical Reads: 16,648,792
· Captured Segments account for 85.2% of Total
Owner |
Tablespace Name |
Object Name |
Subobject Name |
Obj. Type |
Logical Reads |
%Total |
ICCI01 |
ICCIDAT01 |
ICCICCS_PK |
|
INDEX |
1,544,848 |
9.28 |
ICCI01 |
ICCIDAT01 |
CUSCAD_TMP |
|
TABLE |
1,349,536 |
8.11 |
ICCI01 |
ICCIDAT01 |
ICCIFNSACT_PK |
|
INDEX |
1,268,400 |
7.62 |
ICCI01 |
ICCIDAT01 |
IND_OLDNEWACT |
|
INDEX |
1,071,072 |
6.43 |
ICCI01 |
ICCIDAT01 |
CUID_PK |
|
INDEX |
935,584 |
5.62 |
Segments by Physical Reads
· Total Physical Reads: 322,678
· Captured Segments account for 64.2% of Total
Owner |
Tablespace Name |
Object Name |
Subobject Name |
Obj. Type |
Physical Reads |
%Total |
ICCI01 |
ICCIDAT01 |
CUID_TMP |
|
TABLE |
116,417 |
36.08 |
ICCI01 |
ICCIDAT01 |
CUMI_TMP |
|
TABLE |
44,086 |
13.66 |
ICCI01 |
ICCIDAT01 |
CUSM_TMP |
|
TABLE |
26,078 |
8.08 |
ICCI01 |
ICCIDAT01 |
CUSVAA_TMP_PK |
|
INDEX |
19,554 |
6.06 |
ICCI01 |
ICCIDAT01 |
CUID |
|
TABLE |
259 |
0.08 |
Segments by Row Lock Waits
當一個程式予在正被其它程式鎖住的資料行上獲得排它鎖時發生這種等待。這種等待經常是由於在一個有主鍵索引的表上做大量INSERT操作。
No data exists for this section of the report.
Segments by ITL Waits
No data exists for this section of the report.
Segments by Buffer Busy Waits
No data exists for this section of the report.
Segments by Global Cache Buffer Busy
· % of Capture shows % of GC Buffer Busy for each top segment compared
· with GC Buffer Busy for all segments captured by the Snapshot
Owner |
Tablespace Name |
Object Name |
Subobject Name |
Obj. Type |
GC Buffer Busy |
% of Capture |
SYS |
SYSTEM |
TSQ$ |
|
TABLE |
2 |
100.00 |
Segments by CR Blocks Received
· Total CR Blocks Received: 4,142
· Captured Segments account for 95.6% of Total
Owner |
Tablespace Name |
Object Name |
Subobject Name |
Obj. Type |
CR Blocks Received |
%Total |
SYS |
SYSTEM |
USER$ |
|
TABLE |
1,001 |
24.17 |
SYS |
SYSTEM |
TSQ$ |
|
TABLE |
722 |
17.43 |
SYS |
SYSTEM |
SEG$ |
|
TABLE |
446 |
10.77 |
SYS |
SYSTEM |
OBJ$ |
|
TABLE |
264 |
6.37 |
SYS |
SYSTEM |
I_OBJ2 |
|
INDEX |
174 |
4.20 |
Segments by Current Blocks Received
· Total Current Blocks Received: 15,502
· Captured Segments account for 84.8% of Total
Owner |
Tablespace Name |
Object Name |
Subobject Name |
Obj. Type |
Current Blocks Received |
%Total |
ICCI01 |
ICCIDAT01 |
CUSM_TMP |
|
TABLE |
5,764 |
37.18 |
ICCI01 |
ICCIDAT01 |
CUMI_TMP |
|
TABLE |
2,794 |
18.02 |
ICCI01 |
ICCIDAT01 |
CUID_TMP |
|
TABLE |
2,585 |
16.68 |
SYS |
SYSTEM |
SEG$ |
|
TABLE |
361 |
2.33 |
SYS |
SYSTEM |
TSQ$ |
|
TABLE |
361 |
2.33 |
Dictionary Cache Statistics
· Dictionary Cache Stats (RAC)
Dictionary Cache Stats
· "Pct Misses" should be very low (< 2% in most cases)
· "Final Usage" is the number of cache entries being used
Cache |
Get Requests |
Pct Miss |
Scan Reqs |
Pct Miss |
Mod Reqs |
Final Usage |
dc_awr_control |
86 |
0.00 |
0 |
|
4 |
1 |
dc_constraints |
59 |
91.53 |
0 |
|
20 |
1,350 |
dc_files |
23 |
0.00 |
0 |
|
0 |
23 |
dc_global_oids |
406 |
0.00 |
0 |
|
0 |
35 |
dc_histogram_data |
673 |
0.15 |
0 |
|
0 |
1,555 |
dc_histogram_defs |
472 |
24.36 |
0 |
|
0 |
4,296 |
dc_object_grants |
58 |
0.00 |
0 |
|
0 |
154 |
dc_object_ids |
1,974 |
6.13 |
0 |
|
0 |
1,199 |
dc_objects |
955 |
19.58 |
0 |
|
56 |
2,064 |
dc_profiles |
30 |
0.00 |
0 |
|
0 |
1 |
dc_rollback_segments |
3,358 |
0.00 |
0 |
|
0 |
37 |
dc_segments |
2,770 |
2.56 |
0 |
|
1,579 |
1,312 |
dc_sequences |
9 |
33.33 |
0 |
|
9 |
5 |
dc_table_scns |
6 |
100.00 |
0 |
|
0 |
0 |
dc_tablespace_quotas |
1,558 |
28.50 |
0 |
|
1,554 |
3 |
dc_tablespaces |
346,651 |
0.00 |
0 |
|
0 |
7 |
dc_usernames |
434 |
0.00 |
0 |
|
0 |
14 |
dc_users |
175,585 |
0.00 |
0 |
|
0 |
43 |
outstanding_alerts |
57 |
71.93 |
0 |
|
0 |
1 |
Back to Dictionary Cache Statistics
Dictionary Cache Stats (RAC)
Cache |
GES Requests |
GES Conflicts |
GES Releases |
dc_awr_control |
8 |
0 |
0 |
dc_constraints |
88 |
22 |
0 |
dc_histogram_defs |
115 |
0 |
0 |
dc_object_ids |
143 |
101 |
0 |
dc_objects |
253 |
111 |
0 |
dc_segments |
3,228 |
49 |
0 |
dc_sequences |
17 |
3 |
0 |
dc_table_scns |
6 |
0 |
0 |
dc_tablespace_quotas |
3,093 |
441 |
0 |
dc_users |
8 |
1 |
0 |
outstanding_alerts |
113 |
41 |
0 |
Back to Dictionary Cache Statistics
Library Cache Statistics
· Library Cache Activity (RAC)
Library Cache Activity
· "Pct Misses" should be very low
Namespace |
Get Requests |
Pct Miss |
Pin Requests |
Pct Miss |
Reloads |
Invali- dations |
BODY |
105 |
0.00 |
247 |
0.00 |
0 |
0 |
CLUSTER |
3 |
0.00 |
4 |
0.00 |
0 |
0 |
INDEX |
13 |
46.15 |
26 |
42.31 |
5 |
0 |
SQL AREA |
56 |
100.00 |
1,857,002 |
0.02 |
32 |
12 |
TABLE/PROCEDURE |
179 |
35.75 |
3,477 |
8.02 |
63 |
0 |
TRIGGER |
323 |
0.00 |
386 |
0.00 |
0 |
0 |
Back to Library Cache Statistics
Library Cache Activity (RAC)
Namespace |
GES Lock Requests |
GES Pin Requests |
GES Pin Releases |
GES Inval Requests |
GES Invali- dations |
BODY |
5 |
0 |
0 |
0 |
0 |
CLUSTER |
4 |
0 |
0 |
0 |
0 |
INDEX |
26 |
22 |
6 |
17 |
0 |
TABLE/PROCEDURE |
1,949 |
285 |
63 |
244 |
0 |
Back to Library Cache Statistics
Memory Statistics
Process Memory Summary
· 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
|
Category |
Alloc (MB) |
Used (MB) |
Avg Alloc (MB) |
Std Dev Alloc (MB) |
Max Alloc (MB) |
Hist Max Alloc (MB) |
Num Proc |
Num Alloc |
B |
Other |
136.42 |
|
5.25 |
8.55 |
24 |
27 |
26 |
26 |
|
Freeable |
13.50 |
0.00 |
1.50 |
1.11 |
3 |
|
9 |
9 |
|
SQL |
0.33 |
0.16 |
0.03 |
0.03 |
0 |
2 |
12 |
10 |
|
PL/SQL |
0.12 |
0.06 |
0.01 |
0.01 |
0 |
0 |
24 |
24 |
E |
Other |
138.65 |
|
4.78 |
8.20 |
24 |
27 |
29 |
29 |
|
Freeable |
14.94 |
0.00 |
1.36 |
1.04 |
3 |
|
11 |
11 |
|
SQL |
0.39 |
0.19 |
0.03 |
0.03 |
0 |
2 |
15 |
12 |
|
PL/SQL |
0.18 |
0.11 |
0.01 |
0.01 |
0 |
0 |
27 |
26 |
SGA Memory Summary
SGA regions |
Begin Size (Bytes) |
End Size (Bytes) (if different) |
Database Buffers |
3,506,438,144 |
|
Fixed Size |
2,078,368 |
|
Redo Buffers |
14,696,448 |
|
Variable Size |
771,754,336 |
|
SGA breakdown difference
· 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 |
16.00 |
16.00 |
0.00 |
large |
PX msg pool |
1.03 |
1.03 |
0.00 |
large |
free memory |
14.97 |
14.97 |
0.00 |
shared |
ASH buffers |
15.50 |
15.50 |
0.00 |
shared |
CCursor |
8.58 |
8.85 |
3.09 |
shared |
KQR L PO |
8.75 |
8.80 |
0.55 |
shared |
db_block_hash_buckets |
22.50 |
22.50 |
0.00 |
shared |
free memory |
371.80 |
369.61 |
-0.59 |
shared |
gcs resources |
66.11 |
66.11 |
0.00 |
shared |
gcs shadows |
41.65 |
41.65 |
0.00 |
shared |
ges big msg buffers |
13.75 |
13.75 |
0.00 |
shared |
ges enqueues |
7.44 |
7.56 |
1.63 |
shared |
ges reserved msg buffers |
7.86 |
7.86 |
0.00 |
shared |
library cache |
10.78 |
10.93 |
1.41 |
shared |
row cache |
7.16 |
7.16 |
0.00 |
shared |
sql area |
27.49 |
28.50 |
3.67 |
|
buffer_cache |
3,344.00 |
3,344.00 |
0.00 |
|
fixed_sga |
1.98 |
1.98 |
0.00 |
|
log_buffer |
14.02 |
14.02 |
0.00 |
Streams Statistics
· Rule Set
Streams CPU/IO Usage
No data exists for this section of the report.
Streams Capture
No data exists for this section of the report.
Streams Apply
No data exists for this section of the report.
Buffered Queues
No data exists for this section of the report.
Buffered Subscribers
No data exists for this section of the report.
Rule Set
No data exists for this section of the report.
Resource Limit Stats
· only rows with Current or Maximum Utilization > 80% of Limit are shown
· ordered by resource name
Resource Name |
Current Utilization |
Maximum Utilization |
Initial Allocation |
Limit |
gcs_resources |
349,392 |
446,903 |
450063 |
450063 |
gcs_shadows |
400,300 |
447,369 |
450063 |
450063 |
init.ora Parameters
Parameter Name |
Begin value |
End value (if different) |
audit_file_dest |
/oracle/app/oracle/admin/ICCI/adump |
|
background_dump_dest |
/oracle/app/oracle/admin/ICCI/bdump |
|
cluster_database |
TRUE |
|
cluster_database_instances |
2 |
|
compatible |
10.2.0.3.0 |
|
control_files |
/dev/rora_CTL01, /dev/rora_CTL02, /dev/rora_CTL03 |
|
core_dump_dest |
/oracle/app/oracle/admin/ICCI/cdump |
|
db_block_size |
8192 |
|
db_domain |
|
|
db_file_multiblock_read_count |
16 |
|
db_name |
ICCI |
|
dispatchers |
(PROTOCOL=TCP) (SERVICE=ICCIXDB) |
|
instance_number |
1 |
|
job_queue_processes |
10 |
|
open_cursors |
800 |
|
pga_aggregate_target |
1073741824 |
|
processes |
500 |
|
remote_listener |
LISTENERS_ICCI |
|
remote_login_passwordfile |
EXCLUSIVE |
|
sga_max_size |
4294967296 |
|
sga_target |
4294967296 |
|
sort_area_size |
196608 |
|
spfile |
/dev/rora_SPFILE |
|
thread |
1 |
|
undo_management |
AUTO |
|
undo_retention |
900 |
|
undo_tablespace |
UNDOTBS1 |
|
user_dump_dest |
/oracle/app/oracle/admin/ICCI/udump |
|
More RAC Statistics
Global Enqueue Statistics
Statistic |
Total |
per Second |
per Trans |
acks for commit broadcast(actual) |
18,537 |
3.92 |
3.31 |
acks for commit broadcast(logical) |
21,016 |
4.45 |
3.75 |
broadcast msgs on commit(actual) |
5,193 |
1.10 |
0.93 |
broadcast msgs on commit(logical) |
5,491 |
1.16 |
0.98 |
broadcast msgs on commit(wasted) |
450 |
0.10 |
0.08 |
dynamically allocated gcs resources |
0 |
0.00 |
0.00 |
dynamically allocated gcs shadows |
0 |
0.00 |
0.00 |
false posts waiting for scn acks |
0 |
0.00 |
0.00 |
flow control messages received |
0 |
0.00 |
0.00 |
flow control messages sent |
2 |
0.00 |
0.00 |
gcs assume cvt |
0 |
0.00 |
0.00 |
gcs assume no cvt |
9,675 |
2.05 |
1.73 |
gcs ast xid |
1 |
0.00 |
0.00 |
gcs blocked converts |
7,099 |
1.50 |
1.27 |
gcs blocked cr converts |
8,442 |
1.79 |
1.51 |
gcs compatible basts |
45 |
0.01 |
0.01 |
gcs compatible cr basts (global) |
273 |
0.06 |
0.05 |
gcs compatible cr basts (local) |
12,593 |
2.66 |
2.25 |
gcs cr basts to PIs |
0 |
0.00 |
0.00 |
gcs cr serve without current lock |
0 |
0.00 |
0.00 |
gcs dbwr flush pi msgs |
223 |
0.05 |
0.04 |
gcs dbwr write request msgs |
223 |
0.05 |
0.04 |
gcs error msgs |
0 |
0.00 |
0.00 |
gcs forward cr to pinged instance |
0 |
0.00 |
0.00 |
gcs immediate (compatible) converts |
2,998 |
0.63 |
0.54 |
gcs immediate (null) converts |
170,925 |
36.16 |
30.53 |
gcs immediate cr (compatible) converts |
0 |
0.00 |
0.00 |
gcs immediate cr (null) converts |
722,748 |
152.88 |
129.11 |
gcs indirect ast |
306,817 |
64.90 |
54.81 |
gcs lms flush pi msgs |
0 |
0.00 |
0.00 |
gcs lms write request msgs |
189 |
0.04 |
0.03 |
gcs msgs process time(ms) |
16,164 |
3.42 |
2.89 |
gcs msgs received |
1,792,132 |
379.09 |
320.14 |
gcs out-of-order msgs |
0 |
0.00 |
0.00 |
gcs pings refused |
0 |
0.00 |
0.00 |
gcs pkey conflicts retry |
0 |
0.00 |
0.00 |
gcs queued converts |
2 |
0.00 |
0.00 |
gcs recovery claim msgs |
0 |
0.00 |
0.00 |
gcs refuse xid |
0 |
0.00 |
0.00 |
gcs regular cr |
0 |
0.00 |
0.00 |
gcs retry convert request |
0 |
0.00 |
0.00 |
gcs side channel msgs actual |
437 |
0.09 |
0.08 |
gcs side channel msgs logical |
21,086 |
4.46 |
3.77 |
gcs stale cr |
3,300 |
0.70 |
0.59 |
gcs undo cr |
5 |
0.00 |
0.00 |
gcs write notification msgs |
23 |
0.00 |
0.00 |
gcs writes refused |
3 |
0.00 |
0.00 |
ges msgs process time(ms) |
1,289 |
0.27 |
0.23 |
ges msgs received |
138,891 |
29.38 |
24.81 |
global posts dropped |
0 |
0.00 |
0.00 |
global posts queue time |
0 |
0.00 |
0.00 |
global posts queued |
0 |
0.00 |
0.00 |
global posts requested |
0 |
0.00 |
0.00 |
global posts sent |
0 |
0.00 |
0.00 |
implicit batch messages received |
81,181 |
17.17 |
14.50 |
implicit batch messages sent |
19,561 |
4.14 |
3.49 |
lmd msg send time(ms) |
0 |
0.00 |
0.00 |
lms(s) msg send time(ms) |
0 |
0.00 |
0.00 |
messages flow controlled |
15,306 |
3.24 |
2.73 |
messages queue sent actual |
108,411 |
22.93 |
19.37 |
messages queue sent logical |
222,518 |
47.07 |
39.75 |
messages received actual |
474,202 |
100.31 |
84.71 |
messages received logical |
1,931,144 |
408.50 |
344.97 |
messages sent directly |
25,742 |
5.45 |
4.60 |
messages sent indirectly |
137,725 |
29.13 |
24.60 |
messages sent not implicit batched |
88,859 |
18.80 |
15.87 |
messages sent pbatched |
1,050,224 |
222.16 |
187.61 |
msgs causing lmd to send msgs |
61,682 |
13.05 |
11.02 |
msgs causing lms(s) to send msgs |
85,978 |
18.19 |
15.36 |
msgs received queue time (ms) |
911,013 |
192.71 |
162.74 |
msgs received queued |
1,931,121 |
408.50 |
344.97 |
msgs sent queue time (ms) |
5,651 |
1.20 |
1.01 |
msgs sent queue time on ksxp (ms) |
66,767 |
14.12 |
11.93 |
msgs sent queued |
215,124 |
45.51 |
38.43 |
msgs sent queued on ksxp |
243,729 |
51.56 |
43.54 |
process batch messages received |
120,003 |
25.38 |
21.44 |
process batch messages sent |
181,019 |
38.29 |
32.34 |
Global CR Served Stats
Statistic |
Total |
CR Block Requests |
10,422 |
CURRENT Block Requests |
251 |
Data Block Requests |
10,422 |
Undo Block Requests |
2 |
TX Block Requests |
20 |
Current Results |
10,664 |
Private results |
4 |
Zero Results |
5 |
Disk Read Results |
0 |
Fail Results |
0 |
Fairness Down Converts |
1,474 |
Fairness Clears |
0 |
Free GC Elements |
0 |
Flushes |
370 |
Flushes Queued |
0 |
Flush Queue Full |
0 |
Flush Max Time (us) |
0 |
Light Works |
2 |
Errors |
0 |
Global CURRENT Served Stats
· 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 |
17,534 |
99.96 |
0.01 |
0.03 |
0.00 |
0.00 |
Flushes |
77 |
48.05 |
46.75 |
5.19 |
0.00 |
0.00 |
Writes |
255 |
5.49 |
53.73 |
40.00 |
0.78 |
0.00 |
Global Cache Transfer Stats
· 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 No |
Block Class |
Blocks Received |
% Immed |
% Busy |
% Congst |
Blocks Received |
% Immed |
% Busy |
% Congst |
2 |
data block |
3,945 |
87.20 |
12.80 |
0.00 |
13,324 |
99.71 |
0.26 |
0.04 |
2 |
Others |
191 |
100.00 |
0.00 |
0.00 |
2,190 |
96.48 |
3.52 |
0.00 |
2 |
undo header |
11 |
100.00 |
0.00 |
0.00 |
2 |
100.00 |
0.00 |
0.00 |
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23490154/viewspace-1717083/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 詳細的AWR解析報告
- 手工生成AWR分析報告
- oracle AWR報告提取分析Oracle
- ORACLE AWR報告詳細分析Oracle
- Oracle AWR與ASH效能報告深入解析Oracle
- Oracle AWR報告分析之–SQL ordered byOracleSQL
- Oracle 10g AWR 報告分析Oracle 10g
- 星球上最詳細的AWR解析報告
- Oracle的AWR報告分析(簡潔版)Oracle
- 【效能調優】Oracle AWR報告指標全解析Oracle指標
- Oracle_AWR報告分析指南(經典版)Oracle
- 對於AWR報告的幾個片段分析。
- AWR 報告修改moving window 出錯分析
- Oracle生成awr報告Oracle
- mysql-awr報告MySql
- Oracle 生成awr報告Oracle
- oracle效能awr報告Oracle
- AWR報告分析之二:ges inquiry response 過高UI
- Oracle的AWR報告分析(經典串聯版)Oracle
- 關於類似於awr的效能分析報告
- Oracle AWR 介紹及報告分析(2) finalOracle
- Oracle AWR 介紹及報告分析(1) finalOracle
- 【AWR】Oracle批量生成awr報告指令碼Oracle指令碼
- AWR報告分析之三:cursor: pin S 的原理與案例分析
- AWR報告基礎操作
- Oracle AWR報告大綱Oracle
- oracle 產生awr 報告Oracle
- AWR報告分析之二:ges inquiry response 過高-eygleUI
- AWR報告分析之三:cursor: pin S 的原理與案例分析-eygle
- 【AWR】自動生成AWR報告指令碼以及用法指令碼
- 【深度長文】循序漸進解讀Oracle AWR效能分析報告Oracle
- AWR報告的收集和分析執行計劃的方式
- 生成awr報告的指令碼指令碼
- 自動生成AWR HTML報告HTML
- oracle特性之AWR報告2Oracle
- 快捷生出awr和awrsql報告SQL
- AWR報告分析之一:高 DB CPU 消耗的效能根源-eygle
- 轉載詳細的Oracle ASH/AWR介紹及報告分析Oracle