Re: Performance Related

bshah_at_hotmail.com wrote in article <876751305.21322_at_dejanews.com>...
> Hello!
>
> 1> WHat should be SGA size in developement instance where atleast 20
 users
> are logged in through sqlplus and 20 more sessions dong pro*c compilation
> and another 5-6 FE Developers?
> Currently it is,
> SVRMGR> show sga
> Total System Global Area 13357088 bytes
> Fixed Size 46384 bytes
> Variable Size 12892912 bytes
> Database Buffers 409600 bytes
> Redo Buffers 8192 bytes
> Connect/desc time is of the order of 50-60 seconds. By increasing
> shared_pool_size or processes or any other way can i improve responce!?!?
> Compilation time is in hrs.
>
> 2> WHat is the solution for ora-1631? Would like to know how is it
> actually handled in Production environment?
>
> Please advice
> Cheers
> Biren
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
>

Biren,

Your "Database Buffers" figure is very small. This is the area where data blocks are cached and hence is crucial to the performance of the database. It can be adjusted with the db_block_buffers parameter. This is specified in blocks, not bytes, so to calculate what size it will end up as use db_block_buffers*db_block_size = Database Buffers. If your block size is 4096 bytes then you only have 100 - our server, with 45 users and a 2Gb database, has 19200. I should increase it as far as you can without adversely affecting the performance of anything else that is running on the machine too much.

The "Variable Size", which is controlled by shared_pool_size looks fine. This is where PL and SQL code is cached, so unless you are using a hell of a lot of different SQL statements, 12Mb should be enough.

The "Redo Buffers" figure is again, very small. This would slow down updates and inserts severely. I can't remember the name of the parameter which controls this (is it log_buffers?), but it needs increasing.

Good luck,

David Lord Received on Tue Oct 14 1997 - 00:00:00 CDT