Re: Oracle recent version improves bind variable performance?

On Sep 18, 12:13 pm, Luch <DarthL...@xxxxxxxxx> wrote:
In another thread I was reporting how a query I do with bind variables
performs worse than an equivalent query done via strings.. One person
responded that a more recent version of Oracle improves upon this. I
started a new post because I wanted to ask about that... I definitely
want to know about this if it is true so I can try the new version.

I'm running Oracle 10.2.0.1.0 on Windows platform.

Is there a newer version or patch that makes Oracle perform queries
better, that use bind variables?

This was the previous post:

: We have a PowerBuilder application where we started noticing the
: performance of a query is worse if you do it with bind variables
then
: if you do the same query as a string.

Bind variables minimize the number of times a query must be parsed.
That
is likely to speed up your application if the same queries are used
multiple times.

Hard coded values allow the optimizer to select the best possible path
to
select the data. That is likely to speed up your application if a
query
is only run once, or if the time to parse the query is small compared
to
the time it takes to run the query, especially if the values are
unusual
compared to most of the data.

Apparently more recent versions of Oracle (version?) will examine the
values of bind variables before running a query as a sanity check that
the
existing parsed query will still be sensible for those values. I know
not
the exact details.

Oracle 10g (which includes 10.2.0.1) performs bind variable peeking on
the initial hard parse (checking the value of a bind variable, just as
if a constant were passed), and potentally on future parse calls from
the client. Oracle 10g also automatically creates histograms, which
help determine how frequently particular values occur in columns -
this can also influence the execution plan. If the first parse call
included bind variable values with uncommon values, a poor execution
plan may be used for future execution calls with different bind
variable values. If that frequently happens, it may be necessary to
disable bind variable peeking to regain performance.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

.

Relevant Pages

  • Re: Views - performance
    ... when u use stored object views oracle has to read the data dictionary ... this includes lots of steps for execution ... semantics of the query and fires it ... you'll possibly have any idea is to use explain plan to provide a base ...
    (comp.databases.oracle.server)
  • RE: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
    ... can pass strings as varchar's and have them inserted ... In theory I could write something to query the ... CLOB w/in Oracle is probably what is killing me... ...
    (perl.dbi.users)
  • Re: Equilevant of Oracle for optional records
    ... and a normalized t2 could be more useful, to build the query, and once this ... the Iqaluit Trauma: ... Oracle handles this data structure perfectly but I suspect it might be ...
    (microsoft.public.access.queries)
  • Re: Equilevant of Oracle for optional records
    ... this query is done, then flatten the result? ... example, the Iqaluit Trauma: ... Oracle handles this data structure perfectly but I suspect it might be ...
    (microsoft.public.access.queries)
  • DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
    ... can pass strings as varchar's and have them inserted ... In theory I could write something to query the ... CLOB w/in Oracle is probably what is killing me... ...
    (perl.dbi.users)