>use of using bind variables. For transactions and the like they are fine
>but in the case of reporting, bind vars can wreak havoc.
>
>The over simplified explanation is that bind variables can inhibit the
>work of Oracles optimizer in a couple ways. The optimizer ends up using
>heuristics to generate the best explain plan rather than using
>information about data distribution. (from stat tables etc)
>
>The problems may have been exaggerated in our case considering the sheer
>volume of data (and DB links) we have...but nonetheless we've seen
>dramatic improvements in response time after removing all bind
>variables! (couple minutes down to seconds!)
>
>See Oracle technet for additional info!
>
Can you provide any links to this technet info? TechNet is huge and their
search features stink. We use Oracle exclusively and haven't had any big
performace issues. In fact, our stability *improved* when we converted some
legacy non-cfqueryparam-ed queries to use cfqueryparam. I've never heard of
the negative effects you describe, though you have certainly peaked my
interest.
Also, is the bind variable issue with any particular set of JDBC drivers?
The DataDirect that ships with MX? The ones directly from Oracle? Type IV?
Thanks for any info you can provide.
Regards,
Dave.
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
