国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

解決CBO對(duì)TABLE函數(shù)基數(shù)估算導(dǎo)致的性能問題

IT那活兒 / 852人閱讀
解決CBO對(duì)TABLE函數(shù)基數(shù)估算導(dǎo)致的性能問題

點(diǎn)擊上方“IT那活兒”公眾號(hào)，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了！！！

TABLE函數(shù)在SQL中使用，可以將傳入的集合轉(zhuǎn)為普通表使用，與管道函數(shù)結(jié)合使用，往往能夠提高效率，然后在實(shí)際應(yīng)用過程中，發(fā)現(xiàn)CBO對(duì)TABLE函數(shù)的啟發(fā)式基數(shù)估算，往往會(huì)導(dǎo)致性能問題。某庫(kù)下面的SQL雖然單條運(yùn)行很快，但是運(yùn)行非常頻繁，嚴(yán)重消耗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
執(zhí)行計(jì)劃如下：
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))

問題分析與優(yōu)化建議

1. 主要原因使用了嵌套表+TABLE函數(shù)，ORACLE對(duì)TABLE函數(shù)的基數(shù)估算是固定值：返回8168行，這個(gè)值比較大，可以導(dǎo)致執(zhí)行計(jì)劃與其他表JOIN的時(shí)候可能走錯(cuò)，比如應(yīng)該走NESTED LOOPS的走成HASH JOIN，導(dǎo)致部分表無法使用索引訪問。
對(duì)于在SQL中適用嵌套表和TABLE函數(shù)的，如果TABLE函數(shù)實(shí)際返回的數(shù)據(jù)量較小，比如返回200行之下，在11g之前只能使用hint:cardinality、opt_estimate，而且必須手動(dòng)設(shè)置比較小的基數(shù)，比如100。11g可以適用動(dòng)態(tài)采樣，對(duì)table函數(shù)起作用.對(duì)應(yīng)hint:dynamic_sampling。通過以上設(shè)置，可以使TABLE函數(shù)與其它表JOIN可以走NESTED LOOPS，從而使用索引訪問。
當(dāng)然，這個(gè)設(shè)置的前提是實(shí)際情況下，大部分時(shí)候，TABLE函數(shù)返回的結(jié)果行數(shù)較少，如果返回的結(jié)果集行數(shù)接近或大于8168，那將基數(shù)設(shè)置為很小的值，也就失去了意義。ORACLE估算TABLE函數(shù)返回8168行，如下所示：
CBO估算TABLE函數(shù)的cardinality為8168行，這與實(shí)際情況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強(qiáng)制返回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強(qiáng)制返回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. 通過分析語(yǔ)句只訪問TABLE函數(shù)返回的行，為了做JOIN，使用了DISTINCT，是沒有必要的，而且會(huì)影響執(zhí)行計(jì)劃，這里通過EXISTS子查詢改寫。

優(yōu)化方案與效果

1. 優(yōu)化方案
使用cardinality hint，并且將語(yǔ)句中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. 優(yōu)化效果
優(yōu)化后buffer gets從4283到227，是原來的5.3%，時(shí)間也從原來的0.27到0.01。
優(yōu)化前走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))
優(yōu)化后的執(zhí)行計(jì)劃，走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)
總結(jié)與建議
如果SQL中使用TABLE函數(shù)，但是實(shí)際情況返回的行數(shù)比ORACLE估算的8168行少很多，使用ORACLE CBO的估算方式，導(dǎo)致不正確的執(zhí)行計(jì)劃，這時(shí)，就需要人工干預(yù)，使用HINT等方式(可以使用SQL PROFILE等)，讓SQL走更優(yōu)的計(jì)劃，從而提高效率。


本文作者：丁 俊(上海新炬王翦團(tuán)隊(duì)）

本文來源：“IT那活兒”公眾號(hào)

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址：http://specialneedsforspecialkids.com/yun/129208.html

相關(guān)文章

  • 宜信-運(yùn)維-數(shù)據(jù)庫(kù)|SQL優(yōu)化：一篇文章說清楚Oracle Hint的正確使用姿勢(shì)

    摘要：引導(dǎo)優(yōu)化器按照哈希掃描的方式從表中讀取數(shù)據(jù)。告訴優(yōu)化器強(qiáng)制選擇位圖索引。這個(gè)提示會(huì)使優(yōu)化器合并表上的多個(gè)位圖索引，而不是選擇其中最好的索引這是提示的用途。還可以使用指定單個(gè)索引對(duì)于指定位圖索引，該提示優(yōu)先于提示。 一、提示（Hint）概述 1、為什么引入Hint? Hint是Oracle數(shù)據(jù)庫(kù)中很有特色的一個(gè)功能，是很多DBA優(yōu)化中經(jīng)常采用的一個(gè)手段。那為什么Oracle會(huì)考慮引入優(yōu)化...

    LeoHsiun 評(píng)論0 收藏0

發(fā)表評(píng)論

0條評(píng)論

最新活動(dòng)
閱讀需要支付1元查看
<