联合实验室 网站建设方案,阿里云域名续费,棋牌游戏在哪做网站,php靓号网站源码☘️博主介绍☘️#xff1a; ✨又是一天没白过#xff0c;我是奈斯#xff0c;DBA一名✨ ✌✌️擅长Oracle、MySQL、SQLserver、Linux#xff0c;也在积极的扩展IT方向的其他知识面✌✌️ ❣️❣️❣️大佬们都喜欢静静的看文章#xff0c;并且也会默默的点赞收藏加关注❣… ☘️博主介绍☘️ ✨又是一天没白过我是奈斯DBA一名✨ ✌✌️擅长Oracle、MySQL、SQLserver、Linux也在积极的扩展IT方向的其他知识面✌✌️ ❣️❣️❣️大佬们都喜欢静静的看文章并且也会默默的点赞收藏加关注❣️❣️❣️ 经过一段时间的沉寂我回来了今天要给大家分享Oracle数据库在19c版本中引入的一个革命性的新特性自动索引。 自动索引如其名是为了简化Oracle数据库的索引管理而生的。自动索引会根据应用程序工作负载的变化自动创建、重建和删除数据库中的索引从而提高数据库性能。 现在想象一下这样一个场景数据库不再需要你手动干预它可以根据应用程序的工作负载变化智能地创建、重建或删除索引。这不仅大大减轻了DBA的工作负担更重要的是它能够根据实际的数据访问模式自动优化数据库的性能。 那么自动索引是如何工作的呢它又是如何帮助我们提高数据库的性能呢别着急接下来我会详细为大家解读这一强大的新功能。 在开始今天的内容之前我先介绍一下索引的优势、缺点还有索引在什么情况下效果不好以便更好的去了解怎么正确的使用索引。 索引的优势 1.索引是表的一个概念部分用来提高检索数据的效率ORACLE使用了一个复杂的自平衡B-tree结构.通常通过索引查询数据比全表扫描要快.当ORACLE找出执行查询和Update语句的最佳路径时ORACLE优化器将使用索引同样在联结多个表时使用索引也可以提高效率。 2.另一个使用索引的好处是它提供了主键(primary key)的唯一性验证。那些LONG或LONG RAW数据类型你可以索引几乎所有的列。通常在大型表中使用索引特别有效当然你也会发现在扫描小表时使用索引同样能提高效率。 索引的缺点 1.存储索引占用磁盘空间 2.执行DML操作时产生索引维护索引是提高数据查询最有效的方法也是最难全面掌握的技术因为正确的索引可能使效率提高10000倍而无效的索引可能是浪费了数据库空间甚至大大降低查询性能。 在表上创建更多的索引不意味着更快的查询在有索引的表上被提交的每个DML操作都意味着要必须更新所有的索引。表上的索引越多每当有记录在表中增减或索引列被修改时索引本身也会被修改这意味着每条记录的INSERTDELETEUPDATE将为此多付出4、5次的磁盘1/O因为索引需要额外的存储空间和处理那些不必要的索引反而会使查询反应时间变慢。而且表越大oracle服务器的影响越大。 3.表上有过多索引会严重影响insert插入性能 4.对delete操作删除少量数据索引可以有效快速定位如果删除大量数据就会有负面影响 5.对于update操作类似delete而且如果更新的是非索引列则无影响 索引在什么情况下效果不好 1.查询结果超过30%的表记录意味着数据经过过滤后返回的数据仍然很多那么使用索引过滤不一定好。但是随着优化器的优化固定的百分比不再决定是使用索引还是全表扫描。优化器现在更复杂了它的估计基于其他因素如表大小、行数和I/O块大小。 2.有时候索引比全表慢但这是同一个数据量级的区别小表。 One
Two
Three
开始今天的内容 官方文档对自动索引的详细介绍19c
Managing Indexes 自动索引限制 (1)自动索引只能创建本地B树索引。 (2)可以为分区表和非分区表创建自动索引。 (3)无法为临时表创建自动索引。 自动索引工作原理 自动索引过程每15分钟在后台运行一次并执行以下操作 (1)识别自动索引候选 自动索引候选项是根据SQL语句中表列的使用情况标识的。必须确保表统的计数据是最新的没有统计信息的表不考虑自动索引没有实时统计信息则不考虑使用陈旧统计信息的表进行自动索引。 (2)为自动索引候选创建不可见的自动索引 自动索引候选项创建为看不见自动索引即这些自动索引不能在SQL语句中使用。自动索引可以是单列的也可以是多列的考虑的理由如下 表列(包括虚拟列) 分区表和非分区表 (3)根据SQL语句验证不可见的自动索引 不可见的自动索引根据SQL语句进行验证。如果通过使用这些索引来提高SQL语句的性能则将索引配置为可见索引以便它们可以在SQL语句中使用。 如果使用这些索引没有提高SQL语句的性能则将索引配置为不可用索引和SQL语句是黑名单。之后自动索引过程会删除不可用的索引。黑名单中的SQL语句以后不允许使用自动索引。数据库中首次运行的SQL语句不能使用自动索引原因是Oracle无法对该SQL语句做出有效的判断。 (4)删除未使用的自动索引 长期不使用的自动索引将被删除。默认情况下373天后将删除未使用的自动索引。在数据库中保留未使用的自动索引的时间段可以使用DBMS_AUTO_INDEX.CONFIGURE程序。 自动索引相关包 dbms_auto_index.report_activity返回在数据库中特定期间执行的自动索引操作的报告。 dbms_auto_index.report_last_activity返回在数据库中执行的最后一次自动索引操作的报告。 dbms_auto_index.configure配置与自动索引相关的设置。 dbms_auto_index.drop_secondary_indexes从架构或表中删除所有索引(用于约束的索引除外)。 dbms_auto_index.drop_auto_indexes此过程可用于手动删除覆盖保留参数设置的自动创建的索引。 自动索引相关视图 select * from DBA_AUTO_INDEX_CONFIG; ---显示用于自动索引的当前配置设置。如果处于cdb模式会列出当前pdb的配置如果用sqlplus / as sysdba则会显示CDB$ROOT的配置。 select * from DBA_INDEXES; ---这个AUTO列指示索引是否为自动索引(YES)或不(NO). select s.NAME, t.PARAMETER_NAME, t.PARAMETER_VALUE, t.LAST_MODIFIED, t.MODIFIED_BY from cdb_auto_index_config t, v$containers s where t.CON_ID s.CON_ID; ---cdb模式下会列出每个pdb的配置情况 19c使用DBMS_AUTO_INDEX包需要先开启参数 alter system set _exadata_feature_ontrue scopespfile; ---19c版本中此功能仅限于企业版。通过设置初始化参数“_exadata_feature_ontrue”进行测试19c需要设置参数才能使用DBMS_AUTO_INDEX包不然报ORA-40216: feature not supported需要重启生效看19c之后的版本支不支持不设置参数就可以使用自动索引功能 一、启动、禁用、配置自动索引默认是禁用的
通过DBMS_AUTO_INDEX.CONFIGURE存储过程来启用、禁用、配置自动索引。 SQL select * from DBA_AUTO_INDEX_CONFIG; AUTO_INDEX_SCHEMA要包含或排除使用自动索引的用户。 AUTO_INDEX_DEFAULT_TABLESPACE用于存储自动索引的表空间。默认值是NULL这意味着在创建数据库期间指定的默认永久表空间用于存储自动索引。 AUTO_INDEX_SPACE_BUDGET空间大小用于自动索引的百分比 AUTO_INDEX_RETENTION_FOR_AUTO在数据库中保留未使用的自动索引的天数然后删除它们。默认值为373天。 AUTO_INDEX_RETENTION_FOR_MANUAL在数据库中保留未使用的手动创建的索引(非自动索引)的天数然后删除它们。当它被设置为NULL自动索引过程不会删除手动创建的索引。默认值是NULL.。 AUTO_INDEX_MODE自动索引的操作方式。 IMPLEMENT在此模式下新的自动索引创建为可见索引和任何现有的看不见自动索引也被设置为可见索引。在这种模式下可以在SQL语句中使用自动索引。 REPORT ONLY在此模式下新的自动索引创建为看不见索引不能在SQL语句中使用。 OFF将模式设置为OFF防止考虑和创建新的自动索引。但是它并不禁用现有的自动索引。AUTO_INDEX_COMPRESSION自动索引的高级索引压缩。默认值是OFF. AUTO_INDEX_REPORT_RETENTION在删除自动索引日志之前在数据库中保留这些日志的天数。由于自动索引报告是根据这些日志生成的因此不能在指定的值以外的时间内生成自动索引报告。AUTO_INDEX_REPORT_RETENTION。默认值为373天。 (1)库级别禁用 SQL EXEC DBMS_AUTO_INDEX.CONFIGURE(AUTO_INDEX_MODE,OFF); ---禁用(现有的自动索引仍然启用) (2)库级别启动 默认情况下如果启用了自动索引那么数据库中的所有用户都可以使用自动索引。 SQL EXEC DBMS_AUTO_INDEX.CONFIGURE(AUTO_INDEX_MODE,IMPLEMENT); ---启用并将任何新的自动索引创建为可见索引以便它们可以在SQL语句中使用 SQL EXEC DBMS_AUTO_INDEX.CONFIGURE(AUTO_INDEX_MODE,REPORT ONLY); ---启用但将任何新的自动索引创建为看不见索引因此它们不能在SQL语句中使用 (3)用户级别 SQL EXEC DBMS_AUTO_INDEX.CONFIGURE(AUTO_INDEX_SCHEMA, LEI, FALSE); ---除了用户LEI之外其他用户使用自动索引 SQL EXEC DBMS_AUTO_INDEX.CONFIGURE(AUTO_INDEX_SCHEMA, LEI, NULL); ---只有LEI用户可使用自动索引 SQL EXEC DBMS_AUTO_INDEX.CONFIGURE(AUTO_INDEX_SCHEMA, NULL, TRUE); ---所有用户都可以使用自动索引 (4)指定未使用的自动索引的保留时间 使用AUTO_INDEX_RETENTION_FOR_AUTO配置设置以指定用于在数据库中保留未使用的自动索引的保留时间。未使用的自动索引在指定的保留期后被删除。 默认情况下373天后将删除未使用的自动索引。 SQL EXEC DBMS_AUTO_INDEX.CONFIGURE(AUTO_INDEX_RETENTION_FOR_AUTO, 90); ---未使用的自动索引的保留期设置为90天。 SQL EXEC DBMS_AUTO_INDEX.CONFIGURE(AUTO_INDEX_RETENTION_FOR_AUTO, NULL); ---未使用的自动索引的保留期重置为373天的默认值。 (5)指定未使用的非自动索引手动创建的索引的保留时间 使用AUTO_INDEX_RETENTION_FOR_MANUAL设置指定在数据库中未使用的非自动索引(手动创建的索引)的保留时间。未使用的非自动索引将在指定的保留期后删除。 默认情况下自动索引包不会删除未使用的非自动索引手动创建的索引. SQL EXEC DBMS_AUTO_INDEX.CONFIGURE(AUTO_INDEX_RETENTION_FOR_MANUAL, 60); ---未使用的非自动索引手动创建的索引的保留期设置为60天。 SQL EXEC DBMS_AUTO_INDEX.CONFIGURE(AUTO_INDEX_RETENTION_FOR_MANUAL, NULL); ---未使用的非自动索引手动创建的索引的保留期设置为NULL就不会被自动索引包删除。 (6)指定自动化索引日志的保留时间 使用AUTO_INDEX_REPORT_RETENTION配置设置以指定用于在数据库中保留自动索引日志的时间段。在指定的保留期之后自动索引日志将被删除。 默认情况下373天后会删除自动索引日志。 SQL EXEC DBMS_AUTO_INDEX.CONFIGURE(AUTO_INDEX_REPORT_RETENTION, 60); ---将自动索引日志的保留期设置为60天。 SQL EXEC DBMS_AUTO_INDEX.CONFIGURE(AUTO_INDEX_REPORT_RETENTION, NULL); ---将自动索引日志的保留期重置为默认值373天。 ps其他指定要存储自动索引的表空间、指定要分配给自动索引的表空间百分比、为自动索引配置高级索引压缩 案例参考官方文档。 二、生成自动索引报告
使用REPORT_ACTIVITY和REPORT_LAST_ACTIVITY函数生成自动索引操作相关的报告。 dbms_auto_index.report_activity返回在数据库中特定期间执行的自动索引操作的报告。 dbms_auto_index.report_last_activity返回在数据库中执行的最后一次自动索引操作的报告 (1)dbms_auto_index.report_activity使用默认配置生成报告默认是过去24小时自动索引操作的信息以纯文本格式text生成 SQL declare report clob : null; begin report : DBMS_AUTO_INDEX.REPORT_ACTIVITY(); end; / SQL SELECT DBMS_AUTO_INDEX.report_activity( type HTML) ---可以是html、text、xml。默认值是text FROM dual; ---查看默认24小时文本格式的HTML报告 (2)dbms_auto_index.report_activity指定时间间隔、报告类型、级别、内容生成报告 SQL declare report clob : null; begin report : DBMS_AUTO_INDEX.REPORT_ACTIVITY( activity_start TO_TIMESTAMP(2018-11-01, YYYY-MM-DD), ---开始时间 activity_end TO_TIMESTAMP(2018-12-01, YYYY-MM-DD), ---结束时间 type HTML, ---报告类型默认值是TEXT section SUMMARY, ---报告内容 level BASIC); ---报告级别 end; / SQL SELECT DBMS_AUTO_INDEX.report_activity( activity_start SYSTIMESTAMP-2, activity_end SYSTIMESTAMP-1, type HTML) ---可以是html、text、xml。默认值是text FROM dual; ---查看前一天的THML格式的报告 (3)dbms_auto_index.report_last_activity生成上一次自动索引操作的报告 SQL declare report clob : null; begin report : DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY(); end; / SQL SELECT DBMS_AUTO_INDEX.report_last_activity( type HTML) ---可以是html、text、xml。默认值是text FROM dual; ---查看最后一次自动索引操作的HTML报告 (4)dbms_auto_index.report_last_activity生成包含有关上一次自动索引操作的信息 SQL declare report clob : null; begin report : DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY( type HTML, section SUMMARY INDEX_DETAILS ERRORS, level BASIC); end; / SQL SELECT DBMS_AUTO_INDEX.report_last_activity( type HTML, ---可以是html、text、xml。默认值是text section ALL, LEVEL ALL) FROM dual; ---查看最后一次自动索引的html报告 案例开始库级别开启自动索引19c
119c使用自动索引开启参数19c需要设置参数才能使用DBMS_AUTO_INDEX包不然报ORA-40216: feature not supported需要重启生效看19c之后的版本支不支持不设置参数就可以使用自动索引功能 SQL alter system set _exadata_feature_ontrue scopespfile; ---19c版本中此功能仅限于企业版。通过设置初始化参数“_exadata_feature_ontrue”进行测试 2开启库级别的自动索引 默认情况下如果启用了自动索引那么数据库中的所有用户都可以使用自动索引。 SQL EXEC DBMS_AUTO_INDEX.CONFIGURE(AUTO_INDEX_MODE,IMPLEMENT); 3查看库中是否有自动索引和自动索引的配置 SQL select * from dba_indexes where autoYES; ---查看库中自动创建索引的索引 SQL select s.NAME, t.PARAMETER_NAME, t.PARAMETER_VALUE, t.LAST_MODIFIED, t.MODIFIED_BY from cdb_auto_index_config t, v$containers s where t.CON_ID s.CON_ID; ---cdb模式下会列出每个pdb的配置情况 AUTO_INDEX_SCHEMA要包含或排除使用自动索引的用户。 AUTO_INDEX_DEFAULT_TABLESPACE用于存储自动索引的表空间。默认值是NULL这意味着在创建数据库期间指定的默认永久表空间用于存储自动索引。 AUTO_INDEX_SPACE_BUDGET空间大小用于自动索引的百分比 AUTO_INDEX_RETENTION_FOR_AUTO在数据库中保留未使用的自动索引的天数然后删除它们。默认值为373天。 AUTO_INDEX_RETENTION_FOR_MANUAL在数据库中保留未使用的手动创建的索引(非自动索引)的天数然后删除它们。当它被设置为NULL自动索引过程不会删除手动创建的索引。默认值是NULL.。 AUTO_INDEX_MODE自动索引的操作方式。 IMPLEMENT在此模式下新的自动索引创建为可见索引和任何现有的看不见自动索引也被设置为可见索引。在这种模式下可以在SQL语句中使用自动索引。 REPORT ONLY在此模式下新的自动索引创建为看不见索引不能在SQL语句中使用。 OFF将模式设置为OFF防止考虑和创建新的自动索引。但是它并不禁用现有的自动索引。AUTO_INDEX_COMPRESSION自动索引的高级索引压缩。默认值是OFF. AUTO_INDEX_REPORT_RETENTION在删除自动索引日志之前在数据库中保留这些日志的天数。由于自动索引报告是根据这些日志生成的因此不能在指定的值以外的时间内生成自动索引报告。AUTO_INDEX_REPORT_RETENTION。默认值为373天。 4执行一条全表扫的sql耗时31秒 数据库中首次运行的SQL语句不能使用自动索引原因是Oracle无法对该SQL语句做出有效的判断。所以多执行几次 SQL select b.logtime, a.name, a.cardid, b.amount, b.goods, b.score from itpux_member a,itpux_sales b where a.region 省份 and a.recommend lf and b.logtime to_date(2016-12-15, yyyy-mm-dd) and a.cardid b.cardid order by b.logtime; SQL select * from dba_indexes where autoYES; ---自动创建了SYS_AI_3cymkgsdnarfp的自动索引 SQL select b.logtime, a.name, a.cardid, b.amount, b.goods, b.score from itpux_member a,itpux_sales b where a.region 省份 and a.recommend lf and b.logtime to_date(2016-12-15, yyyy-mm-dd) and a.cardid b.cardid order by b.logtime; 执行时间降到0.01秒速度快了N倍 5生成自动索引报告 SQL declare report clob : null; begin report : DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY(); end; / SQL SELECT DBMS_AUTO_INDEX.report_last_activity( type HTML) ---可以是html、text、xml。默认值是text FROM dual; ---查看最后一次自动索引操作的HTML报告 6自动索引的删除策略19c版本中不能手动删除删除 SQL drop index SYS_AI_3cymkgsdnarfp; ---自动创建的自动索引不能被删除掉。只能等到参数AUTO_INDEX_RETENTION_FOR_AUTO设置的时间到了之后未使用的自动索引在指定的保留期后被删除。默认情况下373天后将删除未使用的自动索引。
