Shared SQL and Cursors

Recently I had a discussion on “Shared SQLs and Cursors” with one of my colleagues and during the discussion it came out that “shared SQL” and “cursors” are misunderstood. I thought of blogging on this topic to help those who still misunderstand these topics.

As per his understanding “Shared SQL” and “Cursors” are the same and shared among all user sessions. But in fact:

Shared SQL is the SQL residing in the shared pool. This SQL statement can be shared among all the database sessions. The shared SQL is at the database level, all sessions can see and use them.

A Cursor points to some shared SQL residing in the shared pool. You may have more than one cursor pointing to the same shared SQL. A cursor is at the session level. So, many database sessions may point to the same shared SQL.

The below diagram should help understand this concept:

Below is a demonstration of the same. I have four database connections, one as “SYS” user and other three connected as user “TEST”. I make a note of SIDs of each database connection as user “TEST”. My session ids are listed below:

“TEST” User ---------> SID

TEST Session – 1 ---------> 30

TEST Session – 2 ---------> 27

TEST Session – 3 ---------> 26

I shall be using table “T1” for this test which is residing in “TEST” schema.

SQL> desc t1
 Name             Null?    Type
 ---------------- -------- -------------
 ID               NOT NULL NUMBER
 NAME             NOT NULL VARCHAR2(30)

SQL>

Below is the query that will help us with our experiment. Make sure that you use the same query in all the sessions. For simplicity you could save this query in a file.

select /*momen*/ * from t1 where id = 1;

Session – SYS:

As user SYS, flush the shared pool and query V$SQL and V$OPEN_CURSOR views for our query:

SQL> alter system flush shared_pool;

System altered.

SQL> select parse_calls, executions, invalidations, loads, sql_id, substr(sql_text, 1, 20) from v$sql where sql_text like '%momen%';

PARSE_CALLS EXECUTIONS INVALIDATIONS      LOADS SQL_ID        SUBSTR(SQL_TEXT,1,20
----------- ---------- ------------- ---------- ------------- --------------------
          1          1             0          1 fnndahay4xbgv select parse_calls,

SQL>
SQL> select sid, sql_id, sql_text from v$open_cursor where user_name='TEST' and sql_text like '%momen%';

no rows selected

SQL>

Rightly, this query is not found in the shared pool.

TEST Session – 1:

Execute the query in session – 1

SQL> select /*momen*/ * from t1 where id = 1;

        ID NAME
---------- ------------------------------
         1 ICOL$a

SQL>

Session – SYS:

As the user SYS again query the V$SQL and V$OPEN_CURSOR view for our query.

SQL> select parse_calls, executions, invalidations, loads, sql_id, substr(sql_text, 1, 20) from v$sql where sql_text like '%momen%';

PARSE_CALLS EXECUTIONS INVALIDATIONS      LOADS SQL_ID        SUBSTR(SQL_TEXT,1,20
----------- ---------- ------------- ---------- ------------- --------------------
          1          1             0          1 fvnwsdghq898n select /*momen*/ * f
          2          2             0          1 fnndahay4xbgv select parse_calls,
          1          1             0          1 6wcgj9dxb3yvx select sid, sql_id,

SQL>
SQL> select sid, sql_id, sql_text from v$open_cursor where user_name='TEST' and sql_text like '%momen%';

       SID SQL_ID        SQL_TEXT
---------- ------------- ------------------------------------------------------------
        30 fvnwsdghq898n select /*momen*/ * from t1 where id = 1

SQL>

Our query now appears in both V$SQL & V$OPEN_CURSOR views. There’s one cursor now pointing to the shared SQL residing in the shared pool.

TEST Session – 2:

Now run the same query in another session.

SQL> select /*momen*/ * from t1 where id = 1;

        ID NAME
---------- ------------------------------
         1 ICOL$a

SQL>

Session – SYS:

Go back to the SQL*Plus session connection as user SYS and execute the same set of queries against V$SQL & V$OPEN_CURSOR.

 SQL> select parse_calls, executions, invalidations, loads, sql_id, substr(sql_text, 1, 20) from v$sql where sql_text like '%momen%';

PARSE_CALLS EXECUTIONS INVALIDATIONS      LOADS SQL_ID        SUBSTR(SQL_TEXT,1,20
----------- ---------- ------------- ---------- ------------- --------------------
          2          2             0          1 fvnwsdghq898n select /*momen*/ * f
          3          3             0          1 fnndahay4xbgv select parse_calls,
          2          2             0          1 6wcgj9dxb3yvx select sid, sql_id,

SQL>
SQL> select sid, sql_id, sql_text from v$open_cursor where user_name='TEST' and sql_text like '%momen%';

       SID SQL_ID        SQL_TEXT
---------- ------------- ------------------------------------------------------------
        30 fvnwsdghq898n select /*momen*/ * from t1 where id = 1
        27 fvnwsdghq898n select /*momen*/ * from t1 where id = 1

SQL>

As seen above, we now have two cursors pointing to the same shared SQL.

TEST Session – 3:

You may stop here or proceed to see how one more cursor is created to point to the same shared SQL in the shared pool.

SQL> select /*momen*/ * from t1 where id = 1;

        ID NAME
---------- ------------------------------
         1 ICOL$a

SQL>

Session – SYS:

SQL> select parse_calls, executions, invalidations, loads, sql_id, substr(sql_text, 1, 20) from v$sql where sql_text like '%momen%';

PARSE_CALLS EXECUTIONS INVALIDATIONS      LOADS SQL_ID        SUBSTR(SQL_TEXT,1,20
----------- ---------- ------------- ---------- ------------- --------------------
          3          3             0          1 fvnwsdghq898n select /*momen*/ * f
          4          4             0          1 fnndahay4xbgv select parse_calls,
          3          3             0          1 6wcgj9dxb3yvx select sid, sql_id,

SQL>
SQL> select sid, sql_id, sql_text from v$open_cursor where user_name='TEST' and sql_text like '%momen%';

       SID SQL_ID        SQL_TEXT
---------- ------------- ------------------------------------------------------------
        30 fvnwsdghq898n select /*momen*/ * from t1 where id = 1
        27 fvnwsdghq898n select /*momen*/ * from t1 where id = 1
        26 fvnwsdghq898n select /*momen*/ * from t1 where id = 1

SQL>

We now have three cursors pointing to the same shared SQL.

So, a cursor and shared SQL are two different things. The same shared SQL can have more than one cursor pointing to it.