ITPub博客

首页 > 数据库 > Oracle > Oracle SQL语句解析过长问题

Oracle SQL语句解析过长问题

原创 Oracle 作者:dbs101 时间:2017-01-11 15:04:54 0 删除 编辑

问题描述:

有一个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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Redis CPU高问题
请登录后发表评论 登录
全部评论

注册时间:2010-12-18

  • 博文量
    92
  • 访问量
    439159