Re: Parallel hint
From: Dion Cho <ukja.dion_at_gmail.com>
Date: Sun, 15 Feb 2009 22:18:21 -0800 (PST)
Message-ID: <28a09d79-963e-4c3c-bacb-d6c31ce0ae65_at_o40g2000prn.googlegroups.com>
On 2¿ù16ÀÏ, ¿ÀÈÄ1½Ã43ºÐ, raja <dextersu..._at_gmail.com> wrote:
> Sorry that i have missed to mention the oracle version, its 10gR1.
>
> Yes, i am doing an insert. when i checked in toad session browser, it
> 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.
> I will check the v$pq_stat view after running insert and get back to
> you.
>
> 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.
> 2. If i run inside the package, its taking p00-p007.
>
> I feel that inside the procedure, the execute immediate for altering
> session with DML hasn't worked, but this is impossible.
>
> I cant understand this ??? Please help.
>
> With Regards,
> Raja.
Dion Cho - Oracle Performance Storyteller
Received on Mon Feb 16 2009 - 00:18:21 CST
Date: Sun, 15 Feb 2009 22:18:21 -0800 (PST)
Message-ID: <28a09d79-963e-4c3c-bacb-d6c31ce0ae65_at_o40g2000prn.googlegroups.com>
On 2¿ù16ÀÏ, ¿ÀÈÄ1½Ã43ºÐ, raja <dextersu..._at_gmail.com> wrote:
> Sorry that i have missed to mention the oracle version, its 10gR1.
>
> Yes, i am doing an insert. when i checked in toad session browser, it
> 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.
> I will check the v$pq_stat view after running insert and get back to
> you.
>
> 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.
> 2. If i run inside the package, its taking p00-p007.
>
> I feel that inside the procedure, the execute immediate for altering
> session with DML hasn't worked, but this is impossible.
>
> I cant understand this ??? Please help.
>
> With Regards,
> Raja.
It's not v$pq_stat, but v$pq_tqstat, like
<<parallel_dml_here>>
commit;
select dfo_number, tq_id, server_type, process,
num_rows, bytes, avg_latency, waits, timeouts
from v$pq_tqstat
order by 1, 2, 3, 4, 5
;
Anyway, with additional infos like execution plan, code template and v $pq_tqstat result, we would bave better discussions.
Dion Cho - Oracle Performance Storyteller
http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
Received on Mon Feb 16 2009 - 00:18:21 CST