The question in the heading is one I get asked all the time. Everyone is looking for the fast = true switch, assuming “database tuning” means that you tune the database. In fact, it is my experience that more than 80 percent (frequently 100 percent) of all performance gains are to be realized at the application design and implementation level—not the database level. You can’t tune a database until you have tuned the applications that run on the database.
As time goes on, there are some switches we can throw at the database level to help lessen the impact of egregious programming blunders. For example, Oracle has a parameter, CURSOR_SHARING=FORCE. This feature implements an auto binder, if you will. It will silently take a query written as SELECT * FROM EMP WHERE EMPNO = 1234 and rewrite it for us as SELECT * FROM EMP WHERE EMPNO = :x. This can dramatically decrease the number of hard parses and decrease the library latch waits we discussed in the Architecture sections—but (there is always a but) it can have some side effects. A common side effect with cursor sharing is something like this:
What happened there? Why is the column reported by SQL*Plus suddenly so large for the second query, which is arguably the same query? If we look at what the cursor sharing setting did for us, it (and something else) will become obvious:
The cursor sharing removed information from the query. It found every literal, including the substr constants we were using. It removed them from the query and replaced them with bind variables. The SQL engine no longer knows that the column is a substr of length 1—it is of indeterminate length. Also, you can see that where rownum = 1 is now bound as well.
This seems like a good idea; however, the optimizer has just had some important information removed. It no longer knows that “this query will retrieve a single row”; it now believes “this query will return the first N rows and N could be any number at all.” This can have a negative impact on your generated query plans.
Additionally, I have shown that while CURSOR_SHARING=FORCE runs much faster than parsing and optimizing lots of unique queries (refer to the preceding section on bind variables), I have also found it to be slower than using queries where the developer did the binding.
This arises not from any inefficiency in the cursor sharing code, but rather in inefficiencies in the program itself. In many cases, an application that does not use bind variables is not efficiently parsing and reusing cursors either. Since the application believes each query is unique (it built them as unique statements), it will never use a cursor more than once.
The fact is that if the programmer had used bind variables in the first place, they could have parsed a query once and reused it many times. It is this overhead of parsing that decreases the overall potential performance.
Note It is important to also point out that CURSOR_SHARING=FORCE will not fix SQL injection bugs. The binding comes after the query was rewritten by your end user; the SQL injection has already happened. CURSOR_SHARING=FORCE makes you no more secure than you were before. Only by using bind variables themselves can a developer implement a SQL injection–proof application.
Basically, it is important to keep in mind that simply turning on CURSOR_SHARING = FORCE will not necessarily fix your problems. It may very well introduce new ones. CURSOR_SHARING is, in some cases, a very useful tool, but it is not a silver bullet. A well-developed application would never need it. In the long term, using bind variables where appropriate, and constants when needed, is the correct approach.
Note There are no silver bullets, none. If there were, they would be the default behavior and you would never hear about them.
Even if there are some switches that can be thrown at the database level, and they are truly few and far between, problems relating to concurrency issues and poorly executing queries (due to poorly written queries or poorly structured data) can’t be fixed with a switch.
These situations require rewrites (and frequently a re-architecture). Moving datafiles around, adjusting parameters, and other database-level switches frequently have a minor impact on the overall performance of an application. Definitely not anywhere near the two, three, … n times increase in performance you need to achieve to make the application acceptable.
How many times has your application been ten percent too slow? Ten percent too slow, no one complains about. Five times too slow, people get upset. I repeat: you will not get a five times increase in performance by moving datafiles around. You will only achieve large increments in performance by fixing the application, perhaps by making it do significantly less I/O.
Note This is just to note how things change over time. I’ve often written that you will not get a five times increase in performance by moving datafiles around. With the advent of hardware solutions such as Oracle Exadata, you can, in fact, get a five times, ten times, fifty times, or more decrease in response time (the time it takes to return data) by simply moving datafiles around. But that is more of a “we completely changed our hardware architecture” story than a “we reorganized some of our storage.” Also, getting an application running only five or ten times faster on Exadata would be disappointing to me—I’d want it to be fifty times or more “faster”—and would require a rethinking of how the application is implemented.
Performance is something you have to design for, build to, and test for continuously throughout the development phase. It should never be something to be considered after the fact. I am amazed at how often people wait until the application has been shipped to the customer, put in place, and is actually running before they even start to tune it. I’ve seen implementations where applications are shipped with nothing more than primary keys—no other indexes whatsoever.
The queries have never been tuned or stress-tested. The application has never been tried out with more than a handful of users. Tuning is considered to be part of the installation of the product. To me, that is an unacceptable approach. Your end users should be presented with a responsive, fully tuned system from day one. There will be enough “product issues” to deal with without having poor performance be the first thing users experience. Users expect a few bugs from a new application, but at least don’t make the users wait a painfully long time for those bugs to appear on screen.
