Oracle DBMS_AUTO_INDEX_INTERNAL
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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 19c
What's New In 20c-21c

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-2021 Daniel A. Morgan All Rights Reserved
  DBSecWorx