By Hervé Deschamps.
Basic Performance Tuning
This is a one page list of simple guidelines to tune SQL without much
This is all you need to know to tune some reports from 2 hours (or more)
to 20 seconds (or less). I have compiled this list during a report tuning
where I achieved that kind of runtime improvements repeatedly.
There are many more tips but they tend to be more difficult to understand
and there are many good books already written about this.
Understand the data. Look around table structures and data. Get a feel
for the data model and how to navigate it.
If a view joins 3 extra tables to retrieve data that you do not need, don't
use the view!
When joining 2 views that themselves select from other views, check that
the 2 views that you are using do not join the same tables!
Avoid multiple layers of view. For example, look for queries based on views
that are themselves views. It may be desirable to encapsulate from a development
point of view. But from a performance point of view, you loose control
and understanding of exactly how much task loading your query will generate
for the system.
Look for tables/views that add no value to the query. Try to remove table
joins by getting the data from another table in the join.
WHERE EXISTS sub-queries can be better than join if can you reduce drastically
the number of records in driver query. Otherwise, join is better.
WHERE EXISTS can be better than join when driving from parent records and
want to make sure that at least on child exists. Optimizer knows to bail
out as soon as finds one record. Join would get all records and then distinct
In reports, most of the time fewer queries will work faster. Each query
results in a cursor that Reports has to open and fetch. See Reports Ref
Manual for exceptions.
Avoid NOT in or NOT = on indexed columns. They prevent the optimizer from
using indexes. Use where amount > 0 instead of where amount != 0.
Avoid writing where project_category is not null. nulls can prevent the
optimizer from using an index.
Consider using IN or UNION in place of OR on indexed columns. ORs on indexed
columns causes the optimizer to perform a full table scan.
Avoid calculations on indexed columns. Write WHERE approved_amt > 26000/3
instead of WHERE approved_amt/3 > 26000.
Avoid this: SUBSTR(haou.attribute1,1,LENGTH(':p_otc')) = :p_otc). Consider
this: WHERE haou.attribute1 like :p_otc||'%'
Talk to your DBA. If you think that a column used in a WHERE clause should
have an index, don't assume that an index was defined. Check and talk to
your DBA if you don't find any.
Consider replacing outer joins on indexed columns with UNIONs. A nested
loop outer takes more time than a nested loop unioned with another table
access by index.
Consider adding small frequently accessed columns (not frequently updated)
to an existing index. This will enable some queries to work only with the
index, not the table.
Consider NOT EXISTS instead of NOT IN.
If a query is going to read most of the records in a table (more than 60%),
use a full table scan.
Try to group multiple sub queries into one.
Beyond the Simple stuff ...
If you want to actually understand what you are doing, here are a few things
that you need to start playing with:
Get into EXPLAIN_PLAN. There are multiple way of doing this. The less user
friendly is to simply issue this in SQL*Plus: explain plan set statement_id
= 'HDD1' for <Your DML SQL statement>;
Look at the trace from Oracle Reports. It tells you how much time it spends
on each query. With r25: C:\ORANT\BIN\R25RUN32.EXE module=p:\old\bcmtrka1_hdd.rdf
userid=opps/opps@new tracefile=p:\trace3.txt trace_opts=(trace_all)
Use the SQL Trace by issuing an alter session set sql_trace=true; then
look at it with TKPROF <something>.trc <something>.lis sort=(EXECPU).
If you remember nothing else ...
Don't apply these guidelines blindly, EXPERIMENT: compare one method to
another. Do NOT expect that one trick will work all the time.
Educate yourself: read, read, read. It SAVES time!
Please send me comments or suggestions: firstname.lastname@example.org
Take a look at my