Re: row cache lock contention parallel insert

From: Kellyn Pedersen <kjped1313_at_yahoo.com>
Date: Mon, 21 Dec 2009 17:53:42 -0800 (PST)
Message-ID: <15670.71960.qm_at_web32003.mail.mud.yahoo.com>

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_at_gmail.com> wrote:

From: LS Cheng <exriscer_at_gmail.com>
Subject: Re: row cache lock contention parallel insert To: "Greg Rahn" <greg_at_structureddata.org> Cc: info_at_sqltools-plusplus.org, oracle-l_at_freelists.org 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_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 - 19:53:42 CST