oracle parent cursor && child cursor and three views，v$sql v$sqlarea v$sql_shared_cursor

What on earth is a cursor.

My understanding for cursor. It`s some internal type oracle used to represent a sql statement. With the cursor, oracle knows how to access data how to computer out a result. Basically you can see it as the execution plan.

The relationship between v$sqlarea and v$sql

First we need to know what are these three views.

V$SQLAREA lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.

V$SQL lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered. Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.

V$SQL_SHARED_CURSOR explains why a particular child cursor is not shared with existing child cursors. Each column identifies a specific reason why the cursor cannot be shared.

We could say the v$sqlarea is the parent of the v$sql. v$sqlarea records the information of the parent cursor while v$sql preserve the information of child cursor . There is one column called VERSION_COUNT in the v$sqlarea, its value can tell us how many child cursors does this parent cursor have.

What is the parent cursor and child cursor?

When oracle parse a sql in the first time, it will generate a cursor. This is the parent cursor. At this time you will find a record from the v$sqlarea for this sql. And the version_count will be 1 because it has no child cursor, only one version.

SQL> alter system flush shared_pool;

System altered.

SQL> select * from dual where 1=8;

no rows selected

SQL> select sql_id,sql_text,version_count from v$sqlarea where sql_text='select * from dual where 1=8';

SQL_ID                                  SQL_TEXT                                 VERSION_COUNT
--------------------------------------- ---------------------------------------- -------------
ghzzv229yj9jh                           select * from dual where 1=8                         1

If I issue this SQL again, Oracle will do some check. For example, the optimizer goal, the bind variable peek and so on. If there is some factors will make the execution plan change, oracle will open a sub-cursor for this statement. You can find the version_count in the v$sqlarea is added to 2 and you can find the corresponding records in the v$sql.

SQL> alter session set optimizer_mode=first_rows_10;

Session altered.

SQL> select * from dual where 1=8;

no rows selected

SQL> select sql_id,sql_text,version_count from v$sqlarea where sql_text='select * from dual where 1=8';

SQL_ID                                  SQL_TEXT                                 VERSION_COUNT
--------------------------------------- ---------------------------------------- -------------
ghzzv229yj9jh                           select * from dual where 1=8                         2

SQL> select sql_id,child_number,sql_text from v$sql where sql_text='select * from dual where 1=8';

SQL_ID                                  CHILD_NUMBER SQL_TEXT
--------------------------------------- ------------ ----------------------------------------
ghzzv229yj9jh                                      0 select * from dual where 1=8
ghzzv229yj9jh                                      1 select * from dual where 1=8

So here we can make a summarize. If the text for two SQL is excatly the same. They may share the CURSOR. If they do not share the cursor, then there will be some mismatch.

So what is the mismatch? You can check the v$sql_shared_cursor. V$SQL_SHARED_CURSOR explains why a particular child cursor is not shared with existing child cursors. Each column identifies a specific reason why the cursor cannot be shared.

In our case, we can check the v$sql_sahred_cursor in this way.

SQL> select * from v$sql_shared_cursor where SQL_;

SQL_ID                                  ADDRESS          CHILD_ADDRESS    CHILD_NUMBER UNB SQL OPT OUT STA LIT SEC EXP BUF PDM INS SLA TYP AUT BIN DES LAN TRA ROW INS INS REM LOG INC OVE SQL MV_ USE TYP NO_ FLA ANY INC TOP DIF LOG DIF BIN PLS CUR STB ROW PQ_ TOP MUL BIN MV_ ROL OPT PX_ MV_ FLA LIT
--------------------------------------- ---------------- ---------------- ------------ --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
ghzzv229yj9jh                           0000000392378D20 0000000398362908            0 N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N
ghzzv229yj9jh                           0000000392378D20 00000003983B3688            1 N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   N   Y   N   N   N   N

The result seems a mess. But you just need to find out which column has a Y as the value. Then the column name wil shows you what is the mismatch .