[20141116]11g ACS的一些问题1.txt
--11G下Adaptive Cursor Sharing简称ACS能很好的解决绑定变量窥视的带来的问题，昨天看了2篇blog
https://hourim.wordpress.com/2014/11/06/bind-aware-part-i/
https://hourim.wordpress.com/2014/11/08/bind-aware-part-ii/
--我以前也写过一篇blog,链接如下：
http://blog.itpub.net/267265/viewspace-721817/
--如果绑定变量值出现倾斜，如果执行次数很大，很难改变执行计划，当时提到最好的方法是加入提示/*+ BIND_AWARE */ .
--比如查询status=:b1 ,status='N'的值很少，执行计划走索引更佳，而status='Y'的值很多，执行计划走全表扫描更佳。
--但是由于查询以status='N'次数居多，查询计划几乎很难改变。导致当status='Y'的执行计划依旧是走索引。
--作者最后给出一个结论，或者是规律：
查询v$sql_cs_histogram视图，如果ROWS_PROCESSED存在某种规律：
0 < ROWS_PROCESSED <= 1000 --> COUNT of BUCKET_ID 0 will be incremented
1000 < ROWS_PROCESSED <= 1e6 --> COUNT of BUCKET_ID 1 will be incremented
ROWS_PROCESSED > 1e6 --> COUNT of BUCKET_ID 2 will be incremented
--自己做一个简单验证看看是否正确。
1.建立测试环境:
create table t ( id number,status varchar2(1));
insert into t select rownum,'a' from dual connect by level<=999;
insert into t select rownum,'b' from dual connect by level<=1000;
insert into t select rownum,'c' from dual connect by level<=1001;
insert into t select rownum,'d' from dual connect by level<=1e6-1;
insert into t select rownum,'e' from dual connect by level<=1e6;
insert into t select rownum,'f' from dual connect by level<=1e6+1;
SCOTT@test> select count(*),status from t group by status order by status;
COUNT(*) S
---------- -
999 a
1000 b
1001 c
999999 d
1000000 e
1000001 f
6 rows selected.
SCOTT@test> exec sys.dbms_stats.gather_table_stats ( OwnName => 'SCOTT',TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 for columns status size 254',Cascade => True ,No_Invalidate => false);
PL/SQL procedure successfully completed.
2.测试1：
column address noprint
alter system flush SHARED_POOL;
variable x varchar2(1);
exec :x :='a';
select count(id) from t where status = :x;
COUNT(ID)
----------
999
SCOTT@test> select * from v$sql_cs_statistics where sql_id='fh1fb0c3ppv62';
HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
123399362 fh1fb0c3ppv62 0 29220167 Y 1 1000 5092 0
SCOTT@test> select * from v$sql_cs_histogram where sql_id='fh1fb0c3ppv62';
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- ------------- ------------ ---------- ----------
123399362 fh1fb0c3ppv62 0 0 1 =>增加
123399362 fh1fb0c3ppv62 0 1 0
123399362 fh1fb0c3ppv62 0 2 0
-- 实际上返会999.
-- ROWS_PROCESSED=1000,BUCKET_ID=0 ,count++.
3.测试2：
column address noprint
alter system flush SHARED_POOL;
exec :x :='b';
select count(id) from t where status = :x;
COUNT(ID)
----------
1000
SCOTT@test> select * from v$sql_cs_statistics where sql_id='fh1fb0c3ppv62';
HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
123399362 fh1fb0c3ppv62 0 2672171179 Y 1 1001 5092 0
SCOTT@test> select * from v$sql_cs_histogram where sql_id='fh1fb0c3ppv62';
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- ------------- ------------ ---------- ----------
123399362 fh1fb0c3ppv62 0 0 0
123399362 fh1fb0c3ppv62 0 1 1 =>增加
123399362 fh1fb0c3ppv62 0 2 0
-- 实际上返会1000
-- ROWS_PROCESSED=1001,BUCKET_ID=1 ,count++.
4.测试3：
column address noprint
alter system flush SHARED_POOL;
exec :x :='c';
select count(id) from t where status = :x;
COUNT(ID)
----------
1001
SCOTT@test> select * from v$sql_cs_statistics where sql_id='fh1fb0c3ppv62';
HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
123399362 fh1fb0c3ppv62 0 1422755360 Y 1 1002 5092 0
SCOTT@test> select * from v$sql_cs_histogram where sql_id='fh1fb0c3ppv62';
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- ------------- ------------ ---------- ----------
123399362 fh1fb0c3ppv62 0 0 0
123399362 fh1fb0c3ppv62 0 1 1 =>增加
123399362 fh1fb0c3ppv62 0 2 0
-- ROWS_PROCESSED=1002,BUCKET_ID=1 ,count++.
5.测试4：
column address noprint
alter system flush SHARED_POOL;
exec :x :='d';
select count(id) from t where status = :x;
COUNT(ID)
----------
999999
SCOTT@test> select * from v$sql_cs_statistics where sql_id='fh1fb0c3ppv62';
HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
123399362 fh1fb0c3ppv62 0 1869963691 Y 1 1000000 5092 0
SCOTT@test> select * from v$sql_cs_histogram where sql_id='fh1fb0c3ppv62';
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- ------------- ------------ ---------- ----------
123399362 fh1fb0c3ppv62 0 0 0
123399362 fh1fb0c3ppv62 0 1 1 =>增加
123399362 fh1fb0c3ppv62 0 2 0
-- ROWS_PROCESSED=1000000,BUCKET_ID=1 ,count++.
6.测试5：
column address noprint
alter system flush SHARED_POOL;
exec :x :='e';
select count(id) from t where status = :x;
COUNT(ID)
----------
1000000
SCOTT@test> select * from v$sql_cs_statistics where sql_id='fh1fb0c3ppv62';
HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
123399362 fh1fb0c3ppv62 0 562278933 Y 1 1000001 5092 0
SCOTT@test> select * from v$sql_cs_histogram where sql_id='fh1fb0c3ppv62';
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- ------------- ------------ ---------- ----------
123399362 fh1fb0c3ppv62 0 0 0
123399362 fh1fb0c3ppv62 0 1 0
123399362 fh1fb0c3ppv62 0 2 1 =>增加
-- ROWS_PROCESSED=1000001,BUCKET_ID=2 ,count++.
7.测试6：
column address noprint
alter system flush SHARED_POOL;
exec :x :='f';
select count(id) from t where status = :x;
COUNT(ID)
----------
1000001
SCOTT@test> select * from v$sql_cs_statistics where sql_id='fh1fb0c3ppv62';
HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
123399362 fh1fb0c3ppv62 0 3847398969 Y 1 1000002 5092 0
SCOTT@test> select * from v$sql_cs_histogram where sql_id='fh1fb0c3ppv62';
HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- ------------- ------------ ---------- ----------
123399362 fh1fb0c3ppv62 0 0 0
123399362 fh1fb0c3ppv62 0 1 0
123399362 fh1fb0c3ppv62 0 2 1 =>增加
-- ROWS_PROCESSED=1000002,BUCKET_ID=2 ,count++.
--总结：
-- ROWS_PROCESSED=1000,BUCKET_ID=0 ,count++.
-- ROWS_PROCESSED=1001,BUCKET_ID=1 ,count++.
-- ROWS_PROCESSED=1002,BUCKET_ID=1 ,count++.
-- ROWS_PROCESSED=1000000,BUCKET_ID=1 ,count++.
-- ROWS_PROCESSED=1000001,BUCKET_ID=2 ,count++.
-- ROWS_PROCESSED=1000002,BUCKET_ID=2 ,count++.
根据以上测试，结论如下：
0 < ROWS_PROCESSED <= 1000 --> COUNT of BUCKET_ID 0 will be incremented
1000 < ROWS_PROCESSED <= 1e6 --> COUNT of BUCKET_ID 1 will be incremented
ROWS_PROCESSED > 1e6 --> COUNT of BUCKET_ID 2 will be incremented
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-1336242/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
