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: Questions on hard coded vs bind variable performance

Re: Questions on hard coded vs bind variable performance

From: <dave_93_98_at_yahoo.com>
Date: 5 Apr 2005 07:47:12 -0700
Message-ID: <1112712432.288875.105470@f14g2000cwb.googlegroups.com>

Jonathan Lewis wrote:
> 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
>
>

Jonathan,
Thank you very much for the tip on displaying the execution plan with bind variables -
> And txntime between :b1 and :b2

This allowed me to see the plan and I was able to add the correct hints and speed up the execution to mirror the hard coded performance.

Dave Received on Tue Apr 05 2005 - 09:47:12 CDT

Original text of this message

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