Re: row cache lock contention parallel insert
- From: LS Cheng <exriscer@xxxxxxxxx>
- To: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 23 Dec 2009 09:45:49 +0100
Hi
Thanks for the info
The workload is more or less every 5 minutes 10 data load processes kicks
in, 3 of them needs to load around 12 million of rows each (going through
ETL which are 3 tables per process), the rest from 300000 to 3 million.
All insert append with parallel dml and PQ for the SELECT (insert...select).
Basically there is only 10 application processes but with 64 slaves running
almost all the time. All ETL Tables has more or less 4000 to 5000
subpartitions
I noticed also compatible is set to 10.2.0.1 instead of 10.2.0.4, might be
some old bug? Probably because when I was doing test with a test database,
also 10.2.0.4 with compatible set to 10.2.0.3 I managed to reproduce massive
row cache contention, when I changed compatible to 10.2.0.4 row cache
requests reduced by a factor of 10. I will change compatible to 10.2.0.4
after holidays because they dont allow anymore system changes until then
Thanks!
--
LSC
On Tue, Dec 22, 2009 at 4:30 PM, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote:
> That query is used for the row cache read call back for SEG$ when the
> segment entry is not available in the row cache. Once the row cache
> entry is available, all the reads should go through row cache rather
> than from disk. It seems that something may be wrong with the row
> cache layer.
>
> Can you describe the workload?
> What are the other top SQLs?
>
> On Mon, Dec 21, 2009 at 3:47 PM, LS Cheng <exriscer@xxxxxxxxx> wrote:
> > Yes there are many executions on this two query:
> >
> > 2ym6hhaq30r73 - around 11 millions executions per hour
> > SELECT type#, blocks, extents, minexts, maxexts, extsize, extpct, user#,
> > iniexts, NVL (lists, 65535), NVL (GROUPS, 65535), cachehint,
> hwmincr,
> > NVL (spare1, 0), NVL (scanhint, 0)
> > FROM seg$
> > WHERE ts# = :1 AND file# = :2 AND block# = :3
>
> --
> Regards,
> Greg Rahn
> http://structureddata.org
>
Other related posts:
