Home » RDBMS Server » Performance Tuning » restart database after increase the db_cache_size? (oracle 10.2.0.4 , sun solaris 5.10 64bit)
restart database after increase the db_cache_size? [message #564351] Thu, 23 August 2012 00:44 Go to next message
shmily2012
Messages: 2
Registered: August 2012
Junior Member
Hi All,


SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1152M
sga_target big integer 0
SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string

in scenario above, the database do not using ASMM, and spfile
If I wan to increase db_cache_size parameter, do i need to rebounce instance?

Report message to a moderator

Re: restart database after increase the db_cache_size? [message #564354 is a reply to message #564351] Thu, 23 August 2012 01:22 Go to previous messageGo to next message
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

When working with Oracle, a test that takes a few moments can save hours of speculation:
orcl> sho parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 512M
sga_target                           big integer 0
orcl> sho parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
db_cache_size                        big integer 180M
orcl> alter system set db_cache_size=150m;

System altered.

orcl> sho parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
db_cache_size                        big integer 152M
orcl> alter system set db_cache_size=250m;
alter system set db_cache_size=250m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache


orcl> alter system set db_cache_size=200m;

System altered.

orcl> alter system set db_cache_size=250m scope=spfile;

System altered.

orcl>
it depends on various factors, so experiment and then confirm the detail by reading the docs, which explain how the various parameters interact.

Report message to a moderator

Re: restart database after increase the db_cache_size? [message #564355 is a reply to message #564354] Thu, 23 August 2012 01:50 Go to previous messageGo to next message
shmily2012
Messages: 2
Registered: August 2012
Junior Member
yea but this database has been long time never restart. The application owner would not allow me to trial and error.

Report message to a moderator

Re: restart database after increase the db_cache_size? [message #564357 is a reply to message #564355] Thu, 23 August 2012 02:08 Go to previous messageGo to next message
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
Senior Member
So do the tests on a different database. Do you think my demonstration was on a client's production system?

Report message to a moderator

Re: restart database after increase the db_cache_size? [message #564526 is a reply to message #564351] Fri, 24 August 2012 13:31 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
If Oracle cannot decrease the shared_pool or the pga_aggregate_target within 5 to 6 minutes to increase the size of the buffer cache it will error out. If you retry the command it will try again for another 5 to 6 minutes. If it thinks that there is not enough free space to squeeze out of the shared_pool or pga_aggregate_target it will error out immediately. You have to watch that you do not leave too little space for the shared_pool or pga_aggregate_target by giving too much space to the buffer cache or you will get too many disk sorts or reparse of sql's. Usually it is best to increase the sga_target prior to increasing the db_cache_size or db_keep_cache_size or db_recycle_cache_size. I am able to increase memory_target, sga_target and db_keep_cache_size because I ran "alter system set memory_max_target=60g scope=spfile sid='*';" and restarted all instances prior to running the code below.
ENDOCP4P > alter system set memory_target=40g scope=memory sid='NDOCP4';

System altered.

ENDOCP4P > alter system set sga_target=30g scope=memory sid='NDOCP4';

System altered.

ENDOCP4P > alter system set db_keep_cache_size=4g scope=memory sid='NDOCP4';
alter system set db_keep_cache_size=4g scope=memory sid='NDOCP4'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache

ENDOCP4P > @vp

Enter value for substr_of_name: db_keep_cache_size

MEGABYTES INSTANCE CURRENT_MEMORY_VALUE
--------- -------- --------------------------------------------------
     4087 NDOCP4   4285530112
     4096 NDOCP1   4294967296
     4096 NDOCP2   4294967296
     4096 NDOCP3   4294967296


ENDOCP4P > alter system set db_keep_cache_size=4g scope=memory sid='NDOCP4';

System altered.

ENDOCP4P > @vp

Enter value for substr_of_name: db_keep_cache_size

MEGABYTES INSTANCE CURRENT_MEMORY_VALUE
--------- -------- --------------------------------------------------
     4096 NDOCP4   4294967296
     4096 NDOCP1   4294967296
     4096 NDOCP2   4294967296
     4096 NDOCP3   4294967296
Once it takes on all instances, then I make permanent by:
ENDOCP4P > alter system set memory_target=40g scope=spfile sid='*';

System altered.

ENDOCP4P > alter system set sga_target=30g scope=spfile sid='*';

System altered.

ENDOCP4P > alter system set db_keep_cache_size=4g scope=spfile sid='*';

System altered.

Report message to a moderator

Re: restart database after increase the db_cache_size? [message #564527 is a reply to message #564526] Fri, 24 August 2012 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
There is no memory_target in 10.2.

Regards
Michel

Report message to a moderator

Re: restart database after increase the db_cache_size? [message #564528 is a reply to message #564527] Fri, 24 August 2012 14:11 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
In 10g I increase the sga_max_size and restart all instances prior to the following:
-- 10g Parameters before changes
INSTANC VALUE                                    NAME
------- ---------------------------------------- ---------------------
NDOCP1  15367929856                              sga_max_size
NDOCP1  12297699328                              sga_target
NDOCP1  2063597568                               db_keep_cache_size
NDOCP1  2063597568                               db_recycle_cache_size

ENDOCP1 > @esys NDOCP1
Connected.
SYS AS SYSDBA> CREATE PFILE = '$ORACLE_HOME/dbs/init_2G_caches_pfile.ora' FROM SPFILE;

File created.

SYS AS SYSDBA> @esys NDOCP2
Connected.
SYS AS SYSDBA> CREATE PFILE = '$ORACLE_HOME/dbs/init_2G_caches_pfile.ora' FROM SPFILE;

File created.

SYS AS SYSDBA> @E NDOCP2
Connected.
ENDOCP2 > alter system set sga_target=14g scope=memory sid='NDOCP2';

System altered.

ENDOCP2 > alter system set db_recycle_cache_size=3g scope=memory sid='NDOCP2';

System altered.

ENDOCP2 > alter system set db_keep_cache_size=3g scope=memory sid='NDOCP2';

System altered.

ENDOCP2 > @E NDOCP1
Connected.
ENDOCP1 > alter system set sga_target=14g scope=memory sid='NDOCP1';

System altered.

ENDOCP1 > alter system set db_keep_cache_size=3g scope=memory sid='NDOCP1';

System altered.

ENDOCP1 > alter system set db_recycle_cache_size=3g scope=memory sid='NDOCP1';

System altered.

ENDOCP1 > alter system set sga_target=14g scope=spfile sid='*';

System altered.

ENDOCP1 > alter system set db_keep_cache_size=3g scope=spfile sid='*';

System altered.

ENDOCP1 > alter system set db_recycle_cache_size=3g scope=spfile sid='*';

System altered.

ENDOCP1 > @esys NDOCP1
Connected.
SYS AS SYSDBA> CREATE PFILE = '$ORACLE_HOME/dbs/init_3G_caches_pfile.ora' FROM SPFILE;

File created.

SYS AS SYSDBA> @esys NDOCP2
Connected.
SYS AS SYSDBA> CREATE PFILE = '$ORACLE_HOME/dbs/init_3G_caches_pfile.ora' FROM SPFILE;

File created.

SYS AS SYSDBA> @vp
Enter value for substr_of_name: sga
old   3: where upper(name) like upper('%&substr_of_name%')
new   3: where upper(name) like upper('%sga%')

INSTANC VALUE                                    NAME
------- ---------------------------------------- -------------------------------------
NDOCP2  15032385536                              sga_max_size
NDOCP2  FALSE                                    pre_page_sga
NDOCP2  FALSE                                    lock_sga
NDOCP2  15032385536                              sga_target

Report message to a moderator

Previous Topic: Frequent changed table stats problem
Next Topic: index behaviour
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jan 10 18:59:27 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.