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 is now availble. If you haven't you downloaded already: Why?

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