Tunning Problem

Hi Guys

I am working on Windows NT 4.0 & Oracle 7.3.4 Database.My Database performance is NOT THAT GOOD.

The Physical RAM on server is 780 MB

SGA size is 120000000

db_block_buffers 20000

The Statistics are below.See there The Db buffer hit ratio is very very poor & Also Hit ratio of Row cache is not
that much good.It's a 24*7 Database.Can you give me any Valuable Suggestions and Advices to Improve the Performance of My Database.I increased the db_block_buffers from 18000 To 20000.Even though there is no much Improvement.I ran the utlbstat and utlestat at different time intervals and collected the statistics.


1.SQL> select name,value from v$sysstat where name in('db block gets','consistent gets','physical reads');

NAME VALUE
---------------------------------------------------------------- ----------
db block gets 742893
consistent gets 33363298
physical reads 27658686

1-[Physical reads/(db block gets + consistent gets)] = .81095


2.SQL> select * from v$sgastat where name='free memory';

NAME BYTES
-------------------------- ----------
free memory 21716996


3.SQL> select (sum(pins-reloads))/sum(pins) "Lib Cache" from v$librarycache;

Lib Cache
----------
.99995384


4.SQL> select (sum(gets - getmisses - usage - fixed)) / sum(gets) "Row Cache" from v$rowcache;

Row Cache
----------
.967373848

Thanks in Advance
Ravi

Your buffer size is about 1/3rd of the total SGA size (assuming that the block size is 2k), which I think is less. You have enough RAM on the server, increase db_block_buffer and monitor the hit ratios.

[Edited by Halo on 02-26-2001 at 04:43 PM]

Buffer size=db_block_size*db_block_buffers=40M!!

Hi,
What you have done to your database parameters is good enough but they also need to be relationally organized. From v$sqltext or v$sqlarea search on those queries where hit is 10000 and up. Then check the from clause from the sql statement and check from dba_indexes that weather any index (simple or bitmap) is existing or not if not than you need to create a index according to your search criteria.
Increasing the parameter is necessary but when data is well organized otherwise it will scan the whole table every time for anything and stay same.

Hi All

My Block size is 2k.

Db_block_buffers = 20000

i.e., Buffer size = 2k * 20000 = 40Mb (Which is 1/3 of my SGA)

1/3 of SGA ( 120000000).

Which I have already.SO what should i do now.

Thanks
Ravi

Increase it more. It's 1/3rd of the SGA size and in my opinion this is too small. 1/2 or more of the SGA size is not uncommon.

Hi Halo

I will increase in Steps & will see the Ratio.Can you give me your frequently accessing Mail Id.

Thanks
Ravi

Hi

Only on saturday i can change the db_block_buffers Because i have to stop and start the instance which is only possiblwe once in a week.

Thanks
ravi

Look for offensive sql statements that is trashing your db_block buffers.
Look for statements that are doing full table scans or look for adhoc queries
that your users might be running. you could increase your SGA and db block
buffers and still run into the same problem with the hit ratio.

Thanks