Re: IO and CPU cost - direct path read

Hi Zoran,

I can only speculate in two possible directions:

1. 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_at_yahoo.com> To: <Oracle-L_at_freelists.org> 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