Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: IO and CPU cost - direct path read

Re: IO and CPU cost - direct path read

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Tue, 24 May 2005 23:37:41 +0200
Message-ID: <051901c560a8$d1568e10$3c02a8c0@JARAWIN>


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

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
Received on Tue May 24 2005 - 17:46:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US