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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
