This content has been marked as final. Show 20 replies
943276 wrote:Exactly. This is same what everybody else has been trying to convey. Bulk SQL will never perform better than Plain SQL. Plus you are wasting your expensive PGA in doing so and degrading your performance.
thanks Purvesh. Should I get rid of bulk operation with execute immediate over db_link because it is not possible at all wihthin the combination I'm asking about?
I would prefer, use of MViews and a process as below:
1. Create an Updatable MView for each of the tables.
2. Code to Perform the DML on MViews.
3. Fast Refresh to Push the data from Local DB to Remote (Master Site) DB
You negate, the Dynamic SQL and the Bulk operations.
Example as dynamic code supporting run-time specification of database link. Pushing the local connection into a data type that the remote cursor interface (DBMS_SQL) supports, is not particularly to my liking, as the local collection is duplicated locally in the PGA.
SQL> --// on remote db SQL> create table testtab( id number ); Table created.
You however need to consider how much more effective this is performance wise, versus a normal remote insert FOR loop ran by local PL/SQL code. The entire local collection needs to be pushed across - and that happens irrespective of the method used to get the local data into the remote database process.
SQL> --// on local database SQL> SQL> --// local SQL collection type used for bulk collection SQL> create or replace type TIdList is table of number; 2 / Type created. SQL> --// procedure that constructs and executes a remote cursor dynamically SQL> create or replace procedure RemoteInsert( dbLink varchar2, idList TIdList ) is 2 INSERT_TEMPLATE constant varchar2(32767) := 3 'declare 4 idList DBMS_SQL.number_table@$LINK; 5 cur integer; 6 rc integer; 7 begin 8 select column_value bulk collect into idList from TABLE(:idList); 9 cur := DBMS_SQL.Open_Cursor@$LINK( cur ); 10 DBMS_SQL.parse@$LINK( cur, ''insert into testtab(id) values( :id )'', DBMS_SQL.Native ); 11 DBMS_SQL.Bind_Array@$LINK( cur, ''id'', idList ); 12 rc := DBMS_SQL.Execute@$LINK( cur ); 13 DBMS_SQL.Close_Cursor@$LINK( cur ); 14 exception when OTHERS then 15 if DBMS_SQL.Is_Open@$LINK( cur ) then 16 DBMS_SQL.Close_Cursor@$LINK( cur ); 17 end if; 18 raise; 19 end;'; 20 21 plBlock varchar2(32767); 22 begin 23 plBlock := replace( INSERT_TEMPLATE, '$LINK', dbLink ); 24 execute immediate plBlock using in idList; 25 end; 26 / Procedure created. SQL> --// inserting a local collection via a remote bulk bind into remote table SQL> exec RemoteInsert( 'dev2', TIdList(0,1,2,3,4,5,6,7,8,9) ); PL/SQL procedure successfully completed. SQL> SQL> select count(*) from testtab@dev2; COUNT(*) ---------- 10 SQL>
The question is, does using this interface speed up the transfer of the collection? What it does using a bulk bind on the remote database for the remote insert - for data already on that remote database.
The data is transferred (in an implicit) fashion by the PL/SQL statement that calls the remote DBMS_SQL interface.
So all the above "tricks" to get the remote insert to run as a bulk process, may not address the actual transfer of local data to the remote process.
Instrumentation and analysis are important to determine if the above approach is actually more optimal. It is less flexible though, given the need for dynamic PL/SQL code.
@Purvesh, @Billy: I was thinking about bulk operations as an advantage over ordinary for loop, not pure SQL. Some pointed me to ordinary for loop solutions, which I know and can use, but I was wondering if there is possibility to take an advantage of bulk with the execute immediate (over db_link) instead. unfortunately I couldn't find simple "no, you cannot do this the way you suggest". Now when I'm clear about it I will consider other possibilities.
Thank you, I treat your opinion as great help. Along with Billy's answer you both made my day. I have to digest Billy's example now and consider his remarks about performance. Thanks to both of you!
I was wondering if there is possibility to take an advantage of bulk with the execute immediate (over db_link) instead. unfortunately I couldn't find simple "no, you cannot do this the way you suggest".
I provided a link which gave you your 'simple' answer. You just chose to either not read the article or didn't understand it.
If you read the article fully and carefully you will see this line in it:
ERROR at line 18: ORA-06550: line 18, column 4: PLS-00739: FORALL INSERT/UPDATE/DELETE not supported on remote tables
That is about as clear as it can be - directly from Oracle.
It is your own laziness and stubborness that delayed your enlightenment.