Here’s a funny little detail relating to the cursor_sharing parameter and ANSI date/time literals that I don’t think I’ve noticed before – needing only a simple demo script:
rem rem Script: cursor_sharing_oddity.sql rem Author: Jonathan Lewis rem Dated: Oct 2019 rem rem Last tested rem 19.11.0.0 rem 12.2.0.1 rem 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 where 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 12.2.0.1:
SQL_ID 9qwa9gpg9rmjv, child number 0 ------------------------------------- select * from t1 where created between date:"SYS_B_0" and date:"SYS_B_1" 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 "CREATED">=TO_DATE(:SYS_B_2,:SYS_B_3)))
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 datetime 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 19.11.0.0.
[…] Cursor sharing update (Oct 2019): Rediscovery and further comment on the above […]
Pingback by ANSI catalogue | Oracle Scratchpad — March 20, 2022 @ 8:59 am GMT Mar 20,2022 |
[…] Cursor sharing update (Oct 2019): Rediscovery and further comment on the anomaly below (May 2016) […]
Pingback by Bug Catalogue | Oracle Scratchpad — March 20, 2022 @ 9:01 am GMT Mar 20,2022 |