Re: row cache lock contention parallel insert
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
"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)
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
-- 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-lReceived on Mon Dec 21 2009 - 19:53:42 CST