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).
Received on Mon Sep 23 2002 - 15:13:36 CDT
