Performance tuning: whether SQL plan will be shared if underlining objects not the same

Question: I have many schemas, each with same table names, thus my application will use the same SQL statements, but running under different schema. Does it mean that SQL statement parse once and used by all the applications?

Answers: There is one parent cursor, since it determined by SQL Text. But different child cursors (versions) for the same SQL statement. (If either parent cursor or child cursor not in library cache, Oracle must "hard" parse the SQL statement.)

Below is one example, I have created 2 users, user1 and user2, each with one table t. But there is one index on user1.t(object_id).

Same SQL ID (aka. parent cursor), but 2 versions (aka, child cursors). We also notice that PLAN_HASH_VALUE is different.

SQL> select sql_id,sql_text,executions exec,version_count vers, 
  2> plan_hash_value from v$sqlarea where sql_text like '%id=558%' 
  3> and sql_text not like '%sqlarea%'
SQL> /

 
SQL_ID        SQL_TEXT                             EXEC       VERS PLAN_HASH_VALUE
------------- ------------------------------ ---------- ---------- ---------------
2a7uchc9628su select object_name,object_type          1          2      3772518221
               from t where object_id=558

2a7uchc9628su select object_name,object_type          1          2      1601196873
               from t where object_id=558

Why the child cursor is not sharing:

SQL> select child_number,auth_check_mismatch, translation_mismatch 
  2> from v$sql_shared_cursor where sql_id='2a7uchc9628su';

CHILD_NUMBER A T

------------ - -
           0 N N
           1 Y Y

We can confirm that sql plan are different.

SQL> select * from table(dbms_xplan.display_cursor('2a7uchc9628su',0));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  2a7uchc9628su, child number 0
-------------------------------------
select object_name,object_type from t where object_id=558

Plan hash value: 3772518221

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |    29 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_PK |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=558)

SQL> select * from table(dbms_xplan.display_cursor('2a7uchc9628su',1));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID  2a7uchc9628su, child number 1
-------------------------------------
select object_name,object_type from t where object_id=558

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    32 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    29 |    32   (4)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=558)

Reference:

120655.1 VIEW: "V$SQL_SHARED_CURSOR" Reference Note

http://www.dbaglobe.com/2009/08/troubleshooting-oracle-performance.html