SQL> create table t (id number, pad varchar2(100));
表已创建。
SQL> insert into t
2 SELECT rownum AS id, rpad('*',100,'*') AS pad
3 FROM dual
4 CONNECT BY rownum <= 1000;
已创建1000行。
SQL> ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);
表已更改。
SQL> SELECT count(id), count(DISTINCT id), min(id), max(id) FROM t;
COUNT(ID) COUNT(DISTINCTID) MIN(ID) MAX(ID)
---------- ----------------- ---------- ----------
1000 1000 1 1000
收集直方图等信息
SQL> BEGIN
2 dbms_stats.gather_table_stats(
3 ownname => user,
4 tabname => 't',
5 estimate_percent => 100,
6 method_opt => 'for all columns size 1'
7 );
8 END;
9 /
PL/SQL 过程已成功完成。
先不用绑定变量,看看执行计划
SQL> SELECT /*+ gather_plan_statistics*/count(pad) FROM t WHERE id < 990;
COUNT(PAD)
----------
989
COLUMN_VALUE
--------------------------------------------------------------------------------
SQL_ID 2tp9rk1nrtbw1, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics*/count(pad) FROM t WHERE id < 990
Plan hash value: 2966233522
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buff
ers |
COLUMN_VALUE
--------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 |
23 |
|* 2 | TABLE ACCESS FULL| T | 1 | 990 | 989 |00:00:00.01 | --全表扫描
23 |
--------------------------------------------------------------------------------
COLUMN_VALUE
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<990)
已选择18行。
SQL> SELECT /*+ gather_plan_statistics*/count(pad) FROM t WHERE id < 10;
COUNT(PAD)
----------
9
COLUMN_VALUE
--------------------------------------------------------------------------------
SQL_ID av58qh423qac2, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics*/count(pad) FROM t WHERE id < 10
Plan hash value: 4270555908
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Tim
e | Buffers |
COLUMN_VALUE
--------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00
.01 | 3 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 9 | 9 |00:00:00
.01 | 3 |
|* 3 | INDEX RANGE SCAN | T_PK | 1 | 9 | 9 |00:00:00 --用的索引,并且优化器的估计值都很准确
COLUMN_VALUE
--------------------------------------------------------------------------------
.01 | 2 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"<10)
已选择19行。
下面用绑定变量
SQL> variable id number
SQL> EXECUTE :id := 990;
PL/SQL 过程已成功完成。
SQL> SELECT /*+ gather_plan_statistics*/count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
989
COLUMN_VALUE
--------------------------------------------------------------------------------
SQL_ID av8xawvkkhsac, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics*/count(pad) FROM t WHERE id < :id
Plan hash value: 2966233522 --执行计划与不用绑定变量时(id<990)一样
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buff
ers |
COLUMN_VALUE
--------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 |
23 |
|* 2 | TABLE ACCESS FULL| T | 1 | 990 | 989 |00:00:00.01 |
23 |
--------------------------------------------------------------------------------
COLUMN_VALUE
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<:id>
<%}%>
已选择18行。
当id为10时
SQL> EXECUTE :id := 10;
PL/SQL 过程已成功完成。
SQL> SELECT /*+ gather_plan_statistics*/count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
9
COLUMN_VALUE
--------------------------------------------------------------------------------
SQL_ID av8xawvkkhsac, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics*/count(pad) FROM t WHERE id < :id
Plan hash value: 2966233522 --走的执行计划是上次的计划
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buff
ers |
COLUMN_VALUE
--------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 |
23 |
|* 2 | TABLE ACCESS FULL| T | 1 | 990 | 9 |00:00:00.01 | --估计值也错了,走的全表扫描
23 |
--------------------------------------------------------------------------------
COLUMN_VALUE
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<:id>
已选择18行。
把shared_pool清空
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
系统已更改。
SQL> EXECUTE :id := 10;
PL/SQL 过程已成功完成。
SQL> SELECT /*+ gather_plan_statistics*/count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
9
SQL> select * from table(exec_plan);
COLUMN_VALUE
--------------------------------------------------------------------------------
SQL_ID av8xawvkkhsac, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics*/count(pad) FROM t WHERE id < :id
Plan hash value: 4270555908
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Tim
e | Buffers |
COLUMN_VALUE
--------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00
.01 | 3 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 9 | 9 |00:00:00
.01 | 3 |
|* 3 | INDEX RANGE SCAN | T_PK | 1 | 9 | 9 |00:00:00
COLUMN_VALUE
--------------------------------------------------------------------------------
.01 | 2 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"<:id>
已选择19行。
执行计划、估计值都正确
SQL> EXECUTE :id := 990;
PL/SQL 过程已成功完成。
SQL> SELECT /*+ gather_plan_statistics*/count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
989
SQL> select * from table(exec_plan);
COLUMN_VALUE
--------------------------------------------------------------------------------
SQL_ID av8xawvkkhsac, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics*/count(pad) FROM t WHERE id < :id
Plan hash value: 4270555908 --执行计划又与上面的一样了
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Tim
e | Buffers |
COLUMN_VALUE
--------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00
.01 | 18 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 9 | 989 |00:00:00
.01 | 18 |
|* 3 | INDEX RANGE SCAN | T_PK | 1 | 9 | 989 |00:00:00
COLUMN_VALUE
--------------------------------------------------------------------------------
.01 | 3 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"<:id>
已选择19行。
使用了绑定变量,就会影响优化器的估计值,从而影响所走的查询计划。并且生成的执行计划会依赖第一次生成的查询计划,就是共享了一个子游标。
从flush shared_pool开始,子游标执行了两次,共享了一个执行计划。
SQL> select sql_id, sql_text, executions
2 from v$sqlarea
3 where sql_id = 'av8xawvkkhsac';
SQL_ID SQL_TEXT
EXECUTIONS
----------------- ---------------------------------------------- ----------
av8xawvkkhsac SELECT /*+ gather_plan_statistics*/count(pad) FROM t WHERE id < :id 2
SQL> select child_number, sql_text, executions from v$sql
2 where sql_id = 'av8xawvkkhsac';
CHILD_NUMBER SQL_TEXT
EXECUTIONS
------------ -------------------------------------------------------------------- -----------
0
SELECT /*+ gather_plan_statistics*/count(pad) FROM t WHERE id < :id 2
在OLAP系统中,解析的时间几乎可以忽略,使用绑定变量可能会影响到优化器生成一个高效的执行计划。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25361369/viewspace-712352/,如需转载,请注明出处,否则将追究法律责任。
上一篇:
基本的行列转换
下一篇:
显示游标与隐式游标对加锁的区别
![user_pic_default.png](http://blog.itpub.net/images/user_pic_default.png)
请登录后发表评论
登录
全部评论
<%=items[i].createtime%>
<%=items[i].content%>
<%if(items[i].items.items.length) { %>
<%for(var j=0;j
<%}%> <%if(items[i].items.total > 5) { %>
<%}%>
<%=items[i].items.items[j].createtime%>
<%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>: <%=items[i].items.items[j].content%>
还有<%=items[i].items.total-5%>条评论
) data-count=1 data-flag=true>点击查看
<%}%>
转载于:http://blog.itpub.net/25361369/viewspace-712352/