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 as SYSTEM.

  
  $ sqlplus system
  
  Copyright (c) 1982, 2020, Oracle.  All rights reserved.
  
  Enter password:
  Last Successful login time: Mon Mar 16 2020 08:49:41 +00:00
  
  Connected to:
  
  SQL> 

• Set SGA_TARGET to 2G.

  
  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 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 JOB_QUEUE_PROCESSES to 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 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 the 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 and aq_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 the aq_tm_processes parameter is kept throughout the database instance restart.

• Set db_recovery_file_dest to the same value as $HOME in CDB21.

  
  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
  $