Re: How to make a PL/SQL faster?

On Wed, 6 Dec 2000, Andor Gyula wrote:

> Hi Gurus !
>
> How could I make a PL/SQL faster. I've got two packages. In both packages there are procedures that
> perform string and PL/SQL table operations only. Beside these there is a TEST procedure that
> permorms 1000x an operation using the packeges mentioned above. This is for performace testing.
>
> The problem is the following:
> If the package owner executes the TEST procedure, then it completes very slow, but if any other user
> performs the TEST procedure it completes vert fast. I don't know what can be the cause of such a
> behavior. In both cases I use the SYS.DBMS_SHARED_POOL.KEEP to pin the packages and the standard
> packege into the memory.
>
> Any idea?
>

Set SQLTRACE on for the slow user, one of the fast users, and then compare SQL Execution Plans and IO stats.

That is may give you some clues.

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;) jkstill_at_teleport.com
jared_still_at_enron.net Received on Thu Dec 07 2000 - 09:41:18 CST