Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to understand ORACLE cursor sharing". The explanation in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian slowly and deeply to study and learn "how to understand ORACLE cursor sharing" together!

Cursor sharing refers to sharing cursors.

Cursor sharing is easy to achieve, using binding variables can be achieved, but you will encounter the following two problems

1. Developers do not use binding variables when developing. This is because if you want to use binding variables, you will change sql.

2. Binding variables are used, but due to the influence of binding variable snooping, some parse trees and execution plans will be out of date.

To solve the above two problems, use two kinds of cursor sharing respectively

1. Regular cursor sharing to solve the problem 1

Enable system-bound variables with the parameter cursor_sharing

exact does not enable bound variables

similar is only enabled for safe predicate conditions. The so-called safety means that the execution plan will not change with the change of this value, such as the equivalent query of the primary key column, while the query of the range class is unsafe, such as greater than, less than, like, this is an outdated value. Do not set this value at any time.

force enables binding variables, which can be used to make where clauses or values inserts share the same execution plan and resolution without changing the code.

2. Adaptive cursor sharing

Adaptive cursor sharing introduced in 11g

bind sensitive refers to a target SQL execution plan that contains binding variables that may change depending on the input value of the binding variable. The child cursor corresponding to the target SQL is marked bind sensitive when the following three conditions are met.

1)Enable binding variable snooping

2) This SQL uses binding variables

3) The SQL is an unsafe predicate condition (such as a range query, an equivalent query with histogram statistics in the target column)

bind aware determines that the target SQL execution plan containing binding variables will change with the input value of the binding variables. If the following two conditions are met, the child cursor corresponding to the target SQL will be marked as bind aware.

1)It is labeled as bind sensitive.

2)The runtime statistics corresponding to the next two executions of this SQL are significantly different from the runtime statistics during hard parsing.

v$sql: is_bind_sensitive is_bind_aware is_shareable

Adaptive cursors share two related views: v$sql_cs_statistics v$sql_cs_selectivity

v$sql_cs_statistics Displays runtime statistics for the specified child_cursor

v$sql_cs_selectivity Displays the range of selectability for predicates containing bound variables stored in the specified child_cursor marked bind_aware.

The overall execution of adaptive cursors:

1. SQL is executed for the first time, hard parsing, and then judging whether to mark child_cursor as bind_sensitive according to a series of conditions (whether binding variables are used, the value of cursor_sharing, whether binding variable columns have histograms, equivalent query or range query, etc.). If marked, the runtime statistics for executing the SQL will be additionally stored in child cursor.

2. The second time the SQL is executed, soft parsing will be used, and the previous parse tree and execution plan will be reused.

3. The third execution, if the child_cursor has been marked as bind_sensitive before, and the runtime statistics of the second and third execution of the SQL are too different from the runtime statistics of hard parsing, the SQL will be hard parsed again, and a new child cursor will be generated, and the child cursor will be marked as bind_aware.

4. For sql marked as bind aware, when executed again, hard resolution or soft resolution will be selected according to the selectivity of the bound variable in the predicate condition. The judgment condition is that when the selectivity rate is in the range of selectivity rate in v$sql_cs_statistics when the predicate condition of the same name is in the hard analysis of the SQL, soft analysis is used, otherwise hard analysis is used.

Thank you for reading, the above is "how to understand ORACLE cursor sharing" content, after this study, I believe we have a deeper understanding of how to understand ORACLE cursor sharing this issue, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

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.