Re: IO and CPU cost - direct path read

  • From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
  • To: <zoran_martic@xxxxxxxxx>, <Oracle-L@xxxxxxxxxxxxx>
  • Date: Tue, 24 May 2005 23:37:41 +0200

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.


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


Other related posts: