Re: db block get vs consistent gets

Stephane,

The cost of an LIO is determined by many things. It used to be that one row access was one LIO. Now a days you will see that many rows are gotten in a single LIO. That means that the single LIO is more expensive. You will see that depending on the access plan oracle will access one or multiple rows and that will change the cost of the LIO.

Anjo.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, September 23, 2002 10:13 PM

> Has anybody figures about the CPU cost of a consistent get vs a db block
> get? I have always expected it to be higher but not extremely higher. I
> have had a bad surprise today on a process-of-death which I am trying to
> salvage (although it looks a bit more like the last sacrament rather
> than a cure). Basically, the most costly part of a one-hour process is a
> join between 3/4 tables, the biggest of which is 15 million rows. Nested
> loops, nothing in the process likely to be helped by any parallelization
> (?) on the 8 processors, a bit of hope with the partitioning of one of
> the tables but this is not the question.
> Basically, I had got rid of a UNION and a previous test had shown around
> 40% fewer logical reads, and I was putting some hope in it. In fact, it
> was rather worse.
> In the case with the UNION, I had about 22 million logical reads (don't
> laugh) and about 863 seconds of CPU. Without the UNION, I was down to
> about 17,5 million LR (more than expected), with about 15 million db
> block gets and the rest of consistent reads. CPU consumption about
> trebled. Concerning the whole process, the highest time spent waited no
> longer was PIO waits (not surprising with a process accessing 190 G of
> data and a 1.7 G SGA), but LIO CPU calls. Once again, I was expecting
> some change, but not in such proportions.
> Any figures would be welcome to confirm or infirm what I have seen.
>
> TIA
>
> Stephane Faroult
> Oriole Software
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Stephane Faroult
> INET: sfaroult_at_oriole.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Anjo Kolk
  INET: Anjo_at_oraperf.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).