performance problem

From: Daud <daud11_at_hotmail.com>
Date: 26 Aug 2002 03:26:07 -0700
Message-ID: <f0bf3cc3.0208260226.32fa7dba@posting.google.com>

Hi

I have this problem which I still cannot solve / explain why. It is about doing stuff over a slow db link. Pls see below.

rgds
Daud

BTW. Oracle version = 8.1.7.0.0 for both local and remote databases.

• the table below is created at both remote and local databases

create table testtab
(
 seq number(9) Primary Key,

 col1 varchar2(10),
 col2 varchar2(10),
 col3 varchar2(10)

)
/

• the table at remote database is prep with 500 records

begin
  for i in 1..500 loop

        insert into testtab values (i, 'abc','def','ghi');   end loop;
  commit;
end;
/

• there is a db link PDCDB that links local to remote
• select all records from remote table took just over 6 seconds

SQL> select * from testtab_at_PDCDB;

       SEQ COL1 COL2 COL3
---------- ---------- ---------- ----------

         1 abc        def        ghi
         2 abc        def        ghi
         3 abc        def        ghi
         4 abc        def        ghi
cut cut cut
       496 abc        def        ghi
       497 abc        def        ghi
       498 abc        def        ghi
       499 abc        def        ghi
       500 abc        def        ghi

500 rows selected.

Elapsed: 00:00:06.83
SQL>

• the procedure below is created in the local schema
• the procedure below took over 1 minute and 31 secs to complete. WHY???

create or replace procedure refresh_testtab is

	cursor c1 is
		select * from testtab_at_PDCDB;

	r1	c1%ROWTYPE;

begin

        dbms_output.put_line('Extraction started on :'||to_char(sysdate,'DD-MON-YY HH:MI:SS AM'));         

	open c1;
	loop
		
		fetch c1 into r1;
		exit when c1%NOTFOUND;

		insert into testtab values (r1.seq, r1.col1, r1.col2, r1.col3);	
	
	end loop;
	close c1;

	commit;

	dbms_output.put_line('Extraction completed on

:'||to_char(sysdate,'DD-MON-YY HH:MI:SS AM')); end;
/

SQL> select count(*) from testtab;

  COUNT(*)

---

         0

Elapsed: 00:00:00.00
SQL> execute refresh_testtab;
Extraction started on :26-AUG-02 04:44:47 PM Extraction completed on :26-AUG-02 04:46:19 PM

PL/SQL procedure successfully completed.

Elapsed: 00:01:31.75

• this one is fast
  --
  create or replace procedure refresh_testtab is

	cursor c1 is
		select * from testtab_at_PDCDB r
		where not exists
		(select 'x' 
			from testtab l
			where l.seq = r.seq
		);

	r1	c1%ROWTYPE;

begin

        dbms_output.put_line('Extraction started on :'||to_char(sysdate,'DD-MON-YY HH:MI:SS AM'));         

	open c1;
	loop
		
		fetch c1 into r1;
		exit when c1%NOTFOUND;

		insert into testtab values (r1.seq, r1.col1, r1.col2, r1.col3);	
	
	end loop;
	close c1;

	commit;

	dbms_output.put_line('Extraction completed on

:'||to_char(sysdate,'DD-MON-YY HH:MI:SS AM')); end;
/

SQL> truncate table testtab;

Table truncated.

Elapsed: 00:00:00.02
SQL> select count(*) from testtab;

  COUNT(*)

---

         0

Elapsed: 00:00:00.00

SQL> execute refresh_testtab;
Extraction started on :26-AUG-02 04:42:15 PM Extraction completed on :26-AUG-02 04:42:17 PM

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.71

I dont understand. Why is the second method (with the 'where not exists') so much faster than the first method? Received on Mon Aug 26 2002 - 05:26:07 CDT