Re: Performance probs with db_links in PL/SQL

If you are running pre 8.0.5.1 you might also check for memory leaks - there are some pretty ferocious ones with PL/SQL and dblinks.

Your symptoms sound a little extreme though.

Look at ps -efl for the number of memory pages, or look at v$sesstat, statistic# = 16 (max uga memory) or statistic# = 21 (max pga memory).

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Jonathan Lewis wrote in message
<930864003.4702.0.nnrp-07.9e984b29_at_news.demon.co.uk>...
>
>Just a guess, but this might be a TCP_NODELAY
>problem. Try a very simple test with a table of
>1 column varchar2(2000). Copy it using the PL/SQL
>loop with 1300 bytes used, and then with 1500 bytes
>and see if you get the same performance drop.
>
>--
>
>Jonathan Lewis
>Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>
>Dwight Walker wrote in message <7leaai$ffd$1_at_news.mel.aone.net.au>...
>>I have a procedure that has a cursor for loop that basically selects all
>>columns from a remote ( but on the same physical system ) database and
>>inserts them into a local table.
>>
>>It takes hours to insert 100,000 rows using the PL/SQL procedure, but a
>>couple of minutes via a " create table as select .... " or "insert into
>>table select..." SQL statements. There are no indexes on the local
tables,
>>and no indexes used on the remote table as it is selecting every row
>anyway.
Received on Thu Jul 01 1999 - 16:28:53 CDT