Oracle DBMS

Oracle DBMS_AVTUNE Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

Purpose Analytic View Caching Support

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
General
ATU_IMPLEMENT	VARCHAR2(9)	'IMPLEMENT';

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;

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 No

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

Subprograms

AUTO_CACHE_CREATE	AUTO_CACHE_REFRESH	DBMS_CACHE_STAR_MODIFY
AUTO_CACHE_DISABLE	AUTO_CACHE_REMOVE	AUTO_CACHE_STAR_REFRESH
AUTO_CACHE_ENABLE	AUTO_CACHE_STAR_DISABLE	AUTO_CACHE_TUNE
AUTO_CACHE_MODIFY	AUTO_CACHE_STAR_ENABLE

AUTO_CACHE_CREATE (new 20c)

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

TBD

AUTO_CACHE_DISABLE (new 20c)

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

exec dbms_avtune.auto_cache_disable('UW_AV_CACHE');

AUTO_CACHE_ENABLE (new 20c)

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

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

AUTO_CACHE_MODIFY (new 21c)

Modifies tuning parameters set in the enable call dbms_avtune.auto_cache_modify( av_name IN VARCHAR2, refresh_intvl IN NUMBER, num_queries IN NUMBER, avg_query_time IN NUMBER, total_cache_pct IN NUMBER, av_owner IN VARCHAR2);

TBD

AUTO_CACHE_REFRESH (new 20c)

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 (new 20c)

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 (new 20c)

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 (new 20c)

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 := sys_context('userenv','current_schema'); 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 (new 21c)

Modifies tuning parameters set in the enable call dbms_avtune.auto_cache_star_modify( dim_name IN VARCHAR2, av_name IN VARCHAR2, refresh_intvl IN NUMBER, dim_owner IN VARCHAR2, av_owner IN VARCHAR2);

TBD

AUTO_CACHE_STAR_REFRESH (new 20c)

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_TUNE (new 20c)

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');

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