Fwd: 9.2 - Parallel Query Not Working

  • From: "Tony Adolph" <tony.adolph.dba@xxxxxxxxx>
  • To: david@xxxxxxxxxxxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 28 Mar 2008 09:51:41 +1300

Hi David,

Further to the post about the checking the explain plan.....

 You'll only get parallel table access for full table scans, .i.e. the
 table is not accessed via index lookup.

 Cheers
 Tony




 On Fri, Feb 29, 2008 at 10:10 AM,  <krish.hariharan@xxxxxxxxxxxx> wrote:
 >
 >
 >
 >
 > Out of curiosity
 >
 > Are the execution plans the same (using explain plan and sql_trace/10046)
 > I wonder if you could use 10053 to determine the decision taken the by the
 > optimizer
 > Are the statistics the same
 >
 >
 >
 >
 > Regards,
 >
 > -Krish
 >
 > Krish Hariharan
 >
 > President/Executive Architect, Quasar Database Technologies, LLC
 >
 > http://www.linkedin.com/in/quasardb
 >
 >  ________________________________
 >
 >
 > From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
 > On Behalf Of David Aldridge
 >  Sent: Thursday, February 28, 2008 1:57 PM
 >  To: RS2273@xxxxxxx; mwf@xxxxxxxx; Oracle List
 >
 >  Subject: RE: 9.2 - Parallel Query Not Working
 >
 >
 >
 >
 > No, not RAC.
 >
 >  "Shamsudeen, Riyaj" <RS2273@xxxxxxx> wrote:
 >
 >
 > David
 >
 >
 >             Is this RAC? Can you print exactly values for
 > parallel_instance_group and instance_groups?
 >
 >  ________________________________
 >
 >
 >
 > From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
 > On Behalf Of Mark W. Farnham
 >  Sent: Thursday, February 28, 2008 11:17 AM
 >  To: david@xxxxxxxxxxxxxxxxxx; 'Oracle List'
 >  Subject: RE: 9.2 - Parallel Query Not Working
 >
 >
 >
 >
 >
 > How much of that table is already in the buffer cache when PQ stops being
 > operative?
 >
 >
 >
 >
 >
 > mwf
 >
 >
 >
 >
 >  ________________________________
 >
 >
 >
 > From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
 > On Behalf Of David Aldridge
 >  Sent: Thursday, February 28, 2008 12:03 PM
 >  To: Oracle List
 >  Subject: 9.2 - Parallel Query Not Working
 >
 >
 >
 >
 >
 >
 > On a 9.2.0.6 database on Solarix 64, which is created every day from a
 > magical SAN-based mirror process thing (excuse the tech talk), we alter a
 > particular table to a default degree of parallelsim of 12. A process which
 > performs a full scan of that table picks up the degree of 12 and completes
 > in a couple of hours.
 >
 >
 >
 >
 >
 >
 >
 > Later in the day we can no longer prompt the instance to give any
 > parallelism.
 >
 >
 >
 >
 >
 >
 >
 > I've tried everything obvious ... setting parallel_adaptive_multiuser to
 > false, parallel max servers to 48, used hints, made sure the table's default
 > dop is 12, checked that PROCESSES is reasonably high (500+), that the server
 > is not very busy (less than 10 active sessions), ALTER SESSION FORCE
 > PARALLEL QUERY, but nothing we do will prompt PQ to be used on even the most
 > simple SELECT * FROM ... queries.
 >
 >
 >
 >
 >
 >
 >
 > Any thoughts on this are much appreciated.
 >
 >
--
//www.freelists.org/webpage/oracle-l


Other related posts: