Re: row cache lock contention parallel insert
From: LS Cheng <exriscer_at_gmail.com>
Date: Sun, 20 Dec 2009 18:41:15 +0100
Message-ID: <6e9345580912200941p67009ba0xb0759b8e7088cf28_at_mail.gmail.com>
AWR shows row cache lock as top second wait event
Date: Sun, 20 Dec 2009 18:41:15 +0100
Message-ID: <6e9345580912200941p67009ba0xb0759b8e7088cf28_at_mail.gmail.com>
AWR shows row cache lock as top second wait event
I did further testing, it seems that it's the APPEND hint makes tons of row cache queries, dc_users, dc_objects, dc_object_ids are queried thousand of times for a simple insert of a single row. In order to reduce row cache queries I specified the in the insert statement the partition name, this reduced from 176502, 176419, 264684 dc_* gets to 468, 401 and 1084.
So APPEND + partition clause gives good performance.
If I dont use APPEND clause the performance is well with and without specifying partition.
So I have a doubt, why append needs to make so many recursive queries to row cache (even without parallel) :-?
Thanks!
-- LSC *With APPEND only* set serverout on size 10000 alter session disable parallel query; alter session disable parallel dml; exec snap_rowcache.start_snap insert /*+ append */ into t1 select ................ t2 exec snap_rowcache.end_snap Parameter Usage Fixed Gets Misses Scans Misses Comp Mods Flushes --------- ----- ----- ---- ------ ----- -------------- ---- ------- dc_segments 0 0 100 0 0 0 0 3 3 dc_tablespaces 0 0 63 0 0 0 0 0 0 dc_tablespace_quotas 0 0 3 3 0 0 0 3 3 dc_users 0 0 176,502 0 0 0 0 0 0 dc_objects 0 0 176,419 0 0 0 0 0 0 dc_global_oids 0 0 28 0 0 0 0 0 0 dc_object_ids 0 0 264,684 0 0 0 0 0 0 dc_usernames 0 0 10 0 0 0 0 0 0 dc_database_links 0 0 4 0 0 0 0 0 0 dc_profiles 0 0 3 0 0 0 0 0 0 dc_users 0 0 27 0 0 0 0 0 0 *With APPEND + PARTITION* set serverout on size 10000 alter session disable parallel query; alter session disable parallel dml; exec snap_rowcache.start_snap insert /*+ append */ into t1 partition(p1) select ................ exec snap_rowcache.end_snap Parameter Usage Fixed Gets Misses Scans Misses Comp Mods Flushes --------- ----- ----- ---- ------ ----- -------------- ---- ------- dc_segments 0 0 2 0 0 0 0 0 0 dc_tablespaces 0 0 7 0 0 0 0 0 0 dc_users 0 0 468 0 0 0 0 0 0 dc_objects 1 0 401 1 0 0 0 0 0 dc_object_ids 0 0 1,084 0 0 0 0 0 0 dc_histogram_defs 73 0 292 73 0 0 0 0 0 dc_histogram_data 0 0 52 0 0 0 0 0 0 dc_users 0 0 3 0 0 0 0 0 0 *Without APPEND and Partition* set serverout on size 10000 alter session disable parallel query; alter session disable parallel dml; exec snap_rowcache.start_snap insert into t1 select ................ t2 exec snap_rowcache.end_snap Parameter Usage Fixed Gets Misses Scans Misses Comp Mods Flushes --------- ----- ----- ---- ------ ----- -------------- ---- ------- dc_users 0 0 74 0 0 0 0 0 0 dc_objects 0 0 3 0 0 0 0 0 0 dc_global_oids 0 0 6 0 0 0 0 0 0 dc_object_ids 0 0 495 0 0 0 0 0 0 dc_histogram_defs 0 0 219 0 0 0 0 0 0 dc_histogram_data 0 0 52 0 0 0 0 0 0 On Sun, Dec 20, 2009 at 6:00 PM, Greg Rahn <greg_at_structureddata.org> wrote:Received on Sun Dec 20 2009 - 11:41:15 CST
> What does the ASH/AWR report show?
>
> If you have initial extents of 10M for each segment and you are not
> allocating numerous additional extents, then its unlikely an extent
> allocation issue, correct?
>
>
> On Sun, Dec 20, 2009 at 5:08 AM, LS Cheng <exriscer_at_gmail.com> wrote:
> > Since my results are not consistent I am not really sure if the problem
> is
> > caused by extent or segment space allocation.
>
> --
> Regards,
> Greg Rahn
> http://structureddata.org
>
-- http://www.freelists.org/webpage/oracle-l