Re: performance problem

On 26 Aug 2002 03:26:07 -0700, daud11_at_hotmail.com (Daud) wrote:

>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

The first procedure fetches each record individually to the local database. Each fetch, of whatever size (in this case 1 record) requires a sqlnet roundtrip
Add the following to your code
execute immediate 'alter session set event=''10046 trace name context forever, level 12''';
and you will get a trace file which demonstrates this to you. The other procedure doesn't fetch anything to the local PC

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Aug 26 2002 - 07:06:53 CDT