保障执行计划正确

来源：互联网发布：linux命令 cp r 编辑：程序博客网时间：2024/04/30 19:06

现在项目中经常出现有的时候 SQL跑的很快，有时候确像蜗牛。。

我当时觉得奇怪，为啥哥优化好了一个SQL，怎么又慢的？？于是哥开始反思，这个哥的第一反应是统计信息导致的问题。统计信息把rows 算的错误的太离谱了。

导致本来走hash 的走了 NL。

于是哥找出SQl，看了下执行计划，果然

/*
1 Plan hash value: 2350308821
2
3 -----------------------------------------------------------------------------------------------------
4 | Id | Operation                     | Name                | Rows | Bytes | Cost (%CPU)| Time     |
5 -----------------------------------------------------------------------------------------------------
6 |   0 | SELECT STATEMENT              |                     |     1 |   105 |     7   (0)| 00:00:01 |
7 |   1 | SORT AGGREGATE               |                     |     1 |   105 |            |          |
8 |   2 |   NESTED LOOPS OUTER          |                     |     1 |   105 |     7   (0)| 00:00:01 |
9 |   3 |    NESTED LOOPS               |                     |     1 |    84 |     4   (0)| 00:00:01 |
10 |   4 |     NESTED LOOPS OUTER        |                     |     1 |    77 |     3   (0)| 00:00:01 |
11 |   5 |      TABLE ACCESS FULL        | T_PUB_BILL_ACTION_3 |     1 |    67 |     2   (0)| 00:00:01 |
12 |* 6 |      INDEX RANGE SCAN         | DIM_TASK_SPEC_IDX   |     1 |    10 |     1   (0)| 00:00:01 |
13 |* 7 |     INDEX RANGE SCAN          | IDX_OPENBILL_3      |     1 |     7 |     1   (0)| 00:00:01 |
14 |* 8 |    TABLE ACCESS BY INDEX ROWID| DIM_STAFF           |     1 |    21 |     3   (0)| 00:00:01 |
15 |* 9 |     INDEX RANGE SCAN          | IDX_DIM_STAFF       |     2 |       |     1   (0)| 00:00:01 |
16 -----------------------------------------------------------------------------------------------------

一看果然错误的太离谱了，ＣＢＯ　算成１了，　　　这里哥　想到了，　用ｈｉｎｔ　固定执行计划，　发现这个固然可以，　但是　哥想到为啥　　算成１了。　　

哥看了一下　统计信息　收集的时间，　一看发现　好像是在　９点１７分钟的样子收集的，　　　　哥明白了。

这个正是由于ｔｒｕｎｃａｔｅ　　ｔａｂｌｅ　导致的，　哥查了Ｖ＄ＳＱＬ　，　发现这样搞的还蛮频繁的，　　　ｔｒｕｎｃａｔｅ　之后，　正好　ｏｒａｃｌｅ　来收集统计信息了，　于是哥又收集了一下统计信息　

于是哥　看了一下执行计划，　果然变了　，　变成ｈａｓｈ　了。　

/*
1 Plan hash value: 1978552090
2
3 ------------------------------------------------------------------------------------------------------
4 | Id | Operation              | Name                | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |
5 ------------------------------------------------------------------------------------------------------
6 |   0 | SELECT STATEMENT       |                     | 1047K|   163M|       | 24493   (1)| 00:04:54 |
7 |* 1 | HASH JOIN RIGHT OUTER |                     | 1047K|   163M|       | 24493   (1)| 00:04:54 |
8 |* 2 |   TABLE ACCESS FULL    | DIM_TASK_SPEC       |    51 | 1479 |       |     3   (0)| 00:00:01 |
9 |* 3 |   HASH JOIN RIGHT OUTER|                     | 1047K|   134M| 4544K| 24483   (1)| 00:04:54 |
10 |* 4 |    TABLE ACCESS FULL   | DIM_STAFF           | 81538 | 3583K|       |   231   (1)| 00:00:03 |
11 |* 5 |    HASH JOIN           |                     | 1047K|    89M| 4504K| 18966   (1)| 00:03:48 |
12 |   6 |     TABLE ACCESS FULL | T_OPEN_BILLINFO_3   |   100K| 3323K|       | 4625   (1)| 00:00:56 |
13 |   7 |     TABLE ACCESS FULL | T_PUB_BILL_ACTION_3 | 1047K|    55M|       | 10741   (1)| 00:02:09 |
14 ------------------------------------------------------------------------------------------------------
15
16 Predicate Information (identified by operation id):
17 ---------------------------------------------------
18
19     1 - access("D"."TASK_SPEC_ID"(+)="A"."ACTION_TYPE")
20     2 - filter("D"."SYS_ID"(+)=4)
21     3 - access("C"."ORG_ID"(+)="A"."OPERATOR_GROUP" AND "C"."STAFF_ID"(+)="A"."OPERATOR")
22     4 - filter("C"."SYS_ID"(+)=4)
23     5 - access("A"."BILL_ID"="B"."BILL_ID")*/　　　

这个由于经常要　ｔｒｕｎｃａｔｅ　。　于是　哥需要　固定执行计划，　用ｈｉｎｔ　固定也可以，　　但开发人员好像不太会搞　ｈｉｎｔ，　　那哥只好第二招，让

开发人员方便开发，　　固定统计信息。

于是哥，　　 DBMS_STATS.lock_table_stats　，　　　　那就以下脚本

DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'JS_ZWXN', TABNAME => 'T_PUB_BILL_ACTION_３', ESTIMATE_PERCENT => 100, METHOD_OPT => 'for all columns size repeat', DEGREE => 8, GRANULARITY => 'ALL',CASCADE => TRUE);

DBMS_STATS.lock_table_stats(OWNNAME => 'JS_ZWXN', TABNAME => 'T_PUB_BILL_ACTION_3');

　对了优化前后　　效率提高　２０多倍吧，　　　　　　如果两个表在数据量　最多时候算，　　能提升１００多倍。

　所以　优化来说，　肯定保证　统计信息　正确，　起码不能错误的太离谱了。　

0 0