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 ________________________________________

> 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.***

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 ________________________________________   _____  

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 ________________________________________   _____   _____  

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.***   _____  

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.***

----- 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.

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

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.   _____  

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.   _____   _____  

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