11.2.0.3 系统内部操作引起的硬解析

tom0732

版本：11.2.0.3.5
系统：aix 6.1

发现我们这内部一个11203 上的库存在大量硬解析，抓出SQL如下：

select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 546431 and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 544058 and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 536069 and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 535686 and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 357452 and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 538160 and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 544101 and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 536872 and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 547096 and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 544224 and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 536507 and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 540940 and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 528237 and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 536096 and bitand(FLAGS, 128)=0
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 533857 and bitand(FLAGS, 128)=0

select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 546384
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 538930
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 546289
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 541791
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 545138
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 544439
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 543133
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 545539
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 542137
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 530882
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 531936
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 538695
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 539172
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 547150
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 536386


查了一下，跟trunate 操作有关，游标共享参数设置为FORCE也不会自动绑定，其他人有没有类似情况？

Maclean Liu(刘相兵

这是系统 FBA的一些递归 SQL， 你确定 这些SQL耗费了大量的DB TIME吗？

tom0732

DB TIME消耗得倒不多，有点优化洁癖，每秒一二十个硬解析看得不太顺眼。:)