[20180830]工作中一次失误.txt

--//记录工作中1次失误,做一个简单记录.
--//优化1条sql语句,参考链接: http://blog.itpub.net/267265/viewspace-2213256/
--//语句如下:
sql_id='crzs1c9pnjqg2'

SELECT XXXXXX_YYY.EMR_BL03.*, XXXXXX_YYY.EMR_BL_BL01.BLMC
  FROM XXXXXX_YYY.EMR_BL03
  LEFT JOIN XXXXXX_YYY.EMR_BL_BL01
    ON XXXXXX_YYY.EMR_BL03.BLBH    = XXXXXX_YYY.EMR_BL_BL01.BLBH
 WHERE XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441';

--//执行计划如下:
> @ &r/dpcawr crzs1c9pnjqg2 ''
PLAN_TABLE_OUTPUT
--------------------
SQL_ID crzs1c9pnjqg2
--------------------
SELECT XXXXXX_YYY.EMR_BL03.*,XXXXXX_YYY.EMR_BL_BL01.BLMC FROM
XXXXXX_YYY.EMR_BL03  LEFT JOIN XXXXXX_YYY.EMR_BL_BL01 ON
XXXXXX_YYY.EMR_BL03.BLBH=XXXXXX_YYY.EMR_BL_BL01.BLBH WHERE
XXXXXX_YYY.EMR_BL_BL01.BRBH = '00366441'
Plan hash value: 40434530
----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                         |        |       |   215K(100)|          |
|   1 |  HASH JOIN                    |                         |     19 | 27645 |   215K  (1)| 00:43:02 |
|   2 |   JOIN FILTER CREATE          | :BF0000                 |     19 |   817 |    16   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMR_BL_BL01             |     19 |   817 |    16   (0)| 00:00:01 |
|   4 |     INDEX RANGE SCAN          | I_EMR_BL_BL01_BRBH_CJSJ |     19 |       |     3   (0)| 00:00:01 |
|   5 |   JOIN FILTER USE             | :BF0000                 |   3968K|  5343M|   215K  (1)| 00:43:01 |
|   6 |    TABLE ACCESS STORAGE FULL  | EMR_BL03                |   3968K|  5343M|   215K  (1)| 00:43:01 |
----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$C8875FE2
   3 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1
   4 - SEL$C8875FE2 / EMR_BL_BL01@SEL$1
   6 - SEL$C8875FE2 / EMR_BL03@SEL$2
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
35 rows selected.

--//EMR_BL03存在索引IDX_EMR_BL03_BLBH.字段包括ZYMZ, BLBH, WDLX.不知道为什么没有选择index skip scan.
--//实际上优化很简单,在表EMR_BL03上建立BLBH字段的索引.
CREATE INDEX XXXXXX_YYY.I_EMR_BL03_BLBH ON XXXXXX_YYY.EMR_BL03 (BLBH) LOGGING TABLESPACE XXXXXX_YYY;

--//索引建立完成后,一切ok,实际上索引IDX_EMR_BL03_BLBH(字段包括ZYMZ, BLBH, WDLX)变得无用,BLBH具有很好的选择性.
--//ZYMZ仅仅存在3个值,这个索引应该删除.

--//我当时并没有删除该索引,而是想测试选择IDX_EMR_BL03_BLBH索引,执行计划是否可以选择index skip scan.
--//我尝试许多提示
/*+ cardinality(EMR_BL_BL01 1) */
/*+ INDEX_SS(EMR_BL03 IDX_EMR_BL03_BLBH) */

--//我发现第2种方式在BLBH索引存在的情况下不会起作用.于是我执行如下:
--//ALTER INDEX XXXXXX_YYY.I_EMR_BL03_BLBH             INVISIBLE;
--//ALTER INDEX XXXXXX_YYY.IDX_EMR_BL03_BLBH             VISIBLE;

--//实际上就在我这些来回折腾的时候时,最终还是无法理解为什么oracle不选择index skip scan.
--//放弃探究还原时,我想修改回来,不小心2个索引属性都设置为INVISIBLE.大致过程如下:

1.修改IDX_EMR_BL03_BLBH属性INVISIBLE:
ALTER INDEX XXXXXX_YYY.IDX_EMR_BL03_BLBH INVISIBLE;

--//这样导致2个索引属性都是INVISIBLE.

2.修改I_EMR_BL03_BLBH属性VISIBLE:
ALTER INDEX XXXXXX_YYY.I_EMR_BL03_BLBH VISIBLE;

--//就在这个时候出现ora-00054错误.ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
--//当然问题很简单,是应用出现阻塞,只是我当时没注意.
--//我当时并没有仔细看,以为存在某个事务没有提交,不断尝试执行.依旧报ora-00054错误.

--//也就是这时类似前面的语句大量执行,正好是写病例的时间段,执行该语句的用户非常慢.
--//更要命是我把前面的语句移到sqlplus下执行,这个时候又写错,如下:
ALTER INDEX XXXXXX_YYY.XXXXXX_YYY.I_EMR_BL03_BLBH  VISIBLE;
--//owner写了2遍.有没有仔细看屏幕的错误输出与原来不一样了.

--//我的做法kill掉全部当前正在执行前面类似语句的用户.然后修改索引属性.借助toad写出如下语句:

SELECT    'alter system kill session '''
       || sid
       || ','
       || serial#
       || ',@'
       || inst_id
       || ''' immediate'
          c80
  FROM (SELECT se.inst_id
              ,lk.SID
              ,se.serial#
              ,se.username
              ,se.OSUser
              ,se.Machine
              ,DECODE
               (
                  lk.TYPE
                 ,'TX', 'Transaction'
                 ,'TM', 'DML'
                 ,'UL', 'PL/SQL User Lock'
                 ,lk.TYPE
               )
                  lock_type
              ,DECODE
               (
                  lk.lmode
                 ,0, 'None'
                 ,1, 'Null'
                 ,2, 'Row-S (SS)'
                 ,3, 'Row-X (SX)'
                 ,4, 'Share'
                 ,5, 'S/Row-X (SSX)'
                 ,6, 'Exclusive'
                 ,TO_CHAR (lk.lmode)
               )
                  mode_held
              ,DECODE
               (
                  lk.request
                 ,0, 'None'
                 ,1, 'Null'
                 ,2, 'Row-S (SS)'
                 ,3, 'Row-X (SX)'
                 ,4, 'Share'
                 ,5, 'S/Row-X (SSX)'
                 ,6, 'Exclusive'
                 ,TO_CHAR (lk.request)
               )
                  mode_requested
              ,TO_CHAR (lk.id1) lock_id1
              ,TO_CHAR (lk.id2) lock_id2
              ,ob.owner
              ,ob.object_type
              ,ob.object_name
              ,DECODE (lk.Block,  0, 'No',  1, 'Yes',  2, 'Global') block
              ,se.lockwait
          FROM GV$lock lk, dba_objects ob, GV$session se
         WHERE     lk.TYPE IN ('TX', 'TM', 'UL')
               AND lk.SID = se.SID
               AND lk.id1 = ob.object_id(+)
               AND lk.inst_id = se.inst_id
               AND object_name = 'EMR_BL03')
  union all
  select 'ALTER INDEX XXXXXX_YYY.I_EMR_BL03_BLBH  VISIBLE;' from dual
  ;

--//执行输出内容就ok了.实际上当时脚本ALTER INDEX XXXXXX_YYY.I_EMR_BL03_BLBH  VISIBLE;还是写错的.
--//写成了ALTER INDEX XXXXXX_YYY.XXXXXX_YYY.I_EMR_BL03_BLBH  VISIBLE;

--//实际上在取消阻塞后,ALTER INDEX XXXXXX_YYY.I_EMR_BL03_BLBH  VISIBLE;就ok了.
--//当然最后我还是kill掉全部当前正在执行前面类似语句的用户.因为这些执行很慢.

--//另外当时还有一种方式估计也是可行的,就是修改参数

alter system set OPTIMIZER_USE_INVISIBLE_INDEXES=true scope=memory;

--//这样新进入的用户可以很快运行.

总结一下:
我个人错误在于:把2个索引属性设置为INVISIBLE.而这个时候因为业务的问题,可能无法设置需要的索引为VISIBLE.
对于这个例子,应该先设置2个索引属性设置为VISIBLE,然后在设置无需要的索性属性为VISIBLE.
当然事后看了我当时在测试前执行的:
--//ALTER INDEX XXXXXX_YYY.I_EMR_BL03_BLBH             INVISIBLE;
--//ALTER INDEX XXXXXX_YYY.IDX_EMR_BL03_BLBH             VISIBLE;

--//就已经埋下祸根,因为这样导致前面的语句选择全表扫描EMR_BL03,走direct path read.
--//还有一个想法也影响了我的判断,我开始以为前面的语句是某个开发随手写的sql语句.实际上是开发没有使用绑定变量.
--//而且当时觉得奇怪的是awr报表,SQL Module是空.也是让我感到奇怪的地方.

User I/O Time (s) Executions  UIO per Exec (s) %Total Elapsed Time (s) %CPU  %IO   SQL Id        SQL Module SQL Text
...
            49.51          1         49.51       1.78           54.27  9.63  91.22 crzs1c9pnjqg2            SELECT XXXXXX_YYY.EMR_BL03.*, ...

--//还有当时的我还错误的认为IDX_EMR_BL03_BLBH有用的.实际上这个索引根本没用.
--//唯一感到欣慰的是,就算我把2个索引属性设置为INVISIBLE,这个问题就一直存在的.
--//最终影响业务大约30分钟上下,应该引以为戒.