Practice: Using Expressions in Initialization Parameters
Overview
This practice shows how to optimize the values set 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.
Before starting any new practice, refer to the Practices Environment recommendations.
Step 1 : Test
-
Log in to
CDB21
asSYSTEM.
$ sqlplus system Copyright (c) 1982, 2020, Oracle. All rights reserved. Enter password: password Last Successful login time: Mon Mar 16 2020 08:49:41 +00:00 Connected to: SQL>
-
Set the
sga_target
to2G
.SQL> ALTER SYSTEM SET sga_target = 15G; ALTER SYSTEM SET sga_target = 15G * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00823: Specified value of sga_target greater than sga_max_size SQL>
As it fails, set it to 80 % of the SGA_MAX_SIZE.
SQL> ALTER SYSTEM SET sga_target = 'sga_max_size*80/100'; System altered. SQL> SHOW PARAMETER sga NAME TYPE VALUE ------------------------------------ ----------- ---------------------------- allow_group_access_to_sga boolean FALSE lock_sga boolean FALSE pre_page_sga boolean TRUE sga_max_size big integer 13824M sga_min_size big integer 0 sga_target big integer 11072M SQL>
-
Set the
job_queue_processes
to the 10% of the processes value.SQL> ALTER SYSTEM SET job_queue_processes='processes*10/100' SCOPE=BOTH; System altered. SQL> SHOW PARAMETER processes NAME TYPE VALUE ------------------------------------ ----------- ---------------------------- aq_tm_processes integer 1 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 40 log_archive_max_processes integer 4 processes integer 400 SQL>
-
Set the
aq_tm_processes
to the minimum value between 40 and 10% of processes.SQL> ALTER SYSTEM SET AQ_TM_PROCESSES = 'MIN(40, PROCESSES * .1)' SCOPE=BOTH; System altered. SQL> SHOW PARAMETER processes NAME TYPE VALUE ------------------------------------ ----------- ---------------------------- aq_tm_processes integer 40 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 40 log_archive_max_processes integer 4 processes integer 400 SQL>
- What happens if you change the value of
processes
?-
Set the
processes
value to 500 in SPFILE.SQL> ALTER SYSTEM SET PROCESSES = 500 SCOPE=SPFILE; System altered. SQL>
-
Restart the CDB instance.
SQL> CONNECT / AS SYSDBA Connected. SQL> SHUTDOWN IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP ORACLE instance started. Total System Global Area 1140848912 bytes Fixed Size 9566480 bytes Variable Size 352321536 bytes Database Buffers 771751936 bytes Redo Buffers 7208960 bytes Database mounted. Database opened. SQL>
-
Display the values for
processes
andaq_tm_processes
.SQL> SHOW PARAMETER processes NAME TYPE VALUE ------------------------------------ ----------- ---------------------------- aq_tm_processes integer 40 db_writer_processes integer 1 gcs_server_processes integer 0 global_txn_processes integer 1 job_queue_processes integer 50 log_archive_max_processes integer 4 processes integer 500 SQL>
The minimum value between 40 and 10% of
processes
is now 40 (because 10% of 500 is 50). The expression used for setting theaq_tm_processes
parameter is kept throughout the database instance restarts.
-
-
Set the
db_recovery_file_dest
to the same value as$HOME
, inCDB21
.SQL> ALTER SYSTEM SET db_recovery_file_dest='$HOME' SCOPE=BOTH; System altered. SQL> SHOW PARAMETER db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ---------------------------- db_recovery_file_dest string $HOME db_recovery_file_dest_size big integer 250G SQL> ALTER SYSTEM SWITCH LOGFILE; System altered. SQL> ALTER SYSTEM SWITCH LOGFILE; System altered. SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> HOST $ cd $HOME $ ls -ltR | more .: total 20 ./CDB21_FRA1XN: total 4 drwxr-x--- 3 oracle oinstall 4096 Dec 14 10:21 archivelog ./CDB21_FRA1XN/archivelog: total 4 drwxr-x--- 2 oracle oinstall 4096 Dec 14 10:21 2020_12_14 ./CDB21_FRA1XN/archivelog/2020_12_14: total 499832 -rw-r----- 1 oracle oinstall 511804416 Dec 14 10:21 o1_mf_1_4_hxgh534q_.arc -rw-r----- 1 oracle oinstall 11264 Dec 14 10:21 o1_mf_1_6_hxgh575g_.arc -rw-r----- 1 oracle oinstall 2560 Dec 14 10:21 o1_mf_1_5_hxgh54jb_.arc ... $ exit SQL> EXIT $