Re: row cache lock contention parallel insert

From: LS Cheng <exriscer_at_gmail.com>
Date: Tue, 22 Dec 2009 00:47:33 +0100
Message-ID: <6e9345580912211547p12ab0b0cq2de24965522c8a71_at_mail.gmail.com>

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

And top 4 segment logical reads:

           Tablespace                      Subobject  Obj.       Logical
Owner         Name    Object Name            Name     Type         Reads

%Total

---------- ---------- -------------------- ---------- ----- ------------
-------
SYS        SYSTEM     I_FILE#_BLOCK#                  INDEX   34,485,856
19.60
SYS        SYSTEM     I_OBJ2                          INDEX   17,154,800
9.75
SYS        SYSTEM     SEG$                            TABLE   16,886,000
9.60
SYS        SYSTEM     I_OBJ1                          INDEX   12,866,416

7.31

Thanks!

--
LSC




On Mon, Dec 21, 2009 at 10:17 PM, Greg Rahn <greg_at_structureddata.org> wrote:



> On Mon, Dec 21, 2009 at 11:34 AM, Randolf Geist

> <info_at_sqltools-plusplus.org> wrote:

> > Still it is interesting that using the explicit partition pruning syntax

> changes the outcome of your test case.

>

> Using partition extended syntax reduces the locks and metadata

> required, so the issue seems to point in that direction, not extent

> allocation, etc.  If it were the latter, it would reproduce in both

> cases.

>

> There should be some dictionary query on a $ table (like SEG$, TSQ$,

> etc) that shows up, I would think, in the ASH/AWR report.  Finding

> that SQL might make it more apparent what the issue may be.

>

> --

> Regards,

> Greg Rahn

> http://structureddata.org

>


--
http://www.freelists.org/webpage/oracle-l

Received on Mon Dec 21 2009 - 17:47:33 CST