帮一网友解决问题
寂诚(116343798) 21:58:13
查询表空间的SQL 速度很慢

遇到一个系统查询表空间的剩余空间时，速度异常的慢，下面是10046的信息，大家遇到过类似的现象吗？是bug吗？
AIX：5.3.0.8
Oracle： 10.2.0.3
********************************************************************************

select tablespace_name,sum(bytes)/1048576 as free
from
dba_free_space group by tablespace_name


call     count       cpu    elapsed    disk      query    current    rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          0          0           0
Execute    1      0.00       0.00          0          0          0           0
Fetch        3     53.29   877.19   80125     512228   578      29
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5     53.29    877.21    80125     512228   578      29

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  db file sequential read                     77272        0.17        820.07
  control file sequential read                   12        0.00          0.00
  SQL*Net message from client                     3     1636.90       1636.90
********************************************************************************


寂诚(116343798) 21:59:28
select tablespace_name,sum(bytes)/1048576 as free
from dba_free_space group by tablespace_name；
花了近15分钟，而erp应用的速度基本正常
聆风听雨(76735763) 22:00:40
看看recycle bin是不是有很多删除对象？
寂诚(116343798) 22:03:12
select count(*) from recyclebin; 查询显示 75040
聆风听雨(76735763) 22:03:37
查询就和这个有关
寂诚(116343798) 22:03:46
select count(*) from sys.recyclebin$;

75223