Note in-line
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st
"Allan Streib" <streib_at_cs.indiana.edu> wrote in message
news:e334e4a7.0406221231.1da87dad_at_posting.google.com...
>
> Is there any reason that a static query would run orders of magnitude
> faster than the same query in a stored proc, with parameters for some
> of the "WHERE" conditions?
>
Yes - in the absence of literal values, and depending on
Oracle version - the predicted number of rows in each
step of the plan could vary significantly; which could
cause a dramatic change in execution path.
> Can I view the query plan for a stored proc? I'd like to compare it
> to the one for my static query.
>
Alter session set sql_trace = true;
Call the proc
Run the straight sql
exit from SQL*Plus
Check the trace file (which will be in the
user_dump_dest directory). Use tkprof
to format the trace file, and you will probably
find that the two executions of the SQL
used different execution plans.
Received on Wed Jun 23 2004 - 16:05:58 CDT
