V$SQL_遊標、adaptive cursor sharing、bind peeking、直方圖的概念

這裡討論的遊標主要是共享遊標（shared cursor），跟pl/sql語句中定義的遊標（session cursor）不是一個概念。

 

共享遊標:是使用者提交SQL或PL/SQL程式塊到Oracle的share pool之後，在library cache中生成的一個可執行物件，這個物件我們稱之為遊標（cursor）。是SQL語句在進行硬解析時生成的，其後設資料被在檢視V$sqlarea與v$sql中具體化。

PL/SQL遊標:則是用於存放SQL語句的執行結果,使用者可以通過這個中間緩衝區逐條取出遊標中的記錄並對其處理，直到所有的遊標記錄被逐一處理完畢。需要宣告、開啟、提取、關閉。

 

 

共享遊標包括父遊標和子游標。

父遊標：是在進行硬解析時產生的。將SQL語句的文字進行雜湊得到雜湊值並在library cache尋找相同的雜湊值（SQL語句必須完全一致包括大小寫、空格回車等才能共享），如不存在則生存父遊標且儲存在library cache中，按順序完成後續步驟。如果此時存在父遊標，則進一步判斷是否存在子游標。若存在相同的子游標，則直接呼叫其子游標的執行計劃執行該SQL語句，否則轉到下一步進行邏輯優化。

 

子游標：在發生硬解析時，在產生父遊標的同時，則跟隨父遊標會產生相應的子游標，此時V$SQL.CHILD_NUMBER的值為0。如果存在父遊標，由於不同的執行環境，此時同樣會產生新的子游標，新子游標的CHILD_NUMBER在已有子游標基礎上以1為單位累計。v$sql中的每一行表示了一個child cursor子游標，根據sql_id與父cursor關聯。child cursor有自己的address，即v$sql.child_address。如果你想確定是由那種原因造成的子游標，需要檢視v$sql_shared_cursor。

 

1.父遊標的關鍵資訊是sql文字，子游標的關鍵資訊是執行計劃和執行環境。

2.硬解析通常是由於不可共享的父遊標造成的，如經常變動的SQL語句，或動態SQL或未使用繫結變數等。解決硬解析的辦法則通常是使用繫結變數來解決。

3.與父遊標SQL文字完全一致的情形下，多個相同的SQL語句可以共享一個父遊標。

4.SQL文字、執行環境完全一致的情形下，子游標能夠被共享，否則如果執行環境不一致則生成新的子游標。如果SQL文字相同，但是可能提交SQL語句的使用者不同，或者使用者提交的SQL語句所涉及到的物件為同名詞等，都有可能生成不同的子游標。因為這些SQL語句的文字雖然完全一樣，但是上下文環境卻不一樣，因此這樣的SQL語句不是一個可執行的物件，必須細化為多個子遊標後才能夠執行。

5.遊標是可以被所有程式共享的，也就是說如果100個程式都執行相同的SQL語句，那麼這100個程式都可以同時使用該SQL語句所產生的遊標，從而節省了記憶體。

 

 

兩條一模一樣的語句但是在不同的schema下執行的兩種結果

如sye、system都執行select * from t1.test則V$SQL只有一條記錄，誰先執行則PARSING_SCHEMA_NAME顯示誰。

如sys和system都執行select * from test則V$SQL中有兩條記錄，兩條記錄的CHILD_NUMBER和PARSING_SCHEMA_NAME不一樣

SQL規範肯定是要繫結變數的，資料傾斜也並非什麼常態，實際情況下adaptive cursor sharing還是用的比較少的，遇到沒有繫結變數的情況下就把引數cursor_sharing改成force吧

BIND PEEKING

Bind Peeking 就是當在WHERE條件中使用繫結變數的時候，CBO會根據第一次使用的真實變數值來生成一個執行計劃。在這個cursor的整個生命週期中，CBO不會再對相同的SQL進行hard parse。這種辦法的優點是：如果索引欄位的值是均勻分佈的，hard parse就降低了，效能提高。但是缺點也很明顯：如果欄位分佈不均勻，並且第一次使用值不具有普遍性，那麼執行計劃就將非常糟糕。即欄位資料分佈傾斜嚴重時，使用繫結變數進行查詢時，bind peeking可能導致產生不正確的執行計劃.

解決這個問題就是Oracle11g 提供的一個新特性Adpative Cursor Sharing

 

 

CURSOR_SHARING determines what kind of SQL statements can share the same cursors

Values:

■ FORCE

Allows the creation of a new cursor if sharing an existing cursor, or if the cursor plan is not optimal.
使用了繫結變數可以使用同一個執行計劃遊標，並執行建立一個新的執行計劃遊標（即adaptive cursor sharing可以和CURSOR_SHARING共存）

■ EXACT

Only allows statements with identical text to share the same cursor.
一模一樣的SQL文字才能共用一個執行計劃遊標

cursor_sharing=force

意味著Oracle會對SQL謂詞值進行強制的繫結變數替換，這樣謂詞不一樣的SQL會認為是一模一樣的SQL。使用第一次的bind peeking值生成執行計劃，之後全部使用這個執行計劃，當出現資料傾斜時還會自動建立新的執行計劃。這種方式實現了遊標共享，避免出現大量的library cache硬解析。

如果這種SQL語句本身是“Good SQL”，也就是條件列分佈比較平均，沒有出現過大的偏移分佈。我們認為這種FORCE是很有益的。但是如果資料列分佈不平均，這樣借用第一次輸入的bind peeking生成並且共享執行計劃就很成問題，如果說在cursor_sharing取定FORCE遇到的潛在問題，和我們使用繫結變數時候使用的bind peeking值問題是相同的，但是11G之後這個問題都不存在了，11G的時候就算CURSOR_SHARING=force時也會使用adaptive cursor sharing功能。

adaptive cursor sharing自適應遊標共享

預設啟動的（_optimizer_adaptive_cursor_sharing引數預設為true）。

其允許一個使用繫結變數的SQL語句使用多個執行計劃。即具有繫結變數的sql語句可能會生成多個遊標。如果沒有adaptive cursor sharing則資料存在資料傾斜的情況下使用繫結變數倒可能是最差的方式。
Interacting with adaptive cursor sharing
cursor_sharing:
-if cursor_sharing<>EXACT,statements containing literals may be rewritten using bind variables
-if statements are rewritten,adaptive cursor sharing may apply to them

sql_plan_management(SPM):
- if optimizer_capture_sql_plan_baselines is set to true,only the first generated plan is used
-AS a workload,set this parameter to false,and run your application until all plans are loaded in the cursor cache
-Manually load the cursor cache into the corresponding plan baseline

v$sql.IS_BIND_SENSITIVE

Indicates whether the cursor is bind sensitive (Y) or not (N). A query is considered bind-sensitive if the optimizer peeked at one of its bind variable values when computing predicate selectivities and where a change in a bind variable value may cause the optimizer to generate a different plan.

v$sql.IS_BIND_AWARE

Indicates whether the cursor is bind aware (Y) or not (N). A query is considered bind-aware if it has been marked to use extended cursor sharing. The query would already have been marked as bind-sensitive.

v$sql.IS_SHAREABLE

Indicates whether the cursor can be shared (Y) or not (N)

但是實踐的情況來看：adaptive cursor sharing這個功能貌似不起什麼作用，cursor_sharing= EXACT情況下，就算資料傾斜情況下執行計劃也不一定會根據不同值生成不同執行計劃，而是根據dba_indexes.CLUSTERING_FACTOR來判斷走索引還是全表。更不要說所有語句共享一個執行計劃的引數cursor_sharing=force的情況了。

 

 

 

直方圖

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TESTTB', CASCADE => TRUE, ESTIMATE_PERCENT => 100, METHOD_OPT => 'FOR ALL INDEXED COLUMNS size 100');

METHOD_OPT - The value controls column statistics collection and histogram creation. It accepts either of the following options, or both in combination:

■ FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

■ FOR COLUMNS [size clause] column [size_clause][,column [size_clause]...]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

column is defined as column := column_name | extension name | extension

- integer : Number of histogram buckets. Must be in the range [1,254].

- REPEAT : Collects histograms only on the columns that already have histograms.

- AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.

- SKEWONLY : Oracle determines the columns on which to collect histograms based on the data distribution of the

columns.

- column_name : name of a column

- extension : can be either a column group in the format of (column_name, colume_name [, ...]) or an expression

The default is FOR ALL COLUMNS SIZE AUTO

該預設值是要給所有的資料列進行統計資訊收集，並且依據auto的原則生成直方圖Histogram。

如果給所有列都收集直方圖，統計量收集過程將會很長，需要消耗很多的效能和空間。

auto選項如何決定收集哪些列的直方圖呢，就是參考如下兩條件，即該列使用過且傾斜

1 列裡的值必須是傾斜的

2 該列做為謂語條件被使用過

直方圖有關的兩個SQL

select table_name,column_name,num_distinct,num_buckets,histogram from user_tab_col_statistics
select index_name,table_name,leaf_blocks,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,CLUSTERING_FACTOR,NUM_ROWS,AVG_DATA_BLOCKS_PER_KEY from user_ind_statistics

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30126024/viewspace-2119091/，如需轉載，請註明出處，否則將追究法律責任。