[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分钟上下,应该引以为戒.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2213258/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2213258/