db_cache_size - correct size [message #258614] |
Mon, 13 August 2007 03:40 |
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 #258624 is a reply to message #258614] |
Mon, 13 August 2007 03:51 |
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 #258670 is a reply to message #258614] |
Mon, 13 August 2007 05:40 |
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 #264712 is a reply to message #258614] |
Tue, 04 September 2007 05:09 |
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 |
|
Michel Cadot
Messages: 68731 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|