Home » RDBMS Server » Performance Tuning » db_cache_size - correct size
db_cache_size - correct size [message #258614] Mon, 13 August 2007 03:40 Go to next message
rons
Messages: 18
Registered: June 2007
Location: Scottland
Junior Member
Hi,

Oracle version - 9.2.0.7
OS - HP-UX 64 Risc

Total System Global Area 1034907984 bytes
Fixed Size                   737616 bytes
Variable Size             385875968 bytes
Database Buffers          637534208 bytes
Redo Buffers               10760192 bytes


How would I findout I have set the right db_cache_size.


Ronald.

[Updated on: Mon, 13 August 2007 03:41]

Report message to a moderator

Re: db_cache_size - correct size [message #258616 is a reply to message #258614] Mon, 13 August 2007 03:43 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

show parameter db_cache
Re: db_cache_size - correct size [message #258622 is a reply to message #258614] Mon, 13 August 2007 03:47 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
Total System Global Area 1034907984 bytes
Fixed Size 737616 bytes
Variable Size 385875968 bytes
Database Buffers          637534208 bytes

Redo Buffers 10760192 bytes



Re: db_cache_size - correct size [message #258624 is a reply to message #258614] Mon, 13 August 2007 03:51 Go to previous messageGo to next message
rons
Messages: 18
Registered: June 2007
Location: Scottland
Junior Member
Hi Arju,

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 637534208


I mean,whether dba_cache_size of 608MB is sufficient for the instance or I require stil more. How would I know that.

Ronald.
Re: db_cache_size - correct size [message #258649 is a reply to message #258614] Mon, 13 August 2007 04:30 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

You can have look at,

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14211/memory.htm#sthref540
Re: db_cache_size - correct size [message #258653 is a reply to message #258614] Mon, 13 August 2007 04:42 Go to previous messageGo to next message
rons
Messages: 18
Registered: June 2007
Location: Scottland
Junior Member
Hi,

That was a great help.

Thanks,
Ronald.
Re: db_cache_size - correct size [message #258670 is a reply to message #258614] Mon, 13 August 2007 05:40 Go to previous messageGo to next message
rons
Messages: 18
Registered: June 2007
Location: Scottland
Junior Member
Hi,

SQL> l
  1  select 'V$BUFFER_POOL_STATISTICS' object, PHYSICAL_READS,DB_BLOCK_GETS, CONSISTENT_GETS
  2  from V$BUFFER_POOL_STATISTICS
  3  union all
  4  select 'v$SYSSTAT' object, pr.value ,   bg.value , cg.value
  5  from    v$sysstat pr,   v$sysstat bg,   v$sysstat cg
  6* where    pr.name='physical reads' and   bg.name='db block gets'  and   cg.name ='consistent gets'
SQL> /

OBJECT                   PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS
------------------------ -------------- ------------- ---------------
V$BUFFER_POOL_STATISTICS       37218794      69984093        94587588
v$SYSSTAT                      84233788      69984101        94587642


In the above output, there is a difference in PHYSICAL_READS of V$BUFFER_POOL_STATISTICS and v$SYSSTAT. Can anybody explain me why this is the difference?

Regards,
Ronald.
Re: db_cache_size - correct size [message #258683 is a reply to message #258670] Mon, 13 August 2007 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
v$sysstat.physical_reads also contains direct reads which don't transit through buffer cache.

Regards
Michel
Re: db_cache_size - correct size [message #258689 is a reply to message #258614] Mon, 13 August 2007 06:32 Go to previous messageGo to next message
rons
Messages: 18
Registered: June 2007
Location: Scottland
Junior Member
Michel,

That means, for buffer cache hit ratio we have to use "v$sysstat" ?

Ronald.
Re: db_cache_size - correct size [message #258692 is a reply to message #258689] Mon, 13 August 2007 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This means that you have to use v$buffer_pool_statistics as v$sysstat contains something that is out of buffer pool.

Regards
Michel
Re: db_cache_size - correct size [message #264712 is a reply to message #258614] Tue, 04 September 2007 05:09 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
I think that you can obtain information from v$sgasystat for the first time. According to Oracle guideline, if the "free memory" available, it's no benefit or necessary to ajust Shared Pool Size.

SQL>Select * from V$sgastat
Where name='free memory'
and pool='shared pool';


In the Oracle Database 9i, you can use the dynamic view V$DB_CACHE_ADVICE to collect and display the buffer cache advisory statistics

COLUMN size_for_estimate FORMAT 999,999,999,999 HEADING 'Cache Size (MB)'
COLUMN buffers_for_estimate FORMAT 999,999,999 HEADING 'Buffers'
COLUMN estd_physical_read_factor FORMAT 999.90 HEADING 'Estd Phys|Read Factor'
COLUMN estd_physical_reads FORMAT 999,999,999 HEADING 'Estd Phys| Reads'
SELECT size_for_estimate, buffers_for_estimate,
       estd_physical_read_factor, estd_physical_reads
  FROM v$db_cache_advice
  WHERE name          = 'DEFAULT'
  AND block_size    = (SELECT value FROM v$parameter 
  WHERE name = 'db_block_size')
  AND advice_status = 'ON';


And, with Oracle Database 10g, Automatic Shared Memory Management - ASMM automatically configure the shared memory areas, including the buffer cache.

Use the V$DB_CACHE_ADVICE to get an idea of sizing of buffer cache

SELECT name, block_size, size_for_estimate, size_factor,
       estd_physical_reads
FROM v$db_cache_advice;


@Michael: Would I like to set manually sga in Oracle Database 10g with Windows 2003 Operating System?

Thank you!
Re: db_cache_size - correct size [message #264741 is a reply to message #264712] Tue, 04 September 2007 07:14 Go to previous message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Would I like to set manually sga in Oracle Database 10g with Windows 2003 Operating System?

It's up to you and depends on your environment (workload, Oracle and overall...).
For myself, I just use SGA_TARGET.

Regards
Michel
Previous Topic: shared memory realm already exists
Next Topic: netstat| grep 1522| wc -l
Goto Forum:
  


Current Time: Fri Jun 28 10:48:36 CDT 2024