Re: Why woulld a query run with different performance inside/outside of a stored procedure?

"Jeremy" <jeremy0505_at_gmail.com> schreef in bericht news:MPG.201030a8b6838e7198a3c4_at_news.individual.net...
> In article <45a619fd$0$335$e4fe514c_at_news.xs4all.nl>, What's in a
> namespace says...
>> >
>> > Lifting the SQL out and running in sqlplus, it takes < 0.5 seconds and
>> > has a pretty good explain plan. The query has one condition based on a
>> > parameter in the plsql procedure which, when run in sqlplus, is
>> > replaced
>> > by a constant - other than that the SQL is identical.
>>
>> You should replace this by a binding variable! See what happens then!
>>
>>
>
> As I understand it bind variables increase efficiency - therefore I
> guess the use of a constant in sqlplus shoud, if anything, have a
> detrimental impact on performance?
>
> Anyway, done. Same result (i.e. sub 1-second).
>
> DECLARE p_candidate_id NUMBER:=311662;
> BEGIN
> dbms_output.put_line(TO_CHAR(SYSDATE,'hh24:mi:ss'));
> FOR h IN (select ....
> LOOP
> NULL;
> END LOOP;
> dbms_output.put_line(TO_CHAR(SYSDATE,'hh24:mi:ss'));
> END;
>
> So why should this perform so poorly in the stored procedure versus when
> run as a SQL statement or as an anonymous pl/sql block from within a
> client tool?
>
> Not actually seeking the answer here but at least an indication of where
> I should be looking.
>
> --
> jeremy
>

Sorry, Jeremy, I read your post the wrong way around. But still I think you should use a bind variable in SQLPLUS (select ... from ... where column_x= :A) in stead of a contant to compare the performance. But I have no further indication for clues...

Shakespeare Received on Thu Jan 11 2007 - 06:03:02 CST