Home » RDBMS Server » Performance Tuning » Tuning Buffer Hit Ratio on Oracle 8i
Tuning Buffer Hit Ratio on Oracle 8i [message #161682] Mon, 06 March 2006 12:43 Go to next message
Vamshee
Messages: 32
Registered: February 2005
Member
Hi

We have Oracle 8i here on NT server.

On Checking the buffer Hit ratio on our current environment,its giving a value of 56%,
Where as i heard that this value should be around 90%.

Herez the query that iam using for this:

SELECT ROUND((1-(phy.value / (cur.value +
2 con.value)))*100,2) "Cache Hit Ratio"
3 FROM v$sysstat cur, v$sysstat con, v$sysstat phy
4 WHERE cur.name = 'db block gets'
5 AND con.name = 'consistent gets'
6 AND phy.name = 'physical reads';


Could you please suggest me a few ways of Performace Tuning to enhance this value.

Question 2:
_______________

Also, for instance if a table has a set of indexes, how to know as which particular columns of the table have the index and which ones do not.

Thanks in Advance,
Vamshee
Re: Tuning Buffer Hit Ratio on Oracle 8i [message #161683 is a reply to message #161682] Mon, 06 March 2006 12:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>On Checking the buffer Hit ratio on our current environment,its giving a value of 56%,
>> Where as i heard that this value should be around 90%.
Buffer hit ratio cannot be considered as a measure of tuning.
Please see here.
http://www.orafaq.com/forum/t/23453/0/

For second question, look into user_ind_columns
scott@9i > select index_name,column_name from user_ind_columns;

INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
SYS_C001891                    DEPTNO
SYS_C001892                    EMPNO
MYINDEX                        ENAME
MYINDEX                        SAL
SYS_C001887                    PK
Re: Tuning Buffer Hit Ratio on Oracle 8i [message #161685 is a reply to message #161682] Mon, 06 March 2006 13:44 Go to previous message
Vamshee
Messages: 32
Registered: February 2005
Member
Thanks a ton Mahesh for your immediate reply

Regards
Vamshee
Previous Topic: log waits
Next Topic: ORDER BY CLAUSE
Goto Forum:
  


Current Time: Fri Mar 29 08:27:31 CDT 2024