Expert One-on-One: Oracle: Pt. 4
October 11, 2001
How Do I Make it Run Faster?
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 much more, 100 percent) of all performance gains are to be realized at the application level — not the database level. You cannot tune a database until you have tuned the applications that run on the data.
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 8.1.6 adds a new 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. You may hit an issue (a.k.a. 'bug') with regards to this feature, for example in the first release:
The way they rewrote the query (because of the lack of whitespace between 'X' and the word AND) didn't work in 8.1.6. The query ended up being:
The key word AND became part of the bind variable :SYS_B_0. In 8.1.7, however, this query is rewritten as:
This works syntactically but might negatively affect your program's performance. For example, in the above, notice how 1=0 (also False) is rewritten to be :SYS_B_1 = :SYS_B_2. The optimizer no longer has all of the information at parse time, it can no longer see that this query returns zero rows (before it even executes it). While I don't expect you to have lots of queries with 1=0 in them, I would expect you to have some queries that do use literals in them on purpose. You may have a column with very skewed values in it, for example 90 percent of the values of the column are greater than 100, 10 percent are less then 100. Further, 1 percent is less then 50. You would want the query:
to use an index, and the query:
to not use an index. If you use CURSOR_SHARING = FORCE, the optimizer will not have the 50 or 100 values to consider when optimizing — hence it will come up with a generic plan that probably does not use the index (even if 99.9 percent of your queries are of the type WHERE x < 50).
Additionally, I have found that while CURSOR_SHARING = FORCE runs much faster than parsing and optimizing lots of unique queries, 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 Chapter 10, Tuning Strategies and Tools, we'll discover how parsing of SQL queries can affect our performance. 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 you could see.
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.
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) cannot be fixed with a switch. These situations require rewrites (and frequently a re-architecture). Moving datafiles around, changing the multi-block read count, and other 'database' level switches frequently have a minor impact on the overall performance of an application. Definitely not anywhere near the 2, 3, ... N times increase in performance you need to achieve to make the application acceptable. How many times has your application been 10 percent too slow? 10 percent too slow, no one complains about. Five times too slow, people get upset. I repeat: you will not get a 5-times increase in performance by moving datafiles around. You will only achieve this by fixing the application — perhaps by making it do significantly less I/O.
Performance is something you have to design for, to build to, and to test for continuously throughout the development phase. It should never be something to be considered after the fact. I am amazed at how many times people wait until the application has been shipped to their 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 they experience. Users are expecting a few 'bugs' from a new application, but at least don't make them wait a painfully long time for them to appear on screen.