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

Puzzled. Usually if I find a slow running query in a stored procedure, I lift it out and run it in sqlplus or TOAD etc and tune it from there.

Today I am looking at a query which, when declared in a procedure like this:

  for h in (select ......)
  loop
    ..
  end loop;

takes 3-4 seconds to parse and execute - i.e. if I stick a trace line before the "for" and after the "loop" line, the first row becomes available after 3-4 seconds.

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.

Not sure where to start in trying to find the cause of this discrepancy, would appreciate any pointers.

cheers

-- 
jeremy



 ============================================================

   ENVIRONMENT:                                             
   Oracle 9iR2 / Oracle HTTP Server / mod_plsql / Solaris 8 


 ============================================================