Questions on hard coded vs bind variable performance

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 Received on Mon Apr 04 2005 - 15:02:43 CDT