How to read statspack report
64 views
Skip to first unread message
swathi
Dec 2, 2008, 3:59:59 AM12/2/08
to Ask Oracle DBA
Hi All
We have Oracle 9i database on windows 2003 OS. Our system is very
slow. So I have staspack report for reference. Please help me in
analyzing this report taken for a period of 28 minutes.
Regards
swathi
Below is report
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster
Host
------------ ----------- ------------ -------- ----------- -------
------------
SFMS 1649285097 sfms 1 9.2.0.8.0 NO
DBSERVER
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- ---------
-------------------
Begin Snap: 147 14-Nov-08 17:10:00 135 2.3
End Snap: 148 14-Nov-08 17:38:11 113 3.0
Elapsed: 28.18 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,032M Std Block Size:
8K
Shared Pool Size: 1,024M Log Buffer:
512K
Load Profile
~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Redo size: 31,297.28
16,533.49
Logical reads: 5,916.56
3,125.56
Block changes: 127.63
67.42
Physical reads: 3,688.18
1,948.36
Physical writes: 5.58
2.95
User calls: 502.95
265.70
Parses: 64.34
33.99
Hard parses: 0.22
0.12
Sorts: 51.04
26.96
Logons: 4.44
2.34
Executes: 67.26
35.53
Transactions: 1.89
% Blocks changed per Read: 2.16 Recursive Call %: 25.90
Rollback per transaction %: 18.62 Rows per Sort: 16.34
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 97.87 Redo NoWait %: 100.00
Buffer Hit %: 37.70 In-memory Sort %: 99.98
Library Hit %: 99.84 Soft Parse %: 99.66
Execute to Parse %: 4.34 Latch Hit %: 99.11
Parse CPU to Parse Elapsd %: 94.39 % Non-Parse CPU: 96.91
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 62.28 62.98
% SQL with executions>1: 43.83 43.69
% Memory for SQL w/exec>1: 56.20 56.07
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
% Total
Event Waits Time (s)
Ela Time
-------------------------------------------- ------------ -----------
--------
db file scattered read 473,574
4,328 51.13
ARCH wait on SENDREQ 171
1,667 19.69
buffer busy waits 213,401
1,630 19.26
db file sequential read 113,772
500 5.91
CPU time
234 2.76
-------------------------------------------------------------
Wait Events for DB: SFMS Instance: sfms Snaps: 147 -148
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait
wait Waits
Event Waits Timeouts Time (s)
(ms) /txn
---------------------------- ------------ ---------- ---------- ------
--------
db file scattered read 473,574 0 4,328
9 147.9
ARCH wait on SENDREQ 171 0 1,667
9747 0.1
buffer busy waits 213,401 16 1,630
8 66.7
db file sequential read 113,772 0 500
4 35.5
db file parallel write 501 0 45
90 0.2
log file sync 2,990 4 32
11 0.9
control file parallel write 573 0 10
18 0.2
latch free 6,639 6,542 10
1 2.1
control file sequential read 452 0 3
7 0.1
enqueue 35 0 3
78 0.0
direct path read 1,794 0 2
1 0.6
log file parallel write 4,081 0 0
0 1.3
direct path write 91 0 0
4 0.0
ARCH wait on ATTACH 1 0 0
180 0.0
SQL*Net more data to client 1,126 0 0
0 0.4
SQL*Net break/reset to clien 22 0 0
0 0.0
log file sequential read 13 0 0
0 0.0
LGWR wait for redo copy 168 0 0
0 0.1
async disk IO 56 0 0
0 0.0
undo segment extension 1 1 0
0 0.0
buffer deadlock 2 2 0
0 0.0
virtual circuit status 942,800 915 34,617
37 294.5
SQL*Net message from client 857,967 0 4,247
5 268.0
wakeup time manager 56 56 1,616
28854 0.0
SQL*Net message to client 857,950 0 2
0 268.0
SQL*Net more data from clien 5,485 0 0
0 1.7
-------------------------------------------------------------
Background Wait Events for DB: SFMS Instance: sfms Snaps: 147 -148
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait
wait Waits
Event Waits Timeouts Time (s)
(ms) /txn
---------------------------- ------------ ---------- ---------- ------
--------
ARCH wait on SENDREQ 171 0 1,667
9747 0.1
db file parallel write 501 0 45
90 0.2
control file parallel write 573 0 10
18 0.2
control file sequential read 368 0 3
7 0.1
db file sequential read 286 0 0
2 0.1
log file parallel write 4,082 0 0
0 1.3
direct path write 57 0 0
4 0.0
ARCH wait on ATTACH 1 0 0
180 0.0
direct path read 74 0 0
2 0.0
log file sequential read 13 0 0
0 0.0
rdbms ipc reply 12 0 0
0 0.0
LGWR wait for redo copy 168 0 0
0 0.1
async disk IO 56 0 0
0 0.0
db file scattered read 1 0 0
0 0.0
buffer busy waits 1 0 0
0 0.0
rdbms ipc message 5,258 1,735 7,982
1518 1.6
pmon timer 1,103 1,103 1,690
1532 0.3
smon timer 25 0 1,584
63363 0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: SFMS Instance: sfms Snaps: 147 -148
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used
by
all SQL statements called within the PL/SQL code. As individual
SQL
statements are also reported, it is possible and valid for the
summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
Hash Value
--------------- ------------ -------------- ------ -------- ---------
----------
3,537,730 7 505,390.0 35.4 102.47 1444.15
965668055
Module: JDBC Thin Client
SELECT * FROM SMB_SYS_MSG_BR WHERE SMB_IN_OUT_IND = :1 AND SMB_
OWNR_ADDRS = :2 AND to_char(SMB_SEND_DATE,'%Y') = :3 AND to_cha
r(SMB_SEND_DATE,'%m') = :4 AND to_char(SMB_SEND_DATE,'%d') = :5
1,041,674 2 520,837.0 10.4 27.39 312.75
3847947103
Module: JDBC Thin Client
SELECT * FROM SMB_SYS_MSG_BR WHERE SMB_IN_OUT_IND = :1 AND SMB_O
WNR_ADDRS = :2 ORDER BY SMB_MSG_STAT,DM_LSTUPDDT desc
336,831 519 649.0 3.4 3.45 3.50
3785048508
Module: JDBC Thin Client
SELECT substr(SCD_MNEMNC,4)||'--'||SCD_CODE_DESC MNMNCDESC FROM
SCD_SYS_CODE_DEF WHERE SCD_CODE_ID=:1 AND SCD_CODE_VAL IN (100,1
01,102,103,1000,104,105,107,110,111,112,190,191,192,195,196,198,
199,200,201,202,203,204,205,206,207,210,256,290,291,292,293,295,
296,298,2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2
224,280 7,476 30.0 2.2 7.48 6.90
1356713530
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with (grantee#=:1 or grantee#=
1) and privilege#>0
221,022 713 310.0 2.2 6.84 7.01
3199451410
Module: JDBC Thin Client
SELECT END_IFSC,END_CERT_NAME FROM END_ENC_CERT_DTLS
194,213 2,966 65.5 1.9 4.00 22.73
826609426
Module: JDBC Thin Client
INSERT INTO MPT_MSG_PROC_TBL VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,
:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,
:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,
:42,:43,:44,:45,:46,:47,:48,:49,:50,:51)
181,972 194 938.0 1.8 4.47 4.63
212880470
Module: JDBC Thin Client
SELECT SCD_CODE_VAL FROM SCD_SYS_CODE_DEF WHERE SCD_CODE_ID=:1
AND SCD_LANG_CODE= :2 AND SCD_CODE_VAL NOT IN (SELECT SCD_CODE_
VAL FROM SCD_SYS_CODE_DEF WHERE SCD_CODE_ID=:3) ORDER BY SCD_MNE
MNC
116,594 194 601.0 1.2 1.31 1.21
3193493324
Module: JDBC Thin Client
SELECT substr(SCD_MNEMNC,4)||'--'||SCD_CODE_DESC MNMNCDESC FROM
SCD_SYS_CODE_DEF WHERE SCD_CODE_ID=:1 AND SCD_CODE_VAL IN (100,1
01,102,103,1000,104,105,107,110,111,112,190,191,192,195,196,198,
199,200,201,202,203,204,205,206,207,210,256,290,291,292,293,295,
296,298,2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2
SQL ordered by Gets for DB: SFMS Instance: sfms Snaps: 147 -148
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used
by
all SQL statements called within the PL/SQL code. As individual
SQL
statements are also reported, it is possible and valid for the
summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
Hash Value
--------------- ------------ -------------- ------ -------- ---------
----------
75,458 2 37,729.0 0.8 4.09 9.38
1229324988
Module: JDBC Thin Client
SELECT MHB_USER_ID, MHB_CRT_RCV_SEQ_NUM, MHB_MSG_STAT FROM MHB_M
SG_HDR_BR WHERE MHB_UTR = 'CNRBH08319300805' AND MHB_IN_OUT_IND
= 2 FOR UPDATE
52,770 26,387 2.0 0.5 3.02 2.71
3329356112
Module: JDBC Thin Client
SELECT 1 FROM SCD_SYS_CODE_DEF WHERE SCD_CODE_ID = :1 AND SCD_CO
DE_VAL = :2 AND SCD_LANG_CODE = :3
52,515 1,067 49.2 0.5 0.34 0.43
2243488166
Module: JDBC Thin Client
select COUNT(*) from mpt_msg_proc_tbl where MPT_OWNR_ADDRS=:1 AN
D MPT_IN_OUT_IND=:2 and mpt_crt_rcv_num =:3 and MPT_MSG_SEQ=:4
and mpt_sub_elem_id=:5
44,377 5 8,875.4 0.4 1.02 1.03
2845187387
Module: JDBC Thin Client
SELECT UTT_TEMPL_ID,MHB_MSG_TYP,UTT_TEMPL_DESC,MHB_CRT_RCV_SEQ_N
UM,MHB_RCVR_SNDR_ADDRS,MHB_MUR,MHB_MSG_HDR_BR_VER,DM_LSTUPDDT FR
OM MHB_MSG_HDR_BR,UTT_USR_TEMPL_TBL WHERE to_char(UTT_TEMPL_ID)=
substr(MHB_SNDR_REF,12) AND UTT_TEMPL_IFSC=MHB_OWNR_ADDRS AND U
TT_TEMPL_IFSC=:1 AND MHB_MSG_TYP IN ( 100,101,102,103,1000,104,
35,178 533 66.0 0.4 1.19 1.09
3244566476
Module: JDBC Thin Client
SELECT SCD_CODE_VAL FROM SCD_SYS_CODE_DEF WHERE SCD_CODE_ID=:1
AND SCD_LANG_CODE= :2 AND ( SCD_MNEMNC NOT LIKE 'IFN298I%' AND
SCD_MNEMNC NOT LIKE 'IFN298A%') AND SCD_MNEMNC NOT IN ('IFN500',
'IFN501','IFN502','IFN509','IFN513','IFN535','IFN536','IFN537','
IFN540','IFN541','IFN542','IFN543','IFN544','IFN545','IFN546','I
29,438 2,816 10.5 0.3 0.97 14.83
1364225184
Module: JDBC Thin Client
SELECT MPT_ELEM_VALUE FROM MPT_MSG_PROC_TBL WHERE MPT_IN_OUT_IN
D = :1 AND MPT_OWNR_ADDRS = :2 AND MPT_CRT_RCV_NUM = :3 AND MPT
_FLD_ID IN (20, 2020) AND MPT_FLD_OPTION='-' ORDER BY MPT_ELEM_
SEQ_D1, MPT_ELEM_SEQ_D2, MPT_ELEM_SEQ_D3, MPT_ELEM_SEQ_D4, MPT_
ELEM_SEQ_D5, MPT_ELEM_SEQ_D6, MPT_ELEM_SEQ_D7, MPT_ELEM_SEQ_D8,
26,334 399 66.0 0.3 0.36 0.30
1775992258
Module: JDBC Thin Client
SELECT SCD_CODE_VAL FROM SCD_SYS_CODE_DEF WHERE SCD_CODE_ID =
:1 AND SCD_MNEMNC = :2
24,975 925 27.0 0.2 0.28 0.21
1994601063
Module: JDBC Thin Client
SELECT RFM_FUNC_ID FROM RFM_ROLE_FUNC_MAP WHERE RFM_ROLE_ID =
:1 AND RFM_FUNC_ID IN (:2,:3,:4,:5,:6,:7,:8,:9,:10) AND RFM_IFSC
SQL ordered by Gets for DB: SFMS Instance: sfms Snaps: 147 -148
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used
by
all SQL statements called within the PL/SQL code. As individual
SQL
statements are also reported, it is possible and valid for the
summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
Hash Value
--------------- ------------ -------------- ------ -------- ---------
----------
= :11 AND RFM_AGENCY = :12
21,410 56 382.3 0.2 0.42 0.63
2614951860
select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
ables t, sys.aq$_queue_table_affinities aft, system.aq$_que
ues q where aft.table_objno = t.objno and aft.owner_instance = :
1 and q.table_objno = t.objno and q.usage = 0 and b
itand(t.flags, 4+16+32+64+128+256) = 0 and NOT ( t.n
19,538 24 814.1 0.2 0.13 0.15
418953923
Module: JDBC Thin Client
SELECT COUNT(*) FROM MHB_MSG_HDR_BR WHERE MHB_IN_OUT_IND = 1 AN
D MHB_TRNG_FLAG = 2 AND MHB_OWNR_ADDRS = 'CNRB0001098' AND MHB_M
SG_STAT <> 19 AND MHB_MSG_STAT <> 50 AND MHB_AGENCY = 1 AND MHB
_MSG_STAT <> 18 AND MHB_MSG_TYP IN (100,101,102,103,1000,104,105
-------------------------------------------------------------
SQL ordered by Reads for DB: SFMS Instance: sfms Snaps: 147 -148
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
--------------- ------------ -------------- ------ -------- ---------
----------
3,130,973 7 447,281.9 50.2 102.47 1444.15
965668055
Module: JDBC Thin Client
SELECT * FROM SMB_SYS_MSG_BR WHERE SMB_IN_OUT_IND = :1 AND SMB_
OWNR_ADDRS = :2 AND to_char(SMB_SEND_DATE,'%Y') = :3 AND to_cha
r(SMB_SEND_DATE,'%m') = :4 AND to_char(SMB_SEND_DATE,'%d') = :5
1,009,508 2 504,754.0 16.2 27.39 312.75
3847947103
Module: JDBC Thin Client
SELECT * FROM SMB_SYS_MSG_BR WHERE SMB_IN_OUT_IND = :1 AND SMB_O
WNR_ADDRS = :2 ORDER BY SMB_MSG_STAT,DM_LSTUPDDT desc
11,710 4 2,927.5 0.2 0.70 7.95
2750276848
Module: JDBC Thin Client
DELETE FROM MTR_MSG_TRN_REF WHERE MTR_OWNR_ADDRS = :1 and MTR_IN
_OUT_IND = :2 and MTR_CRT_RCV_NUM = :3
3,448 1 3,448.0 0.1 1.84 8.05
4131769790
Module: JDBC Thin Client
SELECT MHB_OWNR_ADDRS ,MHB_CRT_RCV_SEQ_NUM,MHB_SNDR_REF,MHB_RCVR
_SNDR_ADDRS,MHB_MSG_STAT,MHB_MSG_TYP,MHB_OPEN_FLG,MHB_CRTN_DATE,
MHB_REC_DATE,MHB_USER_ID,MHB_MSG_HDR_BR_VER,DM_LSTUPDDT,MHB_UTR
FROM MHB_MSG_HDR_BR WHERE MHB_IN_OUT_IND = 1 AND MHB_TRNG_FLAG
= 2 AND MHB_OWNR_ADDRS = 'CNRB0MUMCLG' AND MHB_MSG_STAT <> 19 AN
1,703 2,966 0.6 0.0 4.00 22.73
826609426
Module: JDBC Thin Client
INSERT INTO MPT_MSG_PROC_TBL VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,
:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,
:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,
:42,:43,:44,:45,:46,:47,:48,:49,:50,:51)
1,237 2,816 0.4 0.0 0.97 14.83
1364225184
Module: JDBC Thin Client
SELECT MPT_ELEM_VALUE FROM MPT_MSG_PROC_TBL WHERE MPT_IN_OUT_IN
D = :1 AND MPT_OWNR_ADDRS = :2 AND MPT_CRT_RCV_NUM = :3 AND MPT
_FLD_ID IN (20, 2020) AND MPT_FLD_OPTION='-' ORDER BY MPT_ELEM_
SEQ_D1, MPT_ELEM_SEQ_D2, MPT_ELEM_SEQ_D3, MPT_ELEM_SEQ_D4, MPT_
ELEM_SEQ_D5, MPT_ELEM_SEQ_D6, MPT_ELEM_SEQ_D7, MPT_ELEM_SEQ_D8,
658 2 329.0 0.0 4.09 9.38
1229324988
Module: JDBC Thin Client
SELECT MHB_USER_ID, MHB_CRT_RCV_SEQ_NUM, MHB_MSG_STAT FROM MHB_M
SG_HDR_BR WHERE MHB_UTR = 'CNRBH08319300805' AND MHB_IN_OUT_IND
= 2 FOR UPDATE
612 9 68.0 0.0 0.20 0.74
2315869717
Module: JDBC Thin Client
SELECT FMI_ERR_DESC,FMI_USER_ID,FMI_RSND_CNT,DM_LSTUPDDT,FMI_SND
R_IFSC,FMI_CRTN_DATE,FMI_DCRYPTD_MSG_1,FMI_DCRYPTD_MSG_2,FMI_DCR
YPTD_MSG_3 FROM FMI_FAIL_MSG_INFO WHERE FMI_SNDR_REF=:1
506 1 506.0 0.0 1.03 2.53
2153809700
Module: JDBC Thin Client
SELECT COUNT(*) FROM MHB_MSG_HDR_BR WHERE MHB_IN_OUT_IND = 1 AN
SQL ordered by Reads for DB: SFMS Instance: sfms Snaps: 147 -148
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
--------------- ------------ -------------- ------ -------- ---------
----------
D MHB_TRNG_FLAG = 2 AND MHB_OWNR_ADDRS = 'CNRB0MUMCLG' AND MHB_M
SG_STAT <> 19 AND MHB_MSG_STAT <> 50 AND MHB_AGENCY = 1 AND MHB
_MSG_STAT <> 18 AND MHB_MSG_TYP IN (100,101,102,103,1000,104,105
,107,110,111,112,190,191,192,195,196,198,199,200,201,202,203,204
393 289 1.4 0.0 0.27 7.83
345990914
Module: JDBC Thin Client
INSERT INTO MTB_MSG_TXT_BR values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10
,1,:11)
286 1 286.0 0.0 0.03 0.38
4115672663
select u.name, o.name, t.spare1, t.pctfree$ from sys.obj$ o,
sys.user$ u, sys.tab$ t where (bitand(t.trigflag, 1048576) =
1048576) and o.obj#=t.obj# and o.owner# = u.user#
279 16 17.4 0.0 0.05 1.47
2817775879
Module: JDBC Thin Client
SELECT MHB_MSG_TYP, MHB_SNDR_REF,MHB_RCVR_SNDR_ADDRS,DM_LSTUPDDT
FROM MHB_MSG_HDR_BR_AR WHERE MHB_OWNR_ADDRS= :1 AND DM_LSTUP
DDT >= :2 AND DM_LSTUPDDT <= :3 AND MHB_MSG_STAT=:4 AND MHB_I
N_OUT_IND=:5 AND MHB_AGENCY=:6 AND MHB_TRNG_FLAG=:7 ORDER BY
DM_LSTUPDDT,MHB_MSG_TYP
245 5 49.0 0.0 0.06 2.25
3206473185
Module: JDBC Thin Client
SELECT MHB_CRT_RCV_SEQ_NUM ,MHB_MSG_TYP ,MHB_SNDR_REF ,MHB_RC
VR_SNDR_ADDRS ,MHB_CRTN_DATE ,MHB_PDE ,MHB_TRNG_FLAG ,MHB_MS
G_STAT FROM MHB_MSG_HDR_BR_AR WHERE MHB_OWNR_ADDRS= :1 AND MH
B_CRTN_DATE >= :2 AND MHB_CRTN_DATE <= :3 AND MHB_IN_OUT_IND=
:4 AND MHB_AGENCY=:5 ORDER BY MHB_CRTN_DATE
230 132 1.7 0.0 0.06 3.43
2452212640
Module: JDBC Thin Client
SELECT 1 FROM LLD_LOGIN_LOGOUT_DTLS_AR WHERE LLD_USER_ID=:1 AND
LLD_IFSC=:2
165 1 165.0 0.0 0.92 0.97
323607971
Module: JDBC Thin Client
UPDATE MHB_MSG_HDR_BR SET MHB_MSG_STAT='25' WHERE MHB_UTR = 'CNR
BH08319300805' AND MHB_IN_OUT_IND = 2
164 1 164.0 0.0 1.06 2.71
1728714374
Module: JDBC Thin Client
UPDATE MHB_MSG_HDR_BR SET MHB_MSG_STAT='27' WHERE MHB_UTR = 'CNR
BH08319300805' AND MHB_IN_OUT_IND = 2
130 358 0.4 0.0 0.23 3.02
3629200986
Module: JDBC Thin Client
INSERT INTO MTB_MSG_TXT_BR VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:1
0,:11,:12)
109 122 0.9 0.0 0.05 3.15
2966950249
Module: JDBC Thin Client
SELECT MTB_MSG_BLOCK5A FROM MTB_MSG_TXT_BR WHERE MTB_OWNR_ADDR
SQL ordered by Reads for DB: SFMS Instance: sfms Snaps: 147 -148
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
--------------- ------------ -------------- ------ -------- ---------
----------
S = :1 AND MTB_IN_OUT_IND =:2 AND MTB_CRT_RCV_SEQ_NUM = :3 unio
n all SELECT MTB_MSG_BLOCK5A FROM MTB_MSG_TXT_BR_AR WHERE MTB_O
WNR_ADDRS = :4 AND MTB_IN_OUT_IND =:5 AND MTB_CRT_RCV_SEQ_NUM =
:6
109 756 0.1 0.0 0.38 3.34
3003997262
Module: JDBC Thin Client
SELECT MTB_MSG_BLOCK1,MTB_MSG_BLOCK2,MTB_MSG_BLOCK3,MTB_MSG_BLOC
K4,MTB_MSG_BLOCK5A,MTB_MSG_BLOCK5B,MTB_MSG_BLOCK5C,MTB_MSG_TXT_B
R_VER,DM_LSTUPDDT FROM MTB_MSG_TXT_BR WHERE MTB_OWNR_ADDRS = :1
AND MTB_IN_OUT_IND = :2 AND MTB_CRT_RCV_SEQ_NUM = :3
-------------------------------------------------------------
SQL ordered by Executions for DB: SFMS Instance: sfms Snaps: 147
-148
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
------------ --------------- ---------------- ----------- ----------
----------
26,387 25,977 1.0 0.00 0.00
3329356112
Module: JDBC Thin Client
SELECT 1 FROM SCD_SYS_CODE_DEF WHERE SCD_CODE_ID = :1 AND SCD_CO
DE_VAL = :2 AND SCD_LANG_CODE = :3
7,476 194,376 26.0 0.00 0.00
1356713530
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with (grantee#=:1 or grantee#=
1) and privilege#>0
6,724 0 0.0 0.00 0.00
3570694802
Module: JDBC Thin Client
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN'
6,721 6,721 1.0 0.00 0.00
1702556642
Module: JDBC Thin Client
SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER ='NLS_
DATE_FORMAT'
6,715 0 0.0 0.00 0.00
2707901715
Module: JDBC Thin Client
ALTER SESSION SET NLS_TERRITORY = 'AMERICA'
4,094 6,086 1.5 0.00 0.00
1780264807
Module: JDBC Thin Client
SELECT * FROM MSD_SUBFLD_DEF WHERE MSD_MSG_SEQ= :1 AND MSD_FLD =
:2 AND MSD_OPTION = :3 ORDER BY MSD_SUBFLD_SEQ
4,094 4,094 1.0 0.00 0.00
4157967512
Module: JDBC Thin Client
SELECT COUNT(*) FROM MSD_SUBFLD_DEF WHERE MSD_MSG_SEQ = :1 AND M
SD_FLD = :2 AND MSD_OPTION = :3
3,531 3,531 1.0 0.00 0.00
2290514744
Module: JDBC Thin Client
SELECT UAI_CONNECT_STAT FROM UAI_USER_ACC_INFO WHERE UAI_USER_ID
= :1AND UAI_USER_NODE_ADDRS = :2
2,966 35,066 11.8 0.00 0.01
826609426
Module: JDBC Thin Client
INSERT INTO MPT_MSG_PROC_TBL VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,
:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,
:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,
:42,:43,:44,:45,:46,:47,:48,:49,:50,:51)
2,816 2,388 0.8 0.00 0.01
1364225184
Module: JDBC Thin Client
SELECT MPT_ELEM_VALUE FROM MPT_MSG_PROC_TBL WHERE MPT_IN_OUT_IN
D = :1 AND MPT_OWNR_ADDRS = :2 AND MPT_CRT_RCV_NUM = :3 AND MPT
_FLD_ID IN (20, 2020) AND MPT_FLD_OPTION='-' ORDER BY MPT_ELEM_
SEQ_D1, MPT_ELEM_SEQ_D2, MPT_ELEM_SEQ_D3, MPT_ELEM_SEQ_D4, MPT_
ELEM_SEQ_D5, MPT_ELEM_SEQ_D6, MPT_ELEM_SEQ_D7, MPT_ELEM_SEQ_D8,
1,537 1,537 1.0 0.00 0.00
1774114274
SQL ordered by Executions for DB: SFMS Instance: sfms Snaps: 147
-148
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
------------ --------------- ---------------- ----------- ----------
----------
Module: JDBC Thin Client
SELECT COUNT(*) FROM CWD_CODE_WORD WHERE CWD_MSG_SEQ = :1 AN
D CWD_FLD_ID = :2 AND CWD_FLD_OPTION = :3 AND CWD_SUBFLD_SEQ = :
4
1,331 1,191 0.9 0.00 0.00
2124505501
Module: JDBC Thin Client
SELECT MHB_SNDR_REF,MHB_RCVR_REF,MHB_RCVR_SNDR_ADDRS,MHB_AGENCY,
MHB_AGENCY_CD,MHB_MSG_STAT,MHB_MSG_STAT_CD,MHB_MSG_TYP,MHB_MSG_T
YP_CD,MHB_MSG_PRRTY,MHB_TRNG_FLAG,MHB_NON_DLV_WRNG_IND,MHB_OBSLN
C_PRD,MHB_PDE,MHB_DLM,MHB_DELAYED,MHB_MSG_VER,MHB_OPEN_FLG,MHB_O
RIG_FL_NAME,MHB_PREV_ADDRS,MHB_NEXT_ADDRS,MHB_CRTN_DATE,MHB_SEND
1,176 1,169 1.0 0.00 0.00
3876120182
Module: JDBC Thin Client
SELECT UAI_CREATOR_ID,UAI_ENABLER_ID,UAI_MODIFIER_ID,UAI_APPROV
ER_ID,UAI_USER_EMP_NO,UAI_SMART_CARD_SL,UAI_USER_GRP_CODE,UAI_US
ER_GRP_CODE_CD,UAI_USER_NAME,UAI_USER_STAT,UAI_USER_STAT_CD,UAI_
CRTN_DATE,UAI_ROLE_ID,UAI_ENABLE_ONDT,UAI_CONNECT_STAT,UAI_CONNE
CT_STAT_CD,UAI_LAST_LOGIN_DATE,UAI_LAST_LOGINON,UAI_AGENCY,UAI_U
1,151 1,151 1.0 0.00 0.00
1495493136
Module: JDBC Thin Client
UPDATE MHB_MSG_HDR_BR SET MHB_SNDR_REF = :1,MHB_RCVR_REF = :2,MH
B_RCVR_SNDR_ADDRS = :3,MHB_AGENCY = :4,MHB_AGENCY_CD = :5,MHB_MS
G_STAT = :6,MHB_MSG_STAT_CD = :7,MHB_MSG_TYP = :8,MHB_MSG_TYP_CD
= :9,MHB_MSG_PRRTY = :10,MHB_TRNG_FLAG = :11,MHB_NON_DLV_WRNG_I
ND = :12,MHB_OBSLNC_PRD = :13,MHB_PDE = :14,MHB_DLM = :15,MHB_DE
1,131 1,128 1.0 0.00 0.00
685878034
Module: JDBC Thin Client
INSERT INTO SMD_SYS_MSG_DTLS VALUES (:1,:2,:3,:4,:5,:6,:7,:8,0,0
,null,:9,:10,:11,:12,null,:13,1,:14)
1,120 1,120 1.0 0.00 0.00
3371479671
select t.name, (select owner_instance from sys.aq$_queue_table_
affinities where table_objno = t.objno) from system.aq$_queue
_tables t where t.name = :1 and t.schema = :2 for update skip lo
cked
1,067 1,067 1.0 0.00 0.00
2243488166
Module: JDBC Thin Client
select COUNT(*) from mpt_msg_proc_tbl where MPT_OWNR_ADDRS=:1 AN
D MPT_IN_OUT_IND=:2 and mpt_crt_rcv_num =:3 and MPT_MSG_SEQ=:4
and mpt_sub_elem_id=:5
925 6,040 6.5 0.00 0.00
1994601063
Module: JDBC Thin Client
SELECT RFM_FUNC_ID FROM RFM_ROLE_FUNC_MAP WHERE RFM_ROLE_ID =
:1 AND RFM_FUNC_ID IN (:2,:3,:4,:5,:6,:7,:8,:9,:10) AND RFM_IFSC
= :11 AND RFM_AGENCY = :12
851 562 0.7 0.00 0.00
4150459836
Module: JDBC Thin Client
SQL ordered by Executions for DB: SFMS Instance: sfms Snaps: 147
-148
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
------------ --------------- ---------------- ----------- ----------
----------
SELECT MHB_OWNR_ADDRS,MHB_IN_OUT_IND,MHB_CRT_RCV_SEQ_NUM,MHB_RCV
R_REF,MHB_RCVR_SNDR_ADDRS,MHB_AGENCY,MHB_AGENCY_CD,MHB_MSG_STAT,
MHB_MSG_STAT_CD,MHB_MSG_TYP,MHB_MSG_TYP_CD,MHB_MSG_PRRTY,MHB_TRN
G_FLAG,MHB_NON_DLV_WRNG_IND,MHB_OBSLNC_PRD,MHB_PDE,MHB_DLM,MHB_D
ELAYED,MHB_MSG_VER,MHB_OPEN_FLG,MHB_ORIG_FL_NAME,MHB_PREV_ADDRS,
845 0 0.0 0.00 0.00
687864414
Module: JDBC Thin Client
SELECT STBSB_OWNR_ADDRS,STBSB_IN_OUT_IND,STBSB_CRT_RCV_SEQ_NUM
FROM STBSB_TO_BE_SENT_BR ORDER BY DM_LSTUPDDT ASC
783 416 0.5 0.00 0.00
850485391
Module: JDBC Thin Client
SELECT TBSB_OWNR_ADDRS,TBSB_IN_OUT_IND,TBSB_CRT_RCV_SEQ_NUM FRO
M TBSB_TO_BE_SENT_BR ORDER BY DM_LSTUPDDT ASC
779 779 1.0 0.00 0.00
937435024
Module: JDBC Thin Client
SELECT 1 FROM IDI_IFSC_DIR_INFO WHERE IDI_IFSC_CODE = :1
-------------------------------------------------------------
SQL ordered by Parse Calls for DB: SFMS Instance: sfms Snaps: 147
-148
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
26,387 26,387 24.25 3329356112
Module: JDBC Thin Client
SELECT 1 FROM SCD_SYS_CODE_DEF WHERE SCD_CODE_ID = :1 AND SCD_CO
DE_VAL = :2 AND SCD_LANG_CODE = :3
7,476 7,476 6.87 1356713530
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with (grantee#=:1 or grantee#=
1) and privilege#>0
6,724 6,724 6.18 3570694802
Module: JDBC Thin Client
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN'
6,721 6,721 6.18 1702556642
Module: JDBC Thin Client
SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER ='NLS_
DATE_FORMAT'
6,715 6,715 6.17 2707901715
Module: JDBC Thin Client
ALTER SESSION SET NLS_TERRITORY = 'AMERICA'
4,094 4,094 3.76 1780264807
Module: JDBC Thin Client
SELECT * FROM MSD_SUBFLD_DEF WHERE MSD_MSG_SEQ= :1 AND MSD_FLD =
:2 AND MSD_OPTION = :3 ORDER BY MSD_SUBFLD_SEQ
4,094 4,094 3.76 4157967512
Module: JDBC Thin Client
SELECT COUNT(*) FROM MSD_SUBFLD_DEF WHERE MSD_MSG_SEQ = :1 AND M
SD_FLD = :2 AND MSD_OPTION = :3
3,531 3,531 3.25 2290514744
Module: JDBC Thin Client
SELECT UAI_CONNECT_STAT FROM UAI_USER_ACC_INFO WHERE UAI_USER_ID
= :1AND UAI_USER_NODE_ADDRS = :2
2,966 2,966 2.73 826609426
Module: JDBC Thin Client
INSERT INTO MPT_MSG_PROC_TBL VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,
:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,
:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,
:42,:43,:44,:45,:46,:47,:48,:49,:50,:51)
2,815 2,816 2.59 1364225184
Module: JDBC Thin Client
SELECT MPT_ELEM_VALUE FROM MPT_MSG_PROC_TBL WHERE MPT_IN_OUT_IN
D = :1 AND MPT_OWNR_ADDRS = :2 AND MPT_CRT_RCV_NUM = :3 AND MPT
_FLD_ID IN (20, 2020) AND MPT_FLD_OPTION='-' ORDER BY MPT_ELEM_
SEQ_D1, MPT_ELEM_SEQ_D2, MPT_ELEM_SEQ_D3, MPT_ELEM_SEQ_D4, MPT_
ELEM_SEQ_D5, MPT_ELEM_SEQ_D6, MPT_ELEM_SEQ_D7, MPT_ELEM_SEQ_D8,
1,537 1,537 1.41 1774114274
SQL ordered by Parse Calls for DB: SFMS Instance: sfms Snaps: 147
-148
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
Module: JDBC Thin Client
SELECT COUNT(*) FROM CWD_CODE_WORD WHERE CWD_MSG_SEQ = :1 AN
D CWD_FLD_ID = :2 AND CWD_FLD_OPTION = :3 AND CWD_SUBFLD_SEQ = :
4
1,331 1,331 1.22 2124505501
Module: JDBC Thin Client
SELECT MHB_SNDR_REF,MHB_RCVR_REF,MHB_RCVR_SNDR_ADDRS,MHB_AGENCY,
MHB_AGENCY_CD,MHB_MSG_STAT,MHB_MSG_STAT_CD,MHB_MSG_TYP,MHB_MSG_T
YP_CD,MHB_MSG_PRRTY,MHB_TRNG_FLAG,MHB_NON_DLV_WRNG_IND,MHB_OBSLN
C_PRD,MHB_PDE,MHB_DLM,MHB_DELAYED,MHB_MSG_VER,MHB_OPEN_FLG,MHB_O
RIG_FL_NAME,MHB_PREV_ADDRS,MHB_NEXT_ADDRS,MHB_CRTN_DATE,MHB_SEND
1,176 1,176 1.08 3876120182
Module: JDBC Thin Client
SELECT UAI_CREATOR_ID,UAI_ENABLER_ID,UAI_MODIFIER_ID,UAI_APPROV
ER_ID,UAI_USER_EMP_NO,UAI_SMART_CARD_SL,UAI_USER_GRP_CODE,UAI_US
ER_GRP_CODE_CD,UAI_USER_NAME,UAI_USER_STAT,UAI_USER_STAT_CD,UAI_
CRTN_DATE,UAI_ROLE_ID,UAI_ENABLE_ONDT,UAI_CONNECT_STAT,UAI_CONNE
CT_STAT_CD,UAI_LAST_LOGIN_DATE,UAI_LAST_LOGINON,UAI_AGENCY,UAI_U
1,151 1,151 1.06 1495493136
Module: JDBC Thin Client
UPDATE MHB_MSG_HDR_BR SET MHB_SNDR_REF = :1,MHB_RCVR_REF = :2,MH
B_RCVR_SNDR_ADDRS = :3,MHB_AGENCY = :4,MHB_AGENCY_CD = :5,MHB_MS
G_STAT = :6,MHB_MSG_STAT_CD = :7,MHB_MSG_TYP = :8,MHB_MSG_TYP_CD
= :9,MHB_MSG_PRRTY = :10,MHB_TRNG_FLAG = :11,MHB_NON_DLV_WRNG_I
ND = :12,MHB_OBSLNC_PRD = :13,MHB_PDE = :14,MHB_DLM = :15,MHB_DE
1,067 1,067 0.98 2243488166
Module: JDBC Thin Client
select COUNT(*) from mpt_msg_proc_tbl where MPT_OWNR_ADDRS=:1 AN
D MPT_IN_OUT_IND=:2 and mpt_crt_rcv_num =:3 and MPT_MSG_SEQ=:4
and mpt_sub_elem_id=:5
925 925 0.85 1994601063
Module: JDBC Thin Client
SELECT RFM_FUNC_ID FROM RFM_ROLE_FUNC_MAP WHERE RFM_ROLE_ID =
:1 AND RFM_FUNC_ID IN (:2,:3,:4,:5,:6,:7,:8,:9,:10) AND RFM_IFSC
= :11 AND RFM_AGENCY = :12
851 851 0.78 4150459836
Module: JDBC Thin Client
SELECT MHB_OWNR_ADDRS,MHB_IN_OUT_IND,MHB_CRT_RCV_SEQ_NUM,MHB_RCV
R_REF,MHB_RCVR_SNDR_ADDRS,MHB_AGENCY,MHB_AGENCY_CD,MHB_MSG_STAT,
MHB_MSG_STAT_CD,MHB_MSG_TYP,MHB_MSG_TYP_CD,MHB_MSG_PRRTY,MHB_TRN
G_FLAG,MHB_NON_DLV_WRNG_IND,MHB_OBSLNC_PRD,MHB_PDE,MHB_DLM,MHB_D
ELAYED,MHB_MSG_VER,MHB_OPEN_FLG,MHB_ORIG_FL_NAME,MHB_PREV_ADDRS,
779 779 0.72 937435024
Module: JDBC Thin Client
SELECT 1 FROM IDI_IFSC_DIR_INFO WHERE IDI_IFSC_CODE = :1
764 764 0.70 2228227173
SQL ordered by Parse Calls for DB: SFMS Instance: sfms Snaps: 147
-148
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
Module: JDBC Thin Client
ALTER SESSION SET TIME_ZONE='+5:30' NLS_LANGUAGE = 'AMERICAN' NL
S_TERRITORY = 'AMERICA'
756 756 0.69 3003997262
Module: JDBC Thin Client
SELECT MTB_MSG_BLOCK1,MTB_MSG_BLOCK2,MTB_MSG_BLOCK3,MTB_MSG_BLOC
K4,MTB_MSG_BLOCK5A,MTB_MSG_BLOCK5B,MTB_MSG_BLOCK5C,MTB_MSG_TXT_B
R_VER,DM_LSTUPDDT FROM MTB_MSG_TXT_BR WHERE MTB_OWNR_ADDRS = :1
AND MTB_IN_OUT_IND = :2 AND MTB_CRT_RCV_SEQ_NUM = :3
727 727 0.67 1791910789
Module: JDBC Thin Client
SELECT UMM_MSG_GRP_ID FROM UMM_USERGRP_MSGTYPE_MAP WHERE UMM_IF
SC=:1 AND UMM_USR_GRP_ID=:2 AND UMM_AGENCY=:3
725 725 0.67 46870338
Module: JDBC Thin Client
SELECT MGD_MSG_MNEMNC FROM MGD_MSG_GROUP_DTL WHERE MGD_MSG_IFSC=
:1 AND MGD_MSG_ID=:2 ORDER BY MGD_MSG_MNEMNC
-------------------------------------------------------------
Instance Activity Stats for DB: SFMS Instance: sfms Snaps: 147 -148
Statistic Total per Second
per Trans
--------------------------------- ------------------ --------------
------------
CPU used by this session 23,390
13.8 7.3
CPU used when call started 23,389
13.8 7.3
CR blocks created 1,561
0.9 0.5
Cached Commit SCN referenced 3,339
2.0 1.0
Commit SCN cached 13
0.0 0.0
DBWR buffers scanned 3,124
1.9 1.0
DBWR checkpoint buffers written 4,698
2.8 1.5
DBWR checkpoints 0
0.0 0.0
DBWR free buffers found 3,118
1.8 1.0
DBWR lru scans 4
0.0 0.0
DBWR make free requests 4
0.0 0.0
DBWR summed scan depth 3,124
1.9 1.0
DBWR transaction table writes 10
0.0 0.0
DBWR undo block writes 1,345
0.8 0.4
SQL*Net roundtrips to/from client 1,585,249
937.5 495.2
SQL*Net roundtrips to/from dblink 0
0.0 0.0
active txn count during cleanout 3,521
2.1 1.1
background checkpoints completed 1
0.0 0.0
background checkpoints started 0
0.0 0.0
background timeouts 2,017
1.2 0.6
branch node splits 2
0.0 0.0
buffer is not pinned count 9,114,964
5,390.3 2,847.5
buffer is pinned count 3,248,575
1,921.1 1,014.9
bytes received via SQL*Net from c 79,022,665
46,731.3 24,686.9
bytes received via SQL*Net from d 0
0.0 0.0
bytes sent via SQL*Net to client 190,248,795,055 112,506,679.5
############
bytes sent via SQL*Net to dblink 0
0.0 0.0
calls to get snapshot scn: kcmgss 126,217
74.6 39.4
calls to kcmgas 8,753
5.2 2.7
calls to kcmgcs 3,984
2.4 1.2
change write time 140
0.1 0.0
cleanout - number of ktugct calls 4,140
2.5 1.3
cleanouts and rollbacks - consist 1,086
0.6 0.3
cleanouts only - consistent read 177
0.1 0.1
cluster key scan block gets 111
0.1 0.0
cluster key scans 47
0.0 0.0
commit cleanout failures: buffer 0
0.0 0.0
commit cleanout failures: callbac 1
0.0 0.0
commit cleanout failures: cannot 23
0.0 0.0
commit cleanouts 21,490
12.7 6.7
commit cleanouts successfully com 21,466
12.7 6.7
commit txn count during cleanout 3,295
2.0 1.0
consistent changes 25,286
15.0 7.9
consistent gets 9,765,234
5,774.8 3,050.7
consistent gets - examination 391,638
231.6 122.4
cursor authentications 126
0.1 0.0
data blocks consistent reads - un 25,285
15.0 7.9
db block changes 215,818
127.6 67.4
db block gets 239,679
141.7 74.9
deferred (CURRENT) block cleanout 10,447
6.2 3.3
dirty buffers inspected 10
0.0 0.0
enqueue conversions 33
0.0 0.0
enqueue releases 28,500
16.9 8.9
enqueue requests 28,501
16.9 8.9
enqueue timeouts 0
0.0 0.0
enqueue waits 35
0.0 0.0
Instance Activity Stats for DB: SFMS Instance: sfms Snaps: 147 -148
Statistic Total per Second
per Trans
--------------------------------- ------------------ --------------
------------
exchange deadlocks 2
0.0 0.0
execute count 113,736
67.3 35.5
failed probes on index block recl 0
0.0 0.0
free buffer inspected 41,539
24.6 13.0
free buffer requested 6,238,209
3,689.1 1,948.8
hot buffers moved to head of LRU 3,644
2.2 1.1
immediate (CR) block cleanout app 1,263
0.8 0.4
immediate (CURRENT) block cleanou 2,893
1.7 0.9
index crx upgrade (positioned) 2,671
1.6 0.8
index fast full scans (full) 331
0.2 0.1
index fetch by key 58,551
34.6 18.3
index scans kdiixs1 440,984
260.8 137.8
leaf node 90-10 splits 15
0.0 0.0
leaf node splits 417
0.3 0.1
logons cumulative 7,502
4.4 2.3
messages received 4,571
2.7 1.4
messages sent 4,573
2.7 1.4
no buffer to keep pinned count 0
0.0 0.0
no work - consistent read gets 9,349,161
5,528.8 2,920.7
opened cursors cumulative 96,347
57.0 30.1
parse count (failures) 1
0.0 0.0
parse count (hard) 372
0.2 0.1
parse count (total) 108,795
64.3 34.0
parse time cpu 723
0.4 0.2
parse time elapsed 766
0.5 0.2
physical reads 6,236,710
3,688.2 1,948.4
physical reads direct 3,630
2.2 1.1
physical reads direct (lob) 0
0.0 0.0
physical writes 9,436
5.6 3.0
physical writes direct 4,722
2.8 1.5
physical writes direct (lob) 0
0.0 0.0
physical writes non checkpoint 8,117
4.8 2.5
pinned buffers inspected 27,502
16.3 8.6
prefetched blocks 5,645,308
3,338.4 1,763.6
prefetched blocks aged out before 0
0.0 0.0
process last non-idle time 1,688
1.0 0.5
recursive calls 297,349
175.8 92.9
recursive cpu usage 1,224
0.7 0.4
redo blocks written 109,160
64.6 34.1
redo buffer allocation retries 0
0.0 0.0
redo entries 110,586
65.4 34.6
redo log space requests 0
0.0 0.0
redo log space wait time 0
0.0 0.0
redo size 52,923,708
31,297.3 16,533.5
redo synch time 3,163
1.9 1.0
redo synch writes 2,986
1.8 0.9
redo wastage 1,046,600
618.9 327.0
redo write time 3,038
1.8 1.0
redo writer latching time 0
0.0 0.0
redo writes 4,082
2.4 1.3
rollback changes - undo records a 130
0.1 0.0
rollbacks only - consistent read 700
0.4 0.2
rows fetched via callback 16,010
9.5 5.0
session connect time 0
0.0 0.0
session logical reads 10,004,911
5,916.6 3,125.6
session uga memory 87,168,956
51,548.8 27,231.8
Instance Activity Stats for DB: SFMS Instance: sfms Snaps: 147 -148
Statistic Total per Second
per Trans
--------------------------------- ------------------ --------------
------------
session uga memory max 1,141,299,460 674,925.8
356,544.7
shared hash latch upgrades - no w 3,735
2.2 1.2
sorts (disk) 17
0.0 0.0
sorts (memory) 86,294
51.0 27.0
sorts (rows) 1,410,123
833.9 440.5
summed dirty queue length 17
0.0 0.0
switch current to new buffer 1,147
0.7 0.4
table fetch by rowid 2,002,918
1,184.5 625.7
table fetch continued row 27,999
16.6 8.8
table scan blocks gotten 8,080,808
4,778.7 2,524.5
table scan rows gotten 15,112,161
8,936.8 4,721.1
table scans (long tables) 34
0.0 0.0
table scans (short tables) 4,948
2.9 1.6
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
user calls 850,496
503.0 265.7
user commits 2,605
1.5 0.8
user rollbacks 596
0.4 0.2
workarea executions - multipass 1
0.0 0.0
workarea executions - onepass 22
0.0 0.0
workarea executions - optimal 80,997
47.9 25.3
write clones created in foregroun 0
0.0 0.0
-------------------------------------------------------------
Tablespace IO Stats for DB: SFMS Instance: sfms Snaps: 147 -148
->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)
-------------- ------- ------ ------- ------------ -------- ----------
------
SFMS_BR_DATA
584,294 346 8.2 10.7 1,864 1
212,724 7.6
TEMPBR
3,556 2 0.6 1.0 4,665 3
0 0.0
SFMS_BR_NDX
1,559 1 12.2 1.0 1,247 1
14 2.1
UNDOTBS1
1 0 20.0 1.0 1,358 1
10 0.0
SFMS_BR_ARCH_DATA
587 0 8.3 1.0 31 0
0 0.0
SFMS_BR_ARCH_NDX
340 0 16.1 1.0 50 0
0 0.0
SYSTEM
291 0 1.7 1.0 14 0
0 0.0
PERFSTAT
27 0 8.1 1.0 195 0
0 0.0
CWMLITE
3 0 23.3 1.0 2 0
0 0.0
TOOLS
3 0 26.7 1.0 2 0
0 0.0
EXAMPLE
2 0 20.0 1.0 2 0
0 0.0
XDB
2 0 20.0 1.0 2 0
0 0.0
DRSYS
1 0 30.0 1.0 1 0
0 0.0
INDX
1 0 30.0 1.0 1 0
0 0.0
ODM
1 0 50.0 1.0 1 0
0 0.0
USERS
1 0 30.0 1.0 1 0
0 0.0
-------------------------------------------------------------
File IO Stats for DB: SFMS Instance: sfms Snaps: 147 -148
->ordered by Tablespace, File
Tablespace Filename
------------------------
----------------------------------------------------
Av Av Av Av Buffer
Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
Wt(ms)
-------------- ------- ------ ------- ------------ -------- ----------
------
CWMLITE C:\ORA9I\ORADATA\SFMS\CWMLITE01.DBF
1 0 20.0 1.0 1 0 0
C:\ORA9I\ORADATA\SFMS\CWMLITE02.DBF
2 0 25.0 1.0 1 0 0
DRSYS C:\ORA9I\ORADATA\SFMS\DRSYS01.DBF
1 0 30.0 1.0 1 0 0
EXAMPLE C:\ORA9I\ORADATA\SFMS\EXAMPLE01.DBF
1 0 30.0 1.0 1 0 0
C:\ORA9I\ORADATA\SFMS\EXAMPLE02.DBF
1 0 10.0 1.0 1 0 0
INDX C:\ORA9I\ORADATA\SFMS\INDX01.DBF
1 0 30.0 1.0 1 0 0
ODM C:\ORA9I\ORADATA\SFMS\ODM01.DBF
1 0 50.0 1.0 1 0 0
PERFSTAT C:\ORA9I\ORADATA\SFMS\PERFSFMS_1.DBF
27 0 8.1 1.0 195 0 0
SFMS_BR_ARCH_DATA E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DAT2A12.DBF
2 0 25.0 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DAT2A19.DBF
70 0 6.9 1.0 2 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DAT2A20.DBF
66 0 7.7 1.0 2 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DAT2A21.DBF
73 0 6.8 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DAT2A22.DBF
90 0 6.2 1.0 2 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DAT2A23.DBF
69 0 10.0 1.0 6 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA1.DBF
5 0 8.0 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA10.DBF
3 0 13.3 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA11.DBF
3 0 13.3 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA13.DBF
5 0 16.0 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA14.DBF
14 0 11.4 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA15.DBF
6 0 20.0 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA16.DBF
13 0 13.1 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA17.DBF
9 0 15.6 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA18.DBF
15 0 11.3 1.0 1 0 0
File IO Stats for DB: SFMS Instance: sfms Snaps: 147 -148
->ordered by Tablespace, File
Tablespace Filename
------------------------
----------------------------------------------------
Av Av Av Av Buffer
Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
Wt(ms)
-------------- ------- ------ ------- ------------ -------- ----------
------
SFMS_BR_ARCH_DATA E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA2.DBF
17 0 8.2 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA3.DBF
8 0 7.5 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA4.DBF
27 0 6.3 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA5.DBF
23 0 5.7 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA6.DBF
18 0 6.7 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA7.DBF
15 0 12.0 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA8.DBF
22 0 9.1 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA9.DBF
14 0 10.7 1.0 1 0 0
SFMS_BR_ARCH_NDX E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_NDX1.DBF
27 0 11.9 1.0 3 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_NDX2.DBF
77 0 14.2 1.0 11 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_NDX3.DBF
40 0 12.0 1.0 8 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_NDX4.DBF
59 0 19.3 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_NDX5.DBF
110 0 17.9 1.0 5 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_NDX6.DBF
11 0 20.0 1.0 9 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_NDX7.DBF
16 0 15.6 1.0 13 0 0
SFMS_BR_DATA E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_DATA1.DBF
370,973 219 4.5 11.3 328 0
54,122 3.0
E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_DATA2.DBF
8,338 5 11.6 9.3 108 0
6,377 7.4
E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_DATA3.DBF
16,077 10 13.1 9.1 188 0
13,187 8.5
E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_DATA4.DBF
19,415 11 13.7 8.9 97 0
15,651 9.1
E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_DATA5.DBF
16,938 10 14.7 8.9 328 0
12,901 9.7
E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_DATA6.DBF
48,847 29 15.3 9.7 240 0
34,971 10.0
E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_DATA7.DBF
55,871 33 15.3 9.4 455 0
41,103 9.0
E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_DATA8.DBF
47,835 28 14.5 9.8 120 0
34,412 9.0
SFMS_BR_NDX E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_NDX1.DBF
260 0 23.5 1.0 270 0 0
E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_NDX2.DBF
File IO Stats for DB: SFMS Instance: sfms Snaps: 147 -148
->ordered by Tablespace, File
Tablespace Filename
------------------------
----------------------------------------------------
Av Av Av Av Buffer
Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
Wt(ms)
-------------- ------- ------ ------- ------------ -------- ----------
------
592 0 9.3 1.0 475 0 0
SFMS_BR_NDX E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_NDX3.DBF
707 0 10.4 1.0 502 0
14 2.1
SYSTEM C:\ORA9I\ORADATA\SFMS\SYSTEM01.DBF
290 0 1.7 1.0 13 0 0
C:\ORA9I\ORADATA\SFMS\SYSTEM02.DBF
1 0 10.0 1.0 1 0 0
TEMPBR E:\SFMS_BRANCH\ORADATA\SFMS\TEMP01.DBF
3,556 2 0.6 1.0 4,665 3 0
TOOLS C:\ORA9I\ORADATA\SFMS\TOOLS01.DBF
1 0 30.0 1.0 1 0 0
C:\ORA9I\ORADATA\SFMS\TOOLS02.DBF
2 0 25.0 1.0 1 0 0
UNDOTBS1 C:\ORA9I\ORADATA\SFMS\UNDOTBS01.DBF
1 0 20.0 1.0 1,358 1
10 0.0
USERS C:\ORA9I\ORADATA\SFMS\USERS01.DBF
1 0 30.0 1.0 1 0 0
XDB C:\ORA9I\ORADATA\SFMS\XDB01.DBF
1 0 30.0 1.0 1 0 0
C:\ORA9I\ORADATA\SFMS\XDB02.DBF
1 0 10.0 1.0 1 0 0
-------------------------------------------------------------
Buffer Pool Statistics for DB: SFMS Instance: sfms Snaps: 147 -148
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
Free
Write Buffer
Number of Cache Buffer Physical Physical Buffer
Complete Busy
P Buffers Hit % Gets Reads Writes Waits
Waits Waits
--- ---------- ----- ----------- ----------- ---------- -------
-------- ------
D 128,128 35.9 9,719,664 6,226,456 4,714 0
0 213,349
K 1,001 99.9 253,202 136 0 0
0 0
-------------------------------------------------------------
Instance Recovery Stats for DB: SFMS Instance: sfms Snaps: 147 -148
-> 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 246 41 5467 94378 93580 184320 93580
E 246 44 6994 121754 120993 184320 120993
-------------------------------------------------------------
Buffer Pool Advisory for DB: SFMS Instance: sfms End Snap: 148
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate (default block size
first)
Size for Size Buffers for Est Physical
Estimated
P Estimate (M) Factr Estimate Read Factor Physical
Reads
--- ------------ ----- ---------------- -------------
------------------
D 104 .1 13,013 5.77
203,474,063
D 208 .2 26,026 1.75
61,741,474
D 312 .3 39,039 1.43
50,359,087
D 416 .4 52,052 1.31
46,102,313
D 520 .5 65,065 1.23
43,259,137
D 624 .6 78,078 1.17
41,159,076
D 728 .7 91,091 1.12
39,376,445
D 832 .8 104,104 1.08
37,970,986
D 936 .9 117,117 1.03
36,399,284
D 1,024 1.0 128,128 1.00
35,268,345
D 1,040 1.0 130,130 1.00
35,098,551
D 1,144 1.1 143,143 0.96
33,788,180
D 1,248 1.2 156,156 0.92
32,460,493
D 1,352 1.3 169,169 0.88
31,169,421
D 1,456 1.4 182,182 0.84
29,644,295
D 1,560 1.5 195,195 0.80
28,368,325
D 1,664 1.6 208,208 0.77
27,266,600
D 1,768 1.7 221,221 0.76
26,776,517
D 1,872 1.8 234,234 0.74
26,254,061
D 1,976 1.9 247,247 0.73
25,654,225
D 2,080 2.0 260,260 0.71
24,934,398
K 8 1.0 1,001 1.00
592,740
K 16 2.0 2,002 0.45
268,671
K 24 3.0 3,003 0.00
2,044
K 32 4.0 4,004 0.00
2,044
K 40 5.0 5,005 0.00
2,044
K 48 6.0 6,006 0.00
2,044
K 56 7.0 7,007 0.00
2,044
K 64 8.0 8,008 0.00
2,044
K 72 9.0 9,009 0.00
2,044
K 80 10.0 10,010 0.00
2,044
K 88 11.0 11,011 0.00
2,044
K 96 12.0 12,012 0.00
2,044
K 104 13.0 13,013 0.00
2,044
K 112 14.0 14,014 0.00
2,044
K 120 15.0 15,015 0.00
2,044
K 128 16.0 16,016 0.00
2,044
K 136 17.0 17,017 0.00
2,044
K 144 18.0 18,018 0.00
2,044
K 152 19.0 19,019 0.00
2,044
K 160 20.0 20,020 0.00
2,044
-------------------------------------------------------------
Buffer wait Statistics for DB: SFMS Instance: sfms Snaps: 147 -148
-> ordered by wait time desc, waits desc
Tot Wait Avg
Class Waits Time (s) Time (ms)
------------------ ----------- ---------- ---------
data block 213,389 1,629 8
undo block 9 0 0
segment header 1 0 0
undo header 1 0 0
-------------------------------------------------------------
PGA Aggr Target Stats for DB: SFMS Instance: sfms Snaps: 147 -148
-> B: Begin snap E: End snap (rows dentified 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
--------------- ---------------- -------------------------
90.0 348 39
%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 477 409 37.6 0.0 .0 .0 .0
24,422
E 477 408 36.6 0.4 1.2 .0 100.0
24,422
-------------------------------------------------------------
PGA Aggr Target Histogram for DB: SFMS Instance: sfms Snaps: 147
-148
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 75,297 75,297 0 0
4K 8K 1,436 1,436 0 0
8K 16K 2,432 2,432 0 0
16K 32K 1,738 1,738 0 0
32K 64K 34 34 0 0
64K 128K 23 1 22 0
512K 1024K 34 34 0 0
1M 2M 1 1 0 0
8M 16M 1 0 0 1
16M 32M 1 1 0 0
-------------------------------------------------------------
PGA Memory Advisory for DB: SFMS Instance: sfms End Snap: 148
-> 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
---------- ------- ---------------- ---------------- --------
----------
60 0.1 347.2 100.4 78.0
0
119 0.3 347.2 100.4 78.0
0
238 0.5 347.2 68.6 84.0
0
358 0.8 347.2 56.4 86.0
0
477 1.0 347.2 0.0 100.0
0
572 1.2 347.2 0.0 100.0
0
668 1.4 347.2 0.0 100.0
0
763 1.6 347.2 0.0 100.0
0
859 1.8 347.2 0.0 100.0
0
954 2.0 347.2 0.0 100.0
0
1,431 3.0 347.2 0.0 100.0
0
1,908 4.0 347.2 0.0 100.0
0
2,862 6.0 347.2 0.0 100.0
0
3,816 8.0 347.2 0.0 100.0
0
-------------------------------------------------------------
Enqueue activity for DB: SFMS Instance: sfms Snaps: 147 -148
-> Enqueue stats gathered prior to 9i should not be compared with 9i
data
-> ordered by Wait Time desc, Waits desc
Avg Wt
Wait
Eq Requests Succ Gets Failed Gets Waits Time (ms)
Time (s)
-- ------------ ------------ ----------- ----------- -------------
------------
TX 6,086 6,086 0 35
78.11 3
-------------------------------------------------------------
Rollback Segment Stats for DB: SFMS Instance: sfms Snaps: 147 -148
->A high value for "Pct Waits" suggests more rollback segments may be
required
->RBS stats may not be accurate between begin and end snaps when using
Auto Undo
managment, as RBS may be dynamically created and dropped as needed
Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks
Extends
------ -------------- ------- --------------- -------- --------
--------
0 8.0 0.00 0 0 0
0
1 1,264.0 0.00 1,395,834 2 1
0
2 1,406.0 0.00 1,252,618 0 0
0
3 1,547.0 0.06 1,228,208 2 1
0
4 1,778.0 0.00 1,107,874 1 0
0
5 1,528.0 0.00 1,152,998 1 1
0
6 1,210.0 0.00 1,209,726 1 1
0
7 1,533.0 0.00 1,466,962 1 1
0
8 2,053.0 0.00 2,277,682 5 0
1
9 1,818.0 0.00 1,322,968 4 2
0
10 1,543.0 0.00 1,182,126 1 1
0
-------------------------------------------------------------
Rollback Segment Storage for DB: SFMS Instance: sfms Snaps: 147
-148
->Optimal Size should be larger than Avg Active
RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 385,024 6,553 385,024
1 21,094,400 943,039 24,240,128
2 84,008,960 849,403 84,008,960
3 16,900,096 927,814 27,385,856
4 20,045,824 985,428 28,434,432
5 16,965,632 948,379 29,548,544
6 16,900,096 869,620 23,191,552
7 14,802,944 941,536 23,191,552
8 16,900,096 927,422 24,240,128
9 16,900,096 829,184 29,483,008
10 20,045,824 940,306 31,580,160
-------------------------------------------------------------
Undo Segment Summary for DB: SFMS Instance: sfms Snaps: 147 -148
-> Undo segment block stats:
-> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired
reUsed
-> eS - expired Stolen, eR - expired Released, eU - expired
reUsed
Undo Undo Num Max Qry Max Tx Snapshot Out of uS/
uR/uU/
TS# Blocks Trans Len (s) Concurcy Too Old Space eS/
eR/eU
---- -------------- ---------- -------- ---------- -------- ------
-------------
1 2,731 606,660 249 1 0 0
0/0/0/0/0/0
-------------------------------------------------------------
Undo Segment Stats for DB: SFMS Instance: sfms Snaps: 147 -148
-> ordered by Time desc
Undo Num Max Qry Max Tx Snap Out of uS/
uR/uU/
End Time Blocks Trans Len (s) Concy Too Old Space eS/
eR/eU
------------ ------------ -------- ------- -------- ------- ------
-------------
14-Nov 17:39 844 204,153 75 1 0 0
0/0/0/0/0/0
14-Nov 17:29 920 202,481 249 1 0 0
0/0/0/0/0/0
14-Nov 17:19 967 200,026 238 1 0 0
0/0/0/0/0/0
-------------------------------------------------------------
Latch Activity for DB: SFMS Instance: sfms Snaps: 147 -148
->"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
------------------------ -------------- ------ ------ ------
------------ ------
Consistent RBA 4,082 0.0
0 0
FAL request queue 29 0.0
0 0
FAL subheap alocation 14 0.0
0 0
FIB s.o chain latch 29 0.0
0 0
FOB s.o list latch 44 0.0
0 0
SQL memory manager latch 1 0.0 0
560 0.0
SQL memory manager worka 112,853 0.0 0.0
0 0
active checkpoint queue 1,054 0.0
0 0
alert log latch 5 0.0
0 0
archive control 5 0.0
0 0
archive process latch 15 0.0
0 0
cache buffer handles 224 0.0
0 0
cache buffers chains 26,609,079 1.5 0.0 11
12,157,479 0.2
cache buffers lru chain 14,374 43.5 0.0 0
13,837,006 3.1
channel handle pool latc 35 0.0
0 0
channel operations paren 1,261 0.0
0 0
checkpoint queue latch 101,076 0.0 0
6,204 0.0
child cursor hash table 2,644 0.0
0 0
dml lock allocation 25,573 0.0 0.0
0 0
dummy allocation 15,026 0.0
0 0
enqueue hash chains 57,103 0.0 0.0
0 0
enqueues 66,404 0.0 0.0
0 0
hash table column usage 23 0.0 0
19,672 0.0
job_queue_processes para 28 0.0
0 0
kmcptab latch 1,528 0.0
0 0
kmcpvec latch 0 0
1,528 0.0
ktm global data 41 0.0
0 0
kwqit: protect wakeup ti 56 0.0
0 0
lgwr LWN SCN 4,083 0.0
0 0
library cache 1,568,378 0.1 0.0
0 0
library cache load lock 2 0.0
0 0
library cache pin 982,871 0.0 0.0
0 0
library cache pin alloca 532,665 0.0 0.0
0 0
list of block allocation 3,849 0.0
0 0
loader state object free 89 1.1 0.0
0 0
longop free list parent 863 0.0 0
24 0.0
message pool operations 34 0.0
0 0
messages 17,258 0.0 0.0
0 0
mostly latch-free SCN 4,094 0.0 0.0
0 0
multiblock read objects 1,157,553 0.2 0.0
0 0
ncodef allocation latch 53 0.0
0 0
post/wait queue 4,551 0.0 0
2,990 0.0
process allocation 1 0.0
0 0
process group creation 1 0.0
0 0
redo allocation 118,810 0.1 0.0
0 0
redo copy 0 0
110,762 0.2
redo writing 14,472 0.0
0 0
row cache enqueue latch 150,482 0.0 0.0
0 0
row cache objects 174,668 0.0 0.0
0 0
sequence cache 23,259 0.0 0.0
0 0
Latch Activity for DB: SFMS Instance: sfms Snaps: 147 -148
->"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
------------------------ -------------- ------ ------ ------
------------ ------
session allocation 100,081 0.2 0.1
0 0
session idle bit 1,741,180 0.0 0.0
0 0
session switching 53 0.0
0 0
session timer 1,103 0.0
0 0
shared pool 864,522 0.3 0.0
0 0
sim partition latch 0 0
780 0.0
simulator hash latch 1,351,647 0.0 0.0
0 0
simulator lru latch 33,645 0.1 0.0 0
390,910 0.0
sort extent pool 260 1.9 0.0
0 0
spilled msgs queues list 56 0.0
0 0
transaction allocation 3,836 0.0
0 0
transaction branch alloc 53 0.0
0 0
undo global data 29,042 0.0 0.0
0 0
user lock 30,052 0.0 0.0
0 0
virtual circuit buffers 7,865,481 0.0 0.0
0 0
virtual circuit queues 3,294,049 0.0 0.0
0 0
virtual circuits 1,559,552 0.1 0.0
0 0
-------------------------------------------------------------
Latch Sleep breakdown for DB: SFMS Instance: sfms Snaps: 147 -148
-> ordered by misses desc
Get
Spin &
Latch Name Requests Misses Sleeps
Sleeps 1->4
-------------------------- -------------- ----------- -----------
------------
cache buffers chains 26,609,079 412,341 6,546
406783/4584/
960/14/0
cache buffers lru chain 14,374 6,249 17
6236/9/4/0/0
shared pool 864,522 2,889 14
2875/14/0/0/
0
multiblock read objects 1,157,553 2,724 34
2694/26/4/0/
0
virtual circuit buffers 7,865,481 2,504 8
2496/8/0/0/0
virtual circuit queues 3,294,049 1,627 3
1624/3/0/0/0
library cache 1,568,378 1,626 8
1618/8/0/0/0
session allocation 100,081 166 13
155/9/2/0/0
session idle bit 1,741,180 136 1
135/1/0/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: SFMS Instance: sfms Snaps: 147 -148
-> 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
4,638 4,937
cache buffers chains kcbrls: kslbegin 0
1,238 1,138
cache buffers chains kcbzwb 0
385 384
cache buffers chains kcbzib: finish free bufs 0
91 18
cache buffers chains kcbzgb: scan from tail. no 0
77 0
cache buffers chains kcbchg: kslbegin: bufs not 0
62 33
cache buffers chains kcbzib: multi-block read: 0
19 0
cache buffers chains kcbgcur: kslbegin 0
1 1
cache buffers lru chain kcbzgb: multiple sets nowa #######
16 17
cache buffers lru chain kcbzgm 1,918
1 0
library cache kgllkdl: child: cleanup 0
8 7
multiblock read objects kcbzib: normal mbr free 0
19 27
multiblock read objects kcbzib: mbr get 0
15 7
session allocation ksuxds: KSUSFCLC not set 0
7 8
session allocation ksursi 0
6 5
session idle bit ksupuc: set busy 0
1 0
shared pool kghfre 0
13 12
shared pool kghupr1 0
1 1
virtual circuit queues kmcadq 0
2 2
virtual circuit queues kmcfsq 0
1 1
virtual circuits kmcgms: if called with non 0
6 0
virtual circuits kmcfms 0
1 0
virtual circuits kmcmbf 0
1 0
-------------------------------------------------------------
Top 5 Logical Reads per Segment for DB: SFMS Instance: sfms Snaps:
147 -148
-> End Segment Logical Reads Threshold: 10000
Subobject Obj.
Logical
Owner Tablespace Object Name Name Type
Reads %Total
---------- ---------- -------------------- ---------- -----
------------ -------
SFMSBR SFMS_BR_DA MHB_MSG_HDR_BR TABLE
147,533,776 54.23
SFMSBR SFMS_BR_DA SMB_SYS_MSG_BR TABLE
33,152,656 12.19
SFMSBR SFMS_BR_ND SYS_C004563 INDEX
28,093,056 10.33
SFMSBR SFMS_BR_DA SCD_SYS_CODE_DEF TABLE
10,969,728 4.03
SYS SYSTEM I_SYSAUTH1 INDEX
9,161,008 3.37
-------------------------------------------------------------
Top 5 Physical Reads per Segment for DB: SFMS Instance: sfms Snaps:
147 -148
-> End Segment Physical Reads Threshold: 1000
Subobject Obj.
Physical
Owner Tablespace Object Name Name Type
Reads %Total
---------- ---------- -------------------- ---------- -----
------------ -------
SFMSBR SFMS_BR_DA SMB_SYS_MSG_BR TABLE
29,022,540 80.88
SFMSBR SFMS_BR_DA MTB_MSG_TXT_BR TABLE
2,126,186 5.93
SFMSBR SFMS_BR_DA MHB_MSG_HDR_BR TABLE
1,787,772 4.98
SFMSBR SFMS_BR_AR MTB_MSG_TXT_BR_AR TABLE
1,065,829 2.97
SFMSBR SFMS_BR_DA SMD_SYS_MSG_DTLS TABLE
851,509 2.37
-------------------------------------------------------------
Top 5 Buf. Busy Waits per Segment for DB: SFMS Instance: sfms
Snaps: 147 -148
-> End Segment Buffer Busy Waits Threshold: 100
Buffer
Subobject Obj.
Busy
Owner Tablespace Object Name Name Type
Waits %Total
---------- ---------- -------------------- ---------- -----
------------ -------
SFMSBR SFMS_BR_DA SMB_SYS_MSG_BR TABLE
513,258 71.25
SFMSBR SFMS_BR_DA MTB_MSG_TXT_BR TABLE
168,033 23.33
SFMSBR SFMS_BR_DA MHB_MSG_HDR_BR TABLE
34,850 4.84
SFMSBR SFMS_BR_DA MPT_MSG_PROC_TBL TABLE
2,851 .40
SFMSBR SFMS_BR_DA ERD_EXT_ROUT_DTLS TABLE
631 .09
-------------------------------------------------------------
Top 5 Row Lock Waits per Segment for DB: SFMS Instance: sfms Snaps:
147 -148
-> End Segment Row Lock Waits Threshold: 100
Row
Subobject Obj.
Lock
Owner Tablespace Object Name Name Type
Waits %Total
---------- ---------- -------------------- ---------- -----
------------ -------
SFMSBR SFMS_BR_DA BSI_BUSS_SEQ_INFO TABLE
324 88.28
SFMSBR SFMS_BR_ND SYS_C004264 INDEX
21 5.72
SFMSBR SFMS_BR_DA MHB_MSG_HDR_BR TABLE
10 2.72
SFMSBR SFMS_BR_DA MPT_MSG_PROC_TBL TABLE
4 1.09
SFMSBR SFMS_BR_ND SYS_C006402 INDEX
3 .82
-------------------------------------------------------------
Top 5 ITL Waits per Segment for DB: SFMS Instance: sfms Snaps: 147
-148
-> End Segment ITL Waits Threshold: 100
Subobject Obj.
ITL
Owner Tablespace Object Name Name Type
Waits %Total
---------- ---------- -------------------- ---------- -----
------------ -------
SFMSBR SFMS_BR_ND EMD_NDX INDEX
1 100.00
-------------------------------------------------------------
Dictionary Cache Stats for DB: SFMS Instance: sfms Snaps: 147 -148
->"Pct Misses" should be very low (< 2% in most cases)
->"Cache Usage" is the number of cache entries being used
->"Pct SGA" is the ratio of usage to allocated size for that cache
Get Pct Scan Pct
Mod Final
Cache Requests Miss Reqs Miss
Reqs Usage
------------------------- ------------ ------ ------- ----- --------
----------
dc_histogram_defs 9,468 0.0 0
0 856
dc_object_ids 1,737 0.0 0
0 425
dc_objects 8,156 0.0 0
0 709
dc_profiles 7,502 0.0 0
0 1
dc_rollback_segments 322 0.0 0
0 22
dc_segments 2,077 0.0 0
17 448
dc_sequences 375 0.0 0
375 3
dc_tablespace_quotas 17 0.0 0
17 4
dc_tablespaces 372 0.0 0
0 17
dc_user_grants 15,038 0.0 0
0 18
dc_usernames 15,005 0.0 0
0 14
dc_users 37,705 0.0 0
0 29
-------------------------------------------------------------
Library Cache Activity for DB: SFMS Instance: sfms Snaps: 147 -148
->"Pct Misses" should be very low
Get Pct Pin Pct
Invali-
Namespace Requests Miss Requests Miss Reloads
dations
--------------- ------------ ------ -------------- ------ ----------
--------
BODY 1 0.0 1 0.0
0 0
INDEX 280 0.0 280 0.0
0 0
SQL AREA 108,779 0.3 436,470 0.2
0 0
TABLE/PROCEDURE 9,420 0.0 17,742 0.0
0 0
TRIGGER 14,759 0.0 14,759 0.0
0 0
-------------------------------------------------------------
Shared Pool Advisory for DB: SFMS Instance: sfms End Snap: 148
-> 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
Estd
Shared Pool SP Estd Estd Estd Lib LC Time
Size for Size Lib Cache Lib Cache Cache Time Saved Estd
Lib Cache
Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem
Obj Hits
----------- ----- ---------- ------------ ------------ -------
---------------
608 .6 524 70,563 142,828 1.0
9,550,530
712 .7 578 79,211 142,828 1.0
9,550,583
816 .8 578 79,211 142,828 1.0
9,550,583
920 .9 578 79,211 142,828 1.0
9,550,583
1,024 1.0 578 79,211 142,828 1.0
9,550,583
1,128 1.1 578 79,211 142,828 1.0
9,550,583
1,232 1.2 578 79,211 142,828 1.0
9,550,583
1,336 1.3 578 79,211 142,828 1.0
9,550,583
1,440 1.4 578 79,211 142,828 1.0
9,550,583
1,544 1.5 578 79,211 142,828 1.0
9,550,583
1,648 1.6 578 79,211 142,828 1.0
9,550,583
1,752 1.7 578 79,211 142,828 1.0
9,550,583
1,856 1.8 578 79,211 142,828 1.0
9,550,583
1,960 1.9 578 79,211 142,828 1.0
9,550,583
2,064 2.0 578 79,211 142,828 1.0
9,550,583
-------------------------------------------------------------
SGA Memory Summary for DB: SFMS Instance: sfms Snaps: 147 -148
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 1,082,130,432
Fixed Size 459,524
Redo Buffers 667,648
Variable Size 1,400,897,536
----------------
sum 2,484,155,140
-------------------------------------------------------------
SGA breakdown difference for DB: SFMS Instance: sfms Snaps: 147 -148
Pool Name Begin value End
value % Diff
------ ------------------------------ ----------------
---------------- -------
java free memory 209,715,200
209,715,200 0.00
large free memory 49,547,656
48,673,596 -1.76
large session heap 9,172,600
10,046,660 9.53
shared 1M buffer 2,098,176
2,098,176 0.00
shared DML lock 960,948
960,948 0.00
shared FileOpenBlock 6,129,492
6,129,492 0.00
shared KGK heap 3,756
3,756 0.00
shared KGLS heap 3,554,148
3,554,148 0.00
shared KQR M PO 1,044,296
1,044,296 0.00
shared KQR S PO 141,396
141,396 0.00
shared KQR S SO 4,616
4,616 0.00
shared KQR X PO 17,024
17,024 0.00
shared KSXR pending messages que 841,036
841,036 0.00
shared KSXR receive buffers 1,033,000
1,033,000 0.00
shared MTTR advisory 183,592
183,592 0.00
shared PL/SQL DIANA 645,008
645,008 0.00
shared PL/SQL MPCODE 401,116
401,116 0.00
shared PLS non-lib hp 2,068
2,068 0.00
shared VIRTUAL CIRCUITS 2,400,000
2,400,000 0.00
shared db_block_hash_buckets 3,901,224
3,901,224 0.00
shared db_handles 1,008,000
1,008,000 0.00
shared dictionary cache 2,137,216
2,137,216 0.00
shared enqueue 1,565,772
1,565,772 0.00
shared errors 9,612
9,612 0.00
shared event statistics per sess 17,477,040
17,477,040 0.00
shared fixed allocation callback 1,084
1,084 0.00
shared free memory 427,215,256
419,247,324 -1.87
shared joxs heap init 4,220
4,220 0.00
shared ktlbk state objects 727,172
727,172 0.00
shared library cache 114,446,020
116,049,436 1.40
shared miscellaneous 28,475,548
27,737,908 -2.59
shared parameters 23,564
23,564 0.00
shared processes 1,400,000
1,400,000 0.00
shared sessions 3,732,720
3,732,720 0.00
shared sim memory hea 643,856
643,856 0.00
shared sql area 508,499,588
515,601,688 1.40
shared table definiti 5,064
5,120 1.11
shared transaction 1,719,916
1,719,916 0.00
shared trigger defini 5,528
5,528 0.00
shared trigger inform 1,176
1,176 0.00
shared trigger source 2,832
2,832 0.00
buffer_cache 1,082,130,432
1,082,130,432 0.00
fixed_sga 459,524
459,524 0.00
log_buffer 656,384
656,384 0.00
-------------------------------------------------------------
init.ora Parameters for DB: SFMS Instance: sfms Snaps: 147 -148
End
value
Parameter Name Begin value (if
different)
----------------------------- ---------------------------------
--------------
_log_archive_callout LOCAL_FIRST=TRUE
aq_tm_processes 1
background_dump_dest C:\oracle\admin\SFMS\bdump
compatible 9.2.0.0.0
control_files C:\ora9i\oradata\SFMS\CONTROL01.C
core_dump_dest C:\oracle\admin\SFMS\cdump
db_block_size 8192
db_cache_size 1073741824
db_domain
db_file_multiblock_read_count 16
db_keep_cache_size 8388608
db_name SFMS
dispatchers (PROTOCOL=TCP)(DISP=25) (CON=20)
fal_client SFMS
fal_server SFMSDR
fast_start_mttr_target 300
hash_join_enabled TRUE
instance_name SFMS
java_pool_size 209715200
job_queue_processes 10
large_pool_size 58720256
log_archive_dest_1 location=d:\archive_1
log_archive_dest_2 service=sfmsdr arch reopen=60 opt
log_archive_dest_state_2 ENABLE
log_archive_format %%ORACLE_SID%%T%TS%S.ARC
log_archive_start TRUE
max_dispatchers 50
max_shared_servers 50
open_cursors 9000
pga_aggregate_target 500165824
processes 1400
query_rewrite_enabled FALSE
remote_login_passwordfile EXCLUSIVE
shared_pool_size 1073741824
shared_servers 25
sort_area_size 65536
standby_archive_dest d:\archive_1
standby_file_management AUTO
star_transformation_enabled FALSE
timed_statistics TRUE
undo_management AUTO
undo_retention 10800
undo_tablespace UNDOTBS1
user_dump_dest C:\oracle\admin\SFMS\udump
-------------------------------------------------------------
End of Report
We have Oracle 9i database on windows 2003 OS. Our system is very
slow. So I have staspack report for reference. Please help me in
analyzing this report taken for a period of 28 minutes.
Regards
swathi
Below is report
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster
Host
------------ ----------- ------------ -------- ----------- -------
------------
SFMS 1649285097 sfms 1 9.2.0.8.0 NO
DBSERVER
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- ---------
-------------------
Begin Snap: 147 14-Nov-08 17:10:00 135 2.3
End Snap: 148 14-Nov-08 17:38:11 113 3.0
Elapsed: 28.18 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,032M Std Block Size:
8K
Shared Pool Size: 1,024M Log Buffer:
512K
Load Profile
~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Redo size: 31,297.28
16,533.49
Logical reads: 5,916.56
3,125.56
Block changes: 127.63
67.42
Physical reads: 3,688.18
1,948.36
Physical writes: 5.58
2.95
User calls: 502.95
265.70
Parses: 64.34
33.99
Hard parses: 0.22
0.12
Sorts: 51.04
26.96
Logons: 4.44
2.34
Executes: 67.26
35.53
Transactions: 1.89
% Blocks changed per Read: 2.16 Recursive Call %: 25.90
Rollback per transaction %: 18.62 Rows per Sort: 16.34
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 97.87 Redo NoWait %: 100.00
Buffer Hit %: 37.70 In-memory Sort %: 99.98
Library Hit %: 99.84 Soft Parse %: 99.66
Execute to Parse %: 4.34 Latch Hit %: 99.11
Parse CPU to Parse Elapsd %: 94.39 % Non-Parse CPU: 96.91
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 62.28 62.98
% SQL with executions>1: 43.83 43.69
% Memory for SQL w/exec>1: 56.20 56.07
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
% Total
Event Waits Time (s)
Ela Time
-------------------------------------------- ------------ -----------
--------
db file scattered read 473,574
4,328 51.13
ARCH wait on SENDREQ 171
1,667 19.69
buffer busy waits 213,401
1,630 19.26
db file sequential read 113,772
500 5.91
CPU time
234 2.76
-------------------------------------------------------------
Wait Events for DB: SFMS Instance: sfms Snaps: 147 -148
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait
wait Waits
Event Waits Timeouts Time (s)
(ms) /txn
---------------------------- ------------ ---------- ---------- ------
--------
db file scattered read 473,574 0 4,328
9 147.9
ARCH wait on SENDREQ 171 0 1,667
9747 0.1
buffer busy waits 213,401 16 1,630
8 66.7
db file sequential read 113,772 0 500
4 35.5
db file parallel write 501 0 45
90 0.2
log file sync 2,990 4 32
11 0.9
control file parallel write 573 0 10
18 0.2
latch free 6,639 6,542 10
1 2.1
control file sequential read 452 0 3
7 0.1
enqueue 35 0 3
78 0.0
direct path read 1,794 0 2
1 0.6
log file parallel write 4,081 0 0
0 1.3
direct path write 91 0 0
4 0.0
ARCH wait on ATTACH 1 0 0
180 0.0
SQL*Net more data to client 1,126 0 0
0 0.4
SQL*Net break/reset to clien 22 0 0
0 0.0
log file sequential read 13 0 0
0 0.0
LGWR wait for redo copy 168 0 0
0 0.1
async disk IO 56 0 0
0 0.0
undo segment extension 1 1 0
0 0.0
buffer deadlock 2 2 0
0 0.0
virtual circuit status 942,800 915 34,617
37 294.5
SQL*Net message from client 857,967 0 4,247
5 268.0
wakeup time manager 56 56 1,616
28854 0.0
SQL*Net message to client 857,950 0 2
0 268.0
SQL*Net more data from clien 5,485 0 0
0 1.7
-------------------------------------------------------------
Background Wait Events for DB: SFMS Instance: sfms Snaps: 147 -148
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait
wait Waits
Event Waits Timeouts Time (s)
(ms) /txn
---------------------------- ------------ ---------- ---------- ------
--------
ARCH wait on SENDREQ 171 0 1,667
9747 0.1
db file parallel write 501 0 45
90 0.2
control file parallel write 573 0 10
18 0.2
control file sequential read 368 0 3
7 0.1
db file sequential read 286 0 0
2 0.1
log file parallel write 4,082 0 0
0 1.3
direct path write 57 0 0
4 0.0
ARCH wait on ATTACH 1 0 0
180 0.0
direct path read 74 0 0
2 0.0
log file sequential read 13 0 0
0 0.0
rdbms ipc reply 12 0 0
0 0.0
LGWR wait for redo copy 168 0 0
0 0.1
async disk IO 56 0 0
0 0.0
db file scattered read 1 0 0
0 0.0
buffer busy waits 1 0 0
0 0.0
rdbms ipc message 5,258 1,735 7,982
1518 1.6
pmon timer 1,103 1,103 1,690
1532 0.3
smon timer 25 0 1,584
63363 0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: SFMS Instance: sfms Snaps: 147 -148
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used
by
all SQL statements called within the PL/SQL code. As individual
SQL
statements are also reported, it is possible and valid for the
summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
Hash Value
--------------- ------------ -------------- ------ -------- ---------
----------
3,537,730 7 505,390.0 35.4 102.47 1444.15
965668055
Module: JDBC Thin Client
SELECT * FROM SMB_SYS_MSG_BR WHERE SMB_IN_OUT_IND = :1 AND SMB_
OWNR_ADDRS = :2 AND to_char(SMB_SEND_DATE,'%Y') = :3 AND to_cha
r(SMB_SEND_DATE,'%m') = :4 AND to_char(SMB_SEND_DATE,'%d') = :5
1,041,674 2 520,837.0 10.4 27.39 312.75
3847947103
Module: JDBC Thin Client
SELECT * FROM SMB_SYS_MSG_BR WHERE SMB_IN_OUT_IND = :1 AND SMB_O
WNR_ADDRS = :2 ORDER BY SMB_MSG_STAT,DM_LSTUPDDT desc
336,831 519 649.0 3.4 3.45 3.50
3785048508
Module: JDBC Thin Client
SELECT substr(SCD_MNEMNC,4)||'--'||SCD_CODE_DESC MNMNCDESC FROM
SCD_SYS_CODE_DEF WHERE SCD_CODE_ID=:1 AND SCD_CODE_VAL IN (100,1
01,102,103,1000,104,105,107,110,111,112,190,191,192,195,196,198,
199,200,201,202,203,204,205,206,207,210,256,290,291,292,293,295,
296,298,2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2
224,280 7,476 30.0 2.2 7.48 6.90
1356713530
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with (grantee#=:1 or grantee#=
1) and privilege#>0
221,022 713 310.0 2.2 6.84 7.01
3199451410
Module: JDBC Thin Client
SELECT END_IFSC,END_CERT_NAME FROM END_ENC_CERT_DTLS
194,213 2,966 65.5 1.9 4.00 22.73
826609426
Module: JDBC Thin Client
INSERT INTO MPT_MSG_PROC_TBL VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,
:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,
:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,
:42,:43,:44,:45,:46,:47,:48,:49,:50,:51)
181,972 194 938.0 1.8 4.47 4.63
212880470
Module: JDBC Thin Client
SELECT SCD_CODE_VAL FROM SCD_SYS_CODE_DEF WHERE SCD_CODE_ID=:1
AND SCD_LANG_CODE= :2 AND SCD_CODE_VAL NOT IN (SELECT SCD_CODE_
VAL FROM SCD_SYS_CODE_DEF WHERE SCD_CODE_ID=:3) ORDER BY SCD_MNE
MNC
116,594 194 601.0 1.2 1.31 1.21
3193493324
Module: JDBC Thin Client
SELECT substr(SCD_MNEMNC,4)||'--'||SCD_CODE_DESC MNMNCDESC FROM
SCD_SYS_CODE_DEF WHERE SCD_CODE_ID=:1 AND SCD_CODE_VAL IN (100,1
01,102,103,1000,104,105,107,110,111,112,190,191,192,195,196,198,
199,200,201,202,203,204,205,206,207,210,256,290,291,292,293,295,
296,298,2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2
SQL ordered by Gets for DB: SFMS Instance: sfms Snaps: 147 -148
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used
by
all SQL statements called within the PL/SQL code. As individual
SQL
statements are also reported, it is possible and valid for the
summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
Hash Value
--------------- ------------ -------------- ------ -------- ---------
----------
75,458 2 37,729.0 0.8 4.09 9.38
1229324988
Module: JDBC Thin Client
SELECT MHB_USER_ID, MHB_CRT_RCV_SEQ_NUM, MHB_MSG_STAT FROM MHB_M
SG_HDR_BR WHERE MHB_UTR = 'CNRBH08319300805' AND MHB_IN_OUT_IND
= 2 FOR UPDATE
52,770 26,387 2.0 0.5 3.02 2.71
3329356112
Module: JDBC Thin Client
SELECT 1 FROM SCD_SYS_CODE_DEF WHERE SCD_CODE_ID = :1 AND SCD_CO
DE_VAL = :2 AND SCD_LANG_CODE = :3
52,515 1,067 49.2 0.5 0.34 0.43
2243488166
Module: JDBC Thin Client
select COUNT(*) from mpt_msg_proc_tbl where MPT_OWNR_ADDRS=:1 AN
D MPT_IN_OUT_IND=:2 and mpt_crt_rcv_num =:3 and MPT_MSG_SEQ=:4
and mpt_sub_elem_id=:5
44,377 5 8,875.4 0.4 1.02 1.03
2845187387
Module: JDBC Thin Client
SELECT UTT_TEMPL_ID,MHB_MSG_TYP,UTT_TEMPL_DESC,MHB_CRT_RCV_SEQ_N
UM,MHB_RCVR_SNDR_ADDRS,MHB_MUR,MHB_MSG_HDR_BR_VER,DM_LSTUPDDT FR
OM MHB_MSG_HDR_BR,UTT_USR_TEMPL_TBL WHERE to_char(UTT_TEMPL_ID)=
substr(MHB_SNDR_REF,12) AND UTT_TEMPL_IFSC=MHB_OWNR_ADDRS AND U
TT_TEMPL_IFSC=:1 AND MHB_MSG_TYP IN ( 100,101,102,103,1000,104,
35,178 533 66.0 0.4 1.19 1.09
3244566476
Module: JDBC Thin Client
SELECT SCD_CODE_VAL FROM SCD_SYS_CODE_DEF WHERE SCD_CODE_ID=:1
AND SCD_LANG_CODE= :2 AND ( SCD_MNEMNC NOT LIKE 'IFN298I%' AND
SCD_MNEMNC NOT LIKE 'IFN298A%') AND SCD_MNEMNC NOT IN ('IFN500',
'IFN501','IFN502','IFN509','IFN513','IFN535','IFN536','IFN537','
IFN540','IFN541','IFN542','IFN543','IFN544','IFN545','IFN546','I
29,438 2,816 10.5 0.3 0.97 14.83
1364225184
Module: JDBC Thin Client
SELECT MPT_ELEM_VALUE FROM MPT_MSG_PROC_TBL WHERE MPT_IN_OUT_IN
D = :1 AND MPT_OWNR_ADDRS = :2 AND MPT_CRT_RCV_NUM = :3 AND MPT
_FLD_ID IN (20, 2020) AND MPT_FLD_OPTION='-' ORDER BY MPT_ELEM_
SEQ_D1, MPT_ELEM_SEQ_D2, MPT_ELEM_SEQ_D3, MPT_ELEM_SEQ_D4, MPT_
ELEM_SEQ_D5, MPT_ELEM_SEQ_D6, MPT_ELEM_SEQ_D7, MPT_ELEM_SEQ_D8,
26,334 399 66.0 0.3 0.36 0.30
1775992258
Module: JDBC Thin Client
SELECT SCD_CODE_VAL FROM SCD_SYS_CODE_DEF WHERE SCD_CODE_ID =
:1 AND SCD_MNEMNC = :2
24,975 925 27.0 0.2 0.28 0.21
1994601063
Module: JDBC Thin Client
SELECT RFM_FUNC_ID FROM RFM_ROLE_FUNC_MAP WHERE RFM_ROLE_ID =
:1 AND RFM_FUNC_ID IN (:2,:3,:4,:5,:6,:7,:8,:9,:10) AND RFM_IFSC
SQL ordered by Gets for DB: SFMS Instance: sfms Snaps: 147 -148
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used
by
all SQL statements called within the PL/SQL code. As individual
SQL
statements are also reported, it is possible and valid for the
summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
Hash Value
--------------- ------------ -------------- ------ -------- ---------
----------
= :11 AND RFM_AGENCY = :12
21,410 56 382.3 0.2 0.42 0.63
2614951860
select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
ables t, sys.aq$_queue_table_affinities aft, system.aq$_que
ues q where aft.table_objno = t.objno and aft.owner_instance = :
1 and q.table_objno = t.objno and q.usage = 0 and b
itand(t.flags, 4+16+32+64+128+256) = 0 and NOT ( t.n
19,538 24 814.1 0.2 0.13 0.15
418953923
Module: JDBC Thin Client
SELECT COUNT(*) FROM MHB_MSG_HDR_BR WHERE MHB_IN_OUT_IND = 1 AN
D MHB_TRNG_FLAG = 2 AND MHB_OWNR_ADDRS = 'CNRB0001098' AND MHB_M
SG_STAT <> 19 AND MHB_MSG_STAT <> 50 AND MHB_AGENCY = 1 AND MHB
_MSG_STAT <> 18 AND MHB_MSG_TYP IN (100,101,102,103,1000,104,105
-------------------------------------------------------------
SQL ordered by Reads for DB: SFMS Instance: sfms Snaps: 147 -148
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
--------------- ------------ -------------- ------ -------- ---------
----------
3,130,973 7 447,281.9 50.2 102.47 1444.15
965668055
Module: JDBC Thin Client
SELECT * FROM SMB_SYS_MSG_BR WHERE SMB_IN_OUT_IND = :1 AND SMB_
OWNR_ADDRS = :2 AND to_char(SMB_SEND_DATE,'%Y') = :3 AND to_cha
r(SMB_SEND_DATE,'%m') = :4 AND to_char(SMB_SEND_DATE,'%d') = :5
1,009,508 2 504,754.0 16.2 27.39 312.75
3847947103
Module: JDBC Thin Client
SELECT * FROM SMB_SYS_MSG_BR WHERE SMB_IN_OUT_IND = :1 AND SMB_O
WNR_ADDRS = :2 ORDER BY SMB_MSG_STAT,DM_LSTUPDDT desc
11,710 4 2,927.5 0.2 0.70 7.95
2750276848
Module: JDBC Thin Client
DELETE FROM MTR_MSG_TRN_REF WHERE MTR_OWNR_ADDRS = :1 and MTR_IN
_OUT_IND = :2 and MTR_CRT_RCV_NUM = :3
3,448 1 3,448.0 0.1 1.84 8.05
4131769790
Module: JDBC Thin Client
SELECT MHB_OWNR_ADDRS ,MHB_CRT_RCV_SEQ_NUM,MHB_SNDR_REF,MHB_RCVR
_SNDR_ADDRS,MHB_MSG_STAT,MHB_MSG_TYP,MHB_OPEN_FLG,MHB_CRTN_DATE,
MHB_REC_DATE,MHB_USER_ID,MHB_MSG_HDR_BR_VER,DM_LSTUPDDT,MHB_UTR
FROM MHB_MSG_HDR_BR WHERE MHB_IN_OUT_IND = 1 AND MHB_TRNG_FLAG
= 2 AND MHB_OWNR_ADDRS = 'CNRB0MUMCLG' AND MHB_MSG_STAT <> 19 AN
1,703 2,966 0.6 0.0 4.00 22.73
826609426
Module: JDBC Thin Client
INSERT INTO MPT_MSG_PROC_TBL VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,
:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,
:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,
:42,:43,:44,:45,:46,:47,:48,:49,:50,:51)
1,237 2,816 0.4 0.0 0.97 14.83
1364225184
Module: JDBC Thin Client
SELECT MPT_ELEM_VALUE FROM MPT_MSG_PROC_TBL WHERE MPT_IN_OUT_IN
D = :1 AND MPT_OWNR_ADDRS = :2 AND MPT_CRT_RCV_NUM = :3 AND MPT
_FLD_ID IN (20, 2020) AND MPT_FLD_OPTION='-' ORDER BY MPT_ELEM_
SEQ_D1, MPT_ELEM_SEQ_D2, MPT_ELEM_SEQ_D3, MPT_ELEM_SEQ_D4, MPT_
ELEM_SEQ_D5, MPT_ELEM_SEQ_D6, MPT_ELEM_SEQ_D7, MPT_ELEM_SEQ_D8,
658 2 329.0 0.0 4.09 9.38
1229324988
Module: JDBC Thin Client
SELECT MHB_USER_ID, MHB_CRT_RCV_SEQ_NUM, MHB_MSG_STAT FROM MHB_M
SG_HDR_BR WHERE MHB_UTR = 'CNRBH08319300805' AND MHB_IN_OUT_IND
= 2 FOR UPDATE
612 9 68.0 0.0 0.20 0.74
2315869717
Module: JDBC Thin Client
SELECT FMI_ERR_DESC,FMI_USER_ID,FMI_RSND_CNT,DM_LSTUPDDT,FMI_SND
R_IFSC,FMI_CRTN_DATE,FMI_DCRYPTD_MSG_1,FMI_DCRYPTD_MSG_2,FMI_DCR
YPTD_MSG_3 FROM FMI_FAIL_MSG_INFO WHERE FMI_SNDR_REF=:1
506 1 506.0 0.0 1.03 2.53
2153809700
Module: JDBC Thin Client
SELECT COUNT(*) FROM MHB_MSG_HDR_BR WHERE MHB_IN_OUT_IND = 1 AN
SQL ordered by Reads for DB: SFMS Instance: sfms Snaps: 147 -148
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
--------------- ------------ -------------- ------ -------- ---------
----------
D MHB_TRNG_FLAG = 2 AND MHB_OWNR_ADDRS = 'CNRB0MUMCLG' AND MHB_M
SG_STAT <> 19 AND MHB_MSG_STAT <> 50 AND MHB_AGENCY = 1 AND MHB
_MSG_STAT <> 18 AND MHB_MSG_TYP IN (100,101,102,103,1000,104,105
,107,110,111,112,190,191,192,195,196,198,199,200,201,202,203,204
393 289 1.4 0.0 0.27 7.83
345990914
Module: JDBC Thin Client
INSERT INTO MTB_MSG_TXT_BR values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10
,1,:11)
286 1 286.0 0.0 0.03 0.38
4115672663
select u.name, o.name, t.spare1, t.pctfree$ from sys.obj$ o,
sys.user$ u, sys.tab$ t where (bitand(t.trigflag, 1048576) =
1048576) and o.obj#=t.obj# and o.owner# = u.user#
279 16 17.4 0.0 0.05 1.47
2817775879
Module: JDBC Thin Client
SELECT MHB_MSG_TYP, MHB_SNDR_REF,MHB_RCVR_SNDR_ADDRS,DM_LSTUPDDT
FROM MHB_MSG_HDR_BR_AR WHERE MHB_OWNR_ADDRS= :1 AND DM_LSTUP
DDT >= :2 AND DM_LSTUPDDT <= :3 AND MHB_MSG_STAT=:4 AND MHB_I
N_OUT_IND=:5 AND MHB_AGENCY=:6 AND MHB_TRNG_FLAG=:7 ORDER BY
DM_LSTUPDDT,MHB_MSG_TYP
245 5 49.0 0.0 0.06 2.25
3206473185
Module: JDBC Thin Client
SELECT MHB_CRT_RCV_SEQ_NUM ,MHB_MSG_TYP ,MHB_SNDR_REF ,MHB_RC
VR_SNDR_ADDRS ,MHB_CRTN_DATE ,MHB_PDE ,MHB_TRNG_FLAG ,MHB_MS
G_STAT FROM MHB_MSG_HDR_BR_AR WHERE MHB_OWNR_ADDRS= :1 AND MH
B_CRTN_DATE >= :2 AND MHB_CRTN_DATE <= :3 AND MHB_IN_OUT_IND=
:4 AND MHB_AGENCY=:5 ORDER BY MHB_CRTN_DATE
230 132 1.7 0.0 0.06 3.43
2452212640
Module: JDBC Thin Client
SELECT 1 FROM LLD_LOGIN_LOGOUT_DTLS_AR WHERE LLD_USER_ID=:1 AND
LLD_IFSC=:2
165 1 165.0 0.0 0.92 0.97
323607971
Module: JDBC Thin Client
UPDATE MHB_MSG_HDR_BR SET MHB_MSG_STAT='25' WHERE MHB_UTR = 'CNR
BH08319300805' AND MHB_IN_OUT_IND = 2
164 1 164.0 0.0 1.06 2.71
1728714374
Module: JDBC Thin Client
UPDATE MHB_MSG_HDR_BR SET MHB_MSG_STAT='27' WHERE MHB_UTR = 'CNR
BH08319300805' AND MHB_IN_OUT_IND = 2
130 358 0.4 0.0 0.23 3.02
3629200986
Module: JDBC Thin Client
INSERT INTO MTB_MSG_TXT_BR VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:1
0,:11,:12)
109 122 0.9 0.0 0.05 3.15
2966950249
Module: JDBC Thin Client
SELECT MTB_MSG_BLOCK5A FROM MTB_MSG_TXT_BR WHERE MTB_OWNR_ADDR
SQL ordered by Reads for DB: SFMS Instance: sfms Snaps: 147 -148
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
--------------- ------------ -------------- ------ -------- ---------
----------
S = :1 AND MTB_IN_OUT_IND =:2 AND MTB_CRT_RCV_SEQ_NUM = :3 unio
n all SELECT MTB_MSG_BLOCK5A FROM MTB_MSG_TXT_BR_AR WHERE MTB_O
WNR_ADDRS = :4 AND MTB_IN_OUT_IND =:5 AND MTB_CRT_RCV_SEQ_NUM =
:6
109 756 0.1 0.0 0.38 3.34
3003997262
Module: JDBC Thin Client
SELECT MTB_MSG_BLOCK1,MTB_MSG_BLOCK2,MTB_MSG_BLOCK3,MTB_MSG_BLOC
K4,MTB_MSG_BLOCK5A,MTB_MSG_BLOCK5B,MTB_MSG_BLOCK5C,MTB_MSG_TXT_B
R_VER,DM_LSTUPDDT FROM MTB_MSG_TXT_BR WHERE MTB_OWNR_ADDRS = :1
AND MTB_IN_OUT_IND = :2 AND MTB_CRT_RCV_SEQ_NUM = :3
-------------------------------------------------------------
SQL ordered by Executions for DB: SFMS Instance: sfms Snaps: 147
-148
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
------------ --------------- ---------------- ----------- ----------
----------
26,387 25,977 1.0 0.00 0.00
3329356112
Module: JDBC Thin Client
SELECT 1 FROM SCD_SYS_CODE_DEF WHERE SCD_CODE_ID = :1 AND SCD_CO
DE_VAL = :2 AND SCD_LANG_CODE = :3
7,476 194,376 26.0 0.00 0.00
1356713530
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with (grantee#=:1 or grantee#=
1) and privilege#>0
6,724 0 0.0 0.00 0.00
3570694802
Module: JDBC Thin Client
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN'
6,721 6,721 1.0 0.00 0.00
1702556642
Module: JDBC Thin Client
SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER ='NLS_
DATE_FORMAT'
6,715 0 0.0 0.00 0.00
2707901715
Module: JDBC Thin Client
ALTER SESSION SET NLS_TERRITORY = 'AMERICA'
4,094 6,086 1.5 0.00 0.00
1780264807
Module: JDBC Thin Client
SELECT * FROM MSD_SUBFLD_DEF WHERE MSD_MSG_SEQ= :1 AND MSD_FLD =
:2 AND MSD_OPTION = :3 ORDER BY MSD_SUBFLD_SEQ
4,094 4,094 1.0 0.00 0.00
4157967512
Module: JDBC Thin Client
SELECT COUNT(*) FROM MSD_SUBFLD_DEF WHERE MSD_MSG_SEQ = :1 AND M
SD_FLD = :2 AND MSD_OPTION = :3
3,531 3,531 1.0 0.00 0.00
2290514744
Module: JDBC Thin Client
SELECT UAI_CONNECT_STAT FROM UAI_USER_ACC_INFO WHERE UAI_USER_ID
= :1AND UAI_USER_NODE_ADDRS = :2
2,966 35,066 11.8 0.00 0.01
826609426
Module: JDBC Thin Client
INSERT INTO MPT_MSG_PROC_TBL VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,
:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,
:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,
:42,:43,:44,:45,:46,:47,:48,:49,:50,:51)
2,816 2,388 0.8 0.00 0.01
1364225184
Module: JDBC Thin Client
SELECT MPT_ELEM_VALUE FROM MPT_MSG_PROC_TBL WHERE MPT_IN_OUT_IN
D = :1 AND MPT_OWNR_ADDRS = :2 AND MPT_CRT_RCV_NUM = :3 AND MPT
_FLD_ID IN (20, 2020) AND MPT_FLD_OPTION='-' ORDER BY MPT_ELEM_
SEQ_D1, MPT_ELEM_SEQ_D2, MPT_ELEM_SEQ_D3, MPT_ELEM_SEQ_D4, MPT_
ELEM_SEQ_D5, MPT_ELEM_SEQ_D6, MPT_ELEM_SEQ_D7, MPT_ELEM_SEQ_D8,
1,537 1,537 1.0 0.00 0.00
1774114274
SQL ordered by Executions for DB: SFMS Instance: sfms Snaps: 147
-148
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
------------ --------------- ---------------- ----------- ----------
----------
Module: JDBC Thin Client
SELECT COUNT(*) FROM CWD_CODE_WORD WHERE CWD_MSG_SEQ = :1 AN
D CWD_FLD_ID = :2 AND CWD_FLD_OPTION = :3 AND CWD_SUBFLD_SEQ = :
4
1,331 1,191 0.9 0.00 0.00
2124505501
Module: JDBC Thin Client
SELECT MHB_SNDR_REF,MHB_RCVR_REF,MHB_RCVR_SNDR_ADDRS,MHB_AGENCY,
MHB_AGENCY_CD,MHB_MSG_STAT,MHB_MSG_STAT_CD,MHB_MSG_TYP,MHB_MSG_T
YP_CD,MHB_MSG_PRRTY,MHB_TRNG_FLAG,MHB_NON_DLV_WRNG_IND,MHB_OBSLN
C_PRD,MHB_PDE,MHB_DLM,MHB_DELAYED,MHB_MSG_VER,MHB_OPEN_FLG,MHB_O
RIG_FL_NAME,MHB_PREV_ADDRS,MHB_NEXT_ADDRS,MHB_CRTN_DATE,MHB_SEND
1,176 1,169 1.0 0.00 0.00
3876120182
Module: JDBC Thin Client
SELECT UAI_CREATOR_ID,UAI_ENABLER_ID,UAI_MODIFIER_ID,UAI_APPROV
ER_ID,UAI_USER_EMP_NO,UAI_SMART_CARD_SL,UAI_USER_GRP_CODE,UAI_US
ER_GRP_CODE_CD,UAI_USER_NAME,UAI_USER_STAT,UAI_USER_STAT_CD,UAI_
CRTN_DATE,UAI_ROLE_ID,UAI_ENABLE_ONDT,UAI_CONNECT_STAT,UAI_CONNE
CT_STAT_CD,UAI_LAST_LOGIN_DATE,UAI_LAST_LOGINON,UAI_AGENCY,UAI_U
1,151 1,151 1.0 0.00 0.00
1495493136
Module: JDBC Thin Client
UPDATE MHB_MSG_HDR_BR SET MHB_SNDR_REF = :1,MHB_RCVR_REF = :2,MH
B_RCVR_SNDR_ADDRS = :3,MHB_AGENCY = :4,MHB_AGENCY_CD = :5,MHB_MS
G_STAT = :6,MHB_MSG_STAT_CD = :7,MHB_MSG_TYP = :8,MHB_MSG_TYP_CD
= :9,MHB_MSG_PRRTY = :10,MHB_TRNG_FLAG = :11,MHB_NON_DLV_WRNG_I
ND = :12,MHB_OBSLNC_PRD = :13,MHB_PDE = :14,MHB_DLM = :15,MHB_DE
1,131 1,128 1.0 0.00 0.00
685878034
Module: JDBC Thin Client
INSERT INTO SMD_SYS_MSG_DTLS VALUES (:1,:2,:3,:4,:5,:6,:7,:8,0,0
,null,:9,:10,:11,:12,null,:13,1,:14)
1,120 1,120 1.0 0.00 0.00
3371479671
select t.name, (select owner_instance from sys.aq$_queue_table_
affinities where table_objno = t.objno) from system.aq$_queue
_tables t where t.name = :1 and t.schema = :2 for update skip lo
cked
1,067 1,067 1.0 0.00 0.00
2243488166
Module: JDBC Thin Client
select COUNT(*) from mpt_msg_proc_tbl where MPT_OWNR_ADDRS=:1 AN
D MPT_IN_OUT_IND=:2 and mpt_crt_rcv_num =:3 and MPT_MSG_SEQ=:4
and mpt_sub_elem_id=:5
925 6,040 6.5 0.00 0.00
1994601063
Module: JDBC Thin Client
SELECT RFM_FUNC_ID FROM RFM_ROLE_FUNC_MAP WHERE RFM_ROLE_ID =
:1 AND RFM_FUNC_ID IN (:2,:3,:4,:5,:6,:7,:8,:9,:10) AND RFM_IFSC
= :11 AND RFM_AGENCY = :12
851 562 0.7 0.00 0.00
4150459836
Module: JDBC Thin Client
SQL ordered by Executions for DB: SFMS Instance: sfms Snaps: 147
-148
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
------------ --------------- ---------------- ----------- ----------
----------
SELECT MHB_OWNR_ADDRS,MHB_IN_OUT_IND,MHB_CRT_RCV_SEQ_NUM,MHB_RCV
R_REF,MHB_RCVR_SNDR_ADDRS,MHB_AGENCY,MHB_AGENCY_CD,MHB_MSG_STAT,
MHB_MSG_STAT_CD,MHB_MSG_TYP,MHB_MSG_TYP_CD,MHB_MSG_PRRTY,MHB_TRN
G_FLAG,MHB_NON_DLV_WRNG_IND,MHB_OBSLNC_PRD,MHB_PDE,MHB_DLM,MHB_D
ELAYED,MHB_MSG_VER,MHB_OPEN_FLG,MHB_ORIG_FL_NAME,MHB_PREV_ADDRS,
845 0 0.0 0.00 0.00
687864414
Module: JDBC Thin Client
SELECT STBSB_OWNR_ADDRS,STBSB_IN_OUT_IND,STBSB_CRT_RCV_SEQ_NUM
FROM STBSB_TO_BE_SENT_BR ORDER BY DM_LSTUPDDT ASC
783 416 0.5 0.00 0.00
850485391
Module: JDBC Thin Client
SELECT TBSB_OWNR_ADDRS,TBSB_IN_OUT_IND,TBSB_CRT_RCV_SEQ_NUM FRO
M TBSB_TO_BE_SENT_BR ORDER BY DM_LSTUPDDT ASC
779 779 1.0 0.00 0.00
937435024
Module: JDBC Thin Client
SELECT 1 FROM IDI_IFSC_DIR_INFO WHERE IDI_IFSC_CODE = :1
-------------------------------------------------------------
SQL ordered by Parse Calls for DB: SFMS Instance: sfms Snaps: 147
-148
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
26,387 26,387 24.25 3329356112
Module: JDBC Thin Client
SELECT 1 FROM SCD_SYS_CODE_DEF WHERE SCD_CODE_ID = :1 AND SCD_CO
DE_VAL = :2 AND SCD_LANG_CODE = :3
7,476 7,476 6.87 1356713530
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with (grantee#=:1 or grantee#=
1) and privilege#>0
6,724 6,724 6.18 3570694802
Module: JDBC Thin Client
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN'
6,721 6,721 6.18 1702556642
Module: JDBC Thin Client
SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER ='NLS_
DATE_FORMAT'
6,715 6,715 6.17 2707901715
Module: JDBC Thin Client
ALTER SESSION SET NLS_TERRITORY = 'AMERICA'
4,094 4,094 3.76 1780264807
Module: JDBC Thin Client
SELECT * FROM MSD_SUBFLD_DEF WHERE MSD_MSG_SEQ= :1 AND MSD_FLD =
:2 AND MSD_OPTION = :3 ORDER BY MSD_SUBFLD_SEQ
4,094 4,094 3.76 4157967512
Module: JDBC Thin Client
SELECT COUNT(*) FROM MSD_SUBFLD_DEF WHERE MSD_MSG_SEQ = :1 AND M
SD_FLD = :2 AND MSD_OPTION = :3
3,531 3,531 3.25 2290514744
Module: JDBC Thin Client
SELECT UAI_CONNECT_STAT FROM UAI_USER_ACC_INFO WHERE UAI_USER_ID
= :1AND UAI_USER_NODE_ADDRS = :2
2,966 2,966 2.73 826609426
Module: JDBC Thin Client
INSERT INTO MPT_MSG_PROC_TBL VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,
:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,
:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,
:42,:43,:44,:45,:46,:47,:48,:49,:50,:51)
2,815 2,816 2.59 1364225184
Module: JDBC Thin Client
SELECT MPT_ELEM_VALUE FROM MPT_MSG_PROC_TBL WHERE MPT_IN_OUT_IN
D = :1 AND MPT_OWNR_ADDRS = :2 AND MPT_CRT_RCV_NUM = :3 AND MPT
_FLD_ID IN (20, 2020) AND MPT_FLD_OPTION='-' ORDER BY MPT_ELEM_
SEQ_D1, MPT_ELEM_SEQ_D2, MPT_ELEM_SEQ_D3, MPT_ELEM_SEQ_D4, MPT_
ELEM_SEQ_D5, MPT_ELEM_SEQ_D6, MPT_ELEM_SEQ_D7, MPT_ELEM_SEQ_D8,
1,537 1,537 1.41 1774114274
SQL ordered by Parse Calls for DB: SFMS Instance: sfms Snaps: 147
-148
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
Module: JDBC Thin Client
SELECT COUNT(*) FROM CWD_CODE_WORD WHERE CWD_MSG_SEQ = :1 AN
D CWD_FLD_ID = :2 AND CWD_FLD_OPTION = :3 AND CWD_SUBFLD_SEQ = :
4
1,331 1,331 1.22 2124505501
Module: JDBC Thin Client
SELECT MHB_SNDR_REF,MHB_RCVR_REF,MHB_RCVR_SNDR_ADDRS,MHB_AGENCY,
MHB_AGENCY_CD,MHB_MSG_STAT,MHB_MSG_STAT_CD,MHB_MSG_TYP,MHB_MSG_T
YP_CD,MHB_MSG_PRRTY,MHB_TRNG_FLAG,MHB_NON_DLV_WRNG_IND,MHB_OBSLN
C_PRD,MHB_PDE,MHB_DLM,MHB_DELAYED,MHB_MSG_VER,MHB_OPEN_FLG,MHB_O
RIG_FL_NAME,MHB_PREV_ADDRS,MHB_NEXT_ADDRS,MHB_CRTN_DATE,MHB_SEND
1,176 1,176 1.08 3876120182
Module: JDBC Thin Client
SELECT UAI_CREATOR_ID,UAI_ENABLER_ID,UAI_MODIFIER_ID,UAI_APPROV
ER_ID,UAI_USER_EMP_NO,UAI_SMART_CARD_SL,UAI_USER_GRP_CODE,UAI_US
ER_GRP_CODE_CD,UAI_USER_NAME,UAI_USER_STAT,UAI_USER_STAT_CD,UAI_
CRTN_DATE,UAI_ROLE_ID,UAI_ENABLE_ONDT,UAI_CONNECT_STAT,UAI_CONNE
CT_STAT_CD,UAI_LAST_LOGIN_DATE,UAI_LAST_LOGINON,UAI_AGENCY,UAI_U
1,151 1,151 1.06 1495493136
Module: JDBC Thin Client
UPDATE MHB_MSG_HDR_BR SET MHB_SNDR_REF = :1,MHB_RCVR_REF = :2,MH
B_RCVR_SNDR_ADDRS = :3,MHB_AGENCY = :4,MHB_AGENCY_CD = :5,MHB_MS
G_STAT = :6,MHB_MSG_STAT_CD = :7,MHB_MSG_TYP = :8,MHB_MSG_TYP_CD
= :9,MHB_MSG_PRRTY = :10,MHB_TRNG_FLAG = :11,MHB_NON_DLV_WRNG_I
ND = :12,MHB_OBSLNC_PRD = :13,MHB_PDE = :14,MHB_DLM = :15,MHB_DE
1,067 1,067 0.98 2243488166
Module: JDBC Thin Client
select COUNT(*) from mpt_msg_proc_tbl where MPT_OWNR_ADDRS=:1 AN
D MPT_IN_OUT_IND=:2 and mpt_crt_rcv_num =:3 and MPT_MSG_SEQ=:4
and mpt_sub_elem_id=:5
925 925 0.85 1994601063
Module: JDBC Thin Client
SELECT RFM_FUNC_ID FROM RFM_ROLE_FUNC_MAP WHERE RFM_ROLE_ID =
:1 AND RFM_FUNC_ID IN (:2,:3,:4,:5,:6,:7,:8,:9,:10) AND RFM_IFSC
= :11 AND RFM_AGENCY = :12
851 851 0.78 4150459836
Module: JDBC Thin Client
SELECT MHB_OWNR_ADDRS,MHB_IN_OUT_IND,MHB_CRT_RCV_SEQ_NUM,MHB_RCV
R_REF,MHB_RCVR_SNDR_ADDRS,MHB_AGENCY,MHB_AGENCY_CD,MHB_MSG_STAT,
MHB_MSG_STAT_CD,MHB_MSG_TYP,MHB_MSG_TYP_CD,MHB_MSG_PRRTY,MHB_TRN
G_FLAG,MHB_NON_DLV_WRNG_IND,MHB_OBSLNC_PRD,MHB_PDE,MHB_DLM,MHB_D
ELAYED,MHB_MSG_VER,MHB_OPEN_FLG,MHB_ORIG_FL_NAME,MHB_PREV_ADDRS,
779 779 0.72 937435024
Module: JDBC Thin Client
SELECT 1 FROM IDI_IFSC_DIR_INFO WHERE IDI_IFSC_CODE = :1
764 764 0.70 2228227173
SQL ordered by Parse Calls for DB: SFMS Instance: sfms Snaps: 147
-148
-> End Parse Calls Threshold: 1000
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
Module: JDBC Thin Client
ALTER SESSION SET TIME_ZONE='+5:30' NLS_LANGUAGE = 'AMERICAN' NL
S_TERRITORY = 'AMERICA'
756 756 0.69 3003997262
Module: JDBC Thin Client
SELECT MTB_MSG_BLOCK1,MTB_MSG_BLOCK2,MTB_MSG_BLOCK3,MTB_MSG_BLOC
K4,MTB_MSG_BLOCK5A,MTB_MSG_BLOCK5B,MTB_MSG_BLOCK5C,MTB_MSG_TXT_B
R_VER,DM_LSTUPDDT FROM MTB_MSG_TXT_BR WHERE MTB_OWNR_ADDRS = :1
AND MTB_IN_OUT_IND = :2 AND MTB_CRT_RCV_SEQ_NUM = :3
727 727 0.67 1791910789
Module: JDBC Thin Client
SELECT UMM_MSG_GRP_ID FROM UMM_USERGRP_MSGTYPE_MAP WHERE UMM_IF
SC=:1 AND UMM_USR_GRP_ID=:2 AND UMM_AGENCY=:3
725 725 0.67 46870338
Module: JDBC Thin Client
SELECT MGD_MSG_MNEMNC FROM MGD_MSG_GROUP_DTL WHERE MGD_MSG_IFSC=
:1 AND MGD_MSG_ID=:2 ORDER BY MGD_MSG_MNEMNC
-------------------------------------------------------------
Instance Activity Stats for DB: SFMS Instance: sfms Snaps: 147 -148
Statistic Total per Second
per Trans
--------------------------------- ------------------ --------------
------------
CPU used by this session 23,390
13.8 7.3
CPU used when call started 23,389
13.8 7.3
CR blocks created 1,561
0.9 0.5
Cached Commit SCN referenced 3,339
2.0 1.0
Commit SCN cached 13
0.0 0.0
DBWR buffers scanned 3,124
1.9 1.0
DBWR checkpoint buffers written 4,698
2.8 1.5
DBWR checkpoints 0
0.0 0.0
DBWR free buffers found 3,118
1.8 1.0
DBWR lru scans 4
0.0 0.0
DBWR make free requests 4
0.0 0.0
DBWR summed scan depth 3,124
1.9 1.0
DBWR transaction table writes 10
0.0 0.0
DBWR undo block writes 1,345
0.8 0.4
SQL*Net roundtrips to/from client 1,585,249
937.5 495.2
SQL*Net roundtrips to/from dblink 0
0.0 0.0
active txn count during cleanout 3,521
2.1 1.1
background checkpoints completed 1
0.0 0.0
background checkpoints started 0
0.0 0.0
background timeouts 2,017
1.2 0.6
branch node splits 2
0.0 0.0
buffer is not pinned count 9,114,964
5,390.3 2,847.5
buffer is pinned count 3,248,575
1,921.1 1,014.9
bytes received via SQL*Net from c 79,022,665
46,731.3 24,686.9
bytes received via SQL*Net from d 0
0.0 0.0
bytes sent via SQL*Net to client 190,248,795,055 112,506,679.5
############
bytes sent via SQL*Net to dblink 0
0.0 0.0
calls to get snapshot scn: kcmgss 126,217
74.6 39.4
calls to kcmgas 8,753
5.2 2.7
calls to kcmgcs 3,984
2.4 1.2
change write time 140
0.1 0.0
cleanout - number of ktugct calls 4,140
2.5 1.3
cleanouts and rollbacks - consist 1,086
0.6 0.3
cleanouts only - consistent read 177
0.1 0.1
cluster key scan block gets 111
0.1 0.0
cluster key scans 47
0.0 0.0
commit cleanout failures: buffer 0
0.0 0.0
commit cleanout failures: callbac 1
0.0 0.0
commit cleanout failures: cannot 23
0.0 0.0
commit cleanouts 21,490
12.7 6.7
commit cleanouts successfully com 21,466
12.7 6.7
commit txn count during cleanout 3,295
2.0 1.0
consistent changes 25,286
15.0 7.9
consistent gets 9,765,234
5,774.8 3,050.7
consistent gets - examination 391,638
231.6 122.4
cursor authentications 126
0.1 0.0
data blocks consistent reads - un 25,285
15.0 7.9
db block changes 215,818
127.6 67.4
db block gets 239,679
141.7 74.9
deferred (CURRENT) block cleanout 10,447
6.2 3.3
dirty buffers inspected 10
0.0 0.0
enqueue conversions 33
0.0 0.0
enqueue releases 28,500
16.9 8.9
enqueue requests 28,501
16.9 8.9
enqueue timeouts 0
0.0 0.0
enqueue waits 35
0.0 0.0
Instance Activity Stats for DB: SFMS Instance: sfms Snaps: 147 -148
Statistic Total per Second
per Trans
--------------------------------- ------------------ --------------
------------
exchange deadlocks 2
0.0 0.0
execute count 113,736
67.3 35.5
failed probes on index block recl 0
0.0 0.0
free buffer inspected 41,539
24.6 13.0
free buffer requested 6,238,209
3,689.1 1,948.8
hot buffers moved to head of LRU 3,644
2.2 1.1
immediate (CR) block cleanout app 1,263
0.8 0.4
immediate (CURRENT) block cleanou 2,893
1.7 0.9
index crx upgrade (positioned) 2,671
1.6 0.8
index fast full scans (full) 331
0.2 0.1
index fetch by key 58,551
34.6 18.3
index scans kdiixs1 440,984
260.8 137.8
leaf node 90-10 splits 15
0.0 0.0
leaf node splits 417
0.3 0.1
logons cumulative 7,502
4.4 2.3
messages received 4,571
2.7 1.4
messages sent 4,573
2.7 1.4
no buffer to keep pinned count 0
0.0 0.0
no work - consistent read gets 9,349,161
5,528.8 2,920.7
opened cursors cumulative 96,347
57.0 30.1
parse count (failures) 1
0.0 0.0
parse count (hard) 372
0.2 0.1
parse count (total) 108,795
64.3 34.0
parse time cpu 723
0.4 0.2
parse time elapsed 766
0.5 0.2
physical reads 6,236,710
3,688.2 1,948.4
physical reads direct 3,630
2.2 1.1
physical reads direct (lob) 0
0.0 0.0
physical writes 9,436
5.6 3.0
physical writes direct 4,722
2.8 1.5
physical writes direct (lob) 0
0.0 0.0
physical writes non checkpoint 8,117
4.8 2.5
pinned buffers inspected 27,502
16.3 8.6
prefetched blocks 5,645,308
3,338.4 1,763.6
prefetched blocks aged out before 0
0.0 0.0
process last non-idle time 1,688
1.0 0.5
recursive calls 297,349
175.8 92.9
recursive cpu usage 1,224
0.7 0.4
redo blocks written 109,160
64.6 34.1
redo buffer allocation retries 0
0.0 0.0
redo entries 110,586
65.4 34.6
redo log space requests 0
0.0 0.0
redo log space wait time 0
0.0 0.0
redo size 52,923,708
31,297.3 16,533.5
redo synch time 3,163
1.9 1.0
redo synch writes 2,986
1.8 0.9
redo wastage 1,046,600
618.9 327.0
redo write time 3,038
1.8 1.0
redo writer latching time 0
0.0 0.0
redo writes 4,082
2.4 1.3
rollback changes - undo records a 130
0.1 0.0
rollbacks only - consistent read 700
0.4 0.2
rows fetched via callback 16,010
9.5 5.0
session connect time 0
0.0 0.0
session logical reads 10,004,911
5,916.6 3,125.6
session uga memory 87,168,956
51,548.8 27,231.8
Instance Activity Stats for DB: SFMS Instance: sfms Snaps: 147 -148
Statistic Total per Second
per Trans
--------------------------------- ------------------ --------------
------------
session uga memory max 1,141,299,460 674,925.8
356,544.7
shared hash latch upgrades - no w 3,735
2.2 1.2
sorts (disk) 17
0.0 0.0
sorts (memory) 86,294
51.0 27.0
sorts (rows) 1,410,123
833.9 440.5
summed dirty queue length 17
0.0 0.0
switch current to new buffer 1,147
0.7 0.4
table fetch by rowid 2,002,918
1,184.5 625.7
table fetch continued row 27,999
16.6 8.8
table scan blocks gotten 8,080,808
4,778.7 2,524.5
table scan rows gotten 15,112,161
8,936.8 4,721.1
table scans (long tables) 34
0.0 0.0
table scans (short tables) 4,948
2.9 1.6
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
user calls 850,496
503.0 265.7
user commits 2,605
1.5 0.8
user rollbacks 596
0.4 0.2
workarea executions - multipass 1
0.0 0.0
workarea executions - onepass 22
0.0 0.0
workarea executions - optimal 80,997
47.9 25.3
write clones created in foregroun 0
0.0 0.0
-------------------------------------------------------------
Tablespace IO Stats for DB: SFMS Instance: sfms Snaps: 147 -148
->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)
-------------- ------- ------ ------- ------------ -------- ----------
------
SFMS_BR_DATA
584,294 346 8.2 10.7 1,864 1
212,724 7.6
TEMPBR
3,556 2 0.6 1.0 4,665 3
0 0.0
SFMS_BR_NDX
1,559 1 12.2 1.0 1,247 1
14 2.1
UNDOTBS1
1 0 20.0 1.0 1,358 1
10 0.0
SFMS_BR_ARCH_DATA
587 0 8.3 1.0 31 0
0 0.0
SFMS_BR_ARCH_NDX
340 0 16.1 1.0 50 0
0 0.0
SYSTEM
291 0 1.7 1.0 14 0
0 0.0
PERFSTAT
27 0 8.1 1.0 195 0
0 0.0
CWMLITE
3 0 23.3 1.0 2 0
0 0.0
TOOLS
3 0 26.7 1.0 2 0
0 0.0
EXAMPLE
2 0 20.0 1.0 2 0
0 0.0
XDB
2 0 20.0 1.0 2 0
0 0.0
DRSYS
1 0 30.0 1.0 1 0
0 0.0
INDX
1 0 30.0 1.0 1 0
0 0.0
ODM
1 0 50.0 1.0 1 0
0 0.0
USERS
1 0 30.0 1.0 1 0
0 0.0
-------------------------------------------------------------
File IO Stats for DB: SFMS Instance: sfms Snaps: 147 -148
->ordered by Tablespace, File
Tablespace Filename
------------------------
----------------------------------------------------
Av Av Av Av Buffer
Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
Wt(ms)
-------------- ------- ------ ------- ------------ -------- ----------
------
CWMLITE C:\ORA9I\ORADATA\SFMS\CWMLITE01.DBF
1 0 20.0 1.0 1 0 0
C:\ORA9I\ORADATA\SFMS\CWMLITE02.DBF
2 0 25.0 1.0 1 0 0
DRSYS C:\ORA9I\ORADATA\SFMS\DRSYS01.DBF
1 0 30.0 1.0 1 0 0
EXAMPLE C:\ORA9I\ORADATA\SFMS\EXAMPLE01.DBF
1 0 30.0 1.0 1 0 0
C:\ORA9I\ORADATA\SFMS\EXAMPLE02.DBF
1 0 10.0 1.0 1 0 0
INDX C:\ORA9I\ORADATA\SFMS\INDX01.DBF
1 0 30.0 1.0 1 0 0
ODM C:\ORA9I\ORADATA\SFMS\ODM01.DBF
1 0 50.0 1.0 1 0 0
PERFSTAT C:\ORA9I\ORADATA\SFMS\PERFSFMS_1.DBF
27 0 8.1 1.0 195 0 0
SFMS_BR_ARCH_DATA E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DAT2A12.DBF
2 0 25.0 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DAT2A19.DBF
70 0 6.9 1.0 2 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DAT2A20.DBF
66 0 7.7 1.0 2 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DAT2A21.DBF
73 0 6.8 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DAT2A22.DBF
90 0 6.2 1.0 2 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DAT2A23.DBF
69 0 10.0 1.0 6 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA1.DBF
5 0 8.0 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA10.DBF
3 0 13.3 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA11.DBF
3 0 13.3 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA13.DBF
5 0 16.0 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA14.DBF
14 0 11.4 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA15.DBF
6 0 20.0 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA16.DBF
13 0 13.1 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA17.DBF
9 0 15.6 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA18.DBF
15 0 11.3 1.0 1 0 0
File IO Stats for DB: SFMS Instance: sfms Snaps: 147 -148
->ordered by Tablespace, File
Tablespace Filename
------------------------
----------------------------------------------------
Av Av Av Av Buffer
Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
Wt(ms)
-------------- ------- ------ ------- ------------ -------- ----------
------
SFMS_BR_ARCH_DATA E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA2.DBF
17 0 8.2 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA3.DBF
8 0 7.5 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA4.DBF
27 0 6.3 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA5.DBF
23 0 5.7 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA6.DBF
18 0 6.7 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA7.DBF
15 0 12.0 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA8.DBF
22 0 9.1 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_DATA9.DBF
14 0 10.7 1.0 1 0 0
SFMS_BR_ARCH_NDX E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_NDX1.DBF
27 0 11.9 1.0 3 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_NDX2.DBF
77 0 14.2 1.0 11 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_NDX3.DBF
40 0 12.0 1.0 8 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_NDX4.DBF
59 0 19.3 1.0 1 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_NDX5.DBF
110 0 17.9 1.0 5 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_NDX6.DBF
11 0 20.0 1.0 9 0 0
E:\SFMS_BRANCH\ORADATA\SFMS
\SFMS_ARCH_NDX7.DBF
16 0 15.6 1.0 13 0 0
SFMS_BR_DATA E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_DATA1.DBF
370,973 219 4.5 11.3 328 0
54,122 3.0
E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_DATA2.DBF
8,338 5 11.6 9.3 108 0
6,377 7.4
E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_DATA3.DBF
16,077 10 13.1 9.1 188 0
13,187 8.5
E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_DATA4.DBF
19,415 11 13.7 8.9 97 0
15,651 9.1
E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_DATA5.DBF
16,938 10 14.7 8.9 328 0
12,901 9.7
E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_DATA6.DBF
48,847 29 15.3 9.7 240 0
34,971 10.0
E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_DATA7.DBF
55,871 33 15.3 9.4 455 0
41,103 9.0
E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_DATA8.DBF
47,835 28 14.5 9.8 120 0
34,412 9.0
SFMS_BR_NDX E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_NDX1.DBF
260 0 23.5 1.0 270 0 0
E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_NDX2.DBF
File IO Stats for DB: SFMS Instance: sfms Snaps: 147 -148
->ordered by Tablespace, File
Tablespace Filename
------------------------
----------------------------------------------------
Av Av Av Av Buffer
Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
Wt(ms)
-------------- ------- ------ ------- ------------ -------- ----------
------
592 0 9.3 1.0 475 0 0
SFMS_BR_NDX E:\SFMS_BRANCH\ORADATA\SFMS\SFMS_NDX3.DBF
707 0 10.4 1.0 502 0
14 2.1
SYSTEM C:\ORA9I\ORADATA\SFMS\SYSTEM01.DBF
290 0 1.7 1.0 13 0 0
C:\ORA9I\ORADATA\SFMS\SYSTEM02.DBF
1 0 10.0 1.0 1 0 0
TEMPBR E:\SFMS_BRANCH\ORADATA\SFMS\TEMP01.DBF
3,556 2 0.6 1.0 4,665 3 0
TOOLS C:\ORA9I\ORADATA\SFMS\TOOLS01.DBF
1 0 30.0 1.0 1 0 0
C:\ORA9I\ORADATA\SFMS\TOOLS02.DBF
2 0 25.0 1.0 1 0 0
UNDOTBS1 C:\ORA9I\ORADATA\SFMS\UNDOTBS01.DBF
1 0 20.0 1.0 1,358 1
10 0.0
USERS C:\ORA9I\ORADATA\SFMS\USERS01.DBF
1 0 30.0 1.0 1 0 0
XDB C:\ORA9I\ORADATA\SFMS\XDB01.DBF
1 0 30.0 1.0 1 0 0
C:\ORA9I\ORADATA\SFMS\XDB02.DBF
1 0 10.0 1.0 1 0 0
-------------------------------------------------------------
Buffer Pool Statistics for DB: SFMS Instance: sfms Snaps: 147 -148
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
Free
Write Buffer
Number of Cache Buffer Physical Physical Buffer
Complete Busy
P Buffers Hit % Gets Reads Writes Waits
Waits Waits
--- ---------- ----- ----------- ----------- ---------- -------
-------- ------
D 128,128 35.9 9,719,664 6,226,456 4,714 0
0 213,349
K 1,001 99.9 253,202 136 0 0
0 0
-------------------------------------------------------------
Instance Recovery Stats for DB: SFMS Instance: sfms Snaps: 147 -148
-> 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 246 41 5467 94378 93580 184320 93580
E 246 44 6994 121754 120993 184320 120993
-------------------------------------------------------------
Buffer Pool Advisory for DB: SFMS Instance: sfms End Snap: 148
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate (default block size
first)
Size for Size Buffers for Est Physical
Estimated
P Estimate (M) Factr Estimate Read Factor Physical
Reads
--- ------------ ----- ---------------- -------------
------------------
D 104 .1 13,013 5.77
203,474,063
D 208 .2 26,026 1.75
61,741,474
D 312 .3 39,039 1.43
50,359,087
D 416 .4 52,052 1.31
46,102,313
D 520 .5 65,065 1.23
43,259,137
D 624 .6 78,078 1.17
41,159,076
D 728 .7 91,091 1.12
39,376,445
D 832 .8 104,104 1.08
37,970,986
D 936 .9 117,117 1.03
36,399,284
D 1,024 1.0 128,128 1.00
35,268,345
D 1,040 1.0 130,130 1.00
35,098,551
D 1,144 1.1 143,143 0.96
33,788,180
D 1,248 1.2 156,156 0.92
32,460,493
D 1,352 1.3 169,169 0.88
31,169,421
D 1,456 1.4 182,182 0.84
29,644,295
D 1,560 1.5 195,195 0.80
28,368,325
D 1,664 1.6 208,208 0.77
27,266,600
D 1,768 1.7 221,221 0.76
26,776,517
D 1,872 1.8 234,234 0.74
26,254,061
D 1,976 1.9 247,247 0.73
25,654,225
D 2,080 2.0 260,260 0.71
24,934,398
K 8 1.0 1,001 1.00
592,740
K 16 2.0 2,002 0.45
268,671
K 24 3.0 3,003 0.00
2,044
K 32 4.0 4,004 0.00
2,044
K 40 5.0 5,005 0.00
2,044
K 48 6.0 6,006 0.00
2,044
K 56 7.0 7,007 0.00
2,044
K 64 8.0 8,008 0.00
2,044
K 72 9.0 9,009 0.00
2,044
K 80 10.0 10,010 0.00
2,044
K 88 11.0 11,011 0.00
2,044
K 96 12.0 12,012 0.00
2,044
K 104 13.0 13,013 0.00
2,044
K 112 14.0 14,014 0.00
2,044
K 120 15.0 15,015 0.00
2,044
K 128 16.0 16,016 0.00
2,044
K 136 17.0 17,017 0.00
2,044
K 144 18.0 18,018 0.00
2,044
K 152 19.0 19,019 0.00
2,044
K 160 20.0 20,020 0.00
2,044
-------------------------------------------------------------
Buffer wait Statistics for DB: SFMS Instance: sfms Snaps: 147 -148
-> ordered by wait time desc, waits desc
Tot Wait Avg
Class Waits Time (s) Time (ms)
------------------ ----------- ---------- ---------
data block 213,389 1,629 8
undo block 9 0 0
segment header 1 0 0
undo header 1 0 0
-------------------------------------------------------------
PGA Aggr Target Stats for DB: SFMS Instance: sfms Snaps: 147 -148
-> B: Begin snap E: End snap (rows dentified 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
--------------- ---------------- -------------------------
90.0 348 39
%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 477 409 37.6 0.0 .0 .0 .0
24,422
E 477 408 36.6 0.4 1.2 .0 100.0
24,422
-------------------------------------------------------------
PGA Aggr Target Histogram for DB: SFMS Instance: sfms Snaps: 147
-148
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 75,297 75,297 0 0
4K 8K 1,436 1,436 0 0
8K 16K 2,432 2,432 0 0
16K 32K 1,738 1,738 0 0
32K 64K 34 34 0 0
64K 128K 23 1 22 0
512K 1024K 34 34 0 0
1M 2M 1 1 0 0
8M 16M 1 0 0 1
16M 32M 1 1 0 0
-------------------------------------------------------------
PGA Memory Advisory for DB: SFMS Instance: sfms End Snap: 148
-> 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
---------- ------- ---------------- ---------------- --------
----------
60 0.1 347.2 100.4 78.0
0
119 0.3 347.2 100.4 78.0
0
238 0.5 347.2 68.6 84.0
0
358 0.8 347.2 56.4 86.0
0
477 1.0 347.2 0.0 100.0
0
572 1.2 347.2 0.0 100.0
0
668 1.4 347.2 0.0 100.0
0
763 1.6 347.2 0.0 100.0
0
859 1.8 347.2 0.0 100.0
0
954 2.0 347.2 0.0 100.0
0
1,431 3.0 347.2 0.0 100.0
0
1,908 4.0 347.2 0.0 100.0
0
2,862 6.0 347.2 0.0 100.0
0
3,816 8.0 347.2 0.0 100.0
0
-------------------------------------------------------------
Enqueue activity for DB: SFMS Instance: sfms Snaps: 147 -148
-> Enqueue stats gathered prior to 9i should not be compared with 9i
data
-> ordered by Wait Time desc, Waits desc
Avg Wt
Wait
Eq Requests Succ Gets Failed Gets Waits Time (ms)
Time (s)
-- ------------ ------------ ----------- ----------- -------------
------------
TX 6,086 6,086 0 35
78.11 3
-------------------------------------------------------------
Rollback Segment Stats for DB: SFMS Instance: sfms Snaps: 147 -148
->A high value for "Pct Waits" suggests more rollback segments may be
required
->RBS stats may not be accurate between begin and end snaps when using
Auto Undo
managment, as RBS may be dynamically created and dropped as needed
Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks
Extends
------ -------------- ------- --------------- -------- --------
--------
0 8.0 0.00 0 0 0
0
1 1,264.0 0.00 1,395,834 2 1
0
2 1,406.0 0.00 1,252,618 0 0
0
3 1,547.0 0.06 1,228,208 2 1
0
4 1,778.0 0.00 1,107,874 1 0
0
5 1,528.0 0.00 1,152,998 1 1
0
6 1,210.0 0.00 1,209,726 1 1
0
7 1,533.0 0.00 1,466,962 1 1
0
8 2,053.0 0.00 2,277,682 5 0
1
9 1,818.0 0.00 1,322,968 4 2
0
10 1,543.0 0.00 1,182,126 1 1
0
-------------------------------------------------------------
Rollback Segment Storage for DB: SFMS Instance: sfms Snaps: 147
-148
->Optimal Size should be larger than Avg Active
RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 385,024 6,553 385,024
1 21,094,400 943,039 24,240,128
2 84,008,960 849,403 84,008,960
3 16,900,096 927,814 27,385,856
4 20,045,824 985,428 28,434,432
5 16,965,632 948,379 29,548,544
6 16,900,096 869,620 23,191,552
7 14,802,944 941,536 23,191,552
8 16,900,096 927,422 24,240,128
9 16,900,096 829,184 29,483,008
10 20,045,824 940,306 31,580,160
-------------------------------------------------------------
Undo Segment Summary for DB: SFMS Instance: sfms Snaps: 147 -148
-> Undo segment block stats:
-> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired
reUsed
-> eS - expired Stolen, eR - expired Released, eU - expired
reUsed
Undo Undo Num Max Qry Max Tx Snapshot Out of uS/
uR/uU/
TS# Blocks Trans Len (s) Concurcy Too Old Space eS/
eR/eU
---- -------------- ---------- -------- ---------- -------- ------
-------------
1 2,731 606,660 249 1 0 0
0/0/0/0/0/0
-------------------------------------------------------------
Undo Segment Stats for DB: SFMS Instance: sfms Snaps: 147 -148
-> ordered by Time desc
Undo Num Max Qry Max Tx Snap Out of uS/
uR/uU/
End Time Blocks Trans Len (s) Concy Too Old Space eS/
eR/eU
------------ ------------ -------- ------- -------- ------- ------
-------------
14-Nov 17:39 844 204,153 75 1 0 0
0/0/0/0/0/0
14-Nov 17:29 920 202,481 249 1 0 0
0/0/0/0/0/0
14-Nov 17:19 967 200,026 238 1 0 0
0/0/0/0/0/0
-------------------------------------------------------------
Latch Activity for DB: SFMS Instance: sfms Snaps: 147 -148
->"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
------------------------ -------------- ------ ------ ------
------------ ------
Consistent RBA 4,082 0.0
0 0
FAL request queue 29 0.0
0 0
FAL subheap alocation 14 0.0
0 0
FIB s.o chain latch 29 0.0
0 0
FOB s.o list latch 44 0.0
0 0
SQL memory manager latch 1 0.0 0
560 0.0
SQL memory manager worka 112,853 0.0 0.0
0 0
active checkpoint queue 1,054 0.0
0 0
alert log latch 5 0.0
0 0
archive control 5 0.0
0 0
archive process latch 15 0.0
0 0
cache buffer handles 224 0.0
0 0
cache buffers chains 26,609,079 1.5 0.0 11
12,157,479 0.2
cache buffers lru chain 14,374 43.5 0.0 0
13,837,006 3.1
channel handle pool latc 35 0.0
0 0
channel operations paren 1,261 0.0
0 0
checkpoint queue latch 101,076 0.0 0
6,204 0.0
child cursor hash table 2,644 0.0
0 0
dml lock allocation 25,573 0.0 0.0
0 0
dummy allocation 15,026 0.0
0 0
enqueue hash chains 57,103 0.0 0.0
0 0
enqueues 66,404 0.0 0.0
0 0
hash table column usage 23 0.0 0
19,672 0.0
job_queue_processes para 28 0.0
0 0
kmcptab latch 1,528 0.0
0 0
kmcpvec latch 0 0
1,528 0.0
ktm global data 41 0.0
0 0
kwqit: protect wakeup ti 56 0.0
0 0
lgwr LWN SCN 4,083 0.0
0 0
library cache 1,568,378 0.1 0.0
0 0
library cache load lock 2 0.0
0 0
library cache pin 982,871 0.0 0.0
0 0
library cache pin alloca 532,665 0.0 0.0
0 0
list of block allocation 3,849 0.0
0 0
loader state object free 89 1.1 0.0
0 0
longop free list parent 863 0.0 0
24 0.0
message pool operations 34 0.0
0 0
messages 17,258 0.0 0.0
0 0
mostly latch-free SCN 4,094 0.0 0.0
0 0
multiblock read objects 1,157,553 0.2 0.0
0 0
ncodef allocation latch 53 0.0
0 0
post/wait queue 4,551 0.0 0
2,990 0.0
process allocation 1 0.0
0 0
process group creation 1 0.0
0 0
redo allocation 118,810 0.1 0.0
0 0
redo copy 0 0
110,762 0.2
redo writing 14,472 0.0
0 0
row cache enqueue latch 150,482 0.0 0.0
0 0
row cache objects 174,668 0.0 0.0
0 0
sequence cache 23,259 0.0 0.0
0 0
Latch Activity for DB: SFMS Instance: sfms Snaps: 147 -148
->"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
------------------------ -------------- ------ ------ ------
------------ ------
session allocation 100,081 0.2 0.1
0 0
session idle bit 1,741,180 0.0 0.0
0 0
session switching 53 0.0
0 0
session timer 1,103 0.0
0 0
shared pool 864,522 0.3 0.0
0 0
sim partition latch 0 0
780 0.0
simulator hash latch 1,351,647 0.0 0.0
0 0
simulator lru latch 33,645 0.1 0.0 0
390,910 0.0
sort extent pool 260 1.9 0.0
0 0
spilled msgs queues list 56 0.0
0 0
transaction allocation 3,836 0.0
0 0
transaction branch alloc 53 0.0
0 0
undo global data 29,042 0.0 0.0
0 0
user lock 30,052 0.0 0.0
0 0
virtual circuit buffers 7,865,481 0.0 0.0
0 0
virtual circuit queues 3,294,049 0.0 0.0
0 0
virtual circuits 1,559,552 0.1 0.0
0 0
-------------------------------------------------------------
Latch Sleep breakdown for DB: SFMS Instance: sfms Snaps: 147 -148
-> ordered by misses desc
Get
Spin &
Latch Name Requests Misses Sleeps
Sleeps 1->4
-------------------------- -------------- ----------- -----------
------------
cache buffers chains 26,609,079 412,341 6,546
406783/4584/
960/14/0
cache buffers lru chain 14,374 6,249 17
6236/9/4/0/0
shared pool 864,522 2,889 14
2875/14/0/0/
0
multiblock read objects 1,157,553 2,724 34
2694/26/4/0/
0
virtual circuit buffers 7,865,481 2,504 8
2496/8/0/0/0
virtual circuit queues 3,294,049 1,627 3
1624/3/0/0/0
library cache 1,568,378 1,626 8
1618/8/0/0/0
session allocation 100,081 166 13
155/9/2/0/0
session idle bit 1,741,180 136 1
135/1/0/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: SFMS Instance: sfms Snaps: 147 -148
-> 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
4,638 4,937
cache buffers chains kcbrls: kslbegin 0
1,238 1,138
cache buffers chains kcbzwb 0
385 384
cache buffers chains kcbzib: finish free bufs 0
91 18
cache buffers chains kcbzgb: scan from tail. no 0
77 0
cache buffers chains kcbchg: kslbegin: bufs not 0
62 33
cache buffers chains kcbzib: multi-block read: 0
19 0
cache buffers chains kcbgcur: kslbegin 0
1 1
cache buffers lru chain kcbzgb: multiple sets nowa #######
16 17
cache buffers lru chain kcbzgm 1,918
1 0
library cache kgllkdl: child: cleanup 0
8 7
multiblock read objects kcbzib: normal mbr free 0
19 27
multiblock read objects kcbzib: mbr get 0
15 7
session allocation ksuxds: KSUSFCLC not set 0
7 8
session allocation ksursi 0
6 5
session idle bit ksupuc: set busy 0
1 0
shared pool kghfre 0
13 12
shared pool kghupr1 0
1 1
virtual circuit queues kmcadq 0
2 2
virtual circuit queues kmcfsq 0
1 1
virtual circuits kmcgms: if called with non 0
6 0
virtual circuits kmcfms 0
1 0
virtual circuits kmcmbf 0
1 0
-------------------------------------------------------------
Top 5 Logical Reads per Segment for DB: SFMS Instance: sfms Snaps:
147 -148
-> End Segment Logical Reads Threshold: 10000
Subobject Obj.
Logical
Owner Tablespace Object Name Name Type
Reads %Total
---------- ---------- -------------------- ---------- -----
------------ -------
SFMSBR SFMS_BR_DA MHB_MSG_HDR_BR TABLE
147,533,776 54.23
SFMSBR SFMS_BR_DA SMB_SYS_MSG_BR TABLE
33,152,656 12.19
SFMSBR SFMS_BR_ND SYS_C004563 INDEX
28,093,056 10.33
SFMSBR SFMS_BR_DA SCD_SYS_CODE_DEF TABLE
10,969,728 4.03
SYS SYSTEM I_SYSAUTH1 INDEX
9,161,008 3.37
-------------------------------------------------------------
Top 5 Physical Reads per Segment for DB: SFMS Instance: sfms Snaps:
147 -148
-> End Segment Physical Reads Threshold: 1000
Subobject Obj.
Physical
Owner Tablespace Object Name Name Type
Reads %Total
---------- ---------- -------------------- ---------- -----
------------ -------
SFMSBR SFMS_BR_DA SMB_SYS_MSG_BR TABLE
29,022,540 80.88
SFMSBR SFMS_BR_DA MTB_MSG_TXT_BR TABLE
2,126,186 5.93
SFMSBR SFMS_BR_DA MHB_MSG_HDR_BR TABLE
1,787,772 4.98
SFMSBR SFMS_BR_AR MTB_MSG_TXT_BR_AR TABLE
1,065,829 2.97
SFMSBR SFMS_BR_DA SMD_SYS_MSG_DTLS TABLE
851,509 2.37
-------------------------------------------------------------
Top 5 Buf. Busy Waits per Segment for DB: SFMS Instance: sfms
Snaps: 147 -148
-> End Segment Buffer Busy Waits Threshold: 100
Buffer
Subobject Obj.
Busy
Owner Tablespace Object Name Name Type
Waits %Total
---------- ---------- -------------------- ---------- -----
------------ -------
SFMSBR SFMS_BR_DA SMB_SYS_MSG_BR TABLE
513,258 71.25
SFMSBR SFMS_BR_DA MTB_MSG_TXT_BR TABLE
168,033 23.33
SFMSBR SFMS_BR_DA MHB_MSG_HDR_BR TABLE
34,850 4.84
SFMSBR SFMS_BR_DA MPT_MSG_PROC_TBL TABLE
2,851 .40
SFMSBR SFMS_BR_DA ERD_EXT_ROUT_DTLS TABLE
631 .09
-------------------------------------------------------------
Top 5 Row Lock Waits per Segment for DB: SFMS Instance: sfms Snaps:
147 -148
-> End Segment Row Lock Waits Threshold: 100
Row
Subobject Obj.
Lock
Owner Tablespace Object Name Name Type
Waits %Total
---------- ---------- -------------------- ---------- -----
------------ -------
SFMSBR SFMS_BR_DA BSI_BUSS_SEQ_INFO TABLE
324 88.28
SFMSBR SFMS_BR_ND SYS_C004264 INDEX
21 5.72
SFMSBR SFMS_BR_DA MHB_MSG_HDR_BR TABLE
10 2.72
SFMSBR SFMS_BR_DA MPT_MSG_PROC_TBL TABLE
4 1.09
SFMSBR SFMS_BR_ND SYS_C006402 INDEX
3 .82
-------------------------------------------------------------
Top 5 ITL Waits per Segment for DB: SFMS Instance: sfms Snaps: 147
-148
-> End Segment ITL Waits Threshold: 100
Subobject Obj.
ITL
Owner Tablespace Object Name Name Type
Waits %Total
---------- ---------- -------------------- ---------- -----
------------ -------
SFMSBR SFMS_BR_ND EMD_NDX INDEX
1 100.00
-------------------------------------------------------------
Dictionary Cache Stats for DB: SFMS Instance: sfms Snaps: 147 -148
->"Pct Misses" should be very low (< 2% in most cases)
->"Cache Usage" is the number of cache entries being used
->"Pct SGA" is the ratio of usage to allocated size for that cache
Get Pct Scan Pct
Mod Final
Cache Requests Miss Reqs Miss
Reqs Usage
------------------------- ------------ ------ ------- ----- --------
----------
dc_histogram_defs 9,468 0.0 0
0 856
dc_object_ids 1,737 0.0 0
0 425
dc_objects 8,156 0.0 0
0 709
dc_profiles 7,502 0.0 0
0 1
dc_rollback_segments 322 0.0 0
0 22
dc_segments 2,077 0.0 0
17 448
dc_sequences 375 0.0 0
375 3
dc_tablespace_quotas 17 0.0 0
17 4
dc_tablespaces 372 0.0 0
0 17
dc_user_grants 15,038 0.0 0
0 18
dc_usernames 15,005 0.0 0
0 14
dc_users 37,705 0.0 0
0 29
-------------------------------------------------------------
Library Cache Activity for DB: SFMS Instance: sfms Snaps: 147 -148
->"Pct Misses" should be very low
Get Pct Pin Pct
Invali-
Namespace Requests Miss Requests Miss Reloads
dations
--------------- ------------ ------ -------------- ------ ----------
--------
BODY 1 0.0 1 0.0
0 0
INDEX 280 0.0 280 0.0
0 0
SQL AREA 108,779 0.3 436,470 0.2
0 0
TABLE/PROCEDURE 9,420 0.0 17,742 0.0
0 0
TRIGGER 14,759 0.0 14,759 0.0
0 0
-------------------------------------------------------------
Shared Pool Advisory for DB: SFMS Instance: sfms End Snap: 148
-> 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
Estd
Shared Pool SP Estd Estd Estd Lib LC Time
Size for Size Lib Cache Lib Cache Cache Time Saved Estd
Lib Cache
Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem
Obj Hits
----------- ----- ---------- ------------ ------------ -------
---------------
608 .6 524 70,563 142,828 1.0
9,550,530
712 .7 578 79,211 142,828 1.0
9,550,583
816 .8 578 79,211 142,828 1.0
9,550,583
920 .9 578 79,211 142,828 1.0
9,550,583
1,024 1.0 578 79,211 142,828 1.0
9,550,583
1,128 1.1 578 79,211 142,828 1.0
9,550,583
1,232 1.2 578 79,211 142,828 1.0
9,550,583
1,336 1.3 578 79,211 142,828 1.0
9,550,583
1,440 1.4 578 79,211 142,828 1.0
9,550,583
1,544 1.5 578 79,211 142,828 1.0
9,550,583
1,648 1.6 578 79,211 142,828 1.0
9,550,583
1,752 1.7 578 79,211 142,828 1.0
9,550,583
1,856 1.8 578 79,211 142,828 1.0
9,550,583
1,960 1.9 578 79,211 142,828 1.0
9,550,583
2,064 2.0 578 79,211 142,828 1.0
9,550,583
-------------------------------------------------------------
SGA Memory Summary for DB: SFMS Instance: sfms Snaps: 147 -148
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 1,082,130,432
Fixed Size 459,524
Redo Buffers 667,648
Variable Size 1,400,897,536
----------------
sum 2,484,155,140
-------------------------------------------------------------
SGA breakdown difference for DB: SFMS Instance: sfms Snaps: 147 -148
Pool Name Begin value End
value % Diff
------ ------------------------------ ----------------
---------------- -------
java free memory 209,715,200
209,715,200 0.00
large free memory 49,547,656
48,673,596 -1.76
large session heap 9,172,600
10,046,660 9.53
shared 1M buffer 2,098,176
2,098,176 0.00
shared DML lock 960,948
960,948 0.00
shared FileOpenBlock 6,129,492
6,129,492 0.00
shared KGK heap 3,756
3,756 0.00
shared KGLS heap 3,554,148
3,554,148 0.00
shared KQR M PO 1,044,296
1,044,296 0.00
shared KQR S PO 141,396
141,396 0.00
shared KQR S SO 4,616
4,616 0.00
shared KQR X PO 17,024
17,024 0.00
shared KSXR pending messages que 841,036
841,036 0.00
shared KSXR receive buffers 1,033,000
1,033,000 0.00
shared MTTR advisory 183,592
183,592 0.00
shared PL/SQL DIANA 645,008
645,008 0.00
shared PL/SQL MPCODE 401,116
401,116 0.00
shared PLS non-lib hp 2,068
2,068 0.00
shared VIRTUAL CIRCUITS 2,400,000
2,400,000 0.00
shared db_block_hash_buckets 3,901,224
3,901,224 0.00
shared db_handles 1,008,000
1,008,000 0.00
shared dictionary cache 2,137,216
2,137,216 0.00
shared enqueue 1,565,772
1,565,772 0.00
shared errors 9,612
9,612 0.00
shared event statistics per sess 17,477,040
17,477,040 0.00
shared fixed allocation callback 1,084
1,084 0.00
shared free memory 427,215,256
419,247,324 -1.87
shared joxs heap init 4,220
4,220 0.00
shared ktlbk state objects 727,172
727,172 0.00
shared library cache 114,446,020
116,049,436 1.40
shared miscellaneous 28,475,548
27,737,908 -2.59
shared parameters 23,564
23,564 0.00
shared processes 1,400,000
1,400,000 0.00
shared sessions 3,732,720
3,732,720 0.00
shared sim memory hea 643,856
643,856 0.00
shared sql area 508,499,588
515,601,688 1.40
shared table definiti 5,064
5,120 1.11
shared transaction 1,719,916
1,719,916 0.00
shared trigger defini 5,528
5,528 0.00
shared trigger inform 1,176
1,176 0.00
shared trigger source 2,832
2,832 0.00
buffer_cache 1,082,130,432
1,082,130,432 0.00
fixed_sga 459,524
459,524 0.00
log_buffer 656,384
656,384 0.00
-------------------------------------------------------------
init.ora Parameters for DB: SFMS Instance: sfms Snaps: 147 -148
End
value
Parameter Name Begin value (if
different)
----------------------------- ---------------------------------
--------------
_log_archive_callout LOCAL_FIRST=TRUE
aq_tm_processes 1
background_dump_dest C:\oracle\admin\SFMS\bdump
compatible 9.2.0.0.0
control_files C:\ora9i\oradata\SFMS\CONTROL01.C
core_dump_dest C:\oracle\admin\SFMS\cdump
db_block_size 8192
db_cache_size 1073741824
db_domain
db_file_multiblock_read_count 16
db_keep_cache_size 8388608
db_name SFMS
dispatchers (PROTOCOL=TCP)(DISP=25) (CON=20)
fal_client SFMS
fal_server SFMSDR
fast_start_mttr_target 300
hash_join_enabled TRUE
instance_name SFMS
java_pool_size 209715200
job_queue_processes 10
large_pool_size 58720256
log_archive_dest_1 location=d:\archive_1
log_archive_dest_2 service=sfmsdr arch reopen=60 opt
log_archive_dest_state_2 ENABLE
log_archive_format %%ORACLE_SID%%T%TS%S.ARC
log_archive_start TRUE
max_dispatchers 50
max_shared_servers 50
open_cursors 9000
pga_aggregate_target 500165824
processes 1400
query_rewrite_enabled FALSE
remote_login_passwordfile EXCLUSIVE
shared_pool_size 1073741824
shared_servers 25
sort_area_size 65536
standby_archive_dest d:\archive_1
standby_file_management AUTO
star_transformation_enabled FALSE
timed_statistics TRUE
undo_management AUTO
undo_retention 10800
undo_tablespace UNDOTBS1
user_dump_dest C:\oracle\admin\SFMS\udump
-------------------------------------------------------------
End of Report
Reply all
Reply to author
Forward
0 new messages