How to Tune My Database...

[iyyappan]

Dear Gurus,

How to determine total memory allocated to ORACLE

We need to increase the buffer cache memory in the SGA. I need to know how to determine the total memory allocated to ORACLE.

Here are some of the parameters that I looked at to calculate the total memory allocated to ORACLE.

Shared_pool:= Shared_poorl_size(117440512)
Database buffer Cache:=db_block_buffer(0)*db_block_size(8192)
Redo log buffer:=log_buffer(524288)

SGA:= Shared_pool+Database buffer cache + Redo log buffer


Please let me know if this is the correct way to calculate the total amount of memory allocated to ORACLE. If not please let me know what needs to be done. Our database
size is 2.28GB. We are looking at enhancing the memory structure to improve the performance.

And also My database Hit Ratio:78.1282652. I use following query

SQL> select (1-(sum(decode(name,'physical reads',value,0))/
2 (sum(decode(name, 'db block gets',value,0)) +
3 sum(decode(name,'consistent gets',value,0)))))
4 * 100 "Hit Ratio"
5 from v$sysstat;

Hit Ratio
----------
78.1282652


But Oracle Recommended Hit Ratio above 90%. Now my db_block_buffers value is 0, which value i
use my db_block_buffers?

At the same time i use following query in SYS user

SQL> select state,count(*)
2 from x$bh
3 group by state;

STATE COUNT(*)
---------- ----------
0 326
1 7593
3 37

Total DB_BLOCK_BUFFERS = 7956(326+7593+37)


How to tune my database? Can u please explain,



Thanks in advance.

Regards,
Iyyappan.M

[marist89]

duplicate post, closing. http://www.dbasupport.com/forums/sho...threadid=28673