INSERT /*+ append */ hint

From: z1hou1 <z1hou1_at_gmail.com>
Date: Fri, 7 May 2010 20:39:25 -0700 (PDT)
Message-ID: <0760ed8b-ba6d-44dd-9492-a247508f94e5_at_k29g2000yqh.googlegroups.com>



Hi,
Oracle 10.2.0.4, Solaris

We migrated to the database a code change where we decided to modify most inserts with an /*+ APPEND */ hint. We found the exercise to be fine on all our non-prod databases.

In production, though we ended up with a ORA-12801 (error signaled in parallel query server P006) followed by a ORA-00018 maximum number of sessions exceeded.

This was the only change. I do admit that for a 16 CPU box, the parallel_max_servers is 135 and the sessions is 170. I intend to take this up with the DBAs since the numbers were 285 and 335 respectively on the non-prod boxes. But this has been the case for sometime.

We have not had any variation in load volumes.

My question is, does the /*+ APPEND */ hint cause more parallel processes and/or sessions to be created? The inserts do not have a parallel hint and the tables are all by default NOPARALLEL.

Regards,
z1hou1 Received on Fri May 07 2010 - 22:39:25 CDT

Original text of this message