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]
