关于sql使用db_link查询的表或者视图的statistics的问题

leexi

我很想搞清楚其中的原理，但怎么都找不到与之相关的解释或者文档。
版本：Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
比如下面这个sql plan
SQL> SELECT API_DATA.PL_TXN_12MONTHS.MCV_ID,
  2           API_DATA.VW_CAMPAIGN_PRODUCT_SELECTION.PROD_TXN_CRITERIA_ID
  3      FROM ((   UAC_3663_8
  4             INNER JOIN
  5                API_DATA.PL_TXN_12MONTHS
  6             ON UAC_3663_8.MCV_ID = API_DATA.PL_TXN_12MONTHS.MCV_ID)
  7            INNER JOIN API_DATA.VW_CAMPAIGN_PRODUCT_SELECTION
  8               ON (API_DATA.PL_TXN_12MONTHS.PRODUCT_ID =
  9                      API_DATA.VW_CAMPAIGN_PRODUCT_SELECTION.PRODUCT_ID))
10     WHERE ( (API_DATA.VW_CAMPAIGN_PRODUCT_SELECTION.PROD_TXN_CRITERIA_ID =
11                 'A000001931')
12            AND (API_DATA.PL_TXN_12MONTHS.TRANSACTION_HEADER_DATE <
13                    (SYSDATE - 112)))
14  ORDER BY API_DATA.PL_TXN_12MONTHS.MCV_ID;

Execution Plan
----------------------------------------------------------
Plan hash value: 2728586350

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                               |     1 |    64 |  1625   (2)| 00:00:20 |        |      |
|   1 |  SORT ORDER BY                |                               |     1 |    64 |  1625   (2)| 00:00:20 |        |      |
|   2 |   NESTED LOOPS                |                               |       |       |            |          |        |      |
|   3 |    NESTED LOOPS               |                               |     1 |    64 |  1624   (1)| 00:00:20 |        |      |
|   4 |     MERGE JOIN CARTESIAN      |                               |     1 |    41 |   544   (3)| 00:00:07 |        |      |
|   5 |      REMOTE                   | VW_CAMPAIGN_PRODUCT_SELECTION |     1 |    29 |     2   (0)| 00:00:01 | DATAM~ | R->S |
|   6 |      BUFFER SORT              |                               |  1207K|    13M|   542   (3)| 00:00:07 |        |      |
|   7 |       TABLE ACCESS FULL       | UAC_3663_8                    |  1207K|    13M|   542   (3)| 00:00:07 |        |      |
|*  8 |     INDEX RANGE SCAN          | PL_TXN_TEMP_IX1               |  1260 |       |     4   (0)| 00:00:01 |        |      |
|*  9 |    TABLE ACCESS BY INDEX ROWID| PL_TXN_12MONTHS               |     1 |    23 |  1080   (1)| 00:00:13 |        |      |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access("PL_TXN_12MONTHS"."PRODUCT_ID"="PRODUCT_ID")
   9 - filter("PL_TXN_12MONTHS"."TRANSACTION_HEADER_DATE"<SYSDATE@!-112 AND
              "UAC_3663_8"."MCV_ID"="PL_TXN_12MONTHS"."MCV_ID")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   5 - SELECT "PROD_TXN_CRITERIA_ID","PRODUCT_ID" FROM "PLAN"."VW_CAMPAIGN_PRODUCT_SELECTION"
       "VW_CAMPAIGN_PRODUCT_SELECTION" WHERE "PROD_TXN_CRITERIA_ID"='A000001931' (accessing 'DATAMART2UNICA_PLAN' )


Note
-----
   - dynamic sampling used for this statement

step 5 中通过 db link 查询的那张表的 预估行数只有 1,导致了查询非常慢。
但建立通过创建tmp表来避免使用db link 之后， 查询非常的快。
SQL> select plan_table_output from table(dbms_xplan.display_cursor('7jathn5nngpfs'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7jathn5nngpfs, child number 0
-------------------------------------
select API_DATA.PL_TXN_12MONTHS.MCV_ID,
API_DATA.CAMPAIGN_PRODUCT_SELECTION_TMP.PROD_TXN_CRITERIA_ID from
((UAC_3663_g INNER JOIN API_DATA.PL_TXN_12MONTHS ON UAC_3663_g.MCV_ID =
API_DATA.PL_TXN_12MONTHS.MCV_ID) INNER JOIN
API_DATA.CAMPAIGN_PRODUCT_SELECTION_TMP ON
(API_DATA.PL_TXN_12MONTHS.PRODUCT_ID =
API_DATA.CAMPAIGN_PRODUCT_SELECTION_TMP.PRODUCT_ID)) where
((API_DATA.CAMPAIGN_PRODUCT_SELECTION_TMP.PROD_TXN_CRITERIA_ID =
'A000001933') AND (API_DATA.PL_TXN_12MONTHS.TRANSACTION_HEADER_DATE <
(SYSDATE - 118))) order by API_DATA.PL_TXN_12MONTHS.MCV_ID

Plan hash value: 11236953

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                            |       |       |       |   115K(100)|          |
|   1 |  SORT ORDER BY                 |                            |  3193 |   149K|       |   115K  (1)| 00:23:04 |
|*  2 |   HASH JOIN                    |                            |  3193 |   149K|  2288K|   115K  (1)| 00:23:04 |
|   3 |    TABLE ACCESS FULL           | UAC_3663_G                 | 97471 |  1142K|       |    54   (2)| 00:00:01 |
|   4 |    NESTED LOOPS                |                            |       |       |       |            |          |
|   5 |     NESTED LOOPS               |                            | 67165 |  2361K|       |   114K  (1)| 00:23:00 |
|*  6 |      VIEW                      | index$_join$_004           |   111 |  1776 |       |     8  (13)| 00:00:01 |
|*  7 |       HASH JOIN                |                            |       |       |       |            |          |
|*  8 |        INDEX RANGE SCAN        | CMPN_PRD_SELECTION_TEMPIX2 |   111 |  1776 |       |     1   (0)| 00:00:01 |
|   9 |        INDEX FAST FULL SCAN    | CMPN_PRD_SELECTION_TEMPIX1 |   111 |  1776 |       |     6   (0)| 00:00:01 |
|* 10 |      INDEX RANGE SCAN          | PL_TXN_TEMP_IX1            |  1203 |       |       |     3   (0)| 00:00:01 |
|* 11 |     TABLE ACCESS BY INDEX ROWID| PL_TXN_12MONTHS            |   604 | 12080 |       |  1036   (1)| 00:00:13 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("UAC_3663_G"."MCV_ID"="PL_TXN_12MONTHS"."MCV_ID")
   6 - filter("CAMPAIGN_PRODUCT_SELECTION_TMP"."PROD_TXN_CRITERIA_ID"='A000001933')
   7 - access(ROWID=ROWID)
   8 - access("CAMPAIGN_PRODUCT_SELECTION_TMP"."PROD_TXN_CRITERIA_ID"='A000001933')
  10 - access("PL_TXN_12MONTHS"."PRODUCT_ID"="CAMPAIGN_PRODUCT_SELECTION_TMP"."PRODUCT_ID")
  11 - filter("PL_TXN_12MONTHS"."TRANSACTION_HEADER_DATE"<SYSDATE@!-118)

Note
-----
   - dynamic sampling used for this statement


46 rows selected.

SQL>      

所有的统计信息都是收集过的，请问除了建立物化视图之外有什么办法可以使数据库正确的预估出db_link上的那张表的返回值呢？
如果能讲明下db_Link与统计信息的关系就更感激不尽了。

psufnxk2000

http://www.dba-oracle.com/t_sql_dblink_performance.htm

Maclean Liu(刘相兵

为什么要研究这个呢 ， 大多数人 认为 LOCAL 的optimizer都不需要研究。

对于这种remote查询本身很难保证 CBO准确 ，  类似的例如 如dblink下histogram可能无法正常作用。