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 will be available on generic Linux platforms in January and soon on AIX and Windows

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