Tuning
application queries is one of the biggest challenges for DBAs supporting
production environments. The lack of knowledge about the application data model
makes difficult the tuning. I remember when I worked as a development DBA –
based on my knowledge about the application design I was able to identify
whether the access path was optimal or not just looking at explain plans. It
was easy to identify how a particular query should run.
Without
application knowledge, improvements may be obtained by considering the table
sizes and the number of rows that can be eliminated as early as possible in the
query based on the join and filter conditions. When tables are accessed in the
expected order, rows are eliminated correctly generating no more I/O operations
(physical or logical) than the necessary. It is pretty common to see
problematic situations created by just few SQL statements with non-optimal
execution plans generating so many and unnecessary physical I/O in such way
that the overall performance for the database is degraded.
Normally
tuning OLTP queries is less complicated than tuning queries running on data
warehouse systems, as the queries use to be smaller and simpler. Last week I
was working on a query that is used to show offers to customers contacting the
call center. Most part of the time (99%) the query runs very fast, in less than
100 milliseconds. But some executions take minutes, raising the average
execution time for the query to 20 seconds – I am mentioning the average time
just to give a better idea how extremely opposite the execution times are.
The
execution time is related to the number of rows accessed by the query – fast
executions access few rows, and the slow executions access some thousands rows.
The query joins 7 tables, but only 3 are really relevant: one because it is the
most selective table (it is the driven table) and the other two because they
hold most records accessed by the query, being the top contributors for the
high execution times. This is the simplified query that I am going to use to
show how unordered physical rows (based on join predicates) can impact the
response time due to the high number of I/O operations:
SELECT /*+ ALL_ROWS */
... --
1 indexed column from T1, 5 columns from T2, and 14 columns from T7
FROM T1,
T2,
T7
WHERE T1.UID = :B1
AND T2.PER_ID =
T1.ROW_ID
AND T7.ID =
T2.ROW_ID
AND T7.FLAG =
'Y'
|
Predicate |
Rows |
|
access(T1.UID= '4850') |
1 |
|
access(T2.PER_ID=T1.ROW_ID) |
4,973 |
|
access(T7.
ID=T2.ROW_ID) |
4,893 |
|
filter(T7.FLAG='Y') |
3 |
|
access(T3.ROW_ID= T7.ROW_ID) |
3 |
The query was taking 41 seconds.
|
I/O Statistics |
|
Consistent gets |
19,281 |
|
Physical reads |
7,585 |
The following table shows the number of blocks that contains any row
accessed by the query. It shows the number of blocks before and after
reorganizing the physical order of the rows in tables T2 and T7.
|
Table |
Rows |
Before (blocks) |
After (blocks) |
|
T2 |
4,973 |
4,426 |
75 |
|
T7 |
4,893 |
120 |
23 |
The reorganization was pretty simple, as I was working only with records
related to a sample case instead of reorganizing the entire table:
§
Created a temporary table
with the relevant records;
§
Deleted the records from
the current table;
§
Inserted the records back
using a select ordered by the predicate column (PER_ID for T2 and
ID for T7).
After reorganizing the tables T2 and T7 the query was taking 4.05
seconds.
|
I/O Statistics |
|
Consistent gets |
11,357 |
|
Physical reads |
3,133 |
Analyzing the path access it is possible to reduce a little bit more the
number of I/O operations. Oracle is using an index on column ID to access table
T7 when joining it with T2. The column FLAG used as a filter condition is not
part of the index, so oracle needs to access the table in order to check if the
FLAG value satisfies the filter condition or not (T7.FLAG='Y'). Adding
the column FLAG to the index will allow oracle to check the condition without
accessing the table. After adding the FLAG column to the index this is the
elapsed time and statistics for the query:
Elapsed time: 2.44 seconds
|
I/O Statistics |
|
Consistent gets |
10,081 |
|
Physical reads |
2,819 |
Keeping the physical order of rows based on a specific logical order can
be a nightmare. When the application inserts data through batch or bulk loads
it may be simple to keep the physical order of rows based on a logical order
(index). But normally data is inserted at different period of times, and
without any order, result of online processing. It should be necessary to
schedule table reorganizations on a regular basis to keep the physical order
close to the logical order – I support some databases with tables measured in
hundreds of gigabytes, with active data (not historical), and accessed 24x7x365
– reorganization is not feasible.
Concerning other access paths (for example, T2 was reorganized by PER_ID,
but it has several other indexes used to satisfy different queries), the
reorganization approach might decrease the table
access efficiency thru other indexes. In this specific case of table T2, where
the data was spread over thousands of blocks, the reorganization concentrated
the rows in just few hundreds, improving all access paths.
Besides, this is a RAC database (for sake of sanity I
ran all tests on the same node, and yes, I used the Test environment
:o), therefore by using the reorganization approach there is a good
chance to increase the interconnect traffic, as the rows will be grouped into
fewer (hot) blocks.
Bottom line is there is no magic - bad design or a
design that does not reflect easily the business requirements will cost money
to fix, either for reengineering the application, or for acquiring powerful
hardware.
