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 is now availble. If you haven't you downloaded already: Why?

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