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

one

Adaptive cursor sharing Adaptive Cursor Sharing or extended cursor sharing (Extended Cursor Sharing) is one of the new features of Oracle 11g, which is mainly used to solve the defect that the previous version of SQL statement can not get the best execution plan due to binding variable snooping, that is, it can automatically identify inefficient cursors (sub-cursors) and choose the best execution plan.

2 use binding variables to execute the SQL statement and get the first execution

3 the external embodiment of adaptive cursor sharing

Adaptive cursor sharing is mainly realized through three fields, namely is_bind_sensitive,is_bind_aware,is_shareable. (note: these three fields are only available in Oracle 11g.

Exists in). As you can see from the query above from v$sql (there is no is_shareable in v$sqlarea), the three fields are assigned different values and represent different meanings.

Is_bind_sensitive (whether binding is sensitive)

Indicates whether the binding variable element is used in the subcursor, and the bind peeking method is used for execution plan generation. If the execution plan depends on the snooping value, here is Y

Otherwise, it is N.

Is_bind_aware (whether the binding is known)

Indicates whether the subcursor uses extended cursor sharing technology, if yes, Y, otherwise N, if N, the cursor will be discarded and no longer available.

Is_shareable (whether it can be shared)

Indicates whether the subcursor can be shared by the next soft resolution. If it can be shared, it is Y, otherwise it is N, which means that the sub-cursor has lost its sharing value and is eliminated according to the LRU algorithm.

Since the SQL statement is executed for the first time, you can learn from the results of the v$sql query

Is_bind_sensitive is Y (run for the first time, bind peeking executed)

Is_bind_aware is N (first run, not supported by extended cursor sharing)

Is_shareable is Y (execution plan can be shared)

1. Adaptive cursor sharing performs snooping when the SQL statement is executed for the first time (using binding variables), and records the snooping results. If the same SQL statements are executed later, the snooping results are compared to determine whether a new execution plan needs to be generated. This is whether the binding variable is sensitive.

2. The knowability of the bound variable is used to determine whether the current cursor is extensible cursor sharing. When it is unknowable, the cursor is discarded.

3. The essence of adaptive cursor sharing is to realize multiple binding variable snooping on the basis of Oracle 10g, which increases the probability of obtaining the best execution plan choice.

4. Although the adaptive cursor sharing feature is used, there is no guarantee that each execution of the SQL statement will follow the best plan.

5. Adaptive cursor sharing does not guarantee that the same SQL statement will be executed according to the same execution plan twice, because adaptive cursor sharing will keep trying peeking.

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.