RE: Solution to "9.2 - Parallel Query Not Working"

Hilarious indeed. Now, being a recovering paranoid (aka no longer an operational DBA), I would urge you to carefully consider why someone would disable parallel query from an entire consumer group.  

As the delerium tremens of rampaging paranoia race through my gray matter, I being to suspect there are a sufficient number of hard to find jobs spawning parallel queries at times inopportune for interactive performance so that this limitation was imposed to treat the symptoms. Of course if the DBA was sufficiently lazy (or sleep deprived) that could have been one job, but it was just too much trouble at the moment to track down how it was spawned.  

Anyway, be on the lookout for jobs that have newly (re-)acquired the characteristic of running in parallel.  

The latter is an oft seen symptom of refusing to grant (or being sluggish in implementing) control over cron on a particular server to a mere DBA, who nevertheless can access the database remotely from somewhere said DBA does have cron. All of these are yet more reasons DBAs, storage managers, and system administrators should work together closely and be mutually supportive of each other and each other's choices in beverage. Beyond beverages, the single best bond is a jointly administered shared maintenance of the 365.25, 24x7 schedule of automatically run jobs.  

Regards,  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Aldridge
Sent: Tuesday, April 01, 2008 11:04 PM
To: Oracle List
Subject: Solution to "9.2 - Parallel Query Not Working"  

Many thanks for the responses I had both in public and in private on this.  

The answer was this: "Resource Manager"  

Apparantly, "Several Years Ago" someone configured Resource Manager on this database and in the early hour of every morning a cron job modified the consumer group that everyone is on so that parallel query was no longer available for them. The DBA's had forgotten.  

*ahem*    

Which reminds me ... heh, funny story ... apparantly there was this other cron job running off of a completely different host that was truncating a table on one of our BI systems every Saturday morning, and nobody noticed for three years. Oh, the hilarity.  

Anyway ...  

Try the veal, and don't forget to tip your waitress.

• Original Message ---- From: David Aldridge <david_at_david-aldridge.com> To: Oracle List <oracle-l_at_freelists.org> Sent: Thursday, February 28, 2008 12:03:14 PM 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.  

--
http://www.freelists.org/webpage/oracle-l