Re: Single record insert intermittently slow
- From: Rich <richa03@xxxxxxxxx>
- To: Dennis Williams <oracledba.williams@xxxxxxxxx>
- Date: Thu, 27 Oct 2011 10:35:48 -0700
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
<oracledba.williams@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
