解决CBO对TABLE函数基数估算导致的性能问题

IT那活儿发布于2023-01-11 13:19 / 863人阅读

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

TABLE函数在SQL中使用，可以将传入的集合转为普通表使用，与管道函数结合使用，往往能够提高效率，然后在实际应用过程中，发现CBO对TABLE函数的启发式基数估算，往往会导致性能问题。某库下面的SQL虽然单条运行很快，但是运行非常频繁，严重消耗CPU资源。

SELECT B.ID,
       B.NAME,
       B.TASK_ID,
       B.DICTION,
       B.GROUP_ID,
       NVL(B.ATTEST_FLAG, N),
       NVL(B.DOUWIN_FLAG, N),
       B.DESC,
       NVL(B.SIGN_FLAG, N),
       B.MAX_EXECUTE_NUM
  FROM (SELECT DISTINCT (ID)
          FROM TEST_TAB
         WHERE STATUS = 04
           AND CHN_TYPE = :B1) A,
       TABLE(CAST(:B2 AS TYPE_INFO_TABLE)) B
 WHERE A.ID = B.ID

执行计划如下：

Plan hash value: 918180822
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |       | |   839 (100)| |
|* 1 |  HASH JOIN | |  5784 | 75192 |   839   (5)| 00:00:05 |
| 2 |   COLLECTION ITERATOR PICKLER FETCH| |       | |            | |
| 3 |   VIEW | | 12361 | 132K|   818   (5)| 00:00:05 |
| 4 |    HASH UNIQUE | | 12361 | 301K|   818   (5)| 00:00:05 |
|* 5 |     TABLE ACCESS FULL | TEST_TAB | 21104 | 515K|   814   (4)| 00:00:05 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."ID"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
5 - filter(("CHN_TYPE"=:B1 AND "STATUS"=04))

问题分析与优化建议

1. 主要原因使用了嵌套表+TABLE函数，ORACLE对TABLE函数的基数估算是固定值：返回8168行，这个值比较大，可以导致执行计划与其他表JOIN的时候可能走错，比如应该走NESTED LOOPS的走成HASH JOIN，导致部分表无法使用索引访问。

对于在SQL中适用嵌套表和TABLE函数的，如果TABLE函数实际返回的数据量较小，比如返回200行之下，在11g之前只能使用hint:cardinality、opt_estimate，而且必须手动设置比较小的基数，比如100。11g可以适用动态采样，对table函数起作用.对应hint:dynamic_sampling。通过以上设置，可以使TABLE函数与其它表JOIN可以走NESTED LOOPS，从而使用索引访问。

当然，这个设置的前提是实际情况下，大部分时候，TABLE函数返回的结果行数较少，如果返回的结果集行数接近或大于8168，那将基数设置为很小的值，也就失去了意义。ORACLE估算TABLE函数返回8168行，如下所示：

CBO估算TABLE函数的cardinality为8168行，这与实际情况200以下，差别太大。

SQL> explain plan for
2  SELECT *
3  FROM TABLE(CAST(:B2 AS TYPE_INFO_TABLE));
Explained.
Elapsed: 00:00:00.01

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 1692170009
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 |    14   (0)| 00:00:01 |
| 1 |  COLLECTION ITERATOR PICKLER FETCH| |       | |            | |
------------------------------------------------------------------------------------------

通过cardinality hint强制返回100行：

SQL> explain plan for
2  select/*+cardinality(b 100)*/ * from
3  TABLE(CAST(:B2 AS TYPE_INFO_TABLE)) B;
Explained.
Elapsed: 00:00:00.06

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 1692170009
------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                  | | 100 | 200 | 20   (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| | | | | |
------------------------------------------------------------------------------------------
8 rows selected.

通过opt_estimate hint强制返回100行：

SQL> explain plan for
2  select/*+opt_estimate(table,b,rows=100)*/ * from
3  TABLE(CAST(:B2 AS TYPE_INFO_TABLE)) B;
Explained.
Elapsed: 00:00:00.01

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 1692170009
------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                  | | 100 | 200 | 20   (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| | | | | |
------------------------------------------------------------------------------------------
8 rows selected.
Elapsed: 00:00:00.04

2. 通过分析语句只访问TABLE函数返回的行，为了做JOIN，使用了DISTINCT，是没有必要的，而且会影响执行计划，这里通过EXISTS子查询改写。

优化方案与效果

1. 优化方案

使用cardinality hint，并且将语句中DISTINCT修改为EXISTS子查询，如下：

SELECT/*+cardinality(b 100)*/  B.ID, B.NAME, B.TASK_ID, B.DICTION, B.GROUP_ID,NVL(B.ATTEST_FLAG,N), NVL(B.DOUWIN_FLAG,N), B.DESC, NVL(B.SIGN_FLAG,N),B.MAX_EXECUTE_NUM
FROM  TABLE(CAST(:B2 AS TYPE_INFO_TABLE)) B
WHERE EXISTS(SELECT 1 FROM TEST_TAB A
WHERE A.STATUS = 04
AND A.CHN_TYPE = :B1
AND A.ID = B.ID
);

2. 优化效果

优化后buffer gets从4283到227，是原来的5.3%，时间也从原来的0.27到0.01。

优化前走HASH JOIN，全表扫描表TEST_TAB：

Plan hash value: 3464704515

------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|* 1 |  HASH JOIN | |      1 | 1787 |     75 |00:00:00.27 |   4283 | 1959K|  1363K| 2182K (0)|
| 2 |   VIEW | |      1 | 3807 |  27100 |00:00:00.25 |    4283 | |       | |
| 3 |    HASH UNIQUE | |      1 | 3807 |  27100 |00:00:00.25 |    4283 | 1983K|  1380K| 1725K (0)|
|* 4 |     TABLE ACCESS FULL | TEST_TAB |            1 | 4281 |    208K|00:00:00.21 |    4283 | |       | |
| 5 |   COLLECTION ITERATOR PICKLER FETCH| |      1 | |     75 |00:00:00.01 |       0 | |       | |
------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."ID"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
4 - filter(("CHN_TYPE"=:B1 AND "STATUS"=04))

优化后的执行计划，走NESTED LOOPS+索引访问TEST_TAB：

Plan hash value: 884413475


------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------
|   1 | NESTED LOOPS SEMI |                   | 1 |      1 | 75 |00:00:00.01 | 227 |
| 2 |   COLLECTION ITERATOR PICKLER FETCH| |      1 | |     75 |00:00:00.01 |       0 |
|* 3 | TABLE ACCESS BY INDEX ROWID | TEST_TAB | 75 |      1 | 75 |00:00:00.01 | 227 |
|* 4 |    INDEX RANGE SCAN | PK_TEST_TAB |     75 | 2 |     75 |00:00:00.01 |     152 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A"."STATUS"=04)
4 - access("A"."ID"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2) AND "A"."CHN_TYPE"=:B2)
filter("A"."CHN_TYPE"=:B2)

总结与建议

如果SQL中使用TABLE函数，但是实际情况返回的行数比ORACLE估算的8168行少很多，使用ORACLE CBO的估算方式，导致不正确的执行计划，这时，就需要人工干预，使用HINT等方式(可以使用SQL PROFILE等)，让SQL走更优的计划，从而提高效率。

本文作者：丁俊(上海新炬王翦团队）

本文来源：“IT那活儿”公众号

GPU云服务器云服务器解决问题的方法云计算解决的问题短视频问题的解决措施数据分析解决的问题

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129208.html

宜信-运维-数据库|SQL优化：一篇文章说清楚Oracle Hint的正确使用姿势

摘要：引导优化器按照哈希扫描的方式从表中读取数据。告诉优化器强制选择位图索引。这个提示会使优化器合并表上的多个位图索引，而不是选择其中最好的索引这是提示的用途。还可以使用指定单个索引对于指定位图索引，该提示优先于提示。一、提示（Hint）概述 1、为什么引入Hint? Hint是Oracle数据库中很有特色的一个功能，是很多DBA优化中经常采用的一个手段。那为什么Oracle会考虑引入优化...

LeoHsiun 2019-06-25 19:17 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师

我要关注我要私信

TA的文章

消息中间件故障分析一例

阅读 1359·2023-01-11 13:20
RAC双节点crash回复一例

阅读 1708·2023-01-11 13:20
ORA-600处理一例

阅读 1215·2023-01-11 13:20
双节点RAC实例2 HANG 故障分析一例

阅读 1909·2023-01-11 13:20
RAC集群节点1重启分析一例

阅读 4166·2023-01-11 13:20
CRS启动报错CRS-1656处理分享

阅读 2761·2023-01-11 13:20
oracle 12CR2打补丁报错处理一例

阅读 1402·2023-01-11 13:20
分布式缓存组件故障分析及监控优化

阅读 3674·2023-01-11 13:20

资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

解决CBO对TABLE函数基数估算导致的性能问题

问题分析与优化建议

优化方案与效果

本文作者：丁俊(上海新炬王翦团队）

本文来源：“IT那活儿”公众号

相关文章

宜信-运维-数据库|SQL优化：一篇文章说清楚Oracle Hint的正确使用姿势

发表评论

0条评论

IT那活儿

男|高级讲师

TA的文章

消息中间件故障分析一例

RAC双节点crash回复一例

ORA-600处理一例

双节点RAC实例2 HANG 故障分析一例

RAC集群节点1重启分析一例

CRS启动报错CRS-1656处理分享

oracle 12CR2打补丁报错处理一例

分布式缓存组件故障分析及监控优化

最新活动

资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

解决CBO对TABLE函数基数估算导致的性能问题

问题分析与优化建议

优化方案与效果

本文作者：丁 俊(上海新炬王翦团队）

本文来源：“IT那活儿”公众号

相关文章

发表评论

0条评论

男|高级讲师

TA的文章

最新活动

本文作者：丁俊(上海新炬王翦团队）