Re: row cache lock contention parallel insert

  • From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
  • To: greg@xxxxxxxxxxxxxxxxxx
  • Date: Thu, 24 Dec 2009 08:18:20 -0800 (PST)

"Kellyn raises hand for the monstrous number of partitions issue"  :)  As we 
are in a stabalize the environment mode here, since this company just migrated 
from a mainframe to Oracle this year, I did disable the automated job in our 
larger systems and request the other DBA's that if they wanted to run the 
segment advisor, execute it manually.


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 Tue, 12/22/09, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote:


From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
Subject: Re: row cache lock contention parallel insert
To: "Kellyn Pedersen" <kjped1313@xxxxxxxxx>
Cc: exriscer@xxxxxxxxx, info@xxxxxxxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
Date: Tuesday, December 22, 2009, 8:35 AM


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@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l





      

Other related posts: