Search

Top 60 Oracle Blogs

Recent comments

AWR Flush Levels

From version 12.1.0.2 onward, for taking AWR snapshots, you have the choice between four AWR flush levels: BESTFIT, LITE, TYPICAL and ALL. If you check the Oracle Database documentation, you won’t find much information about the difference between them. The best you will find, in the PL/SQL Packages and Types Reference, is the following:

The flush level can be one of the following:

  • BESTFIT: Uses the default value depending on the type of snapshot being taken.
  • LITE: Lightweight snapshot. Only the most important statistics are collected. This is default for a pluggable database (PDB) and application container.
  • TYPICAL: Regular snapshot. Most of the statistics are collected. This is default for a container database root (CDB root) and non-CDB database.
  • ALL: Heavyweight snapshot. All the possible statistics are collected. This consumes a considerable amount of disk space and takes a long time to create.

Based on that information, and knowing that the default flush level of the CREATE_SNAPSHOT subprograms of the DBMS_WORKLOAD_REPOSITORY package is BESTFIT, we can infer that BESTFIT simply choose LITE for PDB-level snapshots and TYPICAL for the others.
What I miss is actual information about which piece of data is stored for LITE, TYPICAL and ALL.

Finally, as of 19c, we have a way to know more about that topic without having to trace what the CREATE_SNAPSHOT subprograms do. In fact, we can take advantage of three new data dictionary views (AWR_CDB_TABLE_SETTINGS, AWR_ROOT_TABLE_SETTINGS, AWR_PDB_TABLE_SETTINGS) to know the minimum flush level that a snapshot must have to store data into a specific AWR table. Have a look to the following query and its output:

SELECT flush_level_val, count(*)
FROM awr_cdb_table_settings
GROUP BY flush_level_val

FLUSH_LEVEL_VAL   COUNT(*)
--------------- ----------
LITE                    50
TYPICAL                 71
ALL                      7
NOT APPLICABLE          33

Based on it we can infer that:

  • TYPICAL, compared to LITE, stores data into 71 additional tables.
  • ALL, compared to TYPICAL, stores data into 7 additional tables.

It goes without saying that you can also show the name of the tables. For example, the following are the one associated to each of them (please, do not ask how the database engine makes sense of something like “Datafile Group”):

LITE

SELECT table_name
FROM awr_cdb_table_settings
WHERE flush_level_val = 'LITE'
ORDER BY table_name

TABLE_NAME 
--------------------------
Datafile Group
SQL Group
Service Group
Undo Group
WRH$_ACTIVE_SESSION_HISTORY
WRH$_ASM_BAD_DISK
WRH$_ASM_DISKGROUP
WRH$_ASM_DISKGROUP_STAT
WRH$_ASM_DISK_STAT_SUMMARY
WRH$_BG_EVENT_SUMMARY
WRH$_BUFFER_POOL_STATISTICS
WRH$_CELL_CONFIG
WRH$_CELL_CONFIG_DETAIL
WRH$_CELL_DB
WRH$_CELL_DISK_SUMMARY
WRH$_CELL_GLOBAL
WRH$_CELL_GLOBAL_SUMMARY
WRH$_CELL_IOREASON
WRH$_CELL_OPEN_ALERTS
WRH$_CON_SYSSTAT
WRH$_CON_SYS_TIME_MODEL
WRH$_DB_CACHE_ADVICE
WRH$_DYN_REMASTER_STATS
WRH$_ENQUEUE_STAT
WRH$_EVENT_HISTOGRAM
WRH$_LATCH
WRH$_LATCH_MISSES_SUMMARY
WRH$_MEMORY_RESIZE_OPS
WRH$_MEM_DYNAMIC_COMP
WRH$_MTTR_TARGET_ADVICE
WRH$_MUTEX_SLEEP
WRH$_MVPARAMETER
WRH$_OSSTAT
WRH$_PARAMETER
WRH$_PGASTAT
WRH$_PGA_TARGET_ADVICE
WRH$_PROCESS_MEMORY_SUMMARY
WRH$_SERVICE_NAME
WRH$_SGA
WRH$_SGASTAT
WRH$_SQLSTAT
WRH$_SQLTEXT
WRH$_SQL_WORKAREA_HISTOGRAM
WRH$_STREAMS_POOL_ADVICE
WRH$_SYSSTAT
WRH$_SYSTEM_EVENT
WRH$_SYS_TIME_MODEL
WRH$_THREAD
WRH$_UNDOSTAT
WRH$_WAITSTAT

TYPICAL

SELECT table_name
FROM awr_cdb_table_settings
WHERE flush_level_val = 'TYPICAL'
ORDER BY table_name

TABLE_NAME 
--------------------------
IM Segment Group
Resource Manager Metric Group
Segment Group
WRH$_AWR_TEST_1
WRH$_BUFFERED_QUEUES
WRH$_BUFFERED_SUBSCRIBERS
WRH$_CHANNEL_WAITS
WRH$_CLUSTER_INTERCON
WRH$_COMP_IOSTAT
WRH$_CON_SYSMETRIC_SUMMARY
WRH$_CON_SYSTEM_EVENT
WRH$_CR_BLOCK_SERVER
WRH$_CURRENT_BLOCK_SERVER
WRH$_DATAFILE
WRH$_DISPATCHER
WRH$_DLM_MISC
WRH$_FILEMETRIC_HISTORY
WRH$_IC_CLIENT_STATS
WRH$_IC_DEVICE_STATS
WRH$_IM_SEG_STAT
WRH$_IM_SEG_STAT_OBJ
WRH$_INSTANCE_RECOVERY
WRH$_INST_CACHE_TRANSFER
WRH$_INTERCONNECT_PINGS
WRH$_IOSTAT_DETAIL
WRH$_IOSTAT_FILETYPE
WRH$_IOSTAT_FUNCTION
WRH$_JAVA_POOL_ADVICE
WRH$_LIBRARYCACHE
WRH$_LMS_STATS
WRH$_LOG
WRH$_MEMORY_TARGET_ADVICE
WRH$_OPTIMIZER_ENV
WRH$_PERSISTENT_QMN_CACHE
WRH$_PERSISTENT_QUEUES
WRH$_PERSISTENT_SUBSCRIBERS
WRH$_PROCESS_WAITTIME
WRH$_RECOVERY_PROGRESS
WRH$_REPLICATION_TBL_STATS
WRH$_REPLICATION_TXN_STATS
WRH$_RESOURCE_LIMIT
WRH$_ROWCACHE_SUMMARY
WRH$_RSRC_CONSUMER_GROUP
WRH$_RSRC_METRIC
WRH$_RSRC_PDB_METRIC
WRH$_RSRC_PLAN
WRH$_RULE_SET
WRH$_SEG_STAT
WRH$_SEG_STAT_OBJ
WRH$_SERVICE_STAT
WRH$_SERVICE_WAIT_CLASS
WRH$_SESSMETRIC_HISTORY
WRH$_SESS_NETWORK
WRH$_SESS_SGA_STATS
WRH$_SESS_TIME_STATS
WRH$_SGA_TARGET_ADVICE
WRH$_SHARED_POOL_ADVICE
WRH$_SHARED_SERVER_SUMMARY
WRH$_SQL_BIND_METADATA
WRH$_SQL_PLAN
WRH$_SQL_SUMMARY
WRH$_STREAMS_APPLY_SUM
WRH$_STREAMS_CAPTURE
WRH$_SYSMETRIC_HISTORY
WRH$_SYSMETRIC_SUMMARY
WRH$_TABLESPACE
WRH$_TABLESPACE_SPACE_USAGE
WRH$_TABLESPACE_STAT
WRH$_WAITCLASSMETRIC_HISTORY
WRI$_SQLTEXT_REFCOUNT
WRM$_PDB_INSTANCE

ALL

SELECT table_name
FROM awr_cdb_table_settings
WHERE flush_level_val = 'ALL'
ORDER BY table_name

TABLE_NAME 
--------------------------
Tempfile Group
WRH$_CON_SYSMETRIC_HISTORY
WRH$_FILESTATXS
WRH$_LATCH_CHILDREN
WRH$_LATCH_PARENT
WRH$_TEMPFILE
WRH$_TEMPSTATXS

Based on this information you can guess what information is available with each flush level. For example, since WRH$_SQL_PLAN is included in TYPICAL, you can expect to not have execution plans with LITE.

Another (undocumented) feature available as of 19c is the possibility to change the minimum flush level associated with each AWR table. Specifically, you can use the MODIFY_TABLE_SETTINGS procedure of the DBMS_WORKLOAD_REPOSITORY package to override the default value of a table or, in case you already changed it, to restore the default value. The following PL/SQL blocks illustrate.

  • Include the execution plans in the LITE snapshots:
BEGIN
  DBMS_WORKLOAD_REPOSITORY. MODIFY_TABLE_SETTINGS(
    table_name => 'WRH$_SQL_PLAN',
    flush_level => 'LITE'
  );
END;
  • Restore the default value for the WRH$_SQL_PLAN table:
BEGIN
  DBMS_WORKLOAD_REPOSITORY. MODIFY_TABLE_SETTINGS(
    table_name => 'WRH$_SQL_PLAN',
    flush_level => 'DEFAULT'
  );
END;