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 (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

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

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx