Oracle SQL语句解析过长问题
问题描述:
有一个SQL语句解析非常缓慢,但是没有解析出来。只是偶尔出现,当第一次解析不出来时,后续的语句都会在等待。期间大量的library cache等待。
类似语句如下:其中视图中包含了非常复杂的join。导致解析时一直不能成功。
点击(此处)折叠或打开
- SELECT * FROM PROD_VW WHERE PRD_ID = :1
解决方法:
将参数_optimizer_max_permutations从2000修改到500,optimizer_max_permutations参数定义了CBO所考虑的表连接的最大数目的上限。降低该值为了缩短SQL解析所考虑的最大次数。
问题分析:
通过在测试库对该SQL运行,虽然没有在生产库那么慢,比起其他SQL解析还是相当慢。抓起对应SQL的10046和truss信息,发现最多的时间是在mmap,分配内存。
点击(此处)折叠或打开
- truss -clafep 12154
- syscall seconds calls errors
- _exit .000 1
- read .000 10
- write .000 3
- open .000 2
- close .000 9
- chmod .000 1
- stat .001 24
- lseek .000 6
- getpid .000 6
- times .193 6963
- shmdt .002 3
- fcntl .000 2
- lstat .000 5
- sigaction .000 21
- mmap .438 2016
- munmap .260 20
- getrlimit .000 4
- sysconfig .000 2
- yield .054 725
- lwp_sigmask .000 2
- -------- ------ ----
- sys totals: .953 9825 0
- usr time: 40.795
- elapsed: 76.600
经过Oracle support确认和Cost-Based Subquery Unnesting(SU)有关。Oracle的优化器,会将复杂的子查询转换成视图,放在查询块中。可能是由于统计信息不准确,在转换的过程中发生了问题,导致解析时间过长。这个问题可以通过设置隐含参数_unnest_subquery=false解决,并且要求我们收集最新的统计信息后,再次测试SQL。
++ The cost based subquery unnesting happened for the query block which converts complex subquery into view.
++ Upon unnesting, the view does not get merged due to failed checks and thus predicate is pushed into main query causing the issue.
++ Thus, the culprit here is with the unnesting of complex subquery due to cost based transformation.
Setting _unnest_subquery=false resolves the issue.