新炬網路定期推出“名師大講堂”專業IT技術知識分享，內容涉及Oracle資料庫、效能測試、軟體自動化測試等，與工作在技術前線的小夥伴們一起探討實踐中出現的技術難題，提供有效解決方案，大家透過交流共同成長

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

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------

|* 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

------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------

| 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走更優的計劃，從而提高效率。

【新炬網路名師大講堂】解決CBO對TABLE函式基數估算導致的效能問題

相關文章