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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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重要概念。我們需要掌握各種使用場景來進行研究。

Oracle Shared Cursor問題的幾個實驗

相關文章