OraFAQ Forum: Performance Tuning » Bind vs non-Bind query performance

Home » RDBMS Server » Performance Tuning » Bind vs non-Bind query performance

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Bind vs non-Bind query performance [message #147709]

Sat, 19 November 2005 18:23

sbushong@cox.net
Messages: 1
Registered: November 2005

Junior Member

Does anyone have experience with queries that perform much worse when using bind variables than when using specific values in the "where" clause? We have a TCL (oratcl OCI) based application that has some fairly complicated queries. We almost always use variable binding, but have come across a few queries that perform much better (instantaneous response vs many seconds or even minutes) when we remove the bind variables and just submit the queries with actual values. These are always dynamically constructed queries with "where" values unknown until actual query execution.

Report message to a moderator

Re: Bind vs non-Bind query performance [message #147721 is a reply to message #147709]

Sun, 20 November 2005 00:45

scottwmackey
Messages: 515
Registered: March 2005

Senior Member

Yes, all the time. I know for certain that Oracle creates different explain plans when the parameters are hard coded, often giving exactly the different results you are finding. As I hear it, if Oracle knows the values it is going to process, it looks at different statistics for the indexes and can better decide which one to use. I don't know exactly how that works, but I know it happens...all the time.

Report message to a moderator

Re: Bind vs non-Bind query performance [message #147747 is a reply to message #147721]

Sun, 20 November 2005 16:33

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

What happens is that the CBO looks at the histograms for the columns and determines whether there is likely to be a lot of matching rows or just a few for the given constant value.

With bind variables, it has to go on averages alone.

A classic example is a table of payments. Paid transactions will have a CHEQUE_NO set to a unique value for the table, but unpaid transactions may have a default value, eg. 0.

A query WHERE CHEQUE_NO = 0 would be better performing a Full Table Scan, whereas WHERE CHEQUE_NO = 12345 would be better using an index. Using histograms, Oracle can tell that the value "0" has terrible selectivity and adjusts the plan accordingly. Using bind variables (WHERE CHEQUE_NO = :1), the CBO will go on averages, which means using the index, even though the bind value may be 0.

This shows a bit more on histograms.

_____________
Ross Leishman

Report message to a moderator

Re: Bind vs non-Bind query performance [message #147799 is a reply to message #147709]

Mon, 21 November 2005 02:38

dmitry.nikiforov
Messages: 723
Registered: March 2005

Senior Member

Seems you are using Oracle 9i. This release uses techique of
bind variable value pick-up. This can cause some confusion when
variable value doesn't give the representative plan. Use V$SQL_PLAN view to look on cached execution plan for your query with binding variable.

Rgds.

Report message to a moderator