Expert One-on-One: Oracle: Pt. 4

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:

ops$tkyte@ORA8I.WORLD> alter session set cursor_sharing=force;
Session altered.

ops$tkyte@ORA8I.WORLD> select * from dual where dummy='X'and 1=0;
select * from dual where dummy='X'and 1=0
                               *
ERROR at line 1:
ORA-00933: SQL command not properly ended

ops$tkyte@ORA8I.WORLD> alter session set cursor_sharing=exact;
Session altered.

ops$tkyte@ORA8I.WORLD> select * from dual where dummy='X'and 1=0;
no rows selected

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:

select * from dual where dummy=:SYS_B_0and :SYS_B_1=:SYS_B_2;

The key word AND became part of the bind variable :SYS_B_0. In
8.1.7, however, this query is rewritten as:

select * from dual where dummy=:”SYS_B_0”and :”SYS_B_1”=:”SYS_B_2”;

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:

select * from t where x < 50;

to use an index, and the query:

select * from t where x > 100;

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.