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

来源：这里教程网时间：2026-03-03 11:56:47 作者：

[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分钟上下,应该引以为戒.