Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › SQL语句执行计划问题请教
发新帖
SKYLINE.LIU

0

积分

1

好友

6

主题

[ORA-600/ORA-7445] SQL语句执行计划问题请教

1#
发表于 2013-12-11 20:11:19 | 查看: 5977| 回复: 5
本帖最后由 SKYLINE.LIU 于 2013-12-12 09:48 编辑

数据库版本 11.1.0.7.0

SQL语句
select count(*)
    from xcxt.t_sj_sjjbxx sj
   inner join xcxt.t_sys_organization org
      on org.orgcode = sj.bldwid
   where sj.lrrq >= to_date('2013-11-01', 'yyyy-mm-dd hh24:mi:ss')
     and sj.lrrq < to_date('2013-11-30', 'yyyy-mm-dd hh24:mi:ss')
     and org.treepath like '11000000000000.11010600000000' || '%'

默认使用NESTED LOOPS 方式，逻辑读很大 188731，首次执行时间为6~8s
强制使用HASH JOIN 方式，逻辑读降为 1117，首次执行时间 小于1s

t_sj_sjjbxx  lrrq 每天的数据量 8000~10000左右

表数据量情况
T_SJ_SJJBXX 5550289
T_SYS_ORGANIZATION 3522


组合索引情况
t_sj_sjjbxx  lrrq,bldwid
t_sys_organization  treepath,orgcode
  1. INDEX_NAME        DISTINCT_KEYS        NUM_ROWS        BLEVEL        LEAF_BLOCKS        CLUSTERING_FACTOR
  2. INDEX_SJ_SJJBXX_RQDW        4908281        5526878        2        26028        4734152
  3. CIX_SYS_ORGANIZATION_TREECODE        3522        3522        1        40        444
复制代码
索引列统计信息
  1. TABLE_NAME        COLUMN_NAME        NUM_DISTINCT        NUM_NULLS        NUM_BUCKETS        DENSITY
  2. T_SJ_SJJBXX        BLDWID        575        88460        1        0.00173913043478261
  3. T_SJ_SJJBXX        LRRQ        792962        2020        1        1.26109447867615E-6
  4. T_SYS_ORGANIZATION        ORGCODE        3517        0        1        0.000284333238555587
  5. T_SYS_ORGANIZATION        TREEPATH        3517        0        1        0.000284333238555587
复制代码
获取了语句的10053与10046trace，请各位专家给看看，除了使用Hint 使语句走HASH JOIN 外，还有没有其他办法？

附：
1、建立t_sys_organization  的临时表，创建 treepath,orgcode 的组合索引，使用临时表查询，语句使用 hash join 方式，收集临时表统计信息后，执行计划变为 NESTED LOOPS
2、生产环境不建议使用 hint 呀

HASH_JOIN执行计划.txt

2.49 KB, 下载次数: 578

默认NL执行计划.txt

2.5 KB, 下载次数: 546

sqlhc_nl.html

34.68 KB, 下载次数: 546

sqlhc_hash_join.html

23.1 KB, 下载次数: 538

TRACE文件.rar

38.92 KB, 下载次数: 1366
2#
发表于 2013-12-12 09:30:53
本帖最后由 SKYLINE.LIU 于 2013-12-12 10:41 编辑
  1. SQL> select count(1) from xcxt.t_sys_organization org
  2.   2  where org.treepath like '11000000000000.11010600000000' || '%'
  3.   3  /

  5.   COUNT(1)
  6. ----------
  7.        157

  9. Elapsed: 00:00:00.01
  10. SQL> select count(1) from xcxt.t_sj_sjjbxx sj
  11.   2  where sj.lrrq >= to_date('2013-11-01', 'yyyy-mm-dd hh24:mi:ss')
  12.   3     and sj.lrrq < to_date('2013-11-30', 'yyyy-mm-dd hh24:mi:ss')
  13.   4  /

  15.   COUNT(1)
  16. ----------
  17.     253707

  19. Elapsed: 00:00:07.19
  20. SQL> select count(1) from xcxt.t_sj_sjjbxx sj
  21.   2  where sj.lrrq >= to_date('2013-09-01', 'yyyy-mm-dd hh24:mi:ss')
  22.   3     and sj.lrrq < to_date('2013-09-30', 'yyyy-mm-dd hh24:mi:ss')
  23.   4  /

  25.   COUNT(1)
  26. ----------
  27.     234159

  29. Elapsed: 00:00:00.92
复制代码

sjxx_tkprof.trc.txt

6.94 KB, 下载次数: 1120

sjxx.trc.txt

256 KB, 下载次数: 536

回复 显示全部楼层 道具 举报

3#
发表于 2013-12-13 10:20:19
补充信息：
T_SJ_SJJBXX 分析表 gather_table_stats estimate_percent 采样行的百分比为5%时，LRRQ列字段的distinct _keys 为79W，与实际不同值数差别很大，实际distinct _keys为 460W

在正式环境测试了 LRRQ区间间隔 几天，1个月，1年，使用HASH_JOIN方式比使用NESTED_LOOPS方式效率要高

各位专家，像这种ORACLE选择错误的执行计划的情况，大家通常采取什么方式处理呢？
我所想到的就是加HINT与使用sql profile固定执行计划，加HINT方式，开发一般不会同意。

期待各位专家支招~

回复 显示全部楼层 道具 举报

4#
发表于 2013-12-13 13:46:24
T_SYS_ORGANIZATION 这个表是机构表，95%以上数据是2011年底系统上线时初始化进去的，一年变动的机构不超过20个，而且这个表的数据量很小，数据才3000多

回复 显示全部楼层 道具 举报

5#
发表于 2013-12-16 17:41:57

  2. Specify the Sql id
  3. ~~~~~~~~~~~~~~~~~~
  4. Enter value for sqlid: 6d0b2rq5dj5u9

  6. Sql Id specified: 6d0b2rq5dj5u9
  7. Elapsed: 00:00:00.01

  9. Tune the sql
  10. ~~~~~~~~~~~~
  11. Elapsed: 00:00:00.00
  12. Elapsed: 00:00:39.57

  14. GENERAL INFORMATION SECTION
  15. -------------------------------------------------------------------------------
  16. Tuning Task Name   : TASK_59486
  17. Tuning Task Owner  : SYS
  18. Workload Type      : Single SQL Statement
  19. Scope              : COMPREHENSIVE
  20. Time Limit(seconds): 1800
  21. Completion Status  : COMPLETED
  22. Started at         : 12/16/2013 17:18:30
  23. Completed at       : 12/16/2013 17:19:08

  25. -------------------------------------------------------------------------------
  26. Schema Name: SYS
  27. SQL ID     : 6d0b2rq5dj5u9
  28. SQL Text   : select count(*)
  29.                  from xcxtt_sj_sjjbxx sj
  30.                 inner join xcxtt_sys_organization org
  31.                    on org.orgcode = sj.bldwid
  32.                 where sj.lrrq >= to_date('2013-11-01', 'yyyy-mm-dd
  33.              hh24:mi:ss')
  34.                   and sj.lrrq < to_date('2013-11-30', 'yyyy-mm-dd
  35.              hh24:mi:ss')
  36.                   and org.treepath like '11000000000000.11010600000000' || '%'

  38. -------------------------------------------------------------------------------
  39. FINDINGS SECTION (1 finding)
  40. -------------------------------------------------------------------------------

  42. 1- SQL Profile Finding (see explain plans section below)
  43. --------------------------------------------------------
  44.   A potentially better execution plan was found for this statement.

  46.   Recommendation (estimated benefit: 99.35%)
  47.   ------------------------------------------
  48.   - Consider accepting the recommended SQL profile.
  49.     execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_59486',
  50.             task_owner => 'SYS', replace => TRUE);

  52.   Validation results
  53.   ------------------
  54.   The SQL profile was tested by executing both its plan and the original plan
  55.   and measuring their respective execution statistics. A plan may have been
  56.   only partially executed if the other could be run to completion in less time.

  58.                            Original Plan  With SQL Profile  % Improved
  59.                            -------------  ----------------  ----------
  60.   Completion Status:            COMPLETE          COMPLETE
  61.   Elapsed Time(ms):                 7339                50      99.31%
  62.   CPU Time(ms):                     7430                70      99.05%
  63.   User I/O Time(ms):                   0                 0
  64.   Buffer Gets:                    188731              1209      99.35%
  65.   Disk Reads:                          0                 0
  66.   Direct Writes:                       0                 0
  67.   Rows Processed:                      1                 1
  68.   Fetches:                             1                 1
  69.   Executions:                          1                 1

  71. -------------------------------------------------------------------------------
  72. EXPLAIN PLANS SECTION
  73. -------------------------------------------------------------------------------

  75. 1- Original With Adjusted Cost
  76. ------------------------------
  77. Plan hash value: 2027379502

  79. -----------------------------------------------------------------------------------------------------
  80. | Id  | Operation           | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
  81. -----------------------------------------------------------------------------------------------------
  82. |   0 | SELECT STATEMENT    |                               |     1 |    93 |   316   (0)| 00:00:04 |
  83. |   1 |  SORT AGGREGATE     |                               |     1 |    93 |            |          |
  84. |*  2 |   FILTER            |                               |       |       |            |          |
  85. |   3 |    NESTED LOOPS     |                               | 25658 |  2330K|   316   (0)| 00:00:04 |
  86. |*  4 |     INDEX RANGE SCAN| CIX_SYS_ORGANIZATION_TREECODE |   157 | 10990 |     2   (0)| 00:00:01 |
  87. |*  5 |     INDEX RANGE SCAN| INDEX_SJ_SJJBXX_RQDW          |   442 | 10166 |     2   (0)| 00:00:01 |
  88. -----------------------------------------------------------------------------------------------------

  90. Predicate Information (identified by operation id):
  91. ---------------------------------------------------

  93.    2 - filter(TO_DATE('2013-11-01','yyyy-mm-dd hh24:mi:ss')<TO_DATE('2013-11-30','yyyy-mm-dd
  94.               hh24:mi:ss'))
  95.    4 - access("ORG"."TREEPATH" LIKE '11000000000000.11010600000000%')
  96.        filter("ORG"."TREEPATH" LIKE '11000000000000.11010600000000%')
  97.    5 - access("SJ"."LRRQ">=TO_DATE('2013-11-01','yyyy-mm-dd hh24:mi:ss') AND
  98.               "ORG"."ORGCODE"="SJ"."BLDWID" AND "SJ"."LRRQ"<TO_DATE('2013-11-30','yyyy-mm-dd hh24:mi:ss'))
  99.        filter("ORG"."ORGCODE"="SJ"."BLDWID")

  101. 2- Original With Adjusted Cost
  102. ------------------------------
  103. Plan hash value: 2027379502

  105. -----------------------------------------------------------------------------------------------------
  106. | Id  | Operation           | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
  107. -----------------------------------------------------------------------------------------------------
  108. |   0 | SELECT STATEMENT    |                               |     1 |    93 |   316   (0)| 00:00:04 |
  109. |   1 |  SORT AGGREGATE     |                               |     1 |    93 |            |          |
  110. |*  2 |   FILTER            |                               |       |       |            |          |
  111. |   3 |    NESTED LOOPS     |                               | 25658 |  2330K|   316   (0)| 00:00:04 |
  112. |*  4 |     INDEX RANGE SCAN| CIX_SYS_ORGANIZATION_TREECODE |   157 | 10990 |     2   (0)| 00:00:01 |
  113. |*  5 |     INDEX RANGE SCAN| INDEX_SJ_SJJBXX_RQDW          |   442 | 10166 |     2   (0)| 00:00:01 |
  114. -----------------------------------------------------------------------------------------------------

  116. Predicate Information (identified by operation id):
  117. ---------------------------------------------------

  119.    2 - filter(TO_DATE('2013-11-01','yyyy-mm-dd hh24:mi:ss')<TO_DATE('2013-11-30','yyyy-mm-dd
  120.               hh24:mi:ss'))
  121.    4 - access("ORG"."TREEPATH" LIKE '11000000000000.11010600000000%')
  122.        filter("ORG"."TREEPATH" LIKE '11000000000000.11010600000000%')
  123.    5 - access("SJ"."LRRQ">=TO_DATE('2013-11-01','yyyy-mm-dd hh24:mi:ss') AND
  124.               "ORG"."ORGCODE"="SJ"."BLDWID" AND "SJ"."LRRQ"<TO_DATE('2013-11-30','yyyy-mm-dd hh24:mi:ss'))
  125.        filter("ORG"."ORGCODE"="SJ"."BLDWID")

  127. 3- Using SQL Profile
  128. --------------------
  129. Plan hash value: 2716384415

  131. -----------------------------------------------------------------------------------------------------
  132. | Id  | Operation           | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
  133. -----------------------------------------------------------------------------------------------------
  134. |   0 | SELECT STATEMENT    |                               |     1 |    93 |     8  (38)| 00:00:01 |
  135. |   1 |  SORT AGGREGATE     |                               |     1 |    93 |            |          |
  136. |*  2 |   FILTER            |                               |       |       |            |          |
  137. |*  3 |    HASH JOIN        |                               | 25658 |  2330K|     8  (38)| 00:00:01 |
  138. |*  4 |     INDEX RANGE SCAN| CIX_SYS_ORGANIZATION_TREECODE |   157 | 10990 |     2   (0)| 00:00:01 |
  139. |*  5 |     INDEX RANGE SCAN| INDEX_SJ_SJJBXX_RQDW          |   258K|  5804K|     3   (0)| 00:00:01 |
  140. -----------------------------------------------------------------------------------------------------

  142. Predicate Information (identified by operation id):
  143. ---------------------------------------------------

  145.    2 - filter(TO_DATE('2013-11-01','yyyy-mm-dd hh24:mi:ss')<TO_DATE('2013-11-30','yyyy-mm-dd
  146.               hh24:mi:ss'))
  147.    3 - access("ORG"."ORGCODE"="SJ"."BLDWID")
  148.    4 - access("ORG"."TREEPATH" LIKE '11000000000000.11010600000000%')
  149.        filter("ORG"."TREEPATH" LIKE '11000000000000.11010600000000%')
  150.    5 - access("SJ"."LRRQ">=TO_DATE('2013-11-01','yyyy-mm-dd hh24:mi:ss') AND
  151.               "SJ"."LRRQ"<TO_DATE('2013-11-30','yyyy-mm-dd hh24:mi:ss'))

  153. -------------------------------------------------------------------------------

  155. Elapsed: 00:00:02.47
  156. Elapsed: 00:00:00.00
复制代码

回复 显示全部楼层 道具 举报

6#
发表于 2013-12-16 17:48:41
本帖最后由 SKYLINE.LIU 于 2013-12-16 17:53 编辑
Liu Maclean(刘相兵 发表于 2013-12-15 19:25
---------------------------------------------------------------------------------------------------- ...


在1楼的列统计信息
  1. TABLE_NAME        COLUMN_NAME        NUM_DISTINCT        NUM_NULLS        NUM_BUCKETS        DENSITY
  2. T_SJ_SJJBXX        BLDWID        575        88460        1        0.00173913043478261
  3. T_SJ_SJJBXX        LRRQ        792962        2020        1        1.26109447867615E-6
  4. T_SYS_ORGANIZATION        ORGCODE        3517        0        1        0.000284333238555587
  5. T_SYS_ORGANIZATION        TREEPATH        3517        0        1        0.000284333238555587
复制代码
作为条件的四个列 TREEPATH,ORGCODE,BLDWID,LRRQ 均没有直方图，所以直方图的影响可以排除。

非常感谢各位专家在百忙之中的解答，这个问题能否解决并不重要，最重要的是向各位专家学习了解决问题的思路~

回复 显示全部楼层 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-6-2 23:45 , Processed in 0.054129 second(s), 24 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569