Re: Oracle Server performace help needed

dineshsharma_at_adityabirla.com (Dinesh Sharma) wrote in message news:<9d4671a8.0304162326.1bfb0d94_at_posting.google.com>...
> Last month we upgraded our Alphaserver 4100 server from 512 mb RAM to
> 1Gb RAM.
> Following 1 to 8 parameters were altered as mentioned below:
> 9 to 11 are the observations we have been recording thru script.
>
> Sr Parameter Value Before Value After
> 1 db_block_buffers 94208 163840
> 2 shared_pool_size 104857600 134217728
> 3 shared_pool_reserved_size 5242880 8388608
> 4 log_buffer 20971520 33554432
> 5 large_pool_size 1M 10M
> 6 sort_area_size 393216 271360
> 7 sort_area_retained_size 393216 271360
> 8 db_file_multiblock_read_count 64 32
> 9 Buffer Hit Ratio 83.03 94.66
> 10 Dictionary Hit Ratio 99.04 99.93
> 11 Librarycache Hit Ratio 99.77 93.29
>
> I have observed that response time has increased by two times for
> several Developer based reports whose parsing values are very high.
>

Do you have trace for those reports before and after the upgrade? You could see what operation takes more time. Also, execution plan could change, you can see it in the trace, too. And try to find out why library cache hit ratio is so bad. Did you measure it soon after database started? You need to wait a day or so (depending on workload) to measure correct hit ratios.

> Can anybody tell me is there need for shared pool sizing or other
> parameters like cursor_space_for_time (present value false) and
> session_cached_cursors(present value 0) need to be altered
>
> Dinesh
Received on Thu Apr 17 2003 - 19:01:46 CEST