[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分鐘上下,應該引以為戒.

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

相關文章