Parallel query on when it 's not supposed to be (?) 2004-09-14 - By Bobak, Mark
Janine, yes, for that table, do:
alter table table_name parallel (degree 1);
and also set parallel_max_servers to 0, as the other Mark suggested.
Um, the table w/ degree set to default, was that table involved in
the problem query you described in the original post?
-- --Original Message-- --
From: Janine A Sisk [mailto:janine@(protected)]
Sent: Tuesday, September 14, 2004 4:20 PM
To: Bobak, Mark
Cc: oracle-l@(protected)
Subject: Re: Parallel query on when it 's not supposed to be (?)
Here you go:
SQL > select table_name from dba_tables where degree >1;
select table_name from dba_tables where degree >1
*
ERROR at line 1:
ORA-01722 (See ORA-01722.ora-code.com): invalid number
SQL > select index_name from dba_indexes where degree >1;
no rows selected
The error turned out to be because degree is a varchar2(10) in=20
dba_tables, so I did this instead:
SQL > select distinct degree from dba_tables;
DEGREE
-- ---- ---- ---- ---- ---- ---
1
DEFAULT
Interesting... I think the DEFAULT might be the problem, because the=20
tables that have this set are very closely related to this query. =20
Should I change them to 1? Actually, neither of those values is=20
exactly as it appears:
SQL > select distinct '| ' || degree || '| ' from dba_tables;
'| '||DEGREE|| '| '
-- ---- ---- ---- ---- ---- -----
| 1|
| DEFAULT|
So I guess I would change it to 3 spaces + 1. Sounds like a magic=20
incantation or something!
thanks,
janine
On Sep 14, 2004, at 4:10 PM, Bobak, Mark wrote:
> Janine,
>
> Try this:
>
> select table_name from dba_tables where degree >1;
> select index_name from dba_indexes where degree >1;
>
> -- --Original Message-- --
> From: oracle-l-bounce@(protected)
> [mailto:oracle-l-bounce@(protected)]On Behalf Of Janine A Sisk
> Sent: Tuesday, September 14, 2004 3:58 PM
> To: oracle-l@(protected)
> Subject: Parallel query on when it 's not supposed to be (?)
>
>
> Hi all,
>
> I have a mystery on my hands. There must be a good explanation, but=20
> so=3D20
> far it has eluded me; I 'm hoping someone out there will know the=3D20
> answer.
>
> Configuration: Oracle 8.1.7.4 64-bit on Solaris 9. System is a=3D20
> SunFire V440.
>
> As far as I can tell, this system does not have the parallel =
query=3D20
> facility turned on:
>
> SQL > show parameters para
>
> NAME TYPE VALUE
> -- ---- ---- ---- ---- ---- ---- --- -- ----=3D20
> -- ---- ---- ---- ---- ---- --
> fast_start_parallel_rollback string FALSE
> optimizer_percent_parallel integer 0
> parallel_adaptive_multi_user boolean FALSE
> parallel_automatic_tuning boolean FALSE
> parallel_broadcast_enabled boolean FALSE
> parallel_execution_message_size integer 2152
> parallel_instance_group string
> parallel_max_servers integer 5
> parallel_min_percent integer 0
> parallel_min_servers integer 0
> parallel_server boolean FALSE
>
> NAME TYPE VALUE
> -- ---- ---- ---- ---- ---- ---- --- -- ----=3D20
> -- ---- ---- ---- ---- ---- --
> parallel_server_instances integer 1
> parallel_threads_per_cpu integer 2
> recovery_parallelism integer 2
>
> Everything is off, right? Even parallel rollback has been disabled.
>
> I have a very simple query:
>
> select count(dotlrn_member_rels_approved.rel_id)
> from dotlrn_member_rels_approved
> where dotlrn_member_rels_approved.community_id =3D3D 1767463;
>
> dotlrn_member_rels_approved is a view, which is a subset of =
another=3D20
> view, which is a simple join. Nothing obviously tricky there.
>
> This query, when profiled via autotrace, produces the following=3D20
> execution plan:
>
> Execution Plan
> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -----
> 0 SELECT STATEMENT Optimizer=3D3DCHOOSE (Cost=3D3D64 =
Card=3D3D1 =3D
> Bytes=3D3D31)
> 1 0 SORT (AGGREGATE)
> 2 1 SORT* (AGGREGATE) =
> =3D20
> :Q428002
> 3 2 NESTED LOOPS* (Cost=3D3D64 Card=3D3D65 =
Bytes=3D3D2015) =20
> =3D
> =3D20
> :Q428002
> 4 3 HASH JOIN* (Cost=3D3D64 Card=3D3D65 Bytes=3D3D1690) =
=20
> =3D
> =3D20
> :Q428002
> 5 4 TABLE ACCESS* (BY INDEX ROWID) OF 'ACS_RELS '=20
> (Cost=3D20
> :Q428000
> =3D3D17 Card=3D3D65 Bytes=3D3D780)
>
> 6 5 INDEX (RANGE SCAN) OF =
'ACS_RELS_OBJECT_ID_ONE_ID
> X ' (NON-UNIQUE) (Cost=3D3D1 Card=3D3D65)
>
> 7 4 TABLE ACCESS* (FULL) OF 'MEMBERSHIP_RELS ' =3D
> (Cost=3D3D46=3D20
> :Q428001
> Card=3D3D34577 Bytes=3D3D484078)
>
> 8 3 INDEX* (UNIQUE SCAN) OF=20
> 'DOTLRN_MEMBER_RELS_REL_ID_P=3D20
> :Q428002
> K ' (UNIQUE)
>
>
>
> 2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */=3D20
> SYS_OP_MSR(COUNT(A1.C0
> )) FROM (SELECT /*+ ORDERED=20
> NO_EXPAN
>
> 3 PARALLEL_COMBINED_WITH_PARENT
> 4 PARALLEL_COMBINED_WITH_PARENT
> 5 PARALLEL_FROM_SERIAL
> 7 PARALLEL_TO_PARALLEL SELECT /*+ Q428001 NO_EXPAND=3D20
> ROWID(A1) */ A1
> . "REL_ID " C0,A1. "MEMBER_STATE " C1=20
> FR
>
> 8 PARALLEL_COMBINED_WITH_PARENT
>
> Huh? How is this possible?
>
> Furthermore, when I do a 10046 event trace and look at the tkprof=3D20
> report, I don 't see anything about parallel:
>
> Rows Row Source Operation
> -- ---- -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---
> 1 SORT AGGREGATE
> 0 SORT AGGREGATE
> 0 NESTED LOOPS
> 0 HASH JOIN
> 0 TABLE ACCESS BY INDEX ROWID ACS_RELS
> 109 INDEX RANGE SCAN (object id 26428)
> 0 TABLE ACCESS FULL MEMBERSHIP_RELS
> 0 INDEX UNIQUE SCAN (object id 26694)
>
> I have even tried doing an "explain plan for " and then running=3D20
> utlxplp.sql to look at the plan, but the parallel part is not there.
>
> Even more perplexing, it does look like parallel query is turned =
on,=3D20
> despite the parameter settings:
>
> SQL > select * from v_$px_process;
>
> SERV STATUS PID SPID SID SERIAL#
> ---- -- ------ -- ---- -- -- ------ -- ---- -- -- ---- --
> P000 AVAILABLE 24 5175
> P001 AVAILABLE 26 5177
> P002 AVAILABLE 27 5179
> P003 AVAILABLE 28 5181
> P004 AVAILABLE 29 5183
>
> I first started looking at this yesterday and at that time, this=20
> select=3D20
> returned no rows. But now it does. I assume that executing the=20
> query=3D20
> caused the parallel facility to "wake up " since it was needed, but=20
> I 'm=3D20
> only guessing. This is the development server and it 's fairly=20
> lightly=3D20
> used (compared to the production server), so it 's not implausible=20
> that=3D20
> the query doesn 't get executed very often.
>
> One last detail: fast_start_parallel_rollback was originally set =
to=3D20
> LOW, which I believe is the default. We set it to FALSE via an=20
> "alter=3D20
> system " command but it doesn 't seem to have changed anything. This=20
> may=3D20
> not be important but I mention it for the sake of completeness.
>
> BTW, the reason I care about this is that I 'm trying to tune the=3D20
> production server and a fair number of waits associated with=20
> parallel=3D20
> query are showing up in the statspack report. Since parallel query=20
> is=3D20
> not supposed to be turned on there either, I started looking into =
it=3D20
> and found that both systems are exhibiting this bizarre (to me,=20
> anyway)=3D20
> behavior.
>
> Can anyone a) explain what the heck is going on here and b) tell me=20
> how=3D20
> to drive a stake through the heart of parallel query on this system?
>
> thanks,
>
> janine
>
> --
> http://www.freelists.org/webpage/oracle-l
> --
> http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
|
|
