SQL语句是一种描述语句，只是描述了用户的数据需求。每种DBMS都需要对SQL进行解析和处理，形成真正的执行程序步骤。在Oracle中，这个过程称为parse解析。
Parse解析工作涉及数据字典信息检索、优化器运行和内存空间分配。解析工作分为硬解析（Hard Parse）和软解析（Soft Parse）。简单的说，所谓硬解析，就是没有找到可以现成使用的执行计划，或者没有找到符合条件共享的执行计划，不得不全新解析SQL语句形成执行计划。所谓软解析，就是在share pool的library cache中找到了可以使用的执行计划，之后按照这个执行计划去执行。
Shared Cursor在内存中是采用父子游标的结构来进行组织。一个父游标下面对应多个子游标，每个子游标对应的是一个真实的执行计划。一个子游标不能单独存在，一个父游标的生成也必然伴随一个子游标。
对父游标共享的标准，谈的比较多的是SQL语句字面值的相同，也就是SQL语句相同。那么，其他一些因素，如对象真实指代、Parse用户权限会不会有影响呢？本文就通过一系列的实验来证明。
1、实验环境准备
我们选择Oracle 11gR2进行实验。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
我们首先在一个用户sys下，构建一个实验数据环境，创建数据表T。
SQL> show user
User is "SYS"
SQL> create table t as select * from dba_objects;
Table created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
SQL> select object_id, object_name from user_objects where object_name='T';
OBJECT_ID OBJECT_NAME
---------- ------------------------------
192319 T
切换到另一个数据用户scott，构建一个类似的实验环境。
SQL> conn scott/tiger@ora11gw
已连接。
SQL> create table t as select * from dba_objects;
表已创建。
--索引创建
SQL> create index idx_t_owner on t(owner);
索引已创建。
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL 过程已成功完成。
SQL> col object_name for a20;
SQL> select object_id, object_name from user_objects where object_name='T';
OBJECT_ID OBJECT_NAME
---------- --------------------
192320 T
清理shared pool和buffer cache。
SQL> alter system flush shared_pool;
System altered
SQL> alter system flush buffer_cache;
System altered
下面进行一系列的实验。
2、相同SQL字面值，不同用户执行的不同对象
当SQL字面值相同，但是不同用户执行的使用，我们可能会对同名不同实质对象进行执行计划生成。实验环境中，sys和scott均包括一个数据表T。
首先，我们查看sys用户的情况。
SQL> select /*+ DEMO */count(*) from t where wner='SCOTT';
COUNT(*)
----------
9
此时，shared pool中父子游标状态如下。
SQL> select sql_id, version_count, executions, USERS_EXECUTING, PARSING_SCHEMA_NAME from v$sqlarea where sql_text like 'select /*+ DEMO */%';
SQL_ID VERSION_COUNT EXECUTIONS USERS_EXECUTING PARSING_SCHEMA_NAME
------------- ------------- ---------- --------------- ------------------------------
5x21uhnky7bnb 1 1 0 SYS
SQL> select sql_id, child_number, executions, PARSING_SCHEMA_NAME, plan_hash_value from v$sql where sql_text like 'select /*+ DEMO */%';
SQL_ID CHILD_NUMBER EXECUTIONS PARSING_SCHEMA_NAME PLAN_HASH_VALUE
------------- ------------ ---------- ------------------------------ ---------------
5x21uhnky7bnb 0 1 SYS 2966233522
生成了一对父子游标。对应执行计划如下：
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'5x21uhnky7bnb'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5x21uhnky7bnb, child number 0
-------------------------------------
select /*+ DEMO */count(*) from t where wner='SCOTT'
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 331 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| T | 2119 | 12714 | 331 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SCOTT')
19 rows selected
切换到scott用户下，执行相同的SQL。
SQL> select /*+ DEMO */count(*) from t where wner='SCOTT';
COUNT(*)
----------
9
此时，缓存中信息如下：
SQL> select sql_id, version_count, executions, USERS_EXECUTING, PARSING_SCHEMA_NAME from v$sqlarea where sql_text like 'select /*+ DEMO */%';
SQL_ID VERSION_COUNT EXECUTIONS USERS_EXECUTING PARSING_SCHEMA_NAME
------------- ------------- ---------- --------------- ------------------------------
0cmtuq6zf22px 1 1 0 SCOTT
5x21uhnky7bnb 1 1 0 SYS
SQL> select sql_id, child_number, executions, PARSING_SCHEMA_NAME, plan_hash_value from v$sql where sql_text like 'select /*+ DEMO */%';
SQL_ID CHILD_NUMBER EXECUTIONS PARSING_SCHEMA_NAME PLAN_HASH_VALUE
------------- ------------ ---------- ------------------------------ ---------------
0cmtuq6zf22px 0 1 SCOTT 1232703844
5x21uhnky7bnb 0 1 SYS 2966233522
Scott执行的SQL对应的执行计划，如下所示：
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'0cmtuq6zf22px'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0cmtuq6zf22px, child number 0
-------------------------------------
select /*+ DEMO */count(*) from t where wner='SCOTT'
Plan hash value: 1232703844
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)|
| 1 | SORT AGGREGATE | | 1 | 6 | |
|* 2 | INDEX RANGE SCAN| IDX_T_OWNER | 2119 | 12714 | 5 (0)| 00:00:01
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
19 rows selected
结论：当两个不同的用户，使用相同的SQL语句，对应不同的两个对象时，Oracle不会进行父子游标的共享！
3、相同的对象、相同语句，不同的Schema对象执行
如果我们选择相同的语句和对象，不同用户执行的时候，是什么样子呢？
首先在sys用户下执行。
SQL> select /*+ DEMO-1 */count(*) from sys.t where wner='SCOTT';
COUNT(*)
----------
9
SQL> select sql_id, version_count, executions, USERS_EXECUTING, PARSING_SCHEMA_NAME from v$sqlarea where sql_text like 'select /*+ DEMO-1 */%';
SQL_ID VERSION_COUNT EXECUTIONS USERS_EXECUTING PARSING_SCHEMA_NAME
------------- ------------- ---------- --------------- ------------------------------
25yznycprdd5c 1 1 0 SYS
SQL> select sql_id, child_number, executions, PARSING_SCHEMA_NAME, plan_hash_value from v$sql where sql_text like 'select /*+ DEMO-1 */%';
SQL_ID CHILD_NUMBER EXECUTIONS PARSING_SCHEMA_NAME PLAN_HASH_VALUE
------------- ------------ ---------- ------------------------------ ---------------
25yznycprdd5c 0 1 SYS 2966233522
Scott用户再次执行相同的过程。
SQL> select /*+ DEMO-1 */count(*) from sys.t where wner='SCOTT';
COUNT(*)
----------
9
SQL> select sql_id, version_count, executions, USERS_EXECUTING, PARSING_SCHEMA_NAME from v$sqlarea where sql_text like 'select /*+ DEMO-1 */%';
SQL_ID VERSION_COUNT EXECUTIONS USERS_EXECUTING PARSING_SCHEMA_NAME
------------- ------------- ---------- --------------- ------------------------------
25yznycprdd5c 1 1 0 SYS
011nx4vr6bma0 1 1 0 SCOTT
SQL> select sql_id, child_number, executions, PARSING_SCHEMA_NAME, plan_hash_value from v$sql where sql_text like 'select /*+ DEMO-1 */%';
SQL_ID CHILD_NUMBER EXECUTIONS PARSING_SCHEMA_NAME PLAN_HASH_VALUE
------------- ------------ ---------- ------------------------------ ---------------
25yznycprdd5c 0 1 SYS 2966233522
011nx4vr6bma0 0 1 SCOTT 2966233522
即使是相同的SQL，相同的操作对象，Oracle还是拆成了不同的父游标对象。结论：对Oracle CBO而言，父游标共享的条件不仅仅是SQL文本一致，解析用户parse schema name也是一个重要的方面。
但是，两个子游标的执行计划是完全相同的，plan_hash_value也是相同。
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'25yznycprdd5c'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 25yznycprdd5c, child number 0
-------------------------------------
select /*+ DEMO-1 */count(*) from sys.t where wner='SCOTT'
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 331 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| T | 2119 | 12714 | 331 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SCOTT')
19 rows selected
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'011nx4vr6bma0'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 011nx4vr6bma0, child number 0
-------------------------------------
select /*+ DEMO-1 */count(*) from sys.t where wner='SCOTT'
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 331 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| T | 2119 | 12714 | 331 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SCOTT')
19 rows selected
4、同义词的参与
同义词synonym是可能会影响到SQL形态的重要因素。下面我们实验下如果使用synonym在不同用户下，会有什么影响。
我们在sys用户下准备。
--创建公共同义词
SQL> show user;
User is "SYS"
SQL> create public synonym t for t;
Synonym created
测试SYS下的执行计划。
SQL> select /*+ DEMO-2 */count(*) from sys.t where wner='SCOTT';
COUNT(*)
----------
9
SQL> select sql_id, version_count, executions, USERS_EXECUTING, PARSING_SCHEMA_NAME from v$sqlarea where sql_text like 'select /*+ DEMO-2 */%';
SQL_ID VERSION_COUNT EXECUTIONS USERS_EXECUTING PARSING_SCHEMA_NAME
------------- ------------- ---------- --------------- ------------------------------
6tsw7qwgwzwbj 1 1 0 SYS
SQL> select sql_id, child_number, executions, PARSING_SCHEMA_NAME, plan_hash_value from v$sql where sql_text like 'select /*+ DEMO-2 */%';
SQL_ID CHILD_NUMBER EXECUTIONS PARSING_SCHEMA_NAME PLAN_HASH_VALUE
------------- ------------ ---------- ------------------------------ ---------------
6tsw7qwgwzwbj 0 1 SYS 2966233522
SCOTT发出相同的SQL。
SQL> show user
USER 为 "SCOTT"
SQL> drop table t purge;
表已删除。
SQL> select /*+ DEMO-2 */count(*) from sys.t where wner='SCOTT';
COUNT(*)
----------
9
SQL> select sql_id, version_count, executions, USERS_EXECUTING, PARSING_SCHEMA_NAME from v$sqlarea where sql_text like 'select /*+ DEMO-2 */%';
SQL_ID VERSION_COUNT EXECUTIONS USERS_EXECUTING PARSING_SCHEMA_NAME
------------- ------------- ---------- --------------- ------------------------------
7j1ut1cf9az1s 1 1 0 SCOTT
6tsw7qwgwzwbj 1 1 0 SYS
SQL> select sql_id, child_number, executions, PARSING_SCHEMA_NAME, plan_hash_value from v$sql where sql_text like 'select /*+ DEMO-2 */%';
SQL_ID CHILD_NUMBER EXECUTIONS PARSING_SCHEMA_NAME PLAN_HASH_VALUE
------------- ------------ ---------- ------------------------------ ---------------
7j1ut1cf9az1s 0 1 SCOTT 2966233522
6tsw7qwgwzwbj 0 1 SYS 2966233522
说明，解析用户是谁，决定了父游标是否共享重要因素。
5、结论
游标共享cursor sharing是Oracle重要概念。我们需要掌握各种使用场景来进行研究。