Re: Similiar SQL statement, performance improve?

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/03/13
Message-ID: <35095ad8.30717651@www.sigov.si>#1/1

On Fri, 13 Mar 1998 20:19:04 -0800, Jimmy <c6635500_at_comp.polyu.edu.hk> wrote:

>Hello all,
>
> I know that a SQL statement is stored in the shared pool of the SGA
>once a statement is executed. The purpose is to improve the performace
>when the same SQL statement execute again.
>
> If SQL statement1 is : "select * from table1 where table1.att =
>'TEMP1'"
> and sumbitted to Oracle to execute.
>
> Then another SQL statement2 : "select * from table1 where table1.att =
>'TEMP2'"
>
> I want to ask, is statement2 gain the improvement after statement1 is
>executed?

No, it does not. In fact "improvement in performance" as you call it has nothing to do with the statement execution itself. Before oracle executes an SQL it must first parse the statement. So if the *exactly the same* statement allready exists in shared pool, oracle does not have to parse it again as its parsed version is allready there. But the two statements must be realy exact match. In your case, they differ ('TEMP1' <> 'TEMP2').

Use bind variables if you want to avoid unnececary parsing, which can improve performance significaly if the same statements are executing very frequently.

>Thanks,
>Jimmy

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Mar 13 1998 - 00:00:00 CST