Oracle SQL语句解析过长问题

问题描述：

有一个SQL语句解析非常缓慢，但是没有解析出来。只是偶尔出现，当第一次解析不出来时，后续的语句都会在等待。期间大量的library cache等待。

类似语句如下：其中视图中包含了非常复杂的join。导致解析时一直不能成功。

点击(此处)折叠或打开

1. SELECT * FROM PROD_VW WHERE PRD_ID = :1

解决方法：

将参数_optimizer_max_permutations从2000修改到500，optimizer_max_permutations参数定义了CBO所考虑的表连接的最大数目的上限。降低该值为了缩短SQL解析所考虑的最大次数。

问题分析：

通过在测试库对该SQL运行，虽然没有在生产库那么慢，比起其他SQL解析还是相当慢。抓起对应SQL的10046和truss信息，发现最多的时间是在mmap，分配内存。

点击(此处)折叠或打开

1. truss -clafep 12154
   
2. syscall               seconds   calls  errors
   
3. _exit                    .000       1
   
4. read                     .000      10
   
5. write                    .000       3
   
6. open                     .000       2
   
7. close                    .000       9
   
8. chmod                    .000       1
   
9. stat                     .001      24
   
10. lseek                    .000       6
    
11. getpid                   .000       6
    
12. times                    .193    6963
    
13. shmdt                    .002       3
    
14. fcntl                    .000       2
    
15. lstat                    .000       5
    
16. sigaction                .000      21
    
17. mmap                     .438    2016
    
18. munmap                   .260      20
    
19. getrlimit                .000       4
    
20. sysconfig                .000       2
    
21. yield                    .054     725
    
22. lwp_sigmask              .000       2
    
23.                      --------  ------   ----
    
24. sys totals:              .953    9825      0
    
25. usr time:              40.795
    
26. 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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25105315/viewspace-2132251/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25105315/viewspace-2132251/