DB_CACHE_SZE is not getting changed.

All posts relating to Oracle database administration.

Moderator: Tim...

DB_CACHE_SZE is not getting changed.

Postby caesardutta » Fri Jul 13, 2012 8:02 pm

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
caesardutta
Senior Member
 
Posts: 50
Joined: Thu Jul 15, 2010 11:11 am

Re: DB_CACHE_SZE is not getting changed.

Postby Tim... » Sat Jul 14, 2012 12:44 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: DB_CACHE_SZE is not getting changed.

Postby caesardutta » Mon Jul 16, 2012 6:30 am

Great Thanks!!

I will certainly do that and let you know.

Warm regards,

caesar dutta
caesardutta
Senior Member
 
Posts: 50
Joined: Thu Jul 15, 2010 11:11 am

Re: DB_CACHE_SZE is not getting changed.

Postby Tim... » Mon Jul 16, 2012 1:06 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: DB_CACHE_SZE is not getting changed.

Postby caesardutta » Sat Jul 21, 2012 4:24 pm

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
caesardutta
Senior Member
 
Posts: 50
Joined: Thu Jul 15, 2010 11:11 am

Re: DB_CACHE_SZE is not getting changed.

Postby Tim... » Sat Jul 21, 2012 6:47 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: DB_CACHE_SZE is not getting changed.

Postby caesardutta » Sun Jul 22, 2012 4:22 pm

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
caesardutta
Senior Member
 
Posts: 50
Joined: Thu Jul 15, 2010 11:11 am

Re: DB_CACHE_SZE is not getting changed.

Postby Tim... » Sun Jul 22, 2012 4:38 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 10 guests

cron