Oracle parallel_max_servers

[former_member239674]

Hello

I notice I cant seem to raise a call from SAP now as they have changed their web site so they send me to this SDN forum, oh well so much for paying a support license!.

I have the following problem:

On SAP R/3 46C system we use parallel process because our db is 4 TB+.

So for certain queries we have to force parallelism.

Now we have the following in our init.ora

parallel_max_servers integer 240

parallel_min_percent integer 0

parallel_min_servers integer 0

Now this means it will create parallel processes as it sees fit and then terminate the process when done because min setting is 0

However it has used up all 240 now and so I cant do anything in parallel because their are no free parallel processes.

The system is idle so I am assuming their is a bug in oracle where it doesnt free up the parallel processes

Eg lets find them all

SQL> !ps -ef | grep ora_p | wc -l

240

So I have 240 ora_p processes still hanging around, if I kill the process from unix level it still doesnt create a process, so the only option appears to be restart the database, which is a bit annoying.

Anyone any idea why this is happening?

Kind Rgds

Tony

[former_member239674]

Hi

Thankyou for your replies however I suspect this is a bug in oracle, I have tried to reproduce on a test server the db version is 9.2.0.7. However on the test server I never seem to get this condition.

So I am a bit stuck - never mind we will stop/ restart the database tomorrow

Kind Rgds

Tony

[Former Member]

tony,

One doc says like .....

For every process, one chunk of pga memory is allocated. If process is

loaded in memory during instance startup because parallel_min_servers

requested that, chunk remains allocated until instance shutdown. That

chunk has attribute "permanent".

On the opposite, there are "temporary" processes and "temporary" chunks.

Example: parallel_min_servers=10, parallel_max_servers=40, user requested

for query parallelism: alter ... parallel 20. There will be 10 permanent

processes and permanent chunks. Also, Oracle will dynamicaly start

additional processes to satisfy user request for parallel execution. Let's

say 10 temporary processes and 10 temporary chunks. If user requests more

than allowed by parallel_max_servers or system limits, alter command will

return an ORA error. If user requested alter ... parallel 6, no temporary

processes will be created. Only permanent processes will be used.

Setting parallel_min_servers=0 will make all processes and chunks temporary.

At the end of operation, the chunk used for one query is destroyed. The next

query will use new, clean set of chunks.

parallel_max_servers - if you set some big number, for example 255, Oracle

will limit effective (real) max number of processes taking into calculation

number of processors on that machine and number of threads per processor.

parallel_min_servers - basic purpose of this parameter is to tell Oracle

how many processes to start on instance startup

and to keep in memory, in order to save time needed

for process creation for each query.

for 10.2 Rec value for PARALLEL_MAX_SERVERS is #CPUs (database central processing units) * 10

Note 651060 will address all parallel questions

Vinod

[Former Member]

hi

can u plz tell abt the oracle version as well os detiail u r using ..

Regards

Deepesh