"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