First page Back Continue Last page Graphics

Cursor Sharing Example

Notes:

This example demonstrates both what cursor sharing does and one of the problems the FORCE option can create.
First, I executed two identical queries except for the character constant. The TKPROF trace shows that:
The constant was replaced with a bind variable (SYS_B_0).
The cursor was reused by the second query. After the constant was replaced it was identical to the first query.
Only one hard parse was performed for the two queries. (See “Misses in library cache during parse: 1”)
Two soft parses were performed. (See “Parse 2”)
Regarding a problem the FORCE option can create, note that both queries used the same plan (INDEX RANGE SCAN). However, left to it’s own devices, CBO would of (and properly so) chosen a full table scan for the second query because it knows that a large percentage of the rows contain ‘SYNONYM’ in the OBJECT_TYPE column. (I created a histogram on the OBJECT_TYPE column.)
Refer to the supplied script cursor_sharing.sql for a working copy of this demonstration.