Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
RESOLVED - FYI : Oracle 9i and cfqueryparam problems
Author: Stacy Young
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32344#162488
Yes but at that point you're overlapping the optimizer's
functionality...and as soon as u start down the road of manual
hints...you're on your own. We haven't had much luck going that route.
-Stace
_____
From: Jason.Gulledge@alltel.com [mailto:Jason.Gulledge@alltel.com]
Sent: Thursday, May 06, 2004 2:58 PM
To: CF-Talk
Subject: RE: RESOLVED - FYI : Oracle 9i and cfqueryparam problems
You know, we do something VERY similar...we partition by month... I do a
check at execution time because everytime someone queries the data its
for a given month...and the month just so happens to coordinate with the
partition name, so I can somewhat dynamically decide which
index/partition to use. Its *VERY* helpful.
Jason
Author: Jason.Gulledge
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32344#162460
You know, we do something VERY similar...we partition by month... I do a check at
execution time because everytime someone queries the data its for a given
month...and the month just so happens to coordinate with the partition name, so I
can somewhat dynamically decide which index/partition to use. Its *VERY*
helpful.
Jason
We use bind variables extensively as well. The only case in which they
can cause negative side effects relating to reporting. Please see my
last post for more detailed summary info.
In our case the effects were devastating due to our partitioning (we
partition by month)...it was causing full table scans on enormous
tables. (tens of millions of rows)
-Stace
_____
Sent: Thursday, May 06, 2004 12:21 PM
To: CF-Talk
Subject: RE: 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
----- Excess quoted text cut - see Original Post for more -----
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.
_____
_____
Author: Stacy Young
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32344#162449
We use bind variables extensively as well. The only case in which they
can cause negative side effects relating to reporting. Please see my
last post for more detailed summary info.
In our case the effects were devastating due to our partitioning (we
partition by month)...it was causing full table scans on enormous
tables. (tens of millions of rows)
-Stace
_____
Sent: Thursday, May 06, 2004 12:21 PM
To: CF-Talk
Subject: RE: 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
----- Excess quoted text cut - see Original Post for more -----
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.
_____
Author: Stacy Young
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32344#162447
Sorry I should have stated my sources...I had initially googled on:
http://www.google.com/search?sourceid=navclient&ie=UTF-8&oe=UTF-8&q=oracle+bind+variables+performance
Also our DBA found helpful info digging thru our 9i docs and summarized for me:
"It doesn't bypass the optimizer, it just can't use it to its full potential
because it doesn't have enough information to take the best decision.
Let's say you have a "date between 2004-04-06 and 2004-04-07" in your where
clause, when this statement is parsed, it will know that all its information can
be found in the 2004-04 partition so it builds its plan accordingly. But when you
have a "date between :a and :b" in you where clause, it has to go with a general
plan that will fit any kind of date interval.
As a general rule, Bind Variables should be used only if you know the statement
will be reused very often(like transaction processing stuff). A report is not
likely to be run 3000 times per hour.
The reason Bind Variables is a good thing for repetitive statements is because of
the time the BD can save by reusing parsed code. A statement comes in, it's
hashed into a key, then it's parsed(this part takes a lot of time, relative to
CPU clock cycles of course), then it's put into memory, matched to the hash key
and then run. An identical statement comes in(50 milliseconds later), it's hashed
into a key, it compares it's key with what's already in memory, if it finds it,
it just runs it, otherwise it has to go through the parsing, etc."
We've proven the above theory in many instances.
Hope that helps!
Stace
________________________________________
Sent: Thursday, May 06, 2004 10:41 AM
To: CF-Talk
Subject: Re: RESOLVED - FYI : Oracle 9i and cfqueryparam problems
On Thursday 06 May 2004 15:26 pm, Jason.Gulledge@alltel.com wrote:
> Reference for what, specifically?
TechNet is huge, I thought you might have a TAR number or something.
--
Tom Chiverton
Advanced ColdFusion Programmer
Author: Dave Carabetta
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32344#162427
----- Excess quoted text cut - see Original Post for more -----
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.
Author: Thomas Chiverton
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32344#162414
On Thursday 06 May 2004 15:26 pm, Jason.Gulledge@alltel.com wrote:
> Reference for what, specifically?
TechNet is huge, I thought you might have a TAR number or something.
--
Tom Chiverton
Advanced ColdFusion Programmer
Tel: +44(0)1749 834997
email: tom.chiverton@bluefinger.com
BlueFinger Limited
Underwood Business Park
Wookey Hole Road, WELLS. BA5 1AF
Tel: +44 (0)1749 834900
Fax: +44 (0)1749 834901
web: www.bluefinger.com
Company Reg No: 4209395 Registered Office: 2 Temple Back East, Temple
Quay, BRISTOL. BS1 6EG.
*** This E-mail contains confidential information for the addressee
only. If you are not the intended recipient, please notify us
immediately. You should not use, disclose, distribute or copy this
communication if received in error. No binding contract will result from
this e-mail until such time as a written document is signed on behalf of
the company. BlueFinger Limited cannot accept responsibility for the
completeness or accuracy of this message as it has been transmitted over
public networks.***
Author: Jason.Gulledge
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32344#162411
Reference for what, specifically?
The URL for technet is: http://otn.oracle.com/index.html
-Jason Gulledge
> See Oracle technet for additional info!
Do you have a reference ?
--
Tom Chiverton
Advanced ColdFusion Programmer
Tel: +44(0)1749 834997
email: tom.chiverton@bluefinger.com
BlueFinger Limited
Underwood Business Park
Wookey Hole Road, WELLS. BA5 1AF
Tel: +44 (0)1749 834900
Fax: +44 (0)1749 834901
web: www.bluefinger.com
Company Reg No: 4209395 Registered Office: 2 Temple Back East, Temple
Quay, BRISTOL. BS1 6EG.
*** This E-mail contains confidential information for the addressee
only. If you are not the intended recipient, please notify us
immediately. You should not use, disclose, distribute or copy this
communication if received in error. No binding contract will result from
this e-mail until such time as a written document is signed on behalf of
the company. BlueFinger Limited cannot accept responsibility for the
completeness or accuracy of this message as it has been transmitted over
public networks.***
_____
Author: Thomas Chiverton
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32344#162403
> See Oracle technet for additional info!
Do you have a reference ?
--
Tom Chiverton
Advanced ColdFusion Programmer
Tel: +44(0)1749 834997
email: tom.chiverton@bluefinger.com
BlueFinger Limited
Underwood Business Park
Wookey Hole Road, WELLS. BA5 1AF
Tel: +44 (0)1749 834900
Fax: +44 (0)1749 834901
web: www.bluefinger.com
Company Reg No: 4209395 Registered Office: 2 Temple Back East, Temple
Quay, BRISTOL. BS1 6EG.
*** This E-mail contains confidential information for the addressee
only. If you are not the intended recipient, please notify us
immediately. You should not use, disclose, distribute or copy this
communication if received in error. No binding contract will result from
this e-mail until such time as a written document is signed on behalf of
the company. BlueFinger Limited cannot accept responsibility for the
completeness or accuracy of this message as it has been transmitted over
public networks.***
Author: Stacy Young
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32344#162362
I'd imagine that would help...but in our case we rely heavily on
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: Jason.Gulledge@alltel.com [mailto:Jason.Gulledge@alltel.com]
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?
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
________________________________________
_____
_____
Author: Jason.Gulledge
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32344#162361
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?
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
________________________________________
_____
Author: Stacy Young
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32344#162358
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
________________________________________
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
