help! 查询DBA_SEGMENTS突然变得很慢

feng_xin

最初由 cgh_chen 发布
[B]哇 chao_ping 真厉害，一下子就看出端倪。厉害 PPMP
都move了些什么系统表？ [/B]

慢是最近两个星期的事，查看了SYSTEM表空间，发现有个用户把他所有的表放到SYSTEM表空间，就MOVE了出来。 然后还是慢，就想整理一下SYSTEM表空间，就把所有的SYS用户的表 MOVE TABLESPACE SYSTEM， 把SYS用户的INDEX全部REBUILD了一遍，但这个表还是很慢。 而且奇怪的是，只有跟TABLE有关系的表慢，其他的表都不慢。

feng_xin

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     VIEW OF 'SYS_DBA_SEGS'
   3    2       UNION-ALL
   4    3         NESTED LOOPS
   5    4           NESTED LOOPS
   6    5             NESTED LOOPS
   7    6               NESTED LOOPS
   8    7                 NESTED LOOPS
   9    8                   VIEW OF 'SYS_OBJECTS'
  10    9                     UNION-ALL
  11   10                       TABLE ACCESS (FULL) OF 'TAB$'
  12   10                       TABLE ACCESS (FULL) OF 'TABPART$'
  13   10                       TABLE ACCESS (FULL) OF 'CLU$'
  14   10                       TABLE ACCESS (FULL) OF 'IND$'
  15   10                       TABLE ACCESS (FULL) OF 'INDPART$'
  16   10                       TABLE ACCESS (FULL) OF 'LOB$'
  17   10                       TABLE ACCESS (FULL) OF 'TABSUBPART$'
  18   10                       TABLE ACCESS (FULL) OF 'INDSUBPART$'
  19   10                       TABLE ACCESS (FULL) OF 'LOBFRAG$'
  20    8                   TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
  21   20                     INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
  22    7                 TABLE ACCESS (CLUSTER) OF 'SEG$'
  23   22                   INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (N
          ON-UNIQUE)

  24    6               INDEX (UNIQUE SCAN) OF 'I_FILE2' (UNIQUE)
  25    5             TABLE ACCESS (CLUSTER) OF 'TS$'
  26   25               INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
  27    4           TABLE ACCESS (CLUSTER) OF 'USER$'
  28   27             INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
  29    3         NESTED LOOPS
  30   29           NESTED LOOPS
  31   30             NESTED LOOPS
  32   31               NESTED LOOPS
  33   32                 TABLE ACCESS (FULL) OF 'UNDO$'
  34   32                 INDEX (UNIQUE SCAN) OF 'I_FILE2' (UNIQUE)
  35   31               TABLE ACCESS (CLUSTER) OF 'SEG$'
  36   35                 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON
          -UNIQUE)

  37   30             TABLE ACCESS (CLUSTER) OF 'TS$'
  38   37               INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
  39   29           TABLE ACCESS (CLUSTER) OF 'USER$'
  40   39             INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
  41    3         NESTED LOOPS
  42   41           NESTED LOOPS
  43   42             NESTED LOOPS
  44   43               TABLE ACCESS (FULL) OF 'FILE$'
  45   43               TABLE ACCESS (CLUSTER) OF 'SEG$'
  46   45                 INDEX (RANGE SCAN) OF 'I_FILE#_BLOCK#' (NON-
          UNIQUE)

  47   42             TABLE ACCESS (CLUSTER) OF 'TS$'
  48   47               INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
  49   41           TABLE ACCESS (CLUSTER) OF 'USER$'
  50   49             INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)

cgh_chen

Oracle  Metalink 的一片文章，大致说明了，慢的原因。
Data dictionary query very very slow

On my database v8.1.6 64 bits having 6506 objects (select * from DBA_OBJECT), I submit this query :

SELECT DISTINCT(a.segment_name),MAX(a.extent_id), b.max_extents,a.segment_type,a.tablespace_name,
a.owner, b.bytes, b.initial_extent, b.next_extent, b.min_extents, b.pct_increase
FROM dba_extents a, dba_segments b
WHERE a.segment_name=b.segment_name AND a.owner=b.owner AND (a.segment_name,a.owner) IN
(SELECT DISTINCT segment_name,owner FROM dba_extents
WHERE owner!='SYS' AND owner!='SYSTEM' AND owner!='DBSNMP'
AND owner!='SCOPEL' AND owner!='VALETTE')
GROUP BY a.segment_name, a.owner,a.segment_type,a.tablespace_name,
b.bytes, b.initial_extent, b.next_extent, b.min_extents, b.max_extents, b.pct_increase
HAVING MAX(a.extent_id) > b.max_extents * 0.90
ORDER BY a.segment_type,a.segment_name;

My problen is I have the respond after more than 4 hours.
My server (RS6000 Model H70) has 1Gb of RAM and under Server Manager the SHOW SGA command issue the folowing :
Total System Global Area 171628588 bytes
Fixed Size 108588 bytes
Variable Size 89067520 bytes
Database Buffers 81920000 bytes
Redo Buffers 532480 bytes

I don't use special parameters in my init<sid>.ora.
On my other platforms, I have 21 other database in v7.3.4.x, v8.0.6.x and v8.1.5.x and doesn't have any problem when I use the same query(respond after 15 seconds).
Does anyone have an idea about this phenomenon !

Thank you in advance.





--------------------------------------------------------------------------------

From: Oracle, Reem Munakash 24-Sep-00 17:54
Subject: Re : Data dictionary query very very slow


I filed bug 1415101 regarding this.

Reem Munakash
Electronic Support



--------------------------------------------------------------------------------

From: Oracle, Reem Munakash 03-Oct-00 23:47
Subject: Re : Data dictionary query very very slow


FYI -- the bug I filed, 1415101 has been closed as a duplicate of bug 1043147, which is closed as not a bug. The slow down is caused by hints embedded in the dictionary tables. These hints are necessary for other areas of Oracle. Developers suggest using the RULE hint as a way around this issue.

Reem Munakash
Electronic Support



--------------------------------------------------------------------------------

From: Jean-Pierre SCOPEL 06-Oct-00 10:05
Subject: Re : Data dictionary query very very slow


I have modified my query and then have inserted 2 hints /* +RULE */.
I have obtained my result afer 15 seconds.

Thank you for your help.

cgh_chen

我看了执行计划和我9iR2应该是一样的。
有可能基础表的数据量很大，只是原因之一。是不是有很多事务没有提交。
比较数据库实例之间的事务量，是不是有关。
瞎嚷嚷

feng_xin

在这之前做过create directionary，因为有个项目组要用到BFILE类型的数据，就建个表试试BFILE，后来就有人反映用PB打开TABLE的时候慢，跟这个有关系吗？ 应该没有关系的

dblife

这个...............基本上很难

feng_xin

up

cgh_chen

使用RULE查询数据库字典。
使用 /* +RULE */  提示还很慢嘛？
删除字典表的分析数据

feng_xin

使用 /* +RULE */ 提示还是很慢，表中没有分析信息，表没有分析过，我估计不是这方面的原因

richking

问一下chao_ping
你怎么看出来他move过sys table 阿
是不是你碰到过这种情况？？？？