Single record insert intermittently slow
- From: Rich <richa03@xxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 25 Oct 2011 08:03:26 -0700
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
