X

Insights into Statistics, Query Optimization and the Oracle Optimizer

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

Maria Colgan
Distinguished Product Manager

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!

Join the discussion

Comments ( 2 )
  • Michael Tuesday, July 5, 2011

    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?


  • Maria Colgan Monday, July 11, 2011

    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


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.