parse[@more@]演員表：
A——————boylook
B——————hr
C——————david

SQL> show user
USER is "BOYLOOK"
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
USERENV VIEW
TEST_REFED TABLE
TO_TABLE TABLE
FROM_TABLE TABLE
TEST TABLE

SQL> create table t(x int);

Table created.

SQL> select * from t;

no rows selected

SQL> select * from T;

no rows selected

SQL> create public synonym t for t;

Synonym created.

SQL> grant select on t to david;

Grant succeeded.

SQL> conn hr
Enter password:
Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
RLS_TEST TABLE

9 rows selected.

SQL> create table t(x int);

Table created.

SQL> select * from t;

no rows selected

SQL> select * from t;

no rows selected

SQL> conn david
Enter password:
Connected.

SQL> select * from t;

no rows selected

SQL> conn boylook
Enter password:
Connected.

讓我們看看現在shared_pool裡有幾條sql呢：
SQL> select address,executions,sql_text from v$sql
2 where upper(sql_text) like 'SELECT * FROM%';

ADDRESS EXECUTIONS
-------- ----------
SQL_TEXT
--------------------------------------------------------------------------------
309D0E74 2←哪兩條共享了這句呢？
select * from t

309D0E74 1
select * from t

308E306C 1
select * from t

ADDRESS EXECUTIONS
-------- ----------
SQL_TEXT
--------------------------------------------------------------------------------
309D0B00 1
select * from T

SQL> show user
USER is "BOYLOOK"
SQL> show parameter optimizer_mode

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
SQL> alter system set optimizer_mode = choose;

System altered.

SQL> select * from t;

no rows selected

SQL> select address,executions,sql_text from v$sql
2 where upper(sql_text) like 'SELECT * FROM%';

ADDRESS EXECUTIONS
-------- ----------
SQL_TEXT
--------------------------------------------------------------------------------
309D0E74 2
select * from t

309D0E74 1
select * from t

309D0E74 1
select * from t

ADDRESS EXECUTIONS
-------- ----------
SQL_TEXT
--------------------------------------------------------------------------------
308E306C 1
select * from t

309D0B00 1
select * from T

為了看清楚點，多執行了兩次：

SQL> select * from t;

no rows selected

SQL> select * from t;

no rows selected

OK揭示最終的結果：

SQL> select address,executions,sql_text from v$sql
2 where upper(sql_text) like 'SELECT * FROM%';

ADDRESS EXECUTIONS
-------- ----------
SQL_TEXT
--------------------------------------------------------------------------------
309D0E74 2
select * from t

309D0E74 1
select * from t

309D0E74 3
select * from t

ADDRESS EXECUTIONS
-------- ----------
SQL_TEXT
--------------------------------------------------------------------------------
308E306C 1
select * from t

309D0B00 1
select * from T
——————————————————————非常華麗的分割線——————————————————————————

一句sql從開啟遊標到最終返回結果或者事務結束需要經歷一段漫長的旅途。關於共享SQL，主要發生在解析（parse）階段。那麼在解析的時候是什麼樣的呢？

首先，檢查VPD約束條件（是否有安全策略生成的約束條件，有的話要加到where子句中。eg：http://bbs.we168.cn/bbs/forum.php?mod=viewthread&tid=351&extra=page%3D3）。

然後檢查語法，語義以及許可權：語義指的是引用的物件是否都存在，並且是否使用者有許可權按照現在的方式訪問它們。

這之後，在library cache裡面尋找是否存在共享的父遊標（將sql雜湊之後去共享池找相同雜湊值的sql文字，然後進行比較，以確保sql_text完全一致），如果不存在，就在library cache裡分配記憶體併產生新的父遊標。

檢查是否存在共享子游標（warning：要獲取library cache latch）：
檢查語義：如果父遊標可共享，第一步檢查語義：比如上面的A和B都發出了select * from t；但是其實A訪問的是A.t，而B訪問的是B.t。這樣子游標是無法共享的。第二步檢查環境：比如最佳化器的模式，或者pga等。像上面的optimizer_mode從all_rows——>choose，因此雖然A發出了兩條select * from t；卻無法共享子游標。只有語義和環境等都統一（具體條件可以查詢v$sql_shared_cursor），才能共享子游標，直接用執行計劃。
如果無法共享子游標，第一步進行邏輯最佳化（透過各種等價轉換，會生成一些列語義上相同的sql）；第二步物理最佳化（對於這一系列的sql生成執行計劃，並且根據資料字典的統計資訊或者dynamic sampling的資訊進行成本cost的計劃，選出cost最小的執行計劃）。理想上是把一個sql徹底的進行最佳化，把全部的所有的等價的sql都列出來進行成本計算，可是這樣並不現實（執行一句多表的select。。。那是多少排列組合），所以在8i,9i有一個引數來控制生成最大的等價sql集合的大小就是——optimizer_max_permutations。在10g裡面是隱藏引數。

這樣，就生成了一個子遊標，再分配記憶體（warning：要獲取share pool latch），然後插入（釋放share pool latch並獲取library cache latch）。
然後執行遊標，繼續接下來的漫長旅行。。。。。。
當父遊標和子游標都可以共享的時候，我們稱發生了一個軟解析(soft parse)；否則是硬解析(hard parse)。從效能上來看，硬解析是可怕的。。其實最好是不解析。。這是後話。並且，還有個引數cursor_sharing也影響遊標的共享，不過一般都是權宜之計。留給筒子們去思考啦~

關於共享SQL——窺視解析

相關文章