Re: Single record insert intermittently slow

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.

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...

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
<oracledba.williams_at_gmail.com> 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