In this sixth installment of the series of articles about the non-sharing reasons that prevent Oracle from using an existing execution plan, I am going to dig into a new supplementary reason which is Session Specific Cursor Session Mismatch. For the sake of completeness here’s below the list of already investigated non-sharing reasons:
- Part 1 : Hash_match_failed and load_optimizer_stats
- Part 2 : Roll_invalid_mismatch and top_level_rpi_cursor
- Part 3 : Bind_mismatch and stb_object_mismatch
- Part 4 : PQ_slave_mismatch and optimizer_mismatch
- Part 5 : Language_mismatch and auth_check_mismatch
- Part 6 : Session Specific Cursor Session Mismatch
I didn’t find any definition from the official Oracle documentation for the Session Specific Cursor non-sharing reason. So let’s start immediately by building a test case with which we will provoke the apparition of this non-sharing reason
SQL> select banner from v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> select distinct sid from v$mystat;
SID
----------
16
SQL> create global temporary table t1 (owner varchar2(128), object_type varchar2(30)) on commit preserve rows;
Table created.
SQL> insert into t1 select owner, object_type from all_objects where owner ! = 'SYS';
19296 rows created.
SQL> commit;
SQL> col prefs format a20
SQL> select dbms_stats.get_prefs('GLOBAL_TEMP_TABLE_STATS') prefs from dual;
PREFS
--------------------
SESSION
SQL> exec dbms_stats.gather_table_stats(user, 't1');
Using session id n°16, I have created a simple global temporary table, inserted into it few rows and ended up by gathering statistics on this table using the default private SESSION preference. Simply put, statistics gathered under this preference are not propagated to all other sessions. This is why it is also known as PRIVATE statistic. The current session global temporary table (GTT) statistics are not shared with other sessions using the same GTT.
This is clearly shown via the following select
SQL> SELECT
table_name,
blocks,
num_rows,
scope
FROM
user_tab_statistics
WHERE
table_name = 'T1';
TABLE_NAME BLOCKS NUM_ROWS SCOPE
------------ ---------- ---------- -------
T1 SHARED
T1 55 19296 SESSION
As you can see, the only available statistics are those that are local to the current session.
Let’s now execute a simple query and get its execution plan
SQL>select owner, count(1)
from t1
where object_type = 'TABLE'
group by owner
order by 2 desc;
OWNER COUNT(1)
------------------------------ ----------
MDSYS 151
SYSTEM 131
ORDDATA 90
XDB 56
CTXSYS 53
DVSYS 44
GSMADMIN_INTERNAL 42
WMSYS 40
LBACSYS 22
DBSNMP 20
OJVMSYS 6
C##MHOURI 5
APPQOSSYS 5
ORDSYS 4
DBSFWUSER 3
OUTLN 3
OLAPSYS 2
AUDSYS 1
18 rows selected.
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID c1j862cvqgh99, child number 0
-------------------------------------
select owner, count(1) from t1 where object_type = 'TABLE' group by
owner order by 2 desc
Plan hash value: 2808104874
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 19 (100)| |
| 1 | SORT ORDER BY | | 24 | 360 | 19 (11)| 00:00:01 |
| 2 | HASH GROUP BY | | 24 | 360 | 19 (11)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T1 | 772 | 11580 | 17 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("OBJECT_TYPE"='TABLE')
Note
-----
- Global temporary table session private statistics used
The above Note at the bottom of the execution plan is clearly indicating that Oracle has used the session private statistics of the GTT.
To end up the setup I am going to run the same query but from a different session as shown below:
SQL> select distinct sid from v$mystat;
SID
----------
394
SQL> select count(1) from t1;
COUNT(1)
----------
0
SQL> insert into t1 select owner, object_type from all_objects where owner = 'CTXSYS';
SQL>select owner, count(1)
from t1
where object_type = 'TABLE'
group by owner
order by 2 desc;
OWNER COUNT(1)
-------------- ----------
CTXSYS 53
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID c1j862cvqgh99, child number 1 --> new child cursor
-------------------------------------
select owner, count(1) from t1 where object_type = 'TABLE' group by
owner order by 2 desc
Plan hash value: 2808104874
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT ORDER BY | | 53 | 4399 | 4 (50)| 00:00:01 |
| 2 | HASH GROUP BY | | 53 | 4399 | 4 (50)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T1 | 53 | 4399 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("OBJECT_TYPE"='TABLE')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
As you can see the same query has refused to share the previous execution plan (child cursor 0 from session 16) and has hard parsed a new child cursor n°1 (in session 394). This is simply due to the following reason:
SQL> @nonshared c1j862cvqgh99
Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)...
SQL_ID : c1j862cvqgh99
ADDRESS : 00007FFCCD36B030
CHILD_ADDRESS : 00007FFCCD3698D8
CHILD_NUMBER : 0
REASON : <ChildNode><ChildNumber>0</ChildNumber><ID>46</ID>
<reason>Session Specific Cursor SessionMismatch(1)</reason>
<size>7x4</size><session_mismatch>1</session_mismatch>
<current_qcsesidx>394</current_qcsesidx> --> here the current SID
<current_qcsesser>58570</current_qcsesser><current_qcinst>1</current_qcinst>
<stored_qcsesidx>16</stored_qcsesidx> --> the SID of the original child cursor
<stored_qcsesser>58601</stored_qcsesser>
<stored_qcinst>1</stored_qcinst></ChildNode>
CON_ID : 1
-----------------
SQL_ID : c1j862cvqgh99
ADDRESS : 00007FFCCD36B030
CHILD_ADDRESS : 00007FFCCD348328
CHILD_NUMBER : 1
REASON :
CON_ID : 1
-----------------
PL/SQL procedure successfully completed.
Conclusion
In light of what I have demonstrated above I can, finally, define this non-sharing reason as:
(Y|N) The session specific cursor environment does not match the existing child cursor
With the first session mismatch being:
- SessionMismatch(1) –> points to GTT private statistics
Mohamed Houri’s Oracle Notes 
Leave a comment