How do I get an application to use the same execution plan I get in SQL*Plus?

Let me provide a little background to this question before answering it.

Background
This application is a home-grown Java based app and the SQL statement in questions looks like this:

INSERT /*+APPEND*/ INTO t1
(ROW_ID, MODIFICATION_NUM, OPERATION, LAST_UPD)
SELECT ROW_ID ,1 ,'I' ,LAST_UPD
FROM t2
WHERE t2.LAST_UPD > :1;

The table T2 has approximately 1.4 million rows and one index IND_T2 on the LAST_UPD and ROW_ID columns. The execution plans are quite simple and involve just the IND_T2 index. The paln in SQL* Plus does an index range scan on the IND_T2 index.


good_plan.png


While the application does an index full scan of the same index.


bad_plan.png


As you can see the SQL statement is using one bind variable, but bind peeking has been disabled disabled (_optim_peek_user_binds = false) and the same bind value is used in both SQL*Plus and the application. In fact, the SQL*Plus session uses the same db user, SQL_ID and the optimizer_env_hash_value as the application.

Statistics exist for both the table and the index, they are not stale and are not changing between the time the application executes the SQL statement and the time it is executed through SQL*Plus. Dynamic sampling is not kicking in for this SQL statement in either scenario.

The SQL statement is executed only once during the day and is not present in the shared pool by the next morning so it is always hard parsed. And this issue is occurring in multiple databases, across 2 versions (10.2.0.4. and 11.1.0.7).

So why are the plans different?

The plan difference is actually due to a datatype mismatch between the bind variable and the column. The Java application declares the bind variable to be a timestamp data type while the column (LAST_UPD) in the table T2 is defined as a date. In the SQL*Plus session, the bind variable was being declared correctly as a date.

Since the application was using an incorrect datatype the optimizer rewrote the where clause predicate to be;

INTERNAL_FUNCTION("T2"."LAST_UPD")>:1,

The internal_function is wrapped around the column LAST_UPD to convert it to a timestamp data type. The presence of the function around the column causes the cardinality estimate to be a guess. In this cause the guess is 5% of the rows in the table or 70,414, instead of the correct estimate of 12,782.

By changing the application datatype to be a date instead of a timestamp, they were able to get the application to do an index range scan just like SQL*Plus!

Comments:

Just a quick question about this.

I've recently experienced a problem with something similar under Oracle 10.2.0.5 where the condition on the query has a function on both sides of an equality condition and seems to be coming back with a cardinality estimate of 1% of the number of rows.

I have since rewritten the query to have a column value on one side of the condition joining to a table function and it is coming back with much better cardinality estimates and as a result better execution plans. I'm using the cardinality hint because the table function doesn't return 8168 rows but that is a separate issue.

Anyway, since I am getting 1% and the article above is talking about 5%, I'm wondering how the optimizer makes the choice of which value to use when it can't make a decision based on column values alone, or is this just a change between Oracle 10g and 11g?

Posted by Michael on July 04, 2011 at 10:08 PM PDT #

Hi Michael,

The optimizer use a number of different size percentages depending on the type of predicates used (=, !=, >, <, etc) and the type of access method chosen (table scan, index look up etc.). It is not simple 1% or 5%.

Thanks,
Maria

Posted by Maria Colgan on July 11, 2011 at 04:28 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today