Re: INSERT /*+ append */ hint

From: Sybrand Bakker <sybrandb_at_gmail.com>
Date: Sat, 08 May 2010 09:32:10 +0200
Message-ID: <4o4au5lsjdcldip08phms669giji7q1nl1_at_4ax.com>

On Fri, 7 May 2010 20:39:25 -0700 (PDT), z1hou1 <z1hou1_at_gmail.com> wrote:

>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
>

You can configure parallel execution on

- database level
- table level
- statement level

Your production database must have parallel execution configured.

-- 
Sybrand Bakker
Senior Oracle DBA

Received on Sat May 08 2010 - 02:32:10 CDT