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

How to conduct Adaptive Cursor Sharing research, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Adaptive Cursor Sharing (ACS) is another bold and attractive new feature of 11G.

It's bold because it tries to solve one of CBO's biggest headaches: data skewing (data skew) and peeking of bound variables lead to poor SQL PLAN. It's attractive because you want to know what mysterious algorithms Oracle uses to make Oracle smarter.

The reason why I started to study after 11GR2 came out is because this new feture has all kinds of problems in 11GR1, and the first thing that caught my eye was this bug:

Bug 7213010 Adaptive cursor sharing generates lots of child cursors

This issue is fixed in 11.2 (Future Release), 11.1.0.7 (Server Patch Set)

In fact, ACS is to generate more and better execution plans for the same SQL according to the values of different bound variables to adapt to the different situations of data skew. It is precisely because of this that there will be bug as above.

Let's approach ACS with a simple example.

(note: the acquisition of SQL PLAN in the following experiment cannot trust set autotrace. It will not show the actual execution plan of each child cursor. We can get the real PLAN through SELECT * FROM table (: sqlid, NULL, 'TYPICAL LAST'). )

Create a new two-field table, where the column id is very skewed, create an index on the column id, and use the SKEWONLY option to analyze the table to generate histogram.

In order not to let other factors interfere with my experiment and to allow the reader to reproduce, I set the following parameters:

Optimizer_mode=CHOOSE (using CBO)

Optimizer_features_enable=11.2.0.1 (using the latest optimization parameters)

Optimizer_capture_sql_plan_baselines=false (close SPM)

Cursor_sharing=EXACT (using real binding variables)

_ optim_peek_user_binds=true (be sure to turn on binding variable snooping)

_ optimizer_adaptive_cursor_sharing=TRUE (ACS is enabled by default in the following three parameters)

_ optimizer_extended_cursor_sharing=UDO

_ optimizer_extended_cursor_sharing_rel=SIMPLE

SQL > desc testbyhao

Name Type

--

ID NUMBER

NAME VARCHAR2 (128)

SQL > select id,count (*) from testbyhao

Group by id

ID COUNT (*)

--

1 104096

2 498

SQL > create index testidx on testbyhao (id)

Index created.

SQL > exec dbms_stats.gather_table_stats (user,'TESTBYHAO'

Method_opt= > 'for all columns size skewonly')

PL/SQL procedure successfully completed.

SQL > select COLUMN_NAME,HISTOGRAM from user_tab_columns

Where TABLE_NAME='TESTBYHAO'

COLUMN_NAM HISTOGRAM

ID FREQUENCY

NAME HEIGHT BALANCED

Mr. index range scan becomes one of the simplest implementation plans.

For id=2, it is quite appropriate.

SQL > var v number

SQL > exec: v: = 2

SQL > select / * comments*/ * from TESTBYHAO

2 where id =: v

Plan hash value: 254123311

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | 3 (100) | |

| | 1 | TABLE ACCESS BY INDEX ROWID | TESTBYHAO | 387 | 8127 | 3 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | TESTIDX | 387 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

2-access ("ID" =: v)

From v$SQL, you can see the data of this cursor, where IS_BIND_SENSITIVE=Y indicates that you use binding variable snooping to generate this execution plan, and this execution plan depends on this binding variable. If Oracle finds that other binding variables are present, it is possible to generate other execution plans.

IS_BIND_AWARE=N, indicating that Oracle does not yet use extended cursor sharing.

IS_SHAREABLE=Y, indicating that the cursor can be used again, that is, it can be shared; conversely, setting N means that the cursor is out of date and will not be used again, and the cursor will be waiting to be age out out of the shared pool.

SQL > select CHILD_NUMBER,PLAN_HASH_VALUE,EXECUTIONS

BUFFER_GETS/EXECUTIONS BG_PER_EX

IS_BIND_SENSITIVE BS,IS_BIND_AWARE BA,IS_SHAREABLE S

From v$sql where hash_value=1659091011

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 254123311 1 221 Y N Y

Change the binding variable and use id=1 to execute the same SQL.

SQL > exec: v: = 1

SQL > select / * comments*/ * from TESTBYHAO

2 where id =: v

As a result, the SQL that uses the binding variable id=1 uses the same cursor of index range scan. This is actually not what we want, because the cost is obviously lower when id=1 is scanned in full table.

V$SQL hasn't changed much, but the same cursor has been executed for 2 times.

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 254123311 2 7321.5 Y N Y

Execute the same SQL for id=1 again.

SQL > exec: v: = 1

SQL > select / * comments*/ * from TESTBYHAO

2 where id =: v

Plan hash value: 325910803

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | 109th (100) | | |

| | * 1 | TABLE ACCESS FULL | TESTBYHAO | 104k | 2136k | 109 (4) | 00:00:02 |

Predicate Information (identified by operation id):

1-filter ("ID" =: v)

Finally, what we expected happened, and a new full table scan execution plan was created! (corresponding to CHILD_NUMBER=1,PLAN_HASH_VALUE=325910803)

In v$SQL, the new IS_BIND_AWARE=Y of cursor.

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 254123311 2 7321.5 Y N Y

1 325910803 1 7296 Y Y Y

Execute the SQL of id=1 again

SQL > exec: v: = 1

SQL > select / * comments*/ * from TESTBYHAO

2 where id =: v

The number of CHILD 1 execution increased to 2

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 254123311 2 7321.5 Y N Y

1 325910803 2 7296 Y Y Y

Execute the SQL of id=2 again

SQL > exec: v: = 2

SQL > select / * comments*/ * from TESTBYHAO

2 where id =: v

A strange thing happened, a new cursor for index range scan (CHILD 2) was generated, and the IS_SHAREABLE=N for CHILD 0 was added, indicating that the cursor was no longer used.

I think this is because Oracle monitors the average selectivity of each cursor, and when the cursor of newly bound variables is far from the existing cursor, a new cursor is generated, even if their execution plan is likely to be the same.

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 254123311 2 7321.5 Y N N

1 325910803 2 7296 Y Y Y

2 254123311 1 73 Y Y Y

Execute the SQL of id=2 again

SQL > exec: v: = 2

SQL > select / * comments*/ * from TESTBYHAO

2 where id =: v

The number of CHILD 2 execution increased to 2

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 254123311 2 7321.5 Y N N

1 325910803 2 7296 Y Y Y

2 254123311 2 73 Y Y Y

Change the binding variable id=999 and execute it again.

SQL > exec: v: = 999

SQL > select / * comments*/ * from TESTBYHAO

2 where id =: v

Sure enough, the new cursor CHILD 3 was born, although he still uses index range scan, but its selectivity is 0.

At this time, CHILD 2 "died" again. (IS_SHAREABLE=N)

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 254123311 2 7321.5 Y N N

1 325910803 2 7296 Y Y Y

2 254123311 2 73 Y Y N

3 254123311 12 Y Y Y

Try again with id=2.

SQL > exec: v: = 2

SQL > select / * comments*/ * from TESTBYHAO

2 where id =: v

The new CHILD 3 cursor is used.

SQL > /

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 254123311 2 7321.5 Y N N

1 325910803 2 7296 Y Y Y

2 254123311 2 73 Y Y N

3 254123311 2 37.5 Y Y Y

Try another binding variable id=111.

SQL > exec: v: = 111

SQL > select / * comments*/ * from TESTBYHAO

2 where id =: v

CHILD 3 is still used, and it seems that the execution plan is now basically in a stable state.

SQL > /

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 254123311 2 7321.5 Y N N

1 325910803 2 7296 Y Y Y

2 254123311 2 73 Y Y N

3 254123311 3 25.6666667 Y Y Y

Try using id=1 again.

SQL > exec: v: = 1

SQL > select / * comments*/ * from TESTBYHAO

2 where id =: v

Sure enough, CHILD 1 was used.

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 254123311 2 7321.5 Y N N

1 325910803 3 7296 Y Y Y

2 254123311 2 73 Y Y N

3 254123311 3 25.6666667 Y Y Y

Next, let's take a look at the three views of ACS.

1.v$sql_cs_histogram

SQL > SELECT CHILD_NUMBER,BUCKET_ID,COUNT FROM v$sql_cs_histogram

2 WHERE HASH_VALUE = '1659091011' order by 1jue 2jue 3

CHILD_NUMBER BUCKET_ID COUNT

0 0 1

0 1 1

0 2 0

1 0 0

1 1 3

1 2 0

2 0 2

2 1 0

2 2 0

3 0 3

3 1 0

3 2 0

This view has three buckets for each Child Cursor, which is used to calculate the number of times each cursor is executed.

2.v$sql_cs_selectivity

SQL > l

1 SELECT CHILD_NUMBER,PREDICATE,RANGE_ID,LOW,HIGH FROM

2 * v$sql_cs_selectivity WHERE HASH_VALUE = '1659091011'

SQL > /

CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH

-

3 = V 0 0.001705 0.004070

2 = V 0 0.003330 0.004070

1 = V 0 0.896589 1.095831

This view shows what the highest and lowest selectivity are for each Child Cursor.

This is because Oracle will not and can not generate a Child Cursor for each binding variable, then different binding variables will have to look in the existing Child Cursor according to their own selectivity, whether there is a similar selection rate, if so, then reuse the cursor; otherwise, as in my previous experiment, a new Child Cursor will be born.

3.v$sql_cs_statistics

SQL > SELECT CHILD_NUMBER,BIND_SET_HASH_VALUE,PEEKED,EXECUTIONS

2 ROWS_PROCESSED,BUFFER_GETS,CPU_TIME

3 FROM v$sql_cs_statistics WHERE HASH_VALUE = '1659091011'

CHILD_NUMBER BIND_SET_HASH_VALUE PEE EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME

-

3 3028748107 Y 10 2 0

2 2064090006 Y 1 996 73 0

1 2342552567 Y 1 104096 7296 0

0 2064090006 Y 1 996 221 0

This view, which is called thought, shows the statistics of each Child Cursor, such as whether binding variables are used to peek, how many rows are returned, how many logical IO, and so on.

If you need to see what binding variables generate these cursor, you can query v$sql_bind_capture using the following SQL:

SQL > SELECT CHILD_NUMBER,VALUE_STRING,LAST_CAPTURED

2 FROM v$sql_bind_capture WHERE HASH_VALUE = '1659091011' order by 1

CHILD_NUMBER VALUE_STRI LAST_CAPTURED

0 2 20091203 05:37:11

1 1 20091203 05:39:23

2 2 20091203 05:42:18

3 999 20091203 05:43:15

How do I turn off ACS?

Alter system set "_ optimizer_extended_cursor_sharing_rel" = none

Alter system set "_ optimizer_extended_cursor_sharing" = none

Alter system set "_ optimizer_adaptive_cursor_sharing" = false

Proof:

SQL > alter session set "_ optimizer_extended_cursor_sharing_rel" = none

SQL > alter session set "_ optimizer_extended_cursor_sharing" = none

SQL > alter session set "_ optimizer_adaptive_cursor_sharing" = false

SQL > alter system flush shared_pool

SQL > var v number

SQL > exec: v: = 2

SQL > select / * comments*/ * from TESTBYHAO

2 where id =: v

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 254123311 1 286 N N Y

SQL > exec: v: = 1

SQL > select / * comments*/ * from TESTBYHAO

2 where id =: v

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 254123311 2 7354 N N Y

SQL > exec: v: = 1

SQL > select / * comments*/ * from TESTBYHAO

2 where id =: v

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 254123311 3 9710 N N Y

It can be seen that when I turn off these three hidden parameters at the session level, IS_BIND_SENSITIVE is always N, and no new cursor is generated after changing the binding variable.

Therefore, when we do the 11GR2 upgrade, we can first consider turning off these three parameters for the stability of the SQL PLAN while using the other 11G new feature.

After all, for highly concurrent OLTP databases, stability is more important than anything else.

Use hint to force BIND_AWARE

I discovered this hint:BIND_AWARE when I was studying all the new hint of 11G.

As a result, there is the impulse to study ACS, just have this article.

The original name of hint means that SQL will be forced to generate cursor for BIND_AWARE.

What's even more powerful is that even if you turn off these three ACS parameters as shown in the third point above, the hint still works!

I first turned off the parameters of these three ACS at the session level as above, and then carried out the following experiments.

SQL > exec: v: = 1

SQL > select / * comments*/ * from TESTBYHAO

2 where id =: v

Let's find out first, IS_BIND_AWARE=N.

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 325910803 1 7515 N N Y

Then add the hint of BIND_AWARE:

SQL > select / * + BIND_AWARE*/ * from TESTBYHAO

2 where id =: v

You can see the difference, IS_BIND_SENSITIVE=Y,IS_BIND_AWARE=Y.

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 325910803 1 7296 Y Y Y

Then change the binding variable and run it a few more times, and the result is familiar.

Again, this was tested after I turned off the ACS parameter at the session level.

It can be seen that the hint of BIND_AWARE is very strong.

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 325910803 2 7296 Y Y Y

1 254123311 2 73 Y Y N

2 254123311 12 Y Y Y

So, I thought of the hypothetical situation that when we upgraded to 11GR2, we didn't dare to use it because we didn't know anything about ACS, so we turned off the three parameters of ACS at the system level. But suddenly one day, when I found that a SQL PLAN due to data skew and binding variables was difficult to adjust, I could ask developers to add this hint to this particular SQL to break through the restriction of turning off ACS and use ACS. Therefore, this seems to be a good way for the new SQL tunning.

The omnipotent outline is stronger than anything else.

I wanted to finish it at the first four o'clock, but it suddenly occurred to me that we use countless outline on our existing system to fix the SQL PLAN. So if you upgrade to 11GR2, under the strong dominance of ACS, will outline fail?

With this doubt, I have done the following experiment, the conclusion is: outline is better than anything else! You can even break through the restrictions of BIND_AWARE, a powerful hint!

Experiment 1: do not use BIND_AWARE as a hint

SQL > select / * comment*/ * from TESTBYHAO

2 where id =: v

First generate a cursor whose ACS will take effect as desired:

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 325910803 1 7519 Y N Y

Use outline to fix the SQL.

Alter session set current_schema=HAOZHU_USER

Create outline ol_4107335673 for category temp_plan on

Select / * comment*/ * from TESTBYHAO

Where id =: v

Alter session set current_schema=HAOZHU_USER

Create outline ol_temp4107335673 for category temp_plan on

Select / * + full (TESTBYHAO) * / / * comment*/ * from TESTBYHAO

Where id =: v

Then exchange these two outline.

Then change id=2 to execute the same SQL again:

SQL > exec: VRV

SQL > select / * comment*/ * from TESTBYHAO

2 where id =: v

As a result, a new cursor (new HASH_VALUE) is created in v$SQL, which is neither the previous cursor nor the previous Child Cursor.

After executing the same SQL for id=2 above many times, we can see:

SQL > /

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 325910803 4 461.75 N N Y

It can be seen that after using outline, even if you turn on ACS,ACS, it will not take effect!

Experiment 2: using BIND_AWARE hint

Then someone will ask, the BIND_AWARE hint you mentioned in the fourth point is so powerful that it can break through the restriction of shutting down ACS, so can it break the limit of outline?

With this question in mind, I did the following experiment:

Use BIND_AWARE hint first and go to index range scan:

SQL > select / * + BIND_AWARE*/ / * comment*/ * from TESTBYHAO

2 where id =: v

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 254123311 1 73 Y Y Y

As mentioned above, we can see that the three "Ys" look very comfortable.

I went on to create an outline that permanently uses full table scans instead of index.

Alter session set current_schema=HAOZHU_USER

Create outline ol_new for category temp_plan on

Select / * + BIND_AWARE*/ / * comment*/ * from TESTBYHAO

Where id =: v

Alter session set current_schema=HAOZHU_USER

Create outline ol_tempnew for category temp_plan on

Select / * + FULL (TESTBYHAO) * / / * + BIND_AWARE*/ / * comment*/ * from TESTBYHAO

Where id =: v

Exchange, these two outline.

Then run the exact same SQL:

SQL > /

498 rows selected.

Note

-

-outline "OL_NEW" used for this statement

Then take a look at v$SQL:

CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS BG_PER_EX BS BA S

0 325910803 1 584 N N Y

Sure enough, a new implementation plan appeared, replacing the original one.

The number of execution is still 1, which means that the cursor in front of it has been flush, which is a brand new cursor.

IS_BIND_AWARE=N,IS_BIND_SENSITIVE=N means that the SQL is no longer under the control of ACS!

Conclusion: there are still countless experiments to be done on this interesting feature of ACS. For example, I still have 100, 000 questions in my mind: is SQL profile controlled by ACS? What is the specific function of each of the three hidden parameters of ACS? How is the selection rate of ACS calculated? Why is there a selection rate greater than 1.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.