STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- ------- ------------ SIEB7PRD 216115013 sieb7prd 1 9.2.0.7.0 NO WBMSLONSIEDB 01 Snap Id Snap Time Sessions Curs/Sess Comment --------- ------------------ -------- --------- ------------------- Begin Snap: 1 05-May-09 12:15:48 84 15.3 End Snap: 2 05-May-09 12:16:00 84 15.3 Elapsed: 0.20 (mins) Cache Sizes (end) ~~~~~~~~~~~~~~~~~ Buffer Cache: 576M Std Block Size: 4K Shared Pool Size: 624M Log Buffer: 2,048K Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 930,796.00 587,871.16 Logical reads: 36,542.42 23,079.42 Block changes: 1,605.83 1,014.21 Physical reads: 9.92 6.26 Physical writes: 0.58 0.37 User calls: 57.67 36.42 Parses: 10.83 6.84 Hard parses: 1.75 1.11 Sorts: 10.67 6.74 Logons: 0.00 0.00 Executes: 21.00 13.26 Transactions: 1.58 % Blocks changed per Read: 4.39 Recursive Call %: 61.70 Rollback per transaction %: 0.00 Rows per Sort: 526.07 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.97 In-memory Sort %: 100.00 Library Hit %: 91.58 Soft Parse %: 83.85 Execute to Parse %: 48.41 Latch Hit %: 100.00 Parse CPU to Parse Elapsd %: 116.67 % Non-Parse CPU: 97.54 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 88.71 88.74 % SQL with executions>1: 18.57 18.86 % Memory for SQL w/exec>1: 50.12 50.31 Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- CPU time 3 87.41 db file sequential read 119 0 10.92 control file parallel write 4 0 .64 log file sync 20 0 .45 control file sequential read 54 0 .24 ------------------------------------------------------------- Wait Events for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 -> 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 119 0 0 3 6.3 control file parallel write 4 0 0 5 0.2 log file sync 20 0 0 1 1.1 control file sequential read 54 0 0 0 2.8 db file parallel write 3 0 0 2 0.2 log file parallel write 43 0 0 0 2.3 SQL*Net more data to client 45 0 0 0 2.4 SQL*Net message from client 669 0 619 926 35.2 SQL*Net more data from clien 41 0 0 0 2.2 SQL*Net message to client 668 0 0 0 35.2 ------------------------------------------------------------- Background Wait Events for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- control file parallel write 4 0 0 5 0.2 db file parallel write 3 0 0 2 0.2 log file parallel write 43 0 0 0 2.3 rdbms ipc message 59 12 33 563 3.1 pmon timer 4 4 12 3000 0.2 ------------------------------------------------------------- SQL ordered by Gets for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 -> 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 --------------- ------------ -------------- ------ -------- --------- ---------- 24,997 1 24,997.0 5.7 2.34 2.41 2522684317 Module: SQL*Plus BEGIN statspack.snap; END; 21,795 1 21,795.0 5.0 1.45 1.46 3170171180 Module: SQL*Plus INSERT INTO STATS$SQLTEXT ( HASH_VALUE , TEXT_SUBSET , PIECE , S QL_TEXT , ADDRESS , COMMAND_TYPE , LAST_SNAP_ID ) SELECT /*+ ord ered use_nl(vst) */ VST.HASH_VALUE , NEW_SQL.TEXT_SUBSET , VST.P IECE , VST.SQL_TEXT , VST.ADDRESS , VST.COMMAND_TYPE , NEW_SQL.S NAP_ID FROM (SELECT HASH_VALUE , ADDRESS , TEXT_SUBSET , SNAP_ID 20,587 6 3,431.2 4.7 0.09 0.39 2975961164 Module: siebmtshmw.exe SELECT T9.CONFLICT_ID, T9.LAST_UPD, T9.CREATED , T9.LAST_UPD_BY, T9.CREATED_BY, T9.MODIFICATI ON_NUM, T9.ROW_ID, T9.ATP_STATUS_CD, T4.TARGET _OU_ID, T9.ACTION_CD, T9.ADJ_UNIT_PRI, T9.ASSE T_INTEG_ID, T9.AUC_ITEM_ID, T9.BASE_UNIT_PRI, 9,648 6 1,608.0 2.2 0.41 0.42 1263297694 Module: siebmtshmw.exe SELECT T1.CONFLICT_ID, T1.LAST_UPD, T1.CREATED , T1.LAST_UPD_BY, T1.CREATED_BY, T1.MODIFICATI ON_NUM, T1.ROW_ID, T1.CODE, T1.NAME, T1. ORDER_BY, T1.RPLCTN_LVL_CD, T1.SUB_TYPE, T1.TY PE, T1.VAL FROM SIEBEL.S_LST_OF_VAL T1 WHERE 5,729 1 5,729.0 1.3 0.02 0.02 2859710691 Module: siebmtshmw.exe SELECT T31.CONFLICT_ID, T31.LAST_UPD, T31.CREA TED, T31.LAST_UPD_BY, T31.CREATED_BY, T31.MODI FICATION_NUM, T31.ROW_ID, T29.NAME, T29.PR_ADD R_ID, T15.BL_CITY, T15.BL_ZIPCODE, T15.BL_STAT E, T15.BL_ADDR, T29.CONTRACT_VIS_FLG, T31.TARG 1,364 1 1,364.0 0.3 0.06 0.06 1055206310 Module: siebmtshmw.exe SELECT T1.CONFLICT_ID, T1.LAST_UPD, T1.CREATED , T1.LAST_UPD_BY, T1.CREATED_BY, T1.MODIFICATI ON_NUM, T1.ROW_ID, T1.CODE, T1.NAME, T1. ORDER_BY, T1.RPLCTN_LVL_CD, T1.SUB_TYPE, T1.TY PE FROM SIEBEL.S_LST_OF_VAL T1 WHERE (T1.CO 652 1 652.0 0.1 0.00 0.00 162256632 Module: siebmtshmw.exe SELECT T12.CONFLICT_ID, T12.LAST_UPD, T12.CREA TED, T12.LAST_UPD_BY, T12.CREATED_BY, T12.MODI FICATION_NUM, T12.ROW_ID, T26.AVAIL_CREDIT_AMT, T26.OU_NUM, T26.CLIENT_FLG, T26.CMPT_FLG, T2 SQL ordered by Gets for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 -> 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 --------------- ------------ -------------- ------ -------- --------- ---------- 6.BASE_CURCY_CD, T20.AMT_DT, T26.DUNS_NUM, T26 440 4 110.0 0.1 0.02 0.08 4193165248 Module: siebmtshmw.exe BEGIN INSERT INTO SIEBEL.S_EVT_ACT ( CONFLICT_ID, LAST_UPD, CREATED, LAST_UPD_BY, CREATED_BY , MODIFICATION_NUM, ROW_ID, TARGET_OU_ID, PAR_EVT_ID, SRA_SR_ID, ACTIVITY_UID, ALARM_F LAG, AMT_CURCY_CD, AMT_EXCH_DT, ASGN_USR_EXCLD 332 4 83.0 0.1 0.00 0.05 2263967167 Module: siebmtshmw.exe INSERT INTO SIEBEL.S_EVT_ACT ( CONFLICT_ID, LAST_UPD, CREATED, L AST_UPD_BY, CREATED_BY, MODIFICATION_NUM, ROW_ID, TARGET_OU_ID, PAR_EVT_ID, SRA_SR_ID, ACTIVITY_UID, ALARM_FLAG, AMT_CURCY_CD, A MT_EXCH_DT, ASGN_USR_EXCLD_FLG, BILLABLE_FLG, ALLOW_BREAK_FLG, E ST_COST_CURCY_CD, EST_COST_EXCH_DT, CREATOR_LOGIN, COST_CURCY_CD 254 1 254.0 0.1 0.00 0.00 1895171856 Module: siebproc.exe select childn.ROW_ID, childn.NAME, dsr.LAST_UPD from SIEBEL.S_DOCK_STATUS dsr, SIEBEL.S_DOCK_STATUS dsm, SIEBEL.S_NODE childn where childn.PAR_NODE_ID = : V1 and childn.APP_SERVER_NAME = :V2 and childn.NOD E_TYPE_CD = 'REMOTE' and (childn.EFF_END_DT is null or ch 250 20 12.5 0.1 0.00 0.03 1920228538 Module: siebprocmw.exe INSERT INTO SIEBEL.S_DOCK_TXN_LOG ( CONFLICT_ID, LAST_UPD, CREATED, LAST_UPD_BY, CREATED_BY, MODIFICATION_NUM, ROW_ID, FILE_FLG, ITEM_N AME, LOG_DATA_1, LOG_DATA_2, LOG_DATA_3, LOG_DATA_4, LOG_DATA_LONG_LEN, SRC_NODE_ID, O 174 1 174.0 0.0 0.00 0.02 3936430429 Module: siebmtshmw.exe SELECT T6.CONFLICT_ID, T6.LAST_UPD, T6.CREATED , T6.LAST_UPD_BY, T6.CREATED_BY, T6.MODIFICATI ON_NUM, T6.ROW_ID, T6.ROW_ID, T2.PR_POSTN_ID, T2.PAR_OU_ID, T6.X_ROLLED_UP_BILLING_ADJ_FRCST, T6.X_ROLLED_UP_CATALOGUE_FORECAST, T6.X_ROLLED_UP_CATALO 159 3 53.0 0.0 0.00 0.01 2075277026 Module: siebmtsh.exe select R.ROW_ID, R.RPT_TYPE, A.NAME, A.ACTION_TYPE, A.FILE_LOC , R.PARAM_VAL, R.ACTL_START_DT, R.ACTL_END_DT, R.SCHED_START_ DT, R.LAST_UPD, R.LAST_UPD_BY, R.SERVER_NAME, R.RPT_NUM, R.RP T_MAX, R.SRB_FLGS, R.REQUESTED_BY, R.REQ_KEY, R.DESC_TEXT, R. CODE_PAGE, R.LANG_ID, U.LOGIN, R.RETRY_ON_ERROR_FLG, R.NUM_RE SQL ordered by Gets for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 -> 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 --------------- ------------ -------------- ------ -------- --------- ---------- 122 4 30.5 0.0 0.02 0.02 89212873 Module: siebmtshmw.exe INSERT INTO SIEBEL.S_ACT_EMP ( ROW_STATUS, ROW_ID , MODIFICATION_NUM, CREATED_BY, LAST_UPD_BY, CREATED, LAST_UPD, CONFLICT_ID, ACTIVITY_ ------------------------------------------------------------- SQL ordered by Reads for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 162 6 27.0 136.1 0.09 0.39 2975961164 Module: siebmtshmw.exe SELECT T9.CONFLICT_ID, T9.LAST_UPD, T9.CREATED , T9.LAST_UPD_BY, T9.CREATED_BY, T9.MODIFICATI ON_NUM, T9.ROW_ID, T9.ATP_STATUS_CD, T4.TARGET _OU_ID, T9.ACTION_CD, T9.ADJ_UNIT_PRI, T9.ASSE T_INTEG_ID, T9.AUC_ITEM_ID, T9.BASE_UNIT_PRI, 15 4 3.8 12.6 0.02 0.08 4193165248 Module: siebmtshmw.exe BEGIN INSERT INTO SIEBEL.S_EVT_ACT ( CONFLICT_ID, LAST_UPD, CREATED, LAST_UPD_BY, CREATED_BY , MODIFICATION_NUM, ROW_ID, TARGET_OU_ID, PAR_EVT_ID, SRA_SR_ID, ACTIVITY_UID, ALARM_F LAG, AMT_CURCY_CD, AMT_EXCH_DT, ASGN_USR_EXCLD 10 4 2.5 8.4 0.00 0.05 2263967167 Module: siebmtshmw.exe INSERT INTO SIEBEL.S_EVT_ACT ( CONFLICT_ID, LAST_UPD, CREATED, L AST_UPD_BY, CREATED_BY, MODIFICATION_NUM, ROW_ID, TARGET_OU_ID, PAR_EVT_ID, SRA_SR_ID, ACTIVITY_UID, ALARM_FLAG, AMT_CURCY_CD, A MT_EXCH_DT, ASGN_USR_EXCLD_FLG, BILLABLE_FLG, ALLOW_BREAK_FLG, E ST_COST_CURCY_CD, EST_COST_EXCH_DT, CREATOR_LOGIN, COST_CURCY_CD 6 20 0.3 5.0 0.00 0.03 1920228538 Module: siebprocmw.exe INSERT INTO SIEBEL.S_DOCK_TXN_LOG ( CONFLICT_ID, LAST_UPD, CREATED, LAST_UPD_BY, CREATED_BY, MODIFICATION_NUM, ROW_ID, FILE_FLG, ITEM_N AME, LOG_DATA_1, LOG_DATA_2, LOG_DATA_3, LOG_DATA_4, LOG_DATA_LONG_LEN, SRC_NODE_ID, O 5 4 1.3 4.2 0.02 0.02 89212873 Module: siebmtshmw.exe INSERT INTO SIEBEL.S_ACT_EMP ( ROW_STATUS, ROW_ID , MODIFICATION_NUM, CREATED_BY, LAST_UPD_BY, CREATED, LAST_UPD, CONFLICT_ID, ACTIVITY_ ID, EMP_ID, ACT_EVT_STAT_CD, ACT_CAL_TYPE_CD, ACT_ALARM_FLG, ACT_CAL_DISP_FLG, ACT_APPT_REPT 5 4 1.3 4.2 0.00 0.02 4238235151 Module: siebmtshmw.exe UPDATE SIEBEL.S_ACT_EMP SET ACT_CAL_TYPE_CD = :B7, ACT_CAL_DISP_ FLG = :B6, ACT_TEMPLATE_FLG = :B5, ACT_EVT_STAT_CD = :B4, ACT_AL ARM_FLG = :B3, ACT_TODO_PLNSTRTDT = :B2, ACT_APPT_REPT_FLG = :B1 WHERE ACTIVITY_ID = :B8 3 1 3.0 2.5 0.00 0.02 1499736964 Module: siebmtshmw.exe BEGIN INSERT INTO SIEBEL.S_SRV_REQ ( CONFLICT_ID, LAST_UPD, CREATED, LAST_UPD_BY, CREATED_BY , MODIFICATION_NUM, ROW_ID, SR_TITLE, CS T_OU_ID, ALERT_USER_FLG, ASGN_USR_EXCLD_FLG, B ILLABLE_FLG, CALLBACK_REQ_FLG, APPT_REQ_FLG, C SQL ordered by Reads for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 3 1 3.0 2.5 0.00 0.02 3936430429 Module: siebmtshmw.exe SELECT T6.CONFLICT_ID, T6.LAST_UPD, T6.CREATED , T6.LAST_UPD_BY, T6.CREATED_BY, T6.MODIFICATI ON_NUM, T6.ROW_ID, T6.ROW_ID, T2.PR_POSTN_ID, T2.PAR_OU_ID, T6.X_ROLLED_UP_BILLING_ADJ_FRCST, T6.X_ROLLED_UP_CATALOGUE_FORECAST, T6.X_ROLLED_UP_CATALO 2 2 1.0 1.7 0.00 0.02 4085626020 Module: siebmtshmw.exe INSERT INTO SIEBEL.S_AUDIT_ITEM ( CONFLICT_ID, LA ST_UPD, CREATED, LAST_UPD_BY, CREATED_BY, MODIFICATION_NUM, ROW_ID, BUSCOMP_NAME, OPER ATION_DT, USER_ID, FIELD_NAME, NEW_VAL, OPERATION_CD, RECORD_ID) VALUES ( :1, :2, :3, :4, :5, :6, 1 1 1.0 0.8 0.00 0.01 79613511 Module: siebmtshmw.exe INSERT INTO SIEBEL.S_SRV_REQ ( CONFLICT_ID, LAST_UPD, CREATED, L AST_UPD_BY, CREATED_BY, MODIFICATION_NUM, ROW_ID, SR_TITLE, CST_ OU_ID, ALERT_USER_FLG, ASGN_USR_EXCLD_FLG, BILLABLE_FLG, CALLBAC K_REQ_FLG, APPT_REQ_FLG, COMP_UND_WRNTY_FLG, SRV_FEE_CURCY_CD, S R_CST_NUM, DOCK_FLG, ASGN_MANL_FLG, UND_MFG_WRNTY_FLG, NOTIFY_DT 1 1 1.0 0.8 0.00 0.01 1610319394 Module: siebprocmw.exe SELECT T1.CONFLICT_ID, T1.LAST_UPD, T1.CREATED , T1.LAST_UPD_BY, T1.CREATED_BY, T1.MODIFICATI ON_NUM, T1.ROW_ID, T1.ACTION_ID FROM SIEB EL.S_SRM_REQUEST T1 WHERE ((T1.STATUS IN ( :1 ) OR T1. STATUS IN ( :2 ) OR T1.STATUS IN ( :3 )) AND T1.TGT_DELETE_TS <= 1 1 1.0 0.8 0.00 0.01 3556949674 Module: siebmtshmw.exe UPDATE SIEBEL.S_SRV_REQ_BU SET SR_SUB_STAT_ID = :B8, SR_SEV_CD = :B7, SR_NUM = :B6, SR_CST_NUM = :B5, SR_AREA = :B4, SR_TITLE = :B3, SR_TYPE_CD = :B2, SR_STAT_ID = :B1 WHERE SRV_REQ_ID = :B9 1 1 1.0 0.8 0.00 0.01 3694845497 Module: siebmtshmw.exe INSERT INTO SIEBEL.S_SRV_REQ_LOYX ( OTHER_COSTS, ROW_ID, MODIFIC ATION_NUM, CREATED_BY, LAST_UPD_BY, CREATED, LAST_UPD, CONFLICT_ ID, PAR_ROW_ID) VALUES (:B1, :B2, :B3, :B4, :B5, :B6, :B7, :B8, :B9) 1 4 0.3 0.8 0.02 0.00 3845150624 Module: siebmtshmw.exe UPDATE SIEBEL.S_EVT_ACT SET LAST_UPD = :1, LAST_UPD _BY = :2, MODIFICATION_NUM = :3, X_CLEAR_FUT_ACT_FLG = :4 WHERE ROW_ID = :5 AND MODIFICATION_NUM = :6 ------------------------------------------------------------- SQL ordered by Executions for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 20 35 1.8 0.00 0.00 1920228538 Module: siebprocmw.exe INSERT INTO SIEBEL.S_DOCK_TXN_LOG ( CONFLICT_ID, LAST_UPD, CREATED, LAST_UPD_BY, CREATED_BY, MODIFICATION_NUM, ROW_ID, FILE_FLG, ITEM_N AME, LOG_DATA_1, LOG_DATA_2, LOG_DATA_3, LOG_DATA_4, LOG_DATA_LONG_LEN, SRC_NODE_ID, O 19 0 0.0 0.00 0.00 4274598960 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj# =:1 and intcol#=:2 6 6 1.0 0.07 0.07 1263297694 Module: siebmtshmw.exe SELECT T1.CONFLICT_ID, T1.LAST_UPD, T1.CREATED , T1.LAST_UPD_BY, T1.CREATED_BY, T1.MODIFICATI ON_NUM, T1.ROW_ID, T1.CODE, T1.NAME, T1. ORDER_BY, T1.RPLCTN_LVL_CD, T1.SUB_TYPE, T1.TY PE, T1.VAL FROM SIEBEL.S_LST_OF_VAL T1 WHERE 6 1,782 297.0 0.02 0.07 2975961164 Module: siebmtshmw.exe SELECT T9.CONFLICT_ID, T9.LAST_UPD, T9.CREATED , T9.LAST_UPD_BY, T9.CREATED_BY, T9.MODIFICATI ON_NUM, T9.ROW_ID, T9.ATP_STATUS_CD, T4.TARGET _OU_ID, T9.ACTION_CD, T9.ADJ_UNIT_PRI, T9.ASSE T_INTEG_ID, T9.AUC_ITEM_ID, T9.BASE_UNIT_PRI, 5 5 1.0 0.00 0.00 1254950678 select file# from file$ where ts#=:1 5 0 0.0 0.00 0.00 3814755345 Module: siebmtshmw.exe SELECT T4.CONFLICT_ID, T4.LAST_UPD, T4.CREATED , T4.LAST_UPD_BY, T4.CREATED_BY, T4.MODIFICATI ON_NUM, T4.ROW_ID, T4.ADDR_NAME, T4.NAME_LOCK_ FLG, T1.BRICK_ID, T2.NAME, T4.CITY, T4.C OUNTY, T4.ZIPCODE, T4.STATE, T4.ADDR, T4 4 4 1.0 0.00 0.00 89212873 Module: siebmtshmw.exe INSERT INTO SIEBEL.S_ACT_EMP ( ROW_STATUS, ROW_ID , MODIFICATION_NUM, CREATED_BY, LAST_UPD_BY, CREATED, LAST_UPD, CONFLICT_ID, ACTIVITY_ ID, EMP_ID, ACT_EVT_STAT_CD, ACT_CAL_TYPE_CD, ACT_ALARM_FLG, ACT_CAL_DISP_FLG, ACT_APPT_REPT 4 0 0.0 0.00 0.00 102828531 Module: siebmtshmw.exe UPDATE SIEBEL.S_ACT_CAL_RSRC SET ACT_CAL_TYPE_CD = :B4, ACT_TEMP LATE_FLG = :B3, ACT_TODO_PLNSTRTDT = :B2, ACT_APPT_REPT_FLG = :B 1 WHERE ACTIVITY_ID = :B5 SQL ordered by Executions for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 4 4 1.0 0.00 0.00 671044464 Module: siebmtshmw.exe SELECT T4.CONFLICT_ID, T4.LAST_UPD, T4.CREATED , T4.LAST_UPD_BY, T4.CREATED_BY, T4.MODIFICATI ON_NUM, T4.ROW_ID, T4.TARGET_OU_ID, T4.COMMENT S, T4.PAR_EVT_ID, T4.SRA_SR_ID, T4.AGREEMENT_I D, T4.ALARM_FLAG, T4.AMT_CURCY_CD, T4.AMT_EXCH 4 4 1.0 0.00 0.01 2263967167 Module: siebmtshmw.exe INSERT INTO SIEBEL.S_EVT_ACT ( CONFLICT_ID, LAST_UPD, CREATED, L AST_UPD_BY, CREATED_BY, MODIFICATION_NUM, ROW_ID, TARGET_OU_ID, PAR_EVT_ID, SRA_SR_ID, ACTIVITY_UID, ALARM_FLAG, AMT_CURCY_CD, A MT_EXCH_DT, ASGN_USR_EXCLD_FLG, BILLABLE_FLG, ALLOW_BREAK_FLG, E ST_COST_CURCY_CD, EST_COST_EXCH_DT, CREATOR_LOGIN, COST_CURCY_CD 4 4 1.0 0.00 0.00 2329821973 Module: siebmtshmw.exe SELECT T7.CONFLICT_ID, T7.LAST_UPD, T7.CREATED , T7.LAST_UPD_BY, T7.CREATED_BY, T7.MODIFICATI ON_NUM, T7.ROW_ID, T6.ROW_STATUS, T8.AGENT_FLG , T6.ACT_INVT_RESP_CD, T5.CURR_PRD_OBJ_NAME, T 8.EMAIL_ADDR, T8.EMP_FLG, T8.FAX_PH_NUM, T8.FS 4 4 1.0 0.00 0.00 3845150624 Module: siebmtshmw.exe UPDATE SIEBEL.S_EVT_ACT SET LAST_UPD = :1, LAST_UPD _BY = :2, MODIFICATION_NUM = :3, X_CLEAR_FUT_ACT_FLG = :4 WHERE ROW_ID = :5 AND MODIFICATION_NUM = :6 4 4 1.0 0.00 0.02 4193165248 Module: siebmtshmw.exe BEGIN INSERT INTO SIEBEL.S_EVT_ACT ( CONFLICT_ID, LAST_UPD, CREATED, LAST_UPD_BY, CREATED_BY , MODIFICATION_NUM, ROW_ID, TARGET_OU_ID, PAR_EVT_ID, SRA_SR_ID, ACTIVITY_UID, ALARM_F LAG, AMT_CURCY_CD, AMT_EXCH_DT, ASGN_USR_EXCLD 4 4 1.0 0.00 0.01 4238235151 Module: siebmtshmw.exe UPDATE SIEBEL.S_ACT_EMP SET ACT_CAL_TYPE_CD = :B7, ACT_CAL_DISP_ FLG = :B6, ACT_TEMPLATE_FLG = :B5, ACT_EVT_STAT_CD = :B4, ACT_AL ARM_FLG = :B3, ACT_TODO_PLNSTRTDT = :B2, ACT_APPT_REPT_FLG = :B1 WHERE ACTIVITY_ID = :B8 3 0 0.0 0.00 0.00 2075277026 Module: siebmtsh.exe select R.ROW_ID, R.RPT_TYPE, A.NAME, A.ACTION_TYPE, A.FILE_LOC , R.PARAM_VAL, R.ACTL_START_DT, R.ACTL_END_DT, R.SCHED_START_ DT, R.LAST_UPD, R.LAST_UPD_BY, R.SERVER_NAME, R.RPT_NUM, R.RP T_MAX, R.SRB_FLGS, R.REQUESTED_BY, R.REQ_KEY, R.DESC_TEXT, R. SQL ordered by Executions for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- ------------------------------------------------------------- SQL ordered by Parse Calls for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 6 6 4.62 1263297694 Module: siebmtshmw.exe SELECT T1.CONFLICT_ID, T1.LAST_UPD, T1.CREATED , T1.LAST_UPD_BY, T1.CREATED_BY, T1.MODIFICATI ON_NUM, T1.ROW_ID, T1.CODE, T1.NAME, T1. ORDER_BY, T1.RPLCTN_LVL_CD, T1.SUB_TYPE, T1.TY PE, T1.VAL FROM SIEBEL.S_LST_OF_VAL T1 WHERE 5 5 3.85 1254950678 select file# from file$ where ts#=:1 4 4 3.08 89212873 Module: siebmtshmw.exe INSERT INTO SIEBEL.S_ACT_EMP ( ROW_STATUS, ROW_ID , MODIFICATION_NUM, CREATED_BY, LAST_UPD_BY, CREATED, LAST_UPD, CONFLICT_ID, ACTIVITY_ ID, EMP_ID, ACT_EVT_STAT_CD, ACT_CAL_TYPE_CD, ACT_ALARM_FLG, ACT_CAL_DISP_FLG, ACT_APPT_REPT 4 4 3.08 671044464 Module: siebmtshmw.exe SELECT T4.CONFLICT_ID, T4.LAST_UPD, T4.CREATED , T4.LAST_UPD_BY, T4.CREATED_BY, T4.MODIFICATI ON_NUM, T4.ROW_ID, T4.TARGET_OU_ID, T4.COMMENT S, T4.PAR_EVT_ID, T4.SRA_SR_ID, T4.AGREEMENT_I D, T4.ALARM_FLAG, T4.AMT_CURCY_CD, T4.AMT_EXCH 2 2 1.54 55266034 Module: siebprocmw.exe DELETE FROM SIEBEL.S_SRM_REQ_PARAM WHERE REQ_ID = :B1 2 2 1.54 559801051 Module: siebmtshmw.exe SELECT T5.CONFLICT_ID, T5.LAST_UPD, T5.CREATED , T5.LAST_UPD_BY, T5.CREATED_BY, T5.MODIFICATI ON_NUM, T5.ROW_ID, T5.TARGET_OU_ID, T5.AGREEME NT_ID, T5.ALARM_FLAG, T5.SRC_ID, T5.INSCLM_ID, T5.SRA_TYPE_CD, T5.SRA_DEFECT_ID, T5.NAME, 2 2 1.54 596714334 Module: siebmtshmw.exe SELECT T4.CONFLICT_ID, T4.LAST_UPD, T4.CREATED , T4.LAST_UPD_BY, T4.CREATED_BY, T4.MODIFICATI ON_NUM, T4.ROW_ID, T4.ALARM_FLAG, T4.APPT_ALAR M_TM_MIN, T4.SRC_ID, T4.INSCLM_ID, T4.NAME, T4.TODO_ACTL_END_DT, T4.APPT_START_DT, T4.APPT_D 2 1 1.54 2522684317 Module: SQL*Plus BEGIN statspack.snap; END; 2 3 1.54 3098784771 Module: siebmtshmw.exe SELECT T7.CONFLICT_ID, T7.LAST_UPD, T7.CREATED SQL ordered by Parse Calls for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- , T7.LAST_UPD_BY, T7.CREATED_BY, T7.MODIFICATI ON_NUM, T7.ROW_ID, T1.NAME, T7.TARGET_OU_ID, T7.SRA_SR_ID, T7.AGREEMENT_ID, T7.SRC_ID, T7.INSCLM_ID, T7.CG_SRC_PAYMENT_ID, T7.SRA_DEFECT_I 2 2 1.54 3121902482 Module: siebprocmw.exe DELETE FROM SIEBEL.S_SRM_DATA WHERE PAR_ID = :B1 2 2 1.54 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 2 2 1.54 3717164462 Module: siebprocmw.exe DELETE FROM SIEBEL.S_SRM_REQUEST WHERE ROW_ID = :B1 1 1 0.77 79613511 Module: siebmtshmw.exe INSERT INTO SIEBEL.S_SRV_REQ ( CONFLICT_ID, LAST_UPD, CREATED, L AST_UPD_BY, CREATED_BY, MODIFICATION_NUM, ROW_ID, SR_TITLE, CST_ OU_ID, ALERT_USER_FLG, ASGN_USR_EXCLD_FLG, BILLABLE_FLG, CALLBAC K_REQ_FLG, APPT_REQ_FLG, COMP_UND_WRNTY_FLG, SRV_FEE_CURCY_CD, S R_CST_NUM, DOCK_FLG, ASGN_MANL_FLG, UND_MFG_WRNTY_FLG, NOTIFY_DT 1 4 0.77 102828531 Module: siebmtshmw.exe UPDATE SIEBEL.S_ACT_CAL_RSRC SET ACT_CAL_TYPE_CD = :B4, ACT_TEMP LATE_FLG = :B3, ACT_TODO_PLNSTRTDT = :B2, ACT_APPT_REPT_FLG = :B 1 WHERE ACTIVITY_ID = :B5 1 1 0.77 162256632 Module: siebmtshmw.exe SELECT T12.CONFLICT_ID, T12.LAST_UPD, T12.CREA TED, T12.LAST_UPD_BY, T12.CREATED_BY, T12.MODI FICATION_NUM, T12.ROW_ID, T26.AVAIL_CREDIT_AMT, T26.OU_NUM, T26.CLIENT_FLG, T26.CMPT_FLG, T2 6.BASE_CURCY_CD, T20.AMT_DT, T26.DUNS_NUM, T26 1 1 0.77 482261442 Module: siebmtshmw.exe INSERT INTO SIEBEL.S_AUDIT_ITEM ( CONFLICT_ID, LAST_UPD, CREATED , LAST_UPD_BY, CREATED_BY, MODIFICATION_NUM, ROW_ID, BUSCOMP_NAM E, OPERATION_DT, USER_ID, FIELD_NAME, OPERATION_CD, RECORD_ID) V ALUES ( :B1, :B2, :B3, :B4, :B5, :B6, :B7, :B8, :B9, :B10, :B11, :B12, :B13) 1 1 0.77 729813156 Module: siebmtshmw.exe INSERT INTO SIEBEL.S_SRV_ACT ( CURCY_CD, ROW_ID, MODIFICATION_NU M, CREATED_BY, LAST_UPD_BY, CREATED, LAST_UPD, CONFLICT_ID, PAR_ ROW_ID) VALUES (:B1, :B2, :B3, :B4, :B5, :B6, :B7, :B8, :B9) SQL ordered by Parse Calls for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 1 1 0.77 877937736 Module: siebmtshmw.exe SELECT T4.CONFLICT_ID, T4.LAST_UPD, T4.CREATED , T4.LAST_UPD_BY, T4.CREATED_BY, T4.MODIFICATI ON_NUM, T4.ROW_ID, T4.ALARM_FLAG, T4.APPT_ALAR M_TM_MIN, T4.SRC_ID, T4.INSCLM_ID, T4.NAME, T4.TODO_ACTL_END_DT, T4.APPT_START_DT, T4.APPT_D ------------------------------------------------------------- SQL ordered by Sharable Memory for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 -> End Sharable Memory Threshold: 1048576 Sharable Mem (b) Executions % Total Hash Value ---------------- ------------ ------- ------------ 1,886,144 1 0.3 2859710691 Module: siebmtshmw.exe SELECT T31.CONFLICT_ID, T31.LAST_UPD, T31.CREA TED, T31.LAST_UPD_BY, T31.CREATED_BY, T31.MODI FICATION_NUM, T31.ROW_ID, T29.NAME, T29.PR_ADD R_ID, T15.BL_CITY, T15.BL_ZIPCODE, T15.BL_STAT E, T15.BL_ADDR, T29.CONTRACT_VIS_FLG, T31.TARG ------------------------------------------------------------- Instance Activity Stats for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 284 23.7 15.0 CPU used when call started 284 23.7 15.0 CR blocks created 2 0.2 0.1 DBWR buffers scanned 360 30.0 19.0 DBWR checkpoint buffers written 4 0.3 0.2 DBWR checkpoints 0 0.0 0.0 DBWR free buffers found 360 30.0 19.0 DBWR lru scans 2 0.2 0.1 DBWR make free requests 2 0.2 0.1 DBWR revisited being-written buff 0 0.0 0.0 DBWR summed scan depth 360 30.0 19.0 DBWR transaction table writes 0 0.0 0.0 DBWR undo block writes 2 0.2 0.1 SQL*Net roundtrips to/from client 668 55.7 35.2 SQL*Net roundtrips to/from dblink 0 0.0 0.0 active txn count during cleanout 1,298 108.2 68.3 background checkpoints completed 0 0.0 0.0 background checkpoints started 0 0.0 0.0 background timeouts 16 1.3 0.8 branch node splits 4 0.3 0.2 buffer is not pinned count 17,125 1,427.1 901.3 buffer is pinned count 202,433 16,869.4 10,654.4 bytes received via SQL*Net from c 191,604 15,967.0 10,084.4 bytes received via SQL*Net from d 0 0.0 0.0 bytes sent via SQL*Net to client 294,836 24,569.7 15,517.7 bytes sent via SQL*Net to dblink 0 0.0 0.0 calls to get snapshot scn: kcmgss 2,443 203.6 128.6 calls to kcmgas 424 35.3 22.3 calls to kcmgcs 935 77.9 49.2 change write time 9 0.8 0.5 cleanout - number of ktugct calls 1,303 108.6 68.6 cleanouts and rollbacks - consist 0 0.0 0.0 cleanouts only - consistent read 1 0.1 0.1 cluster key scan block gets 4 0.3 0.2 cluster key scans 2 0.2 0.1 commit cleanout failures: block l 0 0.0 0.0 commit cleanout failures: buffer 0 0.0 0.0 commit cleanout failures: callbac 7 0.6 0.4 commit cleanout failures: cannot 0 0.0 0.0 commit cleanouts 2,458 204.8 129.4 commit cleanouts successfully com 2,451 204.3 129.0 commit txn count during cleanout 410 34.2 21.6 consistent changes 2 0.2 0.1 consistent gets 416,612 34,717.7 21,927.0 consistent gets - examination 394,098 32,841.5 20,742.0 current blocks converted for CR 0 0.0 0.0 cursor authentications 1 0.1 0.1 data blocks consistent reads - un 2 0.2 0.1 db block changes 19,270 1,605.8 1,014.2 db block gets 22,001 1,833.4 1,158.0 deferred (CURRENT) block cleanout 241 20.1 12.7 dirty buffers inspected 3 0.3 0.2 enqueue conversions 25 2.1 1.3 enqueue releases 743 61.9 39.1 enqueue requests 743 61.9 39.1 enqueue timeouts 0 0.0 0.0 Instance Activity Stats for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ enqueue waits 0 0.0 0.0 execute count 252 21.0 13.3 free buffer inspected 3 0.3 0.2 free buffer requested 2,889 240.8 152.1 hot buffers moved to head of LRU 386 32.2 20.3 immediate (CR) block cleanout app 1 0.1 0.1 immediate (CURRENT) block cleanou 859 71.6 45.2 index fast full scans (full) 0 0.0 0.0 index fetch by key 197,248 16,437.3 10,381.5 index scans kdiixs1 1,897 158.1 99.8 leaf node 90-10 splits 356 29.7 18.7 leaf node splits 398 33.2 21.0 logons cumulative 0 0.0 0.0 messages received 47 3.9 2.5 messages sent 47 3.9 2.5 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 18,282 1,523.5 962.2 number of map misses 37 3.1 2.0 number of map operations 491,121 40,926.8 25,848.5 opened cursors cumulative 124 10.3 6.5 parse count (failures) 0 0.0 0.0 parse count (hard) 21 1.8 1.1 parse count (total) 130 10.8 6.8 parse time cpu 7 0.6 0.4 parse time elapsed 6 0.5 0.3 physical reads 119 9.9 6.3 physical reads direct 0 0.0 0.0 physical writes 7 0.6 0.4 physical writes direct 0 0.0 0.0 physical writes non checkpoint 3 0.3 0.2 pinned buffers inspected 0 0.0 0.0 prefetch clients - default 0 0.0 0.0 prefetched blocks 0 0.0 0.0 prefetched blocks aged out before 0 0.0 0.0 process last non-idle time 0 0.0 0.0 recovery blocks read 0 0.0 0.0 recursive calls 1,115 92.9 58.7 recursive cpu usage 230 19.2 12.1 redo blocks written 22,801 1,900.1 1,200.1 redo buffer allocation retries 0 0.0 0.0 redo entries 11,473 956.1 603.8 redo log space requests 0 0.0 0.0 redo log space wait time 0 0.0 0.0 redo ordering marks 0 0.0 0.0 redo size 11,169,552 930,796.0 587,871.2 redo synch time 0 0.0 0.0 redo synch writes 20 1.7 1.1 redo wastage 10,712 892.7 563.8 redo write time 6 0.5 0.3 redo writer latching time 0 0.0 0.0 redo writes 44 3.7 2.3 rollback changes - undo records a 2 0.2 0.1 rollbacks only - consistent read 2 0.2 0.1 rows fetched via callback 2,288 190.7 120.4 session connect time 0 0.0 0.0 session logical reads 438,509 36,542.4 23,079.4 Instance Activity Stats for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ session pga memory 65,536 5,461.3 3,449.3 session pga memory max 3,801,088 316,757.3 200,057.3 session uga memory 196,392 16,366.0 10,336.4 session uga memory max 458,248 38,187.3 24,118.3 shared hash latch upgrades - no w 3,466 288.8 182.4 shared hash latch upgrades - wait 0 0.0 0.0 sorts (disk) 0 0.0 0.0 sorts (memory) 128 10.7 6.7 sorts (rows) 67,337 5,611.4 3,544.1 summed dirty queue length 3 0.3 0.2 switch current to new buffer 0 0.0 0.0 table fetch by rowid 8,485 707.1 446.6 table fetch continued row 3,668 305.7 193.1 table scan blocks gotten 47 3.9 2.5 table scan rows gotten 761 63.4 40.1 table scans (long tables) 0 0.0 0.0 table scans (short tables) 5 0.4 0.3 transaction rollbacks 1 0.1 0.1 transaction tables consistent rea 0 0.0 0.0 transaction tables consistent rea 0 0.0 0.0 user calls 692 57.7 36.4 user commits 19 1.6 1.0 user rollbacks 0 0.0 0.0 workarea executions - multipass 0 0.0 0.0 workarea executions - onepass 0 0.0 0.0 workarea executions - optimal 133 11.1 7.0 write clones created in backgroun 0 0.0 0.0 write clones created in foregroun 0 0.0 0.0 ------------------------------------------------------------- Tablespace IO Stats for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ SIEBEL_DATA 80 7 2.5 1.0 0 0 0 0.0 SIEBEL_INDEX 39 3 3.3 1.0 5 0 0 0.0 UNDOTBS01 0 0 0.0 2 0 0 0.0 ------------------------------------------------------------- File IO Stats for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ SIEBEL_DATA F:\ORACLE\ORADATA\SIEB7PRD\SIEBELDATA01.DBF 3 0 10.0 1.0 0 0 0 F:\ORACLE\ORADATA\SIEB7PRD\SIEBELDATA02.DBF 6 1 5.0 1.0 0 0 0 F:\ORACLE\ORADATA\SIEB7PRD\SIEBELDATA03.DBF 5 0 0.0 1.0 0 0 0 F:\ORACLE\ORADATA\SIEB7PRD\SIEBELDATA04.DBF 66 6 2.1 1.0 0 0 0 SIEBEL_INDEX F:\ORACLE\ORADATA\SIEB7PRD\SIEBELINDEX01.DBF 6 1 1.7 1.0 3 0 0 F:\ORACLE\ORADATA\SIEB7PRD\SIEBELINDEX02.DBF 11 1 5.5 1.0 2 0 0 F:\ORACLE\ORADATA\SIEB7PRD\SIEBELINDEX03.DBF 13 1 2.3 1.0 0 0 0 F:\ORACLE\ORADATA\SIEB7PRD\SIEBELINDEX04.DBF 9 1 3.3 1.0 0 0 0 UNDOTBS01 F:\ORACLE\ORADATA\SIEB7PRD\UNDOTBS01.DBF 0 0 2 0 0 ------------------------------------------------------------- Buffer Pool Statistics for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 -> 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 147,456 100.0 385,091 119 7 0 0 0 ------------------------------------------------------------- Instance Recovery Stats for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 -> 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 96 19 4829 36969 36959 276480 36959 E 96 19 7453 59565 59414 276480 59414 ------------------------------------------------------------- PGA Aggr Target Stats for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 -> 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 --------------- ---------------- ------------------------- 100.0 19 0 %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 300 227 106.2 10.2 9.6 100.0 .0 15,360 E 300 227 106.3 10.5 9.9 100.0 .0 15,360 ------------------------------------------------------------- PGA Aggr Target Histogram for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 4K 8K 107 107 0 0 8K 16K 8 8 0 0 32K 64K 6 6 0 0 64K 128K 1 1 0 0 256K 512K 1 1 0 0 512K 1024K 5 5 0 0 4M 8M 3 3 0 0 ------------------------------------------------------------- PGA Memory Advisory for DB: SIEB7PRD Instance: sieb7prd End Snap: 2 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 38 0.1 106,395.5 76,274.7 58.0 3,758 75 0.3 106,395.5 39,848.0 73.0 586 150 0.5 106,395.5 24,689.5 81.0 0 225 0.8 106,395.5 8,935.3 92.0 0 300 1.0 106,395.5 8,107.1 93.0 0 360 1.2 106,395.5 7,515.1 93.0 0 420 1.4 106,395.5 7,458.0 93.0 0 480 1.6 106,395.5 7,412.6 93.0 0 540 1.8 106,395.5 7,412.6 93.0 0 600 2.0 106,395.5 7,302.1 94.0 0 900 3.0 106,395.5 6,738.8 94.0 0 1,200 4.0 106,395.5 6,539.7 94.0 0 1,800 6.0 106,395.5 6,307.4 94.0 0 2,400 8.0 106,395.5 6,307.4 94.0 0 ------------------------------------------------------------- Rollback Segment Stats for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 ->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 1.0 0.00 0 0 0 0 3 36.0 0.00 32,906 0 0 0 4 2.0 0.00 0 0 0 0 5 5.0 0.00 0 0 0 0 6 7.0 0.00 4,984 0 0 0 7 1.0 0.00 0 0 0 0 8 3.0 0.00 2,148 0 0 0 9 2.0 0.00 0 0 0 0 10 1.0 0.00 0 0 0 0 11 1.0 0.00 0 0 0 0 12 2,029.0 0.00 3,302,250 4 0 4 ------------------------------------------------------------- Rollback Segment Storage for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 ->Optimal Size should be larger than Avg Active RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- --------------- --------------- --------------- 0 389,120 6,553 389,120 3 422,703,104 256,094,655 3,624,005,632 4 15,855,616 102,954,073 2,734,813,184 5 142,733,312 512,317,459 2,613,178,368 6 142,733,312 904,651,992 1,955,721,216 7 336,719,872 68,781,766 885,125,120 8 19,001,344 96,055,197 838,987,776 9 67,235,840 34,162,446 2,751,590,400 10 26,341,376 23,966,935 2,080,501,760 11 185,724,928 39,124,342 721,547,264 12 66,187,264 42,307,372 539,095,040 ------------------------------------------------------------- Latch Activity for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 ->"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 44 0.0 0 0 SQL memory manager latch 1 0.0 0 4 0.0 SQL memory manager worka 428 0.0 0 0 active checkpoint queue 9 0.0 0 0 cache buffer handles 3,499 0.0 0 0 cache buffers chains 527,773 0.0 0 2,877 0.0 cache buffers lru chain 14 0.0 0 2,841 0.0 channel operations paren 8 0.0 0 0 checkpoint queue latch 6,231 0.0 0 2,750 0.0 child cursor hash table 276 0.0 0 0 dml lock allocation 202 0.0 0 0 enqueue hash chains 1,483 0.0 0 0 enqueues 430 0.0 0 0 hash table column usage 0 0 26 0.0 lgwr LWN SCN 44 0.0 0 0 library cache 27,536 0.0 0.0 0 40 0.0 library cache load lock 44 0.0 0 0 library cache pin 1,549 0.0 0 0 library cache pin alloca 869 0.0 0 0 list of block allocation 138 0.0 0 0 mapped buffers lru chain 36 0.0 0 0 messages 187 0.0 0 0 mostly latch-free SCN 46 4.3 0.0 0 0 post/wait queue 33 0.0 0 20 0.0 redo allocation 11,532 0.0 0 0 redo copy 0 0 11,444 0.0 redo writing 150 0.0 0 0 row cache enqueue latch 681 0.0 0 0 row cache objects 720 0.0 0 0 sequence cache 112 0.0 0 0 session allocation 43 0.0 0 0 session idle bit 1,405 0.0 0 0 session timer 4 0.0 0 0 shared pool 2,087 0.0 0 0 sim partition latch 0 0 2 0.0 transaction allocation 2 0.0 0 0 undo global data 2,799 0.0 0 0 ------------------------------------------------------------- Dictionary Cache Stats for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 ->"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 48 39.6 0 0 27,395 dc_object_ids 89 0.0 0 0 7,330 dc_objects 111 0.0 0 0 2,465 dc_segments 92 0.0 0 1 9,368 dc_sequences 2 0.0 0 2 7 dc_tablespace_quotas 4 0.0 0 1 4 dc_tablespaces 22 0.0 0 0 20 dc_user_grants 4 0.0 0 0 29 dc_usernames 44 0.0 0 0 22 dc_users 31 0.0 0 0 30 ------------------------------------------------------------- Library Cache Activity for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- SQL AREA 117 5.1 738 8.1 0 0 TABLE/PROCEDURE 321 0.0 533 8.8 0 0 ------------------------------------------------------------- Shared Pool Advisory for DB: SIEB7PRD Instance: sieb7prd End Snap: 2 -> 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 ----------- ----- ---------- ------------ ------------ ------- --------------- 368 .6 325 15,173 206,150 1.0 18,060,337 432 .7 389 24,207 206,157 1.0 18,061,568 496 .8 452 34,630 206,173 1.0 18,062,908 560 .9 515 45,935 206,181 1.0 18,064,001 624 1.0 578 57,101 206,202 1.0 18,065,150 688 1.1 641 68,447 206,207 1.0 18,066,076 752 1.2 704 80,118 206,210 1.0 18,066,943 816 1.3 767 90,322 206,212 1.0 18,067,888 880 1.4 830 99,130 206,215 1.0 18,076,099 944 1.5 893 104,580 206,219 1.0 18,091,403 1,008 1.6 956 110,414 206,222 1.0 18,101,896 1,072 1.7 1,019 115,012 206,225 1.0 18,107,899 1,136 1.8 1,082 121,527 206,226 1.0 18,114,891 1,200 1.9 1,144 125,525 206,227 1.0 18,115,308 1,264 2.0 1,258 133,062 206,240 1.0 18,155,886 ------------------------------------------------------------- SGA Memory Summary for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 SGA regions Size in Bytes ------------------------------ ---------------- Database Buffers 603,979,776 Fixed Size 455,924 Redo Buffers 2,240,512 Variable Size 864,026,624 ---------------- sum 1,470,702,836 ------------------------------------------------------------- SGA breakdown difference for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 Pool Name Begin value End value % Diff ------ ------------------------------ ---------------- ---------------- ------- java free memory 33,554,432 33,554,432 0.00 large free memory 117,440,512 117,440,512 0.00 shared 1M buffer 2,098,176 2,098,176 0.00 shared Checkpoint queue 4,516,864 4,516,864 0.00 shared DML lock 174,244 174,244 0.00 shared FileIdentificatonBlock 323,292 323,292 0.00 shared FileOpenBlock 1,130,084 1,130,084 0.00 shared KGK heap 3,756 3,756 0.00 shared KGLS heap 22,925,112 22,927,852 0.01 shared KQR M PO 19,063,396 19,073,124 0.05 shared KQR S PO 2,498,968 2,498,968 0.00 shared KQR S SO 7,468 7,468 0.00 shared KQR X PO 2,432 2,432 0.00 shared KSXR large reply queue 166,584 166,584 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 165,100 165,100 0.00 shared PL/SQL DIANA 4,277,716 4,277,716 0.00 shared PL/SQL MPCODE 3,593,636 3,608,088 0.40 shared PLS non-lib hp 3,372 3,372 0.00 shared VLM link array 1,757,184 1,757,184 0.00 shared db_block_buffers 29,491,200 29,491,200 0.00 shared db_block_hash_buckets 2,826,296 2,826,296 0.00 shared db_handles 180,000 180,000 0.00 shared dictionary cache 2,137,216 2,137,216 0.00 shared enqueue 300,832 300,832 0.00 shared errors 229,588 229,588 0.00 shared event statistics per sess 3,159,520 3,159,520 0.00 shared fixed allocation callback 388 388 0.00 shared free memory 80,498,432 80,285,344 -0.26 shared joxs heap init 400 400 0.00 shared ksm_file2sga region 370,496 370,496 0.00 shared library cache 86,904,340 86,927,496 0.03 shared message pool freequeue 665,792 665,792 0.00 shared miscellaneous 27,094,540 27,094,540 0.00 shared parameters 263,784 264,460 0.26 shared processes 250,000 250,000 0.00 shared session param values 823,360 823,360 0.00 shared sessions 676,480 676,480 0.00 shared sim trace entries 1,572,864 1,572,864 0.00 shared sql area 410,068,584 410,229,680 0.04 shared table definiti 12,132 13,372 10.22 shared trace buffer 737,280 737,280 0.00 shared transaction 169,248 169,248 0.00 shared trigger defini 14,176 14,176 0.00 shared trigger inform 1,536 1,536 0.00 shared trigger source 1,776 1,776 0.00 fixed_sga 455,924 455,924 0.00 log_buffer 2,229,248 2,229,248 0.00 ------------------------------------------------------------- init.ora Parameters for DB: SIEB7PRD Instance: sieb7prd Snaps: 1 -2 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- _b_tree_bitmap_plans FALSE _no_or_expansion TRUE _shared_pool_reserved_pct 10 background_dump_dest D:\oracle\admin\SIEB7PRD\bdump compatible 9.2.0.0.0 control_files e:\oracle\oradata\SIEB7PRD\contro core_dump_dest D:\oracle\admin\SIEB7PRD\cdump db_block_buffers 147456 db_block_size 4096 db_domain db_file_multiblock_read_count 32 db_name SIEB7PRD distributed_lock_timeout 150 fast_start_mttr_target 300 hash_join_enabled TRUE instance_name SIEB7PRD java_pool_size 33554432 job_queue_processes 200 large_pool_size 117440512 log_archive_dest H:\oracle\oradata\SIEB7PRD\Archiv log_archive_start TRUE log_buffer 2097152 max_enabled_roles 50 open_cursors 1500 optimizer_index_caching 100 optimizer_index_cost_adj 10 pga_aggregate_target 314572800 processes 250 query_rewrite_enabled FALSE remote_login_passwordfile EXCLUSIVE resource_limit TRUE shared_pool_size 654311424 sort_area_retained_size 2097152 sort_area_size 1572864 star_transformation_enabled TRUE timed_statistics TRUE undo_management AUTO undo_retention 14400 undo_tablespace UNDOTBS01 use_indirect_data_buffers TRUE user_dump_dest D:\oracle\admin\SIEB7PRD\udump ------------------------------------------------------------- End of Report