Re: TKPROF - SQL fast, PL/SQL slow?

From: <michael_bialik_at_my-deja.com>
Date: Tue, 18 Jan 2000 21:19:10 GMT
Message-ID: <862lbu$pte$1@nnrp1.deja.com>

In article <861vii$8lm$1_at_nnrp1.deja.com>, johnpaulcook_at_my-deja.com wrote:
> So is this reasoning valid:
>
> It takes 189 seconds to execute the package. It takes 82 seconds to
> execute the SQL statements in the package. By subtraction, it appears
> that it takes 107 seconds to execute the PL/SQL. Is this correct?
>

No it's not.

It take 107 seconds to execute the PL/SQL code and about 240000 ( 59000 * 2 * 2 ) context switches between PL/SQL and SQL processes. Try to rewrite the PL/SQL code to cut that number ( if you are using 8i - try BULK BIND/COLLECTs, otherwise it's going to be much more difficult ).
I had such a problem, but I was able to rewrite the PL/SQL code. The elapse time dropped from 8 min to 10 sec.

Is it possible for you to post package code?

HTH. Michael.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Jan 18 2000 - 15:19:10 CST