Oracle Query Optimization: Why is My Second Query Faster?

Question

I was having some performance issues with an Oracle query, so I downloaded a trial of the Quest SQL Optimizer for Oracle, which made some changes that dramatically improved the query's performance. I'm not exactly sure why the recommended query had such an improvement; can anyone provide an explanation?

---

Before:

SELECT t1.version_id, 
       t1.id, 
       t2.field1, 
       t3.person_id, 
       t2.id 
  FROM table1 t1, 
       table2 t2, 
       table3 t3 
 WHERE t1.id = t2.id 
   AND t1.version_id = t2.version_id 
   AND t2.id = 123
   AND t1.version_id = t3.version_id 
   AND t1.VERSION_NAME <> 'AA' 
 order by t1.id

• Plan Cost: 831
• Elapsed Time: 00:00:21.40
• Number of Records: 40,717

---

After:

SELECT /*+ USE_NL_WITH_INDEX(t1) */ t1.version_id, 
       t1.id, 
       t2.field1, 
       t3.person_id, 
       t2.id 
  FROM table2 t2, 
       table3 t3,
       table1 t1 
 WHERE t1.id = t2.id + 0
   AND t1.version_id = t2.version_id + 0
   AND t2.id = 123
   AND t1.version_id = t3.version_id + 0
   AND t1.VERSION_NAME || '' <> 'AA' 
   AND t3.version_id = t2.version_id + 0
 order by t1.id

• Plan Cost: 686
• Elapsed Time: 00:00:00.95
• Number of Records: 40,717

---

Questions:

1. Why does re-arranging the order of the tables in the FROM clause help?

2. Why does adding + 0 to the WHERE clause comparisons help?

3. Why does || '' <> 'AA' in the WHERE clause VERSION_NAME comparison help? Is this a more efficient way of handling possible nulls on this column?

Answer 1

I agree with Peter's comment - try taking the hint out of the "improved" query and see if it still performs as well.

You might need to update the statistics on this table if oracle doesn't want to use it...

EDIT: Are the version_id and id columns the same datatype in each table?

Answer 2

If you run both statements through an explain plan, you'll see the difference yourself. To do that, issue:

explain plan
for
<your query>;

select * from table(dbms_xplan.display);

If it's still not clear why the difference occurs, then please paste the output here.

Regards, Rob.

PS: the " + 0 " and " || '' " are just tricks to make sure regular indexes on the columns are not being used. They also make your query less readable, so I would tune them myself, instead of relying on a tool.

Answer 3

Almost certainly the statistics on these tables are wrong, causing the optimizer to choose a poor plan.

Gather stats on the tables and indexes ( or get your DBA to if you aren't in control of that kind of thing ), then try your queries again.

Answer 4

First thing to note is that the 'after' plan had a lower cost than the 'before' plan.

This tells us that the plan chosen for the second query was not considered for the first query, as Oracle would have chosen the lower cost plan.

The addition of the USE_NL_WITH_INDEX hint would not have affected the costing of any plan. What it does is, if a Nested Loop With index plan is available, then any other plan would be excluded from consideration even if Oracle though they were more efficient plans.

Secondly, a predicate of "t1.id = t2.id" allows us to start from t1 and link to t2 or to start from t2 and link to t1. Changing that predicate to "t1.id = t2.id + 0" means that it can only go from t2 (add 0 to the id) to link to t1. Oracle isn't bright enough to realize that it could start with t1 (minus 0 from the id) and join to t2 (or that adding/minusing 0 is irrelevant anyway).

Both those tricks prevent particular access paths from being used. Neither would account for the apparent drop in cost of the chosen plan.

Given both "AND t1.version_id = t2.version_id" and "AND t1.version_id = t3.version_id", then "t3.version_id = t2.version_id" is a given (subject to datatype conversions). However adding it explicitly would suggest to Oracle that, since an extra condition, is being applied then fewer rows will be returned, and this might be reflected in a lower cost.

*If the t2 and t3 version_id's were not numeric, that could account for the change in cost. The "+ 0" would force a datatype conversion which may permit the use of an index. I think that unlikely though.*

I suspect the original plan was driving from t1 (with t1.id = 123 and version_name <> 'AA'). The additional predicates would (probably) force it to start at t2.

personally, I don't like any other those optimizations. None address why an incorrect plan was originally chosen.

Answer 5

Supplementing the answers submitted already here is a link from Ask Tom on tuning using tools (or other n step approaches).