|
SQL Tuning
TROUBLESHOOTING GUIDE: SQL Tuning
=================================
This document contains a number of potentially useful pointers for use when
attempting to tune an individual SQL statement. This is a vast topic and this
is just a drop in the ocean.
Contents: Possible Causes of Poor SQL Performance
=================================================
1. Poorly tuned SQL
2. Poor disk performance/disk contention
3. Unnecessary sorting
4. Late row elimination
5. Over parsing
6. Missing indexes/use of 'wrong' indexes
7. Wrong plan or join order selected
8. Import estimating statistics on tables
9. Insufficiently high sample rate for CBO
10. Skewed data
11. New features forcing use of CBO
12. ITL contention
Diagnostics/Remedies
====================
1. Poorly tuned SQL
Often, part of the problem is finding the SQL that is causing the problems.
If you are seeing problems on a system, it is usually a good idea to start
by eliminating database setup issues by using the UTLBSTAT & UTLESTAT
reports. See:
Introduction to Tuning
Tuning using BSTAT/ESTAT
for much more on this.
Once the database has been tuned to a reasonable level then the most
resource hungry selects can be determined as follows
(a very similar report can be found in the Enterprise Manager Tuning Pack):
SELECT address, SUBSTR(sql_text,1,20) Text, buffer_gets, executions,
buffer_gets/executions AVG
FROM v$sqlarea
WHERE executions > 0
AND buffer_gets > 100000
ORDER BY 5;
Remember that the 'buffer_gets' value of > 100000 needs to be varied for the
individual system being tuned. On some systems no queries will read more than
100000 buffers, while on others most of them will. This value allows you to
control how many rows you see returned from the select.
The ADDRESS value retrieved above can then be used to lookup the whole
statement in the v$sqltext view:
SELECT sql_text FROM v$sqltext WHERE address = '...' ORDER BY piece;
Once the whole statement has been identified it can be tuned to reduce
resource usage.
If the problem relates to CPU bound applications then CPU information
for each session can be examined to determine the culprits. The v$sesstat
view can be queried to find high cpu using sessions and then SQL can be
listed as before. Steps:
1. Verify the reference number for the 'CPU used by this session'
statistic:
SELECT name ,statistic#
FROM v$statname
WHERE name LIKE '%CPU%session';
NAME STATISTIC#
----------------------------------- ----------
CPU used by this session 12
2. Then determine which session is using most of the cpu:
SELECT * FROM v$sesstat WHERE statistic# = 12;
SID STATISTIC# VALUE
---------- ---------- ----------
1 12 0
2 12 0
3 12 0
4 12 0
5 12 0
6 12 0
7 12 0
8 12 0
9 12 0
10 12 0
11 12 0
12 12 0
16 12 1930
3. Lookup details for this session:
SELECT address ,SUBSTR(sql_text,1,20) Text, buffer_gets, executions,
buffer_gets/executions AVG
FROM v$sqlarea a, v$session s
WHERE sid = 16
AND s.sql_address = a.address
AND executions > 0
ORDER BY 5;
4. Use v$sqltext to extract the whole SQL text.
5. Explain the queries and examine their access paths. Autotrace is
a useful tool for examining access paths.
2. Poor disk performance/disk contention
Use of BSTAT/ESTAT and/or operating system i/o reports can help in this
area. Remember that you may be able to capture the activity of a single
statement by running the report around the run of your statement with
no other activity.
Another good way of monitoring IO is to run a 10046 Level 8 trace to
capture all the waits for a particular session. 10046 can be turned on at
the session level using:
alter session set events '10046 trace name context forever, level 8';
Excessing i/o can be found by examining the resultant trace file and
looking for i/o related waits such as:
'db file sequential read' (Single-Block i/o - Index, Rollback Segment or Sort)
'db file scattered read' (Multi-Block i/o - Full table Scan).
Remember to set TIMED_STATISTICS = TRUE to capture timing information
otherwise comparisons will be meaningless.
If you are also interested in viewing bind variable values then a level 12
trace an be used.
3. Unnecessary sorting
The first question to ask is 'Does the data REALLY need to be sorted?'
If sorting does need to be done then try to allocate enough memory to
prevent the sorts from spilling to disk an causing i/o problems.
Sorting is a very expensive operation:
- High CPU usage
- Potentially large disk usage
Try to make the query sort the data as late in the access path as possible.
The idea behind this is to make sure that the smallest number of rows
possible are sorted.
Remember that:
- Indexes may be used to provided presorted data.
- Sort merge joins inherently need to do a sort.
- Some sorts don't actually need a sort to be performed. In this case the
explain plan should show NOSORT for this operation.
In summary:
- Increase sort area size to promote in memory sorts.
- Modify the query to process less rows -> Less to sort
- Use an index to retrieve the rows in order and avoid the sort.
- use sort_direct_writes to avoid flooding the buffer cache with sort
blocks.
- If Pro*C use release_cursor=yes as this will free up any temporary
segments held open.
4. Late row elimination
Queries are more likely to be performant if the bulk of the rows can be
eliminated early in the plan. If this does happen then unnecessary
comparisons may be made on rows that are simply eliminated later.
This tends to increase CPU usage with no performance benefits.
If these rows can be eliminated early in the access path using a selective
predicate then this may significantly enhance the query performance.
5. Over parsing
Over parsing implies that cursors are not being shared.
If statements are referenced multiple times then it makes sense to share
then rather than fill up the shared pool with multiple copies of
essentially the same statement. See:
Main issues affecting the Shared Pool on Oracle 7 and 8
Use of bind variables with CBO
6. Missing indexes/use of 'wrong' indexes
If indexes are missing on key columns then queries will have to use Full
Table Scans to retrieve data. Usually indexes for performance should be
added to support selective predicates included in queries.
If an unselective index is chosen in preference to a selective one then
potential solutions are:
RBO
- indexes have an equal ranking so row cache order is used.
CBO
- reanalyze with a higher sample size
- add histograms if column data has an uneven distribution of values
- add hints to force use of the index you require
Remember that index usage on join can be compromised by the join type and
join order chosen.
7. Wrong plan or join order selected
If the wrong plan has been selected then you may want to force the correct
one.
If the problem relates to an incorrect join order, then it ofter helps to
draw out the tables linking them together to show how they join e.g.:
A-B-C-D
E-F
This can help with visualisation of the join order and identifications of
missing joins. When tuning a plan, try different join orders
examining number of rows returned to get an idea of how good they may be.
8. Import estimating statistics on tables
Pre 8i, import performs an analyze estimate statistics on all tables
that were analyzed when the tables were exported. This can result in
different performance after an export/import.
Introduced in 8i, more sampling functionality has been introduced including
the facility to extract statistics on export.
9. Insufficiently high sample rate for CBO
If the CBO does not have the correct statistical information then it
cannot be expected to produce accurate results based on them. Usually a
sample size of 5% will be sufficient.
10. Skewed data
If column data distribution is non uniform, then the use of column statistics
in the form of histograms should be considered. Histogram statistics do not
help with uniformly distributed data or where no information about the
column predicate is available such as with bind variables.
11. New features forcing use of CBO
A number of new features are not implemented in the RBO and their presence
in queries will force the use of the CBO. These include:
- Degree of parallelism set on any table in the query
- Index-only tables
- Partition Tables
- Materialised views
12. ITL contention
ITL contention can occur when there is not enough Interested Transaction
Lists in each block to support the update volume required. This can often
occur after an export and import especially when no update space has been
left in the blocks and the ITLs have not been increased.
|
