Re: row cache lock contention parallel insert

  • From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
  • To: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>, exriscer@xxxxxxxxx
  • Date: Mon, 21 Dec 2009 17:53:42 -0800 (PST)

I'm a tired DBA tonight, but this query sure looks familiar and if I remember 
correctly, I found it when a job was impacting the large warehouse 
tablespaces/datafile, but is this the job from the space advisor?
 
select * from dba_scheduler_jobs
where program_name like 'AUTO_SPACE%';

I'd check and see, if it's enabled and scheduled, see what kind of elapsed time 
it has, then check your AWR and ADDM reports to see if this is impacting 
performance...
 
Greg, tell me if I'm off here, I know I need a nap this evening... :)
Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
 
"Go away before I replace you with a very small and efficient shell script..."

--- On Mon, 12/21/09, LS Cheng <exriscer@xxxxxxxxx> wrote:


From: LS Cheng <exriscer@xxxxxxxxx>
Subject: Re: row cache lock contention parallel insert
To: "Greg Rahn" <greg@xxxxxxxxxxxxxxxxxx>
Cc: info@xxxxxxxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
Date: Monday, December 21, 2009, 4:47 PM


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: