Has anybody any idea why a query against tables on which very few if any update are applied would display a high number of consistent gets ? Details : Big query involving 4/5 tables, most of them partitioned. This is a test database, db block buffers about 100M, 5,000,000 of logical reads with 8K blocks which means that the SGA is flushed a number of times. The execution plan starts with a partition scan, then a series of nested loops (hash join disappointing). The number of db block gets corresponds to the number of blocks read during the partition scan; everything else appears as consistent gets. You can rule out delayed cleanout, since the same behaviour is displayed when the same query is run over and over and not update at all takes place. Another curious symptom is that the number of rows returned per second decreases by a factor 3 or 4 between the beginning and the end of the query. Believe me, no hideous hidden scan of table of partition.
TIA, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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).
