 To: eygle
老大，帮我看看我的系统把，cpu占用居高不下！
STATSPACK report for
DB Name DB IdInstance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
PROD1759676554 PROD1 8.1.7.4.0 NOsun880
Snap Id Snap TimeSessions
------- ------------------ --------
Begin Snap: 29 03-Sep-05 11:00:54415
End Snap: 31 03-Sep-05 11:44:05415
Elapsed:43.18 (mins)
Cache Sizes
~~~~~~~~~~~
db_block_buffers: 180000log_buffer: 10485760
db_block_size: 8192shared_pool_size:500000000
Load Profile
~~~~~~~~~~~~Per Second Per Transaction
--------------- ---------------
Redo size:316,668.93 40,981.43
Logical reads: 42,925.535,555.17
Block changes:1,961.60253.86
Physical reads:2,187.06283.04
Physical writes:127.29 16.47
User calls:482.76 62.48
Parses: 79.09 10.24
Hard parses:1.160.15
Sorts:258.33 33.43
Logons:0.280.04
Executes:1,262.37163.37
Transactions:7.73
% Blocks changed per Read:4.57Recursive Call %: 80.79
Rollback per transaction %:2.55 Rows per Sort: 39.25
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.92 Redo NoWait %:100.00
BufferHit %: 94.90In-memory Sort %:100.00
Library Hit %: 99.91Soft Parse %: 98.53
Execute to Parse %: 93.73 Latch Hit %: 99.94
Parse CPU to Parse Elapsd %: 69.50 % Non-Parse CPU: 99.25
Shared Pool StatisticsBegin End
------------
Memory Usage %: 92.14 92.68
% SQL with executions>1: 65.53 66.47
% Memory for SQL w/exec>1: 64.99 61.63
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event WaitsTime (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
slave wait 78,8761,731,024 62.46
db file sequential read 1,393,126638,327 23.03
db file scattered read814,455230,9588.33
buffer busy waits94,178 83,2333.00
io done 115,547 34,9861.26
-------------------------------------------------------------
Wait Events for DB: PRODInstance: PRODSnaps: 29 -31
-> cs - centisecond -100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total WaitwaitWaits
Event Waits TimeoutsTime (cs)(ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
slave wait 78,8769,964 1,731,0242193.9
db file sequential read 1,393,1260 638,3275 69.6
db file scattered read814,4550 230,9583 40.7
buffer busy waits94,178083,23394.7
io done 115,54713234,98635.8
log file sync18,681516,92090.9
pipe put10910811,143 10220.0
latch free 18,005 11,758 9,36350.9
log file parallel write21,2263 8,19741.1
queue messages 27 27 2,781 10300.0
db file parallel write1,5740 1,21580.1
control file sequential read1,8760 93550.1
db file single write2,3070 47820.1
control file parallel write 1,6660 46330.1
log file switch completion 390 4021030.0
single-task message140 3192280.0
log file sequential read 25,6640 31101.3
SQL*Net more data to client10,2250 10400.5
write complete waits21 1035150.0
file open 5,68907400.3
enqueue 30401330.0
log file single write6403450.0
SQL*Net break/reset to clien42301900.0
direct path read 49,20001002.5
imm op19101010.0
db file parallel read 40 7 180.0
LGWR wait for redo copy 1571 500.0
direct path write 4,9670 400.2
file identify 1340 200.0
refresh controlfile command210 210.0
direct path write (lob) 1,0950 100.1
library cache pin 2320 100.0
SQL*Net message from client 1,264,158058,288,696461 63.1
pipe get 12,219 10,782 4,716,784 38600.6
wakeup time manager81 81 248,608306920.0
SQL*Net more data from clien 12,311037,457 300.6
SQL*Net message to client 1,264,1280 2390 63.1
-------------------------------------------------------------
Background Wait Events for DB: PRODInstance: PRODSnaps: 29 -31
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total WaitwaitWaits
Event Waits TimeoutsTime (cs)(ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
slave wait 78,8649,962 1,730,6002193.9
io done 115,54413234,98635.8
log file parallel write21,2243 8,19741.1
db file sequential read 2,6090 2,22590.1
db file parallel write1,5740 1,21580.1
db file scattered read1,3630 99970.1
control file sequential read1,6480 90550.1
db file single write2,3070 47820.1
control file parallel write 1,6660 46330.1
log file sequential read 25,6640 31101.3
latch free 29 2754 190.0
log file single write6403450.0
enqueue 10323200.0
file open 3,02902800.2
imm op19101010.0
LGWR wait for redo copy 1571 500.0
file identify 1340 200.0
refresh controlfile command 10 000.0
rdbms ipc message39,4072,331 1,371,8183482.0
pmon timer840840 258,779 30810.0
smon timer88 245,768 ######0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: PRODInstance: PRODSnaps: 29 -31
-> 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
Buffer GetsExecutionsGets per Exec% TotalHash Value
--------------- ------------ -------------- ------- ------------
32,058,8032 16,029,401.528.8 2826900378
SELECT COUNT(*) FROM CUX_PROD_INFORMATION_ALL_V CPIAVWHERE C
PIAV.DEPARTMENT_ID = :b1AND CPIAV.CLRQ >= TRUNC(:b2)AND CPIA
V.CLRQ <= TRUNC(:b3)AND NOT EXISTS(SELECT 'X' FROM CUX_WOR
K_TRANSACTIONS CWTWHERE CWT.DEPARTMENT_ID = :b1AND CWT.PROD_
DATE = CPIAV.CLRQ )
11,326,2051 11,326,205.010.2 2133737586
BEGIN MRP_EXP_WF.Launch_Workflow( :errbuf, :rc,:A0,:A1); END;
9,948,10219,948,102.0 8.9 2417740016
BEGIN MRP_EPI.INVENTORY_TURNS( :errbuf, :rc,:A0,:A1); END;
7,691,86871,098,838.3 6.9423950914
BEGIN WF_ENGINE.BACKGROUNDCONCURRENT( :errbuf, :rc,:A0,:A1,:A2,:
A3,:A4,:A5); END;
7,638,84171,091,263.0 6.9713340410
SELECT /*+ ORDERED USE_NL (WIASP WI WPAP WAP) INDEX
(WIASP WF_ITEM_ACTIVITY_STATUSES_N1)*/WIASP.ROWID ROW_ID FRO
M WF_ITEM_ACTIVITY_STATUSES WIASP,WF_ITEMS WI,WF_PROCESS_ACTIVIT
IES WPAP,WF_ACTIVITIES WAPWHERE WIASP.PROCESS_ACTIVITY = WPAP.
INSTANCE_IDAND WPAP.ACTIVITY_ITEM_TYPE = WAP.ITEM_TYPEAND WP
6,741,45141,685,362.8 6.1 1047997455
INSERT INTO MRP_BIS_INV_DETAIL ( COMPILE_DESIGNATOR,OWNING_ORG_I
D,ORGANIZATION_ID,SCHEDULE_DESIGNATOR,DETAIL_DATE,INVENTORY_ITEM
_ID,PROJECT_ID,TASK_ID,MDS_QUANTITY,INVENTORY_QUANTITY,SNAPSHOT_
COST,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY )
SELECT INV.COMPILE_DESIGNATOR,INV.OWNING_ORG_ID,INV.ORGANIZATI
5,607,611 29193,365.9 5.0 3248732514
begin mrp_manager_pk . mds_explode_in_process ( :in_process_item
s:ind_in_process_items , :request_id , :user_id ) ; END ;
5,606,549 29193,329.3 5.0 3780316299
INSERT INTO MRP_FORM_QUERY ( QUERY_ID,NUMBER1,NUMBER2,NUMBER3,LA
ST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY )SELEC
T :b1,UPD.INVENTORY_ITEM_ID,UPD.ORGANIZATION_ID,1,SYSDATE,-1,SYS
DATE,-1 FROM MRP_RELIEF_INTERFACE UPDWHERE UPD.RELIEF_TYPE =
1AND UPD.REQUEST_ID IS NOT NULL AND UPD.ERROR_MESSAGE IS NU
4,994,550150 33,297.0 4.5 2584117115
SELECT distinct ppf.employee_number,ppf.last_name FROM per_peopl
e_f ppf WHERE EXISTS(select 'X' from cux_employee_dis_inf_v cedi
v,bom_standard_operations bso WHERE cediv.person_id=ppf.person_i
d AND cediv.department_id=bso.department_id AND bso.operation_co
de(+)=:1 AND :2>=cediv.effective_date_from AND :3<=NVL(effective
4,937,941183,388 26.9 4.4140579188
SELECT NVL(SUM(DECODE(ORDER_TYPE,4,NVL(REC.DAILY_RATE,0),NVL(REC
SQL ordered by Gets for DB: PRODInstance: PRODSnaps: 29 -31
-> 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
Buffer GetsExecutionsGets per Exec% TotalHash Value
--------------- ------------ -------------- ------- ------------
.NEW_ORDER_QUANTITY,0))),0) * MRP_EPI.MRP_RESOURCE_COST(REC.INVE
NTORY_ITEM_ID,REC.ORGANIZATION_ID)FROM BOM_CALENDAR_DATES CA
L,MTL_PARAMETERS MTL,MRP_SYSTEM_ITEMS MSI,MRP_RECOMMENDATIONS RE
CWHERE REC.DISPOSITION_STATUS_TYPE = 1AND ((CALENDAR_DATE =
4,054,22122,027,110.5 3.6 3576174661
select substr(wac.description,1,4) c
j,g.segment1||'.'||g.segment2||'.'||g.segment3leib,
g.description ,sum(mmt.primary_quantity)
wcsl frommtl_system_items msi, m
tl_material_transactionsmmt, WIP_DISCRETE_JOBS_V
3,921,49321,960,746.5 3.5 2354237089
selecta.WIP_ENTITY_NAME, b.inventory_item_id ,
b.segment1 , b.description , c.REQUIRED_QUANTITY star
t_quantity , a.QUANTITY_COMPLETED , a.QUANTITY_REMAI
NING , a.CLASS_CODE , a.status_type_disp , a.schedul
ed_start_date , to_char(a.SCHEDULED_COMPLETION_DATE,'YYYY.MM
3,919,4215783,884.2 3.5 4185347570
select distinct to_char(b.Schedule_Ship_Date,'MM'
, --计划发货月份to_char(b.schedule_ship_date,'DD
', --计划发货日期h.Customer_Name,
--客户名称a.Order_Number, --订单号
b.attribute12 托盘,substr(a.order_number,5,4),e.
3,800,550200 19,002.8 3.4 1451069948
select wip_entity_id, wip_entity_name, description from wip_open
_discrete_jobs_val_v where organization_id = :1 order by wip_ent
ity_name
2,590,00012,590,000.0 2.3 1849947373
select 0 lb,c.hgsl,c.cpsl,c.zrfp,
c.fzrfp,c.fgslfrom (select msi.segment1,b.x
h,b.gx,b.hgsl,b.cpsl,b.zrfp,
b.fzrfp,b.fgsl from mtl_system_items msi,(
select a.item_id,a.xh,a.gx,sum(a.hgsl) h
2,169,791 10,158213.6 2.0229242746
INSERT INTO WF_ITEM_ATTRIBUTE_VALUES ( ITEM_TYPE,ITEM_KEY,NAME,T
EXT_VALUE,NUMBER_VALUE,DATE_VALUE )SELECT :b1,:b2,WIA.NAME,WIA
.TEXT_DEFAULT,WIA.NUMBER_DEFAULT,WIA.DATE_DEFAULT FROM WF_ITEM
_ATTRIBUTES WIAWHERE WIA.ITEM_TYPE = :b1
1,974,87811,974,878.0 1.8 2552604604
INSERT INTO MRP_BIS_INV_DETAIL ( COMPILE_DESIGNATOR,OWNING_ORG_I
-------------------------------------------------------------
SQL ordered by Reads for DB: PRODInstance: PRODSnaps: 29 -31
-> End Disk Reads Threshold:1000
Physical ReadsExecutionsReads per Exec % TotalHash Value
--------------- ------------ -------------- ------- ------------
1,140,1992005,701.020.1 1451069948
select wip_entity_id, wip_entity_name, description from wip_open
_discrete_jobs_val_v where organization_id = :1 order by wip_ent
ity_name
938,466 89 10,544.616.6 62919221
SELECT CW_T.DEPARTMENT_ID,CW_T.PROCESS_ID,CW_T.PERSON_ID,CW_T.PR
ODUCT_CODE,BD.DEPARTMENT_CODE WORKSHOP_NAME,BSO.OPERATION_CODE P
ROCESS_CODE,PPF.EMPLOYEE_NUMBER EMPLOYEE_ID,PPF.LAST_NAME EMPLOY
EE_NAME,SUM(NVL(CW_T.EXEC_AMOUNT,0)) EXEC_AMOUNT,SUM(NVL(CW_T.PR
OD_QUANTITY,0)) PROD_QUANTITY,SUM(NVL(CW_T.WAST_QUANTITY,0)) WAS
371,6931371,693.0 6.6 2133737586
BEGIN MRP_EXP_WF.Launch_Workflow( :errbuf, :rc,:A0,:A1); END;
346,4511346,451.0 6.1 2907419704
SELECT EXP.EXCEPTION_ID,EXP.ORGANIZATION_ID,EXP.INVENTORY_ITEM_I
D,EXP.EXCEPTION_TYPE,EXP.ORGANIZATION_CODE,EXP.ITEM_SEGMENTS,EXP
.EXCEPTION_TYPE_TEXT,NVL(EXP.PROJECT_NUMBER,'N/A',NVL(EXP.TO_PR
OJECT_NUMBER,'N/A',NVL(EXP.TASK_NUMBER,'N/A',NVL(EXP.TO_TASK_N
UMBER,'N/A',EXP.PLANNING_GROUP,EXP.DUE_DATE,EXP.FROM_DATE,EXP.T
301,0274 75,256.8 5.3 1241474151
select distinct subinventory_code from mtl_material_transactions
291,4097 41,629.9 5.1423950914
BEGIN WF_ENGINE.BACKGROUNDCONCURRENT( :errbuf, :rc,:A0,:A1,:A2,:
A3,:A4,:A5); END;
291,0277 41,575.3 5.1713340410
SELECT /*+ ORDERED USE_NL (WIASP WI WPAP WAP) INDEX
(WIASP WF_ITEM_ACTIVITY_STATUSES_N1)*/WIASP.ROWID ROW_ID FRO
M WF_ITEM_ACTIVITY_STATUSES WIASP,WF_ITEMS WI,WF_PROCESS_ACTIVIT
IES WPAP,WF_ACTIVITIES WAPWHERE WIASP.PROCESS_ACTIVITY = WPAP.
INSTANCE_IDAND WPAP.ACTIVITY_ITEM_TYPE = WAP.ITEM_TYPEAND WP
226,034 288,072.6 4.0360277607
SELECT distinct request_idFROM mrp_sales_order_updatesWHER
Eprocess_status = 3ANDrequest_id IS NOT NULLANDerr
or_message IS NULL
222,989 287,963.9 3.9 1335169110
begin mrp_manager_pk . compute_sales_order_changes ( :request_id
, :user_id ) ; END ;
188,1602 94,080.0 3.3 2826900378
SELECT COUNT(*) FROM CUX_PROD_INFORMATION_ALL_V CPIAVWHERE C
PIAV.DEPARTMENT_ID = :b1AND CPIAV.CLRQ >= TRUNC(:b2)AND CPIA
V.CLRQ <= TRUNC(:b3)AND NOT EXISTS(SELECT 'X' FROM CUX_WOR
K_TRANSACTIONS CWTWHERE CWT.DEPARTMENT_ID = :b1AND CWT.PROD_
DATE = CPIAV.CLRQ )
171,9001171,900.0 3.0 3584012540
SELECT MSI.SEGMENT1,MSI.DESCRIPTION,MMT.SUBINV
SQL ordered by Reads for DB: PRODInstance: PRODSnaps: 29 -31
-> End Disk Reads Threshold:1000
Physical ReadsExecutionsReads per Exec % TotalHash Value
--------------- ------------ -------------- ------- ------------
ENTORY_CODE,SUM(MMT.TRANSACTION_QUANTITY)FROM MTL_SYSTEM
_ITEMS_B MSI,MTL_MATERIAL_TRANSACTIONSMMT-
-MTL_TRANSACTION_TYPESMTT WHERE MSI.ORGANIZATION_ID=83
AND MMT.ORGANIZATION_ID=83 AND MSI.INVENTORY_ITEM_ID=MMT.IN
153,463 295,291.8 2.7 3248732514
begin mrp_manager_pk . mds_explode_in_process ( :in_process_item
s:ind_in_process_items , :request_id , :user_id ) ; END ;
153,463 295,291.8 2.7 3780316299
INSERT INTO MRP_FORM_QUERY ( QUERY_ID,NUMBER1,NUMBER2,NUMBER3,LA
ST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY )SELEC
T :b1,UPD.INVENTORY_ITEM_ID,UPD.ORGANIZATION_ID,1,SYSDATE,-1,SYS
DATE,-1 FROM MRP_RELIEF_INTERFACE UPDWHERE UPD.RELIEF_TYPE =
1AND UPD.REQUEST_ID IS NOT NULL AND UPD.ERROR_MESSAGE IS NU
153,2021153,202.0 2.7 2417740016
BEGIN MRP_EPI.INVENTORY_TURNS( :errbuf, :rc,:A0,:A1); END;
145,6521145,652.0 2.6 1253833548
SELECT ALTERNATE_BOM_DESIGNATOR,BOM_REVISION,BOM_REVISION_DATE,W
IP_SUPPLY_TYPE,WIP_SUPPLY_TYPE_DISP,ALTERNATE_ROUTING_DESIGNATOR
,ROUTING_REVISION,ROUTING_REVISION_DATE,COMPLETION_SUBINVENTORY,
PROJECT_NAME,TASK_NAME,DUE_DATE,PRIORITY,DUE_DATE_PENALTY,DUE_DA
TE_TOLERANCE,WIP_ENTITY_NAME,JOB_TYPE,END_ITEM_UNIT_NUMBER,CLASS
130,0692 65,034.5 2.3 1367263628
select distinct flow_status_code from oe_order_lines_all
108,7584 27,189.5 1.9 1047997455
INSERT INTO MRP_BIS_INV_DETAIL ( COMPILE_DESIGNATOR,OWNING_ORG_I
D,ORGANIZATION_ID,SCHEDULE_DESIGNATOR,DETAIL_DATE,INVENTORY_ITEM
_ID,PROJECT_ID,TASK_ID,MDS_QUANTITY,INVENTORY_QUANTITY,SNAPSHOT_
COST,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY )
SELECT INV.COMPILE_DESIGNATOR,INV.OWNING_ORG_ID,INV.ORGANIZATI
102,4171102,417.0 1.8 3930970491
select msi.segment1,b.xh,b.gx,b.hgsl,
b.cpsl,b.zrfp,b.fzrfp,b.fgsl
from mtl_system_items msi,(select a.item_id,a.xh,
a.gx,sum(a.hgsl) hgsl,sum(a.cpsl) cpsl,
sum(a.zrfp) zrfp,sum(a.fzrfp) fzrfp,sum(a.fg
102,141 137,857.0 1.8 3118028303
INSERT INTO MSC_FORM_QUERY ( QUERY_ID,LAST_UPDATE_DATE,LAST_UPDA
TED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN )SELECT SALE
-------------------------------------------------------------
SQL ordered by Executions for DB: PRODInstance: PRODSnaps: 29 -31
-> End Executions Threshold: 100
Executions Rows ProcessedRows per Exec Hash Value
------------ ---------------- ---------------- ------------
254,244253,4431.04757731
SELECT WIA.TYPE,WIA.FORMAT FROM WF_ITEM_ATTRIBUTES WIAWHERE
WIA.ITEM_TYPE = :b1AND WIA.NAME = :b2
223,240223,2811.0 1275213040
UPDATE WF_ITEM_ATTRIBUTE_VALUES SET TEXT_VALUE=:b1 WHERE ITEM_TY
PE = :b2AND ITEM_KEY = :b3AND NAME = :b4
197,267197,2671.0 2245345989
SELECT NVL(CST.ITEM_COST,0) FROM CST_COST_TYPES CCT,MTL_PARAME
TERS MTL,CST_ITEM_COSTS CSTWHERE (CST.COST_TYPE_ID = CCT.COST_
TYPE_IDOR (CST.COST_TYPE_ID = CCT.DEFAULT_COST_TYPE_IDAND (N
OT EXISTS(SELECT 'Primary Cost Type Row' FROM CST_ITEM_COSTS
CST1WHERE CST1.INVENTORY_ITEM_ID = CST.INVENTORY_ITEM_IDAND
183,3883,7340.0140579188
SELECT NVL(SUM(DECODE(ORDER_TYPE,4,NVL(REC.DAILY_RATE,0),NVL(REC
.NEW_ORDER_QUANTITY,0))),0) * MRP_EPI.MRP_RESOURCE_COST(REC.INVE
NTORY_ITEM_ID,REC.ORGANIZATION_ID)FROM BOM_CALENDAR_DATES CA
L,MTL_PARAMETERS MTL,MRP_SYSTEM_ITEMS MSI,MRP_RECOMMENDATIONS RE
CWHERE REC.DISPOSITION_STATUS_TYPE = 1AND ((CALENDAR_DATE =
183,3884370.0850819904
SELECT NVL(SUM(NVL(REC.NEW_ORDER_QUANTITY,0)),0) * MRP_EPI.MRP_I
TEM_COST(REC.INVENTORY_ITEM_ID,REC.ORGANIZATION_ID)FROM MRP_
SYSTEM_ITEMS MSI,MRP_RECOMMENDATIONS RECWHERE REC.DISPOSITION_
STATUS_TYPE = 1AND TRUNC(REC.NEW_SCHEDULE_DATE) BETWEEN :b1 AN
D :b2AND REC.ORDER_TYPE IN ( 1,2,5,8) AND MSI.PLANNING_MAKE_
135,782135,7801.0885722162
(SELECT STATUS_FLAG FROM IGI_GCC_INSTALLED_OPTIONSWHERE UPPE
R(OPTION_NAME) = UPPER(:b1) ) UNION (SELECT STATUS_FLAG FROM I
GI_GCC_GL_FA_INST_OPSWHERE UPPER(OPTION_NAME) = UPPER(:b1) )
109,643109,6431.0 2151823199
SELECT WPA.PROCESS_ITEM_TYPE,WPA.PROCESS_NAME FROM WF_PROCESS_
ACTIVITIES WPAWHERE WPA.INSTANCE_ID = :b1
108,506 59,2060.5716923151
SELECT WIAS.ACTIVITY_STATUS,WIAS.ACTIVITY_RESULT_CODE,WIAS.ASSIG
NED_USER,WIAS.NOTIFICATION_ID,WIAS.BEGIN_DATE,WIAS.END_DATE,WIAS
.DUE_DATE,WIAS.ERROR_NAME,WIAS.ERROR_MESSAGE,WIAS.ERROR_STACK
FROM WF_ITEM_ACTIVITY_STATUSES WIASWHERE WIAS.ITEM_TYPE = :b1
AND WIAS.ITEM_KEY = :b2AND WIAS.PROCESS_ACTIVITY = :b3
101,208101,3311.0982145717
UPDATE WF_ITEM_ATTRIBUTE_VALUES SET NUMBER_VALUE=:b1 WHERE ITEM_
TYPE = :b2AND ITEM_KEY = :b3AND NAME = :b4
91,694 91,6941.0 2543072890
SELECT SUM(NVL(SCHEDULE_QUANTITY,0)) FROM MRP_SCHEDULE_DATES
WHERE SCHEDULE_DESIGNATOR = :b1AND SCHEDULE_LEVEL = 3AND ORG
ANIZATION_ID = :b2AND INVENTORY_ITEM_ID = :b3AND SCHEDULE_DA
TE < :b4
SQL ordered by Executions for DB: PRODInstance: PRODSnaps: 29 -31
-> End Executions Threshold: 100
Executions Rows ProcessedRows per Exec Hash Value
------------ ---------------- ---------------- ------------
69,819 69,8191.0577235476
SELECT WA.TYPE,WA.RERUN,WA.COST,WA.ERROR_ITEM_TYPE,WA.ERROR_PROC
ESS,WA.EXPAND_ROLE,WA.FUNCTION,NVL(WA.FUNCTION_TYPE,'PL/SQL',WA
.MESSAGE,WA.ITEM_TYPE,WA.EVENT_NAME,WA.DIRECTION,WPA.PERFORM_ROL
E,WPA.PERFORM_ROLE_TYPE,WPA.START_END FROM WF_PROCESS_ACTIVITI
ES WPA,WF_ACTIVITIES WAWHERE WPA.INSTANCE_ID = :b1AND WPA.AC
69,2259,3700.1373737229
SELECT PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTION_VALUESWH
ERE PROFILE_OPTION_ID = :b1AND APPLICATION_ID = :b2AND LEVEL
_ID = :b3AND LEVEL_VALUE = :b4
67,966 67,9661.0 1889886359
SELECT NUMBER_VALUE FROM WF_ITEM_ATTRIBUTE_VALUESWHERE ITEM_
TYPE = :b1AND ITEM_KEY = :b2AND NAME = :b3
60,037 60,0231.0 2025520065
UPDATE WF_ITEM_ACTIVITY_STATUSES SET ACTIVITY_STATUS=:b1,ACTIVIT
Y_RESULT_CODE=NVL(:b2,ACTIVITY_RESULT_CODE),BEGIN_DATE=NVL(:b3,B
EGIN_DATE),END_DATE=NVL(:b4,END_DATE),DUE_DATE=DECODE(:b3,'',DUE
_DATE,:b6),OUTBOUND_QUEUE_ID=:b7,EXECUTION_TIME=DECODE(:b1,:b9,:
b10,:b11,NVL(EXECUTION_TIME,:b10),:b13,NVL(EXECUTION_TIME,:b10),
59,90470.0801006415
SELECT WAAV.VALUE_TYPE,SUBSTRB(WAAV.TEXT_VALUE,1,30),WAAV.NUMBER
_VALUE FROM WF_ACTIVITY_ATTR_VALUES WAAVWHERE WAAV.PROCESS_A
CTIVITY_ID = :b1AND WAAV.NAME = :b2
59,490 110.0 2722962473
SELECT WAAV.VALUE_TYPE,SUBSTRB(WAAV.TEXT_VALUE,1,30),WAAV.DATE_V
ALUE FROM WF_ACTIVITY_ATTR_VALUES WAAVWHERE WAAV.PROCESS_ACT
IVITY_ID = :b1AND WAAV.NAME = :b2
59,458 59,4581.0 2292634451
INSERT INTO WF_ITEM_ACTIVITY_STATUSES ( ITEM_TYPE,ITEM_KEY,PROCE
SS_ACTIVITY,ACTIVITY_STATUS,ACTIVITY_RESULT_CODE,ASSIGNED_USER,N
OTIFICATION_ID,BEGIN_DATE,END_DATE,DUE_DATE,EXECUTION_TIME,OUTBO
UND_QUEUE_ID )VALUES ( :b1,:b2,:b3,:b4,:b5, NULL , NULL ,:b6,:
b7,:b8,DECODE(:b4,:b10,:b11,:b12,:b11,:b14,:b11, NULL ),:b16)
59,39500.0 3441714163
select rownuminto :b0from mrp_assembly_operations ops where
((((ops.inventory_item_id<>ops.using_assembly_item_id and rownum
=1) and ops.compile_designator=:b1) and ops.organization_id=:b2
-------------------------------------------------------------
SQL ordered by Sharable Memory for DB: PRODInstance: PRODSnaps: 29 -31
-> End Sharable Memory Threshold: 1048576
Sharable Mem (b)Executions% TotalHash Value
---------------- ------------ ------- ------------
1,834,5531 0.3 3706299082
select * from PO_REQ_TAX_LINES_SUMMARY_V where trx_header_id = :
b1ORDER BY trx_header_id, trx_line_id, trx_shipment_id , trx_d
istribution_id
1,799,9236 0.3 1306330817
SELECT TO_CHAR(B.SCHEDULE_SHIP_DATE,'MM' MM,TO_CHAR(B.SCHEDULE_
SHIP_DATE,'DD' DD,H.CUSTOMER_NAME CUST_NAME,A.ORDER_NUMBER,SUBS
TR(A.ORDER_NUMBER,5,4) DDH,F.MEANING FYFF,G.MEANING THFF,A.PACKI
NG_INSTRUCTIONS BZSM,E.SEGMENT1 ITEM,E.DESCRIPTION,J.FOB,B.LINE_
ID,SUBSTR(C.SOURCE_LINE_NUMBER,1,2) HH,B.ORDERED_QUANTITY XQSL,N
1,195,8841 0.2 1720182503
SELECT NAME,SUBSTRB(DISPLAY_NAME,1,80) P_DISPLAY_NAME FROM WF_
USERSWHERE ORIG_SYSTEM = :b1AND ORIG_SYSTEM_ID = :b2
-------------------------------------------------------------
Instance Activity Stats for DB: PRODInstance: PRODSnaps: 29 -31
StatisticTotal per Secondper Trans
--------------------------------- ---------------- ------------ ------------
CPU used by this session 997,707385.1 49.8
CPU used when call started 1,000,778386.3 50.0
CR blocks created 10,0843.90.5
Cached Commit SCN referenced 00.00.0
DBWR buffers scanned 772,180298.0 38.6
DBWR checkpoint buffers written 73,336 28.33.7
DBWR checkpoints160.00.0
DBWR free buffers found754,736291.3 37.7
DBWR lru scans 9390.40.1
DBWR make free requests9690.40.1
DBWR revisited being-written buff 720.00.0
DBWR summed scan depth 772,180298.0 38.6
DBWR transaction table writes1650.10.0
DBWR undo block writes46,640 18.02.3
SQL*Net roundtrips to/from client1,261,749487.0 63.0
background checkpoints completed160.00.0
background checkpoints started160.00.0
background timeouts2,5781.00.1
branch node splits350.00.0
buffer is not pinned count55,128,821 21,277.02,753.6
buffer is pinned count89,455,117 34,525.34,468.1
bytes received via SQL*Net from c182,157,484 70,303.99,098.3
bytes sent via SQL*Net to client 370,703,746143,073.6 18,515.8
calls to get snapshot scn: kcmgss3,346,0841,291.4167.1
calls to kcmgas 41,179 15.92.1
calls to kcmgcs 38,166 14.71.9
change write time3,4041.30.2
cleanouts and rollbacks - consist7,2442.80.4
cleanouts only - consistent read 7,9353.10.4
cluster key scan block gets 20,8128.01.0
cluster key scans5,1842.00.3
commit cleanout failures: block l4,3681.70.2
commit cleanout failures: buffer 20.00.0
commit cleanout failures: callbac 250.00.0
commit cleanout failures: cannot 00.00.0
commit cleanouts 136,417 52.76.8
commit cleanouts successfully com132,022 51.06.6
consistent changes 520,002200.7 26.0
consistent gets103,162,655 39,815.85,152.7
current blocks converted for CR
cursor authentications 1,0210.40.1
data blocks consistent reads - un462,680178.6 23.1
db block changes 5,082,4991,961.6253.9
db block gets8,058,4643,110.2402.5
deferred (CURRENT) block cleanout 26,304 10.21.3
dirty buffers inspected3,0581.20.2
enqueue conversions2,1940.90.1
enqueue releases92,305 35.64.6
enqueue requests93,174 36.04.7
enqueue timeouts 8850.30.0
enqueue waits00.00.0
exchange deadlocks 00.00.0
execute count3,270,8041,262.4163.4
free buffer inspected 56,542 21.82.8
free buffer requested5,500,4732,122.9274.7
hot buffers moved to head of LRU 517,138199.6 25.8
Instance Activity Stats for DB: PRODInstance: PRODSnaps: 29 -31
StatisticTotal per Secondper Trans
--------------------------------- ---------------- ------------ ------------
immediate (CR) block cleanout app 15,1795.90.8
immediate (CURRENT) block cleanou 54,165 20.92.7
leaf node splits 6,8862.70.3
logons cumulative7380.30.0
messages received 38,955 15.02.0
messages sent 38,955 15.02.0
no buffer to keep pinned count 00.00.0
no work - consistent read gets71,482,295 27,588.73,570.4
opened cursors cumulative139,145 53.77.0
parse count (hard) 3,0131.20.2
parse count (total)204,919 79.1 10.2
parse time cpu 7,4372.90.4
parse time elapsed10,7014.10.5
physical reads 5,666,6692,187.1283.0
physical reads direct241,210 93.1 12.1
physical reads direct (lob)1,1030.40.1
physical writes329,797127.3 16.5
physical writes direct 235,003 90.7 11.7
physical writes direct (lob) 1,1030.40.1
physical writes non checkpoint 324,439125.2 16.2
pinned buffers inspected52,595 20.32.6
prefetched blocks3,219,4051,242.5160.8
prefetched blocks aged out before10.00.0
process last non-idle time 737,345,097,756 ############ ############
recursive calls5,260,3632,030.2262.7
recursive cpu usage394,421152.2 19.7
redo blocks written1,665,398642.8 83.2
redo buffer allocation retries370.00.0
redo entries 2,537,463979.3126.7
redo log space requests 390.00.0
redo log space wait time 4020.20.0
redo ordering marks 600.00.0
redo size820,489,188316,668.9 40,981.4
redo synch time 16,9856.60.9
redo synch writes 18,8677.30.9
redo wastage 5,301,3082,046.1264.8
redo write time 22,6698.81.1
redo writer latching time50.00.0
redo writes 21,2078.21.1
rollback changes - undo records a1,1440.40.1
rollbacks only - consistent read 3,6351.40.2
rows fetched via callback5,361,7532,069.4267.8
session connect time 737,345,097,756 ############ ############
session cursor cache hits145,652 56.27.3
session logical reads111,220,036 42,925.55,555.2
session pga memory 2,238,662,820864,015.0111,815.7
session pga memory max 2,404,542,892928,036.6120,101.0
session uga memory max 794,504,076306,639.9 39,683.5
sorts (disk)290.00.0
sorts (memory) 669,312258.3 33.4
sorts (rows)26,273,922 10,140.51,312.3
summed dirty queue length 17,4986.80.9
switch current to new buffer
table fetch by rowid55,859,155 21,558.92,790.0
table fetch continued row 10,327,6493,986.0515.8
table scan blocks gotten10,835,1274,181.8541.2
Instance Activity Stats for DB: PRODInstance: PRODSnaps: 29 -31
StatisticTotal per Secondper Trans
--------------------------------- ---------------- ------------ ------------
table scan rows gotten 376,096,456145,154.9 18,785.1
table scans (long tables)6570.30.0
table scans (short tables) 500,883193.3 25.0
total file opens 5,6892.20.3
transaction rollbacks 310.00.0
transaction tables consistent rea 150.00.0
transaction tables consistent rea 10,2183.90.5
user calls 1,250,826482.8 62.5
user commits19,5107.51.0
user rollbacks 5110.20.0
write clones created in backgroun10.00.0
write clones created in foregroun1750.10.0
-------------------------------------------------------------
Tablespace IO Stats for DB: PRODInstance: PRODSnaps: 29 -31
->ordered by IOs (Reads + Writes) desc
Tablespace
------------------------------
AvAv AvAvBuffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/sWaits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
WIPD
738,602 2850.9 2.08680 37,4717.8
APPLSYSX
208,829817.3 1.0 11,36343 13.3
QAD
208,148804.9 2.234802,261 15.0
INVD
197,648764.8 2.97640 55 15.5
CUXD
187,290724.0 5.2 270 19,7948.5
MRPD
171,738663.6 5.5 12,197500.0
ONTD
115,687453.3 1.9 890 15,8866.3
MRPX
97,746389.8 1.08,31233,8828.8
INVX
94,234364.2 1.02,35012316.5
RBS
18,984 74.9 1.0 46,806 1840.0
TEMP
52,16120102.8 4.6 13,29057 14.3
APPLSYSD
54,001218.9 1.05,7522 15 17.3
POD
18,970 73.3 3.9 66000.0
WIPX
16,976 7 11.5 1.01,5851 12,636 14.8
WSHD
17,549 71.7 4.51850 10 12.0
BOMD
10,209 4 10.6 1.8344000.0
QAX
9,274 4 12.2 1.0216000.0
ONTX
7,892 36.0 1.020701,8647.1
SYSTEM
6,075 2 11.4 2.53300 20 16.0
QPD
5,622 23.8 3.80000.0
BOMX
4,424 29.7 1.0 10000.0
WSHX
3,367 16.8 1.0271080.0
PERFSTAT
2,024 17.5 1.0810000.0
CUXX
1,982 13.6 1.0 64000.0
CRPX
1,625 19.5 1.00000.0
POX
1,185 0 10.1 1.0148000.0
Tablespace IO Stats for DB: PRODInstance: PRODSnaps: 29 -31
->ordered by IOs (Reads + Writes) desc
Tablespace
------------------------------
AvAv AvAvBuffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/sWaits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
QPX
1,169 04.2 1.00000.0
HRD
1,074 06.5 1.6001 30.0
TEMP2
678 00.4 6.017905 28.0
ARD
386 0 18.5 1.90000.0
ICXD
85 0 13.4 1.2118000.0
ARX
193 0 13.2 1.00000.0
GLD
71 0 15.1 2.60000.0
ICXX
34 0 17.4 1.0 36000.0
HRX
55 0 10.5 1.00000.0
IGID
40 00.0 1.00000.0
CTXD
16 0 26.9 1.0 14000.0
APD
28 05.7 1.01000.0
WMSX
24 0 10.8 1.00000.0
GLX
19 0 12.1 1.00000.0
WMSD
15 0 17.3 1.00000.0
FAD
14 09.3 1.00000.0
AXD
13 00.0 1.00000.0
JTFD
13 0 23.8 1.40000.0
APX
5 06.0 1.00000.0
ECXD
0 00.03000.0
ECXX
0 00.03000.0
PAD
3 06.7 1.00000.0
PJMX
3 0 13.3 1.00000.0
PJMD
2 0 10.0 1.00000.0
GMIX
1 0 10.0 1.00000.0
OEX
1 0 10.0 1.00000.0
Tablespace IO Stats for DB: PRODInstance: PRODSnaps: 29 -31
->ordered by IOs (Reads + Writes) desc
Tablespace
------------------------------
AvAv AvAvBuffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/sWaits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
-------------------------------------------------------------
File IO Stats for DB: PRODInstance: PRODSnaps: 29 -31
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
AvAv AvAvBuffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/sWaits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
APD/data/PROD/ora/proddata/apd01.dbf
28 05.7 1.0100
APPLSYSD /data/PROD/ora/proddata/applsysd01.dbf
13,693 58.4 1.12,378168.3
/data/PROD/ora/proddata/applsysd02.dbf
9,603 49.5 1.097906 26.7
/data/PROD/ora/proddata/applsysd03.dbf
13,894 58.9 1.095303 16.7
/data/PROD/ora/proddata/applsysd04.dbf
16,798 68.9 1.01,42910
/data/PROD/ora/proddata/applsysd05.dbf
13 0 10.8 1.0 1300
APPLSYSX /data/PROD/ora/proddata/applsysx01.dbf
35,223147.4 1.01,13001 30.0
/data/PROD/ora/proddata/applsysx02.dbf
37,624157.7 1.01,35010
/data/PROD/ora/proddata/applsysx03.dbf
38,337154.9 1.065300
/data/PROD/ora/proddata/applsysx04.dbf
38,810155.0 1.062800
/data/PROD/ora/proddata/applsysx05.dbf
43,59217 10.6 1.05,289225.0
/data/PROD/ora/proddata/applsysx06.dbf
15,243 68.7 1.02,31310
APX/data/PROD/ora/proddata/apx01.dbf
5 06.0 1.0000
ARD/data/PROD/ora/proddata/ard01.dbf
386 0 18.5 1.9000
ARX/data/PROD/ora/proddata/arx01.dbf
193 0 13.2 1.0000
AXD/data/PROD/ora/proddata/axd01.dbf
13 00.0 1.0000
BOMD /data/PROD/ora/proddata/bomd01.dbf
4,498 2 12.4 1.921900
/data/PROD/ora/proddata/bomd02.dbf
4,084 2 10.0 1.6 8600
/data/PROD/ora/proddata/bomd03.dbf
1,607 16.9 2.1 3100
/data/PROD/ora/proddata/bomd04.dbf
20 0 22.0 2.6800
BOMX /data/PROD/ora/proddata/bomx01.dbf
4,424 29.7 1.0 1000
CRPX /data/PROD/ora/proddata/crpx01.dbf
File IO Stats for DB: PRODInstance: PRODSnaps: 29 -31
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
AvAv AvAvBuffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/sWaits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
1,625 19.5 1.0000
CTXD /data/PROD/ora/proddata/ctxd01.dbf
16 0 26.9 1.0 1400
CUXD /data/PROD/ora/proddata/CUXD01.dbf
187,290724.0 5.2 270 19,7948.5
CUXX /data/PROD/ora/proddata/CUXX01.dbf
1,982 13.6 1.0 6400
ECXD /data/PROD/ora/proddata/ecxd01.dbf
0 0 300
ECXX /data/PROD/ora/proddata/ecxx01.dbf
0 0 300
FAD/data/PROD/ora/proddata/fad01.dbf
14 09.3 1.0000
GLD/data/PROD/ora/proddata/gld01.dbf
71 0 15.1 2.6000
GLX/data/PROD/ora/proddata/glx01.dbf
19 0 12.1 1.0000
GMIX /data/PROD/ora/proddata/gmix01.dbf
1 0 10.0 1.0000
HRD/data/PROD/ora/proddata/hrd01.dbf
1,074 06.5 1.6001 30.0
HRX/data/PROD/ora/proddata/hrx01.dbf
55 0 10.5 1.0000
ICXD /data/PROD/ora/proddata/icxd01.dbf
85 0 13.4 1.211800
ICXX /data/PROD/ora/proddata/icxx01.dbf
34 0 17.4 1.0 3600
IGID /data/PROD/ora/proddata/igid01.dbf
40 00.0 1.0000
INVD /data/PROD/ora/proddata/invd01.dbf
108,804424.7 3.01820 41 18.3
/data/PROD/ora/proddata/invd02.dbf
85,394335.0 2.94880 147.1
/data/PROD/ora/proddata/invd03.dbf
3,450 14.5 1.5 9400
INVX /data/PROD/ora/proddata/invx01.dbf
File IO Stats for DB: PRODInstance: PRODSnaps: 29 -31
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
AvAv AvAvBuffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/sWaits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
31,091124.2 1.05440 465.9
INVX /data/PROD/ora/proddata/invx02.dbf
29,239113.8 1.01850 547.0
/data/PROD/ora/proddata/invx03.dbf
30,924124.4 1.068101146.2
/data/PROD/ora/proddata/invx04.dbf
2,980 16.3 1.09400 178.2
JTFD /data/PROD/ora/proddata/jtfd01.dbf
13 0 23.8 1.4000
MRPD /data/PROD/ora/proddata/mrpd01.dbf
48,449193.7 5.74,60720
/data/PROD/ora/proddata/mrpd02.dbf
31,268124.0 5.42,34910
/data/PROD/ora/proddata/mrpd03.dbf
29,270113.3 6.11,36110
/data/PROD/ora/proddata/mrpd04.dbf
44,096173.5 5.52,67210
/data/PROD/ora/proddata/mrpd05.dbf
18,655 73.6 4.61,20800
MRPX /data/PROD/ora/proddata/mrpx01.dbf
62,52524 11.2 1.05,49622,7579.4
/data/PROD/ora/proddata/mrpx02.dbf
35,221147.3 1.02,81611,1257.5
OEX/data/PROD/ora/proddata/oex01.dbf
1 0 10.0 1.0000
ONTD /data/PROD/ora/proddata/ontd01.dbf
115,687453.3 1.9 890 15,8866.3
ONTX /data/PROD/ora/proddata/ontx01.dbf
7,892 36.0 1.020701,8647.1
PAD/data/PROD/ora/proddata/pad01.dbf
3 06.7 1.0000
PERFSTAT /data/PROD/ora/proddata/perfstat01.dbf
2,024 17.5 1.081000
PJMD /data/PROD/ora/proddata/pjmd01.dbf
2 0 10.0 1.0000
PJMX /data/PROD/ora/proddata/pjmx01.dbf
3 0 13.3 1.0000
POD/data/PROD/ora/proddata/pod01.dbf
18,970 73.3 3.9 6600
POX/data/PROD/ora/proddata/pox01.dbf
File IO Stats for DB: PRODInstance: PRODSnaps: 29 -31
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
AvAv AvAvBuffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/sWaits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
1,185 0 10.1 1.014800
QAD/data/PROD/ora/proddata/qad01.dbf
208,148804.9 2.234802,261 15.0
QAX/data/PROD/ora/proddata/qax01.dbf
9,274 4 12.2 1.021600
QPD/data/PROD/ora/proddata/qpd01.dbf
5,622 23.8 3.8000
QPX/data/PROD/ora/proddata/qpx01.dbf
1,169 04.2 1.0000
RBS/data/PROD/ora/proddata/rbs01.dbf
5,662 24.6 1.0 13,780510.0
/data/PROD/ora/proddata/rbs02.dbf
5,551 25.5 1.0 13,83650
/data/PROD/ora/proddata/rbs03.dbf
6,474 24.2 1.0 13,39550
/data/PROD/ora/proddata/rbs04.dbf
1,297 16.5 1.05,795230.0
SYSTEM /data/PROD/ora/proddata/system01.dbf
1,247 0 11.3 2.6 3007 14.3
/data/PROD/ora/proddata/system02.dbf
1,146 0 12.3 2.6 6305 28.0
/data/PROD/ora/proddata/system03.dbf
1,112 0 12.1 2.6 7004 15.0
/data/PROD/ora/proddata/system04.dbf
1,235 0 10.6 2.6111020.0
/data/PROD/ora/proddata/system05.dbf
1,007 0 11.0 2.2 4602 10.0
/data/PROD/ora/proddata/system06.dbf
328 0 11.0 1.9 1000
TEMP /data/PROD/ora/proddata/temp01.dbf
52,16120102.8 4.6 13,29057 14.3
TEMP2/data/PROD/ora/proddata/temp2.dbf
678 00.4 6.017905 28.0
WIPD /data/PROD/ora/proddata/wipd01.dbf
330,368 1280.8 2.01390 13,7327.3
/data/PROD/ora/proddata/wipd02.dbf
248,336961.0 2.1 420 13,8947.6
/data/PROD/ora/proddata/wipd03.dbf
159,898621.3 1.968709,8458.8
WIPX /data/PROD/ora/proddata/wipx01.dbf
7,222 3 11.1 1.031806,095 14.9
/data/PROD/ora/proddata/wipx02.dbf
File IO Stats for DB: PRODInstance: PRODSnaps: 29 -31
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
AvAv AvAvBuffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/sWaits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
6,559 3 11.9 1.024805,506 14.6
WIPX /data/PROD/ora/proddata/wipx03.dbf
3,195 1 11.5 1.01,01901,035 15.0
WMSD /data/PROD/ora/proddata/wmsd01.dbf
15 0 17.3 1.0000
WMSX /data/PROD/ora/proddata/wmsx01.dbf
24 0 10.8 1.0000
WSHD /data/PROD/ora/proddata/wshd01.dbf
17,549 71.7 4.51850 10 12.0
WSHX /data/PROD/ora/proddata/wshx01.dbf
3,367 16.8 1.0271080.0
-------------------------------------------------------------
Buffer Pool Statistics for DB: PRODInstance: PRODSnaps: 29 -31
-> Pools D: default pool,K: keep pool,R: recycle pool
FreeWrite Buffer
BufferConsistentPhysical PhysicalBuffer Complete Busy
PGetsGets Reads Writes WaitsWaitsWaits
- ----------- ------------- ----------- ---------- ------- -------- ----------
D 5,503,37477,696,153 5,426,848 93,727 02 94,177
-------------------------------------------------------------
Buffer wait Statistics for DB: PRODInstance: PRODSnaps: 29 -31
-> ordered by wait time desc, waits desc
Tot WaitAvg
ClassWaitsTime (cs) Time (cs)
------------------ ----------- ---------- ---------
data block94,133 83,263 1
undo header40 0
-------------------------------------------------------------
Rollback Segment Stats for DB: PRODInstance: PRODSnaps: 29 -31
->A high value for "Pct Waits" suggests more rollback segments may be required
Trans TablePct Undo Bytes
RBS No Gets Waits WrittenWrapsShrinksExtends
------ ------------ ------- --------------- -------- -------- --------
0 10.00.00 0000
1 26,187.00.0184,155,138 4100
27,482.00.00 3,412,846100
3204.00.0021,536000
49,359.00.00 3,626,528200
56,456.00.0036,962100
6 11,365.00.00 6,598,622400
74,575.00.00 2,962,712200
83,495.00.00 3,924,298200
9 17,224.00.0030,551,932 1700
10138.00.0047,182000
112,491.00.00 774,784100
12869.00.00 739,280000
132,191.00.00 409,874000
14784.00.00 322,086000
15 58,944.00.00 184,518,92210400
163,954.00.00 6,810,350300
-------------------------------------------------------------
Rollback Segment Storage for DB: PRODInstance: PRODSnaps: 29 -31
->Optimal Size should be larger than Avg Active
RBS NoSegment SizeAvg ActiveOptimal SizeMaximum Size
------ --------------- --------------- --------------- ---------------
0 401,408 0 401,408
1 666,656,76855,965,395 666,656,768
2 468,574,208 5,511,617 468,574,208
3 204,464,128 4,903,254 204,464,128
4 285,401,088 3,684,569 285,401,088
548,979,96812,228,75648,979,968
6 261,971,96818,980,844 261,971,968
7 604,889,08810,714,646 604,889,088
842,590,20820,602,94642,590,208
978,798,848 5,422,57578,798,848
1042,590,208 3,558,11742,590,208
1142,590,208 4,647,19242,590,208
1242,590,20818,001,60142,590,208
13 440,885,248 174,639,432 440,885,248
1442,590,208 5,518,76042,590,208
15 485,613,568 244,940,842 485,613,568
16 523,952,128 5,314,510 523,952,128
-------------------------------------------------------------
Latch Activity for DB: PRODInstance: PRODSnaps: 29 -31
->"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
PctAvg Pct
GetGet Slps NoWait NoWait
Latch Name RequestsMiss/Miss Requests Miss
----------------------------- -------------- ------ ------ ------------ ------
JOX SGA heap latch 2,1660.0 0
Token Manager 11,2710.0 1,2690.0
active checkpoint queue latch 18,0380.0 0
archive control 320.0 0
archive process latch 320.0 0
begin backup scn array 1,0950.0 0
cache buffer handles 1,138,3000.00.00
cache buffers chains 215,370,0670.00.09,751,4580.0
cache buffers lru chain5,935,6920.30.05,488,5680.4
channel handle pool latch8600.0 0
channel operations parent lat1,2710.0 0
checkpoint queue latch 420,4340.00.10
direct msg latch 136,7730.00.00
dml lock allocation 81,4380.0 0
done queue latch 103,9180.20.1136,1950.3
enqueue hash chains187,3430.00.00
enqueues 257,9820.00.00
event group latch4110.0 0
file number translation table2,3730.0 0
i/o slave adaptor1080.0 1,2690.0
job_queue_processes parameter 400.0 0
ksfv messages239,8480.0 0
ktm global data80.0 0
kwqit: protect wakeup time890.0 0
latch wait list9,5634.30.09,0340.1
library cache 25,723,9430.20.2 10,1291.9
library cache load lock1,7420.0 0
list of block allocation84,6860.00.00
loader state object freelist 4,9330.0 0
longop free list 653,8200.00.10
messages 155,8480.40.00
multiblock read objects2,650,6100.10.00
ncodef allocation latch 400.0 0
process allocation 4110.0 4110.0
process group creation 8490.0 0
redo allocation2,580,2110.00.00
redo writing 125,5771.40.00
row cache objects1,261,9700.00.10
sequence cache21,3390.0 0
session allocation 382,7540.00.10
session idle bit 2,557,3690.00.10
session queue latch136,2590.00.00
session switching1,0030.0 0
shared pool753,5580.10.90
sort extent pool15,9400.0 0
temp lob duration state obj a1,3040.0 0
temporary table state object450.0 0
transaction allocation 149,7020.00.00
transaction branch allocation 400.0 0
undo global data 196,3070.00.10
Latch Activity for DB: PRODInstance: PRODSnaps: 29 -31
->"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
PctAvg Pct
GetGet Slps NoWait NoWait
Latch Name RequestsMiss/Miss Requests Miss
----------------------------- -------------- ------ ------ ------------ ------
user lock2,7660.20.80
-------------------------------------------------------------
Latch Sleep breakdown for DB: PRODInstance: PRODSnaps: 29 -31
-> ordered by misses desc
GetSpin &
Latch NameRequests MissesSleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
cache buffers chains215,370,06776,700 2,276 74557/2027/1
06/10/0
library cache25,723,94363,29913,976 54764/3521/4
598/416/0
cache buffers lru chain 5,935,69219,953 686 19273/674/6/
0/0
redo writing125,577 1,72719 1708/19/0/0/
0
multiblock read objects 2,650,610 1,51848 1471/46/1/0/
0
shared pool 753,558 1,086 924 643/9/390/44
/0
messages155,848 675 7 668/7/0/0/0
redo allocation 2,580,211 53110 521/10/0/0/0
latch wait list 9,563 414 2 412/2/0/0/0
row cache objects 1,261,970 27917 262/17/0/0/0
cache buffer handles1,138,300 191 2 189/2/0/0/0
done queue latch103,918 15910 150/8/1/0/0
longop free list653,820 136 9 127/9/0/0/0
session allocation382,75480 5 75/5/0/0/0
session idle bit2,557,36969 4 65/4/0/0/0
checkpoint queue latch420,43446 5 41/5/0/0/0
undo global data196,30713 1 12/1/0/0/0
user lock 2,766 5 4 1/4/0/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: PRODInstance: PRODSnaps: 29 -31
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses SleepsSleeps
------------------------ -------------------------- ------- ---------- -------
cache buffer handles kcbzfs 01 1
cache buffer handles kcbzgs 01 1
cache buffers chains kcbgtcr: kslbegin01,573 1,791
cache buffers chains kcbrls: kslbegin 0390 331
cache buffers chains kcbgtcr011617
cache buffers chains kcbzwb 0 9367
cache buffers chains kcbzib: multi-block read:0 53 0
cache buffers chains kcbchg: kslbegin: bufs not 0 1715
cache buffers chains kcbzib: finish free bufs 0 1012
cache buffers chains kcbzgb: scan from tail. no 09 0
cache buffers chains kcbgcur: kslbegin0610
cache buffers chains kcbchg: kslbegin: call CR0511
cache buffers chains kcbget: pin buffer 03 5
cache buffers chains kcbibr 01 3
cache buffers lru chainkcbzgb: multiple sets nowa 0358 0
cache buffers lru chainkcbbiop: lru scan0239 0
cache buffers lru chainkcbzar: KSLNBEGIN0 70 556
cache buffers lru chainkcbzgm 0 15 125
cache buffers lru chainkcbbwlru 03 0
cache buffers lru chainkcbzgb: posted for free bu 01 0
checkpoint queue latch kcbk0rrd: update recovery03 0
checkpoint queue latch kcbbwthc: thread checkpoin 02 0
cost functionkzulrl 04 4
done queue latch ksfvsioc: 2 nowait 0 10 0
latch wait listkslfre 62 2
library cachekglhdgn: child:08,121 972
library cachekglpnc: child0836 3,606
library cachekglic0802 123
library cachekglpnal: child: before pro 0703 1,679
library cachekglget: child: KGLDSBRD042340
library cachekglget: child: KGLDSBYD0336 1,766
library cachekglpnal: child: alloc spac 0255 561
library cachekglupc: child0240 2,619
library cachekglpnp: child0210 1,615
library cachekglpin 0 66 428
library cachekgllkdl: child: cleanup0 49 118
library cachekgldti: 2child 0 3425
library cachekgllkdl: child: free pin 0 24 178
library cachekglhdgc: child:0 12 7
library cachekglobld: child:0472
library cachekglati 03 0
library cachekglobpn: child:01 7
library cachekglpsl: child01 3
library cachekglpnal: child: check gran 01 1
longop free list ksuloget 09 9
messages ksarcv: after wait 04 1
messages ksaamb: after wakeup 03 5
multiblock read objectskcbzib: MBRGET 0 3018
multiblock read objectskcbzib: MBRFRE 0 1830
redo allocationkcrfwr: redo allocation0 1010
redo writing kcrfsr 0 16 1
redo writing kcrfss 0218
redo writing kcrfwcr01 0
Latch Miss Sources for DB: PRODInstance: PRODSnaps: 29 -31
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses SleepsSleeps
------------------------ -------------------------- ------- ---------- -------
row cache objectskqrpre: find obj 0 17 9
session allocation ksuprc 02 6
session allocation ksuxds: KSUSFCLC not set 02 1
session allocation ksudlc 01 6
session idle bit ksupuc: clear busy 02 1
session idle bit ksupuc: set busy 01 3
session idle bit ksuxds 01 0
shared poolkghfrunp: clatch: nowait 01,820 0
shared poolkghfrunp: alloc: clatch no 0908 0
shared poolkghfrunp: clatch: wait 0 32 160
shared poolkghupr105 779
shared poolkghalo 03 110
shared poolkghfnd: min scan 03 0
shared poolkghfrunp: alloc: wait03 2
shared poolkghalp 0117
shared poolkghfre 0114
undo global data ktubnd 01 1
-------------------------------------------------------------
Dictionary Cache Stats for DB: PRODInstance: PRODSnaps: 29 -31
->"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 PctModFinalPct
CacheRequests Miss RequestsMissReqUsageSGA
---------------------- ------------ ------ -------- ----- -------- ------ ----
dc_constraints0 0000
dc_database_links 0 0000
dc_files0 00377 98
dc_free_extents880.000 11 46
dc_global_oids5660.000 18 51
dc_histogram_data 1700.000 62 90
dc_histogram_data_valu 650.000 28 97
dc_histogram_defs42,0430.400 ######100
dc_object_ids 227,4910.0003,921100
dc_objects 27,3270.5005,570100
dc_outlines 0 0000
dc_profiles 6570.0001 14
dc_rollback_segments2880.000 19 76
dc_segments49,4200.0043,526100
dc_sequence_grants3894.6001,114 93
dc_sequences3460.60275134 97
dc_synonyms10,7680.4001,179100
dc_tablespace_quotas 375.40 10 24 62
dc_tablespaces5,9200.000344 97
dc_used_extents 0 0000
dc_user_grants 14,6630.000 32 47
dc_usernames 14,4170.000 76 84
dc_users 37,1600.000 88 97
ifs_acl_cache_entries 0 0000
-------------------------------------------------------------
Library Cache Activity for DB: PRODInstance: PRODSnaps: 29 -31
->"Pct Misses"should be very low
Get PctPinPct Invali-
Namespace RequestsMiss Requests Miss Reloadsdations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY28,2710.1 27,3820.150
CLUSTER 170.0 250.000
INDEX2,0500.02,0500.000
OBJECT 0 0 00
PIPE 4,1641.04,6420.900
SQL AREA38,1887.03,544,3460.23380
TABLE/PROCEDURE662,0510.03,914,0500.04630
TRIGGER4660.04661.780
-------------------------------------------------------------
SGA Memory Summary for DB: PRODInstance: PRODSnaps: 29 -31
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers1,474,560,000
Fixed Size 73,888
Redo Buffers 10,502,144
Variable Size 587,907,072
----------------
sum 2,073,043,104
-------------------------------------------------------------
SGA breakdown difference for DB: PRODInstance: PRODSnaps: 29 -31
PoolNameBegin valueEnd valueDifference
----------- ------------------------ -------------- -------------- -----------
java pool free memory44,007,424 44,007,424 0
java pool memory in use 5,996,5445,996,544 0
shared pool DML locks 1,200,0001,200,000 0
shared pool KGFF heap 248,636248,636 0
shared pool KGK heap 21,576 21,576 0
shared pool KQLS heap15,026,884 11,846,956-3,179,928
shared pool PL/SQL DIANA8,164,5127,974,292-190,220
shared pool PL/SQL MPCODE30,690,176 29,354,072-1,336,104
shared pool PL/SQL SOURCE12,804 12,804 0
shared pool PLS non-lib hp2,0962,096 0
shared pool State objects 615,376615,376 0
shared pool db_block_buffers 24,480,000 24,480,000 0
shared pool db_block_hash_buckets 3,142,2003,142,200 0
shared pool dictionary cache 13,137,476 13,309,976 172,500
shared pool enqueue_resources 2,304,0002,304,000 0
shared pool event statistics per ses1,926,9601,926,960 0
shared pool fixed allocation callbac1,2801,280 0
shared pool free memory42,258,672 39,386,212-2,872,460
shared pool joxlod: in ehe 99,860 99,860 0
shared pool joxlod: in phe 12,500 12,500 0
shared pool joxs heap init4,2484,248 0
shared pool ksfv subheap4,2484,248 0
shared pool library cache68,859,692 69,003,980 144,288
shared pool miscellaneous 6,070,3445,907,832-162,512
shared pool ruleset defini347,460347,460 0
shared pool sessions1,196,5801,196,580 0
shared pool sql area317,824,884325,226,964 7,402,080
shared pool table columns52,888 44,900-7,988
shared pool table definiti 28,252 25,112-3,140
shared pool temporary tabl 27,984 31,632 3,648
shared pool trigger defini 36,932 32,144-4,788
shared pool trigger inform700700 0
shared pool type object de 69,768 99,91230,144
db_block_buffers1,474,560,0001,474,560,000 0
fixed_sga73,888 73,888 0
log_buffer 10,485,760 10,485,760 0
-------------------------------------------------------------
init.ora Parameters for DB: PRODInstance: PRODSnaps: 29 -31
End value
Parameter NameBegin value (if different)
----------------------------- --------------------------------- --------------
_complex_view_merging TRUE
_fast_full_scan_enabled FALSE
_like_with_bind_as_equality TRUE
_new_initial_join_ordersTRUE
_optimizer_mode_force TRUE
_optimizer_undo_changes FALSE
_or_expand_nvl_predicateTRUE
_ordered_nested_loopTRUE
_push_join_predicateTRUE
_push_join_union_view TRUE
_shared_pool_reserved_min_all 4100
_sort_elimination_cost_ratio5
_sqlexec_progression_cost 2147483647
_system_trig_enabledTRUE
_table_scan_cost_plus_one TRUE
_trace_files_public TRUE
_use_column_stats_for_functio TRUE
always_anti_joinNESTED_LOOPS
always_semi_joinNESTED_LOOPS
aq_tm_processes 1
background_dump_dest/data/PROD/ora/proddb/8.1.7/admin
compatible8.1.7
control_files /data/PROD/ora/proddata/cntrl01.d
core_dump_dest/data/PROD/ora/proddb/8.1.7/admin
db_block_buffers180000
db_block_size 8192
db_file_multiblock_read_count 8
db_files512
db_name PROD
dbwr_io_slaves4
disk_asynch_ioFALSE
dml_locks 10000
enqueue_resources 32000
hash_area_size4194304
ifile ?/dbs/ifilecbo.ora
java_pool_size50000000
job_queue_interval90
job_queue_processes 2
log_archive_dest/data/bak/arc
log_archive_formatARC%T%S.arc
log_archive_max_processes 1
log_archive_start TRUE
log_buffer10485760
log_checkpoint_interval 100000
log_checkpoint_timeout72000
log_checkpoints_to_alertTRUE
max_dump_file_size20480
max_enabled_roles 100
nls_date_format DD-MON-RR
nls_languageamerican
nls_numeric_characters.,
nls_sortbinary
nls_territory america
open_cursors800
optimizer_features_enable 8.1.7
init.ora Parameters for DB: PRODInstance: PRODSnaps: 29 -31
End value
Parameter NameBegin value (if different)
----------------------------- --------------------------------- --------------
optimizer_index_caching 90
optimizer_max_permutations2000
optimizer_modeCHOOSE
optimizer_percent_parallel0
parallel_max_servers8
parallel_min_servers0
processes 500
query_rewrite_enabled TRUE
rollback_segments rbs01, rbs02, rbs03, rbs04, rbs05
row_locking always
session_cached_cursors200
shared_pool_reserved_size 50000000
shared_pool_size500000000
sort_area_size2097152
sort_multiblock_read_count32
timed_statisticsTRUE
user_dump_dest/data/PROD/ora/proddb/8.1.7/admin
utl_file_dir/usr/tmp
-------------------------------------------------------------
End of Report
From: remem
2005.09.03 00:53
|
eygle.com
我要留言
To: eygle
回复
编辑
To: reman
[/COLOR] 
To: aloe vera
To: John
[/COLOR] 
To: 
To: Aloe vera
To: ALL
To: ROSE