21c new feature: Using Expressions in Initialization Parameters

 

Using Expressions in Initialization Parameters

In this post, we will run through how to set parameter in initialization parameters when they depend on environmental characteristics, such as system configurations, run-time decisions, or the values of other parameters by using expressions.

Objective: Set SGA_MAX_SIZE=4096M, and set SGX_TARGET to 80% of SGA_MAX_SIZE or 1024M, whichever is larger.

Implement the initial changes

SQL> alter system set sga_max_size='4096*1024*1024' scope=spfile;
System altered.

SQL> alter system set sga_target='max(1024*1024*1024, sga_max_size*0.8)' scope=spfile;
System altered.

Reboot the instance and verify the change

SQL> shutdown immediate
SQL> startup

SQL> show parameter sga_max_size
NAME         TYPE        VALUE
------------ ----------- -----
sga_max_size big integer 4G

SQL> show parameter sga_target
NAME       TYPE        VALUE
---------- ----------- -----
sga_target big integer 3280M

Change SGA_MAX_SIZE only to observe impact on SGA_TARGET

SQL> alter system set sga_max_size=3G scope=spfile;
System SET altered.

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

SQL> startup
ORACLE instance started.

Total System Global Area   3221225384 bytes
Fixed Size                    9691048 bytes
Variable Size              1191182336 bytes
Database Buffers           2013265920 bytes
Redo Buffers                  7086080 bytes
Database mounted.
Database opened.

SQL> show parameter sga_max_size
NAME         TYPE        VALUE
------------ ----------- -----
sga_max_size big integer 3G

SQL> show parameter sga_target
NAME       TYPE        VALUE
---------- ----------- -----
sga_target big integer 2464M

How does it work internally

The formula stored in spfile and processed during instance startup.

SQL> show parameter spfile

NAME	 TYPE   VALUE
-------- ------ -------------------------------------
spfile	 string	/u01/app/oracle/dbs/spfileorcl21c.ora

[oracle@ol8 dbs]$ strings /u01/app/oracle/dbs/spfileorcl21c.ora|egrep  "sga_max_size|sga_target"
orcl21c.__sga_target=2583691264
*.sga_max_size=3221225472
*.sga_target=MAX(((1024 * 1024) * 1024) , (sga_max_size * 0.8))