DBlink的性能问题

关于DBlink的性能问题：

原则：把DBlink建立在数据量小的数据库中，减少DBlink的数据连接。

• 对于表关联的写法

例如：

表名数据量数据库cux_msc_plan_data1000EBSmsc_full_pegging100000000ASCP

EBS中速度快的写法：

在EBS中创建ASCP的DBlink，然后使用DBlink连接。

SELECT *

FROM cux_msc_plan_data cmpd, msc_full_pegging@ascp mfp

WHERE cmpd.ascp_plan_id = mfp.plan_id

AND cmpd.transaction_id = mfp.transaction_id

ASCP中速度慢的写法

SELECT *

FROM cux_msc_plan_data@EBS cmpd, msc_full_pegging mfp

WHERE cmpd.ascp_plan_id = mfp.plan_id

AND cmpd.transaction_id = mfp.transaction_id

• 对于关联视图的写法(存在EBS的表和ASCP环境的表进行数据连接的时候)

同理：如果我们写了一个视图cux_msc_orders_v从ASCP中获取数据

如果我们把视图建立在ASCP环境中，然后使用DBlink去连接使用，这样速度就非常的慢.

SELECT *

FROM cux_msc_plan_data cmpd, msc_full_pegging@ascp mfp,cux_msc_orders_v@ascp mso

WHERE cmpd.ascp_plan_id = mfp.plan_id

AND cmpd.transaction_id = mfp.transaction_id

AND nvl(mfp1.transaction_id, mfp.demand_id) = mso.transaction_id

AND mso.order_type <> 16

AND mso.plan_id = g_ascp_plan_id

AND mfp.allocated_quantity >= 1

AND mso.category_set_id = 1

如果我们把视图建立在EBS环境中，然后基表使用DBlin连接，这样速度就非常的快.

SELECT *

FROM cux_msc_plan_data cmpd, msc_full_pegging@ascp mfp,cux_msc_orders_v mso

WHERE cmpd.ascp_plan_id = mfp.plan_id

AND cmpd.transaction_id = mfp.transaction_id

AND nvl(mfp1.transaction_id, mfp.demand_id) = mso.transaction_id

AND mso.order_type <> 16

AND mso.plan_id = g_ascp_plan_id

AND mfp.allocated_quantity >= 1

AND mso.category_set_id = 1

当所有的数据都来自DBlink的环境ASCP的时候，则把视图建立在ASCP环境，则速度更快。

SELECT *

FROM cux_msc_orders_v@ascp mso,

msc_system_items@ascp msi,

msc_full_pegging@ascp mfp1

WHERE 1 = 1

AND mso.category_set_id = g_category_set_id

AND mso.sr_instance_id = 1

AND mso.order_type <> 60

AND - 1 = -1

AND mso.source_table = 'MSC_SUPPLIES'

AND mso.order_type IN (5, 18, 3, 7)

AND msi.plan_id = -1

AND msi.sr_instance_id = mso.sr_instance_id

AND msi.organization_id = g_mst_organization_id

AND msi.planning_make_buy_code = 1

AND msi.inventory_item_id = mso.inventory_item_id

AND nvl(mso.source_organization_id, mso.organization_id) = mso.organization_id

AND nvl(mso.source_sr_instance_id, mso.sr_instance_id) = mso.sr_instance_id

AND mso.plan_id = p_msc_plan_id

AND mfp1.transaction_id(+) = mso.transaction_id

AND mfp1.sr_instance_id(+) = mso.sr_instance_id

AND mfp1.organization_id(+) = mso.organization_id

AND mfp1.plan_id(+) = mso.plan_id