關于共享SQL——窺視解析

演員表：

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;

SQL> create public synonym t for t;

Synonym created.

SQL> grant select on t to david;

Grant succeeded.

SQL> conn hr

Enter password: 

Connected.

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> conn david

SQL> conn boylook

讓我們看看現在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

308E306C 1

309D0B00 1

select * from T

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;

309D0E74 2

309D0E74 1

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

OK揭示最終的結果：

309D0E74 3

——————————————————————非常華麗的分割線——————————————————————————

一句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老畢 51CTO部落格，原文連結：http://blog.51cto.com/boylook/1298622，如需轉載請自行聯系原作者