本报告提供了Oracle数据库GAAP实例从12月9日11:32到13:00的性能分析，包括缓存大小、物理读取、缓冲池统计等关键指标的变化情况，并分析了SQL语句的执行效率及资源使用情况。
STATSPACK report for
Database DB Id Instance
Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------
-------- --------------- ----------- ---
2513066722 GAAP 1 05-Nov-09 17:44 10.2.0.1.0
NO
Host Name: NeoAMLKYC Num CPUs: 4 Phys Memory (MB):
7,744
~~~~
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 41 23-Dec-09 11:32:24 24 14.4
End Snap: 81 23-Dec-09 13:00:03 20 15.8
Elapsed: 87.65 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 720M 716M Std Block Size: 8K
Shared Pool Size: 108M 112M
Log Buffer: 6,023K
Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 16,685.62 24,047.59
Logical reads: 44,926.63 64,749.01
Block changes: 88.81 128.00
Physical reads: 60.30 86.90
Physical writes: 9.75 14.05
User calls: 1.05 1.51
Parses: 5.31 7.66
Hard parses: 1.17 1.69
Sorts: 2.70 3.89
Logons: 0.00 0.00
Executes: 62.79 90.49
Transactions: 0.69
% Blocks changed per Read: 0.20 Recursive Call %:
99.28
Rollback per transaction %: 0.00 Rows per Sort:
31.61
Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.88 In-memory Sort %: 100.00
Library Hit %: 96.73 Soft Parse %: 77.95
Execute to Parse %: 91.54 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 75.33 % Non-Parse CPU: 99.40
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 89.74 89.84
% SQL with executions>1: 70.03 73.72
% Memory for SQL w/exec>1: 96.20 96.27
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 5,108 98.8
db file scattered read 17,651 16 1 .3
log file parallel write 5,150 12 2 .2
db file sequential read 3,508 12 3 .2
direct path read temp 1,205 6 5 .1
-------------------------------------------------------------
Host CPU (CPUs: 4)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
0.01 0.01 26.97 1.74
71.30 4.78 44.01
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance:
24.04
% of busy CPU for Instance:
83.77
%DB time waiting for CPU - Resource Mgr:
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 7,744.0 7,744.0
SGA use (MB): 844.0 844.0
PGA use (MB): 104.2 168.2
% Host Mem used for SGA+PGA: 12.2 13.1
-------------------------------------------------------------
Time Model System Stats DB/Inst: GAAP/GAAP Snaps: 41-81
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % of DB time
----------------------------------- -------------------- ------------
sql execute elapsed time 5,175.0 100.0
DB CPU 5,056.0 97.7
parse time elapsed 38.5 .7
hard parse elapsed time 24.5 .5
PL/SQL execution elapsed time 11.8 .2
hard parse (sharing criteria) elaps 1.3 .0
PL/SQL compilation elapsed time 0.5 .0
sequence load elapsed time 0.1 .0
repeated bind elapsed time 0.1 .0
hard parse (bind mismatch) elapsed 0.0 .0
connection management call elapsed 0.0 .0
DB time 5,174.4
background elapsed time 37.2
background cpu time 13.9
-------------------------------------------------------------
Wait Events DB/Inst: GAAP/GAAP Snaps: 41-81
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
db file scattered read 17,651 0 16 1 4.8
log file parallel write 5,150 0 12 2 1.4
db file sequential read 3,508 0 12 3 1.0
direct path read temp 1,205 0 6 5 0.3
direct path write temp 1,205 0 4 3 0.3
db file parallel write 768 0 4 5 0.2
control file parallel write 1,901 0 4 2 0.5
control file sequential read 2,988 0 1 0 0.8
os thread startup 8 0 0 62 0.0
log file sync 195 0 0 2 0.1
log file switch completion 5 0 0 34 0.0
SGA: MMAN sleep for component shr 13 77 0 9 0.0
SGA: allocation forcing component 5 20 0 10 0.0
latch free 7 0 0 6 0.0
direct path write 19 0 0 1 0.0
log file sequential read 4 0 0 5 0.0
LGWR wait for redo copy 420 0 0 0 0.1
direct path read 14 0 0 1 0.0
log file single write 4 0 0 3 0.0
rdbms ipc reply 18 0 0 0 0.0
SQL*Net more data to client 144 0 0 0 0.0
latch: shared pool 11 0 0 0 0.0
SQL*Net message from client 4,286 0 33,285 7766 1.2
Streams AQ: qmn slave idle wait 187 0 5,115 27352 0.1
Streams AQ: qmn coordinator idle 382 51 5,115 13390 0.1
virtual circuit status 175 100 5,104 29168 0.0
Streams AQ: waiting for time mana 1 100 3,684 ###### 0.0
jobq slave wait 84 98 244 2906 0.0
class slave wait 3 100 15 4883 0.0
SQL*Net message to client 4,282 0 0 0 1.2
-------------------------------------------------------------
Background Wait Events DB/Inst: GAAP/GAAP Snaps: 41-81
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
log file parallel write 5,150 0 12 2 1.4
db file parallel write 768 0 4 5 0.2
control file parallel write 1,901 0 4 2 0.5
db file scattered read 110 0 1 9 0.0
control file sequential read 456 0 1 2 0.1
db file sequential read 82 0 0 6 0.0
os thread startup 8 0 0 62 0.0
SGA: MMAN sleep for component shr 13 77 0 9 0.0
events in waitclass Other 439 0 0 0 0.1
direct path write 14 0 0 2 0.0
log file sequential read 4 0 0 5 0.0
direct path read 14 0 0 1 0.0
log file single write 4 0 0 3 0.0
rdbms ipc message 20,804 81 44,632 2145 5.7
smon timer 25 64 5,286 ###### 0.0
pmon timer 1,763 100 5,134 2912 0.5
Streams AQ: qmn slave idle wait 187 0 5,115 27352 0.1
Streams AQ: qmn coordinator idle 382 51 5,115 13390 0.1
Streams AQ: waiting for time mana 1 100 3,684 ###### 0.0
-------------------------------------------------------------
Wait Event Histogram DB/Inst: GAAP/GAAP Snaps: 41-81
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)
Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
LGWR wait for redo copy 420 100.0
SGA: MMAN sleep for compon 13 7.7 92.3
SGA: allocation forcing co 5 100.0
SQL*Net more data to clien 144 100.0
buffer busy waits 7 100.0
control file parallel writ 1901 89.8 4.6 3.3 1.4 .7 .2
control file sequential re 2988 95.3 .4 .4 2.5 1.3 .1
db file parallel write 768 13.9 11.2 31.8 31.1 9.1 1.7 1.2
db file scattered read 17K 90.4 2.9 1.6 .9 3.3 .7 .3
db file sequential read 3512 46.4 5.6 10.2 28.6 8.7 .4 .2
direct path read 14 85.7 7.1 7.1
direct path read temp 1205 51.7 .7 2.6 23.2 19.3 .9 1.6
direct path write 19 94.7 5.3
direct path write temp 1205 53.4 6.3 28.8 6.8 3.3 .4 1.0
latch free 7 85.7 14.3
latch: library cache 2 100.0
latch: shared pool 10 100.0
log file parallel write 5150 64.2 17.7 4.0 5.7 6.7 1.5 .2
log file sequential read 4 50.0 25.0 25.0
log file single write 4 50.0 25.0 25.0
log file switch completion 5 40.0 60.0
log file sync 195 75.9 11.3 3.1 3.1 4.6 1.5 .5
os thread startup 8 12.5 87.5
rdbms ipc reply 18 100.0
undo segment extension 1 100.0
SQL*Net message from clien 4286 61.7 1.6 .6 1.8 3.2 3.8 12.6 14.7
SQL*Net message to client 4282 100.0
Streams AQ: qmn coordinato 382 49.0 51.0
Streams AQ: qmn slave idle 187 100.0
Streams AQ: waiting for ti 1 100.0
class slave wait 3 100.0
dispatcher timer 88 100.0
jobq slave wait 84 100.0
pmon timer 1763 .6 .1 99.4
rdbms ipc message 20K 1.2 .1 .1 2.3 .9 1.0 38.8 55.5
smon timer 24 100.0
virtual circuit status 175 100.0
-------------------------------------------------------------
SQL ordered by CPU DB/Inst: GAAP/GAAP Snaps: 41-81
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB CPU (s): 5,056
-> Captured SQL accounts for 33.6% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
568.53 1,519 0.37 11.2 580.56 73,585,159 1190504466
SELECT NVL(SUM(FUNCACULATEVALUEBYRATE(T.CCY, T.CUR_AMT_O, :B5 ,
:B4 , :B3 )), 0) FROM OTST_T T WHERE T.PROC_DATE = :B4 AND TRIM(
T.BRANCH) = TRIM(:B3 ) AND TRIM(T.AC_ID) = TRIM(:B2 ) AND TRIM(T
.AC_ID) = TRIM(:B2 ) AND TRIM(T.FACILITY) = TRIM(:B1 )
424.86 182,243 0.00 8.4 424.94 57,341,110 355791134
SELECT T.RATEVALUE FROM EXCHANGE_RATE_T T WHERE T.PROC_DATE = :B
4 AND TRIM(T.BRANCH) = TRIM(:B3 ) AND TRIM(T.SCCY) = TRIM(:B2 )
AND TRIM(T.TCCY) = TRIM(:B1 ) AND ROWNUM = 1
91.02 1 91.02 1.8 98.47 2,625,123 1196434917
SELECT A.RPMENTAMOUNT AS RPMENTAMOUNT, A.AMOUNTREF_NO AS AMOUNTR
EF_NO, A.BRANCH AS BRANCH, B.PROC_DATE AS PROC_DATE, B.CUR_AMT_O
AS DEBTAMOUNT, B.ID AS AID, B.BRANCH AS BRCH, B.AC_ID AS ACT, B
.CUST_NAME AS CUS, B.MATURITY AS MAT, B.CCY AS ACCY, B.REF_NO AS
AREFNO, B.INT_RATE AS INTRATE, B.SUBJECT AS SUB1 FROM (SELECT N
72.75 31,443 0.00 1.4 72.73 9,904,545 1589564614
SELECT T1.RATEVALUE FROM EXCHANGE_RATE_T T1 WHERE TRIM(T1.SCCY)
= TRIM(:B3 ) AND TRIM(T1.TCCY) = 'RMB' AND T1.PROC_DATE = :B2 AN
D TRIM(T1.BRANCH) = TRIM(:B1 )
72.19 31,203 0.00 1.4 72.19 9,828,945 3422842001
SELECT T.RATEVALUE FROM EXCHANGE_RATE_T T WHERE TRIM(T.SCCY) = T
RIM(:B3 ) AND TRIM(T.TCCY) = 'RMB' AND T.PROC_DATE = :B2 AND TRI
M(T.BRANCH) = TRIM(:B1 )
60.44 1 60.44 1.2 80.25 945,074 3039278037
SELECT A.CUR_AMT_O AS DEBTAMOUNT, B.INSTAL_AMT AS INSTAMT, A.ID
AS AID, B.ID AS BID, A.BRANCH AS BRCH, A.AC_ID AS ACT, B.AC_TITL
E AS BCT, A.CUST_NAME AS CUS, A.MATURITY AS MAT, A.CCY AS ACCY,
A.REF_NO AS AREFNO, A.INT_RATE AS INTRATE, B.REF_NO AS REFNO, A.
SUBJECT AS SUB1, B.SUB1 AS BSUB1, B.RMK_RVW AS RMKRVW, B.RMK_MAT
-------------------------------------------------------------
SQL ordered by Elapsed DB/Inst: GAAP/GAAP Snaps: 41-81
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total DB Time (s): 5,174
-> Captured SQL accounts for 34.4% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
580.56 1,519 0.38 11.2 568.53 0 1190504466
SELECT NVL(SUM(FUNCACULATEVALUEBYRATE(T.CCY, T.CUR_AMT_O, :B5 ,
:B4 , :B3 )), 0) FROM OTST_T T WHERE T.PROC_DATE = :B4 AND TRIM(
T.BRANCH) = TRIM(:B3 ) AND TRIM(T.AC_ID) = TRIM(:B2 ) AND TRIM(T
.AC_ID) = TRIM(:B2 ) AND TRIM(T.FACILITY) = TRIM(:B1 )
424.94 182,243 0.00 8.2 424.86 0 355791134
SELECT T.RATEVALUE FROM EXCHANGE_RATE_T T WHERE T.PROC_DATE = :B
4 AND TRIM(T.BRANCH) = TRIM(:B3 ) AND TRIM(T.SCCY) = TRIM(:B2 )
AND TRIM(T.TCCY) = TRIM(:B1 ) AND ROWNUM = 1
98.47 1 98.47 1.9 91.02 55 1196434917
SELECT A.RPMENTAMOUNT AS RPMENTAMOUNT, A.AMOUNTREF_NO AS AMOUNTR
EF_NO, A.BRANCH AS BRANCH, B.PROC_DATE AS PROC_DATE, B.CUR_AMT_O
AS DEBTAMOUNT, B.ID AS AID, B.BRANCH AS BRCH, B.AC_ID AS ACT, B
.CUST_NAME AS CUS, B.MATURITY AS MAT, B.CCY AS ACCY, B.REF_NO AS
AREFNO, B.INT_RATE AS INTRATE, B.SUBJECT AS SUB1 FROM (SELECT N
80.25 1 80.25 1.6 60.44 148,376 3039278037
SELECT A.CUR_AMT_O AS DEBTAMOUNT, B.INSTAL_AMT AS INSTAMT, A.ID
AS AID, B.ID AS BID, A.BRANCH AS BRCH, A.AC_ID AS ACT, B.AC_TITL
E AS BCT, A.CUST_NAME AS CUS, A.MATURITY AS MAT, A.CCY AS ACCY,
A.REF_NO AS AREFNO, A.INT_RATE AS INTRATE, B.REF_NO AS REFNO, A.
SUBJECT AS SUB1, B.SUB1 AS BSUB1, B.RMK_RVW AS RMKRVW, B.RMK_MAT
72.73 31,443 0.00 1.4 72.75 0 1589564614
SELECT T1.RATEVALUE FROM EXCHANGE_RATE_T T1 WHERE TRIM(T1.SCCY)
= TRIM(:B3 ) AND TRIM(T1.TCCY) = 'RMB' AND T1.PROC_DATE = :B2 AN
D TRIM(T1.BRANCH) = TRIM(:B1 )
72.19 31,203 0.00 1.4 72.19 0 3422842001
SELECT T.RATEVALUE FROM EXCHANGE_RATE_T T WHERE TRIM(T.SCCY) = T
RIM(:B3 ) AND TRIM(T.TCCY) = 'RMB' AND T.PROC_DATE = :B2 AND TRI
M(T.BRANCH) = TRIM(:B1 )
-------------------------------------------------------------
SQL ordered by Gets DB/Inst: GAAP/GAAP Snaps: 41-81
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 236,269,127
-> Captured SQL accounts for 78.7% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
73,585,159 1,519 48,443.2 31.1 568.53 580.56 1190504466
SELECT NVL(SUM(FUNCACULATEVALUEBYRATE(T.CCY, T.CUR_AMT_O, :B5 ,
:B4 , :B3 )), 0) FROM OTST_T T WHERE T.PROC_DATE = :B4 AND TRIM(
T.BRANCH) = TRIM(:B3 ) AND TRIM(T.AC_ID) = TRIM(:B2 ) AND TRIM(T
.AC_ID) = TRIM(:B2 ) AND TRIM(T.FACILITY) = TRIM(:B1 )
57,341,110 182,243 314.6 24.3 424.86 424.94 355791134
SELECT T.RATEVALUE FROM EXCHANGE_RATE_T T WHERE T.PROC_DATE = :B
4 AND TRIM(T.BRANCH) = TRIM(:B3 ) AND TRIM(T.SCCY) = TRIM(:B2 )
AND TRIM(T.TCCY) = TRIM(:B1 ) AND ROWNUM = 1
9,904,545 31,443 315.0 4.2 72.75 72.73 1589564614
SELECT T1.RATEVALUE FROM EXCHANGE_RATE_T T1 WHERE TRIM(T1.SCCY)
= TRIM(:B3 ) AND TRIM(T1.TCCY) = 'RMB' AND T1.PROC_DATE = :B2 AN
D TRIM(T1.BRANCH) = TRIM(:B1 )
9,828,945 31,203 315.0 4.2 72.19 72.19 3422842001
SELECT T.RATEVALUE FROM EXCHANGE_RATE_T T WHERE TRIM(T.SCCY) = T
RIM(:B3 ) AND TRIM(T.TCCY) = 'RMB' AND T.PROC_DATE = :B2 AND TRI
M(T.BRANCH) = TRIM(:B1 )
4,505,276 1,519 2,965.9 1.9 32.66 36.82 160160517
SELECT NVL(SUM(FUNCACULATEVALUEBYRATE(T23.CCY, T23.GRT_AMT_VALID
, :B6 , :B4 , :B5 )), 0) FROM GRT_SPLIT T23 WHERE T23.PROC_DATE
= :B4 AND TRIM(T23.BRANCH) = TRIM(:B3 ) AND TRIM(T23.AC_ID) = TR
IM(:B2 ) AND TRIM(T23.FAC_CODE) = TRIM(:B1 )
3,144,748 1,193 2,636.0 1.3 23.26 25.72 86285138
SELECT * FROM GRT_SPLIT T24 WHERE T24.PROC_DATE = :B4 AND TRIM(T
24.BRANCH) = TRIM(:B3 ) AND TRIM(T24.AC_ID) = TRIM(:B2 ) AND TRI
M(T24.FAC_CODE) = TRIM(:B1 ) ORDER BY T24.GRT_LEVEL DESC,T24.GRT
_RATING DESC
2,697,232 458 5,889.2 1.1 18.53 19.69 2796187257
SELECT NVL(FUNCACULATEVALUEBYRATE(T15.CCY, T15.EFFECT_CREDIT, T1
5.CCY, :B4 , :B5 ), 0) FROM FACILITY_T T15 WHERE T15.PROC_DATE =
:B4 AND TRIM(T15.BRANCH) = TRIM(:B3 ) AND TRIM(T15.AC_ID) = TRI
M(:B2 ) AND TRIM(T15.FAC_CODE) = TRIM(:B1 )
2,625,123 1 2,625,123.0 1.1 91.02 98.47 1196434917
SELECT A.RPMENTAMOUNT AS RPMENTAMOUNT, A.AMOUNTREF_NO AS AMOUNTR
EF_NO, A.BRANCH AS BRANCH, B.PROC_DATE AS PROC_DATE, B.CUR_AMT_O
AS DEBTAMOUNT, B.ID AS AID, B.BRANCH AS BRCH, B.AC_ID AS ACT, B
.CUST_NAME AS CUS, B.MATURITY AS MAT, B.CCY AS ACCY, B.REF_NO AS
AREFNO, B.INT_RATE AS INTRATE, B.SUBJECT AS SUB1 FROM (SELECT N
2,544,984 432 5,891.2 1.1 17.44 18.53 3847905926
SELECT NVL(FUNCACULATEVALUEBYRATE(T18.CCY, T18.LINE_TOTAL, T18.C
CY, :B4 , :B5 ), 0) FROM FACILITY_T T18 WHERE T18.PROC_DATE = :B
4 AND TRIM(T18.BRANCH) = TRIM(:B3 ) AND TRIM(T18.AC_ID) = TRIM(:
SQL ordered by Gets DB/Inst: GAAP/GAAP Snaps: 41-81
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 236,269,127
-> Captured SQL accounts for 78.7% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
B2 ) AND TRIM(T18.FAC_CODE) = TRIM(:B1 )
-------------------------------------------------------------
SQL ordered by Reads DB/Inst: GAAP/GAAP Snaps: 41-81
-> End Disk Reads Threshold: 1000 Total Disk Reads: 317,094
-> Captured SQL accounts for 97.1% of Total Disk Reads
-> SQL reported below exceeded 1.0% of Total Disk Reads
CPU Elapsd Old
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
148,376 1 148,376.0 46.8 60.44 80.25 3039278037
SELECT A.CUR_AMT_O AS DEBTAMOUNT, B.INSTAL_AMT AS INSTAMT, A.ID
AS AID, B.ID AS BID, A.BRANCH AS BRCH, A.AC_ID AS ACT, B.AC_TITL
E AS BCT, A.CUST_NAME AS CUS, A.MATURITY AS MAT, A.CCY AS ACCY,
A.REF_NO AS AREFNO, A.INT_RATE AS INTRATE, B.REF_NO AS REFNO, A.
SUBJECT AS SUB1, B.SUB1 AS BSUB1, B.RMK_RVW AS RMKRVW, B.RMK_MAT
40,860 1 40,860.0 12.9 1.29 3.80 3204538978
SELECT T.ORI_JE_SET ORI_JE_SET, T.JE_TYPE JE_TYPE, T.TXN_CODE TX
N_CODE, T.AC_ID AC_ID, T.REF_NO REF_NO, T.JE_INPUT_TYPE JE_INPUT
_TYPE, T.ACCT_LEDGER_MOD_FLAG ACCT_LEDGER_MOD_FLAG, T.SEQ_NO SEQ
_NO, T.CCY CCY, T.ORG_AMT_O ORG_AMT_O, T.CUR_AMT_O CUR_AMT_O, T.
N_AMORTISED_COST N_AMORTISED_COST, T.BOND_AFS_INT_ADJ_ACCUM_D BO
40,858 1 40,858.0 12.9 0.93 1.33 2030171700
SELECT T.ORI_JE_SET, T.JE_TYPE, T.TXN_CODE, T.AC_ID, T.REF_NO, T
.JE_INPUT_TYPE, T.ACCT_LEDGER_MOD_FLAG, T.SEQ_NO, T.CCY, T.ORG_A
MT_O, T.CUR_AMT_O, T.N_AMORTISED_COST, T.BOND_AFS_INT_ADJ_ACCUM_
D, T.BOND_AFS_INT_ADJ_ACCUM_M, T.BOND_AFS_INT_ADJ_ACCUM_Y, T.BON
D_AFS_INT_ADJ_ACCUM_TOTAL, T.BOND_AFS_FAIR_VALUE, T.BOND_AFS_FAI
40,857 1 40,857.0 12.9 1.30 1.30 3479783581
SELECT T.AC_TITLE, AC_CODE, ACID2, ACCODE2, TYPE, OTBALANCE, INT
DUE, DAYS, CAL, RATE, ACCRUAL, UNEARNED, KEYIN_FLAT, KEYIN_FLAG,
O.STACS_SUBJ, O.OFF_SHORE, O.SUBJECT, O.CCY, O.CUST_NAME, O.AC_
ID AC_ID, ORG_AMT_O, CUR_AMT_O, O.REF_NO, O.VALUE, O.MATURITY, N
EXT_INT, INT_RATE, RENEWAL_REF, LAST_INT, INT_TYPE, ACT_SP, CAL_
29,192 1 29,192.0 9.2 1.23 1.55 784886
SELECT T.AC_TITLE, AC_CODE, ACID2, ACCODE2, TYPE, OTBALANCE, INT
DUE, DAYS, CAL, RATE, ACCRUAL, UNEARNED, KEYIN_FLAT, KEYIN_FLAG,
O.STACS_SUBJ, O.OFF_SHORE, O.SUBJECT, O.CCY, O.CUST_NAME, O.AC_
ID, ORG_AMT_O, CUR_AMT_O, O.REF_NO, O.VALUE, O.MATURITY, NEXT_IN
T, INT_RATE, RENEWAL_REF, LAST_INT, INT_TYPE, ACT_SP, CAL_MT, FA
3,867 2 1,933.5 1.2 0.17 1.21 2956698025
DELETE FROM CO_LOAN_GRT_T T WHERE T.PROC_DATE = :B2 AND TRIM(T.B
RANCH) = TRIM(:B1 ) AND (TRIM(T.GRT_SHARE_FLAG) IS NULL OR TRIM(
T.GRT_SHARE_FLAG) <> 'N') AND (TRIM(T.KEYIN_FLAG) IS NULL OR TRI
M(T.KEYIN_FLAG) <> 'Y')
-------------------------------------------------------------
SQL ordered by Executions DB/Inst: GAAP/GAAP Snaps: 41-81
-> End Executions Threshold: 100 Total Executions: 330,210
-> Captured SQL accounts for 92.0% of Total Executions
-> SQL reported below exceeded 1.0% of Total Executions
CPU per Elap per Old
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
182,243 24,107 0.1 0.00 0.00 355791134
SELECT T.RATEVALUE FROM EXCHANGE_RATE_T T WHERE T.PROC_DATE = :B
4 AND TRIM(T.BRANCH) = TRIM(:B3 ) AND TRIM(T.SCCY) = TRIM(:B2 )
AND TRIM(T.TCCY) = TRIM(:B1 ) AND ROWNUM = 1
31,443 31,443 1.0 0.00 0.00 1589564614
SELECT T1.RATEVALUE FROM EXCHANGE_RATE_T T1 WHERE TRIM(T1.SCCY)
= TRIM(:B3 ) AND TRIM(T1.TCCY) = 'RMB' AND T1.PROC_DATE = :B2 AN
D TRIM(T1.BRANCH) = TRIM(:B1 )
31,203 31,223 1.0 0.00 0.00 3422842001
SELECT T.RATEVALUE FROM EXCHANGE_RATE_T T WHERE TRIM(T.SCCY) = T
RIM(:B3 ) AND TRIM(T.TCCY) = 'RMB' AND T.PROC_DATE = :B2 AND TRI
M(T.BRANCH) = TRIM(:B1 )
8,750 8,750 1.0 0.00 0.00 841435308
Module: PL/SQL Developer
select value(p$) from "XDB"."XDB$RESOURCE" as of
snapshot(:2) p$ where SYS_NC_OID$ = :1
3,949 3,949 1.0 0.00 0.00 1496481949
INSERT INTO MATURITY_T (ID, PROC_DATE, BRANCH, AC_TITLE, SUB1, K
EYDATE, CUSTOMER, REF_NO, MATURITY, CCY, AMOUNT, INT_RATE, INT_A
MT, RMK_INT, RMK_MAT, RMK_RVW, INSTAL_AMT, NEW_REC_FLAG) VALUES
(SYS_GUID(), :B14 , TRIM(:B13 ), TRIM(:B12 ), TRIM(:B11 ), :B8 ,
TRIM(:B10 ), TRIM(:B9 ), TRIM(:B8 ), TRIM(:B7 ), :B1 , :B6 , :B
3,761 2,077 0.6 0.00 0.00 4274598960
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
density, col#, spare1, spare2, avgcln from hist_head$ where obj#
=:1 and intcol#=:2
-------------------------------------------------------------
SQL ordered by Parse Calls DB/Inst: GAAP/GAAP Snaps: 41-81
-> End Parse Calls Threshold: 1000 Total Parse Calls: 27,949
-> Captured SQL accounts for 30.7% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
1,159 1,159 4.15 1348827743
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0),NVL(scanhint,0) from seg$ where ts#=:1 and file#=:2
and block#=:3
615 615 2.20 2018736380
select timestamp, flags from fixed_obj$ where obj#=:1
555 555 1.99 2554034351
lock table sys.col_usage$ in exclusive mode nowait
555 1,308 1.99 3665763022
update sys.col_usage$ set equality_preds = equality_preds
+ decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoi
n_preds + decode(bitand(:flag,2),0,0,1), nonequijoin_preds
= nonequijoin_preds + decode(bitand(:flag,4),0,0,1), range_pre
ds = range_preds + decode(bitand(:flag,8),0,0,1),
460 460 1.65 3468666020
select text from view$ where rowid=:1
355 1,069 1.27 839312984
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod
e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180
,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto
rage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti
d,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh
303 303 1.08 1232162599
select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.blo
ck#, l.chunk, l.pctversion$, l.flags, l.property, l.retention, l
.freepools from lob$ l where l.obj# = :1 order by l.intcol# asc
-------------------------------------------------------------
Instance Activity Stats DB/Inst: GAAP/GAAP Snaps: 41-81
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 510,844 97.1 140.0
CPU used when call started 504,876 96.0 138.4
CR blocks created 3,094 0.6 0.9
Cached Commit SCN referenced 42 0.0 0.0
Commit SCN cached 1 0.0 0.0
DB time 547,686 104.1 150.1
DBWR checkpoint buffers written 13,185 2.5 3.6
DBWR checkpoints 2 0.0 0.0
DBWR revisited being-written buff 0 0.0 0.0
DBWR thread checkpoint buffers wr 0 0.0 0.0
DBWR transaction table writes 132 0.0 0.0
DBWR undo block writes 5,572 1.1 1.5
IMU CR rollbacks 3 0.0 0.0
IMU Flushes 992 0.2 0.3
IMU Redo allocation size 3,874,196 736.7 1,061.7
IMU commits 2,643 0.5 0.7
IMU contention 0 0.0 0.0
IMU ktichg flush 0 0.0 0.0
IMU pool not allocated 131 0.0 0.0
IMU recursive-transaction flush 7 0.0 0.0
IMU undo allocation size 7,887,024 1,499.7 2,161.4
IMU- failed to get a private stra 131 0.0 0.0
Misses for writing mapping 0 0.0 0.0
PX local messages recv'd 0 0.0 0.0
PX local messages sent 0 0.0 0.0
SMON posted for undo segment shri 8 0.0 0.0
SQL*Net roundtrips to/from client 4,277 0.8 1.2
active txn count during cleanout 1,561 0.3 0.4
application wait time 0 0.0 0.0
background checkpoints completed 2 0.0 0.0
background checkpoints started 2 0.0 0.0
background timeouts 16,828 3.2 4.6
branch node splits 2 0.0 0.0
buffer is not pinned count 99,878,742 18,992.0 27,371.5
buffer is pinned count 6,550,038,633 1,245,491.3 1,795,022.9
bytes received via SQL*Net from c 605,625 115.2 166.0
bytes sent via SQL*Net to client 2,029,742 386.0 556.3
calls to get snapshot scn: kcmgss 1,242,655 236.3 340.6
calls to kcmgas 18,640 3.5 5.1
calls to kcmgcs 2,621 0.5 0.7
change write time 183 0.0 0.1
cleanout - number of ktugct calls 1,921 0.4 0.5
cleanouts and rollbacks - consist 2 0.0 0.0
cleanouts only - consistent read 182 0.0 0.1
cluster key scan block gets 25,418 4.8 7.0
cluster key scans 14,456 2.8 4.0
commit batch/immediate performed 6 0.0 0.0
commit batch/immediate requested 6 0.0 0.0
commit cleanout failures: block l 0 0.0 0.0
commit cleanout failures: buffer 0 0.0 0.0
commit cleanout failures: callbac 91 0.0 0.0
commit cleanout failures: cannot 0 0.0 0.0
commit cleanouts 17,484 3.3 4.8
commit cleanouts successfully com 17,393 3.3 4.8
commit immediate performed 6 0.0 0.0
commit immediate requested 6 0.0 0.0
Instance Activity Stats DB/Inst: GAAP/GAAP Snaps: 41-81
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
commit txn count during cleanout 937 0.2 0.3
concurrency wait time 49 0.0 0.0
consistent changes 3,094 0.6 0.9
consistent gets 235,813,112 44,839.9 64,624.0
consistent gets - examination 940,565 178.9 257.8
consistent gets direct 0 0.0 0.0
consistent gets from cache 235,813,112 44,839.9 64,624.0
current blocks converted for CR 0 0.0 0.0
cursor authentications 1,421 0.3 0.4
data blocks consistent reads - un 3,094 0.6 0.9
db block changes 467,060 88.8 128.0
db block gets 456,015 86.7 125.0
db block gets direct 8 0.0 0.0
db block gets from cache 456,007 86.7 125.0
deferred (CURRENT) block cleanout 9,981 1.9 2.7
dirty buffers inspected 85 0.0 0.0
enqueue conversions 1,299 0.3 0.4
enqueue releases 62,174 11.8 17.0
enqueue requests 62,175 11.8 17.0
enqueue timeouts 0 0.0 0.0
enqueue waits 0 0.0 0.0
exchange deadlocks 0 0.0 0.0
execute count 330,210 62.8 90.5
frame signature mismatch 0 0.0 0.0
free buffer inspected 286,867 54.6 78.6
free buffer requested 293,549 55.8 80.5
heap block compress 17,784 3.4 4.9
hot buffers moved to head of LRU 26,583 5.1 7.3
immediate (CR) block cleanout app 184 0.0 0.1
immediate (CURRENT) block cleanou 3,505 0.7 1.0
index fast full scans (full) 10 0.0 0.0
index fetch by key 227,521 43.3 62.4
index scans kdiixs1 646,341 122.9 177.1
leaf node 90-10 splits 250 0.1 0.1
leaf node splits 624 0.1 0.2
lob reads 599 0.1 0.2
lob writes 34 0.0 0.0
lob writes unaligned 34 0.0 0.0
logons cumulative 12 0.0 0.0
messages received 5,535 1.1 1.5
messages sent 5,535 1.1 1.5
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 233,374,652 44,376.2 63,955.8
opened cursors cumulative 37,680 7.2 10.3
parse count (failures) 0 0.0 0.0
parse count (hard) 6,162 1.2 1.7
parse count (total) 27,949 5.3 7.7
parse time cpu 3,053 0.6 0.8
parse time elapsed 4,053 0.8 1.1
physical read IO requests 22,404 4.3 6.1
physical read bytes 2,597,634,048 493,940.7 711,875.6
physical read total IO requests 25,452 4.8 7.0
physical read total bytes 2,646,697,984 503,270.2 725,321.5
physical read total multi block r 18,888 3.6 5.2
physical reads 317,094 60.3 86.9
physical reads cache 279,725 53.2 76.7
Instance Activity Stats DB/Inst: GAAP/GAAP Snaps: 41-81
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
physical reads cache prefetch 258,540 49.2 70.9
physical reads direct 37,369 7.1 10.2
physical reads direct (lob) 0 0.0 0.0
physical reads direct temporary t 37,355 7.1 10.2
physical reads prefetch warmup 0 0.0 0.0
physical write IO requests 5,051 1.0 1.4
physical write bytes 419,995,648 79,862.3 115,098.8
physical write total IO requests 15,941 3.0 4.4
physical write total bytes 606,124,032 115,254.6 166,106.9
physical write total multi block 7,888 1.5 2.2
physical writes 51,269 9.8 14.1
physical writes direct 37,377 7.1 10.2
physical writes direct (lob) 0 0.0 0.0
physical writes direct temporary 37,355 7.1 10.2
physical writes from cache 13,892 2.6 3.8
physical writes non checkpoint 44,334 8.4 12.2
pinned buffers inspected 23 0.0 0.0
prefetch clients - default 0 0.0 0.0
prefetch warmup blocks aged out b 0 0.0 0.0
prefetched blocks aged out before 0 0.0 0.0
process last non-idle time 6,500 1.2 1.8
recovery blocks read 0 0.0 0.0
recursive calls 758,067 144.2 207.8
recursive cpu usage 509,319 96.9 139.6
redo blocks read for recovery 0 0.0 0.0
redo blocks written 180,872 34.4 49.6
redo buffer allocation retries 4 0.0 0.0
redo entries 233,642 44.4 64.0
redo log space requests 5 0.0 0.0
redo log space wait time 16 0.0 0.0
redo ordering marks 2,998 0.6 0.8
redo size 87,749,668 16,685.6 24,047.6
redo synch time 39 0.0 0.0
redo synch writes 4,651 0.9 1.3
redo wastage 1,734,436 329.8 475.3
redo write time 1,283 0.2 0.4
redo writer latching time 2 0.0 0.0
redo writes 5,150 1.0 1.4
rollback changes - undo records a 620 0.1 0.2
rollbacks only - consistent read 3,092 0.6 0.9
rows fetched via callback 166,478 31.7 45.6
session connect time 0 0.0 0.0
session cursor cache hits 31,406 6.0 8.6
session logical reads 236,269,127 44,926.6 64,749.0
session pga memory 1,572,864 299.1 431.0
session uga memory 17,179,512,328 3,266,688.0 4,708,005.6
session uga memory max 20,037,376 3,810.1 5,491.2
shared hash latch upgrades - no w 693,956 132.0 190.2
shared hash latch upgrades - wait 0 0.0 0.0
sorts (disk) 0 0.0 0.0
sorts (memory) 14,184 2.7 3.9
sorts (rows) 448,342 85.3 122.9
sql area purged 0 0.0 0.0
summed dirty queue length 85 0.0 0.0
switch current to new buffer 6,553 1.3 1.8
table fetch by rowid 3,324,124,504 632,083.0 910,968.6
Instance Activity Stats DB/Inst: GAAP/GAAP Snaps: 41-81
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
table fetch continued row 276,040 52.5 75.7
table scan blocks gotten 112,671,164 21,424.5 30,877.3
table scan rows gotten 7,542,001,404 1,434,113.2 2,066,868.0
table scans (cache partitions) 0 0.0 0.0
table scans (long tables) 12 0.0 0.0
table scans (short tables) 264,501 50.3 72.5
total number of times SMON posted 9 0.0 0.0
transaction rollbacks 6 0.0 0.0
transaction tables consistent rea 0 0.0 0.0
transaction tables consistent rea 0 0.0 0.0
undo change vector size 35,527,160 6,755.5 9,736.1
user I/O wait time 3,794 0.7 1.0
user calls 5,499 1.1 1.5
user commits 3,649 0.7 1.0
user rollbacks 0 0.0 0.0
workarea executions - onepass 1 0.0 0.0
workarea executions - optimal 10,070 1.9 2.8
write clones created in backgroun 0 0.0 0.0
write clones created in foregroun 0 0.0 0.0
-------------------------------------------------------------
Instance Activity Stats DB/Inst: GAAP/GAAP Snaps: 41-81
-> Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
--------------------------------- --------------- ---------------
logons current 24 20
opened cursors current 345 315
session cursor cache count 150,878 151,109
workarea memory allocated 94 1,210
-------------------------------------------------------------
Instance Activity Stats DB/Inst: GAAP/GAAP Snaps: 41-81
-> Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
--------------------------------- ------------------ ---------
log switches (derived) 2 1.37
-------------------------------------------------------------
OS Statistics DB/Inst: GAAP/GAAP Snaps: 41-81
-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name
Statistic Total
------------------------- ----------------------
NUM_LCPUS 0
NUM_VCPUS 0
BUSY_TIME 605,232
IDLE_TIME 1,503,360
IOWAIT_TIME 100,879
SYS_TIME 36,631
USER_TIME 568,601
OS_CPU_WAIT_TIME 928,000
PHYSICAL_MEMORY_BYTES 8,120,172,544
NUM_CPUS 4
NUM_CPU_CORES 2
-------------------------------------------------------------
Tablespace IO Stats DB/Inst: GAAP/GAAP Snaps: 41-81
->ordered by IOs (Reads + Writes) desc
Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
GAAP_DATA
20,106 4 1.1 13.8 2,224 0 0 0.0
GAAP_TEMP
1,205 0 6.6 31.0 1,205 0 0 0.0
SYSTEM
726 0 4.8 2.8 114 0 0 0.0
UNDOTBS1
10 0 9.0 1.0 745 0 7 0.0
PERFSTAT
95 0 6.1 1.0 563 0 0 0.0
SYSAUX
205 0 5.6 1.0 198 0 0 0.0
USERS
2 0 15.0 1.0 2 0 0 0.0
-------------------------------------------------------------
File IO Stats DB/Inst: GAAP/GAAP Snaps: 41-81
->Mx Rd Bkt: Max bucket time for single block read
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Mx Av
Av Rd Rd Av Av Buffer BufWt
Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms)
-------------- ------- ----- --- ------- ------------ -------- ---------- ------
GAAP_DATA /db/GAAP/gaap_data1.dbf
9,613 2 1.1 ### 14.3 821 0 0
/db/GAAP/gaap_data2.dbf
10,493 2 1.2 64 13.3 1,403 0 0
GAAP_TEMP /db/GAAP/gaap_temp.dbf
1,205 0 6.6 31.0 1,205 0 0
PERFSTAT /db/GAAP/perfstat.dbf
95 0 6.1 32 1.0 563 0 0
SYSAUX /db/GAAP/sysaux01.dbf
205 0 5.6 16 1.0 198 0 0
SYSTEM /db/GAAP/system01.dbf
726 0 4.8 64 2.8 114 0 0
UNDOTBS1 /db/GAAP/undotbs01.dbf
10 0 9.0 16 1.0 745 0 7 0.0
USERS /db/GAAP/users01.dbf
2 0 15.0 1.0 2 0 0
-------------------------------------------------------------
File Read Histogram Stats DB/Inst: GAAP/GAAP Snaps: 41-81
->Number of single block reads in each time range
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms
------------ ------------ ------------ ------------ ------------ ------------
SYSTEM /db/GAAP/system01.dbf
240 91 224 54 1 2
UNDOTBS1 /db/GAAP/undotbs01.dbf
0 1 6 1 0 0
SYSAUX /db/GAAP/sysaux01.dbf
25 34 118 25 0 0
PERFSTAT /db/GAAP/perfstat.dbf
7 14 47 24 1 0
GAAP_DATA /db/GAAP/gaap_data1.dbf
589 88 232 62 3 1
/db/GAAP/gaap_data2.dbf
957 122 359 139 8 4
-------------------------------------------------------------
Buffer Pool Statistics DB/Inst: GAAP/GAAP Snaps: 41-81
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
-> Buffers: the number of buffers. Units of K, M, G are divided by 1000
Free Writ Buffer
Pool Buffer Physical Physical Buffer Comp Busy
P Buffers Hit% Gets Reads Writes Waits Wait Waits
--- ------- ---- -------------- ------------ ----------- ------- ---- ----------
D 88K 100 236,270,264 279,681 13,892 0 0 7
-------------------------------------------------------------
Instance Recovery Stats DB/Inst: GAAP/GAAP Snaps: 41-81
-> B: Begin snapshot, E: End snapshot
Targt Estd Log File Log Ckpt Log Ckpt
MTTR MTTR Recovery Actual Target Size Timeout Interval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
- ----- ----- ---------- --------- --------- ---------- --------- ------------
B 0 13 2639 40942 184320 184320 236438
E 0 11 1567 17144 27545 184320 27545
-------------------------------------------------------------
Buffer Pool Advisory DB/Inst: GAAP/GAAP End Snap: 81
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Pool, Block Size, Buffers For Estimate
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
--- -------- ----- ------------ ------ -------------- ------------ --------
D 68 .1 8 1.0 2,619,875 144,590 22.4
D 136 .2 17 1.0 2,614,716 139,835 21.7
D 204 .3 25 1.0 2,610,774 136,202 21.1
D 272 .4 34 1.0 2,607,678 133,349 20.7
D 340 .5 42 1.0 2,605,158 131,027 20.3
D 408 .6 50 1.0 2,603,042 129,077 20.0
D 476 .7 59 1.0 2,601,219 127,397 19.7
D 544 .8 67 1.0 2,599,615 125,919 19.5
D 612 .9 76 1.0 2,598,178 124,594 19.3
D 680 .9 84 1.0 2,596,872 123,390 19.1
D 716 1.0 89 1.0 2,596,214 122,784 19.0
D 748 1.0 93 1.0 2,595,671 122,283 19.0
D 816 1.1 101 1.0 2,594,555 121,255 18.8
D 884 1.2 109 1.0 2,593,511 120,293 18.6
D 952 1.3 118 1.0 2,592,527 119,386 18.5
D 1,020 1.4 126 1.0 2,591,595 118,527 18.4
D 1,088 1.5 135 1.0 2,590,707 117,709 18.2
D 1,156 1.6 143 1.0 2,589,858 116,927 18.1
D 1,224 1.7 151 1.0 2,589,044 116,176 18.0
D 1,292 1.8 160 1.0 2,586,056 113,422 17.6
D 1,360 1.9 168 1.0 2,583,058 110,660 17.2
-------------------------------------------------------------
Buffer wait Statistics DB/Inst: GAAP/GAAP Snaps: 41-81
-> ordered by wait time desc, waits desc
Class Waits Total Wait Time (s)
-------------------------------------------- ----------- -------------------
Avg Time (ms)
-------------
undo header 7 0
0
-------------------------------------------------------------
PGA Aggr Target Stats DB/Inst: GAAP/GAAP Snaps: 41-81
-> B: Begin snap E: End snap (rows identified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
-> 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 Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
69.4 483 212
%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem
Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
- --------- --------- ---------- ---------- ------ ------ ------ ----------
B 280 205 104.2 0.1 .1 100.0 .0 57,344
E 280 208 168.2 1.2 .7 100.0 .0 57,344
-------------------------------------------------------------
PGA Aggr Target Histogram DB/Inst: GAAP/GAAP Snaps: 41-81
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 9,804 9,804 0 0
64K 128K 33 33 0 0
128K 256K 5 5 0 0
256K 512K 3 3 0 0
512K 1024K 188 188 0 0
1M 2M 35 35 0 0
2M 4M 12 12 0 0
128M 256M 1 0 1 0
-------------------------------------------------------------
PGA Memory Advisory DB/Inst: GAAP/GAAP End Snap: 81
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0
Estd Extra Estd PGA Estd PGA
PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
Est (MB) Factr Processed Written to Disk Hit % Count
---------- ------- ---------------- ---------------- -------- ----------
35 0.1 127,398.6 195,916.3 39.0 4,608
70 0.3 127,398.6 47,711.8 73.0 38
140 0.5 127,398.6 42,989.9 75.0 0
210 0.8 127,398.6 42,858.9 75.0 0
280 1.0 127,398.6 42,592.0 75.0 0
336 1.2 127,398.6 41,286.0 76.0 0
392 1.4 127,398.6 34,971.3 78.0 0
448 1.6 127,398.6 31,468.5 80.0 0
504 1.8 127,398.6 28,262.3 82.0 0
560 2.0 127,398.6 28,262.3 82.0 0
840 3.0 127,398.6 28,262.3 82.0 0
1,120 4.0 127,398.6 28,262.3 82.0 0
1,680 6.0 127,398.6 28,262.3 82.0 0
2,240 8.0 127,398.6 28,262.3 82.0 0
-------------------------------------------------------------
Process Memory Summary Stats DB/Inst: GAAP/GAAP Snaps: 41-81
-> 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
-> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of
processes. For Category lines, it is the number of allocations
-> ordered by Begin/End snapshot, Alloc (MB) desc
Hist Num
Avg Std Dev Max Max Procs
Alloc Used Freeabl Alloc Alloc Alloc Alloc or
Category (MB) (MB) (MB) (MB) (MB) (MB) (MB) Allocs
-- -------- --------- --------- -------- -------- ------- ------- ------ ------
B -------- 104.2 53.1 18.4 4.0 6.1 23 98 26
Other 82.5 3.2 5.6 23 23 26
Freeable 18.4 .0 1.1 .9 4 17
SQL 2.3 1.3 .1 .1 0 93 18
PL/SQL 1.0 .3 .0 .1 0 9 24
E -------- 168.2 50.7 89.6 7.6 17.2 79 98 22
Freeable 89.6 .0 6.9 20.2 74 13
Other 74.7 3.4 6.1 23 23 22
SQL 3.2 2.4 .2 .4 1 93 14
PL/SQL .8 .2 .0 .1 0 0 20
-------------------------------------------------------------
Top Process Memory (by component) DB/Inst: GAAP/GAAP Snaps: 41-81
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
-- ------ ------------- ------- ------- -------- ---------- ----------
B 6 LGWR -------- 23.5 11.0 .1 23.5 23.5
Other 23.4 23.4 23.4
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
5 DBW0 -------- 23.0 8.9 3.6 23.0 23.2
Other 19.4 19.4 19.5
Freeable 3.6 .0 3.6
PL/SQL .0 .0 .0 .0
11 MMON -------- 9.1 6.9 1.7 9.1 9.2
Other 7.3 7.3 7.3
Freeable 1.7 .0 1.7
SQL .1 .0 .1 1.4
PL/SQL .0 .0 .0 .1
15 TNS V1-V3 --- 6.1 2.3 2.1 6.1 22.5
Other 3.8 3.8 9.3
Freeable 2.1 .0 2.1
SQL .2 .1 .2 2.5
PL/SQL .0 .0 .0 8.7
20 ------------ 5.3 4.3 .1 5.3 78.7
Other 4.7 4.7 5.5
SQL .4 .3 .4 73.0
PL/SQL .2 .1 .2 .2
Freeable .1 .0 .1
21 ------------ 4.7 3.5 .9 4.7 78.9
Other 3.5 3.5 4.8
Freeable .9 .0 .9
PL/SQL .1 .0 .1 .1
SQL .1 .1 .1 73.0
24 ------------ 4.6 3.0 1.0 4.6 97.7
Other 3.1 3.1 3.5
Freeable 1.0 .0 1.0
SQL .3 .2 .3 93.0
PL/SQL .1 .0 .1 .1
16 ------------ 4.1 2.5 1.0 4.1 7.1
Other 2.7 2.7 3.0
Freeable 1.0 .0 1.0
SQL .2 .1 .2 2.9
PL/SQL .1 .0 .1 .2
26 ------------ 3.9 2.3 1.1 3.9 78.0
Other 2.4 2.4 3.6
Freeable 1.1 .0 1.1
SQL .3 .2 .3 73.1
PL/SQL .2 .0 .2 .2
8 SMON -------- 3.0 .8 1.6 3.0 3.0
Freeable 1.6 .0 1.6
Other 1.2 1.2 1.2
SQL .1 .1 .1 .9
PL/SQL .0 .0 .0 .0
10 CJQ0 -------- 2.3 .7 1.1 2.3 2.3
Freeable 1.1 .0 1.1
Other 1.1 1.1 1.1
SQL .1 .0 .1 1.0
PL/SQL .0 .0 .0 .0
Top Process Memory (by component) DB/Inst: GAAP/GAAP Snaps: 41-81
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
-- ------ ------------- ------- ------- -------- ---------- ----------
B 17 ------------ 2.1 .7 1.1 2.1 8.5
Freeable 1.1 .0 1.1
Other .9 .9 3.5
PL/SQL .1 .0 .1 .1
SQL .1 .0 .1 3.8
7 CKPT -------- 1.7 .4 .9 1.7 2.6
Freeable .9 .0 .9
Other .8 .8 1.7
SQL .0 .0 .0 .1
PL/SQL .0 .0 .0 .0
27 ------------ 1.6 .6 .6 1.6 1.6
Other .9 .9 .9
Freeable .6 .0 .6
SQL .1 .0 .1 .7
PL/SQL .0 .0 .0 .0
9 RECO -------- 1.6 .6 .7 1.6 1.6
Other .7 .7 .7
Freeable .7 .0 .7
SQL .1 .1 .1 .6
PL/SQL .0 .0 .0 .0
23 ------------ 1.5 .6 .4 1.5 1.5
Other 1.0 1.0 1.0
Freeable .4 .0 .4
SQL .1 .0 .1 .7
PL/SQL .0 .0 .0 .0
25 ------------ 1.5 .7 .3 1.5 1.5
Other .9 .9 .9
Freeable .3 .0 .3
SQL .3 .1 .3 .7
PL/SQL .0 .0 .0 .0
E 21 ------------ 78.7 4.6 74.1 78.7 78.9
Freeable 74.1 .0 74.1
Other 3.0 3.0 3.0
SQL 1.4 1.3 1.4 73.0
PL/SQL .1 .0 .1 .1
6 LGWR -------- 23.5 11.0 .1 23.5 23.5
Other 23.4 23.4 23.4
Freeable .1 .0 .1
PL/SQL .0 .0 .0 .0
5 DBW0 -------- 23.0 8.9 3.6 23.0 23.2
Other 19.4 19.4 19.5
Freeable 3.6 .0 3.6
PL/SQL .0 .0 .0 .0
11 MMON -------- 9.4 6.9 1.9 9.4 9.5
Other 7.3 7.3 7.3
Freeable 1.9 .0 1.9
SQL .1 .0 .1 1.5
PL/SQL .0 .0 .0 .1
20 ------------ 4.6 3.6 .9 4.6 78.7
Other 3.2 3.2 4.6
Freeable .9 .0 .9
SQL .3 .2 .3 73.0
PL/SQL .2 .0 .2 .2
Top Process Memory (by component) DB/Inst: GAAP/GAAP Snaps: 41-81
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
-- ------ ------------- ------- ------- -------- ---------- ----------
E 15 J000 -------- 4.4 1.3 2.0 4.4 4.4
Other 2.2 2.2 2.2
Freeable 2.0 .0 2.0
SQL .2 .1 .2 2.4
PL/SQL .0 .0 .0 .0
24 ------------ 4.3 3.5 .8 4.3 97.7
Other 3.1 3.1 3.7
Freeable .8 .0 .8
SQL .3 .2 .3 93.0
PL/SQL .1 .0 .1 .1
16 ------------ 3.6 2.6 1.0 3.6 7.1
Other 2.3 2.3 3.0
Freeable 1.0 .0 1.0
SQL .2 .1 .2 2.9
PL/SQL .1 .0 .1 .2
26 ------------ 3.3 2.5 .8 3.3 78.0
Other 2.1 2.1 4.0
Freeable .8 .0 .8
SQL .3 .2 .3 73.1
PL/SQL .2 .0 .2 .2
8 SMON -------- 3.0 .8 1.3 3.0 3.0
Other 1.6 1.6 1.6
Freeable 1.3 .0 1.3
SQL .1 .0 .1 .9
PL/SQL .0 .0 .0 .0
10 CJQ0 -------- 2.3 .7 1.4 2.3 2.3
Freeable 1.4 .0 1.4
Other .8 .8 .8
SQL .1 .0 .1 1.0
PL/SQL .0 .0 .0 .0
7 CKPT -------- 1.7 .4 .9 1.7 2.6
Freeable .9 .0 .9
Other .8 .8 1.7
SQL .0 .0 .0 .1
PL/SQL .0 .0 .0 .0
9 RECO -------- 1.6 .6 .7 1.6 1.6
Other .7 .7 .7
Freeable .7 .0 .7
SQL .1 .1 .1 .6
PL/SQL .0 .0 .0 .0
-------------------------------------------------------------
Undo Segment Summary DB/Inst: GAAP/GAAP Snaps: 41-81
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count, OOS - Out Of Space count
-> Undo segment block stats:
uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/
TS# Blocks (K) Transactions Len (s) Concy TR (mins) OOS eS/eR/eU
---- ---------- --------------- -------- ---------- --------- ----- -----------
1 6.9 9,452 1,710 4 15/43.5 0/0 0/0/0/0/0/0
-------------------------------------------------------------
Undo Segment Stats DB/Inst: GAAP/GAAP Snaps: 41-81
-> Most recent 35 Undostat rows, ordered by End Time desc
Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/
End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU
------------ ----------- ------------ ------- ------- ------- ----- -----------
23-Dec 13:04 1,467 5,906 143 4 17 0/0 0/0/0/0/0/0
23-Dec 12:54 46 11 405 2 22 0/0 0/0/0/0/0/0
23-Dec 12:44 50 108 303 2 20 0/0 0/0/0/0/0/0
23-Dec 12:34 230 808 1,710 3 44 0/0 0/0/0/0/0/0
23-Dec 12:24 566 611 1,111 3 34 0/0 0/0/0/0/0/0
23-Dec 12:14 123 543 512 3 24 0/0 0/0/0/0/0/0
23-Dec 12:04 2,823 712 766 4 28 0/0 0/0/0/0/0/0
23-Dec 11:54 675 319 166 3 18 0/0 0/0/0/0/0/0
23-Dec 11:44 128 13 682 2 26 0/0 0/0/0/0/0/0
23-Dec 11:34 839 421 0 3 15 0/0 0/0/0/0/0/0
-------------------------------------------------------------
Latch Activity DB/Inst: GAAP/GAAP Snaps: 41-81
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
AWR Alerted Metric Eleme 13,064 0.0 0 0
Consistent RBA 5,152 0.0 0 0
FIB s.o chain latch 32 0.0 0 0
FOB s.o list latch 100 0.0 0 0
In memory undo latch 20,615 0.0 0 4,849 0.0
JS mem alloc latch 9 0.0 0 0
JS queue access latch 9 0.0 0 0
JS queue state obj latch 38,030 0.0 0 0
JS slv state obj latch 28 0.0 0 0
KMG MMAN ready and start 2,068 0.0 0 0
KMG resize request state 22 0.0 0 0
KTF sga latch 35,446 0.0 0 1,675 0.0
KWQMN job cache list lat 1 0.0 0 0
KWQP Prop Status 5 0.0 0 0
MQL Tracking Latch 0 0 105 0.0
Memory Management Latch 487 0.0 0 2,064 0.0
OS process 84 0.0 0 0
OS process allocation 1,804 0.0 0 0
OS process: request allo 20 0.0 0 0
PL/SQL warning settings 199 0.0 0 0
SGA IO buffer pool latch 17,325 0.0 0 17,325 0.0
SQL memory manager latch 6 0.0 0 1,751 0.0
SQL memory manager worka 122,899 0.0 0 0
Shared B-Tree 202 0.0 0 0
active checkpoint queue 2,538 0.0 0 0
active service list 10,636 0.0 0 1,763 0.0
archive control 14 0.0 0 0
begin backup scn array 2 0.0 0 0
buffer pool 97 0.0 0 0
cache buffer handles 1,886 0.0 0 0
cache buffers chains 472,938,626 0.0 0.0 0 577,647 0.0
cache buffers lru chain 60,697 0.0 0.0 0 607,402 0.0
cache table scan latch 0 0 17,651 0.0
channel handle pool latc 22 0.0 0 0
channel operations paren 24,725 0.0 0 0
checkpoint queue latch 97,178 0.0 0 15,122 0.0
client/application info 40 0.0 0 0
compile environment latc 2,122 0.0 0 0
dml lock allocation 12,780 0.0 0 0
dummy allocation 28 0.0 0 0
enqueue hash chains 125,653 0.0 0 18 0.0
enqueues 101,901 0.0 0.0 0 0
event group latch 8 0.0 0 0
file cache latch 180 0.0 0 0
hash table column usage 1,273 0.0 0 95,787 0.0
hash table modification 95 0.0 0 0
job workq parent latch 0 0 10 0.0
job_queue_processes para 93 0.0 0 0
kks stats 18,431 0.0 0 0
ksuosstats global area 363 0.0 0 0
Latch Activity DB/Inst: GAAP/GAAP Snaps: 41-81
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
ktm global data 40 0.0 0 0
kwqbsn:qsga 202 0.0 0 0
lgwr LWN SCN 5,936 0.0 0.0 0 0
library cache 1,322,407 0.0 0.1 0 29,536 0.0
library cache load lock 11,752 0.0 0 0
library cache lock 208,037 0.0 0 0
library cache lock alloc 1,179 0.0 0 0
library cache pin 948,437 0.0 0 21 0.0
library cache pin alloca 1,515 0.0 0 0
list of block allocation 637 0.0 0 0
loader state object free 26 0.0 0 0
longop free list parent 3 0.0 0 3 0.0
message pool operations 2 0.0 0 0
messages 48,837 0.0 0 0
mostly latch-free SCN 5,945 0.2 0.0 0 0
multiblock read objects 36,636 0.0 0 0
ncodef allocation latch 84 0.0 0 0
object queue header heap 84 0.0 0 218 0.0
object queue header oper 634,278 0.0 0.0 0 0
object stats modificatio 1 0.0 0 0
parallel query alloc buf 684 0.0 0 0
parameter list 2 0.0 0 0
parameter table allocati 35 0.0 0 0
post/wait queue 344 0.0 0 200 0.0
process allocation 20 0.0 0 8 0.0
process group creation 20 0.0 0 0
qmn task queue latch 748 0.0 0 0
redo allocation 26,708 0.0 0.0 0 233,656 0.0
redo copy 0 0 233,657 0.2
redo writing 21,684 0.0 0 0
resmgr group change latc 11 0.0 0 0
resmgr:actses active lis 28 0.0 0 0
resmgr:actses change gro 5 0.0 0 0
resmgr:free threads list 22 0.0 0 0
resmgr:schema config 6 0.0 0 0
row cache objects 1,802,670 0.0 0.0 0 3,213 0.0
rules engine rule set st 500 0.0 0 0
sequence cache 133 0.0 0 0
session allocation 626,782 0.0 0.0 0 0
session idle bit 13,106 0.0 0 0
session state list latch 58 0.0 0 0
session switching 84 0.0 0 0
session timer 1,763 0.0 0 0
shared pool 850,090 0.0 0.2 0 0
simulator hash latch 15,269,280 0.0 0.0 0 0
simulator lru latch 15,173,679 0.0 0.0 0 55,165 0.0
slave class 3 0.0 0 0
slave class create 12 8.3 1.0 0 0
sort extent pool 464 0.0 0 0
state object free list 2 0.0 0 0
Latch Activity DB/Inst: GAAP/GAAP Snaps: 41-81
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
statistics aggregation 140 0.0 0 0
threshold alerts latch 281 0.0 0 0
transaction allocation 13,727 0.0 0 0
transaction branch alloc 84 0.0 0 0
undo global data 35,911 0.0 0 0
user lock 19 0.0 0 0
-------------------------------------------------------------
Latch Sleep breakdown DB/Inst: GAAP/GAAP Snaps: 41-81
-> ordered by misses desc
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------- ------------ ----------- -----------
simulator lru latch 15,173,679 160 6 154
shared pool 850,090 54 11 44
library cache 1,322,407 20 2 18
slave class create 12 1 1 0
-------------------------------------------------------------
Latch Miss Sources DB/Inst: GAAP/GAAP Snaps: 41-81
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
cache buffers chains kcbgtcr: kslbegin excl 0 1 0
library cache kglpndl: child: before pro 0 2 1
shared pool kghalo 0 7 6
shared pool kghfrunp: alloc: cursor du 0 3 0
shared pool kgh: quiesce extents 0 1 0
simulator lru latch kcbs_resize_pool : interpo 0 4 0
simulator lru latch kcbs_simulate: simulate se 0 1 6
simulator lru latch kcbs_free_granule_sim_buff 0 1 0
slave class create ksvcreate 0 1 0
-------------------------------------------------------------
Dictionary Cache Stats DB/Inst: GAAP/GAAP Snaps: 41-81
->"Pct Misses" should be very low (< 2% in most cases)
->"Final Usage" is the number of cache entries being used in End Snapshot
Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_awr_control 92 0.0 0 2 1
dc_global_oids 105,885 0.1 0 0 14
dc_histogram_data 171,374 0.7 0 0 1,548
dc_histogram_defs 80,217 4.0 0 0 1,749
dc_object_grants 38 50.0 0 0 19
dc_object_ids 167,376 0.5 0 0 327
dc_objects 15,915 3.1 0 6 322
dc_profiles 8 0.0 0 0 1
dc_rollback_segments 923 0.0 0 0 22
dc_segments 24,712 4.5 0 6 216
dc_sequences 14 50.0 0 14 2
dc_tablespace_quotas 4 50.0 0 0 2
dc_tablespaces 15,931 0.0 0 0 8
dc_usernames 1,987 0.2 0 0 4
dc_users 130,531 0.0 0 0 9
outstanding_alerts 90 0.0 0 0 8
-------------------------------------------------------------
Library Cache Activity DB/Inst: GAAP/GAAP Snaps: 41-81
->"Pct Misses" should be very low
Get Pct Pin Pct
Namespace Requests Miss Requests Miss
------------------------------ ------------ ------ -------------- ------
Invali-
Reloads dations
---------- --------
SQL AREA 5,269 80.9 348,331 3.0
2,393 0
TABLE/PROCEDURE 9,829 5.5 145,017 4.0
2,988 0
BODY 20 60.0 155 20.6
20 0
INDEX 37 51.4 89 22.5
1 0
CLUSTER 26 3.8 422 1.7
6 0
-------------------------------------------------------------
Rule Sets DB/Inst: GAAP/GAAP Snaps: 41-81
-> * indicates Rule Set activity (re)started between Begin/End snaps
-> Top 25 ordered by Evaluations desc
No-SQL SQL
Rule * Eval/sec Reloads/sec Eval % Eval %
----------------------------------- -- ------------ ----------- ------ ------
SYS.ALERT_QUE_R 0 0 0 0
-------------------------------------------------------------
Shared Pool Advisory DB/Inst: GAAP/GAAP End Snap: 81
-> SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
-> Note there is often a 1:Many correlation between a single logical object
in the Library Cache, and the physical number of memory objects associated
with it. Therefore comparing the number of Lib Cache objects (e.g. in
v$librarycache), with the number of Lib Cache Memory Objects is invalid
Est LC Est LC Est LC Est LC
Shared SP Est LC Time Time Load Load Est LC
Pool Size Size Est LC Saved Saved Time Time Mem
Size (M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
100 .9 12 1,523 ####### 1.0 50,689 1.0 73,890,018
112 1.0 23 2,909 ####### 1.0 50,446 1.0 73,959,695
124 1.1 34 4,195 ####### 1.0 50,206 1.0 74,028,427
136 1.2 45 4,925 ####### 1.0 49,971 1.0 74,095,301
148 1.3 56 6,276 ####### 1.0 49,743 1.0 74,159,947
160 1.4 68 7,239 ####### 1.0 49,524 1.0 74,222,164
172 1.5 80 8,234 ####### 1.0 49,312 1.0 74,282,066
184 1.6 92 9,124 ####### 1.0 49,109 1.0 74,339,686
196 1.8 104 9,843 ####### 1.0 48,914 1.0 74,395,180
208 1.9 116 11,555 ####### 1.0 48,726 1.0 74,448,766
220 2.0 128 12,119 ####### 1.0 48,544 1.0 74,500,664
232 2.1 140 13,112 ####### 1.0 48,367 1.0 74,551,077
-------------------------------------------------------------
Cache Size Changes DB/Inst: GAAP/GAAP Snaps: 41-81
-> Not all cache size changes may be captured. Only cache changes which are
evident at snapshot time are shown
Prior New Difference
Snap Id Cache Size (MB) Size (MB) (MB)
--------- ------------ --------- --------- ----------
61 Buffer Cache 720 716 -4
Shared Pool 108 112 4
-------------------------------------------------------------
SGA Target Advisory DB/Inst: GAAP/GAAP End Snap: 81
SGA Target SGA Size Est DB Est DB Est Physical
Size (M) Factor Time (s) Time Factor Reads
---------- -------- -------- ----------- --------------
211 .3 667,279 1.0 2,619,320,372
422 .5 655,795 1.0 2,607,118,166
633 .8 649,343 1.0 2,600,627,631
844 1.0 645,150 1.0 2,596,214,067
1,055 1.3 641,666 1.0 2,592,838,989
1,266 1.5 638,892 1.0 2,589,983,153
1,477 1.8 632,634 1.0 2,582,454,132
1,688 2.0 631,473 1.0 2,582,454,132
-------------------------------------------------------------
SGA Memory Summary DB/Inst: GAAP/GAAP Snaps: 41-81
End Size (Bytes)
SGA regions Begin Size (Bytes) (if different)
------------------------------ -------------------- --------------------
Database Buffers 754,974,720 750,780,416
Fixed Size 2,024,888
Redo Buffers 6,361,088
Variable Size 121,637,448 125,831,752
-------------------- --------------------
sum 884,998,144 876,612,168
-------------------------------------------------------------
SGA breakdown difference DB/Inst: GAAP/GAAP Snaps: 41-81
-> Top 35 rows by size, ordered by Pool, Name (note rows with null values for
Pool column, or Names showing free memory are always shown)
-> Null 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 p free memory 4.0 4.0 0.00
large PX msg pool 1.0 1.0 0.00
large free memory 3.0 3.0 0.00
shared ASH buffers 8.0 8.0 0.00
shared CCursor 2.9 4.1 40.06
shared FileOpenBlock 1.4 1.4 0.00
shared KCB Table Scan Buffer 3.8 3.8 0.00
shared KGH: NO ACCESS 15.4 15.4 -0.10
shared KGLS heap 1.5 2.1 37.26
shared KQR M PO 1.8 -100.00
shared KSFD SGA I/O b 3.8 3.8 0.00
shared KTI-UNDO 1.3 1.3 0.00
shared PCursor 2.4 ########
shared XDB Schema Cac 7.0 7.0 0.00
shared db_block_hash_buckets 4.2 4.2 0.00
shared event statistics per sess 1.5 1.5 0.00
shared free memory 11.1 11.4 2.66
shared kglsim hash table bkts 4.0 4.0 0.00
shared kglsim heap 2.4 2.4 0.00
shared kglsim object batch 7.0 7.0 0.00
shared kks stbkt 1.5 1.5 0.00
shared library cache 8.6 9.1 5.99
shared private strands 2.3 2.3 0.00
shared row cache 7.1 7.1 0.00
shared sql area 2.5 3.0 16.67
buffer_cache 720.0 716.0 -0.56
fixed_sga 1.9 1.9 0.00
log_buffer 6.1 6.1 0.00
-------------------------------------------------------------
SQL Memory Statistics DB/Inst: GAAP/GAAP Snaps: 41-81
Begin End % Diff
-------------- -------------- --------------
Avg Cursor Size (KB): 35.66 29.91 -19.21
Cursor to Parent ratio: 1.26 1.25 -.72
Total Cursors: 463 610 24.10
Total Parents: 367 487 24.64
-------------------------------------------------------------
init.ora Parameters DB/Inst: GAAP/GAAP Snaps: 41-81
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
audit_file_dest /u01/oracle/admin/GAAP/adump
background_dump_dest /u01/oracle/admin/GAAP/bdump
compatible 10.2.0.1.0
control_files /db/GAAP/control01.ctl, /db/GAAP/
core_dump_dest /u01/oracle/admin/GAAP/cdump
db_block_size 8192
db_domain
db_file_multiblock_read_count 16
db_name GAAP
db_recovery_file_dest /u01/oracle/flash_recovery_area
db_recovery_file_dest_size 2147483648
dispatchers (PROTOCOL=TCP) (SERVICE=GAAPXDB)
job_queue_processes 10
nls_language SIMPLIFIED CHINESE
nls_territory CHINA
open_cursors 300
pga_aggregate_target 293601280
processes 150
remote_login_passwordfile EXCLUSIVE
sga_target 884998144
undo_management AUTO
undo_tablespace UNDOTBS1
user_dump_dest /u01/oracle/admin/GAAP/udump
-------------------------------------------------------------
End of Report ( /db/backup/gaap/statspack41-81.txt )
扫一扫
2072
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
