Shulou(Shulou.com)06/01 Report--

1. About testing

Recently, oracle has held a series of online sharing, one of the new features of 19c is automatic indexing. I tested it in docker and failed.

[oracle@8aa96a41b58b ~] $sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0-Production on Thu Feb 20 09:44:52 2020Version 19.3.0.0.0SQL > EXEC DBMS_AUTO_INDEX.CONFIGURE ('AUTO_INDEX_MODE','IMPLEMENT'); BEGIN DBMS_AUTO_INDEX.CONFIGURE (' AUTO_INDEX_MODE','IMPLEMENT'); END * ERROR at line 1:ORA-40216: feature not supportedORA-06512: at "SYS.DBMS_SYS_ERROR", line 79ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9180ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 283ORA-06512: at line 1 automatic indexing can only be configured on exadata machines. Auto indexing will be allowed only in EXADATA systems, if not it will raise ORA-40216: feature not supported when we try it turn it on in non exadata systems.ORA-40216 When Using Auto Index Feature (Doc ID 2570076.1)

two。 Check the system implicit parameters

SELECT i.ksppinm name, i.ksppdesc description, CV.ksppstvl VALUE, CV.ksppstdf isdefault, DECODE (BITAND (CV.ksppstvf, 7), 1, 'MODIFIED', 4,' SYSTEM_MOD', 'FALSE') ismodified, DECODE (BITAND (CV.ksppstvf, 2), 2,' TRUE', 'FALSE') isadjusted FROM sys.x$ksppi I Sys.x$ksppcv CV WHERE i.inst_id = USERENV ('Instance') AND CV.inst_id = USERENV (' Instance') AND i.indx = CV.indx AND i.ksppinm LIKE'% exadata%'/' ORDER BY REPLACE (i.ksppinm,'_','') One of them is: NAME DESCRIPTION VALUE ISDEFAULT ISMODIFIED ISADJ-----_ exadata_feature_on Exadata Feature On FALSE TRUE FALSE FALSE

3. Turn on implied parameters

SQL > alter system set "_ exadata_feature_on" = true scope=spfile;SQL > shut immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL > startupORACLE instance started.Total System Global Area 1610609888 bytesFixed Size 9135328 bytesVariable Size 385875968 bytesDatabase Buffers 1207959552 bytesRedo Buffers 7639040 bytesDatabase mounted.Database opened.

4. Retest

Note: the following test script is from oracle online sharing.

Reopen the automatic indexing configuration successfully. SQL > EXEC DBMS_AUTO_INDEX.CONFIGURE ('AUTO_INDEX_MODE','IMPLEMENT'); PL/SQL procedure successfully completed. There are three parameters to enable automatic indexing: EXEC DBMS_AUTO_INDEX.CONFIGURE ('AUTO_INDEX_MODE','IMPLEMENT | REPORT ONLY | OFF'); IMPLEMENT: enable automatic indexing and make the created index visible, which can be used by the optimizer. REPORT ONLY: enable, index is invisible automatic index OFF: turn off automatic index cdb automatic index enabled: SQL > COLUMN parameter_name FORMAT A40SQL > COLUMN parameter_value FORMAT A20SQL > SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config CON_ID PARAMETER_NAME PARAMETER_VALUE- 1 AUTO_INDEX_COMPRESSION OFF 1 AUTO_INDEX_DEFAULT_TABLESPACE** 1 AUTO_INDEX_MODE IMPLEMENT** 1 AUTO_INDEX_REPORT_RETENTION 31 1 AUTO_INDEX_RETENTION_FOR_AUTO 373 1 AUTO_INDEX_RETENTION_FOR_MANUAL 1 AUTO_INDEX_SCHEMA 1 AUTO_INDEX_SPACE_BUDGET 50 3 AUTO_INDEX_COMPRESSION OFF 3 AUTO_INDEX_DEFAULT_TABLESPACE 3 AUTO_INDEX_MODE OFF CON_ID PARAMETER_NAME PARAMETER_VALUE -- 3 AUTO_INDEX_REPORT_RETENTION 31 3 AUTO_INDEX_RETENTION_FOR_AUTO 373 3 AUTO_INDEX_RETENTION_FOR_MANUAL 3 AUTO_INDEX_SCHEMA 3 AUTO_INDEX_SPACE_BUDGET 50

Enable pdb automatic indexing:

SQL > alter session set container=orclpdb;Session altered.SQL > EXEC DBMS_AUTO_INDEX.CONFIGURE ('AUTO_INDEX_MODE','IMPLEMENT'); PL/SQL procedure successfully completed.SQL > sELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config CON_ID PARAMETER_NAME PARAMETER_VALUE- 3 AUTO_INDEX_COMPRESSION OFF 3 AUTO_INDEX_DEFAULT_TABLESPACE * * 3 AUTO_INDEX_MODE IMPLEMENT** 3 AUTO_INDEX_REPORT_RETENTION 31 3 AUTO_INDEX_RETENTION_FOR_AUTO 373 3 AUTO_INDEX_RETENTION_FOR_MANUAL 3 AUTO_INDEX_SCHEMA 3 AUTO_INDEX_SPACE_BUDGET 508 rows selected.

Create storage space for automatic indexing

By default, the permanent tablespace specified during database creation is used to store automatic indexes. Whether to configure the checkable AUTO_INDEX_DEFAULT_TABLESPACE parameter. SQL > create tablespace tbs_auto datafile'/ opt/oracle/oradata/ORCL/ORCLPDB/tbs_auto_ind01.dbf' size 100M / Tablespace created. Set the default tablespace SQL > EXEC DBMS_AUTO_INDEX.CONFIGURE ('AUTO_INDEX_DEFAULT_TABLESPACE','tbs_auto') used by automatic indexing; PL/SQL procedure successfully completed.SQL > SELECT con_id, parameter_name, parameter_value FROM cdb_auto_index_config CON_ID PARAMETER_NAME PARAMETER_VALUE- 3 AUTO_INDEX_COMPRESSION OFF 3 AUTO_INDEX_DEFAULT_TABLESPACE TBS_AUTO 3 AUTO_INDEX_MODE IMPLEMENT 3 AUTO_INDEX_REPORT_RETENTION 31 3 AUTO_INDEX_RETENTION_FOR_AUTO 373 3 AUTO_INDEX_RETENTION_FOR_MANUAL 3 AUTO_INDEX_SCHEMA 3 AUTO_INDEX_SPACE_BUDGET 50 keep the system using the following stored procedure EXEC DBMS_AUTO_INDEX.CONFIGURE ('AUTO_INDEX_DEFAULT_TABLESPACE') by default NULL)

Specifies that the user enables automatic indexing

When automatic indexing is enabled in the database, automatic indexing is available to all users in the database by default. You can check the cdb_auto_index_config.AUTO_INDEX_SCHEMA field to see different configurations. SH cannot use automatic indexing (AUTO_INDEX_SCHEMA=schema NOT IN (SH)): EXEC DBMS_AUTO_INDEX.CONFIGURE ('AUTO_INDEX_SCHEMA',' SH', FALSE); only SH can use automatic indexing (AUTO_INDEX_SCHEMA=schema IN (SH)): EXEC DBMS_AUTO_INDEX.CONFIGURE ('AUTO_INDEX_SCHEMA',' SH', TRUE) Automatic indexing is available to all users in the database: EXEC DBMS_AUTO_INDEX.CONFIGURE ('AUTO_INDEX_SCHEMA', NULL, TRUE)

Simulate automatic index creation:

SQL > create table hr.tab_auto as select rownum id,t.* from dba_objects twitterTable created.SQL > declarea varchar2 (2000): =''; 2 3 begin 4 for x in 1. 10000 loop 5 select object_name into a from tab_auto where id=x; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed.SQL > COLUMN task_name FORMAT A30SQL > COLUMN advisor_name FORMAT A30SQL > select task_name,advisor_name from dba_advisor_tasks where owner='SYS' ORDER BY TASK_ID TASK_NAME ADVISOR_NAME-- SYS_AUTO_SPM_EVOLVE_TASK SPM Evolve AdvisorSYS_AI_SPM_EVOLVE_TASK SPM Evolve Advisor 'HTML',section = >' SUMMARY + INDEX_DETAILS + ERRORS' Level = > 'BASIC')

Dependent view

Displays the current configuration settings for automatic indexing. The AUTO column in some views of DBA_AUTO_INDEX_CONFIG indicates whether the index is automatically indexed (YES) or not (NO). DBA_INDEXESALL_INDEXESUSER_INDEXES

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.