【关于TABLE函数的SQL优化】解决CBO对TABLE函数基数估算导致的性能问题
来源:互联网 发布:淘宝客服千牛教程 编辑:程序博客网 时间:2024/04/27 17:53
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函数的基数使用1个固定值:返回8168行,这个值比较大,可以导致执行计划与其他表JOIN的时候可能走错,比如应该走NESTED LOOPS的走成HASH JOIN,导致部分表无法使用索引访问。对于在SQL中适用嵌套表和TABLE函数的,如果TABLE函数实际返回的数据量较小,比如返回200行之下,在11g之前只能使用hint:cardinality(9i可使用)、opt_estimate(10g可使用),这2个hint都是undocument的,而且必须手动设置比较小的基数,比如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走更优的计划,从而提高效率。
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函数的基数使用1个固定值:返回8168行,这个值比较大,可以导致执行计划与其他表JOIN的时候可能走错,比如应该走NESTED LOOPS的走成HASH JOIN,导致部分表无法使用索引访问。对于在SQL中适用嵌套表和TABLE函数的,如果TABLE函数实际返回的数据量较小,比如返回200行之下,在11g之前只能使用hint:cardinality(9i可使用)、opt_estimate(10g可使用),这2个hint都是undocument的,而且必须手动设置比较小的基数,比如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走更优的计划,从而提高效率。
0 0
- 【关于TABLE函数的SQL优化】解决CBO对TABLE函数基数估算导致的性能问题
- 自定义函数导致的sql性能问题
- 关于oracle table()函数引起的全表扫描问题
- 小细节导致的bootstrap table append的性能问题
- 解决:对 PInvoke 函数的调用导致堆栈不对称问题
- 解决:对 PInvoke 函数的调用导致堆栈不对称问题
- 解决:对 PInvoke 函数的调用导致堆栈不对称问题
- 解决:对 PInvoke 函数的调用导致堆栈不对称问题
- 解决:对 PInvoke 函数的调用导致堆栈不对称问题
- oracle table()函数的使用
- lua的table相关函数
- 关于table的样式问题
- lua程序性能优化(table的初始化)
- pl/sql中使用table()函数的例子
- sql逗号分隔字符串转化成table的函数
- 关于lua的module函数 传参类型变成table
- 对 table-layout 的错误解决
- SQL优化_高水位线导致的性能问题
- 学生选课系统搞我一个礼拜我会再回来找你的
- hadoop+hbase完全分布式环境搭建
- 【剑指Offer面试编程题】题目1283:第一个只出现一次的字符--九度OJ
- 我对AOP的理解
- eclipse+ android环境搭建
- 【关于TABLE函数的SQL优化】解决CBO对TABLE函数基数估算导致的性能问题
- Spring的事务 之 9.4 声明式事务 ——跟我学spring3
- 【重复行处理1】删除重复数据的另类方法,使用exceptions into
- 简单工厂
- 春天的事务之9.3编程式事务 - 跟我学spring3
- Spring的事务 之 9.1 数据库事务概述 ——跟我学spring3
- 第十七周项目6——学生成绩统计(2)
- 算法练习--二分搜索哈希表-JS 实现
- hdu 1247 Hat’s Words