Re: Slow running procedures

In article <1995Mar24.204455.15171_at_news.vanderbilt.edu>

           FOUGHTRL_at_CTRVAX.VANDERBILT.EDU "Randy Fought" writes:

: I have an overloaded procedure that runs quite well when
: called with one argument (performs an equijoin). However, when it is
: called with two arguments (performs a bounded range search) the
: procedure takes 30-40 times as long to run as the same exact SQL
: code entered by hand through SQL*DBA!

• snip ---

: When entering the above SQL code in by
: hand from SQL*DBA runs in about a minute where constants 12 and 13
: are used. However, executing RXCLM_LEV1(12,13) takes 35 minutes to
: run (returning the same result)!

I suspect your problem here is that the code you 'enter by hand' is _not_ considered to be the same as the code that runs when the procedure is called.

A bind variable in an SQL statement does not have exactly the same precedence to the optimiser as a literal variable: since the code sent to the database by the procedure will use bind variables, and the code sent by SQLDBA will use literal strings, the optimiser is taking two different paths.

Switch SQL_TRACE on for the two tests, then use tkprof with the 'explain' option to find out what the two access paths are.

--
Jonathan Lewis