从Peeked Binds看DDL和DCL语句对执行计划的影响

大多数OLTP型的系统都会采用代码绑定变量的方式来减轻对share pool的压力。因为,频繁的硬解析势必会占用过多的CPU，加重shared pool的压力，造成大量的碎片。而稳定的执行计划也是我们不可忽视的方面.在实际中,往往一些不经意的操作,会造成执行计划的重新解析,比如发布DDL语句,发布授权信息等.

下面用跟踪Peeked Binds的方式,验证一下这个过程.

SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
4 rows selected

SQL> var empno number
SQL> var id number
SQL> exec :id:=7788;
PL/SQL 过程已成功完成。
SQL> select * from emp where empno=:id;
EMPNO ENAME JOB MGR HIREDATE  SAL COMM DEPTNO
---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'peeked_binds'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID ftzypu5fkftks, child number 0
-------------------------------------
select * from emp where empno=:id

Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :ID (NUMBER): 7788

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=:ID)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement

可以看到,由于sql是第一次执行,以empno=7788来生成执行计划.然后我们将变量改为10000.

SQL> exec :id:=10000;
PL/SQL 过程已成功完成。
SQL> select * from emp where empno=:id;
未选定行
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'peeked_binds'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID ftzypu5fkftks, child number 0
-------------------------------------
select * from emp where empno=:id

Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 87 | 3 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :ID (NUMBER): 7788

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=:ID)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement

可以看到Peeked Binds依然为ID (NUMBER): 7788,也就是说sql第二次执行的时候,得以共享,避免了硬解析.

发布一个ddl语句,我们在emp上增加一个字段:

SQL> alter table emp add ts number;

继续发布语句查询,依然沿用原来的变量数值。

SQL> select * from emp where empno=:id;
未选定行

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'peeked_binds'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID ftzypu5fkftks, child number 0
-------------------------------------
select * from emp where empno=:id

Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 100 | 3 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :ID (NUMBER): 10000

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=:ID)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement

此时,可以看到Peeked Binds变为 ID (NUMBER): 10000,也就说明sql已经被重新解析,执行计划被重新生成.

同样,一些DCL(grant/revoke)的发布,同样会造成相应object的属性更改,从而能使得依赖该object的sql不能共享,需要重新生成执行计划.

转自：http://www.easyora.net/blog/change_cost_peeked_bind.html