Hi..

Im new to the area of DB admin and need some quick help on improving performance. The system ( Oracle 7.3.4) has been showing degradation of performance since 2-3 weeks but now it is becoming a bottleneck. This is specially in the evening when the usage is high. The known issue is poorly tuned sqls with high physical reads ( FTS and index not being used). the buffer cache hit ratio has been in the range of 40-60 % although I have incremented the value twice and it now stands at 100m.
The other ratios / stats ( such as data dict/library cache miss ratio ) are ok. There is not much redo log contention. The following two points may be noted -

There appears to be a latch contention for cache buffer lru.

NAME miss_ratio immediate_miss_ratio
------------------------------ ---------- --------------------
cache buffers lru chain 2.98 .53

The V$Waitstat for data blocks has been showing a fairly high value every day.

CLASS COUNT TIME
------------------ ---------- ----------
data block 2354 2012

The db_block_size is 4096 and total SGA size stands at 222 MB.

some other init ora params are :

db_file_multiblock_read_count=16
db_block_buffers=35000
shared_pool_size=75000000
log_checkpoint_interval=100000


Assuming nothing much can be done on the application tuning is there any way I can improve performance ?

The hardware platform is IBM RS600 ( 1 GB RAM and 4 cpu) , RAID 5 and OS is AIX. The machine is running two separate oracle instances for two different applications and the problem is reported in one of the applications having userbase of around 40.

In case I need to provide more stats I shall be glad to provide.

The way I would attack the following is as follows

1) Map out where all the files (datafiles, redo logs, archived logs etc) used by the both instances are located and identify any which clash. Look at the IO stats on the disks, ideally IO should be spread uniformly. Things to watch out for include both instances having redo logs on the same disks which is bad. Make sure tables and their indexes dont reside on the same disks (if not striped).

2) Identify poorly performing sql by looking at v$sqlarea. For those sqls doing a lot of buffer gets and or disk reads, find their execution plans. If they should be using indexes either create the index or make sure they are analyzed (assuming not using RBO). NOTE some sqls are better off with FTS than using indexes, look at their execution plans and use sql trace for actual stats.
Make sure everything has upto date stats by analyzing.

select trunc(buffer_gets/(executions+1)) "BUFF-EXEC", trunc(buffer_gets/(rows_processed+1)) "BUFF-ROWS",first_load_time, executions, parse_calls, disk_reads, buffer_gets, rows_processed, sql_text, sorts , parsing_user_id
from v$sqlarea
where parsing_user_id!=0 --and upper(sql_text) like '%table_name%'
order by executions desc, buffer_gets desc

3) Is the optimizer choosing good execution plans, if not their are certain parameters which affect the optimizer like multiblock read count.

4)Look for latch contention and waits, especially for hotspots in certain datafiles. Goto www.ixora.com.au for more info.

There is a lot more you can do but these are the basic steps I would take.

Alan