Migration using database link Performance

alter database noarchivelog;

NOLOGGING APPEND noarchive log mode no redo
LOGGING no append noarchive log mode redo generated
NOLOGGING no append noarchive log mode redo generated



DB1 : LD
DB2: DPNSDL
Database link created at LD
Processes are running on DPNSDL DB.

Process:

Old procedure we just Select the data from DB1,DB2 and manipulate and insert into DB1. IT takes 5-6 hours

INSERT INTO LEDGER@DPLDLINK
INSERT INTO JOURNDC@DPLDLINK
INSERT INTO FALEDDESCRIPT@DPLDLINK
INSERT INTO JOURNDC@DPLDLINK

New procedure, we create temp table on DPNSDL same as LD where database will have to inserted. Manipulate and store the data into temp table and in last insert the data into LD using database link
It takes 20 min.

INSERT INTO TBLTEMPLEDGERSUM
          (COOWNCODE, NBALANCE)
             SELECT OOWNCODE, NVL(SUM(CAMOUNT - DAMOUNT), 0) AS BAL
             FROM LEDGER@DPLDLINK
WHERE FIRMNUMBER = ILDFIRMNO
AND DTOFTRAN <= TO_DATE(ILDBALDATE, 'dd/mm/yyyy')
GROUP BY OOWNCODE;

INSERT INTO TBLTEMPLEDGERSUM
INSERT INTO TBLTEMPLDLEDGER
INSERT INTO TBLTEMPJOURNDC
INSERT INTO TBLTEMPFALEDDESCRIPT




Changes

Create LD Ledger table into NSDL database and store it at NSDL and later insert into LD db



               NSDL Financial Transmission which was taking around 5 hours has been brought down to only 20 minutes at Aditya Birla. All programmers need remember one thing

1) Problem if it arises at clients place need not necessarily mean that there was some recent change in the program. The problem could be because the data size has increased considerably and there is a change required in query or pl/sql execution to complete the same.

The above is very important as once LD moves to Silverlight the onus would be on programming team. I would be coming out of programming. Remember LD is the flagship product of the company.

Kudos to the programming and support team to tackle the same and solve it on priority basis.




Generally speaking dblink performance limited by network speed, but there are some pitfalls, leading to performance issues:

unnecessary joins between local and remote tables that leads to transferring large amounts of data;
lack of parallelism built into the query (unions help in this case);
implicit sorting on remote database side;
failure to comply with Oracle recommendations such as using of collocated views and hints (mainly DRIVING_SITE and NO_MERGE).




NO_UNNEST hint


USE_NL


http://www.unitask.com/oracledaily/2012/03/01/tuning-query-with-database-link-using-use_nl-hint/
https://cn.forums.oracle.com/forums/thread.jspa?threadID=2256982


create sequence to get import speed

select * from dba_sequences where sequence_name



I was in a very large volume shop pulling very large volumes of data from a collector system to be rolled into reporting tables. They selected from materized view on remote collector system as it was more efficient way to build the result set, which was then inserted noappend into local temp table with no constraints from which data was rolled into local tables.


http://msutic.blogspot.in/2012/03/tuning-distributed-query-using-usenl.html
http://msutic.blogspot.in/2009/08/sql-tuning-using-usehash-hint-dblink.html


I once solved similar issue using USE_HASH hint (SQL Tuning - using USE_HASH hint - dblink issue) so I've tried with USE_NL hint in this case.
It helped! I've received result under a second.

Using USE_HASH hint I resolved network bottleneck as much less data was being sent over the network.

SELECT /*+ use_nl(cc cfp) */ *
  FROM TAB1 cc, TAB2@DB2 cfp
 WHERE cc.C_ID = cfp.B_ID AND cc.CODE = '1234567890'

use both local, remote db table in hint


following may slow

/*+ use_nl(CLDP LDFIBS) OPTIMIZER_FEATURES_ENABLE('10.2.0.4') */


----------What is OPAQUE_TRANSFORM Hint and how to Control it (Doc ID 780503.1)


-----------------------------------------Remote tables have a number of performance implications:

If all of the tables from which you are selecting are on the same remote database, then Oracle will get the remote database to run the query and send back the results. The only problems are the cost of shipping the results back, and the fact that you cannot see how the remote database is executing the query in Explain Plan (from Oracle 10g onwards, Explain Plan will get the remote database to plan its bit of the query and pass back the results to your Explain Plan). Make sure that the whole query is being performed remotely - the Explain Plan output should just have the one step - "REMOTE" - or in 10g+, every line of the plan contains the word "REMOTE".

If some tables are local - or you are joining tables over two or more remote databases - then Oracle will need to ship all of the data to one database before it can perform a join.

If you want to use an Indexed Nested Loop (low volume) join, then the outer (2nd) table cannot be remote - if it is you will get a full table scan and a hash join. You could use the DRIVING SITE hint to make the remote database the driving site of the query. Then the inner (1st) table will be sent over there, and an index can be used on the outer table. The remote database will then send back the results.

Even better, speak to your DBA about having the remote table(s) replicated on your database so that you can avoid the remote join altogether.