Chinaunix首页 | 论坛 | 博客
  • 博客访问: 526252
  • 博文数量: 100
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 1172
  • 用 户 组: 普通用户
  • 注册时间: 2016-09-22 09:50
个人简介

一个守望数据库的老菜鸟

文章分类

全部博文(100)

文章存档

2019年(21)

2018年(17)

2017年(38)

2016年(24)

我的朋友

分类: Oracle

2016-10-16 13:06:50


博客文章除注明转载外,均为原创。转载请注明出处。
本文链接地址:http://blog.chinaunix.net/uid-31396856-id-5753417.html



某库数据库检查表空间大小的时候非常慢
检查其其执行计划:
SQL> set timing on
SQL> set autot on
SQL> set linesize 120
SQL> /


Elapsed: 00:01:45.55

Execution Plan
----------------------------------------------------------
Plan hash value: 2471964280
----------------------------------------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      | |     3 |   217 |   309  (15)| 00:00:04 |
|   1 |  SORT ORDER BY      | |     3 |   217 |   308  (14)| 00:00:04 |
|   2 |   UNION-ALL      | | | |      | |
|*  3 |    HASH JOIN OUTER      | |     2 |   146 |   305  (14)| 00:00:04 |
|   4 |     VIEW      | |     2 |    86 |     5  (20)| 00:00:01 |
|   5 |      HASH GROUP BY      | |     2 |    68 |     5  (20)| 00:00:01 |
|   6 |       VIEW      | DBA_DATA_FILES |     2 |    68 |     4   (0)| 00:00:01 |
|   7 |        UNION-ALL      | | | |      | |
|   8 | NESTED LOOPS      | |     1 |   356 |     2   (0)| 00:00:01 |
|   9 | NESTED LOOPS      | |     1 |   341 |     1   (0)| 00:00:01 |
|  10 |  NESTED LOOPS      | |     1 |   328 |     1   (0)| 00:00:01 |
|* 11 |   FIXED TABLE FULL      | X$KCCFN |     1 |   310 |     0   (0)| 00:00:01 |
|* 12 |   TABLE ACCESS BY INDEX ROWID| FILE$ |     1 |    18 |     1   (0)| 00:00:01 |
|* 13 |    INDEX UNIQUE SCAN      | I_FILE1 |     1 | |     0   (0)| 00:00:01 |
|* 14 |  FIXED TABLE FIXED INDEX     | X$KCCFE (ind:1)  |     6 |    78 |     0   (0)| 00:00:01 |
|  15 | TABLE ACCESS CLUSTER      | TS$ |     1 |    15 |     1   (0)| 00:00:01 |
|* 16 |  INDEX UNIQUE SCAN      | I_TS# |     1 | |     0   (0)| 00:00:01 |
|  17 | NESTED LOOPS      | |     1 |   399 |     2   (0)| 00:00:01 |
|  18 | NESTED LOOPS      | |     1 |   384 |     1   (0)| 00:00:01 |
|  19 |  NESTED LOOPS      | |     1 |   371 |     1   (0)| 00:00:01 |
|  20 |   NESTED LOOPS      | |     1 |   362 |     0   (0)| 00:00:01 |
|* 21 |    FIXED TABLE FULL      | X$KCCFN |     1 |   310 |     0   (0)| 00:00:01 |
|* 22 |    FIXED TABLE FIXED INDEX   | X$KTFBHC (ind:1) |     1 |    52 |     0   (0)| 00:00:01 |
|* 23 |   TABLE ACCESS BY INDEX ROWID| FILE$ |     1 |     9 |     1   (0)| 00:00:01 |
|* 24 |    INDEX UNIQUE SCAN      | I_FILE1 |     1 | |     0   (0)| 00:00:01 |
|* 25 |  FIXED TABLE FIXED INDEX     | X$KCCFE (ind:1)  |     6 |    78 |     0   (0)| 00:00:01 |
|  26 | TABLE ACCESS CLUSTER      | TS$ |     1 |    15 |     1   (0)| 00:00:01 |
|* 27 |  INDEX UNIQUE SCAN      | I_TS# |     1 | |     0   (0)| 00:00:01 |
|  28 |     VIEW      | |    10 |   300 |   300  (14)| 00:00:04 |
|  29 |      HASH GROUP BY      | |    10 |   210 |   300  (14)| 00:00:04 |
|  30 |       VIEW      | DBA_FREE_SPACE |  2074 | 43554 |   299  (14)| 00:00:04 |
|  31 |        UNION-ALL      | | | |      | |
|  32 | NESTED LOOPS      | |     1 |    64 |     4   (0)| 00:00:01 |
|  33 | NESTED LOOPS      | |     1 |    58 |     4   (0)| 00:00:01 |
|  34 |  TABLE ACCESS FULL      | FET$ |     1 |    39 |     4   (0)| 00:00:01 |
|* 35 |  TABLE ACCESS CLUSTER      | TS$ |     1 |    19 |     0   (0)| 00:00:01 |
|* 36 |   INDEX UNIQUE SCAN      | I_TS# |     1 | |     0   (0)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN      | I_FILE2 |     1 |     6 |     0   (0)| 00:00:01 |
|  38 | NESTED LOOPS      | |    87 |  6090 |     5   (0)| 00:00:01 |
|  39 | NESTED LOOPS      | |    87 |  5568 |     5   (0)| 00:00:01 |
|* 40 |  TABLE ACCESS FULL      | TS$ |    10 |   250 |     5   (0)| 00:00:01 |
|* 41 |  FIXED TABLE FIXED INDEX     | X$KTFBFE (ind:1) |     9 |   351 |     0   (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN      | I_FILE2 |     1 |     6 |     0   (0)| 00:00:01 |
|* 43 | HASH JOIN      | |  1985 |   207K|   162  (21)| 00:00:02 |
|* 44 | TABLE ACCESS FULL      | TS$ |    10 |   250 |     5   (0)| 00:00:01 |
|* 45 | HASH JOIN      | |  2080 |   166K|   157  (22)| 00:00:02 |
|  46 |  INDEX FULL SCAN      | I_FILE2 |    18 |   108 |     1   (0)| 00:00:01 |
|* 47 |  HASH JOIN      | | 35177 |  2610K|   156  (22)| 00:00:02 |
|  48 |   TABLE ACCESS FULL      | RECYCLEBIN$ | 35177 |   377K|   123   (1)| 00:00:02 |
|  49 |   FIXED TABLE FULL      | X$KTFBUE |   100K|  6347K|    32 (100)| 00:00:01 |
|  50 | NESTED LOOPS      | |     1 |    88 |   127   (4)| 00:00:02 |
|  51 | NESTED LOOPS      | |     1 |    82 |   127   (4)| 00:00:02 |
|  52 |  NESTED LOOPS      | |     1 |    63 |   127   (4)| 00:00:02 |
|  53 |   TABLE ACCESS FULL      | RECYCLEBIN$ | 35177 |   377K|   123   (1)| 00:00:02 |
|  54 |   TABLE ACCESS CLUSTER       | UET$ |     1 |    52 |     0   (0)| 00:00:01 |
|* 55 |    INDEX UNIQUE SCAN      | I_FILE#_BLOCK# |     1 | |     0   (0)| 00:00:01 |
|* 56 |  TABLE ACCESS CLUSTER      | TS$ |     1 |    19 |     0   (0)| 00:00:01 |
|* 57 |   INDEX UNIQUE SCAN      | I_TS# |     1 | |     0   (0)| 00:00:01 |
|* 58 | INDEX UNIQUE SCAN      | I_FILE2 |     1 |     6 |     0   (0)| 00:00:01 |
|  59 |    NESTED LOOPS OUTER      | |     1 |    71 |     3  (34)| 00:00:01 |
|  60 |     VIEW      | |     1 |    43 |     2  (50)| 00:00:01 |
|  61 |      HASH GROUP BY      | |     1 |   416 |     2  (50)| 00:00:01 |
|  62 |       NESTED LOOPS      | |     1 |   416 |     1   (0)| 00:00:01 |
|  63 |        NESTED LOOPS      | |     1 |   364 |     1   (0)| 00:00:01 |
|  64 | NESTED LOOPS      | |     1 |   349 |     0   (0)| 00:00:01 |
|* 65 | FIXED TABLE FULL      | X$KCCFN |     1 |   297 |     0   (0)| 00:00:01 |
|* 66 | FIXED TABLE FIXED INDEX      | X$KTFTHC (ind:1) |     1 |    52 |     0   (0)| 00:00:01 |
|  67 | TABLE ACCESS CLUSTER      | TS$ |     1 |    15 |     1   (0)| 00:00:01 |
|* 68 | INDEX UNIQUE SCAN      | I_TS# |     1 | |     0   (0)| 00:00:01 |
|* 69 |        FIXED TABLE FIXED INDEX      | X$KCCTF (ind:1)  |     1 |    52 |     0   (0)| 00:00:01 |
|  70 |     VIEW PUSHED PREDICATE      | |     1 |    28 |     1   (0)| 00:00:01 |
|  71 |      SORT GROUP BY      | |     1 |    90 |     1   (0)| 00:00:01 |
|  72 |       NESTED LOOPS      | |     1 |    90 |     1   (0)| 00:00:01 |
|* 73 |        TABLE ACCESS BY INDEX ROWID    | TS$ |     1 |    25 |     1   (0)| 00:00:01 |
|* 74 | INDEX UNIQUE SCAN      | I_TS1 |     1 | |     0   (0)| 00:00:01 |
|* 75 |        FIXED TABLE FIXED INDEX      | X$KTFTHC (ind:2) |     1 |    65 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   3 - access("D"."TABLESPACE_NAME"="F"."TABLESPACE_NAME"(+))
  11 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND
     BITAND("FNFLG",4)<>4)
  12 - filter("F"."SPARE1" IS NULL)
  13 - access("FNFNO"="F"."FILE#")
  14 - filter("FE"."FENUM"="F"."FILE#")
  16 - access("F"."TS#"="TS"."TS#")
  21 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND
     BITAND("FNFLG",4)<>4)
  22 - filter("FNFNO"="HC"."KTFBHCAFNO")
  23 - filter("F"."SPARE1" IS NOT NULL)
  24 - access("FNFNO"="F"."FILE#")
  25 - filter("FE"."FENUM"="F"."FILE#")
  27 - access("HC"."KTFBHCTSN"="TS"."TS#")
  35 - filter("TS"."BITMAPPED"=0)
  36 - access("TS"."TS#"="F"."TS#")
  37 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
  40 - filter("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 OR
     "TS"."ONLINE$"=4))
  41 - filter("TS"."TS#"="F"."KTFBFETSN")
  42 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
  43 - access("TS"."TS#"="RB"."TS#")
  44 - filter("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 OR
     "TS"."ONLINE$"=4))
  45 - access("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")
  47 - access("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND
     "U"."KTFBUESEGBNO"="RB"."BLOCK#")
  55 - access("U"."TS#"="RB"."TS#" AND "U"."SEGFILE#"="RB"."FILE#" AND
     "U"."SEGBLOCK#"="RB"."BLOCK#")
  56 - filter("TS"."BITMAPPED"=0)
  57 - access("TS"."TS#"="U"."TS#")
  58 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
  65 - filter("V"."FNNAM" IS NOT NULL AND "V"."FNTYP"=7)
  66 - filter("V"."FNFNO"="HC"."KTFTHCTFNO")
  68 - access("HC"."KTFTHCTSN"="TS"."TS#")
  69 - filter("TF"."TFDUP"<>0 AND BITAND("TF"."TFSTA",32)<>32 AND "V"."FNFNO"="TF"."TFNUM" AND
     "TF"."TFFNH"="V"."FNNUM")
  73 - filter("TS"."CONTENTS$"=1 AND "TS"."BITMAPPED"<>0 AND "TS"."ONLINE$"=1)
  74 - access("TS"."NAME"="D"."TABLESPACE_NAME")
  75 - filter("HC"."KTFTHCCVAL"=0 AND "HC"."INST_ID"=USERENV('INSTANCE') AND
     "TS"."TS#"="HC"."KTFTHCTSN")

Statistics  --统计信息
----------------------------------------------------------
      51732  recursive calls
211  db block gets
     273537  consistent gets
      48565  physical reads
 0  redo size
       1301  bytes sent via SQL*Net to client
520  bytes received via SQL*Net from client
 2  SQL*Net roundtrips to/from client
 2  sorts (memory)
 0  sorts (disk)
11  rows processed

观察发现执行计划id=53及其前后,发现对RECYCLEBIN$进行全表扫描后作为驱动表进行nested loops
同事id=48,也对RECYCLEBIN$进行全表扫描
于是检查回收站数据库对象
SQL> select count(*) from sys.recyclebin$;
  COUNT(*)
----------
     38499

发现RECYCLEBIN$里记录太多,在做NESTED LOOPS的时候,耗费太大,RECYCLEBIN$记录多,是由于drop的表过多引起,可用PURGE DBA_RECYCLEBIN清空回收站后解决。
但是,根据业务的需求,只能逐步清理
SQL> select owner#,count(*) from sys.recyclebin$ group by owner#;
    OWNER#   COUNT(*)
---------- ----------
34 3140
40 35359

SQL> select user_id,username,default_tablespace from dba_users where  USER_ID in ('34','40');
   USER_ID USERNAME  DEFAULT_TABLESPACE
---------- ------------------------------ ------------------------------
34 YXJS_APP  YXJS_APP
40 XYFILMZC     XYFILMZC

后处理完成后,问题解决。
oracle 关于Recyclebin使用原则:
1. 使用现有的表空间的未使用空间
    2. 如果没有了空闲空间,则检查回收站,对于回收站的对象按照先进先出的原则,对于最先删除的对象,
       oracle在空间不足之时会最先从回收站删除以满足新分配空间的需求
    3. 如果回收站也没有对象可以清理,则检查表空间是否自扩展,如果自扩展则扩展表空间,然后分配新空
       间
    4.如果表空间非自扩展,或者已经不能自扩展(到达最大限制),则直接报表空间不足错误,程序终止
---the end
阅读(1405) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~