Re: Single record insert intermittently slow
Date: Thu, 27 Oct 2011 09:03:56 -0700
Thank you for your response and all of the (very good) questions!
I should restate the issue as we now know it:
We have intermittent/sporadic higher than expected volume of (logical
and physical ['db file sequential read']) IO on a LOB index segment
for single insert statement which creates unacceptable performance.
This insert is sometimes very fast (tim=1319729524407615 to
tim=1319729524417077 = 9462us = 0.009462 seconds) and sometimes very
slow (on the order of 30minutes).
It appears random with a number of very quick inserts then a very long one - no relationship to the number before or after. Nothing (much) else going on on the system at this time - no enqueue blocking; disk IO is about 7500 IOPS during a long execution. We pretty much have a single long execution running all the time (from different sessions - one session will be a long execution, other sessions will be fast during this time, then another session will be a long execution with other sessions being fast). The LOB size varies with fast and slow - there doesn't appear to be a correlation on LOB size inserted.
This is a standard insert statement into this table:
INSERT INTO HSR_TRANSLATION_HISTORY_BASE ( TRHS_PKEY ,TRHS_SEQ ,TRAN_KEY ,BSTP_GUID ,TRSP_KEY ,TRHS_DESC ,TRHS_DOC_URL ,TRHS_DOC_SIZE ,TRHS_CONTENT_TYPE ,TRHS_DOC ,TRHS_ZIPPED ,TRHS_CREATED_DATE,TRHS_INHERITABLE ,TRHS_WRITE_SIZE ) VALUES ( :B12 ,:B11 ,:B10 ,:B9 ,:B8 ,:B7 ,NULL ,:B6 ,:B5 ,:B4 ,:B3 ,SYSDATE ,:B2 ,:B1 ) The table definition is:
CREATE TABLE HSR_TRANSLATION_HISTORY_BASE (
TRHS_PKEY NUMBER CONSTRAINT NN01_HSR_TRANS_HISTORY_BASE NOT NULL, TRHS_SEQ NUMBER CONSTRAINT NN02_HSR_TRANS_HISTORY_BASE NOT NULL, TRAN_KEY NUMBER CONSTRAINT NN03_HSR_TRANS_HISTORY_BASE NOT NULL, TRSP_KEY NUMBER, TRHS_DESC VARCHAR2(256) CONSTRAINT NN04_HSR_TRANS_HISTORY_BASE NOT NULL, TRHS_DOC_URL VARCHAR2(512), TRHS_DOC_SIZE NUMBER DEFAULT 0 CONSTRAINT NN05_HSR_TRANS_HISTORY_BASE NOT NULL, TRHS_CREATED_DATE DATE CONSTRAINT NN06_HSR_TRANS_HISTORY_BASE NOT NULL, TRHS_DOC BLOB, TRHS_CONTENT_TYPE VARCHAR2(256), TRHS_ZIPPED NUMBER(1) DEFAULT 0 CONSTRAINT NN07_HSR_TRANS_HISTORY_BASE NOT NULLCONSTRAINT CK01_HSR_TRANS_HISTORY_BASE CHECK (TRHS_ZIPPED IN (0, 1, 2, 3, 4)),
BSTP_GUID CHAR(32), TRHS_INHERITABLE NUMBER(1) DEFAULT 1 CONSTRAINT NN08_HSR_TRANS_HISTORY_BASE NOT NULL
CONSTRAINT CK02_HSR_TRANS_HISTORY_BASE CHECK (TRHS_INHERITABLE IN (0, 1)), TRHS_WRITE_SIZE NUMBER
LOB (TRHS_DOC) STORE AS
DISABLE STORAGE IN ROW
PROMPT --- Creating Index PK_HSR_TRANS_HISTORY_BASE (Key Constraint)
ALTER TABLE HSR_TRANSLATION_HISTORY_BASE ADD
CONSTRAINT PK_HSR_TRANS_HISTORY_BASE PRIMARY KEY (TRHS_PKEY)
PROMPT --- Creating Index FK01_HSR_TRANS_HISTORY_BASE
CREATE INDEX FK01_HSR_TRANS_HISTORY_BASE ON HSR_TRANSLATION_HISTORY_BASE
I'm now thinking that the LOB index is either foobar'd or ASSM is
searching for free blocks with which to perform the insert into the
This table is mainly insert, however, we do delete test records from it nightly through a batch job.
We have other databases using this same code which are much higher in transaction rate for this statement (this DB with the issue is a staging DB).
We have one (much higher volume) at 126.96.36.199 with no issue; we have another one (MUCH higher volume) at 10.2.0.4 which also has no issue. We had intended on migrating the 10.2.0.4 DB soon, however, with this issue, that is on hold...
On Wed, Oct 26, 2011 at 1:24 PM, Dennis Williams
> Just let me understand your problem definition. So you are saying that
> sometimes it takes far less than one second to insert a single record and
> some times it takes more than 30 minutes? Wow! And earlier it was
> consistently less than one second, and you only recently saw it taking more?
> Here are my random questions:
> Is it completely random? No pattern to when it takes a short time or
> long time? Is the long time consistently long - always 30-minutes? Does it
> just randomly take 30-minutes then is quick for many times, then another
> long time?
> What else is going on the server during this time? Does the server CPU
> max out or go idle? What is the disk I/O during this time?
> What is the SQL like? In my experience, often erratic times results
> from changing execution plans. You mention that it is insert record load
> table conventional. Do you mean it is something like:
> insert into mytable values ( . . . . - no subqueries, etc.?
> Your table includes a blob. Does the blob size vary a lot? Is the blob
> stored in the same tablespace as the other columns? Is the blob stored over
> many blocks? I'm just thinking whether there could be any scrambling for
> available blocks. Low likelihood, but just thinking.
> Dennis Williams