要求分析一个Statspack report，选出那些需要优化的sql, 但以前自己没做过，同事也没有做过，只能上网求助，只要求给出一个具体思路就可以了，谢谢。

STATSPACK report for

DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
SOS1P 3429568817 SOS1P 1 9.2.0.8.0 NO webprddbsa00
1

Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 77265 05-Oct-10 09:00:03 434 11.8
End Snap: 77297 05-Oct-10 17:00:01 436 24.6
Elapsed: 479.97 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 4,096M Std Block Size: 8K
Shared Pool Size: 400M Log Buffer: 1,024K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 73,795.26 9,398.85
Logical reads: 94,275.29 12,007.27
Block changes: 434.71 55.37
Physical reads: 714.38 90.99
Physical writes: 23.67 3.01
User calls: 2,666.92 339.67
Parses: 331.30 42.20
Hard parses: 4.34 0.55
Sorts: 55.84 7.11
Logons: 0.04 0.01
Executes: 1,573.00 200.34
Transactions: 7.85

% Blocks changed per Read: 0.46 Recursive Call %: 28.96
Rollback per transaction %: 0.03 Rows per Sort: 110.68

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.98 Redo NoWait %: 100.00
Buffer Hit %: 99.25 In-memory Sort %: 100.00
Library Hit %: 99.68 Soft Parse %: 98.69
Execute to Parse %: 78.94 Latch Hit %: 99.97
Parse CPU to Parse Elapsd %: 32.09 % Non-Parse CPU: 99.25

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 86.68 97.39
% SQL with executions>1: 82.64 30.40
% Memory for SQL w/exec>1: 42.66 30.17

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 55,311 71.28
db file sequential read 1,984,280 8,106 10.45
db file scattered read 1,687,251 6,890 8.88
db file parallel write 86,430 2,752 3.55
log file sync 275,971 2,441 3.15
-------------------------------------------------------------
Wait Events for DB: SOS1P Instance: SOS1P Snaps: 77265 -77297
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read 1,984,280 0 8,106 4 8.8
db file scattered read 1,687,251 0 6,890 4 7.5
db file parallel write 86,430 0 2,752 32 0.4
log file sync 275,971 4 2,441 9 1.2
buffer busy waits 545,465 5 1,678 3 2.4
latch free 7,918 7,153 74 9 0.0
control file parallel write 9,644 0 57 6 0.0
direct path read (lob) 46,493 0 49 1 0.2
direct path write 13,477 0 47 4 0.1
SQL*Net more data to client 1,361,031 0 42 0 6.0
log file sequential read 2,072 0 34 17 0.0
async disk IO 4,068 0 33 8 0.0
direct path read 5,078 0 25 5 0.0
log file parallel write 296,429 0 16 0 1.3
control file sequential read 11,579 0 12 1 0.1
enqueue 1,120 0 11 10 0.0
SQL*Net break/reset to clien 12,040 0 8 1 0.1
direct path write (lob) 1,501 0 7 5 0.0
library cache pin 125 0 1 11 0.0
row cache lock 756 0 1 2 0.0
process startup 32 0 1 28 0.0
log file switch completion 11 0 1 73 0.0
db file parallel read 9 0 0 21 0.0
LGWR wait for redo copy 844 0 0 0 0.0
log file single write 16 0 0 5 0.0
log buffer space 1 0 0 32 0.0
buffer deadlock 42 42 0 0 0.0
undo segment extension 69 68 0 0 0.0
SQL*Net message from client 68,917,550 0 4,610,546 67 304.8
jobq slave wait 682 661 1,978 2900 0.0
SQL*Net more data from clien 48,461 0 1,948 40 0.2
SQL*Net message to client 68,917,554 0 56 0 304.8
-------------------------------------------------------------
Background Wait Events for DB: SOS1P Instance: SOS1P Snaps: 77265 -77297
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file parallel write 86,430 0 2,752 32 0.4
control file parallel write 9,644 0 57 6 0.0
log file sequential read 2,072 0 34 17 0.0
async disk IO 4,068 0 33 8 0.0
log file parallel write 296,428 0 16 0 1.3
control file sequential read 8,583 0 8 1 0.0
db file scattered read 204 0 2 10 0.0
db file sequential read 92 0 1 7 0.0
direct path read 124 0 0 2 0.0
LGWR wait for redo copy 844 0 0 0 0.0
log file single write 16 0 0 5 0.0
direct path write 124 0 0 0 0.0
rdbms ipc reply 79 0 0 0 0.0
latch free 14 0 0 0 0.0
buffer busy waits 13 0 0 0 0.0
rdbms ipc message 1,123,600 54,975 241,982 215 5.0
pmon timer 9,595 9,595 28,104 2929 0.0
smon timer 142 72 27,615 ###### 0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: SOS1P Instance: SOS1P Snaps: 77265 -77297
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
368,948,718 25,734 14,337.0 13.6 2304.95 2973.01 2469686519
SELECT /*+ ALL_ROWS */ C.COMP_CD, P.PRODUCT_TY, DP.DESCRIPTION_T
EXT, CRM2.BUNDLE_FLAG, R1.PRICE AS ALONE_RATE, R1.PRICE_GST AS A
LONE_RATE_GST, R1.RATE_CD AS ALONE_RATE_CD, R1.FREQUENCY AS ALON
E_FREQ, R2.PRICE AS BUNDLE_RATE, R2.PRICE_GST AS BUNDLE_RATE_GST
, R2.RATE_CD AS BUNDLE_RATE_CD, R2.FREQUENCY AS BUNDLE_FREQ, C.T

302,988,531 376,040 805.7 11.2 ######## 14726.99 2830909588
SELECT DISTINCT C.COMP_CD, C.PLAN_VAS_NRC, D.DESCRIPTION_TEXT, N
VL(P.PRODUCT_TY, C.PRODUCT_TY) AS PRODUCT_TY FROM COMPONENTS C,
DESCRIPTIONS D, PACKAGES P, PACKAGE_COMPONENT_MAP PCM WHERE C.DE
SCRIPTION_CD = D.DESCRIPTION_CD AND P.PACKAGE_CD(+) = PCM.PACKAG
E_CD AND C.COMP_CD = PCM.COMP_CD(+) AND C.COMP_CD IN (:B1 )

176,703,162 32,450 5,445.4 6.5 4012.73 4926.83 2467532504
SELECT /*+ ALL_ROWS */ C.COMP_CD, P.PRODUCT_TY, DP.DESCRIPTION_T
EXT, CRM2.BUNDLE_FLAG, R1.PRICE AS ALONE_RATE, R1.PRICE_GST AS A
LONE_RATE_GST, R1.RATE_CD AS ALONE_RATE_CD, R1.FREQUENCY AS ALON
E_FREQ, R2.PRICE AS BUNDLE_RATE, R2.PRICE_GST AS BUNDLE_RATE_GST
, R2.RATE_CD AS BUNDLE_RATE_CD, R2.FREQUENCY AS BUNDLE_FREQ, TC.

127,489,822 16,180 7,879.5 4.7 2664.50 3229.24 1232749374
SELECT /*+ ALL_ROWS */ C.COMP_CD, P.PRODUCT_TY, DP.DESCRIPTION_T
EXT, CRM2.BUNDLE_FLAG, R1.PRICE AS ALONE_RATE, R1.PRICE_GST AS A
LONE_RATE_GST, R1.RATE_CD AS ALONE_RATE_CD, R1.FREQUENCY AS ALON
E_FREQ, R2.PRICE AS BUNDLE_RATE, R2.PRICE_GST AS BUNDLE_RATE_GST
, R2.RATE_CD AS BUNDLE_RATE_CD, R2.FREQUENCY AS BUNDLE_FREQ, C.T

103,978,799 81,737 1,272.1 3.8 208.40 637.76 2549221931
SELECT /*+ FIRST_ROWS */ C.COMP_CD, C.PRODUCT_TY, CD.DESCRIPTION
_TEXT, CRM2.BUNDLE_FLAG, R1.PRICE AS ALONE_RATE, R1.PRICE_GST AS
ALONE_RATE_GST, R1.RATE_CD AS ALONE_RATE_CD, R1.FREQUENCY AS AL
ONE_FREQ, R2.PRICE AS BUNDLE_RATE, R2.PRICE_GST AS BUNDLE_RATE_G
ST, R2.RATE_CD AS BUNDLE_RATE_CD, R2.FREQUENCY AS BUNDLE_FREQ, T

84,047,191 30,298 2,774.0 3.1 8606.62 10161.96 4129230265
begin GET_PROD_BUNDLE_MATCH(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10); end
;

67,440,657 29,079 2,319.2 2.5 1967.54 2543.01 3445872178
SELECT CNP2.COMP_CD, CNP2.COMP_CD AS PACKAGE_CD, CNP2.VAS_TYPE,
D.DESCRIPTION_TEXT, CNP2.GRP_NAME, CNP2.GRP_LABEL, CNP2.NW_PRDT_
CD, CNP2.PARA_CD, CNP2.PARA_NM, CNP2.GUI_TY, CNP2.GUI_LABEL, CNP
2.GUI_GRP, CNP2.VALIDATION_TY, CNP2.CHARACTER_COUNT, CNP2.WEB_MA
NDATORY, CNP2.WEB_DEFAULT_VAL, CNP2.WEB_DISPLAY_IND, CNP2.WEB_SE

62,452,413 20,042 3,116.1 2.3 430.02 666.40 3593108039
SELECT /*+ FIRST_ROWS */ C.COMP_CD, P.PACKAGE_CD, P.PRODUCT_TY,
DP.DESCRIPTION_TEXT, R1.PRICE AS ALONE_RATE, R1.PRICE_GST AS ALO
NE_RATE_GST, R1.RATE_CD AS ALONE_RATE_CD, R1.FREQUENCY AS ALONE_
SQL ordered by Gets for DB: SOS1P Instance: SOS1P Snaps: 77265 -77297
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
FREQ, R2.BUNDLE_RATE, R2.BUNDLE_RATE_GST, R2.BUNDLE_RATE_CD, R2.
BUNDLE_FREQ, CRM1.BUNDLE_FLAG, C.TERMS_CONDITIONS_CD, C.BUSINESS

61,982,403 4,759 13,024.2 2.3 384.05 498.30 3727778867
SELECT /*+ ALL_ROWS */ C.COMP_CD, P.PRODUCT_TY, DP.DESCRIPTION_T
EXT, CRM2.BUNDLE_FLAG, R1.PRICE AS ALONE_RATE, R1.PRICE_GST AS A
LONE_RATE_GST, R1.RATE_CD AS ALONE_RATE_CD, R1.FREQUENCY AS ALON
E_FREQ, R2.PRICE AS BUNDLE_RATE, R2.PRICE_GST AS BUNDLE_RATE_GST
, R2.RATE_CD AS BUNDLE_RATE_CD, R2.FREQUENCY AS BUNDLE_FREQ, TC.

60,482,620 32,968 1,834.6 2.2 2880.25 3692.06 3252090561
SELECT C.COMP_CD, P.PACKAGE_CD, P.PRODUCT_TY, DP.DESCRIPTION_TEX
T, R1.PRICE AS ALONE_RATE, R1.PRICE_GST AS ALONE_RATE_GST, R1.RA
TE_CD AS ALONE_RATE_CD, R1.FREQUENCY AS ALONE_FREQ, R2.BUNDLE_RA
TE, R2.BUNDLE_RATE_GST, R2.BUNDLE_RATE_CD, R2.BUNDLE_FREQ, CRM1.
BUNDLE_FLAG, C.TERMS_CONDITIONS_CD, C.BUSINESS_RULE, C.CONTRACT_

49,729,192 2,276 21,849.4 1.8 179.99 228.61 1419840349
SELECT B.NW_PRDT_CD,A.* FROM NW_PRDT_PARA_VAL_CS A, NW_PRDT_PARA
_MAP B WHERE A.PARA_CD IN ( SELECT PARA_CD FROM NW_PRDT_PARA_MAP
A WHERE NW_PRDT_CD IN ( SELECT NW_PRDT_CD FROM COMP_NW_PRDT_MAP
WHERE COMP_CD IN ( SELECT COMP_CD FROM COMPONENTS WHERE COMP_CD
= :B2 ) ) AND (END_DT > SYSDATE OR END_DT IS NULL) ) AND A.PARA

48,834,750 2,235 21,850.0 1.8 185.89 236.53 2946669280
SELECT B.NW_PRDT_CD,A.* FROM NW_PRDT_PARA_VAL_CS A, NW_PRDT_PARA
_MAP B WHERE A.PARA_CD IN ( SELECT PARA_CD FROM NW_PRDT_PARA_MAP
A WHERE NW_PRDT_CD IN ( SELECT NW_PRDT_CD FROM COMP_NW_PRDT_MAP
WHERE COMP_CD IN ( SELECT COMP_CD FROM COMPONENTS WHERE COMP_CD
= :B1 ) ) AND (END_DT > SYSDATE OR END_DT IS NULL) ) AND A.PARA

45,622,815 17,549 2,599.7 1.7 1507.83 1911.55 1236849552
SELECT CNP2.COMP_CD, CNP2.COMP_CD AS PACKAGE_CD, CNP2.VAS_TYPE,
D.DESCRIPTION_TEXT, CNP2.INVISIBLE, CNP2.GRP_NAME, CNP2.GRP_LABE
L, CNP2.NW_PRDT_CD, CNP2.PARA_CD, CNP2.PARA_NM, CNP2.GUI_TY, CNP
2.GUI_LABEL, CNP2.GUI_GRP, CNP2.VALIDATION_TY, CNP2.CHARACTER_CO
UNT, CNP2.WEB_MANDATORY, CNP2.WEB_DEFAULT_VAL, CNP2.WEB_DISPLAY_

31,749,408 110,240 288.0 1.2 49.03 264.54 2222750186
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,

input_1.CODE_ID as CODE_VALUE_1,

-------------------------------------------------------------
SQL ordered by Reads for DB: SOS1P Instance: SOS1P Snaps: 77265 -77297
-> End Disk Reads Threshold: 1000

CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
224,093 1 224,093.0 1.1 23.54 109.70 781283627
SELECT STATUS_CHECK.order_no,STATUS_CHECK.prod_ty,status_check.s
ervice_instance,status_check.service_status,status_check.first_n
ame,STATUS_CHECK.last_name,STATUS_CHECK.business_name,STATUS_CHE
CK.channel_ty,status_check.last_status_update_dt,status_check.up
date_reason,ORDER_INFO.created_dt, SOS_USER.USER_ID,SOS_USER.COM

112,059 1 112,059.0 0.5 12.02 39.69 864214346
SELECT STATUS_CHECK.order_no,STATUS_CHECK.prod_ty, status_check.
service_instance,status_check.service_status, status_check.first
_name,STATUS_CHECK.last_name,STATUS_CHECK.business_name, STATUS_
CHECK.channel_ty,status_check.last_status_update_dt,status_check
.update_reason, ORDER_INFO.created_dt, SOS_USER.USER_ID,SOS_USE

69,612 1 69,612.0 0.3 1.36 29.81 246890633
Module: T.O.A.D.
---==== if order failed, check for new error message =====-----
SELECT * FROM LOG_BATCH WHERE order_no IN(13389473,13388015) O
RDER BY order_no DESC

29,034 4,900 5.9 0.1 636.64 1025.89 310521905
SELECT DISTINCT C.ORDER_NO FROM CONSENT C, ORDER_INFO OI WHERE (
1 = 1) AND C.ORDER_NO = OI.ORDER_NO AND (C.CUST_ID IS NULL OR C.
ACCT_ID IS NULL OR UPPER(C.ACCT_ID) = 'NEW' ) AND OI.ORDER_NO =
:B1

26,993 1 26,993.0 0.1 1.49 13.69 3391332519
Module: SQL*Plus
select distinct 'D,'||oi.order_no||','||to_char (oi.created_dt,
'YYYY-MM-DD HH24:MI:SS')||','||oi.sales_rep_id||','||oi.dealer_c
d||','||ci.title||','||ci.first_nm||','||ci.last_nm||','||pmi.po
rt_in_msn||','||pmi.plan_contract||','||mc_ot.decode||','||woi.w
ork_order_no||','||woi.product_ty||','||woi.flow_through_status|

13,682 7,542 1.8 0.1 3.60 172.39 1910739674
INSERT INTO EXISTING_SERVICE_INSTANCE (ORDER_NO,BILL_ACCT_NO,EXT
ERNAL_ID,EXTERNAL_ID_TY,SERVICE_ADDRESS_ID,SERVICE_STATUS,LAST_U
PDATE_ID,LAST_UPDATE_DT,SVC_INST_ID) values (:1,:2,:3,:4,:5,:6,:
7,sysdate,:8)

12,759 8,277 1.5 0.1 6.44 156.62 1108249486
INSERT INTO POSTPAID_MOB_INSTALLATION (ORDER_NO,TXN_NO,SIM_NO,SI
M_TYPE,MSN, PORT_IN_MSN,ACCOUNT_NO,PLAN_CONTRACT,ACCOUNT_PIN,IME
I, DELIVERY_ADDR_INST_ID,TPI_NAME,DELIVERY_NOTES,TPA_FIRST_NAME,
TPA_LAST_NAME, TPA_DOB,VERBAL_RECORDING_ID,LAST_UPDATE_ID,LAST_U
PDATE_DATE, EARLY_RECONTRACT ,NUMBER_TYPE, MSN_LIST, SAP_DELIVER

12,327 20,406 0.6 0.1 3.54 151.35 2605708369
SELECT STATUS_CHECK.order_no,STATUS_CHECK.prod_ty, status_check.
service_instance,status_check.service_status, status_check.first
_name,STATUS_CHECK.last_name,STATUS_CHECK.business_name, STATUS_
CHECK.channel_ty,status_check.last_status_update_dt,status_check
.update_reason, ORDER_INFO.created_dt, SOS_USER.USER_ID,SOS_USE

SQL ordered by Reads for DB: SOS1P Instance: SOS1P Snaps: 77265 -77297
-> End Disk Reads Threshold: 1000

CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
12,086 7,911 1.5 0.1 7.05 153.84 4138076916
BEGIN INSERT_PS_MOB_INSTALLATION(: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)
; END;

11,287 38,100 0.3 0.1 53.81 362.94 1374247114
SELECT DELPHI_FEED_ID, EQUIP_TY, SOS_PLAN_COMP_CD, ARBOR_PLAN_CO
MP_CD, SOS_PLAN_COMP_DESC, SOS_PLAN_CONTRACT, EQUIP_MAKE, EQUIP_
MODEL, EQUIP_BILL_DESC, EQUIP_CONTRACT, PRICE_INSTALL, PRICE_INS
TALL_GST, PRICE_TOTAL, PRICE_TOTAL_GST, ARBOR_EQUIP_COMP_CD, EQU
IP_SAP_CD, HANDSET_DESC, HANDSET_CONTRACT, SOS_EQUIP_COMP_CD, IM

9,899 32 309.3 0.0 18.44 93.61 4043595143
INSERT INTO STATS$SQL_SUMMARY ( SNAP_ID , DBID , INSTANCE_NUMBER
, TEXT_SUBSET , SHARABLE_MEM , SORTS , MODULE , LOADED_VERSIONS
, FETCHES , EXECUTIONS , LOADS , INVALIDATIONS , PARSE_CALLS ,
DISK_READS , BUFFER_GETS , ROWS_PROCESSED , COMMAND_TYPE , ADDRE
SS , HASH_VALUE , VERSION_COUNT , CPU_TIME , ELAPSED_TIME , OUTL

9,244 2,117 4.4 0.0 299.51 441.73 2029660170
begin manage_order_list(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12);
end;

8,969 32 280.3 0.0 4.51 9.62 536839608
SELECT NVL(MAX(BEGIN_TIME), TO_DATE('01011900','DDMMYYYY')) FROM
STATS$UNDOSTAT WHERE DBID = :B2 AND INSTANCE_NUMBER = :B1

8,571 7,424 1.2 0.0 2.78 105.51 1322245302
INSERT INTO EXISTING_CUST_ACCT_INFO (ORDER_NO,CUST_ACCT_NO,CUST_
FIRST_NM,CUST_LAST_NM,DOB,IDENTIFICATION_TYPE,IDENTIFICATION_VAL
UE,ACN_ABRN,PRODUCT_TY,LAST_UPDATE_ID,LAST_UPDATE_DT,CUST_MIDDLE
_NM,TITLE,ACCT_INFO) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,sysd
ate,:11,:12,:13)

7,676 4,949 1.6 0.0 3.88 93.64 2041654271
INSERT INTO PREPAID_MOB_INSTALLATION (ORDER_NO,TXN_NO,SIM_NO,SIM
_TYPE,MSN, PORT_IN_MSN,ACCOUNT_NO,PLAN_CONTRACT,ACCOUNT_PIN,IMEI
, DELIVERY_ADDR_INST_ID,TPI_NAME,DELIVERY_NOTES,TPA_FIRST_NAME,T
PA_LAST_NAME, TPA_DOB,VERBAL_RECORDING_ID, SIM_BRAND,PACK_TYPE,V
OUCHER_NO,VOUCHER_AMT,LAST_UPDATE_ID,LAST_UPDATE_DATE ,NUMBER_TY

-------------------------------------------------------------
SQL ordered by Executions for DB: SOS1P Instance: SOS1P Snaps: 77265 -77297
-> End Executions Threshold: 100

CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
9,358,268 9,355,748 1.0 0.00 0.00 3244213674
select description_text from descriptions where description_cd=8
090

8,706,784 8,704,695 1.0 0.00 0.00 1444007557
select description_text from descriptions where description_cd=8
092

7,887,259 7,886,246 1.0 0.00 0.00 317853294
select count(*) from dual

1,658,545 1,658,214 1.0 0.00 0.00 853199580
begin get_set_function_result(:1,:2,:3); end;

1,658,268 1,658,110 1.0 0.00 0.00 610626927
SELECT NO_OF_PARA FROM SET_FUNCTION_MASTER WHERE FUNCTION_CD = :
B1

901,482 59,493,490 66.0 0.00 0.00 2656735165
select * from SOS_FRONT_END_RULES

703,724 703,723 1.0 0.00 0.00 1390502142
Insert into NW_PRDT_PARA_TRANS(TXN_COMP_MAP_ID,NW_PRDT_CD,PARA_C
D,PARA_VALUE,LAST_UPDATE_ID,LAST_UPDATE_DT) VALUES(:1,:2,:3,:4,:
5,SYSDATE)

378,287 378,127 1.0 0.00 0.00 4102711756
begin GET_NW_PRDT_PARA (:1,:2,:3); end;

378,271 821,888 2.2 0.00 0.00 1007995385
SELECT NPPM.PARA_NM, NPPT.PARA_VALUE FROM NW_PRDT_PARA_TRANS NPP
T, (SELECT * FROM NW_PRDT_PARA_MAP WHERE FLOW_THROUGH='1') NPPM
WHERE NPPM.PARA_CD = NPPT.PARA_CD AND NPPT.TXN_COMP_MAP_ID = :B2
AND NPPT.NW_PRDT_CD = :B1

376,040 397,653 1.1 0.03 0.04 2830909588
SELECT DISTINCT C.COMP_CD, C.PLAN_VAS_NRC, D.DESCRIPTION_TEXT, N
VL(P.PRODUCT_TY, C.PRODUCT_TY) AS PRODUCT_TY FROM COMPONENTS C,
DESCRIPTIONS D, PACKAGES P, PACKAGE_COMPONENT_MAP PCM WHERE C.DE
SCRIPTION_CD = D.DESCRIPTION_CD AND P.PACKAGE_CD(+) = PCM.PACKAG
E_CD AND C.COMP_CD = PCM.COMP_CD(+) AND C.COMP_CD IN (:B1 )

376,018 375,999 1.0 0.00 0.00 3874482373
begin GET_COMP_DETAILS(:1,:2); end;

373,365 1,177,103 3.2 0.00 0.00 1690460927
SELECT CD FROM MASTER_CD WHERE KEY_TY = :1

297,801 297,787 1.0 0.00 0.00 449024146
insert into NW_PRDT_TRANS(TXN_COMP_MAP_ID,NW_PRDT_CD,LAST_UPDATE
_ID,LAST_UPDATE_DT,NW_INST_START_DT, NW_ACTION_TY, NW_INST_ID) v
alues(:1,:2,:3,SYSDATE,:4,:5,:6)

291,331 256,845 0.9 0.00 0.00 2398074630
SQL ordered by Executions for DB: SOS1P Instance: SOS1P Snaps: 77265 -77297
-> End Executions Threshold: 100

CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
SELECT D.DESCRIPTION_TEXT FROM COMPONENTS C, DESCRIPTIONS D WHER
E C.DESCRIPTION_CD = D.DESCRIPTION_CD AND C.COMP_CD = :1

282,946 282,945 1.0 0.00 0.00 707384314
SELECT PARA_NM FROM NW_PRDT_PARA_MAP WHERE PARA_CD = :1

255,024 255,023 1.0 0.00 0.00 1805284188
begin GET_COMP_BY_SCN_TY(:1,:2,:3);end;

227,055 227,055 1.0 0.00 0.00 3716207873
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,
order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:
1

217,189 216,193 1.0 0.00 0.00 2680258006
SELECT PARA_CD FROM NW_PRDT_PARA_MAP WHERE NW_PRDT_CD = :1 AND P
ARA_NM = :2

187,016 1,496,144 8.0 0.00 0.00 1675225820
SELECT * FROM EXTERNAL_SYSTEM_STATUS

187,005 5,049,016 27.0 0.00 0.00 2962400197
SELECT * FROM BCP_EXCEPTION_LIST WHERE IS_VALID = 'Y'

187,001 373,982 2.0 0.00 0.00 4079928138
SELECT cd, decode FROM MASTER_CD WHERE key_ty = 'BCP_BANNER_MESS
AGE'

147,404 173,799 1.2 0.00 0.00 651027796
SELECT CTM.COMP_CD, CTM.ACTION_TY, D.DESCRIPTION_TEXT, R.PRICE,
R.PRICE_GST, R.FREQUENCY, C.PLAN_VAS_NRC FROM SCENARIO_COMP_MAP
CTM, COMPONENTS C, DESCRIPTIONS D, COMPONENT_RATE_MAP CR, RATES
R WHERE CTM.SCENARIO_TY = :B3 AND CTM.SCN_COMP_TYPE = :B2 AND CT
M.COMP_CD = C.COMP_CD AND C.DESCRIPTION_CD = D.DESCRIPTION_CD AN

140,864 704,320 5.0 0.00 0.00 2170281814
SELECT C.COMP_CD, C.CONTRACT_PERIOD, D.DESCRIPTION_TEXT FROM COM
PONENTS C, DESCRIPTIONS D WHERE C.DESCRIPTION_CD = D.DESCRIPTION
_CD AND C.PLAN_VAS_NRC = 'C'

140,239 140,236 1.0 0.00 0.00 735563736
begin GET_NW_PRDT_LIST (:1,:2); end;

140,238 133,547 1.0 0.00 0.00 1585511926
SELECT * FROM COMP_NW_PRDT_MAP WHERE (END_DT IS NULL OR END_DT >
SYSDATE) AND COMP_CD = :B1

135,343 175,661 1.3 0.00 0.00 1001512577
SELECT * FROM NW_PRDT_PARA_MAP WHERE (END_DT IS NULL OR END_DT >
SYSDATE) AND NW_PRDT_CD = :B1

135,339 135,338 1.0 0.00 0.00 1018231328
begin GET_NW_PRDT_PARA_LIST (:1,:2); end;

SQL ordered by Executions for DB: SOS1P Instance: SOS1P Snaps: 77265 -77297
-> End Executions Threshold: 100

CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
131,893 131,881 1.0 0.00 0.00 686200384
begin CHECK_NVAS(:1,:2); end;

131,892 131,892 1.0 0.00 0.00 2171503400
SELECT COUNT(1) FROM COMP_NW_PRDT_MAP WHERE PURE_NW_PRDT = 'Y' A
ND COMP_CD = :B1

117,797 78 0.0 0.00 0.00 1922634261
SELECT DECODE FROM MASTER_CD WHERE KEY_TY = :1 AND SUPP_CD = '1'

117,014 117,040 1.0 0.00 0.00 828457927
SELECT NW_PRDT_TRANS.NW_PRDT_CD, NW_PRDT_TRANS.NW_INST_ID, NW_PR
DT_TRANS.NW_INST_START_DT, NW_PRDT_TRANS.NW_INST_END_DT FROM TXN
_COMP_MAP, NW_PRDT_TRANS, COMP_NW_PRDT_MAP WHERE COMP_NW_PRDT_MA
P.NW_PRDT_CD = NW_PRDT_TRANS.NW_PRDT_CD AND TXN_COMP_MAP.TXN_COM
P_MAP_ID = NW_PRDT_TRANS.TXN_COMP_MAP_ID AND TXN_COMP_MAP.COMP_C

-------------------------------------------------------------
SQL ordered by Parse Calls for DB: SOS1P Instance: SOS1P Snaps: 77265 -77297
-> End Parse Calls Threshold: 1000

% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
378,671 378,271 3.97 1007995385
SELECT NPPM.PARA_NM, NPPT.PARA_VALUE FROM NW_PRDT_PARA_TRANS NPP
T, (SELECT * FROM NW_PRDT_PARA_MAP WHERE FLOW_THROUGH='1') NPPM
WHERE NPPM.PARA_CD = NPPT.PARA_CD AND NPPT.TXN_COMP_MAP_ID = :B2
AND NPPT.NW_PRDT_CD = :B1

376,091 376,040 3.94 2830909588
SELECT DISTINCT C.COMP_CD, C.PLAN_VAS_NRC, D.DESCRIPTION_TEXT, N
VL(P.PRODUCT_TY, C.PRODUCT_TY) AS PRODUCT_TY FROM COMPONENTS C,
DESCRIPTIONS D, PACKAGES P, PACKAGE_COMPONENT_MAP PCM WHERE C.DE
SCRIPTION_CD = D.DESCRIPTION_CD AND P.PACKAGE_CD(+) = PCM.PACKAG
E_CD AND C.COMP_CD = PCM.COMP_CD(+) AND C.COMP_CD IN (:B1 )

227,051 227,055 2.38 3716207873
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,
order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:
1

186,998 187,001 1.96 4079928138
SELECT cd, decode FROM MASTER_CD WHERE key_ty = 'BCP_BANNER_MESS
AGE'

147,406 147,404 1.54 651027796
SELECT CTM.COMP_CD, CTM.ACTION_TY, D.DESCRIPTION_TEXT, R.PRICE,
R.PRICE_GST, R.FREQUENCY, C.PLAN_VAS_NRC FROM SCENARIO_COMP_MAP
CTM, COMPONENTS C, DESCRIPTIONS D, COMPONENT_RATE_MAP CR, RATES
R WHERE CTM.SCENARIO_TY = :B3 AND CTM.SCN_COMP_TYPE = :B2 AND CT
M.COMP_CD = C.COMP_CD AND C.DESCRIPTION_CD = D.DESCRIPTION_CD AN

143,736 217,189 1.51 2680258006
SELECT PARA_CD FROM NW_PRDT_PARA_MAP WHERE NW_PRDT_CD = :1 AND P
ARA_NM = :2

140,241 140,238 1.47 1585511926
SELECT * FROM COMP_NW_PRDT_MAP WHERE (END_DT IS NULL OR END_DT >
SYSDATE) AND COMP_CD = :B1

135,343 135,343 1.42 1001512577
SELECT * FROM NW_PRDT_PARA_MAP WHERE (END_DT IS NULL OR END_DT >
SYSDATE) AND NW_PRDT_CD = :B1

117,093 117,014 1.23 828457927
SELECT NW_PRDT_TRANS.NW_PRDT_CD, NW_PRDT_TRANS.NW_INST_ID, NW_PR
DT_TRANS.NW_INST_START_DT, NW_PRDT_TRANS.NW_INST_END_DT FROM TXN
_COMP_MAP, NW_PRDT_TRANS, COMP_NW_PRDT_MAP WHERE COMP_NW_PRDT_MA
P.NW_PRDT_CD = NW_PRDT_TRANS.NW_PRDT_CD AND TXN_COMP_MAP.TXN_COM
P_MAP_ID = NW_PRDT_TRANS.TXN_COMP_MAP_ID AND TXN_COMP_MAP.COMP_C

111,043 111,016 1.16 513532942
begin GET_NW_INST_PRDT (:1, :2, :3); end;

110,712 110,714 1.16 1076266187
SELECT cd, decode FROM MASTER_CD WHERE key_ty = 'CUST_IDENTIFICA
TION'
SQL ordered by Parse Calls for DB: SOS1P Instance: SOS1P Snaps: 77265 -77297
-> End Parse Calls Threshold: 1000

% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------

110,240 110,240 1.16 2222750186
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,

input_1.CODE_ID as CODE_VALUE_1,

input_2.CODE_ID as CODE_VALUE_2,

om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,

110,239 110,236 1.16 735294811
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,

input_1.CODE_ID as CODE_VALUE_1,

input_2.CODE_ID as CODE_VALUE_2,

om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,

110,238 110,235 1.16 2436847568
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,

input_1.CODE_ID as CODE_VALUE_1,

input_2.CODE_ID as CODE_VALUE_2,

om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,

110,237 110,238 1.16 407518252
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,

input_1.CODE_ID as CODE_VALUE_1,

input_2.CODE_ID as CODE_VALUE_2,

om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,

110,236 110,235 1.16 2371500597
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,

input_1.CODE_ID as CODE_VALUE_1,

input_2.CODE_ID as CODE_VALUE_2,

om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,

110,235 110,232 1.16 778384724
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,

input_1.CODE_ID as CODE_VALUE_1,

input_2.CODE_ID as CODE_VALUE_2,

om.SET_RESULT
from set_in
put_map input_1,
set_input_map input_2,

110,234 110,236 1.16 3027725634
select /*generated by GET_SET_FUNCTION_RESULT*/ om.FUNCTION_CD,

-------------------------------------------------------------
SQL ordered by Sharable Memory for DB: SOS1P Instance: SOS1P Snaps: 77265 -772
-> End Sharable Memory Threshold: 1048576

Sharable Mem (b) Executions % Total Hash Value
---------------- ------------ ------- ------------
2,282,898 2,382 0.3 3942485640
SELECT DISTINCT OI.ORDER_NO ,OI.CREATED_BY ,OI.CREATED_DT ,OI.DE
ALER_CD ,OI.DELIVERY_TIME ,OI.EXTRACTED_DT ,NVL(OI.MARKET_SEGMEN
T,SQ.MARKET_SEGMENT) MARKET_SEGMENT ,OI.CREATED_DT ,OI.PC_CD ,OI
.SALES_REP_ID ,OI.SQ_NO ,OI.ORDER_STATUS ,OI.CAMPAIGN_CD ,SU.CHA
NNEL_TY ,SU.COMPANY_NAME ,OI.FLOW_THROUGH_IND ,OI.SUB_ORDER_TY ,

1,476,871 1 0.2 2049339139
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
.cust_last_nm) last_nm, nvl(ci.DOB,ei.DOB) DOB,nvl(a1.sub_addr_t

1,462,791 1 0.2 4164117355
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
.cust_last_nm) last_nm, nvl(ci.DOB,ei.DOB) DOB,nvl(a1.sub_addr_t

1,458,900 1 0.2 3803692113
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
.cust_last_nm) last_nm, nvl(ci.DOB,ei.DOB) DOB,nvl(a1.sub_addr_t

1,453,335 2 0.2 747248947
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
.cust_last_nm) last_nm, nvl(ci.DOB,ei.DOB) DOB,nvl(a1.sub_addr_t

1,446,259 1 0.2 3367336352
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
.cust_last_nm) last_nm, nvl(ci.DOB,ei.DOB) DOB,nvl(a1.sub_addr_t

1,439,879 1 0.2 1876364937
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
.cust_last_nm) last_nm, nvl(ci.DOB,ei.DOB) DOB,nvl(a1.sub_addr_t

1,438,351 1 0.2 2528738609
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
.cust_last_nm) last_nm, nvl(ci.DOB,ei.DOB) DOB,nvl(a1.sub_addr_t
SQL ordered by Sharable Memory for DB: SOS1P Instance: SOS1P Snaps: 77265 -772
-> End Sharable Memory Threshold: 1048576

Sharable Mem (b) Executions % Total Hash Value
---------------- ------------ ------- ------------

1,434,231 1 0.2 553674281
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
.cust_last_nm) last_nm, nvl(ci.DOB,ei.DOB) DOB,nvl(a1.sub_addr_t

1,433,649 1 0.2 3961989013
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
.cust_last_nm) last_nm, nvl(ci.DOB,ei.DOB) DOB,nvl(a1.sub_addr_t

1,431,089 2 0.2 3130343000
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
.cust_last_nm) last_nm, nvl(ci.DOB,ei.DOB) DOB,nvl(a1.sub_addr_t

1,429,983 1 0.2 2094346914
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei
.cust_last_nm) last_nm, nvl(ci.DOB,ei.DOB) DOB,nvl(a1.sub_addr_t

1,421,127 1 0.2 4148267650
select /* Generated by SP MANAGE_ORDER_LIST */oi.order_no, oi.or
der_status, oi.created_dt, oi.dsl_delivery_addr_inst,nvl(ci.titl
e, ei.title) title,nvl(ci.first_nm, ei.cust_first_nm) first_nm,
nvl(ci.middle_nm, ei.cust_middle_nm) middle_nm,nvl(ci.last_nm,ei

-------------------------------------------------------------
SQL ordered by Version Count for DB: SOS1P Instance: SOS1P Snaps: 77265 -77297
-> End Version Count Threshold: 20

Version
Count Executions Hash Value
-------- ------------ ------------
45 14,865 3394715647
INSERT INTO ADDRESS (ADDR_INST_ID,SQ_ID, ADDR_TYPE, UNIT_NO, FLO
OR_NO, STREET_NO, STREET_ADDR_1, STREET_ADDR_2, STREET_SUFFIX, S
TREET_TYPE, SUBURB, STATE, POSTCODE,LAST_UPDATE_ID,SUB_ADDR_TYPE
, LOCALITY_CD, BOX_NO, BOX_TY, CUST_COUNTRY_CD, CUST_ADDR_INFO,
DPID, BUILDING_NM,LAST_UPDATE_DATE ,STREET_NUMBER_SUFFIX ) VALUE

32 8,277 1108249486
INSERT INTO POSTPAID_MOB_INSTALLATION (ORDER_NO,TXN_NO,SIM_NO,SI
M_TYPE,MSN, PORT_IN_MSN,ACCOUNT_NO,PLAN_CONTRACT,ACCOUNT_PIN,IME
I, DELIVERY_ADDR_INST_ID,TPI_NAME,DELIVERY_NOTES,TPA_FIRST_NAME,
TPA_LAST_NAME, TPA_DOB,VERBAL_RECORDING_ID,LAST_UPDATE_ID,LAST_U
PDATE_DATE, EARLY_RECONTRACT ,NUMBER_TYPE, MSN_LIST, SAP_DELIVER

29 1,935 2032664006
INSERT INTO MULTI_DELIVERY_CONTACT_DETAILS (ORDER_NO, TXN_NO, PR
ODUCT_TY, SALUTATION, FIRST_NAME, LAST_NAME, LAST_UPDATE_DATE, P
RIMARY_VERIFICATION, SECONDARY_VERIFICATION, PRIMARY_VERIFICATIO
N_DESC, SECONDARY_VERIFICATION_DESC ) VALUES ( :B10 , :B9 , :B8
, :B7 , :B6 , :B5 , SYSDATE, :B4 , :B3 , :B2 , :B1 )

-------------------------------------------------------------
Instance Activity Stats for DB: SOS1P Instance: SOS1P Snaps: 77265 -77297

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 5,531,053 192.1 24.5
CPU used when call started 5,518,243 191.6 24.4
CR blocks created 228,175 7.9 1.0
Cached Commit SCN referenced 0 0.0 0.0
Commit SCN cached 0 0.0 0.0
DBWR buffers scanned 271,666 9.4 1.2
DBWR checkpoint buffers written 447,779 15.6 2.0
DBWR checkpoints 4 0.0 0.0
DBWR free buffers found 260,653 9.1 1.2
DBWR lru scans 3,142 0.1 0.0
DBWR make free requests 3,682 0.1 0.0
DBWR revisited being-written buff 0 0.0 0.0
DBWR summed scan depth 271,666 9.4 1.2
DBWR transaction table writes 495 0.0 0.0
DBWR undo block writes 117,428 4.1 0.5
OS Integral shared text size 0 0.0 0.0
OS Integral unshared data size 0 0.0 0.0
OS Involuntary context switches 0 0.0 0.0
OS Maximum resident set size 0 0.0 0.0
OS Page faults 0 0.0 0.0
OS Page reclaims 0 0.0 0.0
OS Socket messages received 0 0.0 0.0
OS System time used 0 0.0 0.0
OS User time used 0 0.0 0.0
OS Voluntary context switches 0 0.0 0.0
SQL*Net roundtrips to/from client 68,917,169 2,393.1 304.8
active txn count during cleanout 354,067 12.3 1.6
background checkpoints completed 4 0.0 0.0
background checkpoints started 4 0.0 0.0
background timeouts 63,817 2.2 0.3
branch node splits 108 0.0 0.0
buffer is not pinned count 1,569,403,567 54,497.0 6,941.0
buffer is pinned count 1,596,309,572 55,431.3 7,059.9
bytes received via SQL*Net from c 3,273,690,947 113,677.7 14,478.4
bytes sent via SQL*Net to client 16,978,497,478 589,572.1 75,090.2
calls to get snapshot scn: kcmgss 98,305,814 3,413.6 434.8
calls to kcmgas 736,133 25.6 3.3
calls to kcmgcs 23,424 0.8 0.1
change write time 7,146 0.3 0.0
cleanout - number of ktugct calls 287,614 10.0 1.3
cluster key scan block gets 432,172 15.0 1.9
cluster key scans 304,479 10.6 1.4
commit cleanout failures: block l 3 0.0 0.0
commit cleanout failures: buffer 82 0.0 0.0
commit cleanout failures: callbac 6,797 0.2 0.0
commit cleanout failures: cannot 686 0.0 0.0
commit cleanout failures: hot bac 0 0.0 0.0
commit cleanouts 1,951,847 67.8 8.6
commit cleanouts successfully com 1,944,279 67.5 8.6
commit txn count during cleanout 351,210 12.2 1.6
consistent changes 733,575 25.5 3.2
consistent gets 2,694,142,717 93,553.1 11,915.3
consistent gets - examination 1,036,848,317 36,004.2 4,585.6
current blocks converted for CR 3 0.0 0.0
cursor authentications 15,181 0.5 0.1
data blocks consistent reads - un 719,567 25.0 3.2
Instance Activity Stats for DB: SOS1P Instance: SOS1P Snaps: 77265 -77297

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
db block changes 12,518,687 434.7 55.4
db block gets 20,844,051 723.8 92.2
deferred (CURRENT) block cleanout 616,029 21.4 2.7
dirty buffers inspected 4,912 0.2 0.0
enqueue conversions 166,677 5.8 0.7
enqueue deadlocks 0 0.0 0.0
enqueue releases 1,971,088 68.5 8.7
enqueue requests 1,971,092 68.5 8.7
enqueue timeouts 4 0.0 0.0
enqueue waits 1,115 0.0 0.0
exchange deadlocks 42 0.0 0.0
execute count 45,299,159 1,573.0 200.3
failed probes on index block recl 0 0.0 0.0
free buffer inspected 25,481 0.9 0.1
free buffer requested 20,826,326 723.2 92.1
hot buffers moved to head of LRU 1,444,018 50.1 6.4
immediate (CR) block cleanout app 227,904 7.9 1.0
immediate (CURRENT) block cleanou 114,842 4.0 0.5
index crx upgrade (found) 26 0.0 0.0
index crx upgrade (positioned) 250,311 8.7 1.1
index fast full scans (full) 687,855 23.9 3.0
index fetch by key 754,873,821 26,212.7 3,338.6
index scans kdiixs1 93,164,757 3,235.1 412.0
leaf node 90-10 splits 12,516 0.4 0.1
leaf node splits 27,654 1.0 0.1
logons cumulative 1,286 0.0 0.0
messages received 1,120,882 38.9 5.0
messages sent 1,120,882 38.9 5.0
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 1,599,749,344 55,550.7 7,075.2
opened cursors cumulative 9,471,530 328.9 41.9
parse count (failures) 134 0.0 0.0
parse count (hard) 125,003 4.3 0.6
parse count (total) 9,540,844 331.3 42.2
parse time cpu 41,241 1.4 0.2
parse time elapsed 128,527 4.5 0.6
physical reads 20,572,572 714.4 91.0
physical reads direct 84,450 2.9 0.4
physical reads direct (lob) 46,802 1.6 0.2
physical writes 681,708 23.7 3.0
physical writes direct 215,395 7.5 1.0
physical writes direct (lob) 2,610 0.1 0.0
physical writes non checkpoint 507,899 17.6 2.3
pinned buffers inspected 19,298 0.7 0.1
prefetch clients - default 2 0.0 0.0
prefetched blocks 16,769,815 582.3 74.2
prefetched blocks aged out before 49 0.0 0.0
process last non-idle time 28,798 1.0 0.1
recursive aborts on index block r 0 0.0 0.0
recursive calls 31,316,519 1,087.5 138.5
recursive cpu usage 992,799 34.5 4.4
redo blocks written 4,438,678 154.1 19.6
redo buffer allocation retries 13 0.0 0.0
redo entries 6,520,185 226.4 28.8
redo log space requests 14 0.0 0.0
redo log space wait time 80 0.0 0.0
Instance Activity Stats for DB: SOS1P Instance: SOS1P Snaps: 77265 -77297

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
redo ordering marks 0 0.0 0.0
redo size 2,125,155,868 73,795.3 9,398.9
redo synch time 250,041 8.7 1.1
redo synch writes 275,960 9.6 1.2
redo wastage 75,754,528 2,630.6 335.0
redo write time 195,678 6.8 0.9
redo writer latching time 8 0.0 0.0
redo writes 296,433 10.3 1.3
rollback changes - undo records a 3,777 0.1 0.0
rows fetched via callback 202,190,130 7,021.0 894.2
session connect time 0 0.0 0.0
session logical reads 2,714,939,876 94,275.3 12,007.3
session pga memory 324,063,016 11,253.0 1,433.2
session pga memory max 1,740,168,616 60,426.7 7,696.2
session uga memory 137,700,672,816 4,781,605.4 609,004.0
session uga memory max 2,382,753,784 82,740.3 10,538.1
shared hash latch upgrades - no w 1,192,694 41.4 5.3
shared hash latch upgrades - wait 3,366 0.1 0.0
sorts (disk) 0 0.0 0.0
sorts (memory) 1,608,041 55.8 7.1
sorts (rows) 177,981,903 6,180.4 787.2
summed dirty queue length 15,996 0.6 0.1
switch current to new buffer 2,326 0.1 0.0
table fetch by rowid 958,598,647 33,287.0 4,239.6
table fetch continued row 37,418 1.3 0.2
table scan blocks gotten 434,760,922 15,096.9 1,922.8
table scan rows gotten 31,984,155,071 1,110,638.1 141,455.2
table scans (cache partitions) 0 0.0 0.0
table scans (long tables) 267 0.0 0.0
table scans (rowid ranges) 0 0.0 0.0
table scans (short tables) 22,142,481 768.9 97.9
transaction rollbacks 525 0.0 0.0
transaction tables consistent rea 20 0.0 0.0
transaction tables consistent rea 9,644 0.3 0.0
user calls 76,802,076 2,666.9 339.7
user commits 226,044 7.9 1.0
user rollbacks 64 0.0 0.0
workarea executions - multipass 0 0.0 0.0
workarea executions - onepass 10 0.0 0.0
workarea executions - optimal 5,474,449 190.1 24.2
write clones created in backgroun 0 0.0 0.0
write clones created in foregroun 241 0.0 0.0
-------------------------------------------------------------
Tablespace IO Stats for DB: SOS1P Instance: SOS1P Snaps: 77265 -77297
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
SOS_OC_DATA
3,070,320 107 3.9 6.5 79,586 3 474,615 2.9
SOS_OC_INDX
606,971 21 5.7 1.0 185,071 6 67,579 4.9
UNDO_01
527 0 6.4 1.0 117,866 4 901 0.2
STATSPACK
21,907 1 7.9 1.4 81,843 3 0 0.0
TEMP
5,218 0 6.0 16.2 13,711 0 0 0.0
SYSTEM
13,308 0 5.0 1.1 530 0 231 0.2
TOOLS
4,407 0 3.3 1.0 5 0 0 0.0
USERS
418 0 6.5 1.0 448 0 0 0.0
SPOTLIGHT
6 0 36.7 1.0 4 0 0 0.0
-------------------------------------------------------------
File IO Stats for DB: SOS1P Instance: SOS1P Snaps: 77265 -77297
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
SOS_OC_DATA /sos/db04/oracle/SOS1P/oradata/SOS1P_sos_oc_data_01.
620,033 22 3.6 7.3 877 0 91,241 2.3
/sos/db04/oracle/SOS1P/oradata/SOS1P_sos_oc_data_02.
498,319 17 4.6 7.2 225 0 109,834 2.9
/sos/db04/oracle/SOS1P/oradata/SOS1P_sos_oc_data_03.
335,935 12 3.7 7.4 19 0 69,831 2.2
/sos/db06/oracle/SOS1P/oradata/SOS1P_temp_04.dbf
512,462 18 3.3 6.6 278 0 90,917 2.4
/sos/db08/oracle/SOS1P/oradata/SOS1P_sos_oc_data_05.
495,149 17 4.0 5.3 23,108 1 52,300 4.2
/sos/db09/oracle/SOS1P/oradata/SOS1P_sos_oc_data_06.
462,956 16 3.9 5.4 18,009 1 53,082 3.8
/sos/db09/oracle/SOS1P/oradata/SOS1P_sos_oc_data_07.
145,466 5 4.1 5.0 37,070 1 7,410 4.5

SOS_OC_INDX /sos/db04/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_05.
13,923 0 5.0 1.0 3,006 0 1,337 5.7
/sos/db05/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_01.
65,656 2 3.6 1.0 1,424 0 8,977 4.4
/sos/db05/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_02.
47,416 2 4.3 1.0 2,692 0 6,620 6.3
/sos/db05/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_03.
25,623 1 5.3 1.0 3,030 0 3,260 7.0
/sos/db05/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_04.
16,547 1 6.2 1.0 2,703 0 2,154 7.4
/sos/db06/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_06.
47,078 2 6.2 1.0 14,231 0 7,641 3.7
/sos/db06/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_07.
46,875 2 6.1 1.0 13,717 0 8,368 3.6
/sos/db06/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_08.
21,495 1 6.4 1.0 5,671 0 3,638 4.6
/sos/db07/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_09.
48,541 2 5.5 1.0 8,435 0 5,173 5.5
/sos/db07/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_10.
69,050 2 5.7 1.0 22,413 1 6,567 4.6
/sos/db07/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_11.
70,902 2 5.5 1.0 31,540 1 7,025 4.5
/sos/db07/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_12.
42,587 1 5.5 1.0 30,250 1 2,796 4.3
/sos/db08/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_13.
41,850 1 7.1 1.0 20,848 1 2,113 7.1
/sos/db08/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_14.
28,348 1 6.8 1.0 9,904 0 1,114 6.3
/sos/db08/oracle/SOS1P/oradata/SOS1P_sos_oc_indx_15.
21,080 1 8.6 1.0 15,207 1 796 6.9

SPOTLIGHT /sos/db07/oracle/SOS1P/oradata/SOS1P_spotlight_01.db
6 0 36.7 1.0 4 0 0

STATSPACK /sos/db05/oracle/SOS1P/oradata/SOS1P_statspack_01.db
18,311 1 7.5 1.3 70,877 2 0
/sos/db06/oracle/SOS1P/oradata/SOS1P_statspack_02.db
File IO Stats for DB: SOS1P Instance: SOS1P Snaps: 77265 -77297
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
3,005 0 9.0 1.6 8,478 0 0
STATSPACK /sos/db08/oracle/SOS1P/oradata/SOS1P_statspack_03.db
591 0 13.7 1.0 2,488 0 0

SYSTEM /sos/db04/oracle/SOS1P/oradata/SOS1P_system_01.dbf
13,308 0 5.0 1.1 530 0 231 0.2

TEMP /sos/db04/oracle/SOS1P/oradata/SOS1P_temp_01.dbf
845 0 2.7 14.8 864 0 0
/sos/db04/oracle/SOS1P/oradata/SOS1P_temp_03.dbf
2,521 0 6.6 18.5 7,936 0 0
/sos/db05/oracle/SOS1P/oradata/SOS1P_temp_02.dbf
1,199 0 9.2 16.9 3,924 0 0
/sos/db07/oracle/SOS1P/oradata/SOS1P_temp_04.dbf
653 0 1.9 7.9 987 0 0

TOOLS /sos/db04/oracle/SOS1P/oradata/SOS1P_tools_01.dbf
4,407 0 3.3 1.0 5 0 0

UNDO_01 /sos/db04/oracle/SOS1P/oradata/SOS1P_undo_01_01.dbf
498 0 6.2 1.0 64,799 2 628 0.2
/sos/db05/oracle/SOS1P/oradata/SOS1P_undo_01_02.dbf
29 0 11.0 1.0 53,067 2 273 0.1

USERS /sos/db04/oracle/SOS1P/oradata/SOS1P_users_01.dbf
418 0 6.5 1.0 448 0 0

-------------------------------------------------------------
Buffer Pool Statistics for DB: SOS1P Instance: SOS1P Snaps: 77265 -77297
-> 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