Long running update
Hello,
Can anyone explain why this simple update statement against a single partition in large table (~300,000 rows, ~1GB in size for the single partition) is taking very long time. The most unusual thing I see in the stats are HUGE number of buffer gets.
Table def is below, and there are 25 local b-tree indexes also on this table (too much to paste here), each on a single column residing in seperate tablespace than the table.
I don't have a trace and will not be able to get one. Any theories as to the high buffer gets? A simple table scan (which occurs many times in our batch) against a single partition takes usually between 30-60 seconds. Sometimes the table scan goes haywire and I see these huge buffer gets, somewhat higher disk reads, and much longer execution time. There are less than 3 million rows in the partition being acted on, and only updating a couple columns, I simply cannot understand why Oracle would be getting a block (whether it was in cache already or not) over 1 BILLION times to perform this update.
This is Oracle 11g 11.1.0.7 on RHL 5.3, 2 node RAC but all processing on instance 1 and instance 2 shut down at this point to avoid any possibility of cache fusuion issues.
Elapsed
SQL Id Time (ms)
0np3ccxhf9jmc 1.79E+07
UPDATE ESRODS.EXTR_CMG_TRANSACTION_HISTORY SET RULE_ID_2 = '9285', REPORT_CODE =
'MMKT' WHERE EDIT_CUSIP_NUM = '19766G868' AND PROCESS_DATE BETWEEN '01-JAN-201
0' AND '31-JAN-2010' AND RULE_ID_2 IS NULL
Plan Statistics
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 1.79E+07 17,915,656.1 2.3
CPU Time (ms) 1.18E+07 11,837,756.4 2.5
Executions 1 N/A N/A
Buffer Gets 1.09E+09 1.089168E+09 3.3
Disk Reads 246,267 246,267.0 0.0
Parse Calls 1 1.0 0.0
Rows 326,843 326,843.0 N/A
User I/O Wait Time (ms) 172,891 N/A N/A
Cluster Wait Time (ms) 0 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 504,047 N/A N/A
Invalidations 0 N/A N/A
Version Count 21 N/A N/A
Sharable Mem(KB) 745 N/A N/A
Execution Plan
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 36029 (100)| | | |
| 1 | UPDATE | EXTR_CMG_TRANSACTION_HISTORY | | | | | | |
| 2 | PARTITION RANGE SINGLE| | 305K| 21M| 36029 (1)| 00:05:16 | 62 | 62 |
| 3 | TABLE ACCESS FULL | EXTR_CMG_TRANSACTION_HISTORY | 305K| 21M| 36029 (1)| 00:05:16 | 62 | 62 |
------------------------------------------------------------------------------------------------------------------------
Full SQL Text
SQL ID SQL Text
------------ -----------------------------------------------------------------
0np3ccxhf9jm UPDATE ESRODS.EXTR_CMG_TRANSACTION_HISTORY SET RULE_ID_2 = '9285'
', REPORT_CODE = 'MMKT' WHERE EDIT_CUSIP_NUM = '19766G868' AND PR
OCESS_DATE BETWEEN '01-JAN-2010' AND '31-JAN-2010' AND RULE_ID_2
IS NULL
Table def:
CREATE TABLE EXTR_CMG_TRANSACTION_HISTORY
(
TRANSACTION_ID NUMBER(15) NOT NULL,
CREATE_DATE DATE,
CREATE_USER VARCHAR2(80 BYTE),
MODIFY_DATE DATE,
MODIFY_USER VARCHAR2(80 BYTE),
EXCEPTION_FLG CHAR(1 BYTE),
SOURCE_SYSTEM VARCHAR2(20 BYTE),
SOURCE_TYPE VARCHAR2(32 BYTE),
TRANSACTION_STATUS VARCHAR2(8 BYTE),
FUND_ID NUMBER(15),
FUND_UNIT_ID NUMBER(15),
FROM_FUND_ID NUMBER(15),
FROM_FUND_UNIT_ID NUMBER(15),
EXECUTING_DEALER_ID NUMBER(15),
EXECUTING_BRANCH_ID NUMBER(15),
CLEARING_DEALER_ID NUMBER(15),
CLEARING_BRANCH_ID NUMBER(15),
BRANCH_PERSON_MAP_ID NUMBER(15),
BP_REP_MAP_ID NUMBER(15),
REP_ID NUMBER(15),
PERSON_ID NUMBER(15),
TPA_DEALER_ID NUMBER(15),
TRUST_DEALER_ID NUMBER(15),
TRANS_CODE_ID NUMBER(15),
EDIT_DEALER_NUM VARCHAR2(30 BYTE),
EDIT_BRANCH_NUM VARCHAR2(50 BYTE),
EDIT_REP_NUM VARCHAR2(100 BYTE),
EDIT_CUSIP_NUM VARCHAR2(9 BYTE),
TRANS_TYPE VARCHAR2(80 BYTE),
TRANSACTION_CD VARCHAR2(8 BYTE),
TRANSACTION_SUFFIX VARCHAR2(8 BYTE),
SHARE_BALANCE_IND VARCHAR2(2 BYTE),
PROCESS_DATE DATE,
BATCH_DATE DATE,
SUPER_SHEET_DATE DATE,
CONFIRM_DATE DATE,
TRADE_DATE DATE,
SETTLE_DATE DATE,
PAYMENT_DATE DATE,
AM_PM_CD VARCHAR2(2 BYTE),
TRUST_DEALER_NUM VARCHAR2(7 BYTE),
TPA_DEALER_NUM VARCHAR2(7 BYTE),
TRUST_COMPANY_NUM VARCHAR2(10 BYTE),
DEALER_NUM VARCHAR2(25 BYTE),
BRANCH_NUM VARCHAR2(50 BYTE),
REP_NUM VARCHAR2(100 BYTE),
DEALER_NAME VARCHAR2(80 BYTE),
REP_NAME VARCHAR2(80 BYTE),
SOCIAL_SECURITY_NUMBER VARCHAR2(9 BYTE),
ACCT_NUMBER_CD VARCHAR2(6 BYTE),
ACCT_NUMBER VARCHAR2(20 BYTE),
ACCT_SHORT_NAME VARCHAR2(80 BYTE),
FROM_TO_ACCT_NUM VARCHAR2(20 BYTE),
EXTERNAL_ACCT_NUM VARCHAR2(14 BYTE),
NAV_ACCT VARCHAR2(1 BYTE),
MANAGEMENT_CD VARCHAR2(16 BYTE),
PRODUCT VARCHAR2(80 BYTE),
SUBSET_PRODUCT VARCHAR2(3 BYTE),
FUND_NAME VARCHAR2(80 BYTE),
FUND_NUM VARCHAR2(7 BYTE),
FUND_CUSIP_NUM VARCHAR2(9 BYTE),
TICKER_SYMBOL VARCHAR2(10 BYTE),
APL_FUND_TYPE VARCHAR2(10 BYTE),
LOAD_INDICATOR VARCHAR2(50 BYTE),
FROM_TO_FUND_NUM VARCHAR2(7 BYTE),
FROM_TO_FUND_CUSIP_NUM VARCHAR2(9 BYTE),
CUM_DISCNT_NUM VARCHAR2(9 BYTE),
NSCC_CONTROL_CD VARCHAR2(15 BYTE),
NSCC_NAV_REASON_CD VARCHAR2(1 BYTE),
BATCH_NUMBER VARCHAR2(20 BYTE),
ORDER_NUMBER VARCHAR2(16 BYTE),
CONFIRM_NUMBER VARCHAR2(9 BYTE),
AS_OF_REASON_CODE VARCHAR2(3 BYTE),
SOCIAL_CODE VARCHAR2(3 BYTE),
NETWORK_MATRIX_LEVEL VARCHAR2(1 BYTE),
SHARE_PRICE NUMBER(15,4),
GROSS_AMOUNT NUMBER(15,2),
GROSS_SHARES NUMBER(15,4),
NET_AMOUNT NUMBER(15,2),
NET_SHARES NUMBER(15,4),
DEALER_COMMISSION_CODE CHAR(1 BYTE),
DEALER_COMMISSION_AMOUNT NUMBER(15,2),
UNDRWRT_COMMISSION_CODE CHAR(1 BYTE),
UNDRWRT_COMMISSION_AMOUNT NUMBER(15,2),
DISCO-stupid spam filter- UNT_CATEGORY VARCHAR2(2 BYTE),
LOI_NUMBER VARCHAR2(9 BYTE),
RULE_ID_1 NUMBER(15),
RULE_ID_2 NUMBER(15),
OMNIBUS_FLG CHAR(1 BYTE),
MFA_FLG CHAR(1 BYTE),
REPORT_CODE VARCHAR2(80 BYTE),
TERRITORY_ADDR_CODE VARCHAR2(3 BYTE),
ADDRESS_ID NUMBER(15),
POSTAL_CODE_ID NUMBER(15),
CITY VARCHAR2(50 BYTE),
STATE_PROVINCE_CODE VARCHAR2(5 BYTE),
POSTAL_CODE VARCHAR2(12 BYTE),
COUNTRY_CODE VARCHAR2(5 BYTE),
LOB_ID NUMBER(15),
CHANNEL_ID NUMBER(15),
REGION_ID NUMBER(15),
TERRITORY_ID NUMBER(15),
EXCEPTION_NOTES VARCHAR2(4000 BYTE),
SOURCE_RECORD_ID NUMBER(15),
LOAD_ID NUMBER(15),
BIN VARCHAR2(20),
SHARE_CLASS VARCHAR2(50),
ACCT_PROD_ID NUMBER,
ORIGINAL_FUND_NUM VARCHAR2(7),
ORIGINAL_FROM_TO_FUND_NUM VARCHAR2(7),
ACCT_PROD_REGISTRATION_ID NUMBER,
REGISTRATION_LINE_1 VARCHAR2(60),
REGISTRATION_LINE_2 VARCHAR2(60),
REGISTRATION_LINE_3 VARCHAR2(60),
REGISTRATION_LINE_4 VARCHAR2(60),
REGISTRATION_LINE_5 VARCHAR2(60),
REGISTRATION_LINE_6 VARCHAR2(35),
REGISTRATION_LINE_7 VARCHAR2(35),
SECONDARY_LOB_ID NUMBER(15,0),
SECONDARY_CHANNEL_ID NUMBER(15,0),
SECONDARY_REGION_ID NUMBER(15,0),
SECONDARY_TERRITORY_ID NUMBER(15,0),
ACCOUNT_OVERRIDE_PRIORITY_CODE NUMBER(3,0)
)
TABLESPACE P_ESRODS_EXTR_TRANS_LARGE_DAT
PCTUSED 0
PCTFREE 25
INITRANS 1
MAXTRANS 255
NOLOGGING
PARTITION BY RANGE (PROCESS_DATE)
(
PARTITION P_ESRODS_EXTR_CMG_TRAN_PRE2005 VALUES LESS THAN (TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE P_ESRODS_EXTR_TRANS_LARGE_DAT
PCTFREE 25
INITRANS 100
MAXTRANS 255
STORAGE (
INITIAL 5M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
.
.
.
.
PARTITION P_ESRODS_EXTR_CMG_TRAN_201105 VALUES LESS THAN (TO_DATE(' 2011-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE P_ESRODS_EXTR_TRANS_LARGE_DAT
PCTFREE 25
INITRANS 100
MAXTRANS 255
STORAGE (
INITIAL 5M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
),
PARTITION P_ESRODS_EXTR_CMG_TRAN_201106 VALUES LESS THAN (TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE P_ESRODS_EXTR_TRANS_LARGE_DAT
PCTFREE 25
INITRANS 100
MAXTRANS 255
STORAGE (
INITIAL 5M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL;
ALTER TABLE EXTR_CMG_TRANSACTION_HISTORY ADD (
CONSTRAINT PK_EXTR_CMG_TRANSACTION_HIST PRIMARY KEY (TRANSACTION_ID)
USING INDEX
TABLESPACE P_ESRODS_EXTR_TRANS_LARGE_IDX
PCTFREE 25
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 5M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
));
Edited by: 855802 on May 1, 2011 6:46 AM