Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2844925
  • 博文数量: 200
  • 博客积分: 2413
  • 博客等级: 大尉
  • 技术积分: 3067
  • 用 户 组: 普通用户
  • 注册时间: 2011-04-01 22:07
文章分类

全部博文(200)

文章存档

2018年(2)

2017年(8)

2016年(35)

2015年(14)

2014年(20)

2013年(24)

2012年(53)

2011年(44)

分类: Oracle

2014-12-26 14:21:56

       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走更优的计划,从而提高效率。


阅读(2129) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~