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

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