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

------------------------------------------------ …… …… Peeked Binds (identified by position): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------- 1 - :A (VARCHAR2(30), CSID=854): 'K' 从执行计划来看这里发生了PEEK 值是'K'被识别了，但是从Rows可以看出是总行数/基数算出来的6673 SQL> exec :a :='M'; PL/SQL procedure successfully completed. 这时候把绑定变量设为M，从上面的比例可以看出，M只有20条， SQL> select * from t where id1=:a;----执行 20 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 2 看到了version_count没有增加。 SQL> select SQL_ID,CHILD_NUMBER,IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE from v$sql where sql_id='dkatjhft2bfmv'; SQL_ID CHILD_NUMBER I I I ------------- ------------ - - - dkatjhft2bfmv 0 Y N Y 这里看到了子cursor只有1个。 child_number=0 再次查看执行计划 Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 10 (100)| | |* 1 | TABLE ACCESS FULL| T | 6673 | 40038 | 10 (0)| 00:00:01 | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / T@SEL$1 Peeked Binds (identified by position): PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------- 1 - :A (VARCHAR2(30), CSID=854): 'K' 依然还是K。 SQL>

select * from t where id1=:a;----再次执行 20 rows selected. select sql_id,child_number from v$sql where sql_id='dkatjhft2bfmv'; SQL_ID CHILD_NUMBER ------------- ------------ dkatjhft2bfmv 0 dkatjhft2bfmv 1 发现多出了一个子游标。两个子游标的rows返回的都是6673，不过peek那里值不同的。执行计划完全一致 --------------------------------------------------------------------------第一档实验结束，11G 如果没有直方图，会发生Adaptive Cursor Sharing，但执行计划完全一直----------- ----------------------------------------------------------------------现在开始收集直方图----------------------- SQL> alter system flush shared_pool; System altered. SQL> exec dbms_stats.gather_table_stats('SYS','T',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,METHOD_opt=>'for columns size 10 id1'); PL/SQL procedure successfully completed. ---------------看统计信息。FREQUENCY的直方图 NUM_BUCKETS 3个----- COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS ------------------ ---------- ----------- ----------- --------------- ----------- ID1 20020 3 .01 FREQUENCY 3 SQL> exec :a:='K'; ----10000行的 PL/SQL procedure successfu

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