Hi all,

I am having a database ,which can only be shutdown every month end.
I just recently noticed that the cache hit ratio is just 64.5%

To increase the db_block_buffer i need to restart the database,which i cannot until this month end.

Is there anything else i can do to increase the ratio

mickykt

Look out for poorly performing sqls, most probably sqls which are doing full table scans on large tables, which will bring down your cache hit ratio. This is especially true if they only require a small percentage of the total rows from a large table i.e. they would benifit from using an index instead of the full table scan.
To identify these sql look at v$sqlarea and find those sqls which are hitting your large tables. Then identify the worst performers by buffer_gets and then get their execution plans. Identify those which should use an index but are not, and then either create an index (if their isnt one already) or modify the app to force the sql to use the index (through hints) if possible. One thing to note is that indexes wont be used if they havent been analyzed so make sure everything is analyzed.


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 '%LARGE_TABLE_NAME%'
order by first_load_time desc, executions desc

Theres a lot of things you can do to improve the performace.

Check out the most frequently used sqls.., aviod full table scans....
pins objects if required,.....

Also check out the dynamic changing of parameters that ORacle 8i allows.
I know Oracle 9i allows you to dynamically change the size of SGA on the fly.

This gets interesting.

The current thread seem to be a very interesting posting for locating bad performing SQLs.

Could you let me know the way to locate the bad performing SQLs, as it would really help me to get information on what is slowing down our systems which has grown quite a bit.

I wanted to know if that query that you mentioned in the first posting, also tracks of the SQLs performed from within the application, based on JSP.

These are 3 sqls which I commonly use to identify poor sqls. Note however that because they query v$sqlarea it wont show you every sql as it is like cache of sqls.

Once you identify the worst sqls get their execution plans and see if they should be using an index instead of a full table scan. Or maybe the index hasnt been analyzed or its columns are in the wrong order. Then execute the sql and use sql trace to look at what it actually does as opposed to what the execution plan guesstimates. Note for sqls which execute many times (i.e. thousands of executions per day) then even a small improvement in performance can make a big difference. With Oracle unlike some other databases there are a multitude of options (i.e. bitmap indexes, compressed indexes, clusters, IOT etc) which means it may take a while to find your solution but you should find it eventually : ).

I've commented out the bit in each sql which selects only those sqls which contain a certain word (i.e. could be a table name for large table or just show inserts or updates etc).

The first two columns are:
1) number of buffer gets per execution
2) ratio of buffer_gets to rows processed


--worst sqls by executions
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 'INSERT INTO%DATA%'
order by executions desc, buffer_gets desc

-- worst sqls by buffer_gets
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 'UPDATE%FINANCIAL%'
order by buffer_gets desc

-- most recent sqls
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 '%VIRTUAL%'
order by first_load_time desc, executions desc

Alan