poor performance of views

[deswal_aja]

the database becomes quite slow during peak hours.
as i see
it's taking quite long for queries on views compared to tables including dd views like v$sqlarea.

in v$filestat iam finding good many reads and writes against
temp tabelspace datafile.

v$waitstat and v$rollstat report no contention.

my phy ram is 512 sga is 336 and shared_pool_size is 120
hit ratio for librarycache and rowcache is above 90

[Queyon]

Check what long query is running on peak hour.

[mbaliga]

Hi,

You have allocated most of your memory to SGA. What about the PGA and the application? Your system must be paging and that may be one of the reasons for poor performance. Definately you need more RAM.

Then you can increase the sort area size and reduce wrires on temporary tablespace. Also look at SQL area pin ratio & free memory in shared pool. You may be able to reduce shared pool size.

Baliga

[deswal_aja]

the queries mostly involve views which we have optimized


the free shared_pool mem is 100M but this is off-peak hour
db_block_buffers are 25000
even during peak i didn't notice paging.


[Edited by deswal_aja on 04-11-2001 at 04:41 PM]