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
    $