Oracle Scratchpad

May 3, 2010

Cursor Sharing 3

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

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.


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
;

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

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		estimate_percent => 100,
		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 clue in the cardinality estimate for the run-time plan.)

Your first response, of course, should be to tell me off for not showing the complete execution plan. Always genreate and look at the predicate sections 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 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.

7 Comments »

  1. I took a slightly different approach on Oracle 10.2.0.4 and left cursor_sharing set to its default value EXACT.

    After I created the index I queried the user_tab_cols view.

    jbrock@orcl10g-local> select column_name, num_distinct, hidden_column,virtual_column
    2 from user_tab_cols where table_name = ‘T1′;

    COLUMN_NAME NUM_DISTINCT HID VIR
    —————————— ———— — —
    ID NO NO
    VC1 NO NO
    PADDING NO NO
    SYS_NC00004$ YES YES

    Appears Oracle creates a hidden/virtual column for FBIs.
    NUM_DISTINCT is null because I haven’t gathered stats.

    jbrock@orcl10g-local> begin
    2 dbms_stats.gather_table_stats(
    3 ownname => user,
    4 tabname =>’T1′,
    5 estimate_percent => 100,
    6 method_opt => ‘for all hidden columns size 1′
    7 );
    8 end;
    9 /

    For FBIs I usually used the ‘hidden columns’ trick I learned from Richard Foote’s seminar.

    Now I rerun the user_tab_cols query again.

    jbrock@orcl10g-local> select column_name, num_distinct, hidden_column,virtual_column
    2 from user_tab_cols where table_name = ‘T1′;

    COLUMN_NAME NUM_DISTINCT HID VIR
    —————————— ———— — —
    ID NO NO
    VC1 NO NO
    PADDING NO NO
    SYS_NC00004$ 676 YES YES

    Now that it can see some values I continue with the script you posted and got the following execution plan.

    alter session set statistics_level=’all';
    select * from table(dbms_xplan.display_cursor);

    —————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    —————————————————————————————
    | 0 | SELECT STATEMENT | | | | 16 (100)| |
    | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 15 | 1710 | 16 (0)| 00:00:01 |
    |* 2 | INDEX RANGE SCAN | T1_FBI1 | 15 | | 1 (0)| 00:00:01 |
    —————————————————————————————
    Predicate Information (identified by operation id):
    —————————————————
    2 – access(“T1″.”SYS_NC00004$”=’zz’)

    Comment by Jimmy — May 3, 2010 @ 9:53 pm GMT May 3,2010 | Reply

  2. 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 GMT 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 GMT May 4,2010 | Reply

  3. […] 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 GMT May 10,2010 | Reply

  4. 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 GMT 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 GMT Mar 26,2011 | Reply

  5. 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 GMT Apr 13,2011 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,430 other followers