ITPub博客

首页 > 数据库 > Oracle > 查看oracle数据库表空间使用情况 非常慢!

查看oracle数据库表空间使用情况 非常慢!

原创 Oracle 作者:shytodear 时间:2015-11-30 21:54:07 0 删除 编辑
1.现象
查看数据库表空间使用情况 非常慢!

2.诊断过程

2.1查看执行计划如下:
SQL> select * from table(dbms_xplan.display_cursor('0n0f0p6jm7tyf','','advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0n0f0p6jm7tyf, child number 0
-------------------------------------
select a.tablespace_name, a.total_mb, b.free_mb,
to_char(100*b.free_mb/a.total_mb, '999.99') free_pct, b.free_exts,
b.max_mb, b.min_mb from  (select tablespace_name,
to_char(sum(bytes)/1048576, '99999999.99') total_mb   from
dba_data_files group by tablespace_name) a,  (select tablespace_name,
to_char(sum(bytes)/1048576, '99999999.99') free_mb,    count(*)
free_exts, to_char(max(bytes)/1048576, '999999') max_mb,
to_char(min(bytes)/1048576, '999999') min_mb   from dba_free_space
group by tablespace_name) b where a.tablespace_name=b.tablespace_name(+)
 order by free_pct


Execution Plan
----------------------------------------------------------
Plan hash value: 2720929131

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

| Id  | Operation                                     | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time
   |    TQ  |IN-OUT| PQ Distrib |


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


|   0 | SELECT STATEMENT                              |                    |    17 |  1615 |       |  1539   (2)| 00:00:
01 |        |      |            |


|   1 |  SORT ORDER BY                                |                    |    17 |  1615 |       |  1539   (2)| 00:00:
01 |        |      |            |
|*  2 |   HASH JOIN OUTER                             |                    |    17 |  1615 |       |  1538   (2)| 00:00:
。。。。
|* 22 |           HASH JOIN                           |                    |  7377 |   785K|       |  1010   (3)| 00:00:
01 |        |      |            |

|* 23 |            TABLE ACCESS FULL                  | TS$                |    12 |   312 |       |     6   (0)| 00:00:
01 |        |      |            |


|* 24 |            HASH JOIN                          |                    |  7650 |   620K|  3344K|  1004   (3)| 00:00:
01 |        |      |            |

|* 25 |             TABLE ACCESS FULL                 | RECYCLEBIN$        |   142K|  1668K|       |   579   (1)| 00:00:
01 |        |      |            |

|* 26 |             HASH JOIN                         |                    | 61538 |  4266K|       |    20  (95)| 00:00:
01 |        |      |            |

|* 27 |              INDEX FULL SCAN                  | I_FILE2            |    30 |   180 |       |     1   (0)| 00:00:
01 |        |      |            |

|  28 |              FIXED TABLE FULL                 | X$KTFBUE           |   100K|  6347K|       |    19 (100)| 00:00:
01 |        |      |            |


|  29 |           NESTED LOOPS                        |                    |     1 |    90 |       |   495   (1)| 00:00:
01 |        |      |            |

|  30 |            NESTED LOOPS                       |                    | 28329 |    90 |       |   495   (1)| 00:00:
01 |        |      |            |

|  31 |             NESTED LOOPS                      |                    |     1 |    78 |       |     8   (0)| 00:00:
01 |        |      |            |

|  32 |              NESTED LOOPS                     |                    |     1 |    72 |       |     8   (0)| 00:00:
01 |        |      |            |

|* 33 |               TABLE ACCESS FULL               | TS$                |     1 |    20 |       |     6   (0)| 00:00:
01 |        |      |            |


|  34 |               TABLE ACCESS CLUSTER            | UET$               |     1 |    52 |       |     2   (0)| 00:00:
01 |        |      |            |


|* 35 |                INDEX RANGE SCAN               | I_FILE#_BLOCK#     |     1 |       |       |     2   (0)| 00:00:
01 |        |      |            |

|* 36 |              INDEX UNIQUE SCAN                | I_FILE2            |     1 |     6 |       |     0   (0)| 00:00:
01 |        |      |            |

|* 37 |             INDEX RANGE SCAN                  | RECYCLEBIN$_TS     | 28329 |       |       |    92   (0)| 00:00:
01 |        |      |            |

|* 38 |            TABLE ACCESS BY INDEX ROWID        | RECYCLEBIN$        |     1 |    12 |       |   487   (1)| 00:00:
01 |        |      |            |

|  39 |        HASH GROUP BY                          |                    |     1 |    22 |       |     1 (100)| 00:00:
01 |        |      |            |

|  40 |         PX COORDINATOR                        |                    |       |       |       |            |
   |        |      |            |


。。。
Statistics
----------------------------------------------------------
     154755  recursive calls
       1979  db block gets
     797948  consistent gets
     153583  physical reads
        132  redo size
       1919  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed


2.2 查看回收站对象
SQL> select count(*) from dba_recyclebin;

  COUNT(*)
----------
   169423
      
3.解决办法
3.1清空回收站所有对象
SQL> purge dba_recyclebin;


3.2 重新收集统计信息
exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'RECYCLEBIN$', estimate_percent => 100, method_opt=> 'for all indexed columns',degree=>8);




4.解决后执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 1258632344

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

| Id  | Operation                                     | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
TQ  |IN-OUT| PQ Distrib |


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


|   0 | SELECT STATEMENT                              |                    |    17 |  1615 |    56  (13)| 00:00:01 |
    |      |            |


。。。。
|* 21 |            INDEX UNIQUE SCAN                  | I_FILE2            |     1 |     6 |     0   (0)| 00:00:01 |
    |      |            |

|  22 |           NESTED LOOPS                        |                    |     1 |   109 |    12  (17)| 00:00:01 |
    |      |            |

|  23 |            NESTED LOOPS                       |                    |     1 |   103 |    12  (17)| 00:00:01 |
    |      |            |

|  24 |             MERGE JOIN                        |                    |     1 |    38 |    11  (10)| 00:00:01 |
    |      |            |

|* 25 |              TABLE ACCESS BY INDEX ROWID      | RECYCLEBIN$        |     1 |    12 |     4   (0)| 00:00:01 |
    |      |            |

|  26 |               INDEX FULL SCAN                 | RECYCLEBIN$_TS     |     1 |       |     3   (0)| 00:00:01 |
    |      |            |

|* 27 |              SORT JOIN                        |                    |    12 |   312 |     7  (15)| 00:00:01 |
    |      |            |

|* 28 |               TABLE ACCESS FULL               | TS$                |    12 |   312 |     6   (0)| 00:00:01 |
    |      |            |

|* 29 |             FIXED TABLE FIXED INDEX           | X$KTFBUE (ind:1)   |     1 |    65 |     1 (100)| 00:00:01 |
    |      |            |

|* 30 |            INDEX UNIQUE SCAN                  | I_FILE2            |     1 |     6 |     0   (0)| 00:00:01 |
    |      |            |

|  31 |           NESTED LOOPS                        |                    |     1 |    90 |    10   (0)| 00:00:01 |
    |      |            |

|  32 |            NESTED LOOPS                       |                    |     1 |    90 |    10   (0)| 00:00:01 |
    |      |            |

|  33 |             NESTED LOOPS                      |                    |     1 |    78 |     8   (0)| 00:00:01 |
    |      |            |

|  34 |              NESTED LOOPS                     |                    |     1 |    72 |     8   (0)| 00:00:01 |
    |      |            |


|* 35 |               TABLE ACCESS FULL               | TS$                |     1 |    20 |     6   (0)| 00:00:01 |
    |      |            |

|  36 |               TABLE ACCESS CLUSTER            | UET$               |     1 |    52 |     2   (0)| 00:00:01 |
    |      |            |

|* 37 |                INDEX RANGE SCAN               | I_FILE#_BLOCK#     |     1 |       |     2   (0)| 00:00:01 |
    |      |            |

|* 38 |              INDEX UNIQUE SCAN                | I_FILE2            |     1 |     6 |     0   (0)| 00:00:01 |
    |      |            |

|* 39 |             INDEX RANGE SCAN                  | RECYCLEBIN$_TS     |     1 |       |     2   (0)| 00:00:01 |
    |      |            |

|* 40 |            TABLE ACCESS BY INDEX ROWID        | RECYCLEBIN$        |     1 |    12 |     2   (0)| 00:00:01 |
    |      |            |

|  41 |        HASH GROUP BY                          |                    |     1 |    22 |     1 (100)| 00:00:01 |
    |      |            |


。。。。


Statistics
----------------------------------------------------------
         48  recursive calls
        589  db block gets
        806  consistent gets
          1  physical reads
          0  redo size
       1909  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         14  rows processed
         
5.原因
应用测试,大量删除对象,导致回收站对象过多。


==补充:
又一次发现查看表空间使用率慢! 查看plan 发现查询dba_free_space时比较慢:
VIEW                            | DBA_FREE_SPACE     | 14599 |   313K|    75  (26)| 00:00:01 
select count(*) from dba_free_space;--耗时76s
purge dba_recyclebin;后执行<1s


         

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

请登录后发表评论 登录
全部评论

注册时间:2013-11-05

  • 博文量
    65
  • 访问量
    155135