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

With the difference of the specific input value, the Selectivity of the where condition of SQL and the row number of result set (Cardinality) may change accordingly, and the values of Selectivity and Cardinality will directly affect the estimation of the cost value of the relevant execution steps by CBO, and then affect the choice of SQL execution plan by CBO. This means that the target SQL execution plan may change depending on the specific input value.

For SQL that does not use bound variables, the SQL text of the target SQL will change as soon as the specific input value changes, so that Oracle can easily calculate the values of the corresponding Selectivity and Cardinality, and then choose the execution plan accordingly. But for SQL that uses bound variables, the situation is completely different, because now no matter what the specific input value of the corresponding binding variable is, the SQL text of the target SQL is exactly the same. in this case, how should the Oracle determine the execution plan of the target SQL?

For SQL that uses bound variables, Oracle can choose the following two ways to determine its execution plan:

Using binding variables to snoop

If you do not use binding variable snooping, the default selectable rate (for example, 5%) is used for predicate conditions that may vary depending on the specific input value.

Binding variable snooping (Bind Peeking) was introduced in Oracle 9i. Whether binding variable snooping is enabled is controlled by the implicit parameter _ OPTIM_PEEK_USER_BINDS. The default value of _ OPTIM_PEEK_USER_BINDS is TRUE, indicating that binding variable snooping has been enabled by default in Oracle 9i and subsequent versions.

When bound variable snooping is enabled, whenever the Oracle parses the target SQL that uses the bound variable in a hard parsing way, the Oracle will actually Peeking the specific input values of the corresponding binding variable, and use these specific input values as the standard to determine the values of the Selectivity and Cardinality of the where condition of the target SQL that uses the bound variable, and select the execution plan of the SQL accordingly. This "Peeking" action will only be performed during hard parsing, and when the target SQL that uses the bound variable is executed again (in this case, the corresponding soft parsing / soft parsing), even if the specific input value of the bound variable is different from the corresponding value of the previous hard parsing, Oracle will continue to use the parsing tree and execution plan generated by the previous hard parsing, instead of repeating the above "snooping" action.

The benefits of binding variable snooping are obvious, because with binding variable snooping, Oracle can avoid using the default selectable rate when calculating the Selectivity and Cardinality values of the where condition of the target SQL, which makes it more likely to get an accurate execution plan for that SQL. Similarly, the disadvantages of bound variable snooping are obvious, for those target SQL whose execution plan may change with the specific input value of the corresponding bound variable, once bound variable snooping is enabled, its execution plan will be fixed, and what the fixed execution plan is depends entirely on the specific value of the corresponding binding variable passed in by the SQL during hard parsing. This means that once binding variable snooping is enabled, the target SQL will follow the parsing tree and execution plan generated by previous hard parsing, even if this is not appropriate for the current situation.

Binding variables have been criticized for snooping the resolution rights and execution plans generated by previous hard parsing regardless of the specific input values of subsequent bound variables (this situation has not been alleviated until the introduction of adaptive cursor sharing in Oracle 11g) Because it may make the execution plan selected by CBO in some cases (corresponding to some specific input values of bound variables) not the target SQL is the optimal execution plan in the current situation, and it may bring about a sudden change in the target SQL execution plan, which directly affects the performance of the application system.

For example, the execution plan of a SQL will correspond to two execution plans depending on the specific input value of the bound variable, one is to scan the index range of the index, and the other is to scan the index quickly and fully. Normally, for the vast majority of bound variable input values, the execution plan should follow the index range scan, and in rare cases the index will be scanned quickly. But if a Shared Cursor corresponding to the SQL is age out out of the Shared Pool, then the Oracle has to be hard parsed when the SQL is executed again. Unfortunately, if the input value of the binding variable passed in during this hard parsing happens to be one of the few cases corresponding to a fast full scan of the index, then the subsequent SQL will follow this execution plan, in which case the SQL is likely to be one or more orders of magnitude slower than before. The performance in the application system is to suddenly find that an application can't run, and it has been fine before.

Let's look at an example of binding variable snooping:

Create test table T1 and index and collect statistics

Zx@MYDB > create table T1 as select * from dba_objects;Table created.zx@MYDB > create index idx_t1 on T1 (object_id); Index created.zx@MYDB > select count (*) from T1; COUNT (*)-72005zx@MYDB > select count (distinct (object_id)) from T1 COUNT (DISTINCT (OBJECT_ID))-- 72005zx@MYDB > exec dbms_stats.gather_table_stats (ownname= > USER,tabname= > 'T1ZHANGZHANGZHANGZHANGZHANG) (PL/SQL procedure successfully completed.) PL/SQL procedure successfully completed.

Execute the following two sql and check the resolution of SQL by Oracle

Zx@MYDB > select count (*) from T1 where object_id between 999 and 1000; COUNT (*)-2zx@MYDB > select count (*) from T1 where object_id between 999 and 600000; COUNT (*)-58180zx@MYDB > col sql_text for a80zx@MYDB > select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count (*) from T1%' SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS -select count (*) from T1 where object_id between 999 and 1000 5gu397922cuqd 1 1select count (*) from T1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1

As you can see from the query results, Oracle uses hard parsing when executing the above SQL. Oracle generates a Parent Cursor and a Child Cursor for each of the above two SQL.

Then look at the execution plan:

From the execution plan, we can see that the SQL of the between 999 and 1000 condition takes the index range scan, while the between 999 and 60000 takes the execution plan of the index fast full scan.

Now let's transform the comprehensive two SQL into the equivalent form of using bound variables. Define two binding variables x and y and assign them values 999 and 1000, respectively.

Zx@MYDB > var x number;zx@MYDB > var y number;zx@MYDB > exec: X: = 999PL procedure successfully completed.zx@MYDB > exec: y: = 1000pm PL SQL procedure successfully completed.

Obviously, the rewritten form "between: X and: y" of the binding variables x and y is equivalent to the original "between 999 and 1000" at this time. And as long as you re-assign y to 60000, it is equivalent to "between 60000 and 60000".

Now the values of x and y are 999 and 100 respectively, and the rewritten sql is executed.

Zx@MYDB > select count (*) from T1 where object_id between: X and: y; COUNT (*)-2zx@MYDB > select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count (*) from T1%' SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS -select count (*) from T1 where object_id between 999 and 1000 5gu397922cuqd 1 1select count (*) from T1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1select count (*) from T1 where object_id between: X and: y 9dhu3xk2zu531 1 1

As can be seen from the above query results, Oracle also uses hard parsing when executing the above equivalent SQL for the first time.

From the execution plan, this is the index range scan taken by the index IDX_T1, and Oracle estimates that the value of the Cardinality returned by the index range scan is 3. And notice that the content of the "Peeked Binds" section is "1 -: X (NUMBER): 9992 -: y (NUMBER): 1000", which shows that Oracle does use binding variable snooping in the process of hard parsing the above SQL, and the specific input values of binding variables x and y seen when doing "snooping" are 999 and 1000, respectively.

Now leave x unchanged and change y to 60000:

Zx@MYDB > exec: y: = 60000 > select count (*) from T1 where object_id between: X and: y; COUNT (*)-58180

From the above query results, we can see that the value of VERSION_COUNT for the above SQL is 1, and the value of column EXECUTIONS is 2, indicating that Oracle used soft parsing when executing the SQL for the second time.

As can be seen from the execution plan, the execution plan of SQL is still scanning the index range of the index IDX_T1, and the content of the "Peeked Binds" section is still "1 -: X (NUMBER): 9992 -: y (NUMBER): 1000".

Previously, we already knew that Oracle performed a fast full scan of the index when performing the "between 999 and 60000" condition without binding variables. But the second time you execute a SQL that is rewritten with binding variables equivalent, even though the specific input values of the binding variables x and y are 999 and 60000, the Oracle still uses the parsing tree and execution plan generated by the previous hard parsing of the SQL (corresponding to the specific input values of the binding quantity x and y are 999 and 1000), instead of repeatedly performing the "snooping" action.

If you want the above equivalent SQL to do a fast full scan of the index again, just ask Oracle to use hard parsing when performing SQL again. Because once hard parsing is used, Oracle performs a "snooping" action again. There are many ways to have Oracle execute the target SQL again using hard parsing, one of which is to DDL the tables involved in the target SQL. Because once a DDL operation is performed on a table, all Shared Cursor in the library cache that contain the table in the SQL text will be marked as invalid by Oracle, which means that the parse tree and execution plan stored in these Shared Cursor can no longer be reused, so hard parsing is used when Oracle executes the SQL associated with the table again. Here you choose to COMMENT the table, which is also a DDL operation.

Execute the COMMENT statement and the equivalent SQL against table T1

Zx@MYDB > comment on table T1 is' Test table for Bind Peeking';Comment created.zx@MYDB > select count (*) from T1 where object_id between: X and: y; COUNT (*)-58180zx@MYDB > select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count (*) from T1%' SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS -select count (*) from T1 where object_id between 999 and 1000 5gu397922cuqd 1 1select count (*) from T1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1select count (*) from T1 where object_id between: X and: y 9dhu3xk2zu531 1 1

From the query results above, you can see that the value of the column VERSION_COUNT corresponding to the equivalent SQL is 1, and the value of the column EXECUTIONS has changed from 2 to 1, indicating that Oracle uses hard parsing when executing the SQL for the third time (the value of EXECUTIONS is 1, because Oracle regenerates a pair of Parent Cursor and Child Cursor here, and the Shared Cursor corresponding to the value of 2 of EXECUTIONS has been marked as invalid by Oracle, which is equivalent to being abandoned).

As can be seen from the execution plan, the execution plan now takes a fast full scan of the index, and Oracle estimates that the value of the Cardinality returned by performing this fast full scan of the index is 57646. And the content of the "Peeked Binds" section is still "1 -: X (NUMBER): 9992 -: y (NUMBER): 60000". It shows that Oracle does use binding variable snooping again in the process of performing the above SQL, and the specific input values of binding variables x and y are 999 and 60000 respectively.

Now set the value of the implicit parameter _ OPTIM_PEEK_USER_BINDS to FALSE to turn off binding variable snooping:

Zx@MYDB > alter session set "_ optim_peek_user_binds" = false;Session altered.

Then leave the value of x unchanged and change the value of y to 1000

Zx@MYDB > select count (*) from T1 where object_id between: X and: y; COUNT (*)-2zx@MYDB > select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count (*) from T1%' SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS -select count (*) from T1 where object_id between 999 and 1000 5gu397922cuqd 1 1select count (*) from T1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1select count (*) from T1 where object_id between: X and: y 9dhu3xk2zu531 2 2

From the query results above, you can see that the values of column VERSION_COUNT and column EXECUTIONS corresponding to the equivalent SQL have changed from 1 to 2, indicating that Oracle uses hard parsing when executing the SQL for the fourth time. The value of VERSION_COUNT is 2, which means that there are two Child Cursor hanging under the Parent Cursor where the SQL is located. From the following query results, you can see that the SQL does have two Child Cursor:

Zx@MYDB > select plan_hash_value,child_number from v$sql where sql_id='9dhu3xk2zu531';PLAN_HASH_VALUE CHILD_NUMBER--1410530761 0 2351893609 1

Obviously, when we turn off binding variable snooping and execute SQL again, the resolution rights and execution plan should be stored in the Child Cursor with a CHILD_NUMBER of 1. View the execution plan

From the execution plan, we can see that the execution plan of Oracle has changed from the previous index fast full scan to the current index range scan. And Oracle estimates that the value of Cardinality for the result set returned by performing this index range scan is 180. Notice that the Outline Data section has "OPT_PARAM ('_ optim_peek_user_binds' 'false')", and there is no "Peeking Binds" section in the execution plan, indicating that Oracle has disabled binding variable snooping at this time.

I've already introduced that using the DDL operation allows Oracle to use hard parsing when performing SQL again, but the drawback of this approach is that the scope of influence is still too wide, because once a DDL operation is performed on a table, hard parsing is used for all SQL related to that table again. This is very bad, especially for OLTP-type applications, because it may lead to a sharp increase in the number of hard parsing in a short period of time, which in turn affects the performance of the system.

Here is another way to let Oracle use hard parsing when executing the target SQL again, but its scope of influence is limited to the Shared Cursor corresponding to the target SQL, that is, it allows Oracle to use hard parsing when executing the target SQL, and remains the same when executing all other SQL.

This approach is to use DBMS_SHARED_POOL.PURGE. It is a method introduced since Oracle 10.2.0.4 that can be used to delete a specified Shared Cursor cached in the library cache. The principle that DBMS_SHARED_POOL.PURGE allows Oracle to use hard parsing when executing a target SQL is obvious-- if the Shared Cursor corresponding to a SQL is deleted, Oracle will naturally use hard parsing when executing the SQL again.

Check the ADDRESS and HASH_ value values for the target SQL:

Zx@MYDB > select sql_text,sql_id,version_count,executions,address,hash_value from v$sqlarea where sql_text like 'select count (*) from T1' SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ADDRESS HASH_VALUE - -select count (*) from T1 where object_id between 999 and 1000 5gu397922cuqd 11 00000000B4D1B130 1143368397select count (*) from T1 where object_id between 999 and 60000 b8xxw70vja3tn 11 00000000B4D1AA90 924127028select count (*) from T1 where object _ id between: x and: y 9dhu3xk2zu531 22 00000000B4CC4840 2247955553

Use dbms_shared_pool.purge to delete the Shared Cursor of the target SQL:

Zx@MYDB > exec sys.dbms_shared_pool.purge ('00000000B4CC4840 PL/SQL procedure successfully completed.zx@MYDB 2247955553); PL/SQL procedure successfully completed.zx@MYDB > select sql_text,sql_id,version_count,executions,address,hash_value from v$sqlarea where sql_text like' select count (*) from T1%' SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ADDRESS HASH_VALUE - -select count (*) from T1 where object_id between 999 and 1000 5gu397922cuqd 11 00000000B4D1B130 1143368397select count (*) from T1 where object_id between 999 and 60000 b8xxw70vja3tn 11 00000000B4D1AA90 924127028

As you can see from the above query results, dbms_shared_pool.purge has indeed deleted the Shared Cursor corresponding to the target sql.

It should be noted that if dbms_shared_pool.purge is used in 10.2.0.4, the agent must set event 5614566 (alter session set events' 5614566 trace name context forever') before using it, otherwise dbms_shared_pool.purge will not work, and this restriction no longer exists in versions above 10.2.0.4.

Now leave the x value unchanged, change y to 60000, and execute the target SQL:

Zx@MYDB > exec: y: = 60000 select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like select count procedure successfully completed.zx@MYDB > select count (*) from T1 where object_id between: X and: y; COUNT (*)-58180 zx@MYDB > select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count (*) SQL T1%' SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS -select count (*) from T1 where object_id between 999 and 1000 5gu397922cuqd 1 1select count (*) from T1 where object_id between 999 and 60000 b8xxw70vja3tn 1 1select count (*) from T1 where object_id between: X and: y 9dhu3xk2zu531 2 1zx@MYDB > select plan_hash_value Child_number from v$sql where sql_id='9dhu3xk2zu531' PLAN_HASH_VALUE CHILD_NUMBER--2351893609 0

From the query results above, you can see that the value of the VERSION_COUNT corresponding to the SQL is 2 and the value of operations is 1. A value of 1 of EXECUTIONS means that Oracle is indeed using hard parsing when performing some SQL, but the value of VERSION_COUNT should be 1. We can also see from the query that there is only one Child Cursor hanging under the Parent Cursor of the target SQL, so the value of VERSION_COUNT should be 1 instead of 2 (maybe it's Oracle's BUG, no longer delve into it).

As you can see from the execution plan, Oracle still chooses the index range scan at this time, and Oracle estimates that the Cardinality value of the result set returned by this index range scan is still 180.

This means that when we turn off binding variable snooping, no matter how many values are passed to the binding variables x and y, it does not affect Oracle's choice of the target SQL execution plan. This is also in line with what was mentioned earlier: if you do not use binding variable snooping, Oracle will use the default selectable rate (for example, 5%) for predicate conditions where the selectable rate may change with specific input values.

How is 180 calculated?

The calculation formulas for Selectivity and Cardinality for the where conditions of the above SQL are as follows:

Cardinality = NUM_ROWS * Selectivity

Selectivity = 0. 05 to 0.05

The above formula is applicable to the calculation of Selectivity and Cardinality with bound variable snooping disabled and the where condition is "target column between x and y".

NUM_ROWS indicates the number of records in which the target column resides

The where condition is "target column between x and y", which is equivalent to "target column > = x and target column = x and target column"

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.