| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Statspack report....
I want to know what you guys think about this Statspack report. I had
a vendor look at it and they said it looked fine..... The users are
complaing that the app is running slow, and yes I do see the #######
signs for db_file_sequential read.
I just want to see what you guys think.
In the I/o section I just posted a few up to the really bad tablesapce..
MIke
STATSPACK report for
DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- ---
Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 239 21-Oct-04 09:00:12 408
End Snap: 240 21-Oct-04 09:16:27 408
Elapsed: 16.25 (mins)
Cache Sizes
db_block_buffers: 109863 log_buffer: 1048576
db_block_size: 8192 shared_pool_size: 675000000
Load Profile
~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Redo size: 554,649.81
7,928.91
Logical reads: 109,724.48
1,568.55
Block changes: 2,862.34
40.92
Physical reads: 9,752.62
139.42
Physical writes: 261.45
3.74
User calls: 7,965.46
113.87
Parses: 1,110.18
15.87
Hard parses: 0.31
0.00
Sorts: 159.50
2.28
Logons: 1.01
0.01
Executes: 4,091.15
58.48
Transactions: 69.95
% Blocks changed per Read: 2.61 Recursive Call %: 4.81
Rollback per transaction %: 1.15 Rows per Sort: 286.82
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 99.91 Redo NoWait %: 100.00
Buffer Hit %: 91.11 In-memory Sort %: 99.89
Library Hit %: 99.97 Soft Parse %: 99.97
Execute to Parse %: 72.86 Latch Hit %: 99.87
Parse CPU to Parse Elapsd %: 83.83 % Non-Parse CPU: 98.44
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 58.50 59.39
% SQL with executions>1: 86.39 86.23
% Memory for SQL w/exec>1: 84.96 84.97
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs)Wt Time
db file sequential read 8,569,335 ############
100.00
db file scattered read 80,158 13,958
.00
buffer busy waits 95,778 8,883
.00
log file sync 71,179 8,123
.00
db file parallel write 46,412 2,744
.00
-------------------------------------------------------------
Wait Events for DB: PROD Instance: prod1 Snaps: 239 -240
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait
wait Waits
Event Waits Timeouts Time (cs)
(ms) /txn
buffer busy waits 95,778 0 8,883
1 1.4
log file sync 71,179 0 8,123
1 1.0
db file parallel write 46,412 0 2,744
1 0.7
log file parallel write 70,525 1 2,426
0 1.0
SQL*Net more data to client 821,315 0 1,810
0 12.0
log file sequential read 8,523 0 1,739
2 0.1
latch free 6,867 5,319 1,180
2 0.1
direct path read 5,337 0 455
1 0.1
file open 9,372 0 169
0 0.1
control file sequential read 2,070 0 147
1 0.0
db file parallel read 178 0 125
7 0.0
control file parallel write 521 0 75
1 0.0
enqueue 103 0 63
6 0.0
direct path write 1,722 0 18
0 0.0
log file switch completion 6 0 13
22 0.0
refresh controlfile command 27 0 4
1 0.0
LGWR wait for redo copy 290 0 2
0 0.0
SQL*Net break/reset to clien 26 0 0
0 0.0
file identify 8 0 0
0 0.0
log file single write 4 0 0
0 0.0
buffer deadlock 1 1 0
0 0.0
SQL*Net message from client 7,765,379 0 37,143,618
48 113.9
SQL*Net message to client 7,765,385 0 1,125
0 113.9
SQL*Net more data from clien 155,940 0 510
0 2.3
-------------------------------------------------------------
Background Wait Events for DB: PROD Instance: prod1 Snaps: 239 -240
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait
wait Waits
Event Waits Timeouts Time (cs)
(ms) /txn
latch free 242 240 241
10 0.0
direct path read 2,504 0 100
0 0.0
control file parallel write 521 0 75
1 0.0
db file scattered read 120 0 73
6 0.0
file open 6,279 0 66
0 0.1
control file sequential read 399 0 49
1 0.0
db file sequential read 29 0 6
2 0.0
LGWR wait for redo copy 290 0 2
0 0.0
direct path write 1,194 0 0
0 0.0
file identify 8 0 0
0 0.0
log file single write 4 0 0
0 0.0
rdbms ipc message 3,107,111 511 626,499
2 45.6
smon timer 4 4 122,864
###### 0.0
pmon timer 318 318 97,556
3068 0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: PROD Instance: prod1 Snaps: 239 -240
-> End Buffer Gets Threshold: 10000
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
Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
10,207,047 46 221,892.3 9.5 2079100604 SELECT /*+ CCL<RAD_GET_INTERFACE_REF_NBRS:S1274:R000:Q01> */ RRD.DETAIL_REFERENCE_NBR,RR.ORDER_ID,RRD.TASK_ASSAY_CD,RRD.SECTI ON_SEQUENCE FROM RAD_REPORT_DETAIL RRD ,RAD_REPORT RR ,ORDER_RA DIOLOGY O WHERE (O.ORDER_ID = :1 AND RR.ORDER_ID = O. PARENT_ORDER_ID AND RR.RAD_REPORT_ID = RRD.RAD_REPORT_ID) OR 4,768,443 23 207,323.6 4.5 1720862556 SELECT /*+ CCL<CP_PROCESS_DIST:S1080:R000:Q01> */ DISTINCT C E.ENCNTR_ID FROM CLINICAL_EVENT CE WHERE (CE.CLINSIG_UPDT_DT_T M >= :1 AND CE.CLINSIG_UPDT_DT_TM < :2 )
4,413,657 3,009 1,466.8 4.1 2130277726
UPDATE /*+ CCL<AFC_ADD_REFERENCE_API:S0000:R000:Q01> */ BILL _ITEM B SET B.ACTIVE_IND = DECODE( B.BILL_ITEM_ID, :1 ,1, B.ACTIVE_IND) , B.EXT_DESCRIPTION = :2 , B.EXT_SHORT_DESC = :3 , B.EXT_OWNER_CD = DECODE( :4 , 1, :5 ,B.EXT_OWNER_CD) , B.UPDT_CNT = (B.UPDT_CNT + 1), B.UPDT_DT_TM = 4,412,096 3,009 1,466.3 4.1 2140264080
UPDATE /*+ CCL<AFC_ADD_REFERENCE_API:S0000:R000:Q01> */ BILL _ITEM B SET B.ACTIVE_IND = DECODE( B.BILL_ITEM_ID, :1 ,1, B.ACTIVE_IND) , B.EXT_DESCRIPTION = :2 , B.EXT_SHORT_DESC = :3 , B.EXT_OWNER_CD = DECODE( :4 , 1, :5 ,B.EXT_OWNER_CD) , B.UPDT_CNT = (B.UPDT_CNT + 1), B.UPDT_DT_TM = 4,409,190 3,009 1,465.3 4.1 1533220943 SELECT /*+ CCL<AFC_ADD_REFERENCE_API:S0000:R000:Q01> */ B.EXT _PARENT_REFERENCE_ID,B.EXT_PARENT_CONTRIBUTOR_CD,B.EXT_CHILD_REF ERENCE_ID,B.EXT_CHILD_CONTRIBUTOR_CD,B.ACTIVE_IND FROM BILL_ITE
M B WHERE ((((B.EXT_PARENT_REFERENCE_ID + 0) = :1 AND (B
.EXT_PARENT_CONTRIBUTOR_CD + 0) = :2 AND B.EXT_CHILD_REFE
4,406,081 3,009 1,464.3 4.1 1704233066
SELECT /*+ CCL<AFC_ADD_REFERENCE_API:S0000:R000:Q01> */ B.EXT
_PARENT_REFERENCE_ID,B.BILL_ITEM_ID,B.EXT_PARENT_CONTRIBUTOR_CD,
B.EXT_CHILD_REFERENCE_ID,B.EXT_CHILD_CONTRIBUTOR_CD FROM BILL_I
TEM B WHERE ((B.EXT_PARENT_REFERENCE_ID + 0) = :1 AND (B
.EXT_PARENT_CONTRIBUTOR_CD + 0) = :2 AND B.EXT_CHILD_REFE
4,405,350 3,009 1,464.1 4.1 3302817892
SELECT /*+ CCL<AFC_ADD_REFERENCE_API:S0000:R000:Q01> */ B.EXT
_PARENT_REFERENCE_ID,B.BILL_ITEM_ID,B.EXT_PARENT_CONTRIBUTOR_CD,
B.EXT_CHILD_REFERENCE_ID,B.EXT_CHILD_CONTRIBUTOR_CD FROM BILL_I
TEM B WHERE ((B.EXT_PARENT_REFERENCE_ID + 0) = :1 AND (B
.EXT_PARENT_CONTRIBUTOR_CD + 0) > :2 AND B.EXT_CHILD_REFEREN
3,501,943 78 44,896.7 3.3 407768283
SELECT /*+ CCL<RAD_GET_BO_PROC_ASSOCS:S1075:R000:Q01> */ DTA2
.TASK_ASSAY_CD,DTA2.ACTIVITY_TYPE_CD,DTA2.DEFAULT_RESULT_TYPE_CD
,DTA2.ACTIVE_IND,BI2.BILL_ITEM_ID,BI2.EXT_PARENT_REFERENCE_ID,BI
SQL ordered by Gets for DB: PROD Instance: prod1 Snaps: 239 -240
-> End Buffer Gets Threshold: 10000
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
Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
2.EXT_CHILD_REFERENCE_ID,BI2.ACTIVE_IND,BIM2.BILL_ITEM_ID,BIM2.B
ILL_ITEM_TYPE_CD,BIM2.KEY6,BIM2.ACTIVE_IND,BIM2.KEY1_ID,BIM2.BIM
3,326,408 181 18,377.9 3.1 2071230177
SELECT /*+ CCL<RAD_GET_ORDERS_FILTERED2:S1075:R000:Q01> */ RO
RD.ORDER_ID,RORD.ACCESSION_ID,RORD.ACCESSION,RORD.CATALOG_CD,ROR
D.PERSON_ID,RORD.ENCNTR_ID,RORD.COMMENTS,RORD.REPORT_STATUS_CD,R
ORD.EXAM_STATUS_CD,RORD.PACKET_ROUTING_CD,RORD.ORD_LOC_CD,RORD.R
EFER_LOC_CD,RORD.CANCEL_DT_TM,RORD.CANCEL_BY_ID,RORD.REQUEST_DT_
2,913,753 240 12,140.6 2.7 2596340213
SELECT /*+ CCL<DCP_GET_PL_ASGMT:S1075:R000:Q01> */ SA.LOC_FAC
ILITY_CD,SA.LOC_BUILDING_CD,SA.LOC_UNIT_CD,SA.LOC_ROOM_CD,SA.LOC
_BED_CD,SA.ENCNTR_ID FROM DCP_SHIFT_ASSIGNMENT SA ,DCP_CARE_TEA
M_PRSNL CTP WHERE (((CTP.PRSNL_ID = :1 ) OR (CTP.PRSNL_ID
= :2 AND ((CTP.BEG_EFFECTIVE_DT_TM <= :3 AND CTP.END
2,242,703 9,645 232.5 2.1 2947506802
SELECT /*+ CCL<CE_GET_BMQ_EVENT:S1224:R000:Q01> */ CE.EVENT_I
D,CE.EVENT_CD FROM CLINICAL_EVENT CE WHERE (CE.PERSON_ID =
:1 AND CE.UPDT_DT_TM > :2 AND CE.VALID_UNTIL_DT_TM =
:3 AND CE.CLINSIG_UPDT_DT_TM > :4 )
2,227,113 5,746 387.6 2.1 3598759010
SELECT /*+ CCL<TRKFN_CONFIG_BED_LIST:S1105:R000:Q01> */ TE.TR
ACKING_EVENT_ID,TE.TRACKING_ID,TE.TRACKING_GROUP_CD,TE.TRACK_EVE
NT_ID,TE.ACTIVE_IND,TE.EVENT_STATUS_CD,ET.TRACK_EVENT_ID,ET.EVEN
T_USE_MEAN_CD FROM TRACK_EVENT ET ,TRACKING_EVENT TE WHERE (TE
.TRACKING_ID = :1 AND NOT :2 IS NULL AND NOT :3
2,156,387 48 44,924.7 2.0 4013739689
SELECT /*+ CCL<RAD_GET_BO_SUB_ASSOCS:S1075:R000:Q01> */ DTA2.
TASK_ASSAY_CD,DTA2.ACTIVITY_TYPE_CD,DTA2.DEFAULT_RESULT_TYPE_CD,
DTA2.ACTIVE_IND,BI2.BILL_ITEM_ID,BI2.EXT_PARENT_REFERENCE_ID,BI2
.EXT_CHILD_REFERENCE_ID,BI2.ACTIVE_IND,BIM2.BILL_ITEM_ID,BIM2.BI
LL_ITEM_TYPE_CD,BIM2.KEY6,BIM2.ACTIVE_IND,BIM2.KEY1_ID,BIM2.BIM1
2,122,459 8,217 258.3 2.0 65931931
SELECT /*+ CCL<RAD_GET_ORDERS_FILTERED2:S1075:R000:Q04> */ RR
.RAD_REPORT_ID,RR.ORDER_ID,RR.ORIGINAL_TRANS_DT_TM,RR.FINAL_DT_T
Physical Reads Executions Reads per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
1,902,675 9,645 197.3 20.0 2947506802 SELECT /*+ CCL<CE_GET_BMQ_EVENT:S1224:R000:Q01> */ CE.EVENT_I D,CE.EVENT_CD FROM CLINICAL_EVENT CE WHERE (CE.PERSON_ID = :1 AND CE.UPDT_DT_TM > :2 AND CE.VALID_UNTIL_DT_TM = :3 AND CE.CLINSIG_UPDT_DT_TM > :4 ) 1,864,384 23 81,060.2 19.6 1720862556 SELECT /*+ CCL<CP_PROCESS_DIST:S1080:R000:Q01> */ DISTINCT C E.ENCNTR_ID FROM CLINICAL_EVENT CE WHERE (CE.CLINSIG_UPDT_DT_T M >= :1 AND CE.CLINSIG_UPDT_DT_TM < :2 )
448,346 25 17,933.8 4.7 2334746304 DELETE /*+ CCL<DM_SQLPLAN:S0000:R000:Q01> */ FROM DM_SQL_PL AN DS WHERE (DS.STATEMENT_ID = :1 ) 315,904 923 342.3 3.3 1067746741 SELECT /*+ CCL<CP_PROCESS_DIST:S1080:R000:Q01> */ CE.EVENT_ID ,CE.ENCNTR_ID,CE.PERSON_ID,CE.VALID_UNTIL_DT_TM,CE.VIEW_LEVEL,CE .ORDER_ID,CE.PARENT_EVENT_ID,CE.EVENT_CLASS_CD,CE.RECORD_STATUS_ CD,CE.RESULT_STATUS_CD,CE.PUBLISH_FLAG,CE.CLINSIG_UPDT_DT_TM FRO M CLINICAL_EVENT CE WHERE (CE.ENCNTR_ID = :1 AND CE.ORD 307,550 396 776.6 3.2 1827812764 SELECT /*+ CCL<:S1224:R000:Q01> */ CE.CLINICAL_EVENT_ID,CE.EV ENT_ID,CE.VALID_UNTIL_DT_TM,CE.VIEW_LEVEL,CE.CLINSIG_UPDT_DT_TM, CE.ORDER_ID,CE.ORDER_ACTION_SEQUENCE,CE.CATALOG_CD,CE.ENCNTR_ID, CE.CONTRIBUTOR_SYSTEM_CD,CE.REFERENCE_NBR,CE.PARENT_EVENT_ID,CE. VALID_FROM_DT_TM,CE.EVENT_CLASS_CD,CE.EVENT_CD,CE.EVENT_TAG,CE.E 268,178 1 268,178.0 2.8 3814505950 SELECT /*+ CCL<ORM_CONT_ORD_UPDATE:S1080:R000:Q01> */ O.ORDER _ID,O.OE_FORMAT_ID,O.CATALOG_CD,O.SUSPEND_IND,O.CATALOG_TYPE_CD, O.ORDER_STATUS_CD,O.TEMPLATE_ORDER_FLAG,O.CONSTANT_IND,O.PRN_IND ,O.NEED_RX_VERIFY_IND,O.PROJECTED_STOP_DT_TM,O.UPDT_CNT,O.SUSPEN D_EFFECTIVE_DT_TM,O.RESUME_IND,O.RESUME_EFFECTIVE_DT_TM,O.DISCON 206,771 46 4,495.0 2.2 2079100604 SELECT /*+ CCL<RAD_GET_INTERFACE_REF_NBRS:S1274:R000:Q01> */ RRD.DETAIL_REFERENCE_NBR,RR.ORDER_ID,RRD.TASK_ASSAY_CD,RRD.SECTI ON_SEQUENCE FROM RAD_REPORT_DETAIL RRD ,RAD_REPORT RR ,ORDER_RA DIOLOGY O WHERE (O.ORDER_ID = :1 AND RR.ORDER_ID = O. PARENT_ORDER_ID AND RR.RAD_REPORT_ID = RRD.RAD_REPORT_ID) OR 170,435 46 3,705.1 1.8 2111040660 SELECT /*+ CCL<RAD_UPD_REPORT_INTERFACE:S1229:R000:Q01> */ RR PH.ACTION_DT_TM FROM RAD_RPT_PRSNL_HIST RRPH WHERE (RRPH.RAD_R
EPORT_ID = :1 AND RRPH.REPORT_PRSNL_ID = :2 AND RR
PH.ACTION_CD = :3 )
137,658 45 3,059.1 1.4 56458603
SELECT /*+ CCL<RAD_GET_INTERFACE_REF_NBRS:S1274:R000:Q01> */
RR.ORDER_ID,RR.RAD_RPT_REFERENCE_NBR FROM RAD_REPORT RR ,ORDER_
RADIOLOGY O WHERE (O.ORDER_ID = :1 AND RR.ORDER_ID = O.PARENT_ORDER_ID AND RR.SEQUENCE = :2 )
SQL ordered by Reads for DB: PROD Instance: prod1 Snaps: 239 -240 -> End Disk Reads Threshold: 1000
Physical Reads Executions Reads per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
137,331 11,337 12.1 1.4 1105872104 SELECT /*+ CCL<DCP_GET_PIP_PAT_RELTN:S1075:R000:Q01> */ EPR.E NCNTR_PRSNL_RELTN_ID,EPR.PRSNL_PERSON_ID,EPR.ENCNTR_PRSNL_R_CD,E PR.ENCNTR_ID,EPR.ACTIVE_IND,EPR.END_EFFECTIVE_DT_TM,EPR.FT_PRSNL _NAME,EPR.EXPIRATION_IND,PRS.PERSON_ID,PRS.ACTIVE_IND,PRS.NAME_F ULL_FORMATTED FROM PRSNL PRS ,ENCNTR_PRSNL_RELTN EPR WHERE (EP 115,677 33 3,505.4 1.2 1983328946 SELECT /*+ CCL<DM_STAT_GATHER_SQL_SMRY:S1090:R000:Q01> */ DS. STAT_SNAP_DT_TM,DS.SNAPSHOT_TYPE,DV.STAT_NAME,DV.STAT_SEQ,DV.STA T_NUMBER_VAL FROM DM_STAT_SNAPS_VALUES DV ,DM_STAT_SNAPS DS WH ERE (DS.SNAPSHOT_TYPE = :1 AND DS.STAT_SNAP_DT_TM BETWEEN :2 AND :3 AND DV.DM_STAT_SNAP_ID = DS.DM_STAT_SNAP_I 107,598 25 4,303.9 1.1 2755710454 SELECT /*+ CCL<DM_SQLPLAN:S0000:R000:Q01> */ P.STATEMENT_ID,P .ID,P.PARENT_ID,P.OBJECT_NAME,P.OPERATION,P.OPTIONS,P.INDEX_COLU MNS FROM DM_SQL_PLAN P WHERE (P.STATEMENT_ID = :1 ) 102,226 8 12,778.3 1.1 1843520581 SELECT /*+ CCL<SCH_INQA_ATGROUP_LOC_PRE_REG:S1075:R000:Q01> * / SB.BOOKING_ID,SB.BEG_DT_TM,SB.PERSON_ID,SB.ENCNTR_ID,SB.APPT_T YPE_CD,SB.LOCATION_CD,SAS.MNEMONIC,SA.STATE_MEANING,SA.SCH_EVENT _ID,SA.SCHEDULE_ID,SA.BIT_MASK,PN.NAME_FULL_FORMATTED,EI.INFO_SU B_TYPE_CD,EI.VALUE_CD FROM ENCNTR_INFO EI ,ENCOUNTER EN ,PERSON 77,281 952 81.2 0.8 449065947 SELECT /*+ CCL<DCP_GET_PIP_ORDERS_FLT:S1075:R000:Q01> */ O.PE RSON_ID,O.ORDER_ID,O.ENCNTR_ID,O.CATALOG_CD,O.CATALOG_TYPE_CD,O.
ORDER_STATUS_CD,O.ORDER_MNEMONIC,O.ACTIVITY_TYPE_CD,O.ORDER_DETA
IL_DISPLAY_LINE,O.TEMPLATE_ORDER_ID,O.IV_IND,O.CONSTANT_IND,O.OR
DER_COMMENT_IND,O.NEED_RX_VERIFY_IND,O.NEED_NURSE_REVIEW_IND,O.N
70,363 244 288.4 0.7 3805960843
SELECT /*+ CCL<DCP_GET_FORMS_BROWSELIST:S1075:R000:Q01> */ FA
.DCP_FORMS_ACTIVITY_ID,FAP.PRSNL_ID,FA.DCP_FORMS_REF_ID,FA.ENCNT
R_ID,FA.TASK_ID,FA.FORM_DT_TM,FA.BEG_ACTIVITY_DT_TM,FA.LAST_ACTI
VITY_DT_TM,FA.DESCRIPTION,FA.FLAGS,FA.FORM_STATUS_CD,FAP.DCP_FOR
Executions Rows Processed Rows per Exec Hash Value
------------ ---------------- ---------------- ------------
388,783 377,660 1.0 862691292 SELECT /*+ CCL<RAD_GET_ORDER_DETAIL_INFO:S1075:R000:Q04> */ P L.PERSON_ID,PL.NAME_FULL_FORMATTED FROM PRSNL PL WHERE (PL.PER SON_ID = :1 ) 388,781 4,391 0.0 488709921 SELECT /*+ CCL<RAD_GET_ORDER_DETAIL_INFO:S1075:R000:Q05> */ R
RP.RAD_REPORT_ID,RRP.REPORT_PRSNL_ID,RRP.PRSNL_RELATION_FLAG,RRP .QUEUE_IND,RRP.ACTION_DT_TM,RRP.PROXIED_FOR_ID,RRP.UPDT_DT_TM,RR P.UPDT_ID,RRP.UPDT_CNT,P2.PERSON_ID,P2.NAME_FULL_FORMATTED FROMPRSNL P2 ,RAD_REPORT_PRSNL RRP WHERE (RRP.RAD_REPORT_ID = 388,781 1,902 0.0 1873982411 SELECT /*+ CCL<RAD_GET_ORDER_DETAIL_INFO:S1075:R000:Q06> */ R RD.RAD_REPORT_ID,RRD.TASK_ASSAY_CD,RRD.REQUIRED_IND,RRD.TEMPLATE _ID,RRD.SECTION_SEQUENCE,RRD.ACR_CODE_IND,RRD.UPDT_CNT,RRD.DETAI L_REFERENCE_NBR,RRD.DETAIL_EVENT_ID,DTA.TASK_ASSAY_CD,DTA.RAD_SE CTION_TYPE_CD FROM DISCRETE_TASK_ASSAY DTA ,RAD_REPORT_DETAIL R 163,817 1,199 0.0 2744862165 SELECT /*+ CCL<CP_PROCESS_DIST:S1080:R000:Q01> */ E.ENCNTR_ID ,E.PERSON_ID,E.CREATE_DT_TM,E.ENCNTR_TYPE_CD,E.LOC_FACILITY_CD,E .LOC_BUILDING_CD,E.LOC_NURSE_UNIT_CD,E.LOC_ROOM_CD,E.LOC_BED_CD,
E.ORGANIZATION_ID FROM ENCOUNTER E WHERE (E.ENCNTR_ID = :1
AND 0 = :2 AND EXISTS ( ( SELECT CFV.PARENT_ENTITY_I
103,681 103,681 1.0 3557884162
SELECT /*+ CCL<TSK_CHG_TASKS_OVERDUE:S1080:R000:Q02> */ O.REF
ERENCE_TASK_ID,O.RETAIN_TIME,O.RETAIN_UNITS,O.OVERDUE_MIN,O.OVER
DUE_UNITS FROM ORDER_TASK O WHERE (O.REFERENCE_TASK_ID = :
1 )
95,035 456,368 4.8 1411947927 SELECT /*+ CCL<DCP_GET_SECTION_INPUT:S1075:R069:Q03> */ NVP.N AME_VALUE_PREFS_ID,NVP.PARENT_ENTITY_NAME,NVP.PARENT_ENTITY_ID,N VP.PVC_NAME,NVP.PVC_VALUE,NVP.ACTIVE_IND,NVP.MERGE_ID,NVP.SEQUEN CE FROM NAME_VALUE_PREFS NVP WHERE (NVP.PARENT_ENTITY_ID = :1 AND NVP.PARENT_ENTITY_NAME = :2 AND NVP.ACTIVE_IND
68,197 0 0.0 1005804620
SELECT PC.TO_PERSON_ID,PC.PERSON_COMBINE_ID FROM PERSON_COMBIN
E PC,PERSON P WHERE P.PERSON_ID = :b1 AND P.ACTIVE_IND = 0 AN
D PC.FROM_PERSON_ID = P.PERSON_ID AND PC.ACTIVE_IND = 1 AND PC
.ENCNTR_ID + 0 = 0 AND ROWNUM <= 1 ORDER BY PC.UPDT_DT_TM DE
SC
58,669 59,982 1.0 3403350612
SELECT /*+ CCL<DCP_GET_DTA_INFO:S1075:R154:Q01> */ RR.REFEREN
CE_RANGE_FACTOR_ID,RR.TASK_ASSAY_CD,RR.SPECIES_CD,RR.SERVICE_RES
OURCE_CD,RR.ACTIVE_IND,RR.SEX_CD,RR.AGE_FROM_MINUTES,RR.AGE_TO_M
INUTES,RR.DEFAULT_RESULT,RR.REVIEW_IND,RR.REVIEW_LOW,RR.REVIEW_H
IGH,RR.SENSITIVE_IND,RR.SENSITIVE_LOW,RR.SENSITIVE_HIGH,RR.NORMA
58,663 58,626 1.0 776413417
SELECT /*+ CCL<DCP_GET_DTA_INFO:S1075:R105:Q01> */ DTA.TASK_A
SQL ordered by Executions for DB: PROD Instance: prod1 Snaps: 239
-240
-> End Executions Threshold: 100
Executions Rows Processed Rows per Exec Hash Value
------------ ---------------- ---------------- ------------
SSAY_CD,DTA.ACTIVITY_TYPE_CD,DTA.DEFAULT_RESULT_TYPE_CD,DTA.EVEN
T_CD,DTA.MNEMONIC,DTA.DESCRIPTION,DTA.ACTIVE_IND,DTA.BEG_EFFECTI
VE_DT_TM,DTA.END_EFFECTIVE_DT_TM FROM DISCRETE_TASK_ASSAY DTA
WHERE (DTA.TASK_ASSAY_CD = :1 AND DTA.ACTIVE_IND = :2
58,652 9,373 0.2 2925092887
SELECT /*+ CCL<DCP_GET_DTA_INFO:S1075:R338:Q01> */ DM.DATA_MA
P_TYPE_FLAG,DM.MAX_DIGITS,DM.MIN_DECIMAL_PLACES,DM.MIN_DIGITS,DM
.RESULT_ENTRY_FORMAT,DM.SERVICE_RESOURCE_CD,DM.TASK_ASSAY_CD,DM.
ACTIVE_IND FROM DATA_MAP DM WHERE (DM.TASK_ASSAY_CD = :1
AND :2 = :3 AND DM.ACTIVE_IND = :4 AND DM.SE
58,647 2,335 0.0 2811665237
SELECT /*+ CCL<DCP_GET_DTA_INFO:S1075:R221:Q01> */ E.EQUATION
_ID,E.SERVICE_RESOURCE_CD,E.TASK_ASSAY_CD,E.SPECIES_CD,E.AGE_FRO
M_MINUTES,E.AGE_TO_MINUTES,E.SEX_CD,E.EQUATION_DESCRIPTION,E.ACT
IVE_IND,E.SCRIPT,E.EQUATION_POSTFIX,EC.EQUATION_ID,EC.CONSTANT_V
ALUE,EC.DEFAULT_VALUE,EC.INCLUDED_ASSAY_CD,EC.NAME,EC.RESULT_REQ
58,647 297,264 5.1 3109024946
SELECT /*+ CCL<DCP_GET_DTA_INFO:S1075:R287:Q01> */ AR.REFEREN
CE_RANGE_FACTOR_ID,AR.SEQUENCE,AR.NOMENCLATURE_ID,AR.DEFAULT_IND
,AR.DESCRIPTION,AR.ACTIVE_IND,AR.RESULT_VALUE,AR.MULTI_ALPHA_SOR
T_ORDER,N.NOMENCLATURE_ID,N.ACTIVE_IND,N.BEG_EFFECTIVE_DT_TM,N.E
ND_EFFECTIVE_DT_TM,N.SOURCE_STRING,N.SHORT_STRING,N.MNEMONIC FRO
38,241 0 0.0 708703282
SELECT PC.TO_ENCNTR_ID,PC.ENCNTR_COMBINE_ID FROM ENCNTR_COMBIN
E PC,ENCOUNTER P WHERE P.ENCNTR_ID = :b1 AND P.ACTIVE_IND = 0
AND PC.FROM_ENCNTR_ID = P.ENCNTR_ID AND PC.ACTIVE_IND = 1 AND
ROWNUM <= 1 ORDER BY PC.UPDT_DT_TM DESC
31,151 0 0.0 3691507891
SELECT /*+ CCL<CS_SRV_GET_BILL_ITEMS:S1344:R000:Q01> */ BIM.B
ILL_ITEM_ID,BIM.BILL_ITEM_TYPE_CD,BIM.KEY6,BIM.KEY7,BIM.ACTIVE_I
ND,BIM.BEG_EFFECTIVE_DT_TM,BIM.END_EFFECTIVE_DT_TM,BIM.KEY1_ID,B
Sharable Mem (b) Executions % Total Hash Value
---------------- ------------ ------- ------------
1,556,723 71 0.2 2359419142 INSERT /*+ CCL<RX_ADD_PRINT_ORD_HX:S1650:R000:Q01> */ INTO FILL_PRINT_ORD_HX (RUN_ID, ORDER_ID, ORDER_ROW_SEQ, ENCNTR_ID, PERSON_ID, PERSON_NAME_S, FACILITY_CD, FACILITY_S, LOCATION_CD, LOCATION_S, ROOM_CD, ROOM_S, BED_CD, BED_S, RACE_CD, RACE_S, SEX _CD, SEX_S, ADMIN_DT_TM, INGRED_DESC, INGRED_SEQ, ITEM_ID, CHARG
Statistic Total per Second perTrans
CPU used by this session 288,627 296.0 4.2 CPU used when call started 288,747 296.2 4.2 CR blocks created 14,619 15.0 0.2 Cached Commit SCN referenced 0 0.0 0.0 DBWR buffers scanned 6,561,908 6,730.2 96.2 DBWR checkpoint buffers written 986 1.0 0.0 DBWR checkpoints 1 0.0 0.0 DBWR free buffers found 6,415,691 6,580.2 94.1 DBWR lru scans 9,587 9.8 0.1 DBWR make free requests 80,754 82.8 1.2 DBWR revisited being-written buff 0 0.0 0.0 DBWR summed scan depth 6,561,908 6,730.2 96.2 DBWR transaction table writes 100 0.1 0.0 DBWR undo block writes 26,805 27.5 0.4 SQL*Net roundtrips to/from client 7,763,987 7,963.1 113.8 SQL*Net roundtrips to/from dblink 0 0.0 0.0 background checkpoints completed 1 0.0 0.0 background checkpoints started 1 0.0 0.0 background timeouts 1,444 1.5 0.0 branch node splits 15 0.0 0.0 buffer is not pinned count 69,940,863 71,734.2 1,025.5 buffer is pinned count 544,324,848 558,281.97,980.8
bytes received via SQL*Net from d 0 0.0 0.0 bytes sent via SQL*Net to client 2,997,298,839 3,074,152.7 43,946.1 bytes sent via SQL*Net to dblink 0 0.0 0.0 calls to get snapshot scn: kcmgss 4,148,800 4,255.2 60.8 calls to kcmgas 79,485 81.5 1.2 calls to kcmgcs 32,676 33.5 0.5 change write time 1,085 1.1 0.0 cleanouts only - consistent read 12,296 12.6 0.2 cluster key scan block gets 17,537 18.0 0.3 cluster key scans 5,878 6.0 0.1 commit cleanout failures: block l 2,073 2.1 0.0 commit cleanout failures: buffer 2 0.0 0.0 commit cleanout failures: callbac 29 0.0 0.0 commit cleanout failures: cannot 98 0.1 0.0 commit cleanouts 524,966 538.4 7.7 commit cleanouts successfully com 522,764 536.2 7.7 consistent changes 154,035 158.0 2.3 consistent gets 102,384,550 105,009.81,501.2
cursor authentications 3,622 3.7 0.1 data blocks consistent reads - un 135,341 138.8 2.0 db block changes 2,790,781 2,862.3 40.9 db block gets 4,596,737 4,714.6 67.4 deferred (CURRENT) block cleanout 153,494 157.4 2.3 dirty buffers inspected 38,966 40.0 0.6 enqueue conversions 5,302 5.4 0.1 enqueue releases 196,713 201.8 2.9 enqueue requests 196,716 201.8 2.9 enqueue timeouts 1 0.0 0.0 enqueue waits 0 0.0 0.0 exchange deadlocks 1 0.0 0.0 execute count 3,988,874 4,091.2 58.5 free buffer inspected 41,611 42.70.6
Statistic Total per Second perTrans
free buffer requested 9,531,278 9,775.7 139.8 hot buffers moved to head of LRU 1,630,504 1,672.3 23.9 immediate (CR) block cleanout app 12,296 12.6 0.2 immediate (CURRENT) block cleanou 45,953 47.1 0.7 leaf node splits 4,283 4.4 0.1 logons cumulative 987 1.0 0.0 messages received 3,547,243 3,638.2 52.0 messages sent 3,547,243 3,638.2 52.0 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 61,942,963 63,531.2 908.2 opened cursors cumulative 32,024 32.9 0.5 parse count (hard) 304 0.3 0.0 parse count (total) 1,082,429 1,110.2 15.9 parse time cpu 4,489 4.6 0.1 parse time elapsed 5,355 5.5 0.1 physical reads 9,508,807 9,752.6 139.4 physical reads direct 35,300 36.2 0.5 physical writes 254,918 261.5 3.7 physical writes direct 69,500 71.3 1.0 physical writes non checkpoint 254,064 260.6 3.7 pinned buffers inspected 361 0.4 0.0 prefetched blocks 823,931 845.1 12.1 prefetched blocks aged out before 153 0.2 0.0 process last non-idle time 20,868,983,035 ############ 305,978.9 recursive calls 392,397 402.5 5.8 recursive cpu usage 2,063 2.1 0.0 redo blocks written 1,126,732 1,155.6 16.5 redo buffer allocation retries 6 0.0 0.0 redo entries 1,433,132 1,469.9 21.0 redo log space requests 6 0.0 0.0 redo log space wait time 13 0.0 0.0 redo ordering marks 0 0.0 0.0 redo size 540,783,564 554,649.8 7,928.9 redo synch time 8,154 8.4 0.1 redo synch writes 70,814 72.6 1.0 redo wastage 17,971,140 18,431.9 263.5 redo write time 6,915 7.1 0.1 redo writer latching time 2 0.0 0.0 redo writes 70,484 72.3 1.0 rollback changes - undo records a 7,816 8.0 0.1 rows fetched via callback 6,639,922 6,810.2 97.4 session connect time 20,868,983,035 ############ 305,978.9 session cursor cache count 564 0.6 0.0 session cursor cache hits 994,061 1,019.6 14.6 session logical reads 106,981,369 109,724.5 1,568.6 session pga memory 145,752,464 149,489.7 2,137.0 session pga memory max 146,143,300 149,890.6 2,142.7 session uga memory 2,640,136 2,707.8 38.7 session uga memory max 42,904,216 44,004.3 629.1 sorts (disk) 169 0.2 0.0 sorts (memory) 155,342 159.3 2.3 sorts (rows) 44,603,040 45,746.7 654.0 summed dirty queue length 3,143 3.20.1
table fetch by rowid 287,661,545 295,037.5 4,217.7 table fetch continued row 92,825 95.21.4
Statistic Total per Second perTrans
table scan blocks gotten 23,686,110 24,293.5
347.3
table scan rows gotten 1,033,797,122 1,060,304.7
15,157.4
table scans (long tables) 45 0.1
0.0
table scans (short tables) 161,873 166.0
2.4
total file opens 9,372 9.6
0.1
transaction rollbacks 716 0.7
0.0
transaction tables consistent rea 7 0.0
0.0
transaction tables consistent rea 18,622 19.1
0.3
user calls 7,766,327 7,965.5
113.9
user commits 67,418 69.2
1.0
user rollbacks 786 0.8
0.0
write clones created in backgroun 0 0.0
0.0
write clones created in foregroun 20 0.0
0.0
-------------------------------------------------------------
Tablespace IO Stats for DB: PROD Instance: prod1 Snaps: 239 -240
->ordered by IOs (Reads + Writes) desc
Av Av Av Av Buffer
Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
Wt(ms)
109,771 113 ###### 1.2 924 1 1,152
0.5
I_SCHED
File IO Stats for DB: PROD Instance: prod1 Snaps: 239 -240
->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)
Free Write
Buffer
Buffer Consistent Physical Physical Buffer Complete
Busy
P Gets Gets Reads Writes Waits Waits
Waits
Buffer wait Statistics for DB: PROD Instance: prod1 Snaps: 239 -240 -> ordered by wait time desc, waits desc
Tot Wait Avg
Class Waits Time (cs) Time (cs)
------------------ ----------- ---------- ---------
data block 95,630 8,891 0
segment header 7 2 0
undo block 114 0 0
undo header 24 0 0
-------------------------------------------------------------
Rollback Segment Stats for DB: PROD Instance: prod1 Snaps: 239 -240
->A high value for "Pct Waits" suggests more rollback segments may be
required
Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks Extends
------ ------------ ------- --------------- -------- -------- --------
0 5.0 0.00 0 0 0 0
9 3,293.0 0.00 3,454,544 1 0 0
10 3,174.0 0.00 3,010,108 0 0 0
11 3,433.0 0.00 3,687,096 0 0 0
12 3,038.0 0.00 2,154,028 0 0 0
13 7,055.0 0.00 11,609,234 1 0 0
14 3,061.0 0.00 2,830,356 0 0 0
15 3,473.0 0.00 3,204,198 0 0 0
16 3,443.0 0.00 2,817,678 0 0 0
17 3,675.0 0.00 3,809,868 0 0 0
18 3,317.0 0.00 3,120,574 0 0 0
19 3,442.0 0.00 2,919,106 0 0 0
20 3,680.0 0.00 4,201,618 0 0 0
21 3,253.0 0.00 2,459,246 0 0 0
22 3,351.0 0.00 2,934,360 0 0 0
23 3,271.0 0.00 2,833,596 0 0 0
24 2,958.0 0.00 2,492,160 1 0 0
25 2,903.0 0.00 2,075,264 0 0 0
26 3,051.0 0.00 2,441,676 0 0 0
27 2,822.0 0.00 2,310,062 0 0 0
28 3,194.0 0.00 2,972,440 1 0 0
29 4,658.0 0.00 8,296,196 1 0 0
30 3,444.0 0.00 2,922,886 0 0 0
31 3,456.0 0.00 2,813,824 0 0 0
32 3,285.0 0.00 2,575,732 1 0 0
33 3,435.0 0.00 3,341,226 0 0 0
34 3,076.0 0.00 1,956,952 0 0 0
35 3,343.0 0.00 3,206,160 0 0 0
36 3,145.0 0.00 2,585,040 0 0 0
37 3,410.0 0.00 3,329,212 1 0 0
38 3,190.0 0.00 2,739,822 0 0 0
39 3,036.0 0.00 2,289,612 0 0 0
40 3,152.0 0.00 2,470,380 0 0 0
41 2,953.0 0.00 1,996,910 0 0 0
42 3,072.0 0.00 2,671,246 0 0 0
43 3,091.0 0.00 2,509,658 0 0 0
44 2,949.0 0.00 2,196,742 0 0 0
45 2,521.0 0.00 2,199,416 1 0 0
46 3,123.0 0.00 3,198,252 1 0 0
47 2,782.0 0.00 1,817,194 0 0 0
48 2,825.0 0.00 2,334,548 0 0 0
49 2,996.0 0.00 2,652,426 0 0 0
50 3,160.0 0.00 3,074,972 1 0 0
51 2,906.0 0.00 2,292,544 0 0 0
52 3,165.0 0.00 3,033,022 0 0 0
53 3,163.0 0.00 2,707,320 0 0 0
54 2,877.0 0.00 2,072,080 0 0 0
55 3,148.0 0.00 3,100,590 0 0 0
56 3,227.0 0.00 3,083,754 1 0 0
57 3,110.0 0.00 2,512,994 0 0 0
58 2,931.0 0.00 2,144,628 0 0 0
59 3,146.0 0.00 2,926,798 1 0 0
60 3,006.0 0.00 2,428,354 0 0 0
61 3,074.0 0.00 2,534,146 0 0 0
Rollback Segment Stats for DB: PROD Instance: prod1 Snaps: 239 -240
->A high value for "Pct Waits" suggests more rollback segments may be
required
Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks Extends
------ ------------ ------- --------------- -------- -------- --------
62 3,105.0 0.00 2,737,228 0 0 0
63 2,939.0 0.00 2,368,878 0 0 0
64 2,985.0 0.00 2,403,918 0 0 0
-------------------------------------------------------------
Rollback Segment Storage for DB: PROD Instance: prod1 Snaps: 239
-240
RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- --------------- --------------- ---------------
0 16,408,576 0 16,408,576
9 629,137,408 14,867,227 629,145,600 629,137,408
10 629,137,408 14,905,434 629,145,600 629,137,408
11 629,137,408 17,697,864 629,145,600 629,137,408
12 629,137,408 16,545,140 629,145,600 629,137,408
13 629,137,408 14,599,480 629,145,600 629,137,408
14 629,137,408 238,771,688 629,145,600 2,689,589,248
15 629,137,408 17,020,643 629,145,600 629,137,408
16 629,137,408 16,891,501 629,145,600 629,137,408
17 629,137,408 14,905,482 629,145,600 629,137,408
18 629,137,408 16,463,282 629,145,600 629,137,408
19 629,137,408 14,599,045 629,145,600 629,137,408
20 629,137,408 14,334,616 629,145,600 629,137,408
21 629,137,408 14,813,201 629,145,600 629,137,408
22 629,137,408 14,599,480 629,145,600 629,137,408
23 629,137,408 14,473,535 629,145,600 629,137,408
24 629,137,408 14,334,616 629,145,600 629,137,408
25 629,137,408 14,473,946 629,145,600 629,137,408
26 629,137,408 16,463,400 629,145,600 629,137,408
27 629,137,408 17,236,598 629,145,600 629,137,408
28 629,137,408 17,067,752 629,145,600 629,137,408
29 629,137,408 14,712,004 629,145,600 629,137,408
30 629,137,408 14,598,942 629,145,600 629,137,408
31 629,137,408 14,953,356 629,145,600 629,137,408
32 629,137,408 14,473,810 629,145,600 629,137,408
33 629,137,408 27,843,066 629,145,600 629,137,408
34 629,137,408 20,488,264 629,145,600 629,137,408
35 629,137,408 22,572,526 629,145,600 629,137,408
36 629,137,408 30,182,534 629,145,600 629,137,408
37 629,137,408 23,461,258 629,145,600 629,137,408
38 629,137,408 21,585,214 629,145,600 629,137,408
39 629,137,408 14,179,468 629,145,600 629,137,408
40 629,137,408 14,599,480 629,145,600 629,137,408
41 629,137,408 14,473,810 629,145,600 629,137,408
42 629,137,408 14,599,415 629,145,600 629,137,408
43 629,137,408 13,816,213 629,145,600 629,137,408
44 629,137,408 14,334,616 629,145,600 629,137,408
45 629,137,408 14,813,201 629,145,600 629,137,408
46 629,137,408 17,020,782 629,145,600 629,137,408
47 314,564,608 14,905,273 629,145,600 314,564,608
48 629,137,408 14,790,496 629,145,600 629,137,408
49 314,564,608 16,462,888 629,145,600 314,564,608
50 629,137,408 14,474,018 629,145,600 629,137,408
51 629,137,408 21,585,364 629,145,600 629,137,408
52 629,137,408 23,926,553 629,145,600 629,137,408
53 534,765,568 17,593,401 629,145,600 534,765,568
54 629,137,408 22,572,812 629,145,600 629,137,408
55 644,866,048 22,572,812 629,145,600 644,866,048
56 644,866,048 24,260,860 629,145,600 644,866,048
57 629,137,408 21,414,711 629,145,600 629,137,408
58 629,137,408 14,179,725 629,145,600 629,137,408
59 629,137,408 22,572,274 629,145,600 629,137,408
60 629,137,408 24,260,652 629,145,600 629,137,408
61 629,137,408 21,585,649 629,145,600 629,137,408
62 629,137,408 23,460,941 629,145,600 629,137,408
Rollback Segment Storage for DB: PROD Instance: prod1 Snaps: 239
-240
RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- --------------- --------------- ---------------
63 629,137,408 23,460,661 629,145,600 629,137,408
64 629,137,408 25,357,476 629,145,600 629,137,408
-------------------------------------------------------------
Latch Activity for DB: PROD Instance: prod1 Snaps: 239 -240
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics
for
Pct Avg
Pct
Get Get Slps
NoWait NoWait
Latch Name Requests Miss /Miss
Requests Miss
Get
Spin &
Latch Name Requests Misses Sleeps
Sleeps 1->4
-------------------------- -------------- ----------- -----------
------------
cache buffers chains 204,069,223 343,957 355
343621/317/1
9/0/0
messages 10,937,945 76,693 475
76220/471/2/
0/0
library cache 6,750,235 22,598 5,673
18325/2900/1
346/27/0
checkpoint queue latch 116,151,800 5,692 188
5504/188/0/0
/0
cache buffers lru chain 1,430,677 2,706 123
2583/123/0/0
/0
redo writing 3,831,107 2,562 3
2559/3/0/0/0
session idle bit 15,611,379 1,912 51
1861/51/0/0/
0
redo allocation 1,574,491 1,487 1
1486/1/0/0/0
sequence cache 397,568 210 1
209/1/0/0/0
shared pool 158,764 44 4
42/0/2/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: PROD Instance: prod1 Snaps: 239 -240
-> only latches with sleeps are shown
NoWait
Waiter
Latch Name Where Misses Sleeps
Sleeps
cache buffers chains kcbgtcr: kslbegin 0 221
281
cache buffers chains kcbrls: kslbegin 0 101
47
cache buffers chains kcbzib: multi-block read: 0 14
0
cache buffers chains kcbzgb: scan from tail. no 0 4
0
cache buffers chains kcbgcur: kslbegin 0 3
1
cache buffers chains kcbzib: finish free bufs 0 3
12
cache buffers chains kcbzwb 0 2
0
cache buffers chains kcbbxsv 0 1
6
cache buffers chains kcbchg: kslbegin: bufs not 0 1
1
cache buffers chains kcbget: exchange rls 0 1
0
cache buffers chains kcbnlc 0 1
3
cache buffers chains kcbget: pin buffer 0 1
4
cache buffers chains kcbget: exchange 0 1
0
cache buffers chains kcbchg: kslbegin: call CR 0 1
0
cache buffers lru chain kcbbiop: lru scan 0 115
0
cache buffers lru chain kcbzgb: multiple sets nowa 0 8
0
checkpoint queue latch kcbk0rrd: update recovery 0 183
0
checkpoint queue latch kcbkllrba: compute lowest 0 3
160
checkpoint queue latch kcbklbc: Link buffer into 0 2
2
library cache kglhdgn: child: 0 5,568
379
library cache kglic 0 26
62
library cache kglpnal: child: alloc spac 0 20
2,007
library cache kglpnal: child: before pro 0 16
2,141
library cache kglsca: parent 0 14
0
library cache kglget: child: KGLDSBRD 0 9
0
library cache kgllkdl: child: cleanup 0 5
6
library cache kglhdgc: child: 0 4
17
library cache kglget: child: KGLDSBYD 0 3
51
library cache kglpnc: child 0 2
188
library cache kglpin 0 1
36
library cache kglupc: child 0 1
196
library cache kglpnp: child 0 1
155
messages ksaamb: after wakeup 0 254
426
messages ksarcv: after wait 0 138
15
messages ksarcv 0 82
33
redo allocation kcrfwr: redo allocation 0 1
1
redo writing kcrfwcr 0 2
1
redo writing kcrfss 0 1
2
sequence cache kdnss 0 1
1
session idle bit ksupuc: set busy 0 31
27
session idle bit ksupuc: clear busy 0 20
24
shared pool kghalo 0 4
0
-------------------------------------------------------------
Dictionary Cache Stats for DB: PROD Instance: prod1 Snaps: 239 -240
->"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 Pct
Cache Requests Miss Requests Miss Req
Usage SGA
dc_constraints 0 0 0
0 0
dc_database_links 0 0 0
2 67
dc_files 16 0.0 0 0
699 99
dc_free_extents 808 1.2 20 0.0 21
3,040 100
dc_global_oids 0 0 0
0 0
dc_histogram_data 0 0 0
0 0
dc_histogram_data_valu 0 0 0
0 0
dc_histogram_defs 0 0 0
868 100
dc_object_ids 2,882 3.0 0 0
3,793 100
dc_objects 654 10.1 0 0
1,995 100
dc_outlines 0 0 0
0 0
dc_profiles 19 0.0 0 0
1 14
dc_rollback_segments 520 0.0 0 0
66 87
dc_segments 3,506 2.6 0 7
4,748 100
dc_sequence_grants 0 0 0
0 0
dc_sequences 6,457 0.0 0 6,412
101 98
dc_synonyms 31 32.3 0 0
58 87
dc_tablespace_quotas 887 0.3 0 227
46 92
dc_tablespaces 8,845 0.0 0 0
144 97
dc_used_extents 7 100.0 0 7
1,274 98
dc_user_grants 1,075 0.0 0 0
12 55
dc_usernames 115 0.0 0 0
8 38
dc_users 1,386 0.0 0 0
14 88
ifs_acl_cache_entries 0 0 0
0 0
-------------------------------------------------------------
Library Cache Activity for DB: PROD Instance: prod1 Snaps: 239 -240 ->"Pct Misses" should be very low
Get Pct Pin Pct
Invali-
Namespace Requests Miss Requests Miss Reloads
dations
BODY 35,763 0.0 35,763 0.0 0
0
CLUSTER 0 0 0
0
INDEX 0 0 0
0
OBJECT 0 0 0
0
PIPE 0 0 0
0
SQL AREA 88,051 0.3 2,123,959 0.0 3
0
TABLE/PROCEDURE 993 6.3 480,556 0.0 0
0
TRIGGER 86,032 0.0 86,032 0.0 0
0
-------------------------------------------------------------
SGA Memory Summary for DB: PROD Instance: prod1 Snaps: 239 -240
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 899,997,696 Fixed Size 73,620 Redo Buffers 1,056,768 Variable Size 703,852,544 ---------------- sum 1,604,980,628 -------------------------------------------------------------
SGA breakdown difference for DB: PROD Instance: prod1 Snaps: 239 -240
Pool Name Begin value End valueDifference
----------- ------------------------ -------------- --------------
-----------
java pool free memory 32,768 32,768
0
shared pool Checkpoint queue 1,177,920 1,177,920
0
shared pool DML locks 240,000 240,000
0
shared pool KGFF heap 393,120 393,120
0
shared pool KGK heap 32,084 32,084
0
shared pool KQLS heap 43,821,732 44,778,128
956,396
shared pool PL/SQL DIANA 842,440 842,440
0
shared pool PL/SQL MPCODE 1,144,088 1,147,656
3,568
shared pool PLS non-lib hp 2,096 2,096
0
shared pool State objects 990,832 990,832
0
shared pool branches 233,760 233,760
0
shared pool db_block_buffers 14,941,368 14,941,368
0
shared pool db_block_hash_buckets 1,888,888 1,888,888
0
shared pool db_files 370,264 370,264
0
shared pool db_handles 400,000 400,000
0
shared pool dictionary cache 5,198,988 5,289,572
90,584
shared pool enqueue_resources 360,000 360,000
0
shared pool event statistics per ses 3,072,720 3,072,720
0
shared pool fixed allocation callbac 1,952 1,952
0
shared pool free memory 292,070,008 285,814,956
-6,255,052
shared pool ktlbk state objects 416,444 416,444
0
shared pool library cache 79,145,772 80,470,552
1,324,780
shared pool message pool freequeue 124,552 124,552
0
shared pool messages 140,800 140,800
0
shared pool miscellaneous 2,937,532 2,933,360
-4,172
shared pool processes 633,600 633,600
0
shared pool sessions 1,908,060 1,908,060
0
shared pool simulator trace entries 320,000 320,000
0
shared pool sql area 249,237,984 253,116,992
3,879,008
shared pool state objects 205,048 205,048
0
shared pool table columns 50,568 51,352
784
shared pool table definiti 138,976 140,936
1,960
shared pool transaction_branches 178,848 178,848
0
shared pool transactions 867,916 867,916
0
shared pool trigger defini 312,368 314,512
2,144
shared pool trigger inform 484 484
0
db_block_buffers 899,997,696 899,997,696
0
fixed_sga 73,620 73,620
0
log_buffer 1,048,576 1,048,576
0
-------------------------------------------------------------
init.ora Parameters for DB: PROD Instance: prod1 Snaps: 239 -240
End
value
Parameter Name Begin value (if
different)
_eliminate_common_subexpr TRUE
audit_file_dest /u02/oracle/admin/prod/udump/prod
background_dump_dest /u02/oracle/admin/prod/bdump/prod
compatible 8.1.7.4
control_files /dev/rprod_0160_003, /dev/rprod_0
core_dump_dest /u02/oracle/admin/prod/cdump/prod
cursor_space_for_time TRUE
db_block_buffers 109863
db_block_lru_latches 16
db_block_size 8192
db_domain world
db_file_multiblock_read_count 16
db_files 1022
db_name prod
dml_locks 2000
enqueue_resources 5000
global_names FALSE
ifile /u02/oracle/admin/prod/pfile/conf
instance_name prod1
instance_number 1
java_pool_size 32768
job_queue_interval 10
job_queue_processes 5
log_archive_dest /u02/oracle/admin/prod/arch
log_archive_format redo%t_%s.arc
log_archive_min_succeed_dest 1
log_archive_start TRUE
log_buffer 1048576
max_dump_file_size 51200
nls_date_format DD-MON-YYYY HH24:MI:SS
open_cursors 4000
optimizer_index_cost_adj 1
optimizer_mode RULE
processes 800
remote_login_passwordfile EXCLUSIVE
rollback_segments rb01, rb02, rb03, rb04, rb05, rb0
session_cached_cursors 400
shared_pool_size 675000000
sort_area_retained_size 1048576
sort_area_size 1048576
thread 1
timed_statistics TRUE
user_dump_dest /u02/oracle/admin/prod/udump/prod
-------------------------------------------------------------
End of Report Received on Mon Oct 25 2004 - 15:43:36 CDT
 |
 |