About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc
全部博文(172)
分类: Oracle
2020-06-18 09:01:58
结构优良的SQL能够更易被CBO理解,从而更好地进行查询转换操作,从而为后续生成最佳执行计划打下基础,然后实际应用过程中,因为不注重SQL写法,导致CBO也无能为力。下面以分页写法案例作为探讨。
低效分页写法:
原写法最内层根据use_date等条件查询,然后排序,获取rownum并取别名,最外层使用rn规律。问题在哪?
分页写法如果直接<,<=可在排序后直接rownum获取(两层嵌套),如果需要获取区间值,在最外层获取>,>=(三层嵌套)。
此语句获取<=,而使用三层嵌套,导致无法使用分页查询STOPKEY算法,因为rownum会阻止谓词推入,导致外层的rn<=20无法推入到第二层,因此执行计划中没有STOPKEY操作。
<=分页只需要2层嵌套,done_date列有索引,根据条件done_date>to_date(‘20150916’,‘YYYYMMDD’)和只获取前20行,可高效利用索引和STOPKEY算法,改写完成后使用索引降序扫描,执行时间从1.72s到0.01s,逻辑IO 从42648到59,具体如下:
高效分页写法应该符合规范,并且能够充分利用索引消除排序。
CBO
BUG出现比较多的就是在查询转换中,一旦出现BUG,可能查找就比较困难,这时候应该通过分析10053或者通过使用SQLT XPLORE快速找到问题根源。如下例:
这个表的oper_type有索引,并且条件oper_type>’D’
or oper_type<’D’走索引较好,但是实际上ORACLE却走了全表扫描,通过SQLT XPLORE快速分析:
其中上面2个是走索引的执行计划,点进去:
很显然,_fix_control=8275054很可疑,通过查询MOS:
转换成a<>b,很显然使用不了索引了,可以通过关闭此8275054解决。
未完待续,见PART8: