Mohamed Houri’s Oracle Notes

May 10, 2020

Why my execution plan has not been shared: Part 6

Filed under: Oracle — hourim @ 9:57 am

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:

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

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: