You Asked
Tom
We all know what you think about binding ;-} and how it will solve all ills (!) I can see that is true in the OLTP world or where we can 'pre-can' web queries, but in data warehouses we are up against 'never the same query twice' - one guy want's to know total sales last quarter, the next wants to know if we sell more beer on a Wednesday than on a Thursday. The use of front end query tools does not help us either as we can not modify the sql that is gerenrated for user queries.
So what do we do? Is binding the holy grail we should try to seek, or do we just rely on other Oracle features such as Dimensions, star transforms, materilized view query rewrite, bitmap indexes, partitioning etc to give the best user performance?
My question is : Do you have any tips to achieve binding in DWH or should we not even try to develop ways to do this?
By the way our dataload process (PL/SQL) is bind, bind, bind ;-)
I still have Effective Oracle by Design on my wish list - perhaps the answer I want is there
and Tom said...
Here is a snipped from that book explaining this in a little more detail:
(2)There Are Exceptions to Every Rule
In this section, we've seen mathematical, scientific, reproducible proof that as a general rule, you should use bind variables:
o Bind variables are a sound method to reduce the number of latches (read that word as locks) your application will use when parsing queries.
o Soft parsing uses significantly less CPU time than hard parsing, and bind variables are the way to achieve soft parsing.
o Stringing literals into your SQL statements, rather than using bind variables, opens your system to the SQL injection security bug.
o Stringing literals into your SQL statements can cause the statement to fail if the user inputs some unanticipated characters such as quotation marks.
o No matter what environment you are using-PL/SQL, Java and JDBC, or some other language-using bind variables is not only at least as fast, if not faster, than not using bind variables, but the code is also easier to write.
In short, if you need to execute hundreds or thousands or more SQL statements per minute or second, use bind variables in place of literals that change in the SQL. The effect of not using them can be quite dramatic. All it takes is one bad query in your system that fails to use a bind variable, but is executed frequently differing only in the literal values, to bring it to its knees.
Does that mean that you should always use bind variables in every circumstance?
No, those kinds of rules of thumb are dumb. For every rule of thumb out there, there are cases where they do not hold true. In some cases on systems that measure queries per second (which we've been focusing on in the examples here), you don't want use bind variables. On a system where you are measuring seconds per query (a data warehouse, for example), rather than per second, bind variables are something you may actually want to avoid using.
(3)Queries-per-Second Systems
....
discussion snipped
....
(3)Seconds-per-Query Systems
In what I'll loosely refer to as data warehouses, instead of running say 1,000 statements per second, they do something like take an average of 100 seconds to run a single query. In these systems, the queries are few but big (they ask large questions). Here, the overhead of the parse time is a tiny fraction of the overall execution time. Even if you have thousands of users, they are not waiting behind each other to parse queries, but rather are waiting for the queries to finish getting the answer.
In these systems, using bind variables may be counterproductive. Here, the runtimes for the queries are lengthy-in seconds, minutes, hours, or more. The goal is to get the best query optimization plan possible to reduce the runtime, not to execute as many of OLTP, one-tenth-second queries as possible. Since the optimizer's goal is different, the rules change.
As explained in the previous section (considering the example with the status = `N' query against the RECORDS_TO_BE_PROCESSED table), sometimes using a bind variable forces the optimizer to come up with the best generic plan, which actually might be the worst plan for the specific query. In a system where the queries take considerable time to execute, bind variables remove information the optimizer could have used to come up with a superior plan. In fact, some data warehouse-specific features are defeated by using bind variables. For example, Oracle supports a star transformation feature for data warehouses that can greatly reduce the time a query takes (we discuss this feature briefly in Chapter 6, the CBO). However, one restriction that precludes star transformation is having queries that contain bind variables.
In general, when you are working with a seconds-per-query system, use of bind variables may well be the performance inhibitor!
Rating
(3 ratings)
Is this answer out of date? If it is, please let us know via a Comment