AWR解析報告分析

us_yunleiwang發表於2015-06-30

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

·         Wait Events Statistics

·         SQL Statistics

·         Instance Activity Statistics

·         IO Stats

·         Buffer Pool Statistics

·         Advisory Statistics

·         Wait Statistics

·         Undo Statistics

·         Latch Statistics

·         Segment Statistics

·         Dictionary Cache Statistics

·         Library Cache Statistics

·         Memory Statistics

·         Streams Statistics

·         Resource Limit Statistics

·         init.ora Parameters


Wait Events Statistics

·         Time Model Statistics

·         Wait Class

·         Wait Events

·         Background Wait Events

·         Operating System Statistics

·         Service Statistics

·         Service Wait Class Stats

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 Elapsed Time

·         SQL ordered by CPU Time

·         SQL ordered by Gets

·         SQL ordered by Reads

·         SQL ordered by Executions

·         SQL ordered by Parse Calls

·         SQL ordered by Sharable Memory

·         SQL ordered by Version Count

·         SQL ordered by Cluster Wait Time

·         Complete List of SQL Text

本節按各種資源分別列出對資源消耗最嚴重的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

d8z0u8hgj8xdy

cuidmain@HPGICCI1 (TNS V1-V3)

insert into CUID select CUID_...

76

75

172,329

0.00

11.52

4vja2k2gdtyup

load_fnsact@HPGICCI1 (TNS V1-V3)

insert into ICCICCS values (:...

58

42

1

58.04

8.75

569r5k05drsj7

cumimain@HPGICCI1 (TNS V1-V3)

insert into CUMI select CUSV_...

51

42

1

50.93

7.68

ackxqhnktxnbc

cusmmain@HPGICCI1 (TNS V1-V3)

insert into CUSM select CUSM_...

38

36

166,069

0.00

5.67

7gtztzv329wg0

 

select c.name, u.name from co...

35

3

1

35.00

5.28

6z06gcfw39pkd

SQL*Plus

SELECT F.TABLESPACE_NAME, TO_...

23

23

172,329

0.00

3.46

1dm3bq36vu3g8

load_fnsact@HPGICCI1 (TNS V1-V3)

insert into iccifnsact values...

15

11

5

2.98

2.25

djs2w2f17nw2z

 

DECLARE job BINARY_INTEGER := ...

14

14

172,983

0.00

2.16

7wwv1ybs9zguz

load_fnsact@HPGICCI1 (TNS V1-V3)

update ICCIFNSACT set BORM_AD...

13

13

172,337

0.00

2.00

gmn2w09rdxn14

load_oldnewact@HPGICCI1 (TNS V1-V3)

insert into OLDNEWACT values ...

13

13

166,051

0.00

1.89

chjmy0dxf9mbj

icci_migact@HPGICCI1 (TNS V1-V3)

insert into ICCICCS values (:...

10

4

1

9.70

1.46

0yv9t4qb1zb2b

cuidmain@HPGICCI1 (TNS V1-V3)

select CUID_CUST_NO , CUID_ID_...

10

8

5

1.91

1.44

1crajpb7j5tyz

 

INSERT INTO STATS$SGA_TARGET_A...

8

8

172,329

0.00

1.25

38apjgr0p55ns

load_fnsact@HPGICCI1 (TNS V1-V3)

update ICCICCS set CCSMAXOVER...

8

8

172,983

0.00

1.16

5c4qu2zmj3gux

load_fnsact@HPGICCI1 (TNS V1-V3)

select * from ICCIPRODCODE wh...

Back to SQL Statistics 

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

4vja2k2gdtyup

load_fnsact@HPGICCI1 (TNS V1-V3)

insert into ICCICCS values (:...

57

93

1

57.31

14.10

d8z0u8hgj8xdy

cuidmain@HPGICCI1 (TNS V1-V3)

insert into CUID select CUID_...

42

51

1

42.43

7.68

ackxqhnktxnbc

cusmmain@HPGICCI1 (TNS V1-V3)

insert into CUSM select CUSM_...

42

58

1

42.01

8.75

569r5k05drsj7

cumimain@HPGICCI1 (TNS V1-V3)

insert into CUMI select CUSV_...

36

38

166,069

0.00

5.67

7gtztzv329wg0

 

select c.name, u.name from co...

23

23

172,329

0.00

3.46

1dm3bq36vu3g8

load_fnsact@HPGICCI1 (TNS V1-V3)

insert into iccifnsact values...

14

14

172,983

0.00

2.16

7wwv1ybs9zguz

load_fnsact@HPGICCI1 (TNS V1-V3)

update ICCIFNSACT set BORM_AD...

13

13

172,337

0.00

2.00

gmn2w09rdxn14

load_oldnewact@HPGICCI1 (TNS V1-V3)

insert into OLDNEWACT values ...

13

13

166,051

0.00

1.89

chjmy0dxf9mbj

icci_migact@HPGICCI1 (TNS V1-V3)

insert into ICCICCS values (:...

11

15

5

2.23

2.25

djs2w2f17nw2z

 

DECLARE job BINARY_INTEGER := ...

8

8

172,329

0.00

1.25

38apjgr0p55ns

load_fnsact@HPGICCI1 (TNS V1-V3)

update ICCICCS set CCSMAXOVER...

8

10

5

1.60

1.44

1crajpb7j5tyz

 

INSERT INTO STATS$SGA_TARGET_A...

8

8

172,983

0.00

1.16

5c4qu2zmj3gux

load_fnsact@HPGICCI1 (TNS V1-V3)

select * from ICCIPRODCODE wh...

4

10

1

3.54

1.46

0yv9t4qb1zb2b

cuidmain@HPGICCI1 (TNS V1-V3)

select CUID_CUST_NO , CUID_ID_...

3

35

1

3.13

5.28

6z06gcfw39pkd

SQL*Plus

SELECT F.TABLESPACE_NAME, TO_...

Back to SQL Statistics 

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

4vja2k2gdtyup

load_fnsact@HPGICCI1 (TNS V1-V3)

insert into ICCICCS values (:...

2,064,414

1

2,064,414.00

12.40

57.31

93.50

d8z0u8hgj8xdy

cuidmain@HPGICCI1 (TNS V1-V3)

insert into CUID select CUID_...

1,826,869

166,069

11.00

10.97

35.84

37.60

7gtztzv329wg0

 

select c.name, u.name from co...

1,427,648

172,337

8.28

8.58

12.97

13.29

gmn2w09rdxn14

load_oldnewact@HPGICCI1 (TNS V1-V3)

insert into OLDNEWACT values ...

1,278,667

172,329

7.42

7.68

22.85

22.94

1dm3bq36vu3g8

load_fnsact@HPGICCI1 (TNS V1-V3)

insert into iccifnsact values...

1,216,367

1

1,216,367.00

7.31

42.43

50.93

ackxqhnktxnbc

cusmmain@HPGICCI1 (TNS V1-V3)

insert into CUSM select CUSM_...

1,107,305

1

1,107,305.00

6.65

42.01

58.04

569r5k05drsj7

cumimain@HPGICCI1 (TNS V1-V3)

insert into CUMI select CUSV_...

898,868

172,983

5.20

5.40

14.28

14.34

7wwv1ybs9zguz

load_fnsact@HPGICCI1 (TNS V1-V3)

update ICCIFNSACT set BORM_AD...

711,450

166,051

4.28

4.27

12.52

12.55

chjmy0dxf9mbj

icci_migact@HPGICCI1 (TNS V1-V3)

insert into ICCICCS values (:...

692,996

172,329

4.02

4.16

8.31

8.31

38apjgr0p55ns

load_fnsact@HPGICCI1 (TNS V1-V3)

update ICCICCS set CCSMAXOVER...

666,748

166,052

4.02

4.00

6.36

6.36

7v9dyf5r424yh

icci_migact@HPGICCI1 (TNS V1-V3)

select NEWACTNO into :b0 from...

345,357

172,983

2.00

2.07

7.70

7.71

5c4qu2zmj3gux

load_fnsact@HPGICCI1 (TNS V1-V3)

select * from ICCIPRODCODE wh...

231,756

51,633

4.49

1.39

5.75

5.83

49ms69srnaxzj

load_fnsact@HPGICCI1 (TNS V1-V3)

insert into ICCIRPYV values (...

Back to SQL Statistics 

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

d8z0u8hgj8xdy

cuidmain@HPGICCI1 (TNS V1-V3)

insert into CUID select CUID_...

50,646

1

50,646.00

15.70

3.54

9.70

0yv9t4qb1zb2b

cuidmain@HPGICCI1 (TNS V1-V3)

select CUID_CUST_NO , CUID_ID_...

24,507

1

24,507.00

7.59

42.01

58.04

569r5k05drsj7

cumimain@HPGICCI1 (TNS V1-V3)

insert into CUMI select CUSV_...

21,893

1

21,893.00

6.78

42.43

50.93

ackxqhnktxnbc

cusmmain@HPGICCI1 (TNS V1-V3)

insert into CUSM select CUSM_...

19,761

1

19,761.00

6.12

2.14

6.04

a7nh7j8zmfrzw

cumimain@HPGICCI1 (TNS V1-V3)

select CUSV_CUST_NO from CUMI...

19,554

1

19,554.00

6.06

1.27

3.83

38gak8u2qm11w

SQL*Plus

select count(*) from CUSVAA_T...

6,342

1

6,342.00

1.97

3.13

35.00

6z06gcfw39pkd

SQL*Plus

SELECT F.TABLESPACE_NAME, TO_...

4,385

1

4,385.00

1.36

1.59

2.43

cp5duhcsj72q0

cusmmain@HPGICCI1 (TNS V1-V3)

select CUSM_CUST_ACCT_NO from...

63

5

12.60

0.02

11.17

14.91

djs2w2f17nw2z

 

DECLARE job BINARY_INTEGER := ...

35

1

35.00

0.01

0.08

0.67

1uk5m5qbzj1vt

SQL*Plus

BEGIN dbms_workload_repository...

Back to SQL Statistics 

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

5c4qu2zmj3gux

load_fnsact@HPGICCI1 (TNS V1-V3)

select * from ICCIPRODCODE wh...

172,983

172,329

1.00

0.00

0.00

7wwv1ybs9zguz

load_fnsact@HPGICCI1 (TNS V1-V3)

update ICCIFNSACT set BORM_AD...

172,337

172,337

1.00

0.00

0.00

gmn2w09rdxn14

load_oldnewact@HPGICCI1 (TNS V1-V3)

insert into OLDNEWACT values ...

172,329

172,329

1.00

0.00

0.00

1dm3bq36vu3g8

load_fnsact@HPGICCI1 (TNS V1-V3)

insert into iccifnsact values...

172,329

172,329

1.00

0.00

0.00

38apjgr0p55ns

load_fnsact@HPGICCI1 (TNS V1-V3)

update ICCICCS set CCSMAXOVER...

172,329

6,286

0.04

0.00

0.00

4vja2k2gdtyup

load_fnsact@HPGICCI1 (TNS V1-V3)

insert into ICCICCS values (:...

166,069

166,069

1.00

0.00

0.00

7gtztzv329wg0

 

select c.name, u.name from co...

166,052

166,052

1.00

0.00

0.00

7v9dyf5r424yh

icci_migact@HPGICCI1 (TNS V1-V3)

select NEWACTNO into :b0 from...

166,051

166,051

1.00

0.00

0.00

chjmy0dxf9mbj

icci_migact@HPGICCI1 (TNS V1-V3)

insert into ICCICCS values (:...

51,740

51,740

1.00

0.00

0.00

bu8tnqr3xv25q

load_fnsact@HPGICCI1 (TNS V1-V3)

select count(*) into :b0 fro...

51,633

51,633

1.00

0.00

0.00

49ms69srnaxzj

load_fnsact@HPGICCI1 (TNS V1-V3)

insert into ICCIRPYV values (...

Back to SQL Statistics 

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

7gtztzv329wg0

 

select c.name, u.name from co...

6,304

6,304

3.45

2ym6hhaq30r73

 

select type#, blocks, extents,...

2,437

2,438

1.33

bsa0wjtftg3uw

 

select file# from file$ where ...

1,568

1,568

0.86

9qgtwh66xg6nz

 

update seg$ set type#=:4, bloc...

1,554

1,554

0.85

aq4js2gkfjru8

 

update tsq$ set blocks=:3, max...

444

444

0.24

104pd9mm3fh9p

 

select blocks, maxblocks, gran...

421

421

0.23

350f5yrnnmshs

 

lock table sys.mon_mods$ in ex...

421

421

0.23

g00cj285jmgsw

 

update sys.mon_mods$ set inser...

86

86

0.05

3m8smr0v7v1m6

 

INSERT INTO sys.wri$_adv_messa...

81

81

0.04

f80h0xb1qvbsk

 

SELECT sys.wri$_adv_seq_msggro...

Back to SQL Statistics 

SQL ordered by Sharable Memory

No data exists for this section of the report.

Back to SQL Statistics 

SQL ordered by Version Count

No data exists for this section of the report.

Back to SQL Statistics 

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

d8z0u8hgj8xdy

cuidmain@HPGICCI1 (TNS V1-V3)

insert into CUID select CUID_...

4.21

7.25

58.04

42.01

1

569r5k05drsj7

cumimain@HPGICCI1 (TNS V1-V3)

insert into CUMI select CUSV_...

3.62

7.12

50.93

42.43

1

ackxqhnktxnbc

cusmmain@HPGICCI1 (TNS V1-V3)

insert into CUSM select CUSM_...

2.39

6.35

37.60

35.84

166,069

7gtztzv329wg0

 

select c.name, u.name from co...

2.38

3.12

76.41

74.57

172,329

4vja2k2gdtyup

load_fnsact@HPGICCI1 (TNS V1-V3)

insert into ICCICCS values (:...

1.64

16.91

9.70

3.54

1

0yv9t4qb1zb2b

cuidmain@HPGICCI1 (TNS V1-V3)

select CUID_CUST_NO , CUID_ID_...

1.06

3.02

35.00

3.13

1

6z06gcfw39pkd

SQL*Plus

SELECT F.TABLESPACE_NAME, TO_...

0.83

13.76

6.04

2.14

1

a7nh7j8zmfrzw

cumimain@HPGICCI1 (TNS V1-V3)

select CUSV_CUST_NO from CUMI...

0.66

87.90

0.75

0.42

444

104pd9mm3fh9p

 

select blocks, maxblocks, gran...

0.50

13.01

3.83

1.27

1

38gak8u2qm11w

SQL*Plus

select count(*) from CUSVAA_T...

0.50

51.75

0.96

0.79

1,554

aq4js2gkfjru8

 

update tsq$ set blocks=:3, max...

0.33

91.11

0.36

0.33

187

04xtrk7uyhknh

 

select obj#, type#, ctime, mti...

0.33

2.47

13.29

12.97

172,337

gmn2w09rdxn14

load_oldnewact@HPGICCI1 (TNS V1-V3)

insert into OLDNEWACT values ...

0.29

1.26

22.94

22.85

172,329

1dm3bq36vu3g8

load_fnsact@HPGICCI1 (TNS V1-V3)

insert into iccifnsact values...

0.25

10.14

2.43

1.59

1

cp5duhcsj72q0

cusmmain@HPGICCI1 (TNS V1-V3)

select CUSM_CUST_ACCT_NO from...

0.21

27.92

0.74

0.74

1,568

9qgtwh66xg6nz

 

update seg$ set type#=:4, bloc...

0.20

3.49

5.83

5.75

51,633

49ms69srnaxzj

load_fnsact@HPGICCI1 (TNS V1-V3)

insert into ICCIRPYV values (...

0.17

1.39

12.55

12.52

166,051

chjmy0dxf9mbj

icci_migact@HPGICCI1 (TNS V1-V3)

insert into ICCICCS values (:...

0.16

57.64

0.28

0.24

39

cn1gtsav2d5jh

cusvaamain@HPGICCI1 (TNS V1-V3)

BEGIN BEGIN IF (xdb.DBMS...

0.14

74.58

0.19

0.14

121

5ngzsfstg8tmy

 

select o.owner#, o.name, o.nam...

0.11

64.72

0.18

0.15

80

78m9ryygp65v5

cusvaamain@HPGICCI1 (TNS V1-V3)

SELECT /*+ ALL_ROWS */ COUNT(*...

0.11

94.54

0.12

0.01

17

bwt0pmxhv7qk7

 

delete from con$ where owner#=...

0.11

80.26

0.14

0.14

327

53saa2zkr6wc3

 

select intcol#, nvl(pos#, 0), ...

0.08

19.20

0.42

0.24

1

d92h3rjp0y217

 

begin prvt_hdm.auto_execute( :...

0.07

54.97

0.13

0.13

83

7ng34ruy5awxq

 

select i.obj#, i.ts#, i.file#,...

0.06

5.22

1.13

0.72

77

0hhmdwwgxbw0r

 

select obj#, type#, flags, ...

0.06

86.50

0.06

0.06

45

a2any035u1qz1

 

select owner#, name from con$...

0.06

8.19

0.67

0.08

1

1uk5m5qbzj1vt

SQL*Plus

BEGIN dbms_workload_repository...

0.04

75.69

0.06

0.06

87

6769wyy3yf66f

 

select pos#, intcol#, col#, sp...

0.04

48.05

0.09

0.07

7

0pvtkmrrq8usg

 

select file#, block# from seg...

0.04

8.84

0.40

0.40

6,304

2ym6hhaq30r73

 

select type#, blocks, extents,...

0.03

28.15

0.12

0.12

49

b52m6vduutr8j

 

delete from RecycleBin$ ...

0.03

66.23

0.05

0.05

85

1gu8t96d0bdmu

 

select t.ts#, t.file#, t.block...

0.03

67.03

0.05

0.05

38

btzq46kta67dz

DBMS_SCHEDULER

update obj$ set obj#=:6, type#...

0.02

66.73

0.04

0.04

86

3m8smr0v7v1m6

 

INSERT INTO sys.wri$_adv_messa...

0.02

26.94

0.09

0.09

38

0k8h617b8guhf

 

delete from RecycleBin$ ...

0.02

76.76

0.03

0.03

51

9vtm7gy4fr2ny

 

select con# from con$ where ow...

0.02

51.91

0.05

0.05

84

83taa7kaw59c1

 

select name, intcol#, segcol#,...

0.02

0.15

14.91

11.17

5

djs2w2f17nw2z

 

DECLARE job BINARY_INTEGER := ...

0.02

2.12

1.00

0.99

8,784

501v412s13r4m

load_fnsact@HPGICCI1 (TNS V1-V3)

update ICCIFNSACT set BORM_FA...

0.02

53.82

0.03

0.03

39

bdv0rkkssq2jm

cusvaamain@HPGICCI1 (TNS V1-V3)

SELECT count(*) FROM user_poli...

0.01

0.10

14.34

14.28

172,983

7wwv1ybs9zguz

load_fnsact@HPGICCI1 (TNS V1-V3)

update ICCIFNSACT set BORM_AD...

0.01

8.29

0.16

0.13

421

g00cj285jmgsw

 

update sys.mon_mods$ set inser...

0.01

1.65

0.56

0.54

2

84qubbrsr0kfn

 

insert into wrh$_latch (snap...

0.01

22.33

0.04

0.02

26

44au3v5mzpc1c

load_curmmast@HPGICCI1 (TNS V1-V3)

insert into ICCICURMMAST valu...

0.01

0.08

7.71

7.70

172,983

5c4qu2zmj3gux

load_fnsact@HPGICCI1 (TNS V1-V3)

select * from ICCIPRODCODE wh...

Back to SQL Statistics 

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)

Back to SQL Statistics 

Instance Activity Statistics

·         Instance Activity Stats

·         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

·         File 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爭用。

Back to IO Stats 

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

Back to IO Stats 

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

·         Buffer Pool Advisory

·         PGA Aggr Summary

·         PGA Aggr Target Stats

·         PGA Aggr Target Histogram

·         PGA Memory Advisory

·         Shared Pool Advisory

·         SGA Target Advisory

·         Streams Pool Advisory

·         Java Pool Advisory

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

 

Back to Advisory Statistics 

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

Back to Advisory Statistics 

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

Back to Advisory Statistics 

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

Back to Advisory Statistics 

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

Back to Advisory Statistics 

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

Back to Advisory Statistics 

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

Back to Advisory Statistics 

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

Back to Advisory Statistics 

Streams Pool Advisory

No data exists for this section of the report.

Back to Advisory Statistics 

Java Pool Advisory

No data exists for this section of the report.

Back to Advisory Statistics 

Wait Statistics

·         Buffer Wait Statistics

·         Enqueue Activity

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

Back to Wait Statistics 

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

Back to Wait Statistics 

Undo Statistics

·         Undo Segment Summary

·         Undo Segment Stats

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

Back to Undo Statistics 

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

Back to Undo Statistics 

Latch Statistics

·         Latch Activity

·         Latch Sleep Breakdown

·         Latch Miss Sources

·         Parent Latch Statistics

·         Child 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

 

Back to Latch Statistics 

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

Back to Latch Statistics 

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

Back to Latch Statistics 

Parent Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics 

Child Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics 

Segment Statistics

·         Segments by Logical Reads

·         Segments by Physical Reads

·         Segments by Row Lock Waits

·         Segments by ITL Waits

·         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

Back to Segment Statistics 

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

Back to Segment Statistics 

Segments by Row Lock Waits

當一個程式予在正被其它程式鎖住的資料行上獲得排它鎖時發生這種等待。這種等待經常是由於在一個有主鍵索引的表上做大量INSERT操作。

No data exists for this section of the report.

Back to Segment Statistics 

Segments by ITL Waits

No data exists for this section of the report.

Back to Segment Statistics 

Segments by Buffer Busy Waits

No data exists for this section of the report.

Back to Segment Statistics 

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

Back to Segment Statistics 

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

Back to Segment Statistics 

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

Back to Segment Statistics 

Dictionary Cache Statistics

·         Dictionary Cache Stats

·         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

·         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

·         SGA Memory Summary

·         SGA breakdown difference

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

Back to Memory Statistics 

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

 

Back to Memory Statistics 

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

Back to Memory Statistics 

Streams Statistics

·         Streams CPU/IO Usage

·         Streams Capture

·         Streams Apply

·         Buffered Queues

·         Buffered Subscribers

·         Rule Set

Streams CPU/IO Usage

No data exists for this section of the report.

Back to Streams Statistics 

Streams Capture

No data exists for this section of the report.

Back to Streams Statistics 

Streams Apply

No data exists for this section of the report.

Back to Streams Statistics 

Buffered Queues

No data exists for this section of the report.

Back to Streams Statistics 

Buffered Subscribers

No data exists for this section of the report.

Back to Streams Statistics 

Rule Set

No data exists for this section of the report.

Back to Streams Statistics 

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

·         Global CR Served Stats

·         Global CURRENT Served Stats

·         Global Cache Transfer Stats


 

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章