统计信息不准导致执行计划出错跑不出结果，优化后只要1分钟
- WBOYOriginal
- 2016-06-07 15:58:381377browse
一天查看数据库长会话，发现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出结果。
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：【翻译自mos文章】asm归档路径满了Next article：相同更改数据量的前提下，单次COMMIT和多次COMMIT对日志空间浪费