Re: IO and CPU cost - direct path read

From: Martic Zoran <zoran_martic_at_yahoo.com>
Date: Wed, 25 May 2005 00:56:22 -0700 (PDT)
Message-ID: <20050525075622.42548.qmail@web52604.mail.yahoo.com>

Jaromir,

Thanks a lot for the response.

You were right about the different parallelism because in trying to copy SQL's I left out 2 in on of the tests like this:

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

I am sometimes just stupid monkey :)

Of course on 24 CPU machine you can imagine what is Oracle using for default parallel parameters :)

Both ways of writing hints are described by Oracle and are valid.
I am almost always using , style. Not sure why I did it differently here, sometimes you just like to do it differently.

One of the driving forces to be creative is to not have usual daily things done the same way, at least that is what is written in the books.
The famous Edison who invented the bulb did not want to hire anybody who salted the soup before trying it first. It was so robot behaviour to him, no thinking :)
I believe that we are all doing similar things from time to time when the cost of doing it differently is higher then just to be lazy.

My mistake here is tipical example of copy/paste technique in IT where even the same person can make the mistake.

Regards,
Zoran

• jaromir nemec <jaromir_at_db-nemec.com> wrote:
  > 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
  

  ---

Yahoo! Mail
Stay connected, organized, and protected. Take the tour: http://tour.mail.yahoo.com/mailtour.html

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

Received on Wed May 25 2005 - 04:01:09 CDT