oracle 执行计划变更

oracle 的执行计划经常会遭遇绑定变量 偷窥问题，导致执行计划变更，带来性能问题

同事发在内网的一个帖子转这里了。

感谢分享。

SQL执行计划变更导致数据库负载突升,让我们措手不及,有没有好的处理办法呢?

让我们来查查这个语句的历史执行信息,看看是否发生变化,何时发生了变化.

如果发生了变化,找出以前的执行计划,与当前的执行计划进行对比,有什么不同.

oracle 10G中我们可以通过下面的三个视图查询到语句的历史执行信息.

DBA_HIST_SQL_PLAN    DBA_HIST_SQLSTAT    DBA_HIST_SNAPSHOT

我在生产库sunha5上做个测试,查询it商城的sql:

1.查询当前在活动的会话获得SQL_ID值

1	SYS AS SYSDBA at v880 >select USERNAME,SQL_ID from v$session where status='ACTIVE' AND SCHEMA#>0;

2

3	USERNAME                       SQL_ID

4	------------------------------ -------------

5	CYP_NW_APP                     dxx8pvcttf5qv

6	PRODUCT_PUB                    5c53uzwqswhtb

我们可以获得一个sql_id='dxx8pvcttf5qv'

2.获得此sql_id对应的sql语句

1	select sql_id,sql_fulltext from v$sql where sql_id='dxx8pvcttf5qv';

从查询结果sql_fulltext,我们可以获得sql语句.

3.查询此sql_id历史执行信息

01	select a.INSTANCE_NUMBER,

02	a.snap_id,

03

a.sql_id,

04	a.plan_hash_value,

05	b.begin_interval_time

06	from dba_hist_sqlstat a, dba_hist_snapshot b

07	where sql_id = 'dxx8pvcttf5qv'

08	and a.snap_id = b.snap_id

09	order by instance_number, snap_id;

10

11	INSTANCE_NUMBER    SNAP_ID SQL_ID        PLAN_HASH_VALUE BEGIN_INTERVAL_TIME

12	--------------- ---------- ------------- --------------- ---------------------------------------------------------------------------

13	1      17370 dxx8pvcttf5qv      2125777269 24-6月 -10 11.00.44.900 上午

14	1      17371 dxx8pvcttf5qv      2125777269 24-6月 -10 12.00.46.879 下午

15	1      17372 dxx8pvcttf5qv      2125777269 24-6月 -10 01.00.48.962 下午

16	1      17373 dxx8pvcttf5qv      1904478120 24-6月 -10 02.00.50.872 下午

17	1      17374 dxx8pvcttf5qv      1904478120 24-6月 -10 03.00.52.840 下午

18	1      17375 dxx8pvcttf5qv      1904478120 24-6月 -10 04.00.54.780 下午

截取了一段查询信息,可以看到sql历史执行信息中,在6月24日时执行计划有变更.

我们具体查查看变更前后的执行计划有什么区别.

4.查询变更前后的执行计划

01	select sql_id,

02	plan_hash_value,

03

id,

04	operation,

05

options,

06	object_owner,

07	object_name,

08

depth,

09

cost,

10

timestamp

11	from DBA_HIST_SQL_PLAN

12	where sql_id = 'dxx8pvcttf5qv'

13	and plan_hash_value in (1904478120,2125777269);

14

15	SQL_ID        PLAN_HASH_VALUE  ID OPERATION            OPTIONS    OBJECT_OWN OBJECT_NAME     DEPTH   COST TIMESTAMP

16	dxx8pvcttf5qv 1904478120 11 TABLE ACCESS BY INDEX ROWID CYP_NW_APP ENT_PRODUCT 11 14780 2010-06-24 14-14-36

17	dxx8pvcttf5qv 1904478120 12 INDEX RANGE SCAN CYP_NW_APP IDX_ENT_PRODUCT_2 12 14767 2010-06-24 14-14-36

18	dxx8pvcttf5qv 1904478120 13 TABLE ACCESS BY INDEX ROWID CYP_NW_APP ENT_USER 9 1 2010-06-24 14-14-36

19	dxx8pvcttf5qv 1904478120 14 INDEX UNIQUE SCAN CYP_NW_APP SYS_C00124956 10 0 2010-06-24 14-14-36

20

21	SQL_ID        PLAN_HASH_VALUE  ID OPERATION            OPTIONS    OBJECT_OWN OBJECT_NAME     DEPTH   COST TIMESTAMP

22	dxx8pvcttf5qv 2125777269 11 TABLE ACCESS FULL CYP_NW_APP ENT_PRODUCT 11 21329 2010-06-17 03-14-15

23	dxx8pvcttf5qv 2125777269 12 TABLE ACCESS BY INDEX ROWID CYP_NW_APP ENT_USER 9 1 2010-06-17 03-14-15

24	dxx8pvcttf5qv 2125777269 13 INDEX UNIQUE SCAN CYP_NW_APP SYS_C00124956 10 0 2010-06-17 03-14-15

我们从查询结果中可以看到变更前后执行计划除了一个索引不同外,其他都一样
plan_hash_value = 1904478120  ---此执行计划多走一个索引IDX_ENT_PRODUCT_2
plan_hash_value = 2125777269

5.根据执行计划的不同点查找原因

1	select * from dba_objects where object_name='IDX_ENT_PRODUCT_2';

从索引'IDX_ENT_PRODUCT_2'的信息中看到, last_ddl_time='2010-06-24 14-01-56' ,应该是这个原因导致执行计划的改变.

我上面是举个例子,当数据库突然有异常sql时,排除程序更新的原因,我们可以按照这个思路去查询异常sql的执行计划是否变更.

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

转载于:http://blog.itpub.net/133735/viewspace-707229/