Prev: update Oracle from 10.2.0.2.0 to 10.2.0.2.0
Next: Parallelise Function call
From: z1hou1 on 7 May 2010 23:39
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


From: Sybrand Bakker on 8 May 2010 03:32
On Fri, 7 May 2010 20:39:25 -0700 (PDT), z1hou1 <z1hou1(a)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
From: vsevolod afanassiev on 8 May 2010 19:02
I doubt that 'modifying most inserts to use /*+ APPEND */ hint' is the
right thing to do:
- The hint is supposed to be used only for bulk load, in other words
in situation where very large number of records is being inserted by
INSERT AS SELECT statement
- You shouldn't use it for single-row INSERT (INSERT INTO TABLE VALUES
())
- INSERT WITH /*+ APPEND */ hint locks table in exclusive mode so no
other insert/update/delete/(select for update) can be executed
- INSERT WITH /*+ APPEND */ hint inserts only above the high water
mark, so you may end up with fragmented table

To answer your question: obviously something is being executed in
parallel here, either SELECT part or INSERT part. There are two things
you can do:
- Increase limit on sessions - normally this is done by setting
parameter processes, however this parameter isn't dynamic, to change
it the database needs to be restarted
- Reduce number of parallel servers. As far as a remember parameter
parallel_max_servers is dynamic, if you reduce the value then Oracle
will use serial execution and you won't needs as many sessions.
From: z1hou1 on 10 May 2010 22:34
Thank you vsevolod for explaining the correlation between parallel
servers and number of sessions. The issue was simply a badly
configured prod environment. We unearthed other issues, such as a
ridiculously low pga_aggregate_target (64M) and workarea_size_policy
set to 64M!. But thank you again.

z1hou1
 | 
Pages: 1
Prev: update Oracle from 10.2.0.2.0 to 10.2.0.2.0
Next: Parallelise Function call