ora-65532 Automatic Indexing - Autonomous Database (Shared) (Doc ID 2764331.1)_ora-65532: cannot alter or drop automatically crea-CSDN博客

APPLIES TO:

Autonomous Database on Shared Infrastructure - Version N/A to N/A
Information in this document applies to any platform.

GOAL

The automatic indexing feature in Oracle Database is enabled in Autonomous Database. The automatic indexing feature automates index management tasks, such as creating, rebuilding, and dropping indexes in an Oracle Database based on changes in the application workload.

This feature improves database performance by managing indexes automatically in an Oracle Database. This Note explains how to enable / disable Auto Index functionality at Database / Schema / Table level.

SOLUTION

Configuring Automatic Indexing in a Database level

You can configure automatic indexing in an Oracle database using the DBMS_AUTO_INDEX.CONFIGURE procedure.

exec dbms_auto_index.configure('AUTO_INDEX_MODE','<IMPLEMENT/REPORT ONLY/OFF>');

AUTO_INDEX_MODE: Modes of operation of auto indexes. It can have one of the following values:

IMPLEMENT: In this mode, new auto indexes are created as visible indexes and any existing invisible auto indexes are also set to visible indexes. In this mode, auto indexes are available to be used in SQL statements.
REPORT ONLY: In this mode, new auto indexes are created as invisible indexes and are not available to be used in SQL statements.
OFF: Setting the mode to OFF prevents new auto indexes from being considered and created. However, it does not disable existing auto indexes.

DBA_AUTO_INDEX_CONFIG displays the current configuration parameter settings for automatic indexing

SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_MODE';

PARAMETER_NAME PARAMETER_VALUE
------------------------------ ------------------------------
AUTO_INDEX_MODE OFF

To enable automatic indexing in a database and creating any new auto indexes as visible indexes:

SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','IMPLEMENT');
SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_MODE';
PARAMETER_NAME PARAMETER_VALUE
------------------------------
AUTO_INDEX_MODE IMPLEMENT

To enable automatic indexing in a database and creating any new auto indexes as invisible indexes:

SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT ONLY');
SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_MODE';

PARAMETER_NAME PARAMETER_VALUE
------------------------------
AUTO_INDEX_MODE REPORT ONLY

To disable automatic indexing in a database so that no new auto indexes are created (existing auto indexes remain enabled):

SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','OFF')
SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_MODE';

PARAMETER_NAME PARAMETER_VALUE
------------------------------
AUTO_INDEX_MODE OFF

Enabling Auto Index at schema level

You can use the AUTO_INDEX_SCHEMA configuration setting to specify schemas that can use auto indexes.
When automatic indexing is enabled in a database, all the schemas in the database can use auto indexes by default.

The automatic indexing process manages two schema lists – the inclusion list and the exclusion list. The inclusion list contains the schemas that can use auto indexes. The exclusion list contains the schemas that cannot use auto indexes. Initially, both these lists are empty and all the schemas in the database can use auto indexes when automatic indexing is enabled for a database.

execute dbms_auto_index.configure('AUTO_INDEX_SCHEMA','<Schema Name>',<TRUE/FALSE/NULL>);

TRUE: Add the specified schema to the inclusion list.
FALSE: Add the specified schema to the exclusion list.
NULL: Remove the specified schema from the list to which it is currently added

If the inclusion list contains at least one schema, then only the schemas listed in the inclusion list can use auto indexes:

SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_SCHEMA';

PARAMETER_NAME PARAMETER_VALUE
------------------------------
AUTO_INDEX_SCHEMA

SQL> execute dbms_auto_index.configure('AUTO_INDEX_SCHEMA','ADMIN',TRUE);
SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_SCHEMA';

PARAMETER_NAME PARAMETER_VALUE
------------------------------
AUTO_INDEX_SCHEMA schema IN (ADMIN)

In this case ADMIN schema only enabled with auto indexes and all other schemas excluded.

If the inclusion list is empty and the exclusion list contains at least one schema, then all the schemas can use auto indexes, except the schemas listed in the exclusion list:

SQL> execute dbms_auto_index.configure('AUTO_INDEX_SCHEMA','ADMIN',FALSE);
SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_SCHEMA';

PARAMETER_NAME PARAMETER_VALUE
------------------------------
AUTO_INDEX_SCHEMA schema NOT IN (ADMIN)

In this case ADMIN schema excluded from auto indexes feature and all other schemas included.

If both the lists (the inclusion list and the exclusion list) contain at least one schema, then all the schemas can use auto indexes, except the schemas listed in the exclusion list:

SQL> execute dbms_auto_index.configure('AUTO_INDEX_SCHEMA','ADMIN',TRUE);
SQL> execute dbms_auto_index.configure('AUTO_INDEX_SCHEMA','TCUSER',FALSE);

SQL> select parameter_name,parameter_value from dba_auto_index_config where parameter_name='AUTO_INDEX_SCHEMA';

PARAMETER_NAME PARAMETER_VALUE
------------------------------ ------------------------------------------------------------
AUTO_INDEX_SCHEMA schema IN (ADMIN) AND schema NOT IN (TCUSER)

In this case, TCUSER is excluded from auto indexes feature and all other schemas included.

Generating Automatic Indexing Reports

You can generate reports related to automatic indexing operations in an Oracle database using the REPORT_ACTIVITY and REPORT_LAST_ACTIVITY functions of the DBMS_AUTO_INDEX package.

Generating a report of automatic indexing operations for last 24 hours

SQL> set long 1000000
SQL> select dbms_auto_index.report_activity from dual;

REPORT_ACTIVITY
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 10-APR-2021 20:14:05
Activity end : 11-APR-2021 20:14:05
Executions completed : 95
Executions interrupted : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)

-------------------------------------------------------------------------------
REPORT_ACTIVITY
--------------------------------------------------------------------------------
Index candidates : 0
Indexes created : 0
Space used : 0 B
Indexes dropped : 0
SQL statements verified : 0
SQL statements improved : 0
SQL plan baselines created : 0
Overall improvement factor : 0x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)

REPORT_ACTIVITY

--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
-------------------------------------------------------------------------------

ERRORS

--------------------------------------------------------------------------------
No errors found.
--------------------------------------------------------------------------------

Generating a report of automatic indexing operations for a specific period

SQL> set long 1000000
SQL> select dbms_auto_index.report_activity(activity_start => TO_TIMESTAMP('2021-04-01', 'YYYY-MM-DD'), activity_end => TO_TIMESTAMP('2021-04-11', 'YYYY-MM-DD')) from dual;

DBMS_AUTO_INDEX.REPORT_ACTIVITY(ACTIVITY_START=>TO_TIMESTAMP('2021-04-01','YYYY-
--------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 01-APR-2021 00:00:00
Activity end : 11-APR-2021 00:00:00
Executions completed : 951
Executions interrupted : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)

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

DBMS_AUTO_INDEX.REPORT_ACTIVITY(ACTIVITY_START=>TO_TIMESTAMP('2021-04-01','YYYY-

--------------------------------------------------------------------------------
Index candidates : 0
Indexes created : 0
Space used : 0 B
Indexes dropped : 0
SQL statements verified : 0
SQL statements improved : 0
SQL plan baselines created : 0
Overall improvement factor : 0x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)

DBMS_AUTO_INDEX.REPORT_ACTIVITY(ACTIVITY_START=>TO_TIMESTAMP('2021-04-01','YYYY-

ERRORS

--------------------------------------------------------------------------------
-------------
No errors found.
--------------------------------------------------------------------------------

How Automatic Indexing Works

The automatic indexing process runs in the background every 15 minutes and performs the following operations:

Identifies auto index candidates

Auto index candidates are identified based on the usage of table columns in SQL statements.
Ensure that table statistics are up to date. Tables without statistics are not considered for auto indexing. Tables with stale statistics are not considered for auto indexing, if real-time statistics are not available.

Creates invisible auto indexes for the auto index candidates

The auto index candidates are created as invisible auto indexes, that is, these auto indexes cannot be used in SQL statements.
Automatic indexes can be single-column or multi-column.

The invisible auto indexes are validated against SQL statements.

If the performance of SQL statements is improved by using these indexes, then the indexes are configured as visible indexes, so that they can be used in SQL statements.
If the performance of SQL statements is not improved by using these indexes, then the indexes remain invisible.

Deletes the unused auto indexes

The auto indexes that are not used for a long period are deleted.

To verify automatic indexing process execution details, you can run dba_auto_index_executions

SQL> select execution_name,execution_start,execution_end,status from dba_auto_index_executions order by execution_start desc fetch first 4 rows only;
EXECUTION_NAME EXECUTION EXECUTION STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------- --------- -----------
SYS_AI_2021-04-15/02:39:31 15-APR-21 15-APR-21 COMPLETED
SYS_AI_2021-04-15/02:24:19 15-APR-21 15-APR-21 COMPLETED
SYS_AI_2021-04-15/02:09:08 15-APR-21 15-APR-21 COMPLETED
SYS_AI_2021-04-15/01:53:58 15-APR-21 15-APR-21 COMPLETED

Example on how the Auto Index is created

SQL> select execution_name,execution_start,execution_end,status from dba_auto_index_executions order by execution_start desc fetch first 1 rows only;
EXECUTION_NAME EXECUTION EXECUTION STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------- --------- -----------
SYS_AI_2021-04-15/02:39:31 15-APR-21 15-APR-21 COMPLETED

SQL> create table customer as select * from ssb.customer;
Table created.

SQL> execute dbms_stats.gather_table_stats('ADMIN','CUSTOMER');
PL/SQL procedure successfully completed.

SQL> select table_name,index_name,auto,visibility from dba_indexes where table_name='CUSTOMER' and table_owner='ADMIN';
no rows selected

SQL> select c_phone from customer where c_phone='32-887-896-3797';
C_PHONE
---------------
32-887-896-3797

SQL> select table_name,index_name,auto,visibility from dba_indexes where table_name='CUSTOMER' and table_owner='ADMIN';
no rows selected

Wait for the next automatic Index process to complete(15 minutes interval from previous execution)

SQL> select execution_name,execution_start,execution_end,status from dba_auto_index_executions order by execution_start desc fetch first 1 rows only;

EXECUTION_NAME EXECUTION EXECUTION STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------- --------- -----------
SYS_AI_2021-04-15/03:09:53 15-APR-21 15-APR-21 COMPLETED

SQL> select table_name,index_name,auto,visibility,status from dba_indexes where table_name='CUSTOMER' and table_owner='ADMIN';

TABLE_NAME INDEX_NAME AUT VISIBILIT STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- --- --------- --------
CUSTOMER SYS_AI_7mg8qpqchj3t0 YES INVISIBLE UNUSABLE

SQL> select index_name,column_name from dba_ind_columns where table_name='CUSTOMER' and table_owner='ADMIN';

INDEX_NAME COLUMN_NAME
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------
SYS_AI_7mg8qpqchj3t0 C_PHONE

Since c_phone is used in the where predicate, an invisible auto index will be created on c_phone column.

How to Drop Auto Index

The procedure DROP_AUTO_INDEXES can be used to manually drop the automatically created indexes that overrides the retention parameter setting

SQL> exec dbms_auto_index.drop_auto_indexes('ADMIN','"SYS_AI_7mg8qpqchj3t0"',TRUE);

PL/SQL procedure successfully completed.

You will not able to use drop index command to drop a auto index

SQL> drop index "SYS_AI_7mg8qpqchj3t0";
drop index "SYS_AI_7mg8qpqchj3t0"
*
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

Drop all indexes owned by ADMIN and allow recreate:
exec dbms_auto_index.drop_auto_indexes('ADMIN',NULL,TRUE);

Drop all indexes owned by ADMIN, disallowing recreate and then change the recreation status back to allow:

exec dbms_auto_index.drop_auto_indexes('ADMIN',NULL);
exec dbms_auto_index.drop_auto_indexes('ADMIN', NULL, TRUE);