绑定变量窥测-白红宇

环境说明】

oracle版本：11.2.0

【一】进行测试环境的配置

1. 创建表test

create table test as select rownum id

from all_objects a

;

2. 创建索引

CREATE INDEX EKPJ."

TEST~ID

" ON EKPJ.TEST

(ID)

NOLOGGING

3. 进行统计信息的收集

Execute

DBMS_STATS

GATHER_TABLE_STATS

(

'SCOTT'

'TEST'

);

4. 进行字段ID分布情况的收集

SELECT count

(

count

(

DISTINCT id

min

(

max

(

)

FROM TEST

;

COUNT(ID) COUNT(DISTINCT ID) MIN(ID) MAX(ID)

1069299 83539 1

83539

5. 查看执行计划的SQL

alter session

set

STATISTICS_LEVEL

ALL

----不设置无法获得

ROWS

等信息；

select

from table

(

dbms_xplan

DISPLAY_CURSOR

(

null

'ALLSTATS'

));

【二】进行测试

2.1 测试当id值小于4的时候，这个时候结果集只有4，应该走索引

点击(此处)折叠或打开

select count(object_name) from test where id <= :id; #此处ID输入4

3.SQL_ID 6jq05u5z8k9bn, child number 0

4.-------------------------------------

5.select count(object_name) from test where id <= :id

7.Plan hash value: 885655847

9.------------------------------------------------------------------------------------------------

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |

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

| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 | 1 |

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 4 | 1 |

|* 2 | INDEX RANGE SCAN| TEST~ID | 1 | 534K| 512 |00:00:00.01 | 4 | 1 |

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

11.Predicate Information (identified by operation id):

12.---------------------------------------------------

13. - access("ID"<=:ID)

14. 总结：此处使用的是走索引的执行计划，效果很好；

2.2 测试当id值小于83000

的时候，几乎是整个表的数据了，这个时候走全表扫描会更快

点击(此处)折叠或打开

select count(object_name) from test where id <= :id; #此处ID输入83000

2.2.SQL_ID 6jq05u5z8k9bn, child number 1

3.3.-------------------------------------

4.4.select count(object_name) from test where id <= :id

5.5.

6.6.-----------------------------------------------------------------------------------------------------------

7.| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |

8.-----------------------------------------------------------------------------------------------------------

9.| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:18.57 | 10M| 172K|

10.| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:18.57 | 10M| 172K|

11.| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 534K| 10M|00:00:17.46 | 10M| 172K|

12.|* 3 | INDEX RANGE SCAN | TEST~ID | 1 | 96237 | 10M|00:00:01.97 | 22664 | 2886 |

13.-----------------------------------------------------------------------------------------------------------

14.7.Predicate Information (identified by operation id):

15.---------------------------------------------------

17. 3 - access("ID"<=:ID)

18.8. 当前显示还是走索引

2.3 测试在ID为83000时候，走全表扫描的速度（通过hint来强制走全表扫描）

点击(此处)折叠或打开

SQL_ID duyq44cmbt2hm, child number 0

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

select /*+FULL(TEST)*/ count(object_type) from test where id <= :id

2.select count(object_type) from test where id <= 83000 #或直接用数字 Plan hash value: 1950795681

3.----------------------------------------------------------------------------------------------

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |

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

| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:02.88 | 158K| 158K|

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:02.88 | 158K| 158K|

|* 2 | TABLE ACCESS FULL| TEST | 1 | 534K| 10M|00:00:01.92 | 158K| 158K|

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

Predicate Information (identified by operation id):

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

2 - filter("ID"<=:ID)

显示为2秒88，完成；

总结：

绑定变量产生的执行计划会以第一次的执行计划为标准，当第一次输入一个值的时候，会对这个值进行一个窥探，从而产生一个执行计划；

在OLAP系统并发查询少，但是每次查询大量数据的情况下，可以通过取消绑定变量来提升系统的运行效率；