Re: Parallel hint

From: Jonathan Lewis <>
Date: Mon, 16 Feb 2009 07:56:17 -0000
Message-ID: <>

"raja" <> wrote in message
> Yes, i am doing an insert.

I assume you are doing

    insert into table ......
    select .... from other_table ...

Are both the insert part of the plan and the select part of the plan running in parallel.

Have you checked v$sql_plan for the actual plan after running the statement ?

You say you used alter session parallel - that's not a legal command, the actual command you used is likely to be just a few words longer (even including the 'execute immediate') so why not tell us exactly what you did.

> shows that the insert query has parallel process-es p00-p007.
> If i check the execution plan, even if i force degree 4 or degree 8,
> the plan is same.

The degree is never visible in the plan - and execution plans may lie - especially parallel ones, as the actual run-time activity may not allow the number of slaves assumed in the plan generation.

> I will check the v$pq_stat view after running insert and get back to
> you.

See Dion Cho's note - I omitted part of the view name v$pq_tqstat.

> Other observations are :
> 1. when i run the same insert outside the package, its taking p00-
> p031, its showing the same parallelism even though i altered the table
> with noparallel, also i tried to force noparallel hint.

"the table" - which table, the one you are inserting into, the table(s) which you are selecting from ? Why not post the query and plan.

Since you've only been talking about parallel 4 and 8, getting 32 slaves, it looks like you've got a plan that will be running up some slaves that work temporarily and then go idle.

> 2. If i run inside the package, its taking p00-p007.

That is a tiny bit interesting - but could indicate a change in execution plan inside the pl/sql block that avoids generating the multiple excess parallel slaves.

> I feel that inside the procedure, the execute immediate for altering
> session with DML hasn't worked, but this is impossible.

Why is it impossible ?


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Mon Feb 16 2009 - 01:56:17 CST

Original text of this message