Oracle | DBMS_AVTUNE

Oracle DBMS_AVTUNE_UTIL Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose Utility functions for Analytic View auto cache auto tune.

AUTHID CURRENT_USER

Dependencies

ALL_OBJECTS	DBMS_ASSERT	DICTIONARY_OBJ_OWNER
AVTUNE_AV_CACHES$	DBMS_AVTUNE	DICTIONARY_OBJ_TYPE
AVTUNE_AV_CACHE_LVLS$	DBMS_HCS_LOG	DUAL
AVTUNE_AV_TUNING$	DBMS_STANDARD	SYSEVENT
AVTUNE_DIM_CACHES$	DICTIONARY_OBJ_NAME	PLITBLM
AVTUNE_TEST_PARAMS$

Documented No

First Available 21c

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_ASSERT	AUTO_CLEAR_CLOB	AUTO_LOG_CLEAR_CLOB
AUTO_BUILD_CLOB	AUTO_CREATE_MV	AUTO_LOG_END
AUTO_CACHE_DDL_TRIGGER	AUTO_GET_TEST_PARAM_VALUE	AUTO_LOG_START
AUTO_CACHE_STAR_DDL_TRIGGER	AUTO_LOG	AUTO_SET_TEST_PARAM

AUTO_ASSERT (new 23ai)

Undocumented dbms_avtune_util.auto_assert( p_cond IN BOOLEAN, p_msg IN VARCHAR2);

TBD

AUTO_BUILD_CLOB (new 23ai)

Interface to build a package private clob over numerous functions used for building a CLOB to log in one call dbms_avtune_util.auto_build_clob(line IN VARCHAR2);

TBD

AUTO_CACHE_DDL_TRIGGER

Updates the AVTUNE auto cache system tables

The relationship to a trigger is not obvious dbms_avtune_util.auto_cache_ddl_trigger( p_stmt IN VARCHAR2, p_obj_id IN NUMBER);

conn / as sysdba

      

      GRANT execute ON dbms_avtune_util TO c##uwclass;

      

      conn c##uwclass

      

      CREATE TABLE t (

      testcol VARCHAR2(20));

      

      SELECT object_id

      FROM user_objects

      WHERE object_name = 'T';

      

       OBJECT_ID

      ----------

           77238

      

      DECLARE

       str dbms_id := 'CREATE  TRIGGER x AFTER UPDATE ON t BEGIN NULL; END testtrig;';

      BEGIN

        execute immediate str;

      END;

      /

      

      DROP TRIGGER x;

      

      DECLARE

       str dbms_id := 'CREATE  TRIGGER x AFTER UPDATE ON t BEGIN NULL; END testtrig;';

       obj_id         INTEGER := 77238;

      BEGIN

        sys.dbms_avtune_util.auto_cache_ddl_trigger(str, obj_id);

      END;

      /

      

      PL/SQL procedure successfully completed.

      

      SELECT owner, object_type

      FROM dba_objects

      WHERE object_name = 'X';

      

      no rows selected

      

      -- the trigger was not created so p_stmt is likely not DDL to be executed

      -- apparently p_stmt is a statement that a trigger is supposed to execute

      -- but we are unable to find any dependent triggers: more work will be required

AUTO_CACHE_STAR_DDL_TRIGGER

Updates avtune auto cache system tables

The relationship to a trigger is not obvious dbms_avtune_util.auto_cache_star_ddl_trigger( p_stmt IN VARCHAR2, p_obj_id IN NUMBER);

TBD

AUTO_CLEAR_CLOB (new 23ai)

Clears the private CLOB dbms_avtune_util.auto_clear_clob;

exec dbms_avtune_util.auto_clear_clob;

AUTO_CREATE_MV (new 23ai)

Build the default CREATE MV statement dbms_avtune_util.auto_create_mv(enable_mv IN BOOLEAN);

TBD

AUTO_GET_TEST_PARAM_VALUE (new 23ai)

Returns the test parameter value dbms_avtune_util.auto_get_test_param_value(p_param_name IN VARCHAR2) RETURN VARCHAR2;

TBD

AUTO_LOG (new 23ai)

sets CALL context for subsequent call_ctx_log

Overload 1 dbms_avtune_util.auto_log(callStr IN VARCHAR2);

TBD

writes component and VARCHAR2 text to HCS log

Overload 2 dbms_avtune_util.auto_log( compStr IN VARCHAR2, compTxt IN VARCHAR2);

TBD

AUTO_LOG_CLEAR_CLOB (new 23ai)

Writes the CLOB to the log and reinitializes it dbms_avtune_util.auto_log_clear_clob(p_comp IN VARCHAR2);

TBD

AUTO_LOG_END (new 23ai)

Logs the END call and nulls the current call ctx dbms_avtune_util.auto_log_end;

TBD

AUTO_LOG_START (new 23ai)

sets the CALL context for subsequent call_ctx_log dbms_avtune_util.auto_log_start(callStr IN VARCHAR2);

TBD

AUTO_SET_TEST_PARAM (new 23ai)

Sets a test parameter for avtune and returns the previous value dbms_avtune_util.auto_set_test_param( p_param_name IN VARCHAR2, p_param_value IN VARCHAR2) RETURN VARCHAR2;

TBD