Cursor Sharing 3

 

Jump to update of Feb 2018

Here’s a simple piece of code demonstrating an irritating problem. I’ve created a table, a function-based index, collected stats (without histograms), and then run a query that should use that index – but doesn’t.

rem
rem     Script:         cursor_share_fbi_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2010
rem

execute dbms_random.seed(0)

create table t1
as
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                rownum <= 10000  -- > comment to avoid wordpress format issue
)
select
        rownum                          id,
        dbms_random.string('l',6)       vc1,
        rpad('x',100)                   padding
from
        generator       v1,
        generator       v2
where
        rownum <= 10000         -- > comment to avoid wordpress format mess
;

create index t1_fbi1 on t1(substr(vc1,4,2));

begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T1',
                partname         => null,
                estimate_percent => 100,
                block_sample     => true,
                method_opt       => 'for all columns size 1'
        );
end;
/

select
        id, vc1
from
        t1
where
        substr(vc1,4,2) = 'ZZ'
;

select * from table(dbms_xplan.display_cursor);

I checked the execution plan using “explain plan” before I ran the query and it said that the index would be used, but when I ran the query Oracle didn’t use the index. Here are the two plans from an instance running 10.2.0.3:

-----------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |    11 |   121 |    12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |    11 |   121 |    12 |
|*  2 |   INDEX RANGE SCAN          | T1_FBI1 |    11 |       |     1 |
-----------------------------------------------------------------------

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    27 |
|*  1 |  TABLE ACCESS FULL| T1   |   100 |  1100 |    27 |
----------------------------------------------------------

So what’s gone wrong ? (There is a subtle clue in the cardinality estimate for the run-time plan if you compare it with the number of rows in the table.)

Your first response, of course, should be to tell me off for not showing the complete execution plan. Always generate and look at the predicate section which, in this case, also shows some variation:

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(SUBSTR("VC1",4,2)='ZZ')

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SUBSTR("VC1",:SYS_B_0,:SYS_B_1)=:SYS_B_2)

Look at those bind variables; the format is a giveaway: “:SYS_B_nn” … I’ve got cursor_sharing enabled (it doesn’t matter whether it’s set to force or similar, either option has the same effect. My query has been rewritten as:

select id, vc1 from t1 where substr(vc1,:”SYS_B_0″,:”SYS_B_1″) = :”SYS_B_2″

so the optimizer can’t use my function-based index because the predicate doesn’t match the index definition. (And, after making the substitution, it can’t peek and decide that it does match because maybe the next time I run the query I’ll use different literal values, which would make the indexed access path invalid).

There is a workaround to this type of problem – hide it inside a view:

create view v1 as
select
        id,
        vc1,
        substr(vc1,4,2) vc_short,
        padding
from
        t1
;

alter session set cursor_sharing = force;

select
        id, vc1
from
        v1
where
        vc_short = 'ZZ'
;

select * from table(dbms_xplan.display_cursor);

-- results:

----------------------------------------
SQL_ID  6bz5b3t980ch1, child number 0
-------------------------------------
select  id, vc1 from  v1 where  vc_short = :"SYS_B_0"

Plan hash value: 338442950

-----------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |    16 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |    15 |   165 |    16 |
|*  2 |   INDEX RANGE SCAN          | T1_FBI1 |    15 |       |     1 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."SYS_NC00004$"=:SYS_B_0)

If you enable cursor_sharing and depend on function-based indexes that include literal values in their definition, then queries that are supposed to use those indexes will also use literals and those literals will be rewritten as bind variables unless you hide the function-based expression inside a view. This means that enabling cursor_sharing may stop some of your function-based indexes from being useful.

Rewriting the code to use the views may be a huge investment in effort and there will be cases where it won’t be legal – e.g. in updates; there is an alternative workaround that is easier and safer – but might have an undesirable impact on the library cache and its latches: use the hint (available since 9i) to disable cursor sharing just for the relevant queries:

alter session set cursor_sharing = force;

select
        /*+ cursor_sharing_exact */
        id, vc1
from
        t1
where
        substr(vc1,4,2) = 'ZZ'
;

select * from table(dbms_xplan.display_cursor);

-- results:

SQL_ID  7tvc045hvz2p5, child number 0
-------------------------------------
select  /*+ cursor_sharing_exact */  id, vc1 from  t1 where
substr(vc1,4,2) = 'ZZ'

Plan hash value: 338442950

-----------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |    12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |    11 |   121 |    12 |
|*  2 |   INDEX RANGE SCAN          | T1_FBI1 |    11 |       |     1 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."SYS_NC00004$"='ZZ')

This posting was prompted by a recent question on OTN. The example is one that I wrote in September 2002 – and modified when dbms_xplan.display_cursor became available in 10g.

Here’s another anomaly with cursor_sharing (fixed in 11g) that might be a source of performance problems.

Footnote: Shortly after finishing this article I discovered from Coskan’s round-up that someone else had beaten me to it by several weeks – but I decided to publish anyway.

Update – Feb 2018

In the “hot topics” email I get from MoS every day, one of the bugs listed as “recently worked on” was Bug 25575369 : FUNCTION BASED INDEX BASED WITH SUBSTR NOT USED WHEN CURSOR_SHARING=FORCE. Reported in Feb 2017 against 12.1.0.2, marked as fixed in 18.1, though there are patches already available for 12.1.0.2.

The bug is a minor, and fairly predictable, variation of the problem above – cursor sharing breaks another of the strategies you might use to work around queries that need to apply functions to columns.

Several times in the past I’ve pointed out that (from 11g onwards) creating and indexing a virtual column is likely to be nicer than creating a function-based index. Moreoever, the optimizer can be very clever if it sees that your SQL has used a function that can be mapped to a virtual column.  Starting with the table above, I might do the following just before gathering stats instead of creating the function-based index:

alter table t1
add (
        vc_virtual      generated always as (substr(vc1,4,2)) virtual
)
;

create index t1_i1 on t1(vc_virtual);

Now, of course, I can just write queries against column vc_virtual and the cursor sharing code won’t see any literals to replace. If, however, someone does write a query that uses the underlying function look what happens:

SQL_ID  0un3zh48njy64, child number 1
-------------------------------------
select  id, vc1 from  t1 where  substr(vc1,4,2) = 'XX'

Plan hash value: 3320414027

---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |       |       |    11 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    |     9 |   126 |    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T1_I1 |     9 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(INTERNAL_FUNCTION("VC1"))
   2 - access("T1"."VC_VIRTUAL"='XX')

The optimizer sees the substr() call in the SQL and rewrites it to use the matching virtual column – unless you’ve enabled cursor_sharing, in which case the bind variable replacement occurs before the column rewrite and the plan looks like this:

SQL_ID  a92r7jthdnvt0, child number 1
-------------------------------------
select  id, vc1 from  t1 where  substr(vc1,:"SYS_B_0",:"SYS_B_1") =
:"SYS_B_2"

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    33 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |   100 |  1100 |    33  (13)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((INTERNAL_FUNCTION("VC1") AND
              SUBSTR(INTERNAL_FUNCTION("VC1"),:SYS_B_0,:SYS_B_1)=:SYS_B_2))

The same thing happened on my (unpatched) 12.2.0.1, although that unexpected extra call to internal_function(vc1) had disappeared from both plans.