關於共享SQL——窺視解析
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也影響遊標的共享，不過一般都是權宜之計。留給筒子們去思考啦~
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也影響遊標的共享，不過一般都是權宜之計。留給筒子們去思考啦~
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11676357/viewspace-1052300/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於sql語句的遊標共享問題SQL
- 13_共享SQL減少硬解析SQL
- SpringMVC原始碼關於檢視解析渲染SpringMVC原始碼
- 關於xml解析XML
- Sql Server關於indexed view索引檢視的總結SQLServerIndexView索引
- v$sql_plan 檢視解析SQL
- 關於地址反向解析
- 關於資料共享的問題
- 關於共享資源保護的思考
- 關於 二維碼 解析
- 關於TS流的解析
- 檢視v$sql_shared_cursor檢視獲取sql語句為什麼不能共享?SQL
- v$sql_plan這個檢視解析SQL
- 1.3.2. 關於SQL*PlusSQL
- 關於迅雷的碎片化解析
- oracle繫結變數窺視(zt)Oracle變數
- 1224關於共享池4SQL記憶體結構父子游標補充SQL記憶體
- 共享SQL語句SQL
- 【SQL 調優】繫結變數窺測SQL變數
- 共享池之八：軟解析、硬解析、軟軟解析 詳解一條SQL在library cache中解析涉及的鎖SQL
- 關於開發檢視
- 【SQL】Oracle SQL共享池檢查SQLOracle
- 關於DBMS_SQL的使用SQL
- sql server 檢視tempdb使用的相關檢視SQLServer
- 1229關於共享池4-SQL記憶體結構父子游標補充3SQL記憶體
- 20150213關於共享池4-SQL記憶體結構父子游標SQL記憶體
- 關於JS中事件代理的解析JS事件
- 關於Gson解析的使用匯總
- 關於SQLServer2005的學習筆記——SQL查詢解析步驟SQLServer筆記
- 繫結變數窺視測試案例變數
- 基於 SQL 解析的 JPA 多租戶方案SQL
- 深入解析：從原始碼窺探MySQL優化器原始碼MySql優化
- sql統計－關於學生成績SQL
- 關於SQL優化的闢謠SQL優化
- 關於SQL Server的WITH(NOLOCK)和(NOLOCK)SQLServer
- 關於sql語句的優化SQL優化
- 關於 TX 鎖的兩句sqlSQL
- 關於SQL Server的分割槽表SQLServer