Dynamic variable profiling of New Oracle11g features

Welcome to the Oracle community forum and interact with 2 million technical staff to access the binding variables before 1.11g. We all know that in order to allow SQL statements to share the execution plan, oracle always emphasizes that when designing an application system, you must bind a variable, that is, replace the literal value that originally appeared in an SQL statement with a variable. For example

Welcome to the Oracle community forum and interact with 2 million technical staff> enter 1. we all know that in order to allow SQL statements to share execution plans, oracle always emphasizes that binding variables must be used for application system design, that is, a variable is used to replace the literal value that originally appeared in the SQL statement. For example

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

1. Check the binding variable before 11 GB

We all know that, in order to allow SQL statements to share execution plans, oracle always emphasizes that binding variables must be used for application system design, that is, a variable is used to replace the literal value that originally appeared in the SQL statement. For example, for the following three SQL statements:

Select col1 from t where col2 = 1; Select col1 from t where col2 = 2; Select col1 from t where col2 = 3;

As we can see, these three SQL statements are almost the same, only the literal values (1, 2, 3) IN THE where condition are different. However, if it is written like this, oracle does not know that these three SQL statements are the same, and still treats them as three completely different SQL statements, so as to perform hard parsing in the shared pool, and generate the final execution plan. However, we will find that the three execution plans may be the same, so the next two generation of the execution plan may be completely unnecessary, this is especially true in typical OLTP environments. Since parsing itself is CPU-intensive, we recommend that you write the following SQL statement to reduce CPU consumption:

Select col1 from t where col2 =: v1;

Then, pass 1, 2, and 3 to v1 respectively. In this case, you only need to parse the data when 1 is passed in for the first time. In the subsequent steps 2 and 3, since the SQL text itself has not changed, you can directly use the execution plan and do not need to generate the execution plan again.

However, generating execution plans is based on the probability theory. You can guess the approximate number of returned records based on your where condition without accessing the data in a specific table, to determine the access path to be used. Obviously, you must make a guess by referring to the values in the where condition. This is in conflict with the original intention of saving CPU, because the key to saving CPU is to use the Bind Variable. Once you use the Bind Variable, isn't oracle unaware of your specific literal value?

To solve this problem, oracle introduced the Bind Variable to peat. When oracle first parses an SQL statement (that is, when the SQL statement is passed into the shared pool for the first time), it will bring the value of the bound variable into the SQL statement, then you can guess the approximate number of records returned by the SQL statement based on your literal value to get an optimized execution plan. Then, when you execute the same SQL statement again later, you will not consider the value of the bound variable you entered and directly retrieve the bound variable generated for the first time.

However, it is a pity that using the Bind Variable to share the cursor and SQL optimization are two conflicting goals. The premise for Oracle to bind variables is that oracle considers that data in most columns is evenly distributed. Therefore, the execution plan obtained by using the value of the variable bound for the first time is applicable to other values of the variable bound in most cases. Obviously, if the value of the bound variable entered for the first time is a high percentage of the total data volume, the execution plan of the full table scan will be caused. However, if the percentage of the bound variable values that are passed in to the data volume is very low, it would be better to use index scanning. However, due to the use of the bound variable, therefore, oracle does not look at the value of your bound variable, but uses the execution plan of the full table scan directly. At this time, due to the use of Bound variables, although we achieve the purpose of sharing the cursor, thus saving the CPU, but the SQL Execution Plan is not optimized enough.

So how can we choose between variable binding and SQL optimization? In OLTP applications, due to high concurrency, the competition for CPU usage will be severe, and the SQL itself runs for a short period of time, resulting in a small amount of data involved, the parsing time accounts for a large proportion of the SQL Execution time, while the time spent on I/O is less. Therefore, although the SQL statement is not optimized enough to bind a variable, we recommend that you use the variable binding function. However, in DSS applications and data warehouse applications, due to low concurrency, the competition for CPU usage is lighter, and SQL statements are executed for a long time, and the main time is spent waiting for I/O, the proportion of resolution is low, and the importance of optimizing the SQL Execution Plan is reflected. Therefore, we recommend that you do not use the Bind Variable instead of the literal value. However, in most cases, hybrid applications have both OLTP and data warehouse, so it is difficult to solve this problem perfectly.

Let's take a look at how the Bind Variable pivoting works before 11g, taking 10g as an example.

First, create a table to make the distribution of the data contained in the table uneven, and create an index on the table.

Hr @ ora10g> create table t1 as select object_id as id, object_name from dba_objects; Hr @ ora10g> update t1 set id = 1 where rownum <= 10000; Hr @ ora10g> commit; Hr @ ora10g> create index idx_t1 on t1 (id );

In this way, there are 10 thousand records with id 1 and only one record with id other values. In this way, we create a test table with uneven distribution. Then, we collect statistics. Note: We need to collect a histogram to make the CBO know that the data distribution on the id column is uneven.

Hr @ ora10g> begin 2 dbms_stats.gather_table_stats ( 3 user, 4 't1 ', 5 cascade => true, 6 method_opt => 'for columns id size 254' 7 ); 8 end; 9/

We found the largest id in Table t1 and passed it as the first binding variable. we can imagine that this binding variable will lead to an index. Note that the optimizer we set here is set to all_rows.

Hr @ ora11g> select max (id) from t1; MAX (ID) ---------- 13871 Hr @ ora10g> alter system flush shared_pool; Hr @ ora10g> var v_id number; Hr @ ora10g> var v_ SQL _id varchar2 (20 ); Hr @ ora10g> exec: v_id: = 13871; Hr @ ora10g> select * from t1 where id =: v_id; The query result is omitted here. Hr @ ora10g> begin 2 select SQL _id into: v_ SQL _id from v $ SQL 3 where SQL _text like 'select * from t1 where id =: v_id % '; 4 end; 5/ Hr @ ora10g> select * from table (dbms_xplan.display_cursor (: v_ SQL _id )); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL _ID djwq30cpbcz7k, child number 0 ------------------------------------- Select * from t1 where id =: v_id Plan hash value: 50753647 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time -------------------------------------------------------------------------------- | 0 | select statement | 11 (100) | | 1 | table access by index rowid | T1 | 1365 | 28665 | 11 (0) | 00:00:01 | * 2 | index range scan | IDX_T1 | 1365 | 3 (0) | 00:00:01 -------------------------------------------------------------------------------- ...... Hr @ ora10g> exec: v_id: = 1; Hr @ ora10g> select * from t1 where id =: v_id; The query result is omitted here. Hr @ ora10g> begin 2 select SQL _id into: v_ SQL _id from v $ SQL 3 where SQL _text like 'select * from t1 where id =: v_id % '; 4 end; 5/ Hr @ ora10g> select * from table (dbms_xplan.display_cursor (: v_ SQL _id )); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL _ID djwq30cpbcz7k, child number 0 ------------------------------------- Select * from t1 where id =: v_id Plan hash value: 50753647 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time -------------------------------------------------------------------------------- | 0 | select statement | 11 (100) | | 1 | table access by index rowid | T1 | 1365 | 28665 | 11 (0) | 00:00:01 | * 2 | index range scan | IDX_T1 | 1365 | 3 (0) | 00:00:01 --------------------------------------------------------------------------------

From the above results, we can see that when the first value is 13871 for the bound variable, the index scan is performed because the number of returned records is small. When we bind variable value 1 for the second time, oracle will not generate a new execution plan, but will directly use the index scan execution path.

However, what if we pass in the Bind Variable value of 1 first and then the Bind Variable value of 13871? We continue the test.

Hr @ ora10g> alter system flush shared_pool; Hr @ ora10g> set autotrace traceonly exp stat; Hr @ ora10g> exec: v_id: = 1; Hr @ ora10g> select * from t1 where id =: v_id; Hr @ ora10g> begin 2 select SQL _id into: v_ SQL _id from v $ SQL 3 where SQL _text like 'select * from t1 where id =: v_id % '; 4 end; 5/ Hr @ ora10g> select * from table (dbms_xplan.display_cursor (: v_ SQL _id )); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL _ID djwq30cpbcz7k, child number 0 ------------------------------------- Select * from t1 where id =: v_id Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | -------------------------------------------------------------------------- | 0 | select statement | 13 (100) | | * 1 | table access full | T1 | 8738 | 179 K | 13 (0) | 00:00:01 | -------------------------------------------------------------------------- ...... Hr @ ora10g> exec: v_id: = 13871; Hr @ ora10g> select * from t1 where id =: v_id; Hr @ ora10g> begin 2 select SQL _id into: v_ SQL _id from v $ SQL 3 where SQL _text like 'select * from t1 where id =: v_id % '; 4 end; 5/ Hr @ ora10g> select * from table (dbms_xplan.display_cursor (: v_ SQL _id )); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL _ID djwq30cpbcz7k, child number 0 ------------------------------------- Select * from t1 where id =: v_id Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | -------------------------------------------------------------------------- | 0 | select statement | 13 (100) | | * 1 | table access full | T1 | 8738 | 179 K | 13 (0) | 00:00:01 | --------------------------------------------------------------------------

Obviously, when you bind a variable of 1, the generated execution plan is scanned in the entire table. The best execution path for variable binding in the following 13871 should be index scanning. However, because CBO does not know this, it uses the execution plan generated for the first time, as a result, the full table scan is performed.

[1] [2]