ORACLE

Automatic indexing是Oracle Database 19c开始新增加的特性，依据应用负载的变化自动/动态地进行索引的管理任务，比如创建(create index)、重构(rebuild index)和删除(drop index)，从而提高数据库性能，这个特性也是Oracle 自治数据库云服务自我优化的一个基础。
Automatic indexing 主要功能
1）定期在预定义的时间间隔内在后台运行自动索引过程
2）分析应用程序工作负载，并根据分析报告相应地创建必要的新的索引，并删除现有耗费性能的

Automatic Indexing相关的数据字典
DBA_AUTO_INDEX_CONFIG –描述当前自动索引的配置
DBA_INDEXES/ALL_INDEXES/USER_INDEXES –新增加的AUTO列标识是自动索引(YES)还是手动索引(NO)
DBA_AUTO_INDEX_EXECUTIONS –显示历史自动索引任务执行
DBA_AUTO_INDEX_STATISTICS –显示与自动索引相关的统计信息
DBA_AUTO_INDEX_IND_ACTIONS –显示在自动索引上执行的操作
DBA_AUTO_INDEX_SQL_ACTIONS –显示在SQL上执行的验证自动索引的操作

DBMS_AUTO_INDEX.CONFIGURE包相关参数
AUTO_INDEX_DEFAULT_TABLESPACE –指定自动索引创建所存储的表空间
AUTO_INDEX_MODE –指定自动索引的模式(开关)，当前3个值，默认OFF,表示特性关闭；IMPLEMENT表示自动创建创建、测试、并报告，最终索引是visible状态; REPORT ONLY 会创建索引但是invisible,不会影响SQL，只是意图生成报告。
AUTO_INDEX_REPORT_RETENTION –自动索引报告历史保留的天数默认31天
AUTO_INDEX_RETENTION_FOR_AUTO — 自动创建的索引从上次使用后多少天不再使用的索引可以删除，默认373天
AUTO_INDEX_RETENTION_FOR_MANUAL — 手动创建的索引从上次使用后多少天不再使用的索引可以删除，默认永远
AUTO_INDEX_SPACE_BUDGET — 自动索引可以使用表空间大小的百分比，默认 50%

Automatic Indexing工作原理
索引管理后台进程TASK调用，可以自动的create, rebuild , drop 索引。后台进程是每15分钟调用一次,(是有j001进程执行_AUTO_INDEX_TASK_INTERVAL参数控制15分钟)。
也是基于传统手动优化SQL的思路，基于SQL中的列使用识别可以创建的索引，然后验证自动索引对性能的影响，然后按预设的值去创建索引，只不过整个过程是自动的，并且整个过程都有审核报告。

Automatic Indexing测试体验
1，查看Oracle数据库版本
[oracle19@source ~]$ sqlplus / as sysdba
…
Version 19.3.0.0.0

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0

SQL> select banner_full from v$version;

BANNER_FULL
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0

SQL> select * from DBA_AUTO_INDEX_CONFIG;

PARAMETER_NAME PARAMETER_VALUE LAST_MODIFIED MODIFIED_BY

AUTO_INDEX_DEFAULT_TABLESPACE

AUTO_INDEX_MODE OFF —并未启用该参数

AUTO_INDEX_REPORT_RETENTION 31

AUTO_INDEX_RETENTION_FOR_AUTO 373

AUTO_INDEX_RETENTION_FOR_MANUAL

AUTO_INDEX_SCHEMA

AUTO_INDEX_SPACE_BUDGET 50

2，开启该特性，可以在CDB也可以在PDB
12:29:43 SYS@ludadbhost(source)> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
1
2 PDB$SEED READ ONLY NO
3 ludadbhostPDB READ WRITE NO
1
2
12:29:58 SYS@ludadbhost(source)> alter session set container=ludadbhostpdb;

Session altered.

12:30:29 SYS@ludadbhost(source)> 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 supported
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”, line 283
ORA-06512: at line 1
通过MOS查询需要开启一个隐患参数并重启数据库服务
12:33:45 SYS@ludadbhost(source)> alter system set “_exadata_feature_on”=true scope=spfile;

System altered.

12:33:48 SYS@ludadbhost(source)> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
12:34:22 SYS@ludadbhost(source)> startup
ORACLE instance started.

Total System Global Area 2147483552 bytes
Fixed Size 9146272 bytes
Variable Size 1090519040 bytes
Database Buffers 1023410176 bytes
Redo Buffers 24408064 bytes
Database mounted.
Database opened.
12:34:47 SYS@ludadbhost(source)> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,‘IMPLEMENT’);

PL/SQL procedure successfully completed. —在PDB中进行完成
12:36:12 SYS@ludadbhost(source)> col PARAMETER_VALUE for a20
12:36:19 SYS@ludadbhost(source)> /

PARAMETER_NAME PARAMETER_VALUE LAST_MODIFIED

AUTO_INDEX_COMPRESSION OFF
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE IMPLEMENT 07-JAN-20 12.35.14.000000 PM
AUTO_INDEX_REPORT_RETENTION 31
AUTO_INDEX_RETENTION_FOR_AUTO 373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET 50

8 rows selected.
3，创建用户以及对应数据表空间
10:55:52 SYS@ludadbhost(source)> alter session set container=ludadbhostpdb;

Session altered.

10:55:59 SYS@ludadbhost(source)> select file_name from dba_data_files;

/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/system01.dbf
/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/sysaux01.dbf
/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/undotbs01.dbf
/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/users01.dbf

10:56:16 SYS@ludadbhost(source)> CREATE TABLESPACEluda_data DATAFILE ‘/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/LUDA_data01.dbf’ SIZE 5G AUTOEXTEND Off;
Tablespace created.

10:58:33 SYS@ludadbhost(source)> 10:58:33 SYS@ludadbhost(source)> CREATE TABLESPACEluda_idx DATAFILE ‘/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/LUDA_idx01.dbf’ SIZE 2G AUTOEXTEND Off;
Tablespace created.

10:58:45 SYS@ludadbhost(source)> 10:58:45 SYS@ludadbhost(source)> create userluda IDENTIFIED BYluda ACCOUNT UNLOCK DEFAULT TABLESPACEluda_data TEMPORARY TABLESPACE TEMP;
User created.

10:58:55 SYS@ludadbhost(source)> grant connect,resource toluda;
Grant succeeded.

10:59:00 SYS@ludadbhost(source)> grant select any table toluda;
Grant succeeded.

4，创建数据
11:37:34luda@ludadbhost(source)> create table index_test as select * from dba_objects;

Table created.

11:37:47luda@ludadbhost(source)> select count(*) from index_test;

COUNT(*)
72406

11:38:50luda@ludadbhost(source)> insert into index_test select * from index_test;

72406 rows created.

11:39:03luda@ludadbhost(source)> insert into index_test select * from index_test;

144812 rows created.

11:39:07luda@ludadbhost(source)> insert into index_test select * from index_test;

289624 rows created.

11:39:09luda@ludadbhost(source)> insert into index_test select * from index_test;

579248 rows created.

11:39:13luda@ludadbhost(source)> insert into index_test select * from index_test;

1158496 rows created.

11:39:16luda@ludadbhost(source)> insert into index_test select * from index_test;

2316992 rows created.

11:39:22luda@ludadbhost(source)> insert into index_test select * from index_test;

4633984 rows created.

11:39:33luda@ludadbhost(source)> select count(*) from index_test;

COUNT(*)
9267968
11:39:45luda@ludadbhost(source)> insert into index_test select * from index_test;

9267968 rows created.

11:40:13luda@ludadbhost(source)> 11:40:13luda@ludadbhost(source)> select count(*) from index_test;

COUNT(*)
18535936

11:41:49luda@ludadbhost(source)> update index_test set object_id=rownum;

18535936 rows updated.

11:45:06luda@ludadbhost(source)> commit;

5，模拟日常查询并查看执行计划
13:59:06luda@ludadbhost(source)> select object_type from index_test where object_id=5555;

OBJECT_TYPE
VIEW

13:59:21luda@ludadbhost(source)> explain plan for SELECT OBJECT_NAME FROM INDEX_TEST WHERE OBJECT_ID=1;

Explained.

13:59:35luda@ludadbhost(source)> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 356488860

| 0 | SELECT STATEMENT | | 1 | 40 | 392 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| INDEX_TEST | 1 | 40 | 392 (1)| 00:00:01 |

Predicate Information (identified by operation id):

1 – filter(“OBJECT_ID”=1)

13 rows selected.
该访问路径是全表扫描的方式；我们多次执行以下语句，并静等15分钟查看相关试图是否有结果
14:05:03luda@ludadbhost(source)> select object_type from index_test where object_id=5559;
14:05:03luda@ludadbhost(source)> select object_type from index_test where object_id=50;
14:05:03luda@ludadbhost(source)> select object_type from index_test where object_id=51;
14:05:03luda@ludadbhost(source)> select object_type from index_test where object_id=52;
14:05:03luda@ludadbhost(source)> select created from index_test where object_id=345;
15:38:33luda@ludadbhost(source)> select * from DBA_AUTO_INDEX_EXECUTIONS;

15:55:34luda@ludadbhost(source)> select index_name,table_name,command,statement from DBA_AUTO_INDEX_IND_ACTIONS where execution_name=‘SYS_AI_2020-01-07/15:40:57’ order by action_id;

INDEX_NAME TABLE_NAME COMMAND STATEMENT

SYS_AI_66825yg9wksv1 INDEX_TEST CREATE INDEX CREATE INDEX “LUDA”.“SYS_AI_66825yg9wksv1” ON “LUDA”.“INDEX_TEST”(“OBJECT_ID”) T
SYS_AI_66825yg9wksv1 INDEX_TEST REBUILD INDEX ALTER INDEX “LUDA”.“SYS_AI_66825yg9wksv1” REBUILD ONLINE
SYS_AI_66825yg9wksv1 INDEX_TEST ALTER INDEX VISIBLE ALTER INDEX “LUDA”.“SYS_AI_66825yg9wksv1” VISIBLE
分了三步，第一创建一个索引，第二步设置online属性，第三步设置为可见状态；

6，检查该SYS_AI索引是否真实存在
15:56:43luda@ludadbhost(source)> explain plan for select object_name from index_test where object_id=1;

案例参考csdn

Oracle 19c Automatic indexing

最近在一个项目中碰到一个事情，缘由大概如下：
业务由中间价tomcat连接到oracle db，每连接一次呢要插入对应表中一条数据，结果出现了偶尔报错ora-01461的错误，估计是因为插入的数据表中对应字段使用varchar2类型数据，但是经过字符集转换后由于长短不一直造成自负超过4000字节，此时数据库会将数据类型转换成long字段类型数据插入，导致字段类型不一致，所以报错01046的错误。
像这类错误只会在业务前端展示，在数据库这层无法看到明确的报错，解决这类问题有2个思路：

一是更新jdbc的驱动；

二是找到对应的表更改字段的varchar2为long等长字段。

我遇到情况是上面的第一种，当时我采用oracle 10g最新的jdbc驱动更新中间件的tomcat（数据库版本为9206），但是很遗憾隔了没多久又出现同样的报错情况。最终我决定要找到对应的表，可是这时候应用开发也不知道是哪张表，无赖之下只能在oracle层面采取errorstack的方式跟踪，大概的原理如下：

errorstack事件：dump 错误栈信息，通常Oracle发生错误时前台进程将得到一条错误信息，但某些情况下得不到错误信息，可以采用这种方式得到Oracle错误。

使用语法：

alter session set events ’12899 trace name errorstack forever’; 不需要重启数据库，在session级别生效。
alter system set events ’12899 trace name errorstack forever’;-需要重启数据库，在全局系统生效

我这里以错误ORA-12899的方式为例，具体如下：

执行语句：

alter system set events ’12899 trace name errorstack forever’;

SQL> alter system set events '12899 trace name errorstack forever';

System altered.

SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(4)

SQL> insert into t1 values(250,'ludatou');
insert into t1 values(250,'ludatou')
*
ERROR at line 1:
ORA-12899: value too large for column "SYS"."T1"."NAME" (actual: 7, maximum: 4)

可以在user_dump下找到最新的dump，ls -lFrt，就为此次错误的跟踪trace,Trace的内容如下：

*** 2014-01-15 17:24:13.461
----- Error Stack Dump -----
ORA-12899: value too large for column "SYS"."T1"."NAME" (actual: 7, maximum: 4)
----- Current SQL Statement for this session (sql_id=8f4g5vujpymhg) -----
insert into t1 values(250,'ludatou')
↑↑↑↑↑
这个trace把语句也抓起来了。

----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------

*** 2014-01-15 17:24:13.992
skdstdst()+38 call kgdsdst() BFD338FC ? 2 ?
ksedst1()+88 call skdstdst() BFD338FC ? 0 ? 1 ? AC9E128 ?
8551B9E ? AC9E128 ?
ksedst()+33 call ksedst1()+8 0 ? F9F580 ? 2050033 ? 0 ?
FF ? 2004 ?
dbkedDefDump()+1062 call ksedst() 0 ? 0 ? 0 ? 0 ? 0 ? 0 ?
ksedmp()+47 call dbkedDefDump() 1 ? 0 ?
dbkdaKsdActDriver() call 00000000 1 ? 1F060C ? 1D19EC ? 0 ?
+1481 FAA03A0 ? F9E3720 ?
dbgdaExecuteAction( call 00000000 F9F580 ? BFD35630 ?
)+595
dbgdaRunAction()+89 call dbgdaExecuteAction( F9F580 ? F21B294 ? 20C0002 ?
4 ) BFD35630 ? 1 ?
dbgdRunActions()+64 call dbgdaRunAction() F9F580 ? 71D0810 ?
dbgdProcessEventAct call dbgdRunActions() DC16520 ? DC16529 ?
ions()+604
dbgdChkEventKgErr() call dbgdProcessEventAct F9F580 ? FAA03A0 ? 71D093C ?
+1542 ions() 0 ? 1 ? EA055FB ?
dbkdChkEventRdbmsEr call dbgdChkEventKgErr() F9F580 ? 223BAD0 ? 3263 ?
r()+23
__PGOSF12_ksfpec()+ call dbkdChkEventRdbmsEr 3263 ? 8 ? EABE0A0 ?
139 r() EABE0A0 ? EAB6A38 ?
BFD35F24 ?
dbgePostErrorKGE()+ call 00000000 FAA03A0 ? 3263 ?
825

Oracle异常错误在数据库层面的跟踪—ERRORSPACK

Oracle 恢复工具 Mdata 5.0.1 版本发布

近期文章

分类目录

扫码关注微信公众号:Oracle运维那些事获取定期发布的数据库运维的有趣事情!

近期活动

Oracle 恢复工具 Mdata 5.0.1 版本发布

近期文章

分类目录

扫码关注微信公众号:Oracle运维那些事 获取定期发布的数据库运维的有趣事情!

近期活动

扫码关注微信公众号:Oracle运维那些事获取定期发布的数据库运维的有趣事情!