Search

Top 60 Oracle Blogs

Recent comments

AWR: Multitenant-Specific Initialization Parameters

By default, the database engine automatically takes snapshots in the root container only. Such snapshots cover the root container as well as all open PDBs belonging to it. From version 12.2 onward, you can control whether the database engine automatically takes also PDB-level snapshots through the dynamic initialization parameter AWR_PDB_AUTOFLUSH_ENABLED. In case you want to enable that feature, you have to carry out two operations:

  • Set the initialization parameter AWR_PDB_AUTOFLUSH_ENABLED to TRUE (the default value is FALSE) either in a specific PDB or, if you want to enable it for all PDBs, in the root container.
  • Set the snapshot interval at the PDB level.

Note that to enable automatic PDB-level snapshots it’s necessary to set the snapshot interval because the PDB-level default is 40,150 days! Hence, if you don’t change it, the database engine will never take them.

From version 18c onward, with the dynamic initialization parameter AWR_PDB_MAX_PARALLEL_SLAVES, you can specify the maximum number of background processes that the database engine can concurrently use to take automatic PDB-level snapshots. Valid values go from 1 to 30; the default is 10. Even though this initialization parameter doesn’t affect the automatic snapshots in the root container, it can only be set in the root container. The following examples illustrate the behaviour with three PDBs and an interval of 15 minutes:

  • AWR_PDB_MAX_PARALLEL_SLAVES = 1: only one PDB-level snapshot is taken at the same time as the snapshot in the root container
SQL> SELECT con_id, to_char(end_interval_time, 'HH24:MI:SS') AS snap_time
  2  FROM cdb_hist_snapshot
  3  WHERE end_interval_time > to_timestamp('2020-09-02 11:00','YYYY-MM-DD HH24:MI')
  4  ORDER BY snap_time, con_id;

CON_ID SNAP_TIME
------ ---------
     0 11:00:45
     4 11:00:45
     6 11:01:45
     3 11:02:46
     0 11:15:49
     3 11:15:49
     4 11:16:49
     6 11:17:50
     0 11:30:53
     6 11:30:53
     3 11:31:53
     4 11:32:53
  • AWR_PDB_MAX_PARALLEL_SLAVES = 10: all PDB-level snapshots are taken at the same time as the snapshot in the root container
SQL> SELECT con_id, to_char(end_interval_time, 'HH24:MI:SS') AS snap_time
  2  FROM cdb_hist_snapshot
  3  WHERE end_interval_time > to_timestamp('2020-09-02 11:45','YYYY-MM-DD HH24:MI')
  4  ORDER BY snap_time, con_id;

CON_ID SNAP_TIME
------ ---------
     0 11:45:57
     3 11:45:57
     4 11:45:57
     6 11:45:57
     0 12:00:01
     3 12:00:01
     4 12:00:01
     6 12:00:01
     0 12:15:05
     3 12:15:05
     4 12:15:05
     6 12:15:05

As of version 18c, with the dynamic initialization parameter AWR_SNAPSHOT_TIME_OFFSET, you can also configure when the database engine takes automatic snapshots. With the default value (0) the snapshots are taken, with the default interval of 1 hour, at the top of the hour (i.e. 01:00, 02:00, 03:00, etc.). For database servers with many database instances, it can be an issue that all of them take the automatic snapshots at the same time. Values greater than 0 can help avoiding such an issue. In fact, they specify, in seconds, how much delay you want to add. For example, with the value 120 and the default interval of 1 hour, the database engine takes automatic snapshots at 01:02, 02:02, 03:02, etc. There’s also a special value: 1000000. With it you can specify that the database engine has to automatically choose a sensible delay. This initialization parameter can only be set in the root container. The following example illustrate the behaviour with three PDBs, an offset of 120 seconds, a maximum of 2 background processes, and an interval of 15 minutes:

SQL> SELECT con_id, to_char(end_interval_time, 'HH24:MI:SS') AS snap_time
  2  FROM cdb_hist_snapshot
  3  WHERE end_interval_time > to_timestamp('2020-09-02 12:30','YYYY-MM-DD HH24:MI')
  4  ORDER BY snap_time, con_id;

CON_ID SNAP_TIM
------ --------
     0 12:32:09
     3 12:32:09
     4 12:32:09
     6 12:33:09
     0 12:47:13
     3 12:47:13
     6 12:47:13
     4 12:48:13
     0 13:02:17
     4 13:02:17
     6 13:02:17
     3 13:03:17