Oracle DBMS_AUTO_INDEX_INTERNAL
Version 21c

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 Support package for Auto Indexing
AUTHID DEFINER
Data Types TYPE cols_type IS ....
Dependencies
COL$ DBMS_LOB SQL$TEXT
COL_GROUP_USAGE$ DBMS_OUTPUT SQLOBJ$
COL_USAGE$ DBMS_REPAIR SQLOBJ$AUXDATA
DBA_ADVISOR_EXECUTIONS DBMS_SMB_INTERNAL SQLSET_ROW
DBA_ADVISOR_FINDINGS DBMS_SPD_INTERNAL SYS_DBA_SEGS
DBA_ADVISOR_SQLPLANS DBMS_SPM TAB$
DBA_ADVISOR_SQLSTATS DBMS_SPM_INTERNAL USER$
DBA_AUTO_INDEX_EXECUTIONS DBMS_SQLDIAG WRH$_SQLTEXT
DBA_AUTO_INDEX_IND_ACTIONS DBMS_SQLPA WRI$_ADV_ACTIONS
DBA_AUTO_INDEX_STATISTICS DBMS_SQLTUNE WRI$_ADV_AI_COL_USAGE
DBA_AUTO_INDEX_VERIFICATIONS DBMS_SQLTUNE_INTERNAL WRI$_ADV_AI_EXP_USAGE
DBA_INDEXES DBMS_SQLTUNE_UTIL0 WRI$_ADV_EXECUTIONS
DBA_IND_PARTITIONS DBMS_STANDARD WRI$_ADV_FINDINGS
DBA_IND_SUBPARTITIONS DBMS_STATS WRI$_ADV_MESSAGE_GROUPS
DBA_OBJECTS DBMS_STATS_INTERNAL WRI$_ADV_OBJECTS
DBA_PART_KEY_COLUMNS DBMS_STATS_LIB WRI$_ADV_TASKS
DBA_SEGMENTS DBMS_SYS_ERROR WRI$_ADV_SQLACCESS_ADV
DBA_SQLSET DBMS_UTILITY WRI$_INDEX_USAGE
DBA_SQLSET_REFERENCES DUAL WRI$_OPTSTAT_TAB_HISTORY
DBA_SQLSET_STATEMENTS ICOL$ WRI$_SQLSET_DEFINITIONS
DBA_SQL_PLAN_BASELINES IND$ WRI$_SQLSET_MASK
DBA_TABLES JSON$AUTO_INDEX WRI$_SQLSET_PLANS
DBA_TABLESPACES JSON$CHECK_EXPR WRI$_SQLSET_PLAN_LINES
DBA_USERS MON_MODS_ALL$ WRI$_SQLSET_STATEMENTS
DBMS_ADVISOR OBJ$ WRI$_SQLSET_STATISTICS
DBMS_APPLICATION_INFO PARTOBJ$ X$MODACT_LENGTH
DBMS_ASSERT PLITBLM XMLTYPE
DBMS_AUTO_INDEX PRVT_ADVISOR XQSEQUENCE
DBMS_AUTO_INDEX_LIB PRVT_SQLPA _auto_index_ind_objects
DBMS_AUTO_TASK_ADMIN SMB$CONFIG _auto_index_log
Documented No
First Available 19c
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvsaii.plb
Subprograms
AI_CLEANUP CONFIGURE INSERT_AUTO_INDEX_PARAMETERS
AI_CLEAR EXECUTE_TASK IS_AUTO_INDEX_TASK
AI_INIT FINDING_NAME MERGE_COLS_STR
AUTO_INDEX_ALLOW GEN_INITIAL_KEY_ID REPORT_ACTIVITY
CAPTURE_STS GET_LAST_ACTIVITY_TIME SET_DROPPED_INDEX_STATUS
CHECK_AUTO_INDEX_ENABLED GET_REPORT_LEVELS SET_GLOBAL_AI_TRACE
COMBINE_COL_GROUP GET_REPORT_SECTIONS TASK_PROC
 
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);
BEGIN dbms_auto_index_internal.ai_cleanup(1); END;
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 6397
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 6894
ORA-06512: at line 1
 
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 (new 21c)
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;
BEGIN dbms_auto_index_internal.check_auto_index_enabled; END;
*
ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9180
ORA-06512: at line 1
 
COMBINE_COL_GROUP (new 21c)
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;
/
DECLARE
*
ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7247
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9180
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7022
ORA-06512: at line 4
 
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)
FROM dual;

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-2021 19:23:45 11-JAN-2021 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-21 07.23.45.000000 PM -05:00
11-JAN-21 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')
FROM dual;

SELECT dbms_auto_index_internal.get_report_levels('DETAIL')
       *
ERROR at line 1:
ORA-13618: The specified value is not a valid value for procedure argument LEVEL.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 8498
 
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')
FROM dual;

SELECT dbms_auto_index_internal.get_report_sections('HEADER')
       *
ERROR at line 1:
ORA-13618: The specified value is not a valid value for procedure argument SECTION.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 8443
 
INSERT_AUTO_INDEX_PARAMETERS
Undocumented dbms_auto_index_internal.insert_auto_index_parameters;
SQL> exec dbms_auto_index_internal.insert_auto_index_parameters;
BEGIN dbms_auto_index_internal.insert_auto_index_parameters; END;
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.I_SMB$CONFIG_PKEY) violated
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9146
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9146
ORA-06512: at line 1
 
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 (new 21c)
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')
FROM dual;

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

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

DBMS_AUTO_INDEX_INTERNAL.MERGE_COLS_STR('42','100')
----------------------------------------------------
42,100
 
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 (new 21c)
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);
BEGIN dbms_auto_index_internal.task_proc(TRUE); END;
*
ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7349
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 6386
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9180
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 6293
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7283
ORA-06512: at line 1


exec dbms_auto_index_internal.task_proc(FALSE);
BEGIN dbms_auto_index_internal.task_proc(FALSE); END;
*
ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7349
ORA-06512: at "SYS.PRVT_ADVISOR", line 3546
ORA-06512: at "SYS.PRVT_ADVISOR", line 932
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7247
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9180
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7022
ORA-06512: at "SYS.WRI$_ADV_SQLACCESS_ADV", line 21
ORA-06512: at "SYS.PRVT_ADVISOR", line 915
ORA-06512: at "SYS.PRVT_ADVISOR", line 3451
ORA-06512: at "SYS.DBMS_ADVISOR", line 276
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 7308
ORA-06512: at line 1
 
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

Related Topics
Built-in Functions
Built-in Packages
DBMS_ADBTASK_ADMIN
DBMS_AUTO_INDEX
DBMS_I_INDEX_UTL
DBMS_INDEX_UTL
DBMS_INDEXING
DBMS_PCLXUTIL
DBMS_SPACE.CREATE_INDEX_COST
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