Oracle11.2.0.1.0下ACS测试 - 数据库编程

NDEX ROWID| TEST | 1 | 909 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_NAME | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("NAME"='200')
统计信息

----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1342 bytes sent via SQL*Net to client
337 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off

--使用绑定窥探，先使用查大部分数据的条件，可以看到两个都是全表扫描

--先看到绑定窥探是有效的

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

已选择99999行。
统计信息

----------------------------------------------------------
354 recursive calls
0 db block gets
13565 consistent gets
6853 physical reads
0 redo size
1564422 bytes sent via SQL*Net to client
73664 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
99999 rows processed
SQL> set autotrace off
SQL> select hash_value, child_number from v$sql
where sql_text = 'select * from test where name=:ccc';
HASH_VALUE CHILD_NUMBER
---------- ------------
1301684711 0
SQL> select * from table(dbms_xplan.display_cursor(1301684711,0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
HASH_VALUE 1301684711, child number 0
--------------------------------------
select * from test where name=:ccc
Plan 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)

第一次执行:ccc:='200'
?

SQL> exec :ccc:='200';
SQL> set autotrace trace stat
SQL> select * from test where name=:ccc;
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
6849 consistent gets
0 physical reads
0 redo size
1342 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off

SQL> select hash_value, child_number from v$sql
where sql_text = 'select * from test where name=:ccc';
HASH_VALUE CHILD_NUMBER
---------- ------------
1301684711 0

第二次执行:ccc:='200'

SQL> exec :ccc:='200';
SQL> set autotrace trace stat
SQL> select * from test where name=:ccc;
统计信息
------------------------

Oracle11.2.0.1.0下ACS测试(二)