Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ref cursor slow compared to sql query

Re: ref cursor slow compared to sql query

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 23 Jun 2004 21:05:58 +0000 (UTC)
Message-ID: <cbcrbm$i57$1@sparta.btinternet.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US