Database Reference
In-Depth Information
Through the
v$sqlarea
view, it's possible to confirm that three distinct parent cursors were created. Also notice
the number of executions for each cursor:
SQL> SELECT sql_id, sql_text, executions
2 FROM v$sqlarea
3 WHERE sql_text LIKE '%1234';
SQL_ID SQL_TEXT EXECUTIONS
------------- --------------------------------------- ----------
2254m1487jg50 select * from t where n = 1234 1
g9y3jtp6ru4cb SELECT * FROM t WHERE n = 1234 2
7n8p5s2udfdsn SELECT * FROM t WHERE n=1234 1
The aim of the second example, based on the
sharable_child_cursors.sql
script, is to show a case where the
parent cursor, but not the child cursor, can be shared. The key information related to a child cursor is an execution
plan and the execution environment related to it. As a result, several SQL statements are able to share the same child
cursor only if they share the same parent cursor and their execution environments are compatible. To illustrate,
the same SQL statement is executed with two different values of the
optimizer_mode
initialization parameter:
SQL> ALTER SESSION SET optimizer_mode = all_rows;
SQL> SELECT count(*) FROM t;
COUNT(*)
--------
1000
SQL> ALTER SESSION SET optimizer_mode = first_rows_1;
SQL> SELECT count(*) FROM t;
COUNT(*)
--------
1000
The result is that a single parent cursor (5tjqf7sx5dzmj) and two child cursors (0 and 1) are created. It's also
essential to note that both child cursors have the same execution plan (the
plan_hash_value
column has the same
value). This shows very well that a new child cursor was created because of a new individual execution environment
and
not
because another execution plan was generated:
SQL> SELECT sql_id, child_number, optimizer_mode, plan_hash_value
2 FROM v$sql
3 WHERE sql_text = 'SELECT count(*) FROM t';
SQL_ID CHILD_NUMBER OPTIMIZER_MODE PLAN_HASH_VALUE
------------- ------------ -------------- ---------------
5tjqf7sx5dzmj 0 ALL_ROWS 2966233522
5tjqf7sx5dzmj 1 FIRST_ROWS 2966233522
