Home » SQL & PL/SQL » SQL & PL/SQL » ORA-12827: insufficient parallel query slaves available (Oracle 10g Linux)
ORA-12827: insufficient parallel query slaves available [message #483858] Thu, 25 November 2010 01:53 Go to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
hi,

We are facing the below error in our Production environment, any idea regarding this ,if means provide your valuable suggestion.

ORA-12827: insufficient parallel query slaves available


Thanks in advance,
Re: ORA-12827: insufficient parallel query slaves available [message #483866 is a reply to message #483858] Thu, 25 November 2010 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-12827: insufficient parallel query slaves available
 *Cause: PARALLEL_MIN_PERCENT parameter was specified and fewer than
         minimum slaves were acquired
 *Action: either re-execute query with lower PARALLEL_MIN_PERCENT or
          wait until some running queries are completed, thus freeing
          up slaves

Regards
Michel
Re: ORA-12827: insufficient parallel query slaves available [message #483868 is a reply to message #483858] Thu, 25 November 2010 02:27 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You statement is failing because it wants to use more parallel servers than are currently available. I would think that you have a hard-coded degree of parallelism, either with hints in the statement or in the DDL of the objects. The standard advice is don't do this, rather enable parallelism on the objects without specifying a degree, and enable automatic parallel tuning by setting the parallel_adaptive_multi_user parameter to TRUE. Then optimizer decide how many to use, depending on workload.
Re: ORA-12827: insufficient parallel query slaves available [message #483891 is a reply to message #483868] Thu, 25 November 2010 05:58 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
But the parallel_adaptive_multi_user parameter is already set to 'TRUE' ,and the query also doesn't have any hints to decide the optimizer.
Re: ORA-12827: insufficient parallel query slaves available [message #483893 is a reply to message #483891] Thu, 25 November 2010 06:10 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
So look at the DDL of the objects, as I said:
select table_name,degree from user_tables;
Re: ORA-12827: insufficient parallel query slaves available [message #483897 is a reply to message #483893] Thu, 25 November 2010 06:53 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member

select table_name,degree from user_tables;


The query returns the table lists and thier degree ,most of the table degree are 1 and 4.
Re: ORA-12827: insufficient parallel query slaves available [message #483902 is a reply to message #483897] Thu, 25 November 2010 07:47 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
So there is a hard-coded degree of parallelism in the DDL.
QED.
Previous Topic: Trigger issue
Next Topic: Dynamic SQL
Goto Forum:
  


Current Time: Thu Mar 28 04:51:45 CDT 2024