有很多情况,导致CBO选错执行计划,良好的统计信息在优化当中还是非常有必要的。在其他文章当中简单说过Oracle表之间的连接方式。如果一个表的基数算错,很可能就会走错执行计划。
PgSQL
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 3215660883
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 78 | 4212 | 15507 (1)| 00:01:47 |
| 1 | HASH GROUP BY | | 78 | 4212 | 15507 (1)| 00:01:47 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 3034 | 159K| 15506 (1)| 00:01:47 |
|* 4 | TABLE ACCESS FULL | OPT_REF_UOM_TEMP_SDIM | 2967 | 101K| 650 (14)| 00:00:05 |
|* 5 | INDEX RANGE SCAN | PROD_DIM_PK | 3 | | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| PROD_DIM | 1 | 19 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("UOM"."RELTV_CURR_QTY"=1)
5 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")
6 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND
"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")
22 rows selected.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SQL>select*fromtable(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Planhashvalue:3215660883
-------------------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
-------------------------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||78|4212|15507(1)|00:01:47|
|1|HASHGROUP BY||78|4212|15507(1)|00:01:47|
|2|NESTEDLOOPS||||||
|3|NESTEDLOOPS||3034|159K|15506(1)|00:01:47|
|*4|TABLEACCESSFULL|OPT_REF_UOM_TEMP_SDIM|2967|101K|650(14)|00:00:05|
|*5|INDEXRANGESCAN|PROD_DIM_PK|3||2(0)|00:00:01|
|*6|TABLEACCESSBYINDEXROWID|PROD_DIM|1|19|5(0)|00:00:01|
-------------------------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
4-filter("UOM"."RELTV_CURR_QTY"=1)
5-access("PROD"."PROD_SKID"="UOM"."PROD_SKID")
6-filter("PROD"."BUOM_CURR_SKID"ISNOT NULLAND"PROD"."PROD_END_DATE"=TO_DATE('
9999-12-31 00:00:00','syyyy-mm-dd hh24:mi:ss')AND"PROD"."CURR_IND"='Y'AND
"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")
22rowsselected.
从执行计划可以看出来。id=4这一步最先执行,全表扫描估算返回2967条数据,然后id=4会和id=5进行嵌套连接,那id=5这一步会被扫描2967次,且id=4这一步有filter过滤,这个执行计划看起来没什么问题,但是在执行的时候会话费比较长的时间。看到执行计划,先从内开始找起,找到id=4这一步,通过过滤条件到表中查询数据,看实际返回的条数
PgSQL
SQL> select count(*) from OPT_REF_UOM_TEMP_SDIM where "RELTV_CURR_QTY"=1;
COUNT(*)
----------
946432
1
2
3
4
5
SQL>selectcount(*)fromOPT_REF_UOM_TEMP_SDIMwhere"RELTV_CURR_QTY"=1;
COUNT(*)
----------
946432
可以从实际环境中看到。实际id=4这一步要返回946432条数据,和CBO估算返回相差太大。基数估算错误,这里不应该嵌套循环,应该走hash join,如果走了嵌套循环,那id=5这一步就要扫描946432次。一般基数估算不准,可以通过查看统计信息是否过期来解决。
转载请注明: 版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!
最后编辑:2014-02-26作者:Jerry
一个积极向上的小青年,热衷于分享--Focus on DB,BI,ETL