“通过本地索引 ROWID 进行表访问”估计成本高
我在 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
I have a query in oracle leading to a high estimated cost in an OLAP system. the estimated row number is only 100K but the cost is a huge number. I wonder how the number of cost is calculated and in which situation a super high estimated cost will happen?
The execution plan:
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
估计的 100,000 行是输出。它可能需要做大量的工作来过滤大型数据集，甚至需要做更多的工作来总结大型数据集。也就是说，这些成本是相当天文数字的（即使数据库的数据大小需要 400 多个分区）
尝试执行解释计划，然后执行 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
这提供了一个更具可读性的计划。您希望所有访问和过滤谓词都能看到它正在做什么，以及汇总的成本。
The 100,000 estimated rows is the output. It may need to do a lot of work to filter a large dataset down to that and even more to summarise a large dataset. That said, those costs are pretty astronomical (even with a database with a data size requiring 400+ partitions)
Try doing the explain plan and then a SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
This provides a much more readable plan. You want all the access and filter predicates to see what it is doing, and the costs as they are summarized up.
位图索引转换表明您错过了一个好的索引，并且 Oracle 决定使用现有索引动态构建一个新的临时索引。这可能是相当繁重的操作，并且一旦执行查询，构建的位图索引就会被删除 - 因此在下次运行时无法重用。
您可以手动创建索引或在查询中添加一些提示来阻止位图转换吗？ http://psoug.org/reference/hints.html - 简短的提示列表。更多信息请参见 Oracle 文档。
我从 100k 行的子查询开始，用 no_merge 提示保护它（Oracle 将在内部创建临时视图），然后在后面放置其他连接。如果查询优化器将继续扰乱计划 - 强制使用更多提示，例如索引或 use_nl 等。
Bitmap index conversion is an indication that you miss a good index and Oracle decided to built a new temp index on the fly using existing indexes. It could be quite heavy operation and the built bitmap index dropped as soon as query performed - so no reuse on next run.
Could you create an index by hand or throw in some hints into the query to block bitmap transformation? http://psoug.org/reference/hints.html - short list of hints. More in Oracle docs.
I'd started with subquery for the 100k rows, protected it with no_merge hint (Oracle will create temp view internally) and put other joins after that. If query optimizer will continue to mess with the plan - force more hints like index or use_nl etc.
询问汤姆在这里有一个关于估计成本的有用帖子：
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:40112614814595
Ask tom has a useful thread on estimated cost here:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:40112614814595
非常高的成本可能是由不良的系统统计数据造成的。
将
select * from sys.aux_stats$;的结果与 此页面。我见过一些由 11g bug 引起的疯狂估计 - 收集工作负载统计数据可能会完全失败，并且设置的数字会偏离几个数量级。
Very high costs may be caused by bad system statistics.
Compare the results from
select * from sys.aux_stats$;with the descriptions on this page.I've seen some crazy estimates caused by an 11g bug - collecting workload statistics can completely fail and set numbers that are off by several orders of magnitude.