Oracle | DBMS

Oracle DBMS_AVTUNE Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose Analytic View Caching Support

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
General
ATU_IMPLEMENT	VARCHAR2(9)	'IMPLEMENT'
ATU_RECOMMEND	VARCHAR2(9)	'RECOMMEND'
General
CACHE_MODE_FLOOR	VARCHAR2(5)	'FLOOR'
CACHE_MODE_QUERY_MATCH	VARCHAR2(11)	'QUERY_MATCH'
General
CALLBACK_PARAM_MV_SQL	VARCHAR2(6)	'MV_SQL'
CALLBACK_PARAM_MV_OWNER	VARCHAR2(8)	'MV_OWNER'
CALLBACK_PARAM_MV_NAME	VARCHAR2(7)	'MV_NAME'
CALLBACK_PARAM_AV_OWNER	VARCHAR2(8)	'AV_OWNER'
CALLBACK_PARAM_AV_NAME	VARCHAR2(7)	'AV_NAME'
CALLBACK_PARAM_CALLBACK_TYPE	VARCHAR2(13)	'CALLBACK_TYPE'
CALLBACK_PARAM_CACHE_TYPE	VARCHAR2(10)	'CACHE_TYPE'

Data Types TYPE level IS RECORD ( dim_name dbms_id, hier_name dbms_id, lvl_name dbms_id); TYPE level_list IS TABLE OF level; TYPE callback IS RECORD ( owner_name dbms_id, pkg_name dbms_id, procedure_name dbms_is, is_not_null BOOLEAN := TRUE); TYPE clob_sequence IS VARRAY(32767) OF CLOB;

Dependencies

ALL_ANALYTIC_VIEWS	AVTUNE_DIM_CACHES$
ALL_ANALYTIC_VIEW_BASE_MEAS	AVTUNE_LOCKS$
ALL_ANALYTIC_VIEW_DIMENSIONS	AVTUNE_LOG_LISTAGGCLOB$
ALL_ANALYTIC_VIEW_DIMS	AVTUNE_LOG_LISTAGG_PARAM_TYPE
ALL_ANALYTIC_VIEW_HIERS	AVTUNE_QUERY_LOG_ARCHIVE$
ALL_ANALYTIC_VIEW_LEVELS	AVTUNE_QUERY_LOG_ARCHIVE_CACHE_LVLS$
ALL_ANALYTIC_VIEW_LVLGRPS	AVTUNE_QUERY_LOG_ARCHIVE_LVLS$
ALL ARGUMENTS	AVTUNE_QUERY_LOG_ARCHIVE_MEAS$
ALL_ATTRIBUTE_DIMENSIONS	DBMS_ASSERT
ALL_ATTRIBUTE_DIM_ATTRS	DBMS_AVTUNE_UTIL
ALL_ATTRIBUTE_DIM_KEYS	DBMS_HCS_LIB
ALL_ATTRIBUTE_DIM_LEVELS	DBMS_HIERARCHY
ALL_ATTRIBUTE_DIM_LEVEL_ATTRS	DBMS_LOB
ALL_ATTRIBUTE_DIM_TABLES	DBMS_LOCK
ALL_DEPENDENCIES	DBMS_METADATA
ALL_MVIEWS	DBMS_SCHEDULER
ALL_OBJECTS	DBMS_SESSION
ALL_PROCEDURES	DBMS_SNAPSHOT
ALL_SCHEDULER_JOBS	DBMS_STANDARD
ALL_SYNONYMS	DBMS_UTILITY
ALL_TABLES	DUAL
ALL_TAB_COLS	PLITBLM
AVTUNE_AV_ARCHIVE_INST$	SYSEVENT
AVTUNE_AV_CACHES$	V_$DIAG_LOG_EXT
AVTUNE_AV_CACHE_LVLS$	V_$PARAMETER
AVTUNE_AV_TUNING$	V_$SQL
AVTUNE_AV_TUNING_CBK_ARGS$	V_$SQLSTATS

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-18521	analytic view auto caching is not enabled.

First Available 20c

Role SELECT 'sys_priv', privilege FROM dba_sys_privs WHERE grantee = 'AVTUNE_PKG_ROLE' UNION SELECT 'obj_priv', table_name OBJECT FROM dba_tab_privs WHERE grantee = 'AVTUNE_PKG_ROLE' ORDER BY 1,2; SYS_PRI PRIVILEGE -------- -------------------------- obj_priv AVTUNE_AV_CACHES$ obj_priv AVTUNE_AV_TUNING$ obj_priv AVTUNE_DIM_CACHES$ obj_priv AVTUNE_LOG_LISTAGGCLOB$ obj_priv AVTUNE_QUERY_LOG_ARCHIVE$ obj_priv DBMS_HCS_LOG obj_priv V_$DIAG_LOG_EXT obj_priv V_$SQL sys_priv ALTER SESSION sys_priv CREATE JOB sys_priv CREATE MATERIALIZED VIEW sys_priv CREATE TABLE sys_priv CREATE TRIGGER

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsavtune.sql
{ORACLE_HOME}/rdbms/admin/prvtavtune.plb

Subprograms

AUTO_CACHE_ARCHIVE	AUTO_CACHE_REFRESH	AUTO_CACHE_STAR_REFRESH
AUTO_CACHE_CREATE	AUTO_CACHE_REMOVE	AUTO_CACHE_SYNC
AUTO_CACHE_DISABLE	AUTO_CACHE_STAR_DISABLE	AUTO_CACHE_TUNE
AUTO_CACHE_ENABLE	AUTO_CACHE_STAR_ENABLE	GET_CALLBACK_PARAM
AUTO_CACHE_INITIALIZE	AUTO_CACHE_STAR_MODIFY	GET_CALLBACK_USER_PARAM
AUTO_CACHE_MODIFY

AUTO_CACHE_ARCHIVE (new 23ai

)

Public interface for copying log data to avtune_query_log_archive$ dbms_avtune.auto_cache_archive( av_name IN dbms_id, av_owner IN dbms_id := sys_context('userenv','current_schema'));

TBD

AUTO_CACHE_CREATE

Create an aggregation cache with the specified level dbms_avtune.auto_cache_create( av_name IN dbms_id, level_group IN sys.dbms_avtune.level_list, av_owner IN dbms_id := sys_context('userenv','current_schema'), force_size IN BOOLEAN := FALSE);

TBD

AUTO_CACHE_DISABLE (new 23ai parameters)

Disable auto cache and tuning, clean out tables etc. dbms_avtune.auto_cache_disable( av_name IN dbms_id, av_owner IN dbms_id := sys_context('userenv','current_schema' force IN BOOLEAN := FALSE);

exec dbms_avtune.auto_cache_disable('UW_AV_CACHE');

AUTO_CACHE_ENABLE (new 23ai parameters)

ALTERS an AV to enable auto cache and tuning dbms_avtune.auto_cache_enable( av_name IN dbms_id, refresh_intvl IN INT := NULL, num_queries IN INT := NULL, avg_query_time IN NUMBER := NULL, total_cache_pct IN INT := NULL, init_max_pct IN INT := NULL, init_numhier IN INT := NULL, init_fixed_lvls IN level_list := NULL, run_mode IN VARCHAR2 := atu_implement, av_owner IN dbms_id := sys_context('userenv','current_schema'); tuning_intvl IN INT := NULL, purge_archive_intvl IN INTERVAL DAY TO SECOND := NULL, single_cache_pct IN INT := NULL, cache_mode IN VARCHAR2 := NULL, archive_intvl IN INT := NULL, num_tunes IN INT := NULL, cache_create_callback IN callback := NULL, create_cbk_args IN clob_sequenc := NULL, cache_refresh_callback IN callback := NULL, refresh_cbk_args IN clob_sequence := NULL, init_star_caches IN BOOLEAN := TRUE);

exec dbms_avtune.auto_cache_disable('UW_AV_CACHE', av_owner=>'UWCLASS');

AUTO_CACHE_INITIALIZE (new 23ai)

Creates a new cache (initial or star cache) dbms_avtune.auto_cache_initialize av_name IN dbms_id, av_owner IN dbms_id := sys_context('userenv','current_schema'), init_max_pct IN INT := NULL, init_numhier IN INT := NULL, init_fixed_lvls IN level_list := NULL, init_star_caches IN BOOLEAN := TRUE);

TBD

AUTO_CACHE_MODIFY

Modifies stored auto cache and tuning parameters set in the enable call dbms_avtune.auto_cache_modify( av_name IN VARCHAR2, refresh_intvl IN NUMBER := NULL, num_queries IN NUMBER := NULL, avg_query_time IN NUMBER := NULL, total_cache_pct IN NUMBER := NULL, av_owner IN VARCHAR2 := sys_context('userenv','current_schema'), tuning_intvl IN INT := NULL, purge_archive_intvl IN INTERVAL DAY TO SECOND := NULL, single_cache_pct IN INT := NULL, cache_mode IN VARCHAR2 := NULL, archive_intvl IN INT := NULL, num_tunes IN INT := NULL, cache_create_callback IN callback := NULL, create_cbk_args IN clob_sequence := NULL, cache_refresh_callback IN callback := NULL, refresh_cbk_args IN clob_sequence := NULL);

TBD

AUTO_CACHE_REFRESH

Refreshes all AV aggregation and star caches dbms_avtune.auto_cache_refresh( av_name IN dbms_id, av_owner IN dbms_id := sys_context('userenv','current_schema');

exec dbms_avtune.auto_cache_refresh('UW_AV_CACHE');

AUTO_CACHE_REMOVE

Removes the named AV cache dbms_avtune.auto_cache_remove( av_name IN dbms_id, level_group IN sys.dbms_avtune.level_list, av_owner IN dbms_id := sys_context('userenv','current_schema');

TBD

AUTO_CACHE_STAR_DISABLE

Disable auto star cache and tuning dbms_avtune.auto_cache_star_disable( dim_name IN dbms_id, dim_owner IN dbms_id := sys_context('userenv','current_schema');

exec dbms_avtune.auto_cache_star_disable('UWDIM', 'UW_AV_NAME');

AUTO_CACHE_STAR_ENABLE

Enable auto star cache and tuning dbms_avtune.auto_cache_star_enable( dim_name IN dbms_id, av_name IN dbms_id := NULL, refresh_intvl IN INT := NULL, run_mode IN VARCHAR2 := atu_implement, dim_owner IN dbms_id := sys_context('userenv','current_schema'), av_owner IN dbms_id := NULL);

exec dbms_avtune.auto_cache_star_enable('UWDIM', 'UW_AV_NAME');

AUTO_CACHE_STAR_MODIFY

Modifies tuning parameters set in the enable call dbms_avtune.auto_cache_star_modify( dim_name IN dbms_id, av_name IN dbms_id := NULL, refresh_intvl IN INT := NULL, dim_owner IN VARCHAR2, av_owner IN VARCHAR2);

TBD

AUTO_CACHE_STAR_REFRESH

Refreshes the dimension's star cache dbms_avtune.auto_cache_star_refresh( dim_name IN dbms_id, dim_owner IN dbms_id := sys_context('userenv','current_schema');

exec dbms_avtune.auto_cache_star_refresh('UWDIM'. 'UWCLASS');

AUTO_CACHE_SYNC (new 23ai)

Keeps caches in sync with ddl changes dbms_avtune.auto_cache_sync( p_av_name dbms_id, p_av_owner dbms_id := SYS_CONTEXT('userenv','current_schema'));

TBD

AUTO_CACHE_TUNE

Updates the query log archive and looks for/create cache tuples dbms_avtune.auto_cache_tune( av_name IN dbms_id, num_queries IN INT := NULL, avg_query_time IN NUMBER := NULL, run_mode IN VARCHAR2 := atu_implement, av_owner IN dbms_id := sys_context('userenv','current_schema'), num_tunes IN INT := NULL, update_archive IN BOOLEAN := FALSE);

exec dbms_avtune.auto_cache_tune('UW_AV_CACHE', 10000, av_owner=>'UWCLASS');

GET_CALLBACK_PARAM (new 23ai

)

Returns the value of the parameter requested dbms_avtune.get_callback_param(param_name IN VARCHAR2) RETURN CLOB;

TBD

GET_CALLBACK_USER_PARAM (new 23ai

)

Return the value of the user parameter requested dbms_avtune.get_callback_user_param(p_param_num INT) RETURN CLOB;

TBD