Single record insert intermittently slow
From: Rich <richa03_at_gmail.com>
Date: Tue, 25 Oct 2011 08:03:26 -0700
Message-ID: <CALgGkeDsjonjmO8NbXGCk2qighZFTBFTk5H-Rm97ZXQGQBDY4A_at_mail.gmail.com>
Hi List,
This is 64-bit 11.2.0.2 on RHEL 5.6 x86_64. There have been no changes to the DB server nor the application servers since 10SEP11 other than normal business. We started to see increased CPU activity on a particular insert statement from our app servers at about 22OCT11 2325 GMT. This lasted until 23OCT11 0430 GMT, when we started to see increased waits on "db file sequential read" for just this particular insert statement - no waits on any enqueues.
These are single record inserts into a table with the following description:
Name Null? Type
----------------------------------------- -------- ----------------------------
TRHS_PKEY NOT NULL NUMBER
TRHS_SEQ NOT NULL NUMBER
TRAN_KEY NOT NULL NUMBER
TRSP_KEY NUMBER
TRHS_DESC NOT NULL VARCHAR2(256)
TRHS_DOC_URL VARCHAR2(512)
TRHS_DOC_SIZE NOT NULL NUMBER
TRHS_CREATED_DATE NOT NULL DATE
TRHS_DOC BLOB
TRHS_CONTENT_TYPE VARCHAR2(256)
TRHS_ZIPPED NOT NULL NUMBER(1)
BSTP_GUID CHAR(32)
TRHS_INHERITABLE NOT NULL NUMBER(1)
TRHS_WRITE_SIZE NUMBER
The segments for this table are the following: SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT
HSR_TRANSLATION_HISTORY_BASE 605028352 73856 577 33554432 16777216 SYS_IL0000025912C00009$$ 417333248 203776 398 13420544 16777216 PK_HSR_TRANS_HISTORY_BASE 75497472 9216 72 65536 16777216 FK01_HSR_TRANS_HISTORY_BASE 131072000 16000 125 10485760 1048576 TRHS_DOC_LOB 8628731904 4213248 8229 268435456 16777216 The execution plan for this insert statement is (and always has been): INSERT STATEMENT
LOAD TABLE CONVENTIONAL An example of a poorly performing execution requires over 30minutes with the Oracle DB server waiting on "db file sequential read" and "CPU Used".
An example of a [correctly] performing execution requires <0.01 sec.
Date: Tue, 25 Oct 2011 08:03:26 -0700
Message-ID: <CALgGkeDsjonjmO8NbXGCk2qighZFTBFTk5H-Rm97ZXQGQBDY4A_at_mail.gmail.com>
Hi List,
This is 64-bit 11.2.0.2 on RHEL 5.6 x86_64. There have been no changes to the DB server nor the application servers since 10SEP11 other than normal business. We started to see increased CPU activity on a particular insert statement from our app servers at about 22OCT11 2325 GMT. This lasted until 23OCT11 0430 GMT, when we started to see increased waits on "db file sequential read" for just this particular insert statement - no waits on any enqueues.
These are single record inserts into a table with the following description:
Name Null? Type
----------------------------------------- -------- ----------------------------
TRHS_PKEY NOT NULL NUMBER
TRHS_SEQ NOT NULL NUMBER
TRAN_KEY NOT NULL NUMBER
TRSP_KEY NUMBER
TRHS_DESC NOT NULL VARCHAR2(256)
TRHS_DOC_URL VARCHAR2(512)
TRHS_DOC_SIZE NOT NULL NUMBER
TRHS_CREATED_DATE NOT NULL DATE
TRHS_DOC BLOB
TRHS_CONTENT_TYPE VARCHAR2(256)
TRHS_ZIPPED NOT NULL NUMBER(1)
BSTP_GUID CHAR(32)
TRHS_INHERITABLE NOT NULL NUMBER(1)
TRHS_WRITE_SIZE NUMBER
The segments for this table are the following: SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT
------------------------------ ---------- ---------- ------------------------ -----------
HSR_TRANSLATION_HISTORY_BASE 605028352 73856 577 33554432 16777216 SYS_IL0000025912C00009$$ 417333248 203776 398 13420544 16777216 PK_HSR_TRANS_HISTORY_BASE 75497472 9216 72 65536 16777216 FK01_HSR_TRANS_HISTORY_BASE 131072000 16000 125 10485760 1048576 TRHS_DOC_LOB 8628731904 4213248 8229 268435456 16777216 The execution plan for this insert statement is (and always has been): INSERT STATEMENT
LOAD TABLE CONVENTIONAL An example of a poorly performing execution requires over 30minutes with the Oracle DB server waiting on "db file sequential read" and "CPU Used".
An example of a [correctly] performing execution requires <0.01 sec.
Has anyone seen anything like this?
We have an SR open with Oracle, however, any help from this list is appreciated.
Thanks,
Rich
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Oct 25 2011 - 10:03:26 CDT