help! 查询DBA_SEGMENTS突然变得很慢

richto

What is you Oracle version ?

feng_xin

oracle815, compaq tru64 unix

richto

Try this one:

select count(*)
  from
       sys.seg$

feng_xin

SQL>  select count(*)
  2   from
  3   sys.seg$
  4   ;

COUNT(*)
---------
    19123

real: 60

richto

So, if you don't need anything other then count(*), that is the answer.  But this internal table may be changed from version to version,  I think it is ok for this table, at least it is called seg$.

feng_xin

L>  select count(*)
2   from
3   sys.seg$
4   ;

OUNT(*)
-------
  19123

eal: 60
L> select count(*) from dba_segments;

OUNT(*)
-------
  19123

eal: 72610
L>  select count(*)
2   from
3  sys.file$;

OUNT(*)
-------
     26

eal: 60
L> select count(*) from sys.tab$;

OUNT(*)
-------
  10681

eal: 220
L> select count(*) from sys.tabpart$;

OUNT(*)
-------
      0

eal: 0
L> select count(*) from sys.clu$;

OUNT(*)
-------
     10

eal: 170
L>  select count(*) from sys.ind$;

OUNT(*)
-------
   8447

eal: 220
L> select count(*) from sys.indpart$;

OUNT(*)
-------
      0

eal: 50
L> select count(*) from sys.lob$;

OUNT(*)
-------
     22

eal: 160
L> select count(*) from sys.tABSUBPART$;

OUNT(*)
-------
      0

eal: 50
L>  select count(*) from sys.indSUBPART$;

OUNT(*)
-------
      0

eal: 50
L> select count(*) from sys.lobfrag$;

OUNT(*)
-------
      0

eal: 50
L> select  count(*) from sys.obj$;

OUNT(*)
-------
  39649

eal: 50
L>  select  count(*) from sys.ts$;

OUNT(*)
-------
     12

eal: 50
L> select count(*) from sys.user$;

OUNT(*)
-------
    121

eal: 50
L> select count(*) from sys.undo$;

OUNT(*)
-------
      6

eal: 50
L>  select count(*) from sys.file$;

OUNT(*)
-------
     26

eal: 50

feng_xin

越来越不明白了，基表看起来都没问题，执行计划也没变，为何速度如此慢？

cookye

把数据库全库导出export，然后重新建库，再import。

feng_xin

最初由 cookye 发布
[B]把数据库全库导出export，然后重新建库，再import。 [/B]

不太可能，数据量太大

whuijun

高度关注！