Dblink if it's slow, you can optimize it in this way.

Find Dblink If very slow can add a/*+driving_site (XXX) in this way */, query quickly

DBLINK HINT/*+ driving_site (org) */optimization strategy and ideas

Optimization strategies and ideas for using the Dblink remote Access database:

Strategy: Remote access, network transmission accounted for a large part of the principle of optimization, reduce network transmission, the small result set to local processing;

Idea 1: Build a view at the far end;

Idea 2: Use Driving_site to pull data to local processing;

The main reason for this implementation plan is the use of the DB link, to see the execution plan, you can add hint, such as:
/*+remote_mapping (db_link) */or/*+DRIVING_SITE (table_name) */


Usage:/*+driving_site (TABLE_NAME) * *: table_name is usually a big watch!

In a recent database query statistic, you need to correlate tables with db_link across libraries, where a table with more than millions of data in a library is associated with a table in B library that has thousands of records. The fields that are associated with both tables are indexed. However, in the actual query if the two tables from the B Library Association will result in a library of large table full table scan, query slow is secondary, the key is that the full table scan will affect the normal operation of a library.

Through the continuous adjustment of SQL statements, optimization found that when the two tables for a simple association query, if the "*" after the Select to obtain all the data, the query will not cause a full table scan, but the select after the specific field will result in a large table full table scan.

Group grouping on the basis of simple query will directly result in large table full table scan, in which case, all the statement optimization techniques will be invalidated, because the index is invalid, at this time must take a special method to establish the index of the associated query, forcing the specified in the Association process that table as the main driver table, that one as from the table. In the process of correlating queries across libraries, because tables are not in the same database (and on servers of different operating systems), it causes the association process to extract data from one table to another database. At this point, if the large table data extracted into the library of the small table in the association process will result in large table full table scan, the whole operation process will be very slow and affect the normal operation of a library.


The final solution to a full-table scan is to enforce the specification of the main driver table by Driving_site when the B-Library is associated with two size tables in different databases, that is, with the table specified as the primary table, and the other table as the library from which the table is extracted to the drive table. The specific statements are as follows:


Select/*+driving_site (Main) */a.*,b.* from a.a [email protected],b.b minor where main.id=minor.id and ....

This avoids the full table scan of the library of the large table, and the query speed will be increased at a level of magnitude.

Explain:


When you specify/*+driving_site (main)//, Oracle will get all the data from the minor table to the database where the main table is located to perform the associated operation. (index works, fast)
If you specify/*+driving_site (minor)//, Oracle will get all the data from the main table to the database where the minor table is located for the associated operation. (Index failed, full table scan)


Of course, Oracle has a lot of keywords used to specify the associated query when the operation method, and so on, followed by specific application examples are described.


Reference:


The Driving_site effect is similar to ORDERED. Driving_site are typically used in distributed queries. Without this hint, Oracle will first retrieve from a remote point and connect them to the local site. By using Driving_site, we can retrieve the retrieved data locally and send it to the remote node for connection

Optimization case:

--create or replace view v_fasp_bjjzzfyszxqkfld2 as
Select A.bdgmanagedivision,a.gnkm,acctdate,sum (A.YSZB) yszb,sum (A.ZJZFJH) zjzfjh, sum (A.SQZFJH) SQZFJH,
SUM (A.ZJZFZC) zjzfzc,sum (A.SQZFZC) sqzfzc,sum (A.ZJZFJHJY) zjzfjhjy,sum (A.SQZFJHJY) sqzfjhjy
From
(select/*+ driving_site (t) */T.mb_code as BDGMANAGEDIVISION,SUBSTR (t.bs_code,1,3) gnkm,substr (t.create_date,1,4) | | substr (t.create_date,6,2) | | SUBSTR (t.create_date,9,2) as Acctdate,
SUM (T.budget_money) yszb,0 as Jhje, 0 as zjzfjh,0 as sqzfjh,0 as zcje,0 as zjzfzc,0 as SQZFZC,
--0 as zcjezt,0 as zjzfzczt,0 as SQZFZCZT,
0 as jhjy,0 as zjzfjhjy,0 as Sqzfjhjy
--0 as jhkyje,0 as zjzfjhkyje,0 as Sqzfjhkyje
from [email Protected]_link t
Where T.en_code not like ' 910% '
--and t.pk_code like ' 1% '
and t.bo_code= ' 001001 '
and T.is_end =1
and t.is_valid=1
and t.bl_code like ' 001001% ' and t.budget_vou_id are not null for GROUP by T.MB_CODE,SUBSTR (t.bs_code,1,3),
substr (t.create_date,1,4) | | substr (t.create_date,6,2) | | SUBSTR (t.create_date,9,2)--payment system input Index
UNION ALL
Select C.mb_code bdgmanagedivision,substr (c.bs_code,1,3) gnkm,plan_pay.acctdate,
0 as YSZB,
NVL (plan_pay.zj_plan_money+plan_pay.sq_plan_money,0) Jhje,
NVL (plan_pay.zj_plan_money,0) ZJZFJH,NVL (plan_pay.sq_plan_money,0) Sqzfjh,
NVL (plan_pay.zj_pay_money+plan_pay.sq_pay_money,0) Zcje,
NVL (plan_pay.zj_pay_money,0) ZJZFZC,NVL (plan_pay.sq_pay_money,0) SQZFZC,
NVL (Plan_pay.zj_plan_money+plan_pay.sq_plan_money)-(Plan_pay.zj_pay_money+plan_pay.sq_pay_money), 0) Jhjy,
NVL (plan_pay.zj_plan_money,0)-NVL (plan_pay.zj_pay_money,0) Zjzfjhjy,
NVL (plan_pay.sq_plan_money,0)-NVL (plan_pay.sq_pay_money,0) sqzfjhjy
--20130507 Change Gl_balance to (select Sum_id,ccid from Gl_balance_month_detail where set_month>0 Group by Sum_id,ccid) G
--settlement Plan quota ID number 953119, June plan, new quota, there is no corresponding record in the Gl_balance table.
From (select/*+ driving_site (c) */SUM_ID,CCID,RCID from [e-mail protected]_link where set_month>0 GROUP by sum_id,ccid , Rcid) G,
[Email Protected]_link c,
(Select Plan.toctrlid,plan.acctdate acctdate,
SUM (Zj_plan_money) Zj_plan_money,
SUM (Sq_plan_money) Sq_plan_money,
SUM (Zj_pay_money) Zj_pay_money,
SUM (Sq_pay_money) Sq_pay_money
From
--Query plan data
(select/*+ driving_site (P) */P.TOCTRLID,SUBSTR (p.create_date,1,4) | | substr (p.create_date,6,2) | | SUBSTR (p.create_date,9,2) as Acctdate,
SUM (case if p.pk_code like ' 11% ' then P.plan_money else 0 end) Zj_plan_money,
SUM (case if p.pk_code like ' 12% ' then P.plan_money else 0 end) Sq_plan_money
from [email protected]_link p, [email Protected]_link g
where p.id=g.vou_id and P.is_valid=1 and G.is_end=1 and g.set_month>0 and g.billtype_code not like ' 1% '
And p.budget_vou_id is not NULL
Group by P.TOCTRLID,SUBSTR (p.create_date,1,4) | | substr (p.create_date,6,2) | | SUBSTR (p.create_date,9,2)
) Plan,
--Querying expense data
(select/*+ driving_site (P) */P.FROMCTRLID,SUBSTR (p.create_date,1,4) | | substr (p.create_date,6,2) | | SUBSTR (p.create_date,9,2) as Acctdate,
SUM (case if (P.pk_code like ' 11% ' and p.clear_date are not null and g.is_end=1) then P.pay_money else 0 end) Zj_pay_money ,
SUM (case if (P.pk_code like ' 12% ' and p.clear_date are not null and g.is_end=1) then P.pay_money else 0 end) Sq_pay_money
from [email protected]_link p, [email Protected]_link g
where p.id=g.vou_id (+) and p.is_valid=1 and g.set_month>0 and g.billtype_code like ' 3% '
And p.budget_vou_id is not NULL
Group by P.FROMCTRLID,SUBSTR (p.create_date,1,4) | | substr (p.create_date,6,2) | | SUBSTR (p.create_date,9,2)
) Pay
where Plan.toctrlid=pay.fromctrlid (+)
GROUP BY Plan.toctrlid,plan.acctdate
) Plan_pay
where G.sum_id=plan_pay.toctrlid and G.sum_id=c.toctrlid and G.rcid=c.rcid--and g.ccid=c.ccid and G.set_month>0
and (plan_pay.zj_pay_money<> ' 0 ' or plan_pay.sq_pay_money<> ' 0 ' or plan_pay.zj_plan_money<> ' 0 ' or Plan_pay.sq_plan_money<> ' 0 ')
and C.en_code not like ' 910% '
and c.bo_code= ' 001001 '
and c.bl_code like ' 1001% '
and exists (select/*+ driving_site (org) */1 from [email protected]_link org where org.user_id = ' 7569 ' and org.org_id = C.MB_ID) and 1=1
) a
Group BY a.bdgmanagedivision,a.gnkm,a.acctdate Order by A.BDGMANAGEDIVISION,A.GNKM;

Dblink if it's slow, you can optimize it in this way.