直方图与ACS实例分析
本篇内容主要讲解"直方图与ACS实例分析"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"直方图与ACS实例分析"吧!
一般情况下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' ----------- 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>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>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>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实例分析"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
