Oracle Scratchpad

May 3, 2010

Cursor Sharing 3

Filed under: Hints,Indexing,Tuning — Jonathan Lewis @ 8:57 pm BST May 3,2010

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
)
select
	rownum				id,
	dbms_random.string('l',6)	vc1,
	rpad('x',100)			padding
from
	generator	v1,
	generator	v2
where
        rownum <= 10000         -- > hint 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:


PLAN_TABLE_OUTPUT
-------------------------------------
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:


PLAN_TABLE_OUTPUT
-------------------------------------
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.

6 Comments »

  1. I have witnessed this behavior but never thought of encapsulating function-based expression inside a view.

    Comment by Asif Momen — May 4, 2010 @ 7:22 am BST May 4,2010 | Reply

    • Asif,

      Sometimes you get a good idea by going back and looking again at something you’ve done in the past and asking yourself if there’s a (possibly new) alternative. Your comment here, for example, prompted me to think of the “obvious” new solution – if you’re running 11g you just declare a virtual column to match the substr() requirement and create an index on the virtual column (remembering to collect stats on the virtual column, of course) then modify the code accordingly.

      Comment by Jonathan Lewis — May 4, 2010 @ 11:03 pm BST May 4,2010 | Reply

  2. […] also shares an interesting case where a function based index is not used, even though it should […]

    Pingback by Log Buffer #188, a Carnival of the Vanities for DBAs | The Pythian Blog — May 10, 2010 @ 3:00 pm BST May 10,2010 | Reply

  3. Hi Jonathan,

    I think I have a similar issue. I created a FBI which is used when queried “directly” but not when used in an IN clause. Maybe this is due to the optimizer not taking into account that index for some reason.

    Example :

    Select ...
    From some_table
    Where translate(some_column,'0123...abc...ABC...','0123')='123';
    

    That case works even using cs similar, but this other doesn’t :

    Select ...
    From some_join
    Where filter column in ( select_from_prior_example );
    

    This case doesn’t take FBI into account unless I add the hint :

    Select /*+ cursor_sharing_exact */...
    From some_join
    Where filter column in ( select_from_prior_example );
    

    I have tried your view approach bu didn’t work.

    Can you shed some light on this ?

    Thanks,
    Bertran.

    Comment by Bertran Saragusti — March 21, 2011 @ 6:40 pm BST Mar 21,2011 | Reply

    • Bertran
      I am surprised that this works at all when cursor_sharing is set to anything other than exact.

      The fact that the index doesn’t work in the subquery example may be nothing to do with the setting for cursor_sharing, and may depend on other transformations taking place. In the absence of any information about the version of Oracle, the actual execution plan, and the actual use of predicates, it’s impossible to come to any conclusion.

      Comment by Jonathan Lewis — March 26, 2011 @ 12:55 pm BST Mar 26,2011 | Reply

  4. Hi Jonathan,

    Sorry I didn’t put another comment earlier but I didn’t get your post via email ( I think I checked the box !!! weird ).

    Anyway… our database is 10.2.0.4 and I finally solved the issue adding a hint with the name of the index :

    SELECT ...
    FROM ...
    WHERE ...
      and ... IN (SELECT /*+ index(pp FBI)*/...
                  FROM ... PP 
                  WHERE ... );
    

    That approach is much better than the hint “cursor_sharing_exact” for our needs.

    Bertran.

    Comment by Bertran Saragusti — April 13, 2011 @ 3:48 pm BST Apr 13,2011 | Reply


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:

WordPress.com Logo

You are commenting using your WordPress.com 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.

Powered by WordPress.com.