Re: Questions on hard coded vs bind variable performance

An execution plan is only dumped in the
trace file when the cursor closes; and when you run from a pl/sql block, the cursor can be held open long after the block ends.

Try executing the block with trace enabled, and then exit from your session. The cursor will close as the session ends, and the plan should be in the trace file.

Alternatively, since txntime is a character column, you should get the correct run time plan by doing:

explain plan
for
Select trn.lot, MAX(txn_id) As txn_id
From transaction trn, history his
Where trn.his_ID = his.his_ID
And txntime between :b1 and :b2
Group by trn.lot

Check $ORACLE_HOME/rdbms/admin/utlxpls.sql for a script to report the execution plan.

The main reason for a change in plan would be that with the pl/sql, Oracle 8.1 sees only bind variables, and uses a fixed selectivity (0.25% for the between) to estimate the
fraction of the table returned by your range predicate. With constants, Oracle will do some arithmetic based on low/high values for the column compared to the supplied values. This will probably be way off mark because you are storing dates as character strings.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005






<dave_93_98_at_yahoo.com> wrote in message 
news:1112644963.186128.234850_at_f14g2000cwb.googlegroups.com...


> Hello,

> I am currently using Oracle 8.1.7 on a sun sparc computer. I have a

> query that runs extremely fast with hard coded variables in the where

> clause for date parameters (which are actually char(18)). When I change

> this query to be in an anonymous sql block (or a procedure which takes

> start and end times) it suddenly takes 30-40 times as long.

>

> e.g. Switching the below And clauses makes an incredible difference in

> performance.

>

>  Select trn.lot, MAX(txn_id) As txn_id

>  into nLot, nID

>  From transaction trn, history his

>  Where trn.his_ID = his.his_ID

> --  And txntime between cStartTime and cEndTime

>  And txntime between '20040904 000000000' and '20040905 000000000'

>  Group by trn.lot

>

> I can easily grab the explain plan for the hard coded version, however

> when I run this as an anonymous block with sql_tuning = true the tkprof

> output doesn't show the execution plan "tree" just the parse, execute,

> and fetch statistics. Is there something else I have to do in tkprof to

> generate more detailed output (it does show a tree for simple sql)? Has

> anyone else run into variables causing such a radical change in

> performance?

>

> Thanks,

> Dave

>