Oracle之不可见索引


Oracle之不可见索引

 BLOG文档结构图

wps2EA1.tmp[4] 

 前言部分

2.1  导读和注意事项

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~

① Oracle不可见索引的使用

  Tips:

① 本文在ITpubhttp://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和微信公众号(xiaomaimiaolhr)有同步更新

② 文章中用到的所有代码,相关软件,相关资料请前往小麦苗的云盘下载(http://blog.itpub.net/26736162/viewspace-1624453/

③ 若文章代码格式有错乱,推荐使用搜狗360或QQ浏览器,也可以下载pdf格式的文档来查看,pdf文档下载地址:http://blog.itpub.net/26736162/viewspace-1624453/,另外itpub格式显示有问题,可以去博客园地址阅读

④ 本篇BLOG中命令的输出部分需要特别关注的地方我都用灰色背景和粉红色字体来表示,比如下边的例子中,thread 1的最大归档日志号为33thread 2的最大归档日志号为43是需要特别关注的地方;而命令一般使用黄色背景和红色字体注;对代码或代码输出部分的注释一般采用蓝色字体表示

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

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

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

 

[ZHLHRDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

 

====》2097152*512/1024/1024/1024=1G 

 

本文如有错误或不完善的地方请大家多多指正,ITPUB留言或QQ皆可,您的批评指正是我写作的最大动力。

 

 

2.2  相关参考文章连接

 

Oracle之虚拟索引http://blog.itpub.net/26736162/viewspace-2123687/

Oracle索引的监控:http://blog.itpub.net/26736162/viewspace-2120752/

oracle 如何预估将要创建的索引的大小:http://blog.itpub.net/26736162/viewspace-1381160/

 

 

 

 不可见索引(Invisible Indexes)

您常常感到疑惑,索引是否真的有利于用户的查询?它可能有利于一个查询,但会影响 10 个其他查询。索引肯定会对 INSERT 语句造成负面影响,也会执行潜在的删除和更新操作,这取决于WHERE 条件是否在索引中包括该列。一个相关的问题是,使用索引时,如果该索引被删除,会对查询性能造成什么影响?当然,您可以删除索引并查看对查询的影响,但说起来容易做起来难。索引实际上如何有助于查询?您必须重新定义索引,为此,需要进行重新创建。完全重新创建之后,就没有人能使用它了。重新创建索引也是一个昂贵的过程;它会占用许多有用的数据库资源。

我们经常在数据库上建索引或删除索引,由于索引对SQL的执行性能影响非常大,有可能变得很好,也有可能变得很差,在线下开发环境我们可以充分测试,对于创建或删除索引没什么问题。但是在线上环境,由于高并发的访问,如果我们删除了一个重要的大索引(GB以上),删除后才发现大量SQL性能变差,很快主机就LOAD飙升,系统无法运行了,由于索引已经删除,并且很大,要当场重建基本不可能,因为这个索引巨大,创建估计要几分钟甚至几个小时,况且这时主机已经基本没有响应,IO全部用光,只能把应用停了,等索引建好后再开始打开应用,等发生这样的事才会为自己的失误而后悔。那我们有没有办法让删除索引的风险降低呢,答案是有!即本文介绍的不可见索引。

索引维护是DBA的一项重要工作。当一个系统运行很长一段时间,经过需求变更、结构设计变化后,系统中就可能会存在一些不会被使用的索引,或者使用效率很低的索引。这些索引的存在,不仅占用系统空间,而且会降低事务效率,增加系统的waits。因此,我们需要找出那些无用或低效索引的索引并删除它们(找出无用索引可以通过索引监控的方法)。但是,直接删除索引还是存在一定风险的。例如,某些索引可能只是在一些周期的作业中被使用到,而如果监控周期没有覆盖到这些作业的触发点,就会认为索引是无用的而被删除。当作业启动后,可能就会对系统性能造成冲击。这时,可能就会手忙脚乱的去找回索引定义语句、重建索引。11G之前,我们可以先不删除索引,而将其修改为unusable。这样的话,索引的定义并未删除,只是索引不能再被使用也不会随着表数据的更新而更新。当需要重新使用该索引时,需要用rebuild语句重建、然后更新统计信息。对于一些大表来说,这个时间可能就非常长。在11g里,Oracle 提供了一个新的特性来降低直接删除索引或者禁用索引的风险,那就是索引不可见(Index Invisible)。

从版本11g 开始,可以创建不可见的索引。优化程序会忽略不可见的索引,除非在会话或系统级别上将 OPTIMIZER_USE_INVISIBLE_INDEXES初始化参数显式设置为TRUE此参数的默认值是FALSE

使索引不可见是使索引不可用或删除索引的一种替代办法。使用不可见的索引,可完成以下操作:

(1) 在删除索引之前测试对索引的删除。

(2) 对应用程序的特定操作或模块使用临时索引结构,这样就不会影响整个应用程序。

注意:与不可用的索引不同,不可见的索引在使用DML 语句期间仍会得到维护。

wps2EA2.tmp[4]

当索引不可见时,优化程序生成的计划不会使用该索引。如果未发现性能下降,则可以删除该索引。还可以创建最初不可见的索引,执行测试,然后确定是否使该索引可见。

可以查询*_INDEXES 数据字典视图的VISIBILITY 列来确定该索引是VISIBLE 还是INVISIBLE

SQL> SELECT VISIBILITY FROM DBA_INDEXES WHERE INDEX_NAME='IDX_ID';

VISIBILIT

---------

VISIBLE

--创建不可见索引:

CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME) INVISIBLE;

--修改索引是否可见:

ALTER INDEX INDEX_NAME INVISIBLE;

ALTER INDEX INDEX_NAME VISIBLE;

 

特点总结:

1、当索引变更为不可见的时候,只是对oracle的优化器不可见。

2、不可见索引在DML操作的时候也会被维护。

3、加HNIT对不可见索引无效。

4、可以通过修改system级别和session级别参数来使用不可见索引。

 

3.1  我的示例

创建表,不可见索引,并收集统计信息:

SYS@lhrdb> CREATE TABLE T_II_20160819_01_LHR AS SELECT * FROM DBA_OBJECTS;

 

Table created.

 

SYS@lhrdb> CREATE INDEX IDX_II_20160819 ON T_II_20160819_01_LHR(OBJECT_ID) INVISIBLE;

 

Index created.

 

 

SYS@lhrdb> SELECT VISIBILITY FROM DBA_INDEXES WHERE INDEX_NAME='IDX_II_20160819';

 

VISIBILIT

---------

INVISIBLE

 

SYS@lhrdb> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>USER,TABNAME=>'T_II_20160819_01_LHR',DEGREE=>2,CASCADE => TRUE);

 

PL/SQL procedure successfully completed.

 

 

--带where条件查询:

SYS@lhrdb> SHOW PARAMETER OPTIMIZER_USE_INVISIBLE_INDEXES

 

NAME                                 TYPE        VALUE

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

optimizer_use_invisible_indexes      boolean     FALSE

SYS@lhrdb> set line 9999

SYS@lhrdb> set autot traceonly exp

SYS@lhrdb> SELECT * FROM T_II_20160819_01_LHR WHERE OBJECT_ID=1;

 

Execution Plan

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

Plan hash value: 700947541

 

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

| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |                      |     1 |    98 |   343   (2)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| T_II_20160819_01_LHR |     1 |    98 |   343   (2)| 00:00:05 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("OBJECT_ID"=1)

 

--这里使用了全表扫描,根据唯一性,这里应该走索引的,我们加上hint试试

 

SYS@lhrdb> SELECT /*+index(T,IDX_II_20160819)*/ * FROM T_II_20160819_01_LHR T WHERE OBJECT_ID=1;

 

Execution Plan

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

Plan hash value: 700947541

 

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

| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |                      |     1 |    98 |   343   (2)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| T_II_20160819_01_LHR |     1 |    98 |   343   (2)| 00:00:05 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("OBJECT_ID"=1)

 

--对于invisibleindex,使用hint也没有用。

--修改OPTIMIZER_USE_INVISIBLE_INDEXES参数TRUE,再次查询:

SYS@lhrdb> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;

 

Session altered.

 

SYS@lhrdb> SELECT * FROM T_II_20160819_01_LHR WHERE OBJECT_ID=1;

 

Execution Plan

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

Plan hash value: 2544197461

 

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

| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                      |     1 |    98 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T_II_20160819_01_LHR |     1 |    98 |     2   (0)| 00:00:01 |

|*  2 |  INDEX RANGE SCAN          | IDX_II_20160819      |     1 |       |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - access("OBJECT_ID"=1)

 

--这次使用了索引。关闭 OPTIMIZER_USE_INVISIBLE_INDEXES参数,将索引改成visible测试:

SYS@lhrdb> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;

 

Session altered.

 

SYS@lhrdb> ALTER INDEX IDX_II_20160819 VISIBLE;

 

Index altered.

 

SYS@lhrdb> SELECT * FROM T_II_20160819_01_LHR WHERE OBJECT_ID=1;

 

Execution Plan

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

Plan hash value: 2544197461

 

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

| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                      |     1 |    98 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T_II_20160819_01_LHR |     1 |    98 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_II_20160819      |     1 |       |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - access("OBJECT_ID"=1)

 

--这次又正常了。

以上是Oracle11g的处理方法,但是在Oracle9iOracle10g中索引没有invisible的功能,我们如何处理呢?现在Oracle数据库一般都采用基于成本的计算方法来生成执行计划,只要索引的成本更低,ORACLE就会选择使用索引,OK,那我们只要告诉ORACLE使用这个索引成本很高,它就不会使用这个索引,这样就达到了暂时让索引不可用的效果。相信很多人都知道ORACLE提供了dbms_stats包来管理对像的统计信息,通过DBMS_STATS.SET_INDEX_STATS函数我们可以强制设置统计信息,现在我们只要把索引的成本设置成非常大即可,如下所示

--设置非常离谱的统计信息,让ORACLE认为使用索引的成本很高

SYS@lhrdb> SELECT A.OWNER,A.INDEX_NAME,A.BLEVEL,A.LEAF_BLOCKS,A.NUM_ROWS FROM DBA_INDEXES A WHERE INDEX_NAME='IDX_II_20160819';

 

OWNER                          INDEX_NAME                         BLEVEL LEAF_BLOCKS   NUM_ROWS

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

SYS                            IDX_II_20160819                         1         193      87133

SYS@lhrdb> EXEC DBMS_STATS.SET_INDEX_STATS(OWNNAME => user,INDNAME => 'IDX_II_20160819',INDLEVEL => 10,NUMLBLKS => 1000000000,NUMROWS => 100000000000,NO_INVALIDATE => FALSE );

 

PL/SQL procedure successfully completed.

SYS@lhrdb> col NUM_ROWS format 999999999999999

SYS@lhrdb> SELECT A.OWNER,A.INDEX_NAME,A.BLEVEL,A.LEAF_BLOCKS,A.NUM_ROWS FROM DBA_INDEXES A WHERE INDEX_NAME='IDX_II_20160819';

 

OWNER                          INDEX_NAME                         BLEVEL LEAF_BLOCKS         NUM_ROWS

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

SYS                            IDX_II_20160819                        10  1000000000     100000000000

no_invalidate=false表示让CACHE中的执行计划立即失效,重新按现在的统计信息生成SQL执行计划。验证一下是否生效

SYS@lhrdb> set autot on9

SYS@lhrdb> SELECT * FROM T_II_20160819_01_LHR WHERE OBJECT_ID=1;

 

no rows selected

 

 

Execution Plan

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

Plan hash value: 700947541

 

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

| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |                      |     1 |    98 |   343   (2)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| T_II_20160819_01_LHR |     1 |    98 |   343   (2)| 00:00:05 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("OBJECT_ID"=1)

 

 

Statistics

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

          0  recursive calls

          0  db block gets

       2491  consistent gets

          0  physical reads

          0  redo size

       1343  bytes sent via SQL*Net to client

        509  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          0  rows processed

 

 

3.2  OCP

An index called ORD_CUSTNAME_IX has been created on the CUSTNAME column in the ORDERS table using the following command:

SQL>CREATE INDEX ord_custname_ix ON orders(custname);

The ORDERS table is frequently queried using the CUSTNAME column in the WHERE clause.You want to check the impact on the performance of the queries if the index is not available.You do not want the index to be dropped or rebuilt to perform this test.

Which is the most efficient method of performing this task?

A.disabling the index

B.making the index invisible

C.making the index unusable

D.using the MONITORING USAGE clause for the index

Answer:B

答案解析:题目要求在不能删除和重建的情况下来测试索引的性能

对于选项A,索引不能被禁用。所以,选项A错误。

对于选项B,让索引不可用,为正确选项。所以,选项B正确。

对于选项C,让索引不可用之后还是得重建索引。所以,选项C错误。

对于选项D,监控索引并不能测试索引在不可用的情况下对系统的性能影响。所以,选项D错误。

所以,本题的答案为B

 

  About Me

..........................................................................................................................................................................................................                        

● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

● 本文在ITpub(http://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● QQ群:230161599 微信群:私聊

● 本文itpub地址:http://blog.itpub.net/26736162/viewspace-2124044/ 本文博客园地址:http://www.cnblogs.com/lhrbest/articles/5808049.html

● 本文pdf版:http://yunpan.cn/cdEQedhCs2kFz (提取码:ed9b)

● 小麦苗分享的其它资料:http://blog.itpub.net/26736162/viewspace-1624453/

● 联系我请加QQ好友(642808185),注明添加缘由

●  2016-08-19 09:00~ 2016-08-19 19:00 在中行完成

● 【版权所有,文章允许转载,但须以链接方式注明源地址,否则追究法律责任】

..........................................................................................................................................................................................................

长按识别二维码或微信客户端扫描下边的二维码来关注小麦苗的微信公众号:xiaomaimiaolhr,学习最实用的数据库技术。

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。
【故障处理】序列 cache 值过小导致 CPU 利用率过高 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ① enq: SQ - contention 等待事件的解决 ② 一般等待事件的解决办法 ③  DFS lock handle 等待事件 ④ 与序列有关的等待事件   Tips: ① 本文在 ITpub ( http://blog.itpub.
这两天做性能测试碰见一个问题,比较有意思。 一条SQL,使用了绑定变量,查看V$SQLAREA发现version_count是2,  查看V$SQL,发现有两条记录,分别对应了0和1两个child cursor:  再查看这两个child cursor对应的执行计划:  child cursor:0 -----------------------------------------------------------------------------------------------------| I

OCP如何查看历史成绩(2) - 2016-08-25 14:08:08

OCP如何查看历史成绩( 2 ) 之前写过一篇文章 OCP如何查看历史成绩: http://blog.itpub.net/26736162/viewspace-1624996/  , 但是现在界面好像有所变动,而且有的连接也找不见了,今天有朋友问如何查询历史的成绩,晚上小麦苗就抽了点时间来找找,顺便记录下来。最近心情不好,不想写技术文章了。       首先登陆网址: https://education.oracle.com/ https://education.oracle.com/pls/eval-e
下午的时候收到这么一条报警。 ZABBIX- 监控系统 : ------------------------------------ 报警内容 : Too many parallel sessions on xxxxx_ xx机房 _xxxxx ------------------------------------ 报警级别 : PROBLEM ------------------------------------ 监控项目 : parallel_session_cnt : 66 -----------

【MySQL】Tokudb安装测试初探 - 2016-08-24 14:08:02

一 前言    TokuDB 是一个高性能、支持MVCC的MySQL 和 MariaDB 的存储引擎。TokuDB 的主要特点是数据压缩功能出色,对高写压力的支持,由美国TokuTek公司(http://www.tokutek.com/) 研发,该公司于2015年4月份被Percona收购,理所当然地提供了TokuDB版本的Percona Server。本文使用Peronca server 5.6.30 版本进行测试安装。 二 安装前的准备     请参考官方文档 Percona Server YUM源

通过shell脚本添加备库日志 - 2016-08-21 17:08:14

今天下午的时候,准备顺手写一个简单的脚本,但是发现很多事情较真起来真是寸步难行。在写脚本的过程中碰到了太多的问题,很多时候感觉像要实现的功能更通用,就得做更多的检查,更多的校验也就意味着有更多的预先条件,这些条件里面有些是规范和建议,有些是按照已有的配置情况,尽管如此,自己感觉还是缺少了太多的检查。   先来说说今天尝试的简单脚本,就是给主库添加standby logfile,这个需求听起来非常简单,都甚至在我的半自动化脚本中隐去了,但是把这个需求要落到纸面上来,简直了。 首先这个需求会涉及到下面的几个数据
今天在测试时遇到一个ORA-38706ORA-38707报错,乍一看到报错内容竟然没有回过神儿来。 ORA-38706ORA-38707报错 点击( 此处 )折叠或打开 SYS @ HOEGH select banner from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition R

Oracle之虚拟索引 - 2016-08-21 14:08:07

Oracle 之虚拟索引 1    BLOG 文档结构图     2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ①  Oracle 虚拟索引的使用   Tips: ① 本文在 ITpub ( http://blog.itpub.net/26736162 )、博客园 ( http://www.cnblogs.com/lhrbest )和微信公众号( xiaomaimiaolhr )有同步更新 ②

MySQL 事务提交过程 - 2016-08-18 17:08:13

开发老大要求通过binlog查询一条被修改的数据,数据被查出后问我,有没有可能binlog中不会记录,回答不会,因为数据被修改,若失败直接回滚,不会在binlog中记录,此刻一个朋友用了洪荒之力告诉我,失败的话也会记录,坐地无语,因为他sqlserver dba,用sqlserver的思维考虑mysql,哈哈哈哈哈,用实验让他闭嘴! 简单测试步骤如下: root(yoon) flush logs; Query OK, 0 rows affected (0.01 sec) root((none)) show

Data Guard跳归档恢复的案例 - 2016-08-18 14:08:08

自前些天写了一个脚本之后,今天特意测试了一下,没想到一下子发现了一个大问题。有一套一主两备的10gR2环境,一个异机备库一直在READ ONLY状态,也就意味着数据库在打开之后一直忘了恢复应用归档,然后在某一天发现时,已经延迟了好几个月。无论怎样,还得庆幸发现了这个问题。 目前来看一种行之有效的方法就是重搭备库,但是这种修复方式需要大量的磁盘空间,而且需要恢复的时间较长,怎么改进呢,可以考虑通过基于SCN的增量备份来跳归档恢复。目前的环境是一主两备,再怎么改进呢,我们可以基于备库1来完成基于SCN的增量备份