DB_CACHE_SZE is not getting changed.

[caesardutta]

Dear Sir:

In one node I am trying to increase db_cache_size by issuing the command below.

SQL> alter system set db_cache_size=800M scope=spfile sid='*'
2 /

System altered.

But after bouncing the node I still see that the db_cache_size=304M.

But in the other node it is 800M

I also manually changed the init file by incoroprating the line db_cache_size=800M and then performed a shutdown ==> startup.

After startup I again issued

SQL> alter system set db_cache_size=800M scope=spfile sid='*'
2 /

System altered.

Again bounced the node.

But still the value of db_cache_size is equal to 304M.

Please let me know what I am missing.

warm regards,

Caesar

[Tim...]

Hi.

Things you need to consider...

Create a pfile from the current SPFILE. Look at the contents. Does it contain any other memory parameters, like?

MYSID.db_cache_size=????
*.db_cache_size=????

Make sure if you have multiple settings (SID-specific or general (*) ) you clear them down and leave a single setting.

Cheers

Tim...

[caesardutta]

Great Thanks!!

I will certainly do that and let you know.

Warm regards,

caesar dutta

[Tim...]

[caesardutta]

Dear Sir:

As per your advise I have created a PFILE and giving below few lines pertaining to this topic. I did found repeatation. The "???" are related to node 1 or all.

oiddb2.__db_cache_size=905969664
oiddb1.__db_cache_size=922746880 -->> ???
oiddb2.__java_pool_size=218103808
oiddb1.__java_pool_size=218103808
oiddb2.__large_pool_size=16777216
oiddb1.__large_pool_size=16777216
oiddb2.__shared_pool_size=452984832
oiddb1.__shared_pool_size=436207616
oiddb2.__streams_pool_size=0
oiddb1.__streams_pool_size=0
*.aq_tm_processes=1
*.audit_file_dest='/oracle/ora10g/product/1020/admin/oiddb/adump'
*.audit_trail='TRUE'
*.background_dump_dest='/oracle/ora10g/product/1020/admin/oiddb/bdump'
*.cluster_database_instances=2
*.cluster_database=true
*.compatible='10.2.0.3.0'
*.control_files='+DG_oiddb/oiddb/control01.ctl','+DG_oiddb/oiddb/control02.ctl','+DG_oiddb/oiddb/control03.ctl'#Restore Controlfile
*.core_dump_dest='/oracle/ora10g/product/1020/admin/oiddb/cdump'
*.db_block_size=8192
oiddb1.db_cache_size=318767104 ---???
*.db_cache_size=838860800 ---- ???
*.db_domain='sahaj.co.in'
*.db_file_multiblock_read_count=16
*.db_name='oiddb'
*.db_recovery_file_dest='+DG_oiddb'
*.db_recovery_file_dest_size=53687091200
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oiddbXDB)'



Now I have 2 questions:
1>> What does the __ stands for
2>> If I want to get read of line "oiddb1.db_cache_size=318767104" what steps should I follow? I have to be extra careful
as this is a RAC environment and production system used by Oracle Internet Directory/

Warm regards,

caesar

[Tim...]

Hi.

The presence of the "__" parameters suggests you are using either Automatic Shared Memory Management (ASMM) by setting the SGA_TARGET, or the Automatic Memory Management (AMM) by setting the MEMORY_TARGET parameter. These parameters hold the dynamic value of the relevant setting. You should not mess with them.

In both cases, Oracle controls all the cache and pool sizes. If the regular parameters are set, like DB_CACHE_SIZE, they act as minimum values, preventing the automatic configuration dropping that value. You can set these minimum values, or reset the parameter to blank it.

Code: Select all

alter system reset param-name scope=both sid='mysid1';

http://www.oracle-base.com/articles/10g ... management
http://www.oracle-base.com/articles/11g ... -11gr1.php

Cheers

Tim...

[caesardutta]

Dear Tim:

Thanks for your reply. I also went to the sites reccommended by you (Automatic Shared Memory Management). But I am little confused. Seeing that
<1> Automatic Memory Management is on as SGA_MAX is set to a value other than ZERO and
<2> I also see multiple declarations of db_cache_size in the parameter file.

So what should be my next steps. I understand that since SGA_MAX and SGA_TARGET there is only need of *.db_cache_size = Min_Value and all other
instances of db_cache_size in parameter file should be removed. So how should I proceeed??

Moreover, ASMM mentions that the following should be manually set:

•DB_KEEP_CACHE_SIZE
•DB_RECYCLE_CACHE_SIZE
•DB_nK_CACHE_SIZE (non-default block size)
•STREAMS_POOL_SIZE
•LOG_BUFFER

Now how to calculate their minimum values.

I am still getting Latch Hit Ratio less than 98% even after increasing SGA_MAX and setting db_cache_size = 800 (min value) from 304.

Warm regards,

caesar

[Tim...]

Hi.

I think you are little confused. AMM is controlled by MEMORY_TARGET, not SGA_MAX.

I've explained to you what the purpose of the DB_CACHE_SIZE entries are for. I don't know what else I can say as I keep telling you what to stuff is for and point you to articles that explain it and it doesn't seem to make a difference...

Cheers

Tim...