11G ACS特性探究实验 - 数据库编程

lly completed. SQL> select * from t where id1=:a; 10000 rows selected. 执行了一遍。 SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select * from t%'; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS -------------------------------------------------- ------------- ------------- ---------- select * from t where id1=:a dkatjhft2bfmv 1 1 再看执行计划 Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 10 (100)| | |* 1 | TABLE ACCESS FULL| T | 10000 | 60000 | 10 (0)| 00:00:01 | Peeked Binds (identified by position): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------- 1 - :A (VARCHAR2(30), CSID=854): 'K' 返回的行数是10000 精确的。 ------------------现在开始更改绑定变量的值 SQL> exec :a:='M'; ---20行 PL/SQL procedure successfully completed. SQL> select * from t where id1=:a; 20 rows selected. SQL> select sql_id,child_number from v$sql where sql_id='dkatjhft2bfmv'; SQL_ID CHILD_NUMBER ------------- ------------ dkatjhft2bfmv 0 还是走了原来的老计划再来执行一次 SQL> select * from t where id1=:a; 20 rows selected. 出现了一个新计划…… SQL> select sql_id,child_number from v$sql where sql_id='dkatjhft2bfmv'; SQL_ID CHILD_NUMBER ------------- ------------ dkatjhft2bfmv 0 dkatjhft2bfmv 1 查看新计划的执行计划 SQL> select * from table(dbms_xplan.display_cursor('dkatjhft2bfmv',1,'advanced')); ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T | 20 | 120 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T_ID1 | 20 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- 偷窥到了M Peeked Binds (identified by position): -------------------------------------- 1 - :A (VARCHAR2(30), CSID=854): 'M' -------再次更改绑定变量的值， SQL>

exec :a:='A'; SQL> select * from t where id1=:a; 10000 rows selected. SQL> select sql_id,child_number from v$sql where sql_id='dkatjhft2bfmv'; SQL_ID CHILD_NUMBER ------------- ------------ dkatjhft2bfmv 0 dkatjhft2bfmv 1 dkatjhft2bfmv 2 又多了一个子cursor 查看执行计划 SQL> select * from table(dbms_xplan.display_cursor('dkatjhft2bfmv',2,'advanced')); -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 10 (100)| | |* 1 | TABLE ACCESS FULL| T | 10000 | 60000 | 10 (0)| 00:00:01 | -------------------------------------------------------------------------- Peeked Binds (identified by position): -------------------------------------- 1 - :A (VARCHAR2(30), CSID=854): 'A' 又偷窥出了A 结论：再有柱状图的时候，会发生ACS 在统计信息准确的时候，返回的结果是准确的（如果esti

11G ACS特性探究实验(四)