Select COUNT(1) is faster when cursor_sharing=exact compared to cursor_sharing=similar (Doc ID 867342.1)

Last updated on AUGUST 05, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.2 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Symptoms

SQLs using count(literal) like count(1)  is taking longer to run when cursor_sharing=similar/force, with the existence of bitmap index

select /* cursor_sharing= EXACT */ count(1)
from  Table_1; 


Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=5339 pr=5311 pw=0 time=2373003 us)
62590 BITMAP CONVERSION COUNT (cr=5339 pr=5311 pw=0 time=3078949 us)
62590 BITMAP INDEX FAST FULL SCAN TABLE_1_IDX_002 (cr=5339 pr=5311 pw=0 time=2640719 us)(object id 213076)

select /* cursor_sharing= SIMILAR */ count(1)
from Table_1;


Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=5339 pr=5311 pw=0 time=190421528 us)
148649048 BITMAP CONVERSION TO ROWIDS (cr=5339 pr=5311 pw=0 time=6094631315 us)
62590 BITMAP INDEX FAST FULL SCAN TABLE_1_IDX_002 (cr=5339 pr=5311 pw=0 time=5590735 us)(object id 213076)

From the execution plan, it is clear that BITMAP CONVERSION COUNT  operation is used  when
cursor_sharing=exact(which runs in less time ) and BITMAP CONVERSION TO ROWIDS  is used
when  cursor_sharing=similar/force ( which uses much more time ).

Changes

The change happens when the SQL is executed under cursor_sharing=force/similar, and the SQL is converted to bind variable:

select /* cursor_sharing= SIMILAR */ count(:"SYS_B_0")
from Table_1;

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms