Tuesday, September 15, 2009

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