Re: IO and CPU cost - direct path read

Hi Zoran,

I can only speculate in two possible directions:



a) The ratio of the cost of parallel access vs. no_parallel is similar to a 
ratio expected on full scan vs. index access

(or bitmap index access)



There are scenarios where the change of the DOP can switch the access path. 
Of course I saw the full hint and you sow the execution plan, so the 
probability of this is rather low.



b) The ratio of the cost of parallel access vs. no_parallel corresponds not 
to a DOP = 2 but to DOP say 40



I'm not really sure about the syntax "parallel(x 2)" (I always use 
"parallel(x,2)" as documented) but using a hint

"parallel(x)" as mentioned bellow you can see more parallel slaves on work 
than expected (especially with parallel_automatic_tuning = false).



I once had to trace the cause why a select on a medium table opens DOP up to 
80. The sql statement was similar to that



select /*+ parallel(a,$USE_LOW_DEGREE) */ .



The reason was that the environment variable was unfortunately not 
initialised. This was on 8.2, if I recall it correctly the parallel hint 
without degree means: "anybody who can read on work!".



Again explain plan and / or 10053 trace should resolve the problem.



HTH



Jaromir D.B. Nemec


----- Original Message ----- 
From: "Martic Zoran" <zoran_martic@xxxxxxxxx>
To: <Oracle-L@xxxxxxxxxxxxx>
Sent: Tuesday, May 24, 2005 10:55 AM
Subject: IO and CPU cost - direct path read


Hi all,


select /*+ full(x) */ count(*) from x;
select /*+ full(x) parallel(x 2) */ count(*) from x;

I did the same on another table y to compare results.
select /*+ full(y) */ count(*) from y;
select /*+ full(y) parallel(x 2) */ count(*) from y;
IO cost:
parallel(x) - 37
full(x) - 1619
parallel(y) - 801
full(y) - 1601

CPU cost:
parallel(x) - 2.5M (+1 to the overall cost)
full(x) - 313M (+55 to the overall cost)
parallel(y) - 55M (+10 to the overall cost)
full(y) - 311M (+54 to the overall cost)

Overall cost:
parallel(x) - 38
full(x) - 1674
parallel(y) - 811
full(y) - 1655




--
http://www.freelists.org/webpage/oracle-l

Other related posts: