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.
I wonder if a because the FIRST_ROWS optimizer mode has an “infinite” number of possibilities due
the FIRST_ROWS(n) hint e.g.
SELECT /*+ FIRST_ROWS(15) */ employee_id, last_name, salary, job_id
…
which should it show in the above case ?
Andy
Hi Andy
Good comment. But, honestly, such an issue can only cause a view like v$sql_shared_cursor to show (partially) wrong information and not a false sharing. BTW, there are several parameters with a similar “behavior”… Take for example optimizer_index_cost_adj. When you change its value the child cursor is not shared and the view v$sql_shared_cursor reports a generic “optimizer_mismatch”.
In addition, the issue of a high number of possibilities only happens when the hint is used. And, when the hint is used, there is no child cursor that can be shared. In fact, even the parent cursor cannot be shared.
Cheers,
Chris
Hello , We are seeing the same behavior in one of our 11gr2 databases , every time this sql got executed its spawning a new child cursors either due to Cardinality feedback or optimizer_mismatch and some time due to both reasons , Majority of time is due to optimizer_mismatch.
As its creating a child cursor every resulting in a hard parse consuming significant of systems resources, Do we have any view in database which records what’s the actual difference during optimizer_mistamtch like ALL_ROWS , FIRST_ROWS ,we know the reason why optimizer choosen to generate a new plan , but how do we find that exact value that optimizer is seeing and spawning a new cursors ??
Thank for you help in advance.
-rajesh
Hi Rajesh
In $sql_optimizer_env you can see the optimizer environment at child cursor level. In addition, if you are in 11.2.0.2+, you should see what you are looking for in v$sql_shared_cursor.reason.
HTH
Chris