Home » RDBMS Server » Server Administration » change db_cache_size (oracle 11gR2 on 64bit windows 2008 r2)
change db_cache_size [message #608217] Mon, 17 February 2014 11:15 Go to next message
krish96
Messages: 26
Registered: October 2012
Junior Member
Hello Friends,

How to change the db_cache_size on 11g r2 / windows to 2600MB

Here is the current parameters of SGA
SQL> show sga

Total System Global Area 4275781632 bytes
Fixed Size 2262048 bytes
Variable Size 3372223456 bytes
Database Buffers 889192448 bytes
Redo Buffers 12103680 bytes
SQL>

SQL> show parameter max

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
license_max_sessions integer 0
license_max_users integer 0
log_archive_max_processes integer 4
max_dispatchers integer
max_dump_file_size string unlimited
max_enabled_roles integer 150
max_shared_servers integer
memory_max_target big integer 4G
object_cache_max_size_percent integer 10
parallel_max_servers integer 0

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
result_cache_max_result integer 5
result_cache_max_size big integer 12640K
sec_max_failed_login_attempts integer 10
session_max_open_files integer 10
sga_max_size big integer 4G
SQL>


Please guide me with your suggestions ..
thanks in advance.

Krish..
Re: change db_cache_size [message #608218 is a reply to message #608217] Mon, 17 February 2014 11:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just remove the parameter as well as sga_max_size, sga_max_target.

Re: change db_cache_size [message #608219 is a reply to message #608218] Mon, 17 February 2014 11:40 Go to previous messageGo to next message
krish96
Messages: 26
Registered: October 2012
Junior Member
thanks michel, for the quick response..want me to remove these parameters and set the value to sga_max_size=0, sga_max_target=0 in the pfile and start the database..
Re: change db_cache_size [message #608220 is a reply to message #608219] Mon, 17 February 2014 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes.

Re: change db_cache_size [message #608333 is a reply to message #608220] Tue, 18 February 2014 15:24 Go to previous messageGo to next message
krish96
Messages: 26
Registered: October 2012
Junior Member
Hello Michel..

thanks for your response.. after changing the db_cache_size still the show parameter displays same old value .. can you please suggest me..

SQL> alter system set db_cache_size=2600M scope=spfile;

System altered.

SQL> show parameter target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 4G
memory_target big integer 4G
parallel_servers_target integer 0
pga_aggregate_target big integer 819M
sga_target big integer 0
SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 0
SQL>
Re: change db_cache_size [message #608334 is a reply to message #608333] Tue, 18 February 2014 15:31 Go to previous messageGo to next message
BlackSwan
Messages: 22792
Registered: January 2009
Senior Member
>SQL> alter system set db_cache_size=2600M scope=spfile;
you need to restart the DB after the change above
Re: change db_cache_size [message #608335 is a reply to message #608334] Tue, 18 February 2014 15:56 Go to previous messageGo to next message
krish96
Messages: 26
Registered: October 2012
Junior Member
thank you BlackSwan for suggestions and its working..

[Updated on: Tue, 18 February 2014 15:56]

Report message to a moderator

Re: change db_cache_size [message #608355 is a reply to message #608335] Wed, 19 February 2014 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said set it to 0 and set ONLY sga_target and sga_max_size.
Oracle will take care of the size of all SGA components.

Re: change db_cache_size [message #608443 is a reply to message #608355] Wed, 19 February 2014 12:52 Go to previous message
krish96
Messages: 26
Registered: October 2012
Junior Member
Thank you Michel..!
Previous Topic: Huge core file in ORACLE_HOME/dbs
Next Topic: Index problem
Goto Forum:
  


Current Time: Sat Sep 20 15:02:58 CDT 2014

Total time taken to generate the page: 0.09610 seconds