Key Concepts - Troubleshooting Oracle Performance - page 27

Because the execution environment is different, two child cursors with the same parent cursor are used.
Notice that in this case, the mismatch is visible through the v$sql_shared_cursor view, specifically in the
language_mismatch column:

SQL> SELECT sql_id, child_number, plan_hash_value, executions
2 FROM v$sql
3 WHERE sql_text = 'SELECT * FROM t ORDER BY pad';

SQL_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS
------------- ------------ --------------- ----------
1f7qg6nu40shd 0 961378228 1
1f7qg6nu40shd 1 961378228 1

SQL> SELECT child_number, language_mismatch
2 FROM v$sql_shared_cursor
3 WHERE sql_id = '1f7qg6nu40shd'
4 AND child_number > 0;

CHILD_NUMBER LANGUAGE_MISMATCH
------------ -----------------
1 Y

In practice, it's quite common to see more hard parses caused by nonshared parent cursors than nonshared child
cursors. In fact, more often than not, there are few child cursors for each parent cursor. If the parent cursors can't be
shared, it almost always means that the text of SQL statements changes constantly. This happens if either the SQL
statements are dynamically generated by the application or literals are used instead of bind variables. In general,
dynamically generated SQL statements can't be avoided. On the other hand, it's usually possible to use bind variables.
Unfortunately, it isn't always good to use them. The following discussion of the pros and cons of bind variables will
help you understand when it's good and not so good to use them.

Bind Variables
Bind variables impact applications in three ways. First, from a development point of view, they make programming
either easier or more difficult (or more precisely, more or less code must be written). In this case, the effect depends
on the application programming interface used to execute the SQL statements. For example, if you're programming
PL/SQL code, it's easier to execute them with bind variables. On the other hand, if you're programming in Java
with JDBC, it's easier to execute SQL statements without bind variables. Second, from a security point of view, bind
variables mitigate the risk of SQL injection. Third, from a performance point of view, bind variables introduce both an
advantage and a disadvantage.

■ In the following sections, you'll see some execution plans. Chapter 10 explains how to obtain and interpret
execution plans. you might consider returning to this chapter after reading Chapter 10 if something isn't clear.

Note