Re: Parallel Query Idle Wait - Slave

Ed Stevens <nospam_at_noway.nohow> schreef in berichtnieuws ub7iavglggu2c9r7je261n2q96lshj3fq4_at_4ax.com...
| Platform - Oracle 8.0.6 EE on Win NT
|
| Customer complains that performance on a particular query went from "a
| couple of minutes" to "3 or 4 hours"
|
| Ran a utlbstat (this is 8.0, so don't have statspack), started the
| query, let it run 10 minutes, killed it and ran utlestat. Fed results
| to Oraperf.
|
| 99.63% of response time was wait time. 90.34% of wait time was
| "Parallel Query Idle Wait - Slave" Search of MetaLink turned up
| several items that said essentially "this is an idle event, don't
| worry about it." A Google search of this ng archive turned up
| nothing.
|
| Is this wait event a red herring? If not, where do I go from here?
| I've never had a need to look at tuning parallel processing. Relevent
| init parms are:
|
| parallel_adaptive_multi_user = false
| parallel_broadcast_enabled = false
| parallel_default_max_instances=0
| parallel_execution_message_size=2148
| parallel_instance_group=[null]
| parallel_max_servers=16
| parallel_min_message_pool=154656
| parallel_min_percent=0
| parallel_min_servers=8
| parallel_server=false
| parallel_server_idle_time=5
| parallel_transaction_resource_timeout=300
|
| init.ora was last modified 15 months ago. Problem began 3 weeks ago.
|

Not so familiar with parallel options but a try. The init.ora makes it possible to use parallel query. If it is used is set at table level. Check user_tables.degree. When not 1 parallel query will be used. Was it altered by accident on the table(s) involved? Check the create/alter tables parallel_clause. You switch it of with: alter table <tablename> noparallel;
I've had some trouble where not properly tuned parallel stuff makes performance much worse instead of better. Received on Fri Apr 25 2003 - 15:18:02 CDT