资讯

精准传达 • 有效沟通

从品牌网站建设到网络营销策划，从策略到执行的一站式服务

网站建设 >

查看其它板块

直方图与ACS实例分析

本篇内容主要讲解“直方图与ACS实例分析”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“直方图与ACS实例分析”吧!

坚守“ 做人真诚 · 做事靠谱 · 口碑至上 · 高效敬业 ”的价值观，专业网站建设服务10余年为成都主动防护网小微创业公司专业提供成都定制网页设计营销网站建设商城网站建设手机网站建设小程序网站建设网站改版，从内容策划、视觉设计、底层架构、网页布局、功能开发迭代于一体的高端网站建设服务。

一般情况下ACS必须结合直方图一起使用才能发挥作用，我们看看列上的数据有倾斜，但是却不收集直方图情况下，ACS的表现会怎么样，紧接着还会举出一个特例。以下的代码删除了列status上的直方图。

SQL>begin                                                            

  2     dbms_stats.delete_column_stats(ownname       => 'test',       

  3                                    tabname       => 'test',       

  4                                    colname       => 'status',     

  5                                    col_stat_type => 'HISTOGRAM');

  6   end;                                                            

  7   /                                                               

 

PL/SQL procedure successfully completed.

 

SQL>alter system flush shared_pool;

 

System altered.

删除直方图是11G提供的功能，如果你的版本小于11G，可以重新收集表的统计信息不收集直方图。

SQL>exec :a :='Active'

PL/SQL procedure successfully completed.

SQL>select /*+ find_me */ count(name) from test where status=:a;

COUNT(NAME)

-----------

      49900

SQL>exec :a:='Inactive'

 

PL/SQL procedure successfully completed.

 

SQL>select /*+ find_me */ count(name) from test where status=:a;

 

COUNT(NAME)

-----------

        100

 

SQL>-- 直方图

SQL>SELECT   hash_value, sql_id, child_number, bucket_id, COUNT

  2       FROM v$sql_cs_histogram

  3      WHERE sql_id='a9cf9a1ky3bda'

  4   ORDER BY sql_id, child_number;

 

HASH_VALUE SQL_ID          CHILD_NUMBER  BUCKET_ID      COUNT

---------- --------------- ------------ ---------- ----------

1709288874 a9cf9a1ky3bda              0          1          1

1709288874 a9cf9a1ky3bda              0          0          1

1709288874 a9cf9a1ky3bda              0          2          0

 

SQL>select * from table(dbms_xplan.display_cursor('a9cf9a1ky3bda',null));

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------

SQL_ID  a9cf9a1ky3bda, child number 0

-------------------------------------

select /*+ find_me */ count(name) from test where status=:a

 

Plan hash value: 1950795681

 

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |       |       |    51 (100)|          |

|   1 |  SORT AGGREGATE    |      |     1 |    25 |            |          |

|*  2 |   TABLE ACCESS FULL| TEST | 25000 |   610K|    51   (2)| 00:00:01 |

---------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("STATUS"=:A)

 

 

我们看到v$sql_cs_histogram里的这个cursor的3个桶里已经有2个桶的count非0，说明优化器已经认识到第二次执行返回的记录数跟第一次大大不同了。按照我们之前所做的测试，如果列上有直方图，再次执行这个SQL，应该就会新产生一个游标了。我们来看看缺少直方图会怎么样：

SQL> select /*+ find_me */ count(name) from test where status=:a;

 

 

COUNT(NAME)

-----------

        100

 

SQL> -- 检查ACS状态

SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive,

  2          is_bind_aware

  3     FROM v$sql

  4    WHERE sql_id='a9cf9a1ky3bda';

 

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

------------ ---------- ----------- -- --

           0          2         463 Y  N

           1          1         210 Y  Y

 

SQL>

SQL>-- 直方图

SQL>SELECT   hash_value, sql_id, child_number, bucket_id, COUNT

  2       FROM v$sql_cs_histogram

  3      WHERE sql_id='a9cf9a1ky3bda'

  4   ORDER BY sql_id, child_number;

 

HASH_VALUE SQL_ID          CHILD_NUMBER  BUCKET_ID      COUNT

---------- --------------- ------------ ---------- ----------

1709288874 a9cf9a1ky3bda              0          1          1

1709288874 a9cf9a1ky3bda              0          0          1

1709288874 a9cf9a1ky3bda              0          2          0

1709288874 a9cf9a1ky3bda              1          1          0

1709288874 a9cf9a1ky3bda              1          0          1

1709288874 a9cf9a1ky3bda              1          2          0

 

6 rows selected.

 

SQL>

SQL>-- 统计信息

SQL>SELECT hash_value, sql_id, child_number,  executions,

  2           rows_processed

  3      FROM v$sql_cs_statistics

  4     WHERE sql_id='a9cf9a1ky3bda'

  5  ORDER BY sql_id, child_number;

 

HASH_VALUE SQL_ID          CHILD_NUMBER EXECUTIONS ROWS_PROCESSED

---------- --------------- ------------ ---------- --------------

1709288874 a9cf9a1ky3bda              0          1            101

1709288874 a9cf9a1ky3bda              1          1          49901

 

SQL>

SQL>-- 选择率

SQL>SELECT   hash_value, sql_id, child_number, predicate, range_id, low, high

  2      FROM v$sql_cs_selectivity

  3     WHERE sql_id='a9cf9a1ky3bda'

  4  ORDER BY sql_id, child_number;

 

HASH_VALUE SQL_ID          CHILD_NUMBER PREDICATE    RANGE_ID LOW        HIGH

---------- --------------- ------------ ---------- ---------- ---------- ----------

1709288874 a9cf9a1ky3bda              1 =A                  0 0.450000   0.550000

SQL>select * from table(dbms_xplan.display_cursor('a9cf9a1ky3bda',null));

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------

SQL_ID  a9cf9a1ky3bda, child number 0

-------------------------------------

select /*+ find_me */ count(name) from test where status=:a

 

Plan hash value: 1950795681

 

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |       |       |    51 (100)|          |

|   1 |  SORT AGGREGATE    |      |     1 |    25 |            |          |

|*  2 |   TABLE ACCESS FULL| TEST | 25000 |   610K|    51   (2)| 00:00:01 |

---------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("STATUS"=:A)

 

SQL_ID  a9cf9a1ky3bda, child number 1

-------------------------------------

select /*+ find_me */ count(name) from test where status=:a

 

Plan hash value: 1950795681

 

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |       |       |    51 (100)|          |

|   1 |  SORT AGGREGATE    |      |     1 |    25 |            |          |

|*  2 |   TABLE ACCESS FULL| TEST | 25000 |   610K|    51   (2)| 00:00:01 |

---------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("STATUS"=:A)

我们看到虽然新生成了一个子cursor，而且这个cursor的bind aware为Y，但是查看执行计划，发现新生成的child_number为1的执行计划也为全表扫描，而非索引扫描。其实优化器在发现这个cursor处理的行数发生巨变后，下次再次执行的话，就会窥探变量值，然后根据窥探到的值进行硬解析，但是由于不存在直方图，优化器认为索引扫描的代价太高，因此硬解析后依然还是生成了全表扫描的执行计划。我们看看索引扫描的COST值是多少：

SQL>select /*+ index(test) */ count(name) from test where status=:a;

 

COUNT(NAME)

-----------

        100

 

SQL>select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------

SQL_ID  88jwg2t11b237, child number 0

-------------------------------------

select /*+ index(test) */ count(name) from test where status=:a

 

Plan hash value: 2948918962

 

--------------------------------------------------------------------------------------------

| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |             |       |       |   218 (100)|          |

|   1 |  SORT AGGREGATE              |             |     1 |    25 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| TEST        | 25000 |   610K|   218   (1)| 00:00:03 |

|*  3 |    INDEX RANGE SCAN          | TEST_ID_IND | 25000 |       |    63   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("STATUS"=:A)

 

索引扫描的cost 为218已经超过了全表扫描的cost 51，因此由于缺少直方图即使重新硬解析也只能产生全表扫描的执行计划。优化器在尝试纠正错误，但是无耐给的信息不够，错误不能得到有效的纠正。

但是有特例，如果列上做的是非等值查询，即使没有直方图，依然可能会使用到ACS，我们看一个案例：

l 创建一张表，500万的记录数，id字段根据rownum生成

l 在id字段上创建索引

l 收集统计信息，不收集直方图

l 清空shared_pool

SQL>create table t as select rownum id,a.* from dba_objects a ,dba_objects b where rownum<5000001;

 

Table created.

 

SQL>create index i on t(id);

 

Index created.

 

SQL>begin

  2    dbms_stats.gather_table_stats(ownname          => 'test',

  3                                  tabname          => 't',

  4                                  no_invalidate    => FALSE,

  5                                  estimate_percent => 100,

  6                                  force            => true,

  7                                  degree           => 5,

  8                              method_opt       => 'for  all  columns size 1',

  9                                  cascade          => true);

 10  end;

 11  /

 

PL/SQL procedure successfully completed.

SQL>alter system flush shared_pool;

 

System altered.

 

SQL>var a number;

SQL>exec :a :=4999999;

 

PL/SQL procedure successfully completed.

 

SQL>select count(object_id) from t where id > :a;

 

COUNT(OBJECT_ID)

----------------

               1

 

SQL>select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------

SQL_ID  1vmttxn3jrww3, child number 0

-------------------------------------

select count(object_id) from t where id > :a

 

Plan hash value: 3694077449

 

-------------------------------------------------------------------------------------

| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |      |       |       |     4 (100)|          |

|   1 |  SORT AGGREGATE              |      |     1 |    10 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     1 |    10 |     4   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | I    |     1 |       |     3   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("ID">:A)

 

先查询了id大于4999999的，由于只返回一条记录，记录集非常小，ORACLE选择了索引扫描。我们看看ACS相关视图的表现：

SQL> -- 检查ACS状态

SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive,

  2          is_bind_aware

  3     FROM v$sql

  4    WHERE sql_id='1vmttxn3jrww3';

 

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

------------ ---------- ----------- -- --

           0          1          48 Y  N

 

SQL>

SQL>-- 直方图

SQL>SELECT   hash_value, sql_id, child_number, bucket_id, COUNT

  2       FROM v$sql_cs_histogram

  3      WHERE sql_id='1vmttxn3jrww3'

  4   ORDER BY sql_id, child_number;

 

HASH_VALUE SQL_ID          CHILD_NUMBER  BUCKET_ID      COUNT

---------- --------------- ------------ ---------- ----------

 119272323 1vmttxn3jrww3              0          0          1

 119272323 1vmttxn3jrww3              0          2          0

 119272323 1vmttxn3jrww3              0          1          0

 

由于处理的结果集较小，执行的统计被列入到了bucket 0。我们继续看看查询id>1的情况下，这个时候要几乎返回整个表的数据：

SQL>exec :a :=1;

select count(object_id) from t where id > :a;

 

PL/SQL procedure successfully completed.

 

SQL>

 

COUNT(OBJECT_ID)

----------------

         4999999

   
SQL> -- 检查ACS状态

SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive,

  2          is_bind_aware

  3     FROM v$sql

  4    WHERE sql_id='1vmttxn3jrww3';

 

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

------------ ---------- ----------- -- --

           0          2       76425 Y  N

 

SQL>

SQL>-- 直方图

SQL>SELECT   hash_value, sql_id, child_number, bucket_id, COUNT

  2       FROM v$sql_cs_histogram

  3      WHERE sql_id='1vmttxn3jrww3'

  4   ORDER BY sql_id, child_number;

 

HASH_VALUE SQL_ID          CHILD_NUMBER  BUCKET_ID      COUNT

---------- --------------- ------------ ---------- ----------

 119272323 1vmttxn3jrww3              0          0          1

 119272323 1vmttxn3jrww3              0          2          1

 119272323 1vmttxn3jrww3              0          1          0

v$sql_cs_histogram已经捕获到本次执行的SQL处理的结果集已经跟第一次执行大大不同，执行的统计已经被列入到了bucket_id为2的桶上。再次执行：

SQL>select count(object_id) from t where id > :a;

COUNT(OBJECT_ID)

----------------

         4999999

 

SQL>select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------

SQL_ID  1vmttxn3jrww3, child number 1

-------------------------------------

select count(object_id) from t where id > :a

 

Plan hash value: 2966233522

 

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |       |       | 14373 (100)|          |

|   1 |  SORT AGGREGATE    |      |     1 |    10 |            |          |

|*  2 |   TABLE ACCESS FULL| T    |  4999K|    47M| 14373   (2)| 00:02:53 |

---------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("ID">:A)

 

再次执行后，已经产生出了全表扫描的执行计划了，因为再次执行，优化器会去窥探绑定变量的值做硬解析，优化器重新评估索引扫描和全表扫描的cost后选择了全表扫描，下面的代码给出了ACS相关视图的变化和索引扫描的cost。


SQL> -- 检查ACS状态

SQL>SELECT child_number, executions, buffer_gets, is_bind_sensitive,

  2          is_bind_aware

  3     FROM v$sql

  4    WHERE sql_id='1vmttxn3jrww3';

 

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS

------------ ---------- ----------- -- --

           0          2       76425 Y  N

           1          1       64685 Y  Y

SQL>-- 直方图

SQL>SELECT   hash_value, sql_id, child_number, bucket_id, COUNT

  2       FROM v$sql_cs_histogram

  3      WHERE sql_id='1vmttxn3jrww3'

  4   ORDER BY sql_id, child_number;

 

HASH_VALUE SQL_ID          CHILD_NUMBER  BUCKET_ID      COUNT

---------- --------------- ------------ ---------- ----------

 119272323 1vmttxn3jrww3              0          1          0

 119272323 1vmttxn3jrww3              0          0          1

 119272323 1vmttxn3jrww3              0          2          1

 119272323 1vmttxn3jrww3              1          1          0

 119272323 1vmttxn3jrww3              1          0          0

 119272323 1vmttxn3jrww3              1          2          1

 

SQL>select /*+ index(t) */count(object_id) from t where id > :a;

select

COUNT(OBJECT_ID)

----------------

         4999999

 

SQL>select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------

SQL_ID  51qy01unwm5r0, child number 0

-------------------------------------

select /*+ index(t) */count(object_id) from t where id > :a

 

Plan hash value: 3694077449

 

-------------------------------------------------------------------------------------

| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |      |       |       | 76652 (100)|          |

|   1 |  SORT AGGREGATE              |      |     1 |    10 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| T    |  4999K|    47M| 76652   (1)| 00:15:20 |

|*  3 |    INDEX RANGE SCAN          | I    |  4999K|       | 11792   (1)| 00:02:22 |

-------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("ID">:A)

 

 

v$sql中也已经出现了child_number为1的子游标。 is_bind_sensitive和is_bind_aware都为Y。v$sql_cs_histogram中也产生出了新的3行记录。说明ACS已经发挥作用产生了新的游标，而且执行计划也非常优秀。上面没有直方图的第一个做等值查询的例子，虽然ACS也发挥了作用，但是由于缺少直方图，并没有产生出优秀的执行计划。

从上面的两个例子可以看出，所谓ACS发挥作用，只不过是给优化器一个机会，让其根据具体的绑定变量的值重新硬解析，但是至于硬解析出来的执行计划优不优秀，要看统计信息的完整度、准确度以及你查询的谓词是做的何种查询。

到此，相信大家对“直方图与ACS实例分析”有了更深的了解，不妨来实际操作一番吧！这里是创新互联网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！


名称栏目：直方图与ACS实例分析
分享地址：http://cdkjz.cn/article/pjihhs.html
返回首页 了解更多建站资讯

相关资讯

多年建站经验

多一份参考，总有益处

联系快上网，免费获得专属《策划方案》及报价

咨询相关问题或预约面谈，可以通过以下方式与我们联系

大客户专线   成都：13518219792   座机：028-86922220

在线咨询 提交需求
友情链接 交换友情链接
成都服装定制宜宾网站设计成都网站制作公司温江网站建设自适应网页设计网站建设公司企业网站设计光华服务器托管遂宁做网站升施印刷

成都网站建设公司地址：成都市青羊区太升南路288号锦天国际A座10层 建设咨询028-86922220

成都快上网科技有限公司-四川网站建设设计公司 | 蜀ICP备19037934号 Copyright 2020,ALL Rights Reserved cdkjz.cn | 成都网站建设 | © Copyright 2020版权所有.

专家团队为您提供成都网站建设,成都网站设计,成都品牌网站设计,成都营销型网站制作等服务,成都建网站就找快上网！ | 成都网站建设哪家好？ | 网站建设地图