Oracle 11.1 自適應遊標
發生自適應條件：
（1）表資料傾斜，可能由於繫結變數不同值導致不同執行計劃。
（2）表有統計資訊，沒有統計資訊不會發生自適應。
（3）繫結變數窺視（_optim_peek_user_binds），即在SQL第一次硬解析時，繫結變數窺視，
將V$SQL中IS_BIND_SENSITIVE標記為Y，下次執行時，如果生成了不同的執行計劃，會將V$SQL中IS_BIND_AWARE標記為Y同時會將之前執行計劃IS_BIND_SHAREABLE標記為N，會在shared pool老化時第一批老化cursor，會根據繫結變數不同傳入值選擇不同的執行計劃，
或者生成不同的執行計劃，造成SQL多Version Count。
（4）列上有直方圖時。
下面為官方原文對自適應遊標概念說明：
A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable.
The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.
The criteria used by the optimizer to decide whether a cursor is bind-sensitive include the following:
（1）The optimizer has peeked at the bind values to generate selectivity estimates.
（2）A histogram exists on the column containing the bind value.
BIND-AWARE
A bind-aware cursor is a bind-sensitive cursor eligible to use different plans for different bind values.
After a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its selectivity estimate.
（1）When a statement with a bind-sensitive cursor executes, the database decides whether to mark the cursor bind-aware.
（2）The decision depends on whether the cursor produces significantly different data access patterns for different bind values.
If the database marks the cursor bind-aware, then the next time that the cursor executes the database does the following:
（1）Generates a new plan based on the new bind value.
（2）Marks the original cursor generated for the statement as not shareable (V$SQL.IS_SHAREABLE is N).
This cursor is no longer usable and will be among the first to be aged out of the shared SQL area.
Adaptive Cursor Sharing (Bind Sensitivity)
The first step towards ACS is recognising a query as "Bind Sensitive".
This means that the best plan for a query has potential to be different according to the bind variables supplied to it.
In effect this just means the cursor is being monitored to see if it should be made bind aware.
A cursor is marked as Bind Sensitive if:
（1）query is executed with bind peeking
（2）binds using any of the following relational operators = < > <= >= != or a user defined bind operator e.g. contains(e.job,:job,1)>0,
From 11.2.0.2 the "LIKE" operator is also supported.
（3）A histogram exists on the column containing the bind value.
（4）In other words, a cursor is marked Bind Sensitive if a change in the bind variable value may lead to a different plan.
Adaptive Cursor Sharing will be disabled Situation
Apart from checking for a valid operator there are also a number of subsequent bind sensitivity checks that need to be performed before it can be marked as bind sensitive.
If any of these checks fail, the cursor will not be marked as bind sensitive and adaptive cursor sharing will not occur and Adaptive Cursor Sharing will be disabled :-
（1）Extended cursor sharing has been disabled
（2）The query has no binds
（3）Parallel query is used
（4）Certain parameters like ("_optim_peek_user_binds"=false) are set
（5）You are using a /*+ NO_BIND_AWARE */ hint
（6）Outlines are being used
（7）Query is recursive
（8）The number of binds in a given sql statement is greater than 14. ** Could be less depending on version and setting of fix_control for Bug 10182051. See Document : 1983132.1
Monitoring View
V$SQL can be used to see whether a cursor is_bind_sensitive, is_bind_aware, or is_shareable.
Bind context information can be viewed via V$SQL_CS_SELECTIVITY, V$SQL_CS_STATISTICS and V$SQL_CS_HISTOGRAM
V$SQL_CS_SELECTIVITY displays the valid selectivity ranges for a child cursor in extended cursor sharing mode.
A valid range consists of a low and high value for each predicate containing binds.
Each predicate's selectivity (with the current bind value) must fall between the corresponding low and high values in order for the child cursor to be shared.
V$SQL_CS_STATISTICS contains the raw execution statistics used by the monitoring component of adaptive cursor sharing.
A sample of the executions is monitored. This view shows which executions were sampled, and what the statistics were for those executions.
The statistics are cumulative for each distinct set of bind values.
V$SQL_CS_HISTOGRAM summarizes the monitoring information stored by adaptive cursor sharing.
This information is used to decide whether to enable extended cursor sharing for a query.
It is stored in a histogram, whose bucket's contents are exposed by this view.
測試
相關引數：
_optimizer_adaptive_cursor_sharing
_optimizer_extended_cursor_sharing
_optim_peek_user_binds
NAME VALUE ISDEFAULT DESCRIBE ISMOD ISADJ ---------------------------------------- --------------- --------- -------------------------------------------------- ---------- ----- _optim_peek_user_binds TRUE TRUE enable peeking of user binds FALSE FALSE _optimizer_extended_cursor_sharing UDO TRUE optimizer extended cursor sharing FALSE FALSE _optimizer_adaptive_cursor_sharing TRUE TRUE optimizer adaptive cursor sharing FALSE FALSE
測試開始：
（1）建立表
LIBAI@honor1 > create table test_bind(id number,name varchar2(10)); Table created.
（2）插入傾斜資料
declare i number; begin for i in 1..100000 loop insert into test_bind values(i,'haha'); end loop; commit; end; / declare i number; begin for i in 100001..100010 loop insert into test_bind values(i,'test'); end loop; commit; end; /
（3）建立索引
LIBAI@honor1 > select count(*) from test_bind; COUNT(*) ---------------------------------------- 100010 LIBAI@honor1 > create index idx_test_bind on test_bind(name); Index created.
（4）未收集統計資訊時檢視執行計劃選擇
LIBAI@honor1 > set autotrace on
LIBAI@honor1 > var v_name varchar2(20);
LIBAI@honor1 > exec :v_name:='haha';
LIBAI@honor1 > select * from test_bind where name=:v_name;
Execution Plan
----------------------------------------------------------
Plan hash value: 2889536435
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 982 | 19640 | 67 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_BIND | 982 | 19640 | 67 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_BIND | 393 | | 112 (0)| 00:00:02 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME"=:V_NAME)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
6957 consistent gets
0 physical reads
0 redo size
2209404 bytes sent via SQL*Net to client
73845 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed
（5）發現並沒有因為查詢資料有傾斜發生自適應，
因為此時Oracle並不知道資料有傾斜，CBO會根據實際表資料評估執行計劃，選擇最優執行計劃
LIBAI@honor1 > SELECT sql_id, sql_text, IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE, child_number FROM v$sql WHERE sql_text LIKE '%select * from test_bind where name=:v_name%'; SQL_ID SQL_TEXT I I I CHILD_NUMBER ------------- ------------------------------------------------------------ - - - ---------------------------------------- 868x3yt87vqhy select * from test_bind where name=:v_name N N Y 0 0
（6）收集統計
LIBAI@honor1 > exec dbms_stats.gather_table_stats('LIBAI','TEST_BIND');
（7）flush shared pool
SYS@honor1 > alter system flush shared_pool;
（8）第一次執行，硬解析，可以看到由於統計資訊作用，Oracle知道選擇列上有資料傾斜，將IS_BIND_SENSITIVE標記為
Y，發生了自適應，以便根據將來繫結變數傳入值，選擇合適或者解析新的執行計劃。
LIBAI@honor1 > exec :v_name:='haha'; LIBAI@honor1 > select * from test_bind where name=:v_name; LIBAI@honor1 > SELECT sql_id, sql_text, IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE, child_number FROM v$sql WHERE sql_text LIKE '%select * from test_bind where name=:v_name%'; SQL_ID SQL_TEXT I I I CHILD_NUMBER ------------- ------------------------------------------------------------ - - - ---------------------------------------- 868x3yt87vqhy select * from test_bind where name=:v_name Y N Y 0
（9）當傳入新值時，由於IS_BIND_SENSITIVE作用，Oracle選擇了新的執行計劃，並標記IS_BIND_AWARE為Y
LIBAI@honor1 > exec :v_name:='test'; LIBAI@honor1 > select * from test_bind where name=:v_name; LIBAI@honor1 > SELECT sql_id, sql_text, IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE, child_number FROM v$sql WHERE sql_text LIKE '%select * from test_bind where name=:v_name%'; SQL_ID SQL_TEXT I I I CHILD_NUMBER ------------- ------------------------------------------------------------ - - - ---------------------------------------- 868x3yt87vqhy select * from test_bind where name=:v_name Y Y Y 0
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31439444/viewspace-2673098/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle遊標Oracle
- 自適應案例
- 程式碼生成器，自適應mysql、oracle資料庫MySqlOracle資料庫
- textarea 高度自適應
- 圖片自適應
- JFrame自適應大小
- 自適應辛普森法
- vue前端自適應佈局，一步到位所有自適應Vue前端
- css自適應佈局CSS
- web自適應尺寸方法Web
- 自適應介面設計
- Android 螢幕自適應Android
- html iframe高度自適應HTML
- LMS自適應濾波
- Oracle顯示遊標的使用及遊標for迴圈Oracle
- 青蛙吃蚊子小遊戲原始碼_自適應手機端遊戲原始碼
- textarea文域高度自適應
- textarea高度自適應詳解
- 元素自適應水平垂直居中
- 小程式Swiper高度自適應
- jQuery textarea框高度自適應jQuery
- Android 8.0 自適應圖示Android
- c# 窗體自適應C#
- 思路：element 表格元件自適應元件
- gridview自動適應列寬View
- iframe 跨域高度自適應跨域
- 自適應CFAR檢測器
- 前端:WebP自適應實踐前端Web
- Oracle - 共享遊標、父子游標、硬軟解析Oracle
- Oracle開發基礎-遊標Oracle
- 自動載入的iframe高度自適應
- 響應式圖片(自適應圖片)
- QTableWidget行高列寬自適應QT
- input文字框寬度自適應
- CSS佈局 --- 自適應佈局CSS
- web 報表工具如何自適應Web
- Widget小元件如何自適應高度元件
- WPF控制元件介面自適應控制元件