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

資訊專欄INFORMATION COLUMN

解決CBO對TABLE函數基數估算導致的性能問題

IT那活兒 / 856人閱讀
解決CBO對TABLE函數基數估算導致的性能問題

點擊上方“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那活兒”公眾號

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://m.specialneedsforspecialkids.com/yun/129208.html

相關文章

  • 宜信-運維-數據庫|SQL優化：一篇文章說清楚Oracle Hint的正確使用姿勢

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

    LeoHsiun 評論0 收藏0

發表評論

0條評論

IT那活兒

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<