Test ACS in Oracle11.2.0.1.0

Test ACS in Oracle11.2.0.1.0

In version 11.2.0.1.0, ACS (adaptiver cursor sharing) is tested as follows:

1. when binding is effective, CBO uses the binding variable. When the histogram is accurate, CBO compares the same SQL statement with the previous execution plan, if the first discovery is less efficient than the previous one, the second execution plan will be regenerated.

2. When binding to snoop fails, use the binding variable. CBO ignores the histogram completely.

SQL> select * from v $ version; BANNER implements Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit ProductionPL/SQL Release 11.2.0.1.0-ProductionCORE 11.2.0.1.0 ProductionTNS for Linux: version 11.2.0.1.0-ProductionNLSRTL Version 11.2.0.1.0-ProductionSQL> show parameter _ partition; name type value values ----------- ---------------------- _ Empty boolean TRUESQL> show parameter _ optimizer_extended_cursor_sharing; name type value values ----------- --------------------- _ optimizer_extended_cursor_sharing string values string SIMPLE -- Manufacture data drop table test purge; create table test (id number, name varchar2 (10), descri varchar2 (1000 )); beginfor I in 1 .. 100000 loopinsert into test values (I, '000000', lpad ('A', 100, 'B'); end loop; commit; end; /update test set name = '000000' where rownum = 1; commit; create index ind_name on TEST (name) nologging

;

-- Because dbms_stats is not used to collect the histogram accurately in version 11.2.0.1.0, use analyze

-- The collection histogram of dbms_stats.gather_table_stats in Oracle 11.2.0.1.0 is incorrect.

SQL> analyze table test compute statistics for table for columns name size 2; -- exec dbms_stats.gather_table_stats (user, 'test', cascade => true, method_opt => 'for all columns size skewonly'); -- Data Distribution SQL> select name, count (1) from test group by name; NAME COUNT (1) ---------- 100 99999200 1SQL> set autotrace traceonlySQL> select * from test where name = '000000'; 100 rows have been selected. Execution Plan -------------------------------------------------------- Plan hash value: 1357081020 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | latency | 0 | select statement | 99999 | 86M | 2664 (1) | 00:00:38 | * 1 | table access full | TEST | 99999 | 86M | 2664 (1) | 00:00:38 | identified by operation id ):---------------------------------------------------

1-filter ("NAME" = '20140901 ')
Statistics

When 1 recursive calls0 db block contains consistent gets1_physical reads0 redo bytes sent via SQL * Net to client73663 bytes encoded ed via SQL * Net from client6668 SQL * Net roundtrips to/from client0 sorts) 0 sorts (disk) 99999 rows processedSQL> select * from test where name = '000000'; Execution Plan ------------------------------------------------------ Plan hash value: 688048857 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 909 | 2 (0) | 00:00:01 | 1 | table access by index rowid | TEST | 1 | 909 | 2 (0) | 00:00:01 | * 2 | index range scan | IND_NAME | 1 | 1 (0) | 00:00:01 | identified Predicate Information (identified by operation id ):---------------------------------------------------

2-access ("NAME" = '000000 ')
Statistics

----------------------------------------------------------1 recursive calls0 db block gets3 consistent gets0 physical reads0 redo size1342 bytes sent via SQL*Net to client337 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processedSQL> set autotrace off

-- Use the BIND check condition. First, use the condition to check most of the data. You can see that both are full table scans.

-- It is valid to see the binding Snoop first.

SQL> show parameter _optim_peek_user_binds;NAME TYPE VALUE------------------------------------ ----------- ------------------------------_optim_peek_user_binds boolean TRUESQL> alter system flush shared_pool;SQL> alter system flush buffer_cache;SQL> var ccc varchar2(10);SQL> exec :ccc:='100';SQL> set autotrace trace statSQL> select * from test where name=:ccc;

Row 99999 has been selected.
Statistics

----------------------------------------------------------354 recursive calls0 db block gets13565 consistent gets6853 physical reads0 redo size1564422 bytes sent via SQL*Net to client73664 bytes received via SQL*Net from client6668 SQL*Net roundtrips to/from client7 sorts (memory)0 sorts (disk)99999 rows processedSQL> set autotrace offSQL> select hash_value, child_number from v$sqlwhere sql_text = 'select * from test where name=:ccc';HASH_VALUE CHILD_NUMBER---------- ------------1301684711 0SQL> select * from table(dbms_xplan.display_cursor(1301684711,0));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------HASH_VALUE 1301684711, child number 0--------------------------------------select * from test where name=:cccPlan hash value: 1357081020--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 2664 (100)| ||* 1 | TABLE ACCESS FULL| TEST | 99999 | 86M| 2664 (1)| 00:00:38 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------

1-filter ("NAME" =: CCC)

First execution: ccc: = '20140901'
 

SQL> exec: ccc: = '000000'; SQL> set autotrace trace statSQL> select * from test where name =: ccc; statistics 0 recursive calls0 db block gets6849 consistent gets0 physical reads0 redo bytes sent via SQL * Net to client338 bytes encoded ed via SQL * Net from client2 SQL * Net roundtrips to/from client0 sorts (memory) 0 sorts (disk) 1 rows processedSQL> set autotrace offSQL> select hash_value, child_number from v $ sqlwhere SQL _text = 'select * from test where name =: ccc '; HASH_VALUE CHILD_NUMBER ---------- ------------ 1301684711 0

Second execution: ccc: = '20140901'

SQL> exec: ccc: = '000000'; SQL> set autotrace trace statSQL> select * from test where name =: ccc; statistics 1 recursive calls0 db block gets3 consistent gets8 physical reads0 redo size1342 bytes sent via SQL * Net to client338 bytes received via SQL * Net from client2 SQL * Net roundtrips to/from client0 sorts (memory) 0 sorts (disk) 1 rows processedSQL> set autotrace offSQL> select hash_value, child_number from v $ sqlwhere SQL _text = 'select * from test where name =: ccc '; HASH_VALUE CHILD_NUMBER ---------- 1301684711 01301684711 1SQL> select * from table (dbms_xplan.display_cursor (1301684711,1); PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------HASH_VALUE 1301684711, child number 1 explain select * from test where name =: cccPlan hash value: 688048857 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement ||| 2 (100) | 1 | table access by index rowid | TEST | 1 | 909 | 2 (0) | 00:00:01 | * 2 | index range scan | IND_NAME | 1 | 1 (0) | 00:00:01 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 2-access ("NAME" =: CCC)

-- Use the binding condition to check a small amount of data.

SQL> alter system flush shared_pool; SQL> alter system flush buffer_cache; SQL> select hash_value, child_number from v $ sqlwhere SQL _text = 'select * from test where name =: ccc '; unselected rows: SQL> exec: ccc: = '000000'; SQL> set autotrace trace statSQL> select * from test where name =: ccc; statistics ------------------------------------------------------------ 354 recursive calls0 db block gets54 consistent gets27 physical reads0 Redo size1342 bytes sent via SQL * Net to client338 bytes encoded ed via SQL * Net from client2 SQL * Net roundtrips to/from client7 sorts (memory) 0 sorts (disk) 1 rows processedSQL> set autotrace offSQL> select hash_value, child_number from v $ sqlwhere SQL _text = 'select * from test where name =: ccc '; HASH_VALUE CHILD_NUMBER ---------- 1301684711 0SQL> select * from table (dbms_xplan.display_curso R (1301684711,0); PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------HASH_VALUE 1301684711, child number 0 ------------------------------------------ select * from test where name =: cccPlan hash value: 688048857 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | increment | 0 | select statement | 2 (100) | 1 | table access by index rowid | TEST | 1 | 909 | 2 (0) | 00:00:01 | * 2 | index range scan | IND_NAME | 1 | 1 (0) | 00:00:01 | identified Predicate Information (identified by operation Id): ------------------------------------------------- 2-access ("NAME" =: CCC) The first time you run ccc: = '000000' SQL> exec: ccc: = '000000 '; SQL> set autotrace trace statSQL> select * from test where name =: ccc; row 99999 has been selected. Statistics: 0 recursive calls0 db block gets20106 consistent gets6884 physical reads0 redo bytes sent via SQL * Net to bytes encoded ed via SQL * Net from client6668 SQL * Net roundtrips to/from client0 sorts (memory) 0 sorts (disk) 99999 rows processedSQL> set autotrace offSQL> select hash_value, child_number from v $ sqlwhere SQL _text = 'Select * from test where name =: ccc '; HASH_VALUE CHILD_NUMBER ---------- ------------ 1301684711 0 second execution ccc: = '000000' SQL> exec: ccc: = '000000 '; SQL> set autotrace trace statSQL> select * from test where name =: ccc; row 99999 has been selected. Statistics: 1 recursive calls0 db block contains consistent gets1_physical reads0 redo bytes sent via SQL * Net to client73664 bytes encoded ed via SQL * Net from client6668 SQL * Net roundtrips to/from client0 sorts (memory) 0 sorts (disk) 99999 rows processedSQL> set autotrace offSQL> select hash_value, child_number from v $ sqlwhere SQL _text = 'Select * from test where name =: ccc '; HASH_VALUE CHILD_NUMBER ---------- ------------ 1301684711 01301684711 1SQL> select * from table (dbms_xplan.display_cursor (1301684711,1); PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------HASH_VALUE 1301684711, child number 1 -------------------------------------- select * from test where name =: cccPlan hash Value: 1357081020 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 2664 (100) | * 1 | table access full | TEST | 99999 | 86M | 2664 (1) | 00:00:38 | ----------------------------------------------------------------- --------- Predicate Information (identified by operation id): ----------------------------------------------------- 1-filter ("NAME" =: CCC) -- do not use binding to snoop, first, use the condition SQL> alter session set "_ optim_peek_user_binds" = false; SQL> alter system flush shared_pool; SQL> alter system flush buffer_cache; SQL> select hash_value, child_number from v $ sqlwhere SQL _text = 'select * from test where name =: ccc '; SQL> exec: ccc: = '123'; SQL> set autotrace trace statSQL> select * from test where name =: ccc; statistics: Listen 354 recursive calls0 db block gets6899 consistent comment physical reads0 redo comment bytes sent via SQL * Net to client338 bytes encoded ed via SQL * Net from client2 SQL * Net roundtrips to/from client7 sorts (memory) 0 sorts (disk) 1 rows processedSQL> set autotrace OffSQL> select hash_value, child_number from v $ sqlwhere SQL _text = 'select * from test where name =: ccc '; HASH_VALUE CHILD_NUMBER ---------- 1301684711 0 -- see full table scan, correct is index scan SQL> select * from table (dbms_xplan.display_cursor (1301684711,0); PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------HASH_VALUE 1301684711, child number 0 ------------------ -------------------- Select * from test where name =: cccPlan hash value: 1357081020 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | latency | 0 | select statement | 2663 (100) | * 1 | table access full | TEST | 50000 | 43M | 2663 (1) | 00:00:38 |- Required Predicate Information (identified by operation id): --------------------------------------------------- 1-filter ("NAME" =: CCC) SQL> exec: ccc: = '2016 '; SQL> set autotrace trace statSQL> select * from test where name =: ccc; row 99999 has been selected. Statistics limit 0 recursive calls0 db block limit consistent gets0 physical reads0 redo limit bytes sent via SQL * Net to client73664 bytes encoded ed via SQL * Net from client6668 SQL * Net roundtrips to/from client0 sorts (memory) 0 sorts (disk) 99999 rows processedSQL> set autotrace offSQL> select hash_value, child_number from v $ sqlwhere SQL _text =' Select * from test where name =: ccc '; HASH_VALUE CHILD_NUMBER ---------- ------------ 1301684711 0SQL> exec: ccc: = '20140901 '; SQL> set autotrace trace statSQL> select * from test where name =: ccc; row 99999 has been selected. Statistics limit 0 recursive calls0 db block limit consistent gets0 physical reads0 redo limit bytes sent via SQL * Net to client73664 bytes encoded ed via SQL * Net from client6668 SQL * Net roundtrips to/from client0 sorts (memory) 0 sorts (disk) 99999 rows processedSQL> set autotrace offSQL> select hash_value, child_number from v $ sqlwhere SQL _text = 'select * from test where name =: ccc '; HASH_VALUE CHILD_NUMBER ---------- ------------ 1301684711 0