Jun 03 2010
Optimizer Mode Mismatch Does Not Prevent Sharing of Child Cursor!?!?
The aim of this post is to describe a strange (buggy) situation that I observed recently. But before doing that, I shortly summarize what a parent cursor and a child cursor are as well as when they can be shared. By the way, I borrowed this description from the pages 20/21 of my book. Hence, if you are interested in more information about this topic refer to it…
The result of a parse operation is a parent cursor and a child cursor stored in the library cache.
The key information related to a parent cursor is the text of the SQL statement. Therefore, several SQL statements share the same parent cursor if their text is exactly the same (note that there is at least an exception to this, specifically when cursor sharing is used). In the following example, four SQL statements are executed. Two have the same text. Two others differ only because of lowercase and uppercase letters or blanks. Through the V$SQLAREA view, it is possible to confirm that three distinct parent cursors were created.
SQL> ALTER SYSTEM FLUSH SHARED_POOL; SQL> SELECT * FROM t WHERE n = 1234; SQL> select * from t where n = 1234; SQL> SELECT * FROM t WHERE n=1234; SQL> SELECT * FROM t WHERE n = 1234; 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 key information related to a child cursor is the execution plan and the execution environment related to it. The execution environment is important because if it changes, the execution plan might change as well. 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 initialization OPTIMIZER_MODE parameter. The result is that a single parent cursor and two child cursors are created.
SQL> ALTER SESSION SET optimizer_mode = all_rows;
SQL> SELECT count(*) FROM t;
COUNT(*)
----------
1000
SQL> ALTER SESSION SET optimizer_mode = first_rows_10;
SQL> SELECT count(*) FROM t;
COUNT(*)
----------
1000
SQL> SELECT sql_id, child_number, sql_text, optimizer_mode, plan_hash_value
2 FROM v$sql
3 WHERE sql_id = (SELECT prev_sql_id
4 FROM v$session
5 WHERE sid = sys_context('userenv','sid'));
SQL_ID CHILD_NUMBER SQL_TEXT OPTIMIZER_MODE PLAN_HASH_VALUE
------------- ------------ ---------------------- -------------- ---------------
5tjqf7sx5dzmj 0 SELECT count(*) FROM t ALL_ROWS 2966233522
5tjqf7sx5dzmj 1 SELECT count(*) FROM t FIRST_ROWS 2966233522
To know which mismatch led to several child cursors, you can query the V$SQL_SHARED_CURSOR view.
SQL> SELECT child_number, optimizer_mode_mismatch
2 FROM v$sql_shared_cursor
3 WHERE sql_id = '5tjqf7sx5dzmj';
CHILD_NUMBER OPTIMIZER_MODE_MISMATCH
------------ -----------------------
0 N
1 Y
So far, so good… Now, let’s see what’s strange…
The interesting thing to point out about the previous example is that while I set FIRST_ROWS_10 as optimizer mode, the V$SQL view displayed the value FIRST_ROWS. Mhmm… That’s strange… They are two different optimizer modes. They cannot be considered equivalent. What are the implications? It is just the view that provides the wrong information or the database engine is able to share the same child cursor even with two different values of the OPTIMIZER_MODE parameter? Let’s try it with FIRST_ROWS (i.e. without “_10”)…
SQL> ALTER SESSION SET optimizer_mode = first_rows;
SQL> SELECT sql_id, child_number, sql_text, optimizer_mode, executions
2 FROM v$sql
3 WHERE sql_id = (SELECT prev_sql_id
4 FROM v$session
5 WHERE sid = sys_context('userenv','sid'));
SQL_ID CHILD_NUMBER SQL_TEXT OPTIMIZER_MODE EXECUTIONS
------------- ------------ --------------------------------- -------------- ----------
5tjqf7sx5dzmj 0 SELECT count(*) FROM t ALL_ROWS 1
5tjqf7sx5dzmj 1 SELECT count(*) FROM t FIRST_ROWS 2
Oh, damn! Even though the OPTIMIZER MODE is set to a different value the same child cursor is used. Since in this particular situation the execution plans associated to both child cursors are the same (their hash value are equal), it’s not a real problem. But, in practice, it might be possible that two different optimizer modes lead to different execution plans. The following example illustrates this.
- Build a table for the test:
SQL> CREATE TABLE t AS
2 SELECT rownum AS id, rpad('*',500,'*') AS pad
3 FROM dual
4 CONNECT BY level <= 1000;
SQL> CREATE UNIQUE INDEX i ON t (id);
SQL> execute dbms_stats.gather_table_stats(user, 'T')
- Show that different values of the OPTIMIZER_MODE parameter lead to different execution plans:
SQL> ALTER SESSION SET optimizer_mode = FIRST_ROWS_1;
SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE id <= 500;
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 242607798
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 1515 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 3 | 1515 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I | | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<=500)
SQL> ALTER SESSION SET optimizer_mode = FIRST_ROWS_1000;
SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE id <= 500;
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 246K| 10 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 500 | 246K| 10 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<=500)
- Execute the test query with both values of the OPTIMIZER_MODE parameter:
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> ALTER SESSION SET optimizer_mode = FIRST_ROWS_1;
SQL> SELECT * FROM t WHERE id <= 500;
ID PAD
---------- ----------
1 **********
2 **********
…
499 **********
500 **********
SQL> ALTER SESSION SET optimizer_mode = FIRST_ROWS_1000;
SQL> SELECT * FROM t WHERE id <= 500;
ID PAD
---------- ----------
1 **********
2 **********
…
499 **********
500 **********
- Show that a single execution plan was used for both executions:
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID 2vw03p929jzgz, child number 0
-------------------------------------
SELECT * FROM t WHERE id <= 500
Plan hash value: 242607798
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 3 | 1515 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I | | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<=500)
SQL> SELECT sql_id, child_number, executions, optimizer_mode
2 FROM v$sql
3 WHERE sql_id = '2vw03p929jzgz';
SQL_ID CHILD_NUMBER EXECUTIONS OPTIMIZER_MODE
------------- ------------ ---------- --------------
2vw03p929jzgz 0 2 FIRST_ROWS
Even though it is not very likely that this bug (yes, in my opinion something like this cannot be considered a restriction of the implementation…) has an impact on a production system, I really don’t understand why the developers didn’t implement it correctly. It should not be that difficult to manage a byte containing the information about the used optimizer mode! Note that this is not the only case where something like that happens with the first rows optimizer mode. For example, also in a trace file generated through SQL trace no difference is made between the old and the new first row optimizer. So, it seams that they really got it wrong.