我在oracle中有一个查询,导致OLAP系统中的估计成本很高。估计的行数只有100K,但成本是一个巨大的数字。我想知道成本的数量是如何计算的,在什么情况下会出现超高的估计成本?
执行计划:
17 TABLE ACCESS BY LOCAL INDEX ROWID /BIC/FZ3PM_C01
| ( Estim. Costs = 1,299,922,942,955,190 , Estim. #Rows = 104,711 )
| Pstart: 1 Pstop: 471
| Estim. CPU-Costs = 18,446,744,073,709,601,000 Estim. IO-Costs = 86,157,375,
|
--- 16 BITMAP CONVERSION TO ROWIDS
|
--- 15 BITMAP AND
|
|-- 7 BITMAP MERGE
| |
| --- 6 BITMAP KEY ITERATION
| |
| |-- 4 BUFFER SORT
| | |
| | ------3 TABLE ACCESS FULL /BIC/DZ3PM_C012
| | ( Estim. Costs = 4 , Estim. #Rows = 180 )
| | Estim. CPU-Costs = 1,093,126 Estim. IO-Costs = 4
| | Filter Predicates
| |
| ------5 BITMAP INDEX RANGE SCAN /BIC/FZ3PM_C01~050
| Pstart: 1 Pstop: 471
| Search Columns: 1
| Access Predicates
|
--- 14 BITMAP MERGE
|
--- 13 BITMAP KEY ITERATION
|
|-- 11 BUFFER SORT
| |
| --- 10 HASH JOIN
| | ( Estim. Costs = 2,492 , Estim. #Rows = 1,264,100 )
| | Estim. CPU-Costs = 801,483,146 Estim. IO-Costs = 2,407
| | Access Predicates
| |
| |-----8 TABLE ACCESS FULL /BI0/XMATERIAL
| | ( Estim. Costs = 1,470 , Estim. #Rows = 50,880 )
| | Estim. CPU-Costs = 403,451,418 Estim. IO-Costs = 1,427
| | Filter Predicates
| ------9 TABLE ACCESS FULL /BIC/DZ3PM_C011
| ( Estim. Costs = 1,007 , Estim. #Rows = 1,264,100 )
| Estim. CPU-Costs = 259,249,328 Estim. IO-Costs = 980
|
------12 BITMAP INDEX RANGE SCAN /BIC/FZ3PM_C01~040
Pstart: 1 Pstop: 471
Search Columns: 1
Access Predicates
估计的100,000行是输出。它可能需要做很多工作来过滤一个大型数据集,甚至更多的工作来总结一个大型数据集。也就是说,这些成本相当天文数字(即使数据库的数据大小需要400多个分区)
尝试执行explain plan,然后执行SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
这提供了一个更可读的计划。您希望所有的访问和筛选谓词都能看到它正在做什么,以及汇总起来的成本。
位图索引转换表明您错过了一个好的索引,Oracle决定使用现有索引动态构建一个新的临时索引。这可能是相当繁重的操作,并且一旦执行查询,构建的位图索引就会下降-因此下次运行时无法重用。
您可以手工创建索引或在查询中添加一些提示来阻止位图转换吗?http://psoug.org/reference/hints.html -简短的提示列表。
我从100k行的子查询开始,用no_merge提示保护它(Oracle将在内部创建临时视图),并在此之后放置其他连接。如果查询优化器将继续打乱计划-强制更多的提示,如index或use_nl等
Ask tom有一个关于估算成本的有用线程:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0:::: P11_QUESTION_ID: 40112614814595
错误的系统统计信息可能导致非常高的成本。
比较select * from sys.aux_stats$;
的结果与本页的描述。
我曾见过一些由11g bug引起的疯狂估计——收集工作负载统计信息可能完全失败,并且设置的数字偏离了几个数量级。