QBE bind variables

[Vitor Hugo Campos]

Hello folks,

My company's DBA is complaining we are using literals too many times and this is severely degrading our database server performance. He showed me some queries where this problem happens and almost all of them are due to the use of the QBE mode, where literals are put for each field users want to query. I recall old versions of DOA used to add :qbe__ like bind variables, but this behavior changed on new versions (I'm using DAO 4.0.6.2). I didn't find any property to change that behavior. Is there a way I can change that?

I know it is possible to change the user's session to force the use of bind variables (i.e. ALTER SESSION SET CURSOR_SHARING = FORCE), but that does n't solve the real problem and it also add some other problems.

Thank you in advance,

Vitor Hugo Campos.

 

[Marco Kalter]

Using literals instead of bind variables should improve performance, since the optimizer can now use the table statistics to determine the optimal query plan. Perhaps you need to perform an "analyze table ... compute statistics" on the tables involved?

 

[Vitor Hugo Campos]

Originally posted by Marco Kalter:
Using literals instead of bind variables should improve performance, since the optimizer can now use the table statistics to determine the optimal query plan. Perhaps you need to perform an "analyze table ... compute statistics" on the tables involved?

We compute statistics every night. Actually, the "analyze table" syntax is not recommended anymore (according to the AskTom website, at http://asktom.oracle.com/pls/ask/f?p=4950:8:10209249703092375567::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:5792247321358).

Using literals can improve performance only on columns with heavily sweked data (ex: one value has 99% of all data stored in a table). In all other cases only one query plan should be enough. In fact, skewed data is the exception, not the rule. This is especially true for primary keys, fields containing unique constraints (other than PK's), people/product/service names and the like, where there are few or no duplicates at all. IMHO (and according to the queries I got from my DBA) , these types of fields are the ones where users query most often, and I strong believe this can be true on many other companies.

When I use literals instead of bind variables, the database server must parse every single statement, increasing library cache misses and thus reducing performance (especially on high concurrent servers), as can be seen at the links below:

http://asktom.oracle.com/pls/ask/f?p=4950:8:10209249703092375567::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1163635055580
http://asktom.oracle.com/pls/ask/f?p=4950:8:10209249703092375567::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:7832114438832
http://asktom.oracle.com/pls/ask/f?p=4950:8:10209249703092375567::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:8764517459743
http://asktom.oracle.com/pls/ask/f?p=4950:8:10209249703092375567::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:3675961676045

(you can find more information about that pointing your browser at http://asktom.oracle.com/ and searching for "bind variables use".

There is also very good book where you can find more information about this issue (among others): "Expert Oracle Database Architecture" (ISBN 1-59059-530-0). It explains details about how Oracle works and give useful hints on how (and how not) to develop software on that database, using a easy to understand language for the non-DBA.

Back to the subject, it would be a good idea to give an option (i.e. a property) to DOA's users to choose whether to use bind variables in QBE mode or not.

 

[Marco Kalter]

I have added this to the list of enhancement requests.

 

[Vitor Hugo Campos]

Thank you very much.