Oracle | DBMS_AUTO_INDEX

Oracle DBMS_AUTO_INDEX_INTERNAL 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 Support package for Auto Indexing

AUTHID DEFINER

Data Types TYPE cols_type IS ....

Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_AUTO_INDEX_INTERNAL UNION SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_AUTO_INDEX_INTERNAL; ORDER BY 1; Query returns 100 objects;

Documented No

First Available 19c

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/prvsaii.plb

Subprograms

AI_CLEANUP	EXECUTE_TASK	IS_AUTO_INDEX_TASK
AI_CLEAR	FINDING_NAME	MERGE_COLS_STR
AI_INIT	GEN_INITIAL_KEY_ID	RECOMMEND_AND_VERIFY
AUTO_INDEX_ALLOW	GET_LAST_ACTIVITY_TIME	REPORT_ACTIVITY
CAPTURE_STS	GET_REPORT_LEVELS	SET_DROPPED_INDEX_STATUS
CHECK_AUTO_INDEX_ENABLED	GET_REPORT_SECTIONS	SET_GLOBAL_AI_TRACE
COMBINE_COL_GROUP	INSERT_AUTO_INDEX_PARAMETERS	TASK_PROC
CONFIGURE

AI_CLEANUP

Not sure but the exception may well relate to the fact that AI_CLEAR was run first and there may have been "NO DATA" to clean up. dbms_auto_index_internal.ai_cleanup(CLEANUP_TYPE IN NUMBER);

exec dbms_auto_index_internal.ai_cleanup(1);

           *

      ORA-01403: no data found

AI_CLEAR

Removes auto indexing jobs from DBA_ADVISOR_LOG and likely elsewhere as well dbms_auto_index_internal.ai_clear;

exec dbms_auto_index_internal.ai_clear;

      

      PL/SQL procedure successfully completed.

AI_INIT

Initializes Automatic Indexing dbms_auto_index_internal.ai_init;

exec dbms_auto_index_internal.ai_init;

      

      PL/SQL procedure successfully completed.

AUTO_INDEX_ALLOW

Undocumented dbms_auto_index_internal.auto_index_allow(opt_env IN RAW) RETURN NUMBER;

TBD

CAPTURE_STS

Undocumented dbms_auto_index_internal.capture_sts( sts_own IN VARCHAR2, sts IN VARCHAR2);

TBD

CHECK_AUTO_INDEX_ENABLED

Presumably will in the future raise an exception when auto indexing is not enable dbms_auto_index_internal.check_auto_index_enabled;

exec dbms_auto_index_internal.check_auto_index_enabled;

           *

      ORA-40216: feature not supported

COMBINE_COL_GROUP

Undocumented dbms_auto_index_internal.combine_col_group(cur IN sys.col_group_usage$) RETURN sys.dbms_auto_index_internal.cols_tab;

TBD

CONFIGURE

Configure an Auto Indexing parameter dbms_auto_index_internal.configure( parameter_name IN VARCHAR2, parameter_value IN VARCHAR2, allow IN BOOLEAN, allow_internal IN BOOLEAN);

TBD

EXECUTE_TASK

Run an auto indexing task but apparently not the way I tried at right dbms_auto_index_internal.execute_task( task_id IN NUMBER, err OUT NUMBER);

SELECT MAX(task_id)

      FROM dba_advisor_log;

      

      MAX(TASK_ID)

      ------------

               101

      

      DECLARE

       outVal NUMBER;

      BEGIN

        dbms_auto_index_internal.execute_task(5, outVal);

        dbms_output.put_line(outVal);

      END;

      /

      *

      ORA-40216: feature not supported

FINDING_NAME

Returns the finding name corresponding to the finding code dbms_auto_index_internal.finding_name(finding_code IN NUMBER) RETURN VARCHAR2;

SELECT dbms_auto_index_internal.finding_name(1);

      

      DBMS_AUTO_INDEX_INTERNAL.FINDING_NAME(1)

      -----------------------------------------

      Compiled statements

GEN_INITIAL_KEY_ID

Undocumented dbms_auto_index_internal.gen_iniital_key_id(cols IN VARCHAR2) RETURN VARCHAR2;

TBD

GET_LAST_ACTIVITY_TIME

Returns the start and end times of the most recent execution of the Index Advisor task dbms_auto_index_internal.get_last_activity_time( start_exec_time OUT TIMESTAMP WITH TIME ZONE, end_exec_time OUT TIMESTAMP WITH TIME ZONE);

SELECT task_id, task_name, execution_start, execution_end

      FROM dba_advisor_log

      WHERE task_name LIKE '%AUTO%INDEX%'

      ORDER BY 1;

      

       TASK_ID TASK_NAME            EXECUTION_START      EXECUTION_END

      -------- -------------------- -------------------- --------------------

             5 SYS_AUTO_INDEX_TASK  11-JAN-2024 19:23:45 11-JAN-2024 19:23:45

      

      

      DECLARE

       stime TIMESTAMP WITH TIME ZONE;

       etime TIMESTAMP WITH TIME ZONE;

      BEGIN

        dbms_auto_index_internal.get_last_activity_time(stime, etime);

        dbms_output.put_line(stime);

        dbms_output.put_line(etime);

      END;

      /

      11-JAN-24 07.23.45.000000 PM -05:00

      11-JAN-24 07.23.45.000000 PM -05:00

      

      PL/SQL procedure successfully completed.

GET_REPORT_LEVELS

Undocumented dbms_auto_index_internal.get_report_levels(report_level IN VARCHAR2) RETURN NUMBER;

SELECT dbms_auto_index_internal.get_report_levels('DETAIL');

      *

      ORA-13618: The specified value is not a valid value for procedure argument LEVEL.

GET_REPORT_SECTIONS

Undocumented dbms_auto_index_internal.get_report_sections(report_section IN VARCHAR2) RETURN NUMBER;

SELECT dbms_auto_index_internal.get_report_sections('HEADER');

      *

      ORA-13618: The specified value is not a valid value for procedure argument SECTION.

INSERT_AUTO_INDEX_PARAMETERS

Undocumented dbms_auto_index_internal.insert_auto_index_parameters;

SQL> exec dbms_auto_index_internal.insert_auto_index_parameters;

      *

      ORA-00001: unique constraint (SYS.I_SMB$CONFIG_PKEY) violated

IS_AUTO_INDEX_TASK

Returns TRUE if the task number corresponds to an auto indexing task dbms_auto_index_internal.is_auto_index_task(task_id IN NUMBER) RETURN BOOLEAN;

SELECT task_id, task_name

      FROM dba_advisor_log

      ORDER BY task_id;

      

       TASK_ID    TASK_NAME

      -------- ------------------------

             1 SYS_AUTO_SQL_TUNING_TASK

             2 SYS_AUTO_SPM_EVOLVE_TASK

             3 SYS_AI_SPM_EVOLVE_TASK

             4 SYS_AI_VERIFY_TASK

             5 SYS_AUTO_INDEX_TASK

             6 AUTO_STATS_ADVISOR_TASK

             7 INDIVIDUAL_STATS_ADVISOR_TASK

      

      BEGIN

        IF dbms_auto_index_internal.is_auto_index_task(4) THEN

          dbms_output.put_line('T');

        ELSE

          dbms_output.put_line('F');

        END IF;

      END;

      /

      F

      

      BEGIN

        IF dbms_auto_index_internal.is_auto_index_task(5) THEN

          dbms_output.put_line('T');

        ELSE

          dbms_output.put_line('F');

        END IF;

      END;

      /

      T

      

      PL/SQL procedure successfully completed.

MERGE_COLS_STR

Returns the numbers, comma delimited, with the smaller integer first dbms_auto_index_internal.merge_cols_str( cols1 IN VARCHAR2, cols2 IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_auto_index_internal.merge_cols_str('1', '2');

      

      DBMS_AUTO_INDEX_INTERNAL.MERGE_COLS_STR('100','42')

      ----------------------------------------------------

      42,100

      

      SELECT dbms_auto_index_internal.merge_cols_str('42', '100');

      

      DBMS_AUTO_INDEX_INTERNAL.MERGE_COLS_STR('42','100')

      ----------------------------------------------------

      42,100

RECOMMEND_AND_VERIFY (new 23ai)

Undocumented dbms_auto_index_internal.recommend_and_verify( workload_start_time IN TIMESTAMP workload_end_time IN TIMESTAMP, auto_index_mode IN VARCHAR2) RETURN VARCHAR2;

TBD

REPORT_ACTIVITY

Report on autoindexing activities dbms_auto_index_internal.report_activity( activity_start IN TIMESTAMP WITH TIME ZONE activity_end IN TIMESTAMP WITH TIME ZONE section_flags IN NUMBER level_flags IN NUMBER report_xml IN OUT XMLTYPE);

TBD

SET_DROPPED_INDEX_STATUS

Drops an auto_index created by the auto index task dbms_auto_index_internal.set_dropped_index_status( owner IN VARCHAR2, index_name IN VARCHAR2, allow_recreate IN BOOLEAN DEFAULT FALSE);

TBD

SET_GLOBAL_AI_TRACE

Undocumented dbms_auto_index_internal.set_global_ai_trace;

exec dbms_auto_index_internal.set_global_ai_trace;

      

      PL/SQL procedure successfully completed.

TASK_PROC

Undocumented and apparently also unfinished dbms_auto_index_internal.task_proc(sts_capture IN BOOLEAN);

exec dbms_auto_index_internal.task_proc(TRUE);

      

      PL/SQL procedure successfully completed.

      

      exec dbms_auto_index_internal.task_proc(FALSE);

      

      
      PL/SQL procedure successfully completed.

Hidden Auto Index Views

The views were specifically named by Oracle with a leading underscore and in lower case to make them difficult to find. Difficult but not impossible. SQL> desc "_auto_index_log" Name Null? Type -------------------- -------- ----------------- TASK_ID NOT NULL NUMBER EXECUTION_NAME VARCHAR2(128) LOG_ID NOT NULL NUMBER OBJECT_ID NUMBER FINDING_CODE NUMBER FINDING_NAME VARCHAR2(80) FLAGS NUMBER VC_ARG1 VARCHAR2(4000) VC_ARG2 VARCHAR2(4000) VC_ARG3 VARCHAR2(4000) N_ARG1 NUMBER N_ARG2 NUMBER SQL> desc "_auto_index_ind_objects" Name Null? Type -------------------- -------- ----------------- TASK_ID NOT NULL NUMBER OBJECT_ID NOT NULL NUMBER INDEX_OWNER VARCHAR2(4000) INDEX_NAME VARCHAR2(4000) TABLE_OWNER VARCHAR2(4000) TABLE_NAME VARCHAR2(4000) COLUMN_LIST CLOB TABLESPACE_NAME VARCHAR2(4000) LAST_EXECUTION_NAME VARCHAR2(4000) TYPE NUMBER PROPERTY NUMBER INDEX_OBJ# NUMBER FLAGS NUMBER REBUILD_COUNT NUMBER MISESTIMATE_COUNT NUMBER