Oracle's optimizer. We've tried to 'beat it' in the past...and there
were just to many variances on how these reporting queries could be
executed. In the end we were a touch faster in a few areas...much worse
in many others.
Cheers,
Stace
_____
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 05, 2004 4:32 PM
To: CF-Talk
Subject: RE: RESOLVED - FYI : Oracle 9i and cfqueryparam problems
It was my understanding (and I could be wrong) that if you were using
hints in your select statements to force oracle to use whatever indexes
(or whatever) you wanted, using bind variables should be okay. I've
been able to test this using the plan table, then tracing active
sessions using Bind variables, and they seem to be following my orders.
Have you tried forcing the use of optimizations via /*+ index(table
index_name) */ or the like?
-----Original Message-----
From: Stacy Young [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 05, 2004 3:19 PM
To: CF-Talk
Subject: RESOLVED - FYI : Oracle 9i and cfqueryparam problems
Wow, folks using CF for reporting w/ Oracle DBs should re-evaluate the
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!
Cheers,
Stace
________________________________________
From: Stacy Young
Sent: Tuesday, May 04, 2004 3:48 PM
To: CF-Talk
Subject: Oracle 9i and cfqueryparam problems
Has anyone had any issue using TIMESTAMP as the datatype for bind
variables when using cfmx 6.1 with Oracle 9i? It seems we're getting
queries that run for very long periods of time...if we take out the bind
variables...query only takes a few seconds!
-Stace
________________________________________
_____
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
