Re: how to explain this result

A copy of this was sent to Limin Guo <guo_at_andrews.edu> (if that email address didn't require changing) On Mon, 10 May 1999 20:33:43 GMT, you wrote:

>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?
>

your doing lots of indexed reads. indexed reads are block IOs. When you have 32m of SGA, the blocks you read at one point in time are being flushed to make room for new blocks to come in (since the SGA is smaller, the result cannot be cached). The problem is, the index is causing you to read the same block again later and you must read it from disk once more since its no longer cached (we flushed it to make room for some other block).

by making the SGA bigger, you are not flushing that block -- and the physical IOs go way down. With the small SGA, we might read the block, flush it, read it, flush it, many times. With the large SGA -- one read and we are done

>Any helps would be highly appreciated.
>
>******** explain plan *********
>Rows Execution Plan
>------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 372 FILTER
> 372 NESTED LOOPS
> 324818 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PS_JOB'
> 286339 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'PS_POSITION_DATA'
> 784772 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PS_POSITION_DATA' (UNIQUE)
> 497139 SORT (AGGREGATE)
> 782814 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PS_POSITION_DATA' (UNIQUE)
> 481 SORT (AGGREGATE)
> 853 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PSAJOB' (NON-UNIQUE)
>
> 743 SORT (AGGREGATE)
> 1097 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'PSAJOB' (NON-UNIQUE)
>

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue May 11 1999 - 08:10:08 CDT