优化器革命之- adaptive cursor sharing （二）

请先阅读优化器革命之- adaptive cursor sharing （一）http://blog.itpub.net/22034023/viewspace-1218980/上文提到了对于没有直方图有索引的列，...

cotchte0421

95人浏览 · 2014-07-18 12:57:07

cotchte0421 · 2014-07-18 12:57:07 发布

请先阅读优化器革命之- adaptive cursor sharing （一）
http://blog.itpub.net/22034023/viewspace-1218980/

上文提到了对于没有直方图有索引的列，即使存在数据倾斜也不会使用到ACS，但是有一个特殊情况，如果不做等值查询，也有可能在没有直方图的列上使用到ACS。
我们来看下一个实验：
1.创建一张表，500万的记录数，id字段根据rownum生成
2.在id字段上创建索引
3.收集统计信息，不收集直方图
4.清空shared_pool

create table t as select rownum id,a.* from dba_objects a ,dba_objects b where rownum<5000001;
create index i on t(id);
begin
dbms_stats.gather_table_stats(ownname =>'test',
tabname => 't',
no_invalidate => FALSE,
estimate_percent => 100,
force => true,
degree => 5,
method_opt => 'for all columns size 1',
cascade => true);
end;
/

alter system flush shared_pool;

上面的代码完成后，我们先来看下查询id>4999999的情况：
var a number;
exec :a :=4999999;
select count(object_id) from t where id > :a;
COUNT(OBJECT_ID)
----------------
1
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)

由于只返回一条记录，记录集非常小，ORACLE选择了索引扫描。

SELECT child_number, executions, buffer_gets, is_bind_sensitive,
is_bind_aware
FROM v$sql
WHERE sql_id='1vmttxn3jrww3';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS
------------ ---------- ----------- -- --
0 1 224 Y N

这个SQL也已经被标记为绑定敏感 is_bind_sensitive='Y'
SELECT hash_value, sql_id, child_number, bucket_id, COUNT
FROM v$sql_cs_histogram
WHERE sql_id='1vmttxn3jrww3'
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的情况下，这个时候要几乎返回整个表的数据：

exec :a :=1;
select count(object_id) from t where id > :a;
COUNT(OBJECT_ID)
----------------
4999999

SELECT child_number, executions, buffer_gets, is_bind_sensitive,
is_bind_aware
FROM v$sql
WHERE sql_id='1vmttxn3jrww3';
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS
------------ ---------- ----------- -- --
0 2 76540 Y N

没有生成新的执行计划，这个SQL还是只存在一个游标。
SELECT hash_value, sql_id, child_number, bucket_id, COUNT
FROM v$sql_cs_histogram
WHERE sql_id='1vmttxn3jrww3'
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的桶上。
再次执行：
select count(object_id) from t where id > :a;
COUNT(OBJECT_ID)
----------------
4999999
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 | | | | 14359 (100)| |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | TABLE ACCESS FULL| T | 4999K| 47M| 14359 (2)| 00:02:53 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("ID">:A)

我们看到索引扫描也就是child_number为0的执行计划已经失效了，已经产生了child_number为1的全表扫描的执行计划。

SELECT child_number, executions, buffer_gets, is_bind_sensitive,
is_bind_aware
FROM v$sql
WHERE sql_id='1vmttxn3jrww3';
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS
------------ ---------- ----------- -- --
0 2 76540 Y N
1 1 64624 Y Y

v$sql中也已经出现了child_number为1的子游标。 is_bind_sensitive和is_bind_aware都为Y。

SELECT hash_value, sql_id, child_number, bucket_id, COUNT
FROM v$sql_cs_histogram
WHERE sql_id='1vmttxn3jrww3'
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

v$sql_cs_histogram也已经产生了新增的3个记录。
至此我们证明了，在做不等值查询的时候，即使列上没有直方图信息，也可以使用到ACS，根据绑定值，智能的生成执行计划。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-1222110/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-1222110/

https://edu.csdn.net/learn/39067/627173?utm_source=2019755004

汇聚全球AI编程工具，助力开发者即刻编程。

更多推荐

2026脑机共生记忆革命：当GitHub Copilot遇见CRITIC模型，程序员如何重构认知架构

AI编程社区

2026 AI 元年｜智能体来了：Agent Native 正在取代 Copilot，定义下一代 AI 公司

AI编程社区

AI编程时代的变现新范式：拆解4个被低估的AI自动化现金流闭环

AI时代涌现出四种低门槛高回报的变现模式：1）AI成人睡前故事音频，通过自动化脚本生成+语音合成实现月入$77,000；2）GEO优化代理服务，利用AI优化内容成为大模型知识锚点，客单价达$3,000-$5,000/月；3）AI驱动的Print On Demand，零库存模式下利润率超80%；4）Pinterest联盟营销，通过AI生成视觉内容实现单月$15,000被动收入。这些模式共同特点是利用