Oracle | DBMS

Oracle DBMS_AVTUNE Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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	ALL_MVIEWS	DBMS_HIERARCHY
ALL_ANALYTIC_VIEW_BASE_MEAS	ALL_TABLES	DBMS_METADATA
ALL_ANALYTIC_VIEW_DIMENSIONS	ALL_TAB_COLS	DBMS_MVIEW
ALL_ANALYTIC_VIEW_HIERS	AVTUNE_ATU_MV_SEQ	DBMS_OUTPUT
ALL_ANALYTIC_VIEW_LEVELS	AVTUNE_AUTO_TUNE_LOG	DBMS_SCHEDULER
ALL_ANALYTIC_VIEW_LVLGRPS	AVTUNE_AV_CACHES	DBMS_STANDARD
ALL_ATTRIBUTE_DIMENSIONS	AVTUNE_AV_TUNING	DUAL
ALL_ATTRIBUTE_DIM_ATTRS	AVTUNE_DIM_CACHES	PLITBLM
ALL_ATTRIBUTE_DIM_KEYS	AVTUNE_LOG_LISTAGGCLOB	USER_SCHEDULER_JOBS
ALL_ATTRIBUTE_DIM_LEVELS	AVTUNE_QUERY_LOG_ARCHIVE	UTL_FILE
ALL_ATTRIBUTE_DIM_LEVEL_ATTRS	DBMS_ASSERT	V$DIAG_LOG_EXT
ALL_ATTRIBUTE_DIM_TABLES	DBMS_HCS_LIB	V$SQL

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 23c

)

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')); PRAGMA supplemental_log_data(auto_cache_archive, UNSUPPORTED_WITH_COMMIT);

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); PRAGMA supplemental_log_data(auto_cache_create, UNSUPPORTED_WITH_COMMIT);

TBD

AUTO_CACHE_DISABLE (new 23c 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) PRAGMA supplemental_log_data(auto_cache_disable, UNSUPPORTED_WITH_COMMIT);

exec dbms_avtune.auto_cache_disable('UW_AV_CACHE');

AUTO_CACHE_ENABLE (new 23c 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); PRAGMA supplemental_log_data(auto_cache_enable, UNSUPPORTED_WITH_COMMIT);

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

AUTO_CACHE_INITIALIZE (new 23c)

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); PRAGMA supplemental_log_data(auto_cache_disable, UNSUPPORTED_WITH_COMMIT);

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'); PRAGMA supplemental_log_data(auto_cache_refresh, UNSUPPORTED_WITH_COMMIT);

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'); PRAGMA supplemental_log_data(auto_cache_star_enable, UNSUPPORTED_WITH_COMMIT);

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); PRAGMA supplemental_log_data(auto_cache_star_enable, UNSUPPORTED_WITH_COMMIT);

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'); PRAGMA supplemental_log_data(auto_cache_star_refresh, UNSUPPORTED_WITH_COMMIT);

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

AUTO_CACHE_SYNC (new 23c)

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 23c

)

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

TBD

GET_CALLBACK_USER_PARAM (new 23c

)

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

TBD