Re: Single record insert intermittently slow

From: Rich <>
Date: Thu, 27 Oct 2011 10:35:48 -0700
Message-ID: <>

Thanks, Sidney and Dennis.

We have started tracing of the SQL with: alter system set events 'sql_trace[sql:ffvyx9fzdyzuv] plan_stat=all_executions,wait=true,bind=true'; as we don't know beforehand which session might be bad. Finally got a bad one - waiting for it to end (at 1684054136 bytes right now...).
Will condense and analyze it once it's finished.

Emits of this method of tracing are like: WAIT #47630675251888: nam='db file sequential read' ela= 731 file#=33 block#=394802 blocks=1 obj#=25914 tim=1319736506763016

We know the segment - it's the internal LOB index.

What we don't know is why Oracle wants to scan most (if not all) blocks in this index.
Once the trace is complete, we might get a better picture. However, every block read is not necessarily associated with a wait - they could have come from the cache.

This segment is in a 2KB block TS with a small db_2k_cache_size (64M). This is not an issue on other, higher transaction DBs...

We are not seeing high DBW activity - from a previous AWR: Buffer Pool Statistics DB/Inst: STGSTR/stgstr Snaps: 1514-1515 (25-Oct-11 21:00:29-22:00:32)
-> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

                                                            Free   Writ   Buffer
     Number of Pool       Buffer     Physical    Physical   Buff   Comp     Busy
P      Buffers Hit%         Gets        Reads      Writes   Wait   Wait    Waits
--- ---------- ---- ------------ ------------ ----------- ------ ------ --------
D      204,947   99    3,558,888       43,260      24,747      0      0      474
32k      2,024   97        2,096           57          76      0      0        0
2k      28,444  101 -4.23940E+09   27,589,997       6,409      0      0      206

It is the scanning of this internal LOB index segment that is the performance issue.
Question is why did this just start happening to just this DB?

On Thu, Oct 27, 2011 at 10:16 AM, Dennis Williams <> wrote:
> Rich,
> After reading your followup reply, I wonder if the following is happening:
> 1. A number of inserts are performed. Recall that they aren't immediately
> written to disk, but held in memory. So response is very fast.
> 2. A threshold is reached. Oracle is forced to flush the buffers - write the
> blocks to disk. You experience this as a delay.
> Dennis

Received on Thu Oct 27 2011 - 12:35:48 CDT

Original text of this message