From version 18.104.22.168 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”):
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
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
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;