SQL Versions

How to find the number of SQL Versions
https://blogs.oracle.com/PerformanceDiagnosis/entry/diagnosis_of_a_high_version


This is a short summary from Tom Kyte article On Seeing Double in V$SQL

When querying V$SQL one might find several entries for same sql_id and same sql_text:

SELECT sql_id, sql_text 
  FROM V$SQL 
 WHERE UPPER(SQL_TEXT) LIKE 'SELECT % T LOOK_FOR_ME%';

Why would SQL have many versions?
Each time there is a different execution plan, there would be a new entry in V$SQL.

Option A - Two users ran same SQL text- but each one got his own objects.
Option B - Different input parameter type, would cause implicit TO_CHAR/TO_NUMBER conversion in SQL query.
                     This could be seen in Predicate Information part of the Execution Plan.
Option C - Different Optimizer mode in a session (ALL_ROWS vs FIRST_ROWS) would cause a new entry to V$SQL.

SQL Parent Cursor and SQL Child Cursor definitions.
Parent Cursor - The entry in V$SQL.
                                When two statements are identical textually, they will share a same Parent Cursor.
Child Cursor - The Child Cursor holds other required information.
                             For example: 
                             - Identity of the referenced objects the SQL Cursor;
                             - Bind variables names, type and length.
                             - Sessions parameter value of optimizer related settings, that have an impact on the optimizer decisions and the execution plan of the Cursor.


V$SQL_SHARED_CURSOR
To find the various versions for SQL cursors, need to query V$SQL_SHARED_CURSOR:

SELECT sql_id, 
       child_address, 
       sql_type_mismatch, 
       optimizer_mismatch, 
       literal_mismatch
       --other mismatch columns...
  FROM V$SQL_SHARED_CURSOR 

Each sql_id got child_ids, starting from zero.

For example:

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('1qqtru155tyz8',0))

PLAN_TABLE_OUTPUT
-------------------------
SQL_ID  1qqtru155tyz8, child number 0
-----------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1

Plan hash value: 1601196873

------------------------------------------------------------------------------
| Id  |  Operation          | Name | Rows  | Bytes | Cost (%CPU) | Time       |
------------------------------------------------------------------------------
|   0 |  SELECT STATEMENT   |      |       |       |             | 30891 (100)|
|*  1 |  TABLE ACCESS FULL| T    | 10000 |   292K| 30891    (2)| 00:02:27   |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
----------------------
   1 - filter(TO_NUMBER("X")=:B1)

18 rows selected.

Note:

ALL_ROWS - would cause TABLE ACCESS FULL

TO_NUMBER - is the cause from numeric input parameter.

SQL> select * from table( dbms_xplan.display_cursor('1qqtru155tyz8', 3 ) );

PLAN_TABLE_OUTPUT
------------------------------------
SQL_ID  1qqtru155tyz8, child number 3
--------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1

Plan hash value: 3817779948

---------------------------------------------------------------------------------
| Id  |  Operation                    | Name         | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 |  SELECT STATEMENT             |              |      |       |     2 (100)|
|   1 |    TABLE ACCESS BY INDEX ROWID| T            |    1 |     30|     2   (0)|
|*  2 |     INDEX UNIQUE SCAN         | SYS_C0023438 |    1 |       |     1   (0)|
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
-------------------------
   2 - access("X"=:B1)

19 rows selected.

Note:
FIRST_ROWS - would cause INDEX UNIQUE SCAN
With matching input parameter - there is no implicit TO_NUMBER conversion.


Querying V$SQL_SHARED_CURSOR:

 SELECT sql_id,
        child_number, 
        bind_mismatch bind_mismatch, 
        optimizer_mode_mismatch optimizer_mismatch
   FROM V$SQL_SHARED_CURSOR
  WHERE sql_id = '1qqtru155tyz8'
  ORDER BY child_number




 sql_id         child_number bind_mismatch optimizer_mismatch
 -------------  ------------ ------------- ------------------
 1qqtru155tyz8             0 N             N
 1qqtru155tyz8             1 Y             N
 1qqtru155tyz8             2 N             Y
 1qqtru155tyz8             3 Y             Y