OraFAQ Forum: Performance Tuning » Tuning Buffer Hit Ratio on Oracle 8i

Home » RDBMS Server » Performance Tuning » Tuning Buffer Hit Ratio on Oracle 8i

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tuning Buffer Hit Ratio on Oracle 8i [message #161682]

Mon, 06 March 2006 12:43

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

Report message to a moderator

Re: Tuning Buffer Hit Ratio on Oracle 8i [message #161683 is a reply to message #161682]

Mon, 06 March 2006 12:53

Mahesh Rajendran
Messages: 10708
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

Report message to a moderator

Re: Tuning Buffer Hit Ratio on Oracle 8i [message #161685 is a reply to message #161682]

Mon, 06 March 2006 13:44

Vamshee
Messages: 32
Registered: February 2005

Member

Thanks a ton Mahesh for your immediate reply

Regards
Vamshee

Report message to a moderator

Previous Topic:	log waits
Next Topic:	ORDER BY CLAUSE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 14:41:40 CST 2024