Shulou(Shulou.com)06/01 Report--

Adaptive conditions occur:

(1) the table data is skewed, which may lead to different execution plans due to different values of bound variables.

(2) the table has statistical information, and adaptation will not occur without statistical information.

(3) bind variable peep (_ optim_peek_user_binds), that is, during the first hard parsing of SQL, bind variable peep

Mark IS_BIND_SENSITIVE as Y in V$SQL. If a different execution plan is generated the next time it is executed, the IS_BIND_AWARE in V$SQL will be marked Y and the previous execution plan IS_BIND_SHAREABLE will be marked N, the first batch of cursor will be aged when the shared pool is aging, and different execution plans will be selected according to the different incoming values of binding variables.

Or generate different execution plans, resulting in SQL multi-Version Count.

(4) when there is a histogram on the column.

The following is the official original description of the concept of adaptive cursors:

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.jobjobjobjob1) > 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.

test

Related parameters:

_ 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

The test begins:

(1) create a table

LIBAI@honor1 > create table test_bind (id number,name varchar2 (10)); Table created.

(2) insert tilt data

Declarei number;beginfor I in 1..100000loopinsert into test_bind values (end loop;commit;end;/declarei number;beginfor I in 100001..100010loopinsert into test_bind values); end loop;commit;end;/

(3) create an index

LIBAI@honor1 > select count (*) from test_bind; COUNT (*)-100010 LIBAI@honor1 > create index idx_test_bind on test_bind (name); Index created.

(4) View execution plan selection when no statistics are collected

LIBAI@honor1 > set autotrace onLIBAI@honor1 > var v_name varchar2 (20); LIBAI@honor1 > exec Execution Plan---Plan hash value: 2889536435 Murray- -| 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 | 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) it is found that there is no self-adaptation due to the skew of the query data.

Because Oracle does not know that the data is skewed at this time, CBO will evaluate the execution plan according to the actual table data and choose the best execution plan.

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) collecting statistics

LIBAI@honor1 > exec dbms_stats.gather_table_stats ('LIBAI','TEST_BIND')

(7) flush shared pool

SYS@honor1 > alter system flush shared_pool

(8) when performing hard parsing for the first time, you can see that due to the action of statistical information, Oracle knows that there is a data skew on the selection column and marks IS_BIND_SENSITIVE as

Y, adaptation has occurred to select the appropriate or parse new execution plan based on the values passed in by the future binding variables.

LIBAI@honor1 > exec: LIBAIhonor1 > 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) when a new value is passed in, Oracle selects a new execution plan and marks IS_BIND_AWARE as Y due to the effect of IS_BIND_SENSITIVE

LIBAI@honor1 > exec: LIBAIhonor1 > 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.