Re: row cache lock contention parallel insert

From: Greg Rahn <greg_at_structureddata.org>
Date: Tue, 22 Dec 2009 07:35:30 -0800
Message-ID: <a9c093440912220735x6d3728ame31acac2703811ad_at_mail.gmail.com>

The space segment advisor auto job can consume significant resources (basically burn 1 CPU core) if the data dictionary has no stats or very stale stats and there are a large number of partitions in the database. One of the dictionary queries ends up with an NLJ plan when it should have a HJ plan (because of large # of partitions/segments).

On Mon, Dec 21, 2009 at 5:53 PM, Kellyn Pedersen <kjped1313_at_yahoo.com> wrote:
>
> 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... :)

--
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Dec 22 2009 - 09:35:30 CST