10.1.0.2 的dbsnmp用户的session一直在db file sequenal read

10.1.0.2 的dbsnmp用户的session一直在db file sequenal read

sql语句为：

insert into mgmt_db_size_gtt select tablespace_name,NVL(sum(bytes)/1048576, 0) sz from sys.dba_free_space group by tablespace_name

原来视图本身有问题。

以下是10.1.0.2下视图部分内容：
...
select /*+ ordered use_nl(u) use_nl(fi) */
ts.name, fi.file#, u.ktfbuebno,
u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = u.ktfbuesegtsn
and u.ktfbuesegtsn = fi.ts#
and u.ktfbuesegfno = fi.relfile#
and u.ktfbuesegtsn = rb.ts#
and u.ktfbuesegfno = rb.file#
and u.ktfbuesegbno = rb.block# and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
...

以下是10.2.0.2下视图部分内容：
...
union all
select /*+ ordered use_nl(u) use_nl(fi) */
ts.name, fi.file#, u.ktfbuebno,
u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = rb.ts#
and rb.ts# = fi.ts#
and rb.file# = fi.relfile#
and u.ktfbuesegtsn = rb.ts#
and u.ktfbuesegfno = rb.file#
and u.ktfbuesegbno = rb.block# and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
...

导致两个视图执行计划不同，第一种执行计划出现偏差。

[@more@]

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

转载于:http://blog.itpub.net/112417/viewspace-977668/