mysql 统计信息不准_统计信息不准导致执行计划出错跑不出结果，优化后只要1分钟...

于 2021-01-19 03:10:06 发布
阅读量192 收藏
点赞数
文章标签： mysql 统计信息不准
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39835607/article/details/113196425
版权

一天查看数据库长会话，发现1个sql跑得很慢，1个多小时不出结果，花了点时间把它给优化了。 优化前： SELECT 20131023, A2.ORG_ID, COUNT(DISTINCT NLSSORT(CASE A2.RES_TYPE WHEN DP THEN A2.RES_CODE END, nls_sort=BINARY)), COUNT(DISTINCT NLSSORT(CASE

一天查看数据库长会话，发现1个sql跑得很慢，1个多小时不出结果，花了点时间把它给优化了。

优化前：

SELECT 20131023,

"A2"."ORG_ID",

COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"

WHEN 'DP' THEN

"A2"."RES_CODE"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"

WHEN 'BOX' THEN

"A2"."RES_CODE"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"

WHEN 'ONU' THEN

"A2"."RES_CODE"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"

WHEN 'OBD' THEN

"A2"."RES_CODE"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE

WHEN ("A1"."CON_TYPE" = '001' AND "A2"."RES_TYPE" = 'DP') THEN

"A1"."RES_ID"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE

WHEN ("A1"."CON_TYPE" = '002' AND "A2"."RES_TYPE" = 'BOX') THEN

"A1"."RES_ID"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE

WHEN ("A1"."CON_TYPE" = '0011' AND "A2"."RES_TYPE" = 'ONU') THEN

"A1"."RES_ID"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE

WHEN ("A1"."CON_TYPE" = '0022' AND "A2"."RES_TYPE" = 'OBD') THEN

"A1"."RES_ID"

END,

'nls_sort=''BINARY'''))

FROM "CRM_SZ"."AAA" "A2",

"CRM_SZ"."BBB" "A1"

WHERE "A1"."RES_ID"(+) = "A2"."RES_CODE"

GROUP BY "A2"."ORG_ID"

执行计划：

Plan hash value: 2627707252

---------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 1065 | 3 (34)| 00:00:01 |

| 1 | SORT GROUP BY | | 1 | 1065 | 3 (34)| 00:00:01 |

| 2 | NESTED LOOPS OUTER| | 1 | 1065 | 2 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| AAA | 1 | 539 | 2 (0)| 00:00:01 |

|* 4 | INDEX FULL SCAN | IX_MO_CON_VALUE | 1 | 526 | 0 (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("A1"."RES_ID"(+)="A2"."RES_CODE")

filter("A1"."RES_ID"(+)="A2"."RES_CODE")

cbo估算错了，rows全是1，导致走nl

手工count了一把：

select count(*) from "CRM_SZ"."AAA" ;--1365564

select count(*) from "CRM_SZ"."BBB";--119949

走nl那岂不是sb啦。

第一次优化后：

SELECT/*+use_hash(A1,A2) swap_join_inputs(A1)*/20131023,

"A2"."ORG_ID",

COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"

WHEN 'DP' THEN

"A2"."RES_CODE"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"

WHEN 'BOX' THEN

"A2"."RES_CODE"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"

WHEN 'ONU' THEN

"A2"."RES_CODE"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"

WHEN 'OBD' THEN

"A2"."RES_CODE"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE

WHEN ("A1"."CON_TYPE" = '001' AND "A2"."RES_TYPE" = 'DP') THEN

"A1"."RES_ID"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE

WHEN ("A1"."CON_TYPE" = '002' AND "A2"."RES_TYPE" = 'BOX') THEN

"A1"."RES_ID"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE

WHEN ("A1"."CON_TYPE" = '0011' AND "A2"."RES_TYPE" = 'ONU') THEN

"A1"."RES_ID"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE

WHEN ("A1"."CON_TYPE" = '0022' AND "A2"."RES_TYPE" = 'OBD') THEN

"A1"."RES_ID"

END,

'nls_sort=''BINARY'''))

FROM "CRM_SZ"."AAA" "A2",

"CRM_SZ"."BBB" "A1"

WHERE "A1"."RES_ID"(+) = "A2"."RES_CODE"

GROUP BY "A2"."ORG_ID"

63 rows selected.

Elapsed: 00:00:47.64

Execution Plan

----------------------------------------------------------

Plan hash value: 3074972763

------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 1065 | 4 (50)| 00:00:01 |

| 1 | SORT GROUP BY | | 1 | 1065 | 4 (50)| 00:00:01 |

|* 2 | HASH JOIN RIGHT OUTER| | 1 | 1065 | 3 (34)| 00:00:01 |

| 3 | INDEX FULL SCAN | IX_MO_CON_VALUE | 1 | 526 | 0 (0)| 00:00:01 |

| 4 | TABLE ACCESS FULL | AAA | 1 | 539 | 2 (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("A1"."RES_ID"(+)="A2"."RES_CODE")

Statistics

----------------------------------------------------------

1065 recursive calls

3 db block gets

13375 consistent gets

16369 physical reads

0 redo size

4862 bytes sent via SQL*Net to client

791 bytes received via SQL*Net from client

6 SQL*Net roundtrips to/from client

12 sorts (memory)

1 sorts (disk)

63 rows processed

第二次优化后：

SELECT/*+use_hash(A1,A2) full(A1) full(A2) parallel(A1,5) parallel(A2,5) swap_join_inputs(A1)*/20131023,

"A2"."ORG_ID",

COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"

WHEN 'DP' THEN

"A2"."RES_CODE"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"

WHEN 'BOX' THEN

"A2"."RES_CODE"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"

WHEN 'ONU' THEN

"A2"."RES_CODE"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE "A2"."RES_TYPE"

WHEN 'OBD' THEN

"A2"."RES_CODE"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE

WHEN ("A1"."CON_TYPE" = '001' AND "A2"."RES_TYPE" = 'DP') THEN

"A1"."RES_ID"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE

WHEN ("A1"."CON_TYPE" = '002' AND "A2"."RES_TYPE" = 'BOX') THEN

"A1"."RES_ID"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE

WHEN ("A1"."CON_TYPE" = '0011' AND "A2"."RES_TYPE" = 'ONU') THEN

"A1"."RES_ID"

END,

'nls_sort=''BINARY''')),

COUNT(DISTINCT NLSSORT(CASE

WHEN ("A1"."CON_TYPE" = '0022' AND "A2"."RES_TYPE" = 'OBD') THEN

"A1"."RES_ID"

END,

'nls_sort=''BINARY'''))

FROM "CRM_SZ"."AAA" "A2",

"CRM_SZ"."BBB" "A1"

WHERE "A1"."RES_ID"(+) = "A2"."RES_CODE"

GROUP BY "A2"."ORG_ID";

63 rows selected.

Elapsed: 00:00:10.21

Execution Plan

----------------------------------------------------------

Plan hash value: 4044842257

-------------------------------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |

-------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 1065 | 6 (34)| 00:00:01 | | | |

| 1 | PX COORDINATOR | | | | | | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10004 | 1 | 1065 | 6 (34)| 00:00:01 | Q1,04 | P->S | QC (RAND) |

| 3 | SORT GROUP BY | | 1 | 1065 | 6 (34)| 00:00:01 | Q1,04 | PCWP | |

| 4 | PX RECEIVE | | 1 | 1065 | 6 (34)| 00:00:01 | Q1,04 | PCWP | |

| 5 | PX SEND HASH | :TQ10003 | 1 | 1065 | 6 (34)| 00:00:01 | Q1,03 | P->P | HASH |

| 6 | SORT GROUP BY | | 1 | 1065 | 6 (34)| 00:00:01 | Q1,03 | PCWP | |

| 7 | PX RECEIVE | | 1 | 1065 | 6 (34)| 00:00:01 | Q1,03 | PCWP | |

| 8 | PX SEND HASH | :TQ10002 | 1 | 1065 | 6 (34)| 00:00:01 | Q1,02 | P->P | HASH |

| 9 | SORT GROUP BY | | 1 | 1065 | 6 (34)| 00:00:01 | Q1,02 | PCWP | |

|* 10 | HASH JOIN RIGHT OUTER| | 1 | 1065 | 5 (20)| 00:00:01 | Q1,02 | PCWP | |

| 11 | PX RECEIVE | | 1 | 526 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |

| 12 | PX SEND HASH | :TQ10000 | 1 | 526 | 2 (0)| 00:00:01 | Q1,00 | P->P | HASH |

| 13 | PX BLOCK ITERATOR | | 1 | 526 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |

| 14 | TABLE ACCESS FULL| BBB | 1 | 526 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |

| 15 | PX RECEIVE | | 1 | 539 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |

| 16 | PX SEND HASH | :TQ10001 | 1 | 539 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |

| 17 | PX BLOCK ITERATOR | | 1 | 539 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |

| 18 | TABLE ACCESS FULL| AAA | 1 | 539 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |

-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

10 - access("A1"."RES_ID"(+)="A2"."RES_CODE")

Statistics

----------------------------------------------------------

585 recursive calls

4 db block gets

14267 consistent gets

13126 physical reads

808 redo size

4888 bytes sent via SQL*Net to client

840 bytes received via SQL*Net from client

6 SQL*Net roundtrips to/from client

23 sorts (memory)

0 sorts (disk)

63 rows processed

优化前，执行计划走了nl，1个多小时查不出结果

第一次优化后，首次00:00:47.64可以出结果

第二次优化后，首次00:00:10.21出结果，重复执行(有缓存)的情况下3s出结果。

f68f2add0b68e4f9810432fce46917b7.png

本文原创发布php中文网，转载请注明出处，感谢您的尊重！

weixin_39835607
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值