[20141116]11g ACS的一些问题1.txt

[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    COUNT of BUCKET_ID  0 will be incremented
1000 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 levelinsert into t select rownum,'b' from dual connect by levelinsert into t select rownum,'c' from dual connect by level

insert into t select rownum,'d' from dual connect by levelinsert into t select rownum,'e' from dual connect by levelinsert into t select rownum,'f' from dual connect by level

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    COUNT of BUCKET_ID  0 will be incremented
1000 COUNT of BUCKET_ID  1 will be incremented
       ROWS_PROCESSED > 1e6    --> COUNT of BUCKET_ID  2 will be incremented

时间： 2024-09-06 06:19:18

[20141116]11g ACS的一些问题1.txt的相关文章

[20141213]11g ACS的一些问题4.txt

[20141213]11g ACS的一些问题4.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.itp

[20141213]11g ACS的一些问题3.txt

[20141213]11g ACS的一些问题3.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.itp

[20170824]11G备库启用DRCP连接.txt

[20170824]11G备库启用DRCP连接.txt --//参考链接: http://blog.itpub.net/267265/viewspace-2099397/ blogs.oracle.com/database4cn/adg%e5%a4%87%e5%ba%93%e7%9a%84drcp%e8%bf%9e%e6%8e%a5%e6%8a%a5%e9%94%99oci-21500%e8%a7%a3%e5%86%b3%e4%b8%80%e4%be%8b 1.测试环境: SYS@bookdg>

[20170726]11G 12c expand sql text 2.txt

[20170726]11G 12c expand sql text 2.txt --//原来写的脚本只能分别在11g,12c单独使用.上午花一点点时间.把两者整合起来. --//讨论链接:http://www.itpub.net/thread-2088981-1-1.html --//感谢solomon_007的指点,通过建立动态sql语句来实现.链接:http://blog.itpub.net/267265/viewspace-2141010/ --//qqjue给出建立建立类似c的宏来实现C

[20160517]11G HugePage的使用问题2.txt

[20160517]11G HugePage的使用问题2.txt --昨天测试了HUGEPAGE的使用问题,自己也看了一些文档,从11.2.0.2,加入了参数use_large_pages. SYS@book> @ &r/ver1 PORT_STRING         VERSION        BANNER ------------------- -------------- -------------------------------------------------------

[20150705]11G表统计信息与PUBLISH.txt

[20150705]11G表统计信息与PUBLISH.txt --11G表统计信息可以先不发布(在PUBLISH参数的控制下),等检测合适再发布. --确实参数optimizer_use_pending_statistics为false,可以在session级别打开为true,检测统计是否有用. SYS@test> @hide optimizer_use_pending_statistics NAME                              DESCRIPTION       

[20150910]11G ADG与延迟日志应用.txt

[20150910]11G ADG与延迟日志应用.txt --11G ADG是一个非常好的特性,它可以一边应用日志,一边提供查询,前一阵子跟别人讨论ADG 是否可以与延迟日志应用结合起来,既 --提供只读查询,又延迟日志应用,自己从来没有测试过,今天测试看看. --实际上一种可能就是在dg上打开flashback,这样在出现问题时闪回到出问题的时间点.但是这个是回滚,而我延迟应用是前进. 1.测试环境: SCOTT@test> @ver BANNER ----------------------

[20150710]11G谓词推入问题2.txt

[20150710]11G谓词推入问题2.txt --生产系统遇到一个sql语句的问题. --生产系统的sql语句比较复杂,做一个简化的例子来说明问题.来说明自己优化遇到的困惑. --昨天看来别人的回复,加提示 /*+ push_pred(v_tallx)*/,无效.实际上如果仔细看我的帖子 --http://blog.itpub.net/267265/viewspace-1724554/, 可以发现T2表的id是字符类型,存在隐式转换,虽然我定义了函数索引,视乎 --对于这种情况谓词推入存在问

[20131027]11G的内部视图X$DBGALERTEXT.txt

[20131027]11G的内部视图X$DBGALERTEXT.txt 链接:http://www.askmaclean.com/archives/11g%E6%96%B0%E7%89%B9%E6%80%A7xdbgalertext%E4%B8%80%E4%B8%AA%E5%BE%88%E9%85%B7%E7%9A%84%E5%86%85%E9%83%A8%E8%A7%86%E5%9B%BE.html 以前我们看alert*.log文件,一般直接进入目录,直接查看.当然也可以通过外部表来访问al