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

Today we will talk about the impact of the following parameters on database performance and stability:

Cursor_sharing: cursor sharing

_ optim_peek_user_binds: bind variable peep

_ optimizer_adaptive_cursor_sharing: adaptive cursor sharing (ACS for short), which generally includes two other _ optimizer_extended_cursor_sharing and _ optimizer_extended_cursor_sharing_rel parameters)

_ optim_peek_user_binds and ACS:

At 10g, since there is no ACS, it is generally recommended that customers turn off binding variable snooping.

At 11g, many customers still turned off binding variable snooping and ACS because the database was upgraded from 1g and did not change after the upgrade, and because earlier versions of ACS had some bug. In fact, these are two very good parameters, can be written in the code is not too good, can also achieve better performance. Although ACS may still have some small bug unresolved (some bug will only be triggered in very special circumstances), version 11204 should not be a big problem. Tiger Liu suggested that it would be better to open them all.

The most important parameters are still below, and if you do the following, the above two parameters will not be so important:

First of all, the parameter cursor_sharing is very important to system performance and stability, but it is often ignored. It is recommended to use the default value of this parameter:

Cursor_sharing=EXACT (not FORCE or similar)

This requires that where bound variables should be used, bound variables must be used. This is an iron rule for OLTP systems, and there is no doubt that cursor_sharing=FORCE is usually designed to solve the problem of using bound variables instead of binding variables. The year before last, I saw a widely circulated AWR report of a fruit company on the Internet, and it was amazing to set up cursor_sharing= FORCE.

Secondly, there is an important supplementary condition:

Where bound variables should not be used, do not bind variables: for fields with fewer unique values, especially where the data is unevenly distributed, binding variables are not recommended. For fields such as type, status, etc., we recommend using the constant: where type=1 and status=2.

If binding variables are used in this case, that's when binding variables snoop and ACS come into play.

If cursor_sharing=FORCE; or cursor_sharing=EXACT, but bind variables are also used on fields where the data is unevenly distributed (the two are basically the same, although the latter is slightly better than the former), then consider the effects of the parameters "bind variable snoop" and "adaptive cursor".

Look at the following situations:

1. Turn off "bind variable peep" (default is on):

When ACS fails at the same time, the stability of the system is good (the execution plan will not change due to different binding variables), but the overall performance will decline: because you cannot peek into bound variables, you can only calculate if the fields are evenly distributed, and there will be large deviations in the estimation of whether you can use indexes and return row sources, and sometimes you may use hint in conjunction with hint to improve SQL performance.

2. If "bind variable peep" is enabled but ACS is not enabled (default is enabled):

Then the system will be extremely unstable: for example, hard parsing peeps into an execution plan in which a bound variable is suitable for a full table scan, regardless of whether the next bound variable can use an index or not. it is not possible to regenerate a new execution plan until the next hard parsing by peeking at the bound variable again.

3. If "bind variable peep" is enabled, and ACS is enabled:

This situation not only solves part of the stability, but also takes into account the performance. This is also where 11g's new ACS is better than 10g's without ACS: the execution plan is no longer consistent, but will not be adjusted in time according to different binding variables: for example, if the binding variable you peep at for the first time is suitable for full table scan, then even if the binding variable used for the second time is suitable for index walking, the execution plan for full table scan will still be used. The next execution will correct the execution plan to use the index (please refer to the implementation principle of ACS for details).

Binding variable snooping and ACS are closely related to histogram information, and turning off histogram collection is equivalent to turning off binding variable peeping and ACS, even if these two parameters are turned on.

Histogram can accurately reflect the data distribution of uneven data distribution fields, generally use the default option (auto), some special cases can supplement or remove the histogram information of some fields. It is not recommended for some customers to turn off collecting histograms at the database level.

Summary:

Best practices:

Cursor_sharing=EXACT + reasonable use of binding variables (reasonable is: fields with unique values equal to or close to the number of table rows, such as ID and account_no, must use bound variables, while fields with few unique values and uneven numbers, such as type and status, do not use binding variables).

Bind variable snooping and ACS are left on by default.

Special circumstances:

1. There are a certain number of unique values of the field (between less and more), such as 1000. If the data is evenly distributed, you can use bound variables. If the fields are unevenly distributed, use constants for the majority of values, and bind variables for other values.

2. The unique value of the field is small, and there are situations that often change with each other, such as the common work order processing table: the state that is not processed is 0, and the state after processing is 1. After the night statistical information is collected, due to the instability of the field value, the statistical information often can not reflect the real-time data distribution of the table. In this case, it is meaningless to talk about whether to use binding variables or not, which involves the SQL of this kind of table. You can turn off histogram collection on fields, work with rownum and hint to improve SQL efficiency and stability, and use dynamic_sampling (dynamic sampling) if necessary to assist the optimizer in making the correct execution plan.

Worst combination:

Cursor_sharing=FORCE

_ optim_peek_user_binds=TRUE (enable binding variable peep)

_ optimizer_adaptive_cursor_sharing=FALSE (turn off ACS, and the other two parameters should be set together)

Unstable execution plans can also lead to low performance.

Prerequisites for using ACS:

1. Bind variables use variables to peep

two。 Use histograms on columns that bind variables

To close acs:

Let's first take a look at the three hidden parameters related to ACS, which are used to control whether ACS is enabled.

Col ksppinm for a30

Col ksppstvl for a20

Col ksppdesc for a35

SELECT ksppinm, ksppstvl, ksppdesc

FROM x$ksppi x, x$ksppcv y

WHERE x.indx = y.indx AND ksppinm ='_ optimizer_adaptive_cursor_sharing'

KSPPINM KSPPSTVL KSPPDESC

-

_ optimizer_adaptive_cursor_sha TRUE optimizer adaptive cursor sharing

Ring

SELECT ksppinm, ksppstvl, ksppdesc

FROM x$ksppi x, x$ksppcv y

WHERE x.indx = y.indx AND ksppinm ='_ optimizer_extended_cursor_sharing'

KSPPINM KSPPSTVL KSPPDESC

-

_ optimizer_extended_cursor_sha UDO optimizer extended cursor sharing

Ring

SELECT ksppinm, ksppstvl, ksppdesc

FROM x$ksppi x, x$ksppcv y

WHERE x.indx = y.indx AND ksppinm ='_ optimizer_extended_cursor_sharing_rel'

KSPPINM KSPPSTVL KSPPDESC

-

_ optimizer_extended_cursor_sha SIMPLE optimizer extended cursor sharing f

Ring_rel

So if we want to shut down ACS, use the following command

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

Reference:

Http://mp.weixin.qq.com/s?__biz=MzIzMTQ3OTE4Mw==&mid=2247483871&idx=1&sn=06a86ac02f4f63e339979588308ea386&scene=1&srcid=09140h8P90bBFNlYiDgaEojG#rd

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.