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 Go to next message
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 #528514 is a reply to message #528507] Tue, 25 October 2011 07:43 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Regarding "buffer busy waits" s. the note 34405.1 please.
Re: buffer busy wait [message #528517 is a reply to message #528514] Tue, 25 October 2011 08:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

post SQL which produced output below

EVENT                          TIME_SPENT
------------------------------ ----------
latch: cache buffers chains             0
buffer busy waits                  341898


Do you suffer from Compulsive Tuning Disorder?
Re: buffer busy wait [message #528536 is a reply to message #528517] Tue, 25 October 2011 10:36 Go to previous messageGo to next message
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 #528537 is a reply to message #528536] Tue, 25 October 2011 10:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: buffer busy wait [message #528558 is a reply to message #528536] Tue, 25 October 2011 12:37 Go to previous messageGo to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Hi,

I have checked with the explain plan and one of the big table running with TABLE ACCESS FULL. Is that can be cause of issue.? Please suggest further path.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                       |                         |  8168 |  8112K|       |  9509   (1)|       |       |
|   1 |  MERGE                                | F_SVC_ORDER_TASK        |       |       |       |            |       |       |
|   2 |   VIEW                                |                         |       |       |       |            |       |       |
|   3 |    NESTED LOOPS OUTER                 |                         |  8168 |  8112K|       |  9509   (1)|       |       |
|   4 |     VIEW                              |                         |  8168 |  5878K|       |  1299   (1)|       |       |
|   5 |      SORT UNIQUE                      |                         |  8168 |  5878K|  7272K|  1299   (1)|       |       |
|   6 |       EXTERNAL TABLE ACCESS FULL      | E30_F_SVC_ORDER_TASK_I1 |  8168 |  5878K|       |    30   (4)|       |       |
|   7 |     TABLE ACCESS BY GLOBAL INDEX ROWID| F_SVC_ORDER_TASK        |     1 |   280 |       |     2   (0)| ROWID | ROWID |
|*  8 |      INDEX UNIQUE SCAN                | F_SVC_ORDER_TASK_PK     |     1 |       |       |     1   (0)|       |       |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access("O"."SVC_ORDER_TASK_DW_ID"(+)="E"."SVC_ORDER_TASK_DW_ID")

Note
-----
   - 'PLAN_TABLE' is old version
Re: buffer busy wait [message #528560 is a reply to message #528558] Tue, 25 October 2011 12:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
1) DDL for all tables & indexes
Re: buffer busy wait [message #528613 is a reply to message #528560] Wed, 26 October 2011 01:47 Go to previous messageGo to next message
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 #528614 is a reply to message #528613] Wed, 26 October 2011 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When you post a DDL, never post the storage clause and owner name:
1) we don't care (so it is just noise in the post);
2) we have not the same owners and tablespaces so we have to modify the statement to execute it
(when the question requires we do it);
3) it may be confidential at your site.

Regards
Michel

Re: buffer busy wait [message #528616 is a reply to message #528614] Wed, 26 October 2011 01:56 Go to previous messageGo to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Sorry Michel,

I will take care in future post. Also please suggest, if change in any storage clause (PCTFREE) will help in BUFFER BUSY WAIT. Also please suggest for further action.

Thanks.
Re: buffer busy wait [message #528624 is a reply to message #528616] Wed, 26 October 2011 02:33 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #528633 is a reply to message #528624] Wed, 26 October 2011 03:16 Go to previous messageGo to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

not sure but i was looking more at the query because didn't any object reference when looking to hot block. Please correct me if i'm wrong.

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
Re: buffer busy wait [message #528634 is a reply to message #528633] Wed, 26 October 2011 03:19 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
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 #529053 is a reply to message #529052] Fri, 28 October 2011 08:39 Go to previous messageGo to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Since last couples of days, i am buffer busy wait due to file#=3 and block#=2.
Re: buffer busy wait [message #529071 is a reply to message #529053] Fri, 28 October 2011 10:04 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
could you please run the following query:
select
  class  "block class",
  count  "total waits",
  time  "time waited"
from
  v$waitstat  
where
count > 0
order by time desc, count desc;
Re: buffer busy wait [message #529074 is a reply to message #529071] Fri, 28 October 2011 10:15 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
upload please additionally an awr report to the interesting period of time.
Re: buffer busy wait [message #529093 is a reply to message #529074] Fri, 28 October 2011 12:12 Go to previous messageGo to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

please find the output.

SQL> select
  class  "block class",
  count  "total waits",
  time  "time waited"
from
  v$waitstat
where
count > 0
order by time desc, count desc;  2    3    4    5    6    7    8    9

block class        total waits time waited
------------------ ----------- -----------
file header block        40894     4069804
data block              489744      605163
undo header                755       45900
1st level bmb               36          94
2nd level bmb               40          45
segment header              12          23
3rd level bmb               32          14
extent map                   1           2
undo block                  14           0


Sorry but i'm getting option to upload AWR report. please suggest.
Re: buffer busy wait [message #529095 is a reply to message #529093] Fri, 28 October 2011 12:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You suffer from Compulsive Tuning Disorder
SQL> select 45900/(4069804+605163) from dual;

45900/(4069804+605163)
----------------------
	    .009818251

if waits on UNDO went to ZERO, the application users would not notice any change
Re: buffer busy wait [message #529142 is a reply to message #529093] Fri, 28 October 2011 15:44 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Reading explain plan
Next Topic: Bulk update performance issue
Goto Forum:
  


Current Time: Fri Mar 29 10:33:46 CDT 2024