Re: Parallel hint

"raja" <dextersunil_at_gmail.com> wrote in message news:cf5b958e-60af-4d48-bc00-d283adda0cb7_at_i24g2000prf.googlegroups.com...
>
> 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 ?

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html