Re: row cache lock contention parallel insert

  • From: LS Cheng <exriscer@xxxxxxxxx>
  • To: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
  • Date: Tue, 22 Dec 2009 00:47:33 +0100

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@xxxxxxxxxxxxxxxxxx> wrote:

> On Mon, Dec 21, 2009 at 11:34 AM, Randolf Geist
> <info@xxxxxxxxxxxxxxxxxxxxx> 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
>

Other related posts: