Oracle Scratchpad

October 9, 2019


Filed under: CBO,Execution plans,Oracle,Troubleshooting — Jonathan Lewis @ 4:58 pm BST Oct 9,2019

Here’s a funny little detail that I don’t think I’ve noticed before – needing only a simple demo script:

rem     Script:         cursor_sharing_oddity.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Oct 2019
rem     Last tested

create table t1 as
select  * 
from    all_objects 

set serveroutput off
alter system flush shared_pool;
alter session set cursor_sharing = force;

select  *
from    t1
        created between date'2019-06-01' and date'2019-06-30'

select * from table(dbms_xplan.display_cursor);

Given that I’ve set cursor_sharing to FORCE (and flushed the shared pool just in case), what SQL do you expect to see if I pull the plan from memory, and what sort of thing do you expect to see in the Predicate Information. Probably some references to system-constructed bind variables like :”SYS_B_0″. This is what I got on

SQL_ID  9qwa9gpg9rmjv, child number 0
select * from t1 where  created between date:"SYS_B_0" and

Plan hash value: 3617692013

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT  |      |       |       |   170 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |  1906 |   251K|   170   (8)| 00:00:01 |

Predicate Information (identified by operation id):
   1 - filter(("CREATED">=TO_DATE(' 2019-06-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "CREATED"<=TO_DATE(' 2019-06-30 00:00:00',
              'syyyy-mm-dd hh24:mi:ss')))

Somehow I’ve got system-generated bind variables in the SQL (and v$sql – when I checked), but the original literal values are still present (in a different form) in the predicate information. Then, when I re-ran the query changing 1st June to 15th June I got the same SQL_ID (and generated bind variables) but with child number 1 and suitably modified filter predicates.

Of course, just for completion, if I write the query using the “old-fashioned” to_date() approach I end up with a single statement with lots of system-generated bind variables that are consistent between the SQL and the Predicate Information.

SQL_ID  10sfymvwv00qx, child number 0
select * from t1 where  created between to_date(:"SYS_B_0",:"SYS_B_1")
                   and to_date(:"SYS_B_2",:"SYS_B_3")

Plan hash value: 3332582666

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT   |      |       |       |   189 (100)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |  1029 |   135K|   189  (17)| 00:00:01 |

Predicate Information (identified by operation id):
   1 - filter(TO_DATE(:SYS_B_2,:SYS_B_3)>=TO_DATE(:SYS_B_0,:SYS_B_1))
   2 - filter(("CREATED">=TO_DATE(:SYS_B_0,:SYS_B_1) AND

If you are planning to do anything with cursor_sharing, watch out for the side effects of the “ANSI” date and time operators.

Update (3 hours later)

It turns out that I have come across this before – and written about it because it appeared as a performance problem to someone on the Oracle Developer Community forum.

The same behaviour is shown by timestamp literals and interval literals. For details on the two types of literal here’s a link to the 12.2 SQL Language Reference Manual section on literals.

Update (May 2021)

In the linked note the anomaly is described as “not a bug” – so it should be no surprise that nothing has changed in more recent versions of Oracle – most recently

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by