Home » RDBMS Server » Performance Tuning » buffer busy wait (Linux 2.6.18-92.1.13.el5)
| buffer busy wait [message #528507] |
Tue, 25 October 2011 06:34  |
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
|
Senior Member |
|
|
Hello Expert,
Looking for one instance, it is experienced with BUFFER BUSY WAIT.
EVENT TIME_SPENT
------------------------------ ----------
latch: cache buffers chains 0
buffer busy waits 341898
read by other session 39
log file switch completion 12
db file sequential read 95578
enq: TX - index contention 36
external table read 473
external table write 2
external table misc IO 2
SQL*Net message to client 0
events in waitclass Other 0
CPU used when call started 0
Thus looking to the issue, i check further for the block that causing wait event.
SQL> SELECT p1 "file#", p2 "block#", p3 "wait class#"
2 FROM v$session_wait
3 WHERE event ='buffer busy waits';
file# block# wait class#
---------- ---------- -----------
3 2 13
3 2 13
3 2 13
3 2 13
3 2 13
3 2 13
3 2 13
SQL> SELECT relative_fno, owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = &file
AND &block BETWEEN block_id AND block_id + blocks - 1; 2 3 4
Enter value for file: 3
old 3: WHERE file_id = &file
new 3: WHERE file_id = 3
Enter value for block: 2
old 4: AND &block BETWEEN block_id AND block_id + blocks - 1
new 4: AND 2 BETWEEN block_id AND block_id + blocks - 1
no rows selected
Please suggest, as i got no rows selected. Please suggest for further analysis.
Thanks.
|
|
|
|
|
|
|
|
| Re: buffer busy wait [message #528536 is a reply to message #528517] |
Tue, 25 October 2011 10:36  |
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
|
Senior Member |
|
|
Below are the four queries running in parallel to insert data into the F_SVC_ORDER_TASK table.
MERGE INTO F_SVC_ORDER_TASK O
USING (SELECT DISTINCT * FROM E30_F_SVC_ORDER_TASK_I1) E
ON (O.SVC_ORDER_TASK_DW_ID = E.SVC_ORDER_TASK_DW_ID)
WHEN NOT MATCHED THEN
INSERT (SVC_ORDER_TASK_DW_ID,
CORP_ID,
HOUSE_DW_ID,
CUST_DW_ID,
DATE_ID,
SVC_ORDER_TYPE_ID,
TECH_DW_ID,
CORP_NO_TRUCK_DW_ID,
SVC_ORDER_TASK_TRUCK_ROLL_ID,
SVC_ORDER_TASK_SEQ_ID,
SVC_ORDER_TASK_OUTLET,
CORP_SVC_ORDER_TASK_CODE_DW_ID,
DW_ADD_DATE,
DW_UPDATE_DATE,
SS_HOUSE_ID,
SS_CUST_ID,
SS_WPCNT,
SVC_ORDER_TASK_CNT,
SVC_ORDER_TASK_PTS,
SVC_ORDER_TASK_RESCHEDULE_CNT,
SVC_ORDER_DW_ID,
ORDER_DONE_DATE,
ORDER_ENTRY_DATE,
ORDER_FINALIZE_DATE,
ORDER_SCHEDULE_DATE,
ORDER_BILL_DATE,
ORDER_WORK_BEGIN_DATE,
ORDER_COMPLETE_DATE,
ORDER_WORK_START_BY_DATE,
SVC_SCHEDULE_AREA_ID,
OPERATOR_DW_ID,
SALES_REP_DW_ID,
SVC_CYCLE_DATE,
WIP_PERIOD,
NON_PAY_ORDER,
NEW_DROP,
CORP_SCHEDULE_TIME_DW_ID,
CORP_CHANGE_RSN_DW_ID,
CORP_DISCONNECT_RSN_DW_ID,
CORP_SALES_METHOD_DW_ID,
CORP_CAMPAIGN_DW_ID,
CORP_ORDER_RSN_DW_ID,
CORP_CANCEL_RSN_DW_ID,
SVC_BUSINESS_DAYS_DONE,
COMPLETE_FLAG,
QUOTA_GROUP,
RESCHEDULE_NOT_DONE_ID,
SVC_CUSTOM_TASKS,
SVC_COMPLIANCE_ID,
LINE_OF_BUSINESS_ID,
CUST_CABLE_DW_ID,
LAST_CHANGE_OPR_DW_ID,
CDW_DATE,
LAST_ORDER_SCHEDULE_DATE,
ROUTING_ID,
SELF_INSTALL_FLAG)
VALUES (E.SVC_ORDER_TASK_DW_ID,
E.CORP_ID,
E.HOUSE_DW_ID,
E.CUST_DW_ID,
TO_DATE(E.DATE_ID, 'YYYY-MM-DD HH24:MI:SS'),
E.SVC_ORDER_TYPE_ID,
E.TECH_DW_ID,
E.CORP_NO_TRUCK_DW_ID,
E.SVC_ORDER_TASK_TRUCK_ROLL_ID,
E.SVC_ORDER_TASK_SEQ_ID,
E.SVC_ORDER_TASK_OUTLET,
E.CORP_SVC_ORDER_TASK_CODE_DW_ID,
TO_TIMESTAMP(E.DW_ADD_DATE, 'YYYY-MM-DD HH24:MI:SS.FF9'),
TO_TIMESTAMP(E.DW_UPDATE_DATE, 'YYYY-MM-DD HH24:MI:SS.FF9'),
nvl(rtrim(E.SS_HOUSE_ID), ' '),
E.SS_CUST_ID,
E.SS_WPCNT,
E.SVC_ORDER_TASK_CNT,
E.SVC_ORDER_TASK_PTS,
E.SVC_ORDER_TASK_RESCHEDULE_CNT,
E.SVC_ORDER_DW_ID,
TO_DATE(E.ORDER_DONE_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_ENTRY_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_FINALIZE_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_SCHEDULE_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_BILL_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_WORK_BEGIN_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_COMPLETE_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_WORK_START_BY_DATE, 'YYYY-MM-DD HH24:MI:SS'),
nvl(rtrim(E.SVC_SCHEDULE_AREA_ID), ' '),
E.OPERATOR_DW_ID,
E.SALES_REP_DW_ID,
TO_DATE(E.SVC_CYCLE_DATE, 'YYYY-MM-DD HH24:MI:SS'),
E.WIP_PERIOD,
E.NON_PAY_ORDER,
E.NEW_DROP,
E.CORP_SCHEDULE_TIME_DW_ID,
E.CORP_CHANGE_RSN_DW_ID,
E.CORP_DISCONNECT_RSN_DW_ID,
E.CORP_SALES_METHOD_DW_ID,
E.CORP_CAMPAIGN_DW_ID,
E.CORP_ORDER_RSN_DW_ID,
E.CORP_CANCEL_RSN_DW_ID,
E.SVC_BUSINESS_DAYS_DONE,
E.COMPLETE_FLAG,
E.QUOTA_GROUP,
E.RESCHEDULE_NOT_DONE_ID,
nvl(rtrim(E.SVC_CUSTOM_TASKS), ' '),
E.SVC_COMPLIANCE_ID,
E.LINE_OF_BUSINESS_ID,
E.CUST_CABLE_DW_ID,
E.LAST_CHANGE_OPR_DW_ID,
TO_DATE(E.CDW_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.LAST_ORDER_SCHEDULE_DATE, 'YYYY-MM-DD HH24:MI:SS'),
E.ROUTING_ID,
E.SELF_INSTALL_FLAG)
LOG ERRORS INTO D30_F_SVC_ORDER_TASK_I1 ('MERGE') REJECT LIMIT UNLIMITED;
MERGE INTO F_SVC_ORDER_TASK O
USING (SELECT DISTINCT * FROM E30_F_SVC_ORDER_TASK_I2) E
ON (O.SVC_ORDER_TASK_DW_ID = E.SVC_ORDER_TASK_DW_ID)
WHEN NOT MATCHED THEN
INSERT (SVC_ORDER_TASK_DW_ID,
CORP_ID,
HOUSE_DW_ID,
CUST_DW_ID,
DATE_ID,
SVC_ORDER_TYPE_ID,
TECH_DW_ID,
CORP_NO_TRUCK_DW_ID,
SVC_ORDER_TASK_TRUCK_ROLL_ID,
SVC_ORDER_TASK_SEQ_ID,
SVC_ORDER_TASK_OUTLET,
CORP_SVC_ORDER_TASK_CODE_DW_ID,
DW_ADD_DATE,
DW_UPDATE_DATE,
SS_HOUSE_ID,
SS_CUST_ID,
SS_WPCNT,
SVC_ORDER_TASK_CNT,
SVC_ORDER_TASK_PTS,
SVC_ORDER_TASK_RESCHEDULE_CNT,
SVC_ORDER_DW_ID,
ORDER_DONE_DATE,
ORDER_ENTRY_DATE,
ORDER_FINALIZE_DATE,
ORDER_SCHEDULE_DATE,
ORDER_BILL_DATE,
ORDER_WORK_BEGIN_DATE,
ORDER_COMPLETE_DATE,
ORDER_WORK_START_BY_DATE,
SVC_SCHEDULE_AREA_ID,
OPERATOR_DW_ID,
SALES_REP_DW_ID,
SVC_CYCLE_DATE,
WIP_PERIOD,
NON_PAY_ORDER,
NEW_DROP,
CORP_SCHEDULE_TIME_DW_ID,
CORP_CHANGE_RSN_DW_ID,
CORP_DISCONNECT_RSN_DW_ID,
CORP_SALES_METHOD_DW_ID,
CORP_CAMPAIGN_DW_ID,
CORP_ORDER_RSN_DW_ID,
CORP_CANCEL_RSN_DW_ID,
SVC_BUSINESS_DAYS_DONE,
COMPLETE_FLAG,
QUOTA_GROUP,
RESCHEDULE_NOT_DONE_ID,
SVC_CUSTOM_TASKS,
SVC_COMPLIANCE_ID,
LINE_OF_BUSINESS_ID,
CUST_CABLE_DW_ID,
LAST_CHANGE_OPR_DW_ID,
CDW_DATE,
LAST_ORDER_SCHEDULE_DATE,
ROUTING_ID,
SELF_INSTALL_FLAG)
VALUES (E.SVC_ORDER_TASK_DW_ID,
E.CORP_ID,
E.HOUSE_DW_ID,
E.CUST_DW_ID,
TO_DATE(E.DATE_ID, 'YYYY-MM-DD HH24:MI:SS'),
E.SVC_ORDER_TYPE_ID,
E.TECH_DW_ID,
E.CORP_NO_TRUCK_DW_ID,
E.SVC_ORDER_TASK_TRUCK_ROLL_ID,
E.SVC_ORDER_TASK_SEQ_ID,
E.SVC_ORDER_TASK_OUTLET,
E.CORP_SVC_ORDER_TASK_CODE_DW_ID,
TO_TIMESTAMP(E.DW_ADD_DATE, 'YYYY-MM-DD HH24:MI:SS.FF9'),
TO_TIMESTAMP(E.DW_UPDATE_DATE, 'YYYY-MM-DD HH24:MI:SS.FF9'),
nvl(rtrim(E.SS_HOUSE_ID), ' '),
E.SS_CUST_ID,
E.SS_WPCNT,
E.SVC_ORDER_TASK_CNT,
E.SVC_ORDER_TASK_PTS,
E.SVC_ORDER_TASK_RESCHEDULE_CNT,
E.SVC_ORDER_DW_ID,
TO_DATE(E.ORDER_DONE_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_ENTRY_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_FINALIZE_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_SCHEDULE_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_BILL_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_WORK_BEGIN_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_COMPLETE_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_WORK_START_BY_DATE, 'YYYY-MM-DD HH24:MI:SS'),
nvl(rtrim(E.SVC_SCHEDULE_AREA_ID), ' '),
E.OPERATOR_DW_ID,
E.SALES_REP_DW_ID,
TO_DATE(E.SVC_CYCLE_DATE, 'YYYY-MM-DD HH24:MI:SS'),
E.WIP_PERIOD,
E.NON_PAY_ORDER,
E.NEW_DROP,
E.CORP_SCHEDULE_TIME_DW_ID,
E.CORP_CHANGE_RSN_DW_ID,
E.CORP_DISCONNECT_RSN_DW_ID,
E.CORP_SALES_METHOD_DW_ID,
E.CORP_CAMPAIGN_DW_ID,
E.CORP_ORDER_RSN_DW_ID,
E.CORP_CANCEL_RSN_DW_ID,
E.SVC_BUSINESS_DAYS_DONE,
E.COMPLETE_FLAG,
E.QUOTA_GROUP,
E.RESCHEDULE_NOT_DONE_ID,
nvl(rtrim(E.SVC_CUSTOM_TASKS), ' '),
E.SVC_COMPLIANCE_ID,
E.LINE_OF_BUSINESS_ID,
E.CUST_CABLE_DW_ID,
E.LAST_CHANGE_OPR_DW_ID,
TO_DATE(E.CDW_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.LAST_ORDER_SCHEDULE_DATE, 'YYYY-MM-DD HH24:MI:SS'),
E.ROUTING_ID,
E.SELF_INSTALL_FLAG)
LOG ERRORS INTO D30_F_SVC_ORDER_TASK_I2 ('MERGE') REJECT LIMIT UNLIMITED;
MERGE INTO F_SVC_ORDER_TASK O
USING (SELECT DISTINCT * FROM E30_F_SVC_ORDER_TASK_I3) E
ON (O.SVC_ORDER_TASK_DW_ID = E.SVC_ORDER_TASK_DW_ID)
WHEN NOT MATCHED THEN
INSERT (SVC_ORDER_TASK_DW_ID,
CORP_ID,
HOUSE_DW_ID,
CUST_DW_ID,
DATE_ID,
SVC_ORDER_TYPE_ID,
TECH_DW_ID,
CORP_NO_TRUCK_DW_ID,
SVC_ORDER_TASK_TRUCK_ROLL_ID,
SVC_ORDER_TASK_SEQ_ID,
SVC_ORDER_TASK_OUTLET,
CORP_SVC_ORDER_TASK_CODE_DW_ID,
DW_ADD_DATE,
DW_UPDATE_DATE,
SS_HOUSE_ID,
SS_CUST_ID,
SS_WPCNT,
SVC_ORDER_TASK_CNT,
SVC_ORDER_TASK_PTS,
SVC_ORDER_TASK_RESCHEDULE_CNT,
SVC_ORDER_DW_ID,
ORDER_DONE_DATE,
ORDER_ENTRY_DATE,
ORDER_FINALIZE_DATE,
ORDER_SCHEDULE_DATE,
ORDER_BILL_DATE,
ORDER_WORK_BEGIN_DATE,
ORDER_COMPLETE_DATE,
ORDER_WORK_START_BY_DATE,
SVC_SCHEDULE_AREA_ID,
OPERATOR_DW_ID,
SALES_REP_DW_ID,
SVC_CYCLE_DATE,
WIP_PERIOD,
NON_PAY_ORDER,
NEW_DROP,
CORP_SCHEDULE_TIME_DW_ID,
CORP_CHANGE_RSN_DW_ID,
CORP_DISCONNECT_RSN_DW_ID,
CORP_SALES_METHOD_DW_ID,
CORP_CAMPAIGN_DW_ID,
CORP_ORDER_RSN_DW_ID,
CORP_CANCEL_RSN_DW_ID,
SVC_BUSINESS_DAYS_DONE,
COMPLETE_FLAG,
QUOTA_GROUP,
RESCHEDULE_NOT_DONE_ID,
SVC_CUSTOM_TASKS,
SVC_COMPLIANCE_ID,
LINE_OF_BUSINESS_ID,
CUST_CABLE_DW_ID,
LAST_CHANGE_OPR_DW_ID,
CDW_DATE,
LAST_ORDER_SCHEDULE_DATE,
ROUTING_ID,
SELF_INSTALL_FLAG)
VALUES (E.SVC_ORDER_TASK_DW_ID,
E.CORP_ID,
E.HOUSE_DW_ID,
E.CUST_DW_ID,
TO_DATE(E.DATE_ID, 'YYYY-MM-DD HH24:MI:SS'),
E.SVC_ORDER_TYPE_ID,
E.TECH_DW_ID,
E.CORP_NO_TRUCK_DW_ID,
E.SVC_ORDER_TASK_TRUCK_ROLL_ID,
E.SVC_ORDER_TASK_SEQ_ID,
E.SVC_ORDER_TASK_OUTLET,
E.CORP_SVC_ORDER_TASK_CODE_DW_ID,
TO_TIMESTAMP(E.DW_ADD_DATE, 'YYYY-MM-DD HH24:MI:SS.FF9'),
TO_TIMESTAMP(E.DW_UPDATE_DATE, 'YYYY-MM-DD HH24:MI:SS.FF9'),
nvl(rtrim(E.SS_HOUSE_ID), ' '),
E.SS_CUST_ID,
E.SS_WPCNT,
E.SVC_ORDER_TASK_CNT,
E.SVC_ORDER_TASK_PTS,
E.SVC_ORDER_TASK_RESCHEDULE_CNT,
E.SVC_ORDER_DW_ID,
TO_DATE(E.ORDER_DONE_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_ENTRY_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_FINALIZE_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_SCHEDULE_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_BILL_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_WORK_BEGIN_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_COMPLETE_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.ORDER_WORK_START_BY_DATE, 'YYYY-MM-DD HH24:MI:SS'),
nvl(rtrim(E.SVC_SCHEDULE_AREA_ID), ' '),
E.OPERATOR_DW_ID,
E.SALES_REP_DW_ID,
TO_DATE(E.SVC_CYCLE_DATE, 'YYYY-MM-DD HH24:MI:SS'),
E.WIP_PERIOD,
E.NON_PAY_ORDER,
E.NEW_DROP,
E.CORP_SCHEDULE_TIME_DW_ID,
E.CORP_CHANGE_RSN_DW_ID,
E.CORP_DISCONNECT_RSN_DW_ID,
E.CORP_SALES_METHOD_DW_ID,
E.CORP_CAMPAIGN_DW_ID,
E.CORP_ORDER_RSN_DW_ID,
E.CORP_CANCEL_RSN_DW_ID,
E.SVC_BUSINESS_DAYS_DONE,
E.COMPLETE_FLAG,
E.QUOTA_GROUP,
E.RESCHEDULE_NOT_DONE_ID,
nvl(rtrim(E.SVC_CUSTOM_TASKS), ' '),
E.SVC_COMPLIANCE_ID,
E.LINE_OF_BUSINESS_ID,
E.CUST_CABLE_DW_ID,
E.LAST_CHANGE_OPR_DW_ID,
TO_DATE(E.CDW_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(E.LAST_ORDER_SCHEDULE_DATE, 'YYYY-MM-DD HH24:MI:SS'),
E.ROUTING_ID,
E.SELF_INSTALL_FLAG)
LOG ERRORS INTO D30_F_SVC_ORDER_TASK_I3 ('MERGE') REJECT LIMIT UNLIMITED;
MERGE INTO F_SVC_ORDER_TASK O
USING (SELECT DISTINCT * FROM E30_F_SVC_ORDER_TASK_U1) E
ON (O.SVC_ORDER_TASK_DW_ID = E.SVC_ORDER_TASK_DW_ID)
WHEN MATCHED THEN
UPDATE SET O.CORP_ID = E.CORP_ID,
O.HOUSE_DW_ID = E.HOUSE_DW_ID,
O.CUST_DW_ID = E.CUST_DW_ID,
O.DATE_ID = E.DATE_ID,
O.SVC_ORDER_TYPE_ID = E.SVC_ORDER_TYPE_ID,
O.TECH_DW_ID = E.TECH_DW_ID,
O.CORP_NO_TRUCK_DW_ID = E.CORP_NO_TRUCK_DW_ID,
O.SVC_ORDER_TASK_TRUCK_ROLL_ID = E.SVC_ORDER_TASK_TRUCK_ROLL_ID,
O.SVC_ORDER_TASK_SEQ_ID = E.SVC_ORDER_TASK_SEQ_ID,
O.SVC_ORDER_TASK_OUTLET = E.SVC_ORDER_TASK_OUTLET,
O.CORP_SVC_ORDER_TASK_CODE_DW_ID = E.CORP_SVC_ORDER_TASK_CODE_DW_ID,
O.DW_ADD_DATE = E.DW_ADD_DATE,
O.DW_UPDATE_DATE = E.DW_UPDATE_DATE,
O.SS_HOUSE_ID = nvl(rtrim(E.SS_HOUSE_ID), ' '),
O.SS_CUST_ID = E.SS_CUST_ID,
O.SS_WPCNT = E.SS_WPCNT,
O.SVC_ORDER_TASK_CNT = E.SVC_ORDER_TASK_CNT,
O.SVC_ORDER_TASK_PTS = E.SVC_ORDER_TASK_PTS,
O.SVC_ORDER_TASK_RESCHEDULE_CNT = E.SVC_ORDER_TASK_RESCHEDULE_CNT,
O.SVC_ORDER_DW_ID = E.SVC_ORDER_DW_ID,
O.ORDER_DONE_DATE = E.ORDER_DONE_DATE,
O.ORDER_ENTRY_DATE = E.ORDER_ENTRY_DATE,
O.ORDER_FINALIZE_DATE = E.ORDER_FINALIZE_DATE,
O.ORDER_SCHEDULE_DATE = E.ORDER_SCHEDULE_DATE,
O.ORDER_BILL_DATE = E.ORDER_BILL_DATE,
O.ORDER_WORK_BEGIN_DATE = E.ORDER_WORK_BEGIN_DATE,
O.ORDER_COMPLETE_DATE = E.ORDER_COMPLETE_DATE,
O.ORDER_WORK_START_BY_DATE = E.ORDER_WORK_START_BY_DATE,
O.SVC_SCHEDULE_AREA_ID = nvl(rtrim(E.SVC_SCHEDULE_AREA_ID), ' '),
O.OPERATOR_DW_ID = E.OPERATOR_DW_ID,
O.SALES_REP_DW_ID = E.SALES_REP_DW_ID,
O.SVC_CYCLE_DATE = E.SVC_CYCLE_DATE,
O.WIP_PERIOD = E.WIP_PERIOD,
O.NON_PAY_ORDER = E.NON_PAY_ORDER,
O.NEW_DROP = E.NEW_DROP,
O.CORP_SCHEDULE_TIME_DW_ID = E.CORP_SCHEDULE_TIME_DW_ID,
O.CORP_CHANGE_RSN_DW_ID = E.CORP_CHANGE_RSN_DW_ID,
O.CORP_DISCONNECT_RSN_DW_ID = E.CORP_DISCONNECT_RSN_DW_ID,
O.CORP_SALES_METHOD_DW_ID = E.CORP_SALES_METHOD_DW_ID,
O.CORP_CAMPAIGN_DW_ID = E.CORP_CAMPAIGN_DW_ID,
O.CORP_ORDER_RSN_DW_ID = E.CORP_ORDER_RSN_DW_ID,
O.CORP_CANCEL_RSN_DW_ID = E.CORP_CANCEL_RSN_DW_ID,
O.SVC_BUSINESS_DAYS_DONE = E.SVC_BUSINESS_DAYS_DONE,
O.COMPLETE_FLAG = E.COMPLETE_FLAG,
O.QUOTA_GROUP = E.QUOTA_GROUP,
O.RESCHEDULE_NOT_DONE_ID = E.RESCHEDULE_NOT_DONE_ID,
O.SVC_CUSTOM_TASKS = nvl(rtrim(E.SVC_CUSTOM_TASKS), ' '),
O.SVC_COMPLIANCE_ID = E.SVC_COMPLIANCE_ID,
O.LINE_OF_BUSINESS_ID = E.LINE_OF_BUSINESS_ID,
O.CUST_CABLE_DW_ID = E.CUST_CABLE_DW_ID,
O.LAST_CHANGE_OPR_DW_ID = E.LAST_CHANGE_OPR_DW_ID,
O.CDW_DATE = E.CDW_DATE,
O.LAST_ORDER_SCHEDULE_DATE = E.LAST_ORDER_SCHEDULE_DATE,
O.ROUTING_ID = E.ROUTING_ID,
O.SELF_INSTALL_FLAG = E.SELF_INSTALL_FLAG
LOG ERRORS INTO D30_F_SVC_ORDER_TASK_U1 ('MERGE') REJECT LIMIT UNLIMITED;
|
|
|
|
|
|
|
|
|
|
| Re: buffer busy wait [message #528613 is a reply to message #528560] |
Wed, 26 October 2011 01:47 |
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
|
Senior Member |
|
|
It is the partition table and have indexes on all indexes. Please also share if needed all indexes DDL. Thanks
CREATE TABLE "SG_LOAD"."F_SVC_ORDER_TASK" ("SVC_ORDER_TASK_DW_ID"
NUMBER DEFAULT 0, "CORP_ID" NUMBER DEFAULT 0, "HOUSE_DW_ID"
NUMBER DEFAULT 0, "CUST_DW_ID" NUMBER DEFAULT 0, "DATE_ID"
DATE NOT NULL, "SVC_ORDER_TYPE_ID" NUMBER DEFAULT 0,
"TECH_DW_ID" NUMBER DEFAULT 0, "CORP_NO_TRUCK_DW_ID" NUMBER
DEFAULT 0, "SVC_ORDER_TASK_TRUCK_ROLL_ID" NUMBER DEFAULT 0,
"SVC_ORDER_TASK_SEQ_ID" NUMBER DEFAULT 0,
"SVC_ORDER_TASK_OUTLET" NUMBER DEFAULT 0,
"CORP_SVC_ORDER_TASK_CODE_DW_ID" NUMBER DEFAULT 0,
"DW_ADD_DATE" "TIMESTAMP(6)" NOT NULL, "DW_UPDATE_DATE"
"TIMESTAMP(6)" NOT NULL, "SS_HOUSE_ID" VARCHAR2(20 byte),
"SS_CUST_ID" VARCHAR2(2 byte), "SS_WPCNT" VARCHAR2(1 byte),
"SVC_ORDER_TASK_CNT" NUMBER DEFAULT 0, "SVC_ORDER_TASK_PTS"
NUMBER DEFAULT 0, "SVC_ORDER_TASK_RESCHEDULE_CNT" NUMBER
DEFAULT 0, "SVC_ORDER_DW_ID" NUMBER NOT NULL,
"ORDER_DONE_DATE" DATE NOT NULL, "ORDER_ENTRY_DATE" DATE NOT
NULL, "ORDER_FINALIZE_DATE" DATE NOT NULL,
"ORDER_SCHEDULE_DATE" DATE NOT NULL, "ORDER_BILL_DATE" DATE
NOT NULL, "ORDER_WORK_BEGIN_DATE" DATE NOT NULL,
"ORDER_COMPLETE_DATE" DATE NOT NULL,
"ORDER_WORK_START_BY_DATE" DATE NOT NULL,
"SVC_SCHEDULE_AREA_ID" VARCHAR2(3 byte), "OPERATOR_DW_ID"
NUMBER DEFAULT 0, "SALES_REP_DW_ID" NUMBER DEFAULT 0,
"SVC_CYCLE_DATE" DATE NOT NULL, "WIP_PERIOD" VARCHAR2(1 byte),
"NON_PAY_ORDER" NUMBER DEFAULT 0, "NEW_DROP" NUMBER DEFAULT 0,
"CORP_SCHEDULE_TIME_DW_ID" NUMBER DEFAULT 0,
"CORP_CHANGE_RSN_DW_ID" NUMBER DEFAULT 0,
"CORP_DISCONNECT_RSN_DW_ID" NUMBER DEFAULT 0,
"CORP_SALES_METHOD_DW_ID" NUMBER DEFAULT 0,
"CORP_CAMPAIGN_DW_ID" NUMBER DEFAULT 0,
"CORP_ORDER_RSN_DW_ID" NUMBER DEFAULT 0,
"CORP_CANCEL_RSN_DW_ID" NUMBER DEFAULT 0,
"SVC_BUSINESS_DAYS_DONE" NUMBER DEFAULT 0, "COMPLETE_FLAG"
VARCHAR2(1 byte), "QUOTA_GROUP" NUMBER DEFAULT 0,
"RESCHEDULE_NOT_DONE_ID" NUMBER DEFAULT 0, "SVC_CUSTOM_TASKS"
VARCHAR2(3 byte), "SVC_COMPLIANCE_ID" NUMBER DEFAULT 0,
"LINE_OF_BUSINESS_ID" NUMBER DEFAULT 8, "CUST_CABLE_DW_ID"
NUMBER DEFAULT 0, "LAST_CHANGE_OPR_DW_ID" NUMBER DEFAULT 0,
"CDW_DATE" DATE DEFAULT to_date('31-DEC-9999','DD-MON-YYYY'),
"LAST_ORDER_SCHEDULE_DATE" DATE DEFAULT to_date('31-DEC-9999',
'DD-MON-YYYY'), "ROUTING_ID" NUMBER DEFAULT 100,
"SELF_INSTALL_FLAG" VARCHAR2(1 byte) DEFAULT 'N',
CONSTRAINT "F_SOT_CUST_CABLE_DWA_FK" FOREIGN
KEY("CUST_CABLE_DW_ID")
REFERENCES "SG_LOAD"."LU_CUSTOMER_CABLE"("CUST_CABLE_DW_ID")
NOVALIDATE,
CONSTRAINT "F_SVC_ORDER_TASK_FK_DWA_1" FOREIGN
KEY("CORP_CANCEL_RSN_DW_ID")
REFERENCES
"SG_LOAD"."LU_CORP_CANCEL_RSN"("CORP_CANCEL_RSN_DW_ID")
NOVALIDATE,
CONSTRAINT "F_SVC_ORDER_TASK_PK" PRIMARY
KEY("SVC_ORDER_TASK_DW_ID")
USING INDEX
TABLESPACE "SGINDEX_PART"
STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0) PCTFREE 10 INITRANS 2 MAXTRANS 255
NOVALIDATE,
CONSTRAINT "SVC_ORD_TASK_CAMPAIGN_FKC" FOREIGN
KEY("CORP_CAMPAIGN_DW_ID")
REFERENCES
"SG_LOAD"."LU_CORP_CAMPAIGN"("CORP_CAMPAIGN_DW_ID")
NOVALIDATE,
CONSTRAINT "SVC_ORD_TASK_CHANGE_RSN_FKC" FOREIGN
KEY("CORP_CHANGE_RSN_DW_ID")
REFERENCES
"SG_LOAD"."LU_CORP_CHANGE_RSN"("CORP_CHANGE_RSN_DW_ID")
NOVALIDATE,
CONSTRAINT "SVC_ORD_TASK_CORP_ID_FKC" FOREIGN KEY("CORP_ID")
REFERENCES "SG_LOAD"."LU_CORP"("CORP_ID") NOVALIDATE,
CONSTRAINT "SVC_ORD_TASK_CUST_FKC" FOREIGN KEY("CUST_DW_ID")
REFERENCES "SG_LOAD"."LU_CUSTOMER"("CUST_DW_ID") NOVALIDATE,
CONSTRAINT "SVC_ORD_TASK_DISCONN_RSN_FK" FOREIGN
KEY("CORP_DISCONNECT_RSN_DW_ID")
REFERENCES
"SG_LOAD"."LU_CORP_DISCONNECT_RSN"("CORP_DISCONNECT_RSN_DW_ID")
NOVALIDATE,
CONSTRAINT "SVC_ORD_TASK_HOUSE_FKC" FOREIGN
KEY("HOUSE_DW_ID")
REFERENCES "SG_LOAD"."LU_HOUSE"("HOUSE_DW_ID") NOVALIDATE,
CONSTRAINT "SVC_ORD_TASK_NO_TRUCK_FK" FOREIGN
KEY("CORP_NO_TRUCK_DW_ID")
REFERENCES
"SG_LOAD"."LU_CORP_NO_TRUCK_RSN"("CORP_NO_TRUCK_DW_ID")
NOVALIDATE,
CONSTRAINT "SVC_ORD_TASK_OPERATOR_FKC" FOREIGN
KEY("OPERATOR_DW_ID")
REFERENCES "SG_LOAD"."LU_OPERATOR"("OPERATOR_DW_ID")
NOVALIDATE,
CONSTRAINT "SVC_ORD_TASK_ORDER_RSN_FKC" FOREIGN
KEY("CORP_ORDER_RSN_DW_ID")
REFERENCES
"SG_LOAD"."LU_CORP_ORDER_RSN"("CORP_ORDER_RSN_DW_ID")
NOVALIDATE,
CONSTRAINT "SVC_ORD_TASK_ORDER_TYPE_FKC" FOREIGN
KEY("SVC_ORDER_TYPE_ID")
REFERENCES "SG_LOAD"."LU_ORDER_TYPE"("SVC_ORDER_TYPE_ID")
NOVALIDATE,
CONSTRAINT "SVC_ORD_TASK_SALES_METHOD_FK" FOREIGN
KEY("CORP_SALES_METHOD_DW_ID")
REFERENCES
"SG_LOAD"."LU_CORP_SALES_METHOD"("CORP_SALES_METHOD_DW_ID")
NOVALIDATE,
CONSTRAINT "SVC_ORD_TASK_SALES_REP_FKC" FOREIGN
KEY("SALES_REP_DW_ID")
REFERENCES "SG_LOAD"."LU_SALES_REP"("SALES_REP_DW_ID")
NOVALIDATE,
CONSTRAINT "SVC_ORD_TASK_SCHEDULE_AREA_FKC" FOREIGN
KEY("SVC_SCHEDULE_AREA_ID")
REFERENCES "SG_LOAD"."LU_SCHEDULE_AREA"("SCHEDULE_AREA_ID")
NOVALIDATE,
CONSTRAINT "SVC_ORD_TASK_SOTC_FK" FOREIGN
KEY("CORP_SVC_ORDER_TASK_CODE_DW_ID")
REFERENCES
"SG_LOAD"."LU_CORP_SVC_ORDER_TASK_CODE"("CORP_SVC_ORDER_TASK_CODE_DW_ID")
NOVALIDATE,
CONSTRAINT "SVC_ORD_TASK_TECH_FKC" FOREIGN KEY("TECH_DW_ID")
REFERENCES "SG_LOAD"."LU_TECH"("TECH_DW_ID") NOVALIDATE,
CONSTRAINT "SVC_ORD_TASK_TRUCK_ROLL_FKC" FOREIGN
KEY("SVC_ORDER_TASK_TRUCK_ROLL_ID")
REFERENCES "SG_LOAD"."LU_TRUCK_ROLL"("TRUCK_ROLL_ID")
NOVALIDATE)
TABLESPACE "SGDATA_PART" PCTFREE 10 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
PARALLEL ( DEGREE 8)
LOGGING
PARTITION BY RANGE ("CORP_ID") (PARTITION "PART_N99999"
VALUES LESS THAN (-99999)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_1105"
VALUES LESS THAN (1105)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_1108"
VALUES LESS THAN (1108)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_1110"
VALUES LESS THAN (1110)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_1624"
VALUES LESS THAN (1624)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_1636"
VALUES LESS THAN (1636)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_1638"
VALUES LESS THAN (1638)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_1641"
VALUES LESS THAN (1641)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_1643"
VALUES LESS THAN (1643)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_1710"
VALUES LESS THAN (1710)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_1716"
VALUES LESS THAN (1716)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_1718"
VALUES LESS THAN (1718)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_1719"
VALUES LESS THAN (1719)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_1723"
VALUES LESS THAN (1723)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_1724"
VALUES LESS THAN (1724)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_4418"
VALUES LESS THAN (4418)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_5628"
VALUES LESS THAN (5628)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_5705"
VALUES LESS THAN (5705)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_9512"
VALUES LESS THAN (9512)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_9544"
VALUES LESS THAN (9544)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_9577"
VALUES LESS THAN (9577)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_9579"
VALUES LESS THAN (9579)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_9586"
VALUES LESS THAN (9586)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_9587"
VALUES LESS THAN (9587)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_15515"
VALUES LESS THAN (15515)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_15552"
VALUES LESS THAN (15552)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_15557"
VALUES LESS THAN (15557)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_19204"
VALUES LESS THAN (19204)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_25201"
VALUES LESS THAN (25201)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING,
PARTITION "PART_32007"
VALUES LESS THAN (32007)
TABLESPACE "SGDATA_PART" PCTFREE 10 PCTUSED 0 INITRANS 1
MAXTRANS 255
STORAGE ( INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0) LOGGING)
|
|
|
|
|
|
|
|
| Re: buffer busy wait [message #528624 is a reply to message #528616] |
Wed, 26 October 2011 02:33 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hi - are you sure that you have a problem?
What was the query you ran to get that first listing, in particular what are the units for the TIME_SPENT column?
Over what time frame was the data accumulated?
Are you certain that those wait events are to do with the queries you posted later?
John.
[edit: added line breaks)
[Updated on: Wed, 26 October 2011 02:34] Report message to a moderator |
|
|
|
|
|
| Re: buffer busy wait [message #528634 is a reply to message #528633] |
Wed, 26 October 2011 03:19 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:What was the query you ran to get that first listing, in particular what are the units for the TIME_SPENT column?
Over what time frame was the data accumulated?
|
|
|
|
| Re: buffer busy wait [message #529052 is a reply to message #528634] |
Fri, 28 October 2011 08:29 |
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
|
Senior Member |
|
|
Sorry for late response.
I have checked with issue and traced we are having buffer waits will be due to contention for an undo segment header or an undo segment block. But when looking with undo tablespace at first sight, it is just 25% used. Shall it be advisable to add more space. What is your suggestion. Also please correct, if i'm getting in wrong direction.
Thanks.
SQL> SET LINESIZE 200
SET PAGESIZE 1000
SQL> SQL>
SQL> COLUMN username FORMAT A20
SQL> COLUMN event FORMAT A30
SQL> COLUMN wait_class FORMAT A15
SQL>
SQL> SELECT s.inst_id,
2 NVL(s.username, '(oracle)') AS username,
3 s.sid,
4 s.serial#,
sw.event,
5 6 sw.wait_class,
7 sw.wait_time,
8 sw.seconds_in_wait,
9 sw.state
10 FROM gv$session_wait sw,
11 gv$session s
WHERE s.sid = sw.sid
12 13 AND s.inst_id = sw.inst_id
14 AND SW.wait_class not in ('Idle')
15 ORDER BY sw.seconds_in_wait DESC;
INST_ID USERNAME SID SERIAL# EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
---------- -------------------- ---------- ---------- ------------------------------ --------------- ---------- --------------- -------------------
1 (oracle) 544 1 latch: cache buffers lru chain Other -1 9558 WAITED SHORT TIME
1 (oracle) 546 1 latch: cache buffers lru chain Other -1 9259 WAITED SHORT TIME
1 SG_LOAD 353 1290 buffer busy waits Concurrency 0 1 WAITING
1 SG_LOAD 468 5687 buffer busy waits Concurrency 0 1 WAITING
1 (oracle) 541 1 rdbms ipc reply Other 0 0 WAITING
1 SYS 410 5081 SQL*Net message to client Network -1 0 WAITED SHORT TIME
1 SG_LOAD 499 5236 buffer busy waits Concurrency 0 0 WAITING
7 rows selected.
SQL>
SQL>
SQL> select p1 "File #",p2 "Block #",p3 "Reason Code" from v$session_wait where event = 'buffer busy waits';
File # Block # Reason Code
---------- ---------- -----------
3 2 13
3 2 13
3 2 13
SQL> select name from v$datafile where file#=3;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/p07/oradata/padwsdpr/UNDOTBS_DATA_MED_01.dbf
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: buffer busy wait [message #529142 is a reply to message #529093] |
Fri, 28 October 2011 15:44 |
 |
LNossov
Messages: 318
Registered: July 2011
Location: Germany
|
Senior Member |
|
|
You need execute privilege on sys.dbms_workload_repository. It would be fine, if you get select privileges on sys.wrm$_snapshot for finding out of
- dbid,
- instance number and
- snapid range.
After that you can run
set pagesize 0
set linesize 1000
set trimspool on
spool awr.out
select output from table (sys.dbms_workload_repository.awr_report_text(<dbid>,<instance id>,<begin snapid>,<end snapid>,8));
exit
Upload please the file awr.out
[Updated on: Fri, 28 October 2011 15:52] Report message to a moderator |
|
|
|
| Re: buffer busy wait [message #529147 is a reply to message #529093] |
Fri, 28 October 2011 16:12 |
|
LNossov
Messages: 318
Registered: July 2011
Location: Germany
|
Senior Member |
|
|
Upload additionally the file ts.out - the output of
set linesize 1000
set trimspool on
spool ts.out
select * from dba_tablespaces order y tablespace_name;
exit
|
|
|
|
Goto Forum:
Current Time: Sun Nov 24 12:40:59 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
