Why Oracle Execution Plans with Predicates are an Important Tuning Tool

When tuning SQL statements, reviewing SQL plans are an important part of the process. During a recent consulting session with one of our customers, we used a key piece of the plan called a filter predicate to determine why the customer’s shipping application was performing poorly and costing the company a lot of money.

Note: The data gathered in this case study was done using SolarWinds Database Performance Analyzer.

Problem

Using DPA we determined the following query was causing 80% of all wait time for the application. It is fairly straightforward and looked similar to the following query:

SELECT company, attribute
 FROM data_out
 WHERE segment = :1;

A unique index exists on the SEGMENT column (standard NUMBER data type) so the selectivity is very good. However, when this query executes from the Java application, it always takes 2-3 seconds and waits exclusively on the “db file scattered read” event. Since the SEGMENT column has a unique index, 2-3 seconds seems really slow, and why does it wait on this event that typically indicates a full table scan?

Analysis

The developer retrieved the SQL plan using the EXPLAIN PLAN command and found the following:

Execution Plan
 ———————————————————-
 0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=13)
 1    0   TABLE ACCESS (BY INDEX ROWID) OF ‘DATA_OUT’ (TABLE) (Cost=3 Card=1 Bytes=13)
 2    1     INDEX (UNIQUE SCAN) OF ‘IX1_DATA_OUT’ (INDEX (UNIQUE)) (Cost=2 Card=1)
 
Statistics
 ———————————————————-
 1  recursive calls
 0  db block gets
 2645  consistent gets
 1  rows processed

Based on this, Oracle appears to be using the unique index, but at least a couple questions arise from the data above:

1. Why is a UNIQUE SCAN being used on the IX1_DATA_OUT unique index?
2. Why are 2,645 consistent gets being performed when a unique index is being used?

Solution

Further analysis and the use of something called filter predicates led us to the answer. In this case, we used data collected by DPA from the V$SQL_PLAN table to get more information. You can also utilize the DBMS_XPLAN package to get similar results:

SQL_ID  46nx5qrtk8mtd, child number 0
 ————————————-
 SELECT company, attribute FROM data_out WHERE segment = :s1
 
Plan hash value: 3666395456
 
——————————————————————————
 | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
 ——————————————————————————
 |   0 | SELECT STATEMENT  |          |       |       |   668 (100)|          |
 |*  1 |  TABLE ACCESS FULL| DATA_OUT |     1 |    13 |   668  (14)| 00:00:09 |
 ——————————————————————————
 
Predicate Information (identified by operation id):
 —————————————————
 
1 – filter(TO_BINARY_DOUBLE(“SEGMENT”)=:S1)

Wait a minute, this plan says a full table scan is occurring. Why the difference in the two plans? The difference can be explained by the fact that EXPLAIN PLAN does not have all the information necessary to give an accurate plan. It has no knowledge of what the bind variable data will look like. Using V$SQL_PLAN to get the actual execution plan, you can see the filter predicates and immediately recognize that a data conversion is being performed. The TO_BINARY_DOUBLE function used around the SEGMENT column is negating the use of the unique index and hence a full table scan occurs.

Results

Now that we know what is really happening, we can fix this problem several different ways:

1. Tell the Java developers to pass a number data type so Oracle does not have to perform any data conversion.
2. Since we know a data conversion is done, we could utilize a function-based index on TO_BINARY_DOUBLE(SEGMENT).
3. Change the definition of the SEGMENT column to BINARY_DOUBLE vs. NUMBER.

Because this was a vendor application and we did not have immediate control of the code nor column definitions, we decided to go with option 2. Using DPA or the DBMS_XPLAN package again we see the new plan. Instead of the query executing in 2-3 seconds, it now executes in 0.01 seconds.

SQL_ID  46nx5qrtk8mtd, child number 0
 ————————————-
 SELECT company, attribute FROM data_out WHERE segment = :s1
 
Plan hash value: 4253606649
 
——————————————————————————————–
 | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
 ——————————————————————————————–
 |   0 | SELECT STATEMENT            |              |       |       |     3 (100)|          |
 |   1 |  TABLE ACCESS BY INDEX ROWID| DATA_OUT     |     1 |    14 |     3   (0)| 00:00:01 |
 |*  2 |   INDEX UNIQUE SCAN         | IX3_DATA_OUT |     1 |       |     2   (0)| 00:00:01 |
 ——————————————————————————————–
 
Predicate Information (identified by operation id):
 —————————————————
 
2 – access(“DATA_OUT”.”SYS_NC00004$”=:S1)

Conclusion

Without using the predicate information to see exactly how Oracle was executing this query, we were blind to the real problem. Using DPA for Oracle’s Historical Execution Plan feature to collect the real execution plan from the V$SQL_PLAN table, we knew immediately what the options were and fixed the problem in less than a day.