CBO基数估算错误导致sql低效

最新推荐文章于 2021-03-18 17:13:58 发布

转载最新推荐文章于 2021-03-18 17:13:58 发布 · 185 阅读

本文分析了一条SQL语句在执行过程中出现的基数估算错误问题，并通过使用hint进行了优化，显著提高了查询效率。

原SQL语句如下：

select ((v.yvalue * 300) / (u.xvalue * 50)), u.xtime

from (select x.index_value xvalue, substr(x.update_time, 1, 14) xtime

[@more@]

原SQL语句如下：

select ((v.yvalue * 300) / (u.xvalue * 50)), u.xtime

from (select x.index_value xvalue, substr(x.update_time, 1, 14) xtime

from tb_indexs x

where x.id in (select min(a.id)

from tb_indexs a

where a.code = 'HSI'

and a.update_time > 20110701000000

and a.update_time < 20110722000000

group by a.update_time)) u,

(select y.index_value yvalue, substr(y.update_time, 1, 14) ytime

from tb_indexs y

where y.id in (select min(b.id)

from tb_indexs b

where b.code = '000300'

and b.update_time > 20110701000000

and b.update_time < 20110722000000

group by b.update_time)) v

where u.xtime = v.ytime

order by u.xtime

执行计划如下：

PLAN_TABLE_OUTPUT

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

Plan hash value: 573554298

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

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

| 0 | SELECT STATEMENT | | 1 | 54 |

| 1 | SORT ORDER BY | | 1 | 54 |

| 2 | NESTED LOOPS | | 1 | 54 |

| 3 | MERGE JOIN CARTESIAN | | 1 | 33 |

| 4 | NESTED LOOPS | | 1 | 27 |

| 5 | VIEW | VW_NSO_2 | 1 | 6 |

| 6 | HASH GROUP BY | | 1 | 26 |

| 7 | TABLE ACCESS BY INDEX ROWID| TB_INDEXS | 1 | 26 |

|* 8 | INDEX RANGE SCAN | IDX_UPDATE_TIME | 1 | |

| 9 | TABLE ACCESS BY INDEX ROWID | TB_INDEXS | 1 | 21 |

|* 10 | INDEX UNIQUE SCAN | PK_INDEXS | 1 | |

| 11 | BUFFER SORT | | 1 | 6 |

| 12 | VIEW | VW_NSO_1 | 1 | 6 |

| 13 | HASH GROUP BY | | 1 | 26 |

| 14 | TABLE ACCESS BY INDEX ROWID| TB_INDEXS | 1 | 26 |

|* 15 | INDEX RANGE SCAN | IDX_UPDATE_TIME | 1 | |

|* 16 | TABLE ACCESS BY INDEX ROWID | TB_INDEXS | 1 | 21 |

|* 17 | INDEX UNIQUE SCAN | PK_INDEXS | 1 | |

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

Predicate Information (identified by operation id):

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

8 - access("A"."CODE"='HSI' AND "A"."UPDATE_TIME">20110701000000 AND

"A"."UPDATE_TIME"<20110722000000)

10 - access("X"."ID"="$nso_col_1")

15 - access("B"."CODE"='000300' AND "B"."UPDATE_TIME">20110701000000 AND

"B"."UPDATE_TIME"<20110722000000)

16 - filter(SUBSTR(TO_CHAR("X"."UPDATE_TIME"),1,14)=SUBSTR(TO_CHAR("Y"."UPDATE

)

17 - access("Y"."ID"="$nso_col_1")

执行计划中出现两个VIEW，也就是SQL中的u和v。

执行一下字句：

select y.index_value yvalue, substr(y.update_time, 1, 14) ytime

from tb_indexs y

where y.id in (select min(b.id)

from tb_indexs b

where b.update_time < 20110722000000

and b.update_time > 20110701000000

and b.code = '000300'

group by b.update_time)

大概有4万行结果，执行计划ID=5和ID=12都只有1行返回。很明显CBO估算错误。

看看表tb_indexes的结构：

desc tb_indexs

Name Type Nullable Default Comments

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

ID NUMBER

CODE VARCHAR2(50) Y

INDEX_VALUE NUMBER(20,10) Y

UPDATE_TIME NUMBER Y

列update_time竟然是number类型，设计的真牛逼。

其实问题出在VIEW部分：

VIEW | VW_NSO_1 |

HASH GROUP BY | |

TABLE ACCESS BY INDEX ROWID| TB_INDEXS |

INDEX RANGE SCAN | IDX_UPDATE_TIME |

以及

VIEW | VW_NSO_2 |

HASH GROUP BY | |

TABLE ACCESS BY INDEX ROWID| TB_INDEXS |

INDEX RANGE SCAN | IDX_UPDATE_TIME |

CBO 认为只返回1行，但是实际上要返回4W行+

以下是同事的优化：

select /*+ cardinality(@a 20000) cardinality(@b 20000) */ ((v.yvalue * 300) / (u.xvalue * 50)), u.xtime

from (select x.index_value xvalue, substr(x.update_time, 1, 14) xtime

from tb_indexs x

where x.id in (select /*+ QB_NAME(a)*/ min(a.id)

from tb_indexs a

where a.code = 'HSI'

and a.update_time > 20110701000000

and a.update_time < 20110722000000

group by a.update_time)) u,

(select y.index_value yvalue, substr(y.update_time, 1, 14) ytime

from tb_indexs y

where y.id in (select /*+ QB_NAME(b) */ min(b.id)

from tb_indexs b

where b.code = '000300'

and b.update_time > 20110701000000

and b.update_time < 20110722000000

group by b.update_time)) v

where u.xtime = v.ytime

order by u.xtime;

Execution Plan

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

Plan hash value: 2679503093

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

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

| 0 | SELECT STATEMENT | | 935 | 50490 | 1393 (7)| 00:00:17 |

| 1 | SORT ORDER BY | | 935 | 50490 | 1393 (7)| 00:00:17 |

|* 2 | HASH JOIN | | 935 | 50490 | 1392 (7)| 00:00:17 |

| 3 | VIEW | VW_NSO_1 | 20000 | 117K| 4 (0)| 00:00:01 |

| 4 | HASH GROUP BY | | 20000 | 800K| 4 (0)| 00:00:01 |

| 5 | TABLE ACCESS BY INDEX ROWID | TB_INDEXS | 1 | 41 | 4 (0)| 00:00:01 |

|* 6 | INDEX RANGE SCAN | IDX_UPDATE_TIME | 1 | | 3 (0)| 00:00:01 |

|* 7 | HASH JOIN | | 31729 | 1487K| 1386 (7)| 00:00:17 |

|* 8 | HASH JOIN | | 20000 | 527K| 695 (7)| 00:00:09 |

| 9 | VIEW | VW_NSO_2 | 20000 | 117K| 4 (0)| 00:00:01 |

| 10 | HASH GROUP BY | | 20000 | 800K| 4 (0)| 00:00:01 |

| 11 | TABLE ACCESS BY INDEX ROWID| TB_INDEXS | 1 | 41 | 4 (0)| 00:00:01 |

|* 12 | INDEX RANGE SCAN | IDX_UPDATE_TIME | 1 | | 3 (0)| 00:00:01 |

| 13 | TABLE ACCESS FULL | TB_INDEXS | 678K| 13M| 678 (5)| 00:00:09 |

| 14 | TABLE ACCESS FULL | TB_INDEXS | 678K| 13M| 678 (5)| 00:00:09 |

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

Predicate Information (identified by operation id):

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

2 - access("Y"."ID"="$nso_col_1")

6 - access("B"."UPDATE_TIME">20110701000000 AND "B"."CODE"='000300' AND

"B"."UPDATE_TIME"<20110722000000)

filter("B"."CODE"='000300')

7 - access(SUBSTR(TO_CHAR("X"."UPDATE_TIME"),1,14)=SUBSTR(TO_CHAR("Y"."UPDATE_TIME"),1,14)

)

8 - access("X"."ID"="$nso_col_1")

12 - access("A"."UPDATE_TIME">20110701000000 AND "A"."CODE"='HSI' AND

"A"."UPDATE_TIME"<20110722000000)

filter("A"."CODE"='HSI')

基数估算错误，使用hint修正后，sql很快出结果。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25586587/viewspace-1053336/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25586587/viewspace-1053336/