Re: Single record insert intermittently slow

From: Rich <richa03_at_gmail.com>
Date: Thu, 27 Oct 2011 09:03:56 -0700
Message-ID: <CALgGkeA1jr+jjZaAUfby=7QRFCSe9kmUPSMS+9kCsHGGjpLFKA_at_mail.gmail.com>

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 NULL

CONSTRAINT 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
)
TABLESPACE RTDATA
PCTFREE 0
INITRANS 26
LOB (TRHS_DOC) STORE AS
(
TABLESPACE RTBLOB_LARGE
DISABLE STORAGE IN ROW
PCTVERSION 0
CACHE
)
NOPARALLEL
/

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) USING INDEX
TABLESPACE RTINDX
PCTFREE 20
INITRANS 30
REVERSE
/

PROMPT --- Creating Index FK01_HSR_TRANS_HISTORY_BASE

CREATE INDEX FK01_HSR_TRANS_HISTORY_BASE ON HSR_TRANSLATION_HISTORY_BASE (
TRAN_KEY
)
TABLESPACE RTINDX
PCTFREE 20
INITRANS 30
/

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 LOB data.
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 11.2.0.2 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 <oracledba.williams_at_gmail.com> wrote:
> Rich,
>
> 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
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 27 2011 - 11:03:56 CDT