setting db_cache_size when using automatic sga management

When using automatic sga management (sga_target), you might notice that your shared pool sometimes keeps growing, at the cost of the db_cache_size. I suspect this to be caused by applications that are not using bind variables, but I have not yet researched this further.

When you have such an occasion you can do 4 things :

• Instruct the programmers to fix the program (but you can only really do this when the application is developed in house)
• Leave it as it is (not really an option)
• Discard automatic sga management, and specify the different memory parameter yourself.
• Give a lower limit for the db_cache_size, by setting the db_cache_size without removing the sga_target (oracle would then use the db_cache_size as a lower bound)

I went with option number 4, as the application was not developed by us and I still wanted oracle to be able to move memory from one pool to the other.
For this system I also wanted to increase the sga_target, as several applications had been migrated to this database.

SQL> alter system set sga_target=3000M scope=spfile sid='*';

System altered.

SQL> alter system set db_cache_size=2000M scope=spfile sid='*';
alter system set db_cache_size=2000M scope=spfile sid='*'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache

SQL> column sid format a15
SQL> column name format a40
SQL> column value format a20
SQL> r
  1  select sid, name, value
  2  from v$spparameter
  3* where name = 'db_cache_size'

SID             NAME                                     VALUE
--------------- ---------------------------------------- --------------------
*               db_cache_size

*sight*

It seems like oracle is checking the new db_cache_size value (that you want to put in the spfile, not in the running instance) against the current values in the running instance.
On metalink I found the following bug note about this: bug 4919526 “alter system set db_cache_size=xxxx scope=spfile fails. ora-00384″, only this has been marked as not a bug (yeah, right).

To overcome this issue I specified the db_cache_size parameter in the pfile that pointed to the spfile (which was located in asm) and after that changed the parameter again in the spfile itfself and removed the setting from the pfile.

SQL> !
[oracle@eocpc-rc01:~]$ echo db_cache_size=2000M >> $ORACLE_HOME/dbs/initTRIDION1.ora
[oracle@eocpc-rc01:~]$ cat $ORACLE_HOME/dbs/initTRIDION1.ora
spfile='+DGTRIDION/TRIDIONUK/spfileTRIDION.ora'
db_cache_size=2000M
[oracle@eocpc-rc01:~]$ exit
exit

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 3154116608 bytes
Fixed Size                  2033408 bytes
Variable Size             620763392 bytes
Database Buffers         2516582400 bytes
Redo Buffers               14737408 bytes
Database mounted.
Database opened.

SQL> alter system set db_cache_size=2000M scope=spfile sid='*';

System altered.

SQL> column sid format a15
SQL> column name format a40
SQL> column value format a30
SQL> set linesize 120
SQL> select sid, name, value
from v$spparameter
where name = 'db_cache_size';  2    3

SID             NAME                                     VALUE
--------------- ---------------------------------------- ------------------------------
*               db_cache_size                            2097152000

SQL> !
[oracle@eocpc-rc01:~]$ echo spfile=\'+DGTRIDION/TRIDIONUK/spfileTRIDION.ora\' > $ORACLE_HOME/dbs/initTRIDION1.ora
[oracle@eocpc-rc01:~]$ cat $ORACLE_HOME/dbs/initTRIDION1.ora
spfile='+DGTRIDION/TRIDIONUK/spfileTRIDION.ora'

Of course, recreating the spfile is also an option

---

Possibly related posts: (automatically generated)

• disaster recovery troubles
• Setting Oracle SP File