Re: how to explain this result

The bigger you set your "db_block_buffer" parameter the more rows of the table will be kept in the memory, the less disk reads will be made. It's called table caching. Have you ever noticed how much faster some queries run the second time? That's because the table is cached in the memory when you run your query the second time, the first time Oracle had to go to the disk to read the table blocks.

Vitaliy Mogilevskiy
Get DBA Script Library GUI Version @ http://home.earthlink.net/~vit100gain/index.html Free.

Limin Guo 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?
>
> 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)
Received on Tue May 11 1999 - 13:57:28 CDT