Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> how to explain this result

how to explain this result

From: Limin Guo <guo_at_andrews.edu>
Date: Mon, 10 May 1999 20:33:43 GMT
Message-ID: <Pine.GSO.4.10.9905101609360.6553-100000@deepika.squonk.net>


My developers turned me a query and claimed this query takes 6 minutes to run under production instance and almost 30 minutes to run under development instance. I then check the execution plan in both instacnes, superisely they are identical. By using sql trace and tkprof, I saw number of "disk" read is 22478 in production instance and 191580 in development. The only difference I could
think of is SGA sizes are not same (160 M in production and 32 M in development). To prove that number of "disk" read is caused by SGA size, I increased SGA (db_block_buffer) to 160 M in my development instance and run the same query again, the number of "disk" read dropped significantly to 22478.

The question I have here is why db_block_buffer size has so much impact on number of disk read?

Any helps would be highly appreciated.

    743 SORT (AGGREGATE)
   1097 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PSAJOB' (NON-UNIQUE) Received on Mon May 10 1999 - 15:33:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US