11g Adaptive Cursor Sharing

The binding variables before 11g Adaptive Cursor Sharing 11g have a peek, leading to some binding variables that cause execution plan problems. Study the Adaptive Cursor Sharing [SQL] SQL> select * from v $ version; BANNER Release Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production PL/SQL Release 11.2.0.3.0-Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0-Production NLSRTL Version 11.2.0.3.0-Production 1: Create a test table, analyze the test table [SQL] SQL> Drop table t; Table dropped. SQL> CREATE TABLE t 2 AS 3 SELECT rownum AS id, rpad ('*', 100, '*') AS pad 4 FROM dual 5 CONNECT BY level <= 1000; table created. SQL> ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id); Table altered. SQL> BEGIN 2 dbms_stats.gather_table_stats (3 ownname => user, 4 tabname => 'T', 5 estimate_percent => 100, 6 method_opt => 'for all columns size 1 '7 ); 8 END; 9/: 2: View Data distribution [SQL] SQL> SELECT count (id), count (DISTINCT id), min (id), max (id) FROM t; COUNT (ID) COUNT (DISTINCTID) MIN (ID) MAX (ID) ---------- ----------------- ---------- 1000 1000 1 1000 3: If the binding is not used, query the 990 condition and scan the entire table. For the expected execution plan: [SQL] SQL> SELECT count (pad) FROM t WHERE id <990; COUNT (PAD) ---------- 989 SQL> SELECT * FROM table (dbms_xplan.display_cursor (NULL, NULL, 'Basic '); PLAN_TABLE_OUTPUT tables ----------------------- explained SQL STATEMENT: ------------------------ SELECT count (pad) FRO M t WHERE id <990 Plan hash value: 2966233522 STATEMENT | Id | Operation | Name | STATEMENT | 0 | select statement | 1 | sort aggregate | 2 | table access full | T | --------------------------------- 4: without variable binding. When condition 10 is adopted, cbo selects the correct execution plan [SQL] SQL> SELECT count (pad) FROM t WHERE id <10; COUNT (PAD) ---------- 9 QL> SELECT * FROM table (dbms_xplan.display_cursor (NULL, NULL, 'Basic '); PLAN_TABLE_OUTPUT tables --------------------- explained SQL STATEMENT: -------------------- SELECT count (Pad) FROM t WHERE id <10 Plan hash value: 4270555908 bytes | Id | Operation | Name | ------------------------------------------- | 0 | select statement | 1 | sort aggregate | 2 | table access by index rowid | T | 3 | INDEX RANGE SCAN | T_PK | 5: use the Bind Variable to have a look. Cbo finds that the value of the variable to be bound goes through the full table, so it goes through the full table scan. Normally [SQL] SQL> var id number; SQL> EXECUTE: id: = 990; PL/SQL procedure successfully completed. SQL> SELECT count (pad) FROM t WHERE id <: id; COUNT (PAD) ---------- 989 SQL> SELECT * FROM table (dbms_xplan.display_cursor (NULL, NULL, 'Basic ')); PLAN_TABLE_OUTPUT tables ---------------------------------------------------------------------------------------------------------------------- -------------------------------- Explained SQL STATEMENT: ---------------------- SELECT count (pad) FROM t WHERE id <: id Plan hash value: 2966233522 bytes | Id | Operation | Name | --------------------------------- | 0 | select statement | 1 | sort aggregate | 2 | table access full | T | 6: The binding variable is changed to 10, in fact, index should be used at this time. But because of the binding. Cbo is so stupid that it still uses full table scan [SQL] SQL> EXECUTE: id: = 10; PL/SQL procedure successfully completed. SQL> SELECT count (pad) FROM t WHERE id <: id; COUNT (PAD) ---------- 9 SQL> SELECT * FROM table (dbms_xplan.display_cursor (NULL, NULL, 'Basic ')); PLAN_TABLE_OUTPUT tables ----------------------- EXPL Ained SQL STATEMENT: ------------------------ SELECT count (pad) FROM t WHERE id <: id Plan hash value: 2966233522 ----------------------------------- | Id | Operation | Name | ----------------------------------- | 0 | select statement | 1 | sort aggregate | 2 | table access full | T | 7: the statement is executed 2nd times. Note that the Bind Variable value is still 10 at this time. [SQL] SQL> SELECT count (pad) FROM t WHERE id <: id; COUNT (PAD) ---------- 9 SQL> SELECT * FROM table (dbms_xplan.display_cursor (NULL, NULL, 'Basic '); PLAN_TABLE_OUTPUT tables --------------------- explained SQL STATEMENT: -------------------- SELECT coun T (pad) FROM t WHERE id <: id Plan hash value: 4270555908 bytes | Id | Operation | Name | ------------------------------------------- | 0 | select statement | 1 | sort aggregate | 2 | table access by index rowid | T | 3 | INDEX RANGE SCAN | T_PK | --------------------------------------------- the execution plan was changed to an index. This is the new feature of 11g. Conclusion: ACS refers to Oracle's independent selection between different cursor sharing technologies. Oracle supports two cursor sharing technologies: the traditional PCST (Previous Cursor Sharing Technique) generates cursor during hard parsing. Subsequent semantically equivalent SQL always shares this cursor and will not repeat hard parsing. The new ECST (Extended Cursor Sharing Technique) introduced by Oracle11g may be hard parsed and created by Oracle based on factors such as selectivity of different semantically equivalent SQL. After ECST is used, Oracle will evaluate the selectivity of the predicate based on the bound variable value when parsing SQL statements. If the available child cursor is found in the soft parsing phase and the selectiator range of the child cursor covers the selectiators evaluated previously, the cursor will be shared; otherwise, the execution plan will be created, that is, hard parsing. (The selectitings range of child cursor is saved in the V $ SQL _cs_selecti.pdf view ). After a new execution plan is obtained through hard parsing, it is compared with the execution plan of the original child cursor. If the two are very different, a new child cursor is generated, and record the selectivity range (for example, if selectivity is 0.01, the range is 0.009 ~ 0.011. If the value is 0.2, the range may be 0.15 ~ 0.25 ). If the execution plan is the same, the original cursor is shared and the selectivity range of the cursor is adjusted. Oracle uses PCST by default because it is cost-effective and good enough. ECST is only valid for tables with severe data skew. ACS is used to determine under which circumstances the PCST is converted to ECST. When the same child cursor is used to execute semantically equivalent SQL multiple times, Oracle records the performance of each execution, such as cpu. The information is organized into buckets. Each bucket contains a range and the number of executions. For example, the cpu time ranges from ~ The number of 0.2s is 1, ranging from 10 s ~ 50 s of execution times is also 1. (Bucket information is stored in V $ SQL _CS_HISTOGRAM) if these statistical values differ greatly and the number of executions is similar, it means that the data skew is very severe and it is converted to ECST. When PCST is used, the cursor is in the monitored state, and the ECST is the aware state. That is, is_bind_aware of v $ SQL