Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> how to explain this result
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.