Database Reference
In-Depth Information
number of hard parses, and decrease the library latch waits we discussed in the Architecture sections—
but
(there is
always a but) it can have some side effects. A common side effect with cursor sharing is something like this:
EODA@ORA12CR1> select /* TAG */ substr( username, 1, 1 )
2 from all_users au1
3 where rownum = 1;
S
-
S
EODA@ORA12CR1> alter session set cursor_sharing=force;
Session altered.
EODA@ORA12CR1> select /* TAG */ substr( username, 1, 1 )
2 from all_users au2
3 where rownum = 1;
SUBSTR(USERNAME,1,1)
-------------------------------------------------------------------------------
S
What happened there? Why is the column reported by SQL*Plus suddenly so large for the second query, which is
arguably the same query? If we look at what the cursor sharing setting did for us, it (and something else) will become
obvious:
EODA@ORA12CR1> select sql_text from v$sql where sql_text like 'select /* TAG */ %';
SQL_TEXT
-------------------------------------------------------------------------------
select /* TAG */ substr( username, 1, 1 )
from all_users au1
where rownum = 1
select /* TAG */ substr( username, :"SYS_B_0", :"SYS_B_1" )
from all_users au2
where rownum = :"SYS_B_2"
The cursor sharing removed information from the query. It found
every
literal, including the
substr
constants
we were using. It removed them from the query and replaced them with bind variables. The SQL engine no longer
knows that the column is a
substr
of length 1—it is of indeterminate length. Also, you can see that
where rownum = 1
is now bound as well. This seems like a good idea; however, the optimizer has just had some important information
removed. It no longer knows that “this query will retrieve a single row;” it now believes “this query will return the first
N rows and N could be any number at all.” This can have a negative impact on your generated query plans.
Additionally, I have shown that while
CURSOR_SHARING = FORCE
runs much faster than parsing and optimizing
lots of unique queries (refer to the preceding section on bind variables), I have also found it to be slower than using
queries where the developer did the binding. This arises not from any inefficiency in the cursor-sharing code, but
rather in inefficiencies in the program itself. In many cases, an application that does not use bind variables is not
efficiently parsing and reusing cursors either. Since the application believes each query is unique (it built them as
unique statements), it will never use a cursor more than once. The fact is that if the programmer had used bind
variables in the first place, she could have parsed a query once and reused it many times. It is this overhead of parsing
that decreases the overall potential performance.
