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

Related Topics
Built-in Functions
Built-in Packages
Database Security
Analytic Views
DBMS_AVTUNE_UTIL
DBMS_HCS_LOG
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx