Re: Parallel hint
Date: Mon, 16 Feb 2009 07:56:17 -0000
"raja" <dextersunil_at_gmail.com> 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
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.htmlReceived on Mon Feb 16 2009 - 01:56:17 CST