Oracle 19cR2 DBMS_AUTO

Oracle DBMS_AUTO_INDEX Version 19.2.0.1

General Information

Library Note

Morgan's Library Page Header

For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.

Purpose The Oracle Database periodically runs a task, SYS_AUTO_INDEX_TASK that monitors
the system and creates indexes based on workload running in the system. This package contains subprograms that display the report of the task,
configure task execution and behavior.

AUTHID DEFINER

Dependencies

Documented Yes

First Available 19c

Security Model Owned by SYS with EXECUTE granted to the DBA role.

Source {ORACLE_HOME}/rdbms/admin/dbmsai.sql

Subprograms

CONFIGURE	REPORT_ACTIVITY	REPORT_LAST_ACTIVITY
DROP_SECONDARY_INDEXES

CONFIGURE (new 19c)

Sets configuration options for auto indexing. dbms_auto_index.configure( parameter_name IN VARCHAR2, parameter_value IN VARCHAR2, allow IN BOOLEAN := TRUE);

exec dbms_auto_index.configure('AUTO_INDEX_REPORT_RETENTION', '90', TRUE);

DROP_SECONDARY_INDEXES (new 19c)

Drops indexes that are not used for constraints for given (optional) schema and table. If no schema is given, all indexes which the user has priviledges on will be dropped. If no table is given, all indexes within the given schema will be dropped. dbms_auto_index.drop_secondary_indexes( ownname IN VARCHAR2 DEFAULT NULL, tabname IN VARCHAR2 DEFAULT NULL);

exec dbms_auto_index.drop_secondary_indexes('UWCLASS', 'SERVERS');

REPORT_ACTIVITY (new 19c)

Reports the results of auto index executions between specified activity_start and activity_end dbms_auto_index.report_activity( activity_start IN TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP - 1, activity_end IN TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP, type IN VARCHAR2 := 'TEXT', section IN VARCHAR2 := 'ALL', level IN VARCHAR2 := 'TYPICAL') RETURN CLOB);

exec dbms_auto_index.report_activity(SYSTIMESTAMP-24, SYSTIMESTAMP, 'TEXT', 'INDEX_DETAILS', 'ALL');

REPORT_LAST_ACTIVITY (new 19c)

Reports the results of last auto index task execution dbms_auto_index.report_last_activity( type IN VARCHAR2 := 'TEXT', section IN VARCHAR2 := 'ALL', level IN VARCHAR2 := 'TYPICAL') RETURN CLOB;

spool /home/oracle/autoidx_last_activity.html

      

      exec dbms_auto_index.report_last_activity('HTML', 'ALL', 'ALL');

      

      spool off