Re: Slow response with packaged varaibles...

Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0307090530.6834303f_at_posting.google.com>...

> Carlos, Steve's observatcion that code using bind variables seems to...
> ...
> HTH -- Mark D Powell --

Dear Mark:

> Carlos, Steve's observatcion that code using bind variables seems to
> run slower than the same SQL statement written using constants is a
> commom occurrence.

Steve was talking about 'packaged' variables, not 'bind' variables, this is why I wrote ('bind') enclosed in parentheses, since if we use the 'packaged' variables the optimizer will act as if they were bind variables.

> ...The cost based optimizer, CBO, knows more about
> the data when a constant is present than what it can determine from
> the statistics when a bind variable is present even in the absense of
> histograms.

As I said, 'the advantages of using ('bind') variables come up when executing the same code multiple times with different values'. This is the 'ABC' of a well designed client software. (You can 'ask Tom', but I'm not sure of the kindness of his answer: he has been holding a crusade in order to everyone to understand this). The CBO does NOT act considering the VALUES of the items used in the predicates, but considering HOW are the statements constructed (among other factors).

> The explain plans for the same SQL statement substituting bind
> variables for constants will often be different. Many developers make
> the mistake of explaining SQL using constants where bind variables
> appear in the code. They are not the same statement.

I know that, but I also know that when you use bind variables the optimizer generates one execution plan and then uses it for all the same queries no matter if the variable values are the same or not, avoiding some parsing too.
Moreover, is more likely that a hard coded query falls off the memory (LRU) than a query that has bind variables instead .

> Note version 9 has a bind variable value peek feature where on first
> execution the plan is adjusted for the value found in the variable.
> Which may or may not be a good think depending on the data value skew.

Yes, 9i takes the work that programmers should do: making bind variables statements from expressions made up with constant values.

I hold on with my opinion about Steve's tests: 'the difference is too big to be only a HARDCODE/NOT HARCODE problem'. I think my own example is clear enough.

Greetings. Received on Thu Jul 10 2003 - 07:30:31 CDT