關於共用SQL——窺視解析

http://boylook.itpub.net/post/43144/520546

演員表：
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子句中）。

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

這之後，在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也影響遊標的共用，不過一般都是權宜之計。留給筒子們去思考啦~

本文出自 “MIKE老畢的部落格” 部落格，請務必保留此出處http://boylook.blog.51cto.com/7934327/1298622