技术博客 数据库、运维与工程实践笔记

oracle 11g表空间使用率统计心得 – magic5650的专栏 – 博客频道 – CSDN.NET

2024-12-04Oracle / RAC / 性能优化

oracle 11g表空间使用统计

本文研究有关oracle表空间统计的相关手段

  • 传统的oracle表空间统计
  • oracle11g新增表空间统计视图
  • x$kttets
  • v$filespace_usage
  • sys.WRH$_TABLESPACE_SPACE_USAGE
  • 保证快速及准确的查询表空间

传统的oracle表空间统计

传统的oracle表空间的统计是通过查询视图dba_data_files及dba_free_space统计出来的

例如这样：

select 
    tb1.Tablespace_name "Tablespace_name",
    decode(sign(tb2.Sizes-0.99),1,round(tb2.Sizes,2) || 'GB',round(tb2.Sizes*1024,2) || 'MB') "Size",
    decode(sign((tb2.Sizes-tb1.Free)-0.99),1,round((tb2.Sizes-tb1.Free),2) || 'GB',round((tb2.Sizes-tb1.Free)*1024,2) || 'MB') "Used",
    decode(sign(tb1.Free-0.99),1,round(tb1.Free,2) || 'GB',round(tb1.Free*1024,2) || 'MB') "Free",
    decode(sign(tb2.Max-0.99),1,round(tb2.Max,2) || 'GB',round(tb2.Max*1024,2) || 'MB') "Max",
    to_number(round(((tb2.Sizes-tb1.Free)/tb2.Sizes)*100,2)) "Usage%",
    to_number(round(((tb2.Sizes-tb1.Free)/tb2.Max)*100,2)) "Usageofmax%"    
from
   (select 
         tablespace_name,
         round(sum(bytes)/power(2, 30),2) as Free 
      from dba_free_space 
      group by tablespace_name) tb1,
   (select 
         tablespace_name,
         round(sum(user_bytes)/power(2, 30),2) as Sizes,
         round(sum(decode(autoextensible,'YES',maxbytes,'NO',bytes)/power(2, 30))) as Max
      from dba_data_files
      group by tablespace_name) tb2  
where tb1.Tablespace_name=tb2.Tablespace_name;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

    • 这个查询无论数据文件是自增长还是非自增长的，统计的都是最准确的，因为dba_free_space的数据来源自最基础的系统表，准确性毋庸置疑。

但是在实际的使用过程中，我在某些数据库查询时，这条语句执行的非常缓慢，原因是dba_free_space的行数太多了，例如这样的解释计划：

| Id  | Operation                           | Name             | Rows  | Bytes |   TempSpc| Cost (%CPU)| Time     |
……
|  26 |   VIEW                              |                  |    16 |   480 |       |  3036K(100)| 10:07:17 |

|  27 |    HASH GROUP BY                    |                  |    16 |   352 |       |  3036K(100)| 10:07:17 |

|  28 |     VIEW                            | DBA_FREE_SPACE   | 10353 |   222K|      |  3036K(100)| 10:07:17 |
……

|  40 |       NESTED LOOPS                  |                  | 10275 |  1073K|      |  3036K(100)| 10:07:16 |

|  41 |        NESTED LOOPS                 |                  | 95085 |  9285K|      |  3036K(100)| 10:07:16 |

|* 42 |         HASH JOIN                   |                  |   398K|    13M|    8600K|  3003   (1)| 00:00:37 |
……
Statistics
 ----------------------------------------------------------
     420830  recursive calls
       3626  db block gets
     863221  consistent gets
       3699  physical reads
          0  redo size
       1745  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processed
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28

    • 由于dba_free_space的行数加上group by的原因，竟然有86W次的逻辑读，在这里查询耗费了大量的时间，时间长达30秒或者更多

递归查询次数竟然有42W次，如你所见，这里耗费了20秒，最后整个查询有50多秒，真的让人崩溃。

select max(rownum) from dba_free_space;；这里统计出dba_free_space的行数超过19W条。于是我开始寻找其他办法解决这个烦人的问题。

oracle11g新增表空间统计视图

>

DBA_TABLESPACE_USAGE_METRICS describes tablespace usage metrics for all types of tablespaces, including permanent, temporary, and undo tablespaces. —— [ [ Oracle在线文档 ]](http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_5059.htm#REFRN23496)

> DBA_TABLESPACE_USAGE_METRICS 视图给出了答案

select * from dba_tablespace_usage_metrics;

TABLESPACE_NAME | USED_SPACE | TABLESPACE_SIZE | USED_PERCENT
—
— | | DATABAK | 396538250 | 402669384 | 98.4773777

GZCDR | 96887456 | 2684354560 | 3.609339

GZUNIBPMN | 2856 | 67108832 | .004255774

这里used_space和tablespace_size的单位都是block

统计结果跟传统的方法在99%的情况下无二致

我们来看一下DBA_TABLESPACE_USAGE_METRICS 视图的定义

CREATE OR REPLACE VIEW SYS.DBA_TABLESPACE_USAGE_METRICS AS
SELECT  t.name,
        tstat.kttetsused,
        tstat.kttetsmsize,
        (tstat.kttetsused / tstat.kttetsmsize) * 100
  FROM  sys.ts$ t, x$kttets tstat
  WHERE
        t.online$ != 3 and
        t.bitmapped <> 0 and
        t.contents$ = 0 and
        bitand(t.flags, 16) <> 16 and
        t.ts# = tstat.kttetstsn
union
 SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
     (sum(f.allocated_space)/sum(f.file_maxsize))*100
     FROM sys.ts$ t, v$filespace_usage f
     WHERE
     t.online$ != 3 and
     t.bitmapped <> 0 and
     t.contents$ <> 0 and
     f.flag = 6 and
     t.ts# = f.tablespace_id
     GROUP BY t.name, f.tablespace_id, t.ts#
union
 SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
     (sum(f.allocated_space)/sum(f.file_maxsize))*100
     FROM sys.ts$ t, gv$filespace_usage f, gv$parameter param
     WHERE
     t.online$ != 3 and
     t.bitmapped <> 0 and
     f.inst_id = param.inst_id and
     param.name = 'undo_tablespace' and
     t.name = param.value and
     f.flag = 6 and
     t.ts# = f.tablespace_id
     GROUP BY t.name, f.tablespace_id, t.ts#;
comment on table SYS.DBA_TABLESPACE_USAGE_METRICS is 'Description of all tablespace space usage metrics';
comment on column SYS.DBA_TABLESPACE_USAGE_METRICS.TABLESPACE_NAME is 'Tablespace name';
comment on column SYS.DBA_TABLESPACE_USAGE_METRICS.USED_SPACE is 'Total space consumed in the tablespace';
comment on column SYS.DBA_TABLESPACE_USAGE_METRICS.TABLESPACE_SIZE is 'Total size of the tablespace';
comment on column SYS.DBA_TABLESPACE_USAGE_METRICS.USED_PERCENT is '% of used space, as a function of maximum possible tablespace size';
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41

    • 主要是从x$kttets系统表和v$filespace_usage视图取数据

显然我们从这张视图查表空间使用情况的速度快多了

事实也是如此

Elapsed: 00:00:00.78
……
Statistics
----------------------------------------------------------
         80  recursive calls
          0  db block gets
      49438  consistent gets
          0  physical reads
          0  redo size
       1694  bytes sent via SQL*Net to client
        535  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         17  rows processed
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

x$kttets

有关这张系统表的相关信息很少

SQL> desc x$kttets;
 名称                                    是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 KTTETSTSN                                          NUMBER
 KTTETSMSIZE                                        NUMBER
 KTTETSSIZE                                         NUMBER
 KTTETSUSED                                         NUMBER
 KTTETSEMA                                          NUMBER
 KTTETSCTIME                                        NUMBER
 KTTETSCSCN                                         NUMBER
 KTTETSLASTRFN                                      NUMBER
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

v$filespace_usage

>

V$FILESPACE_USAGE summarizes space allocation information of each datafile and tempfile. ——[ [ Oracle在线文档 ]](http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_1140.htm#REFRN30333)

>

SQL> desc v$filespace_usage
 名称                                    是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_ID                                      NUMBER
 RFNO                                               NUMBER
 ALLOCATED_SPACE                                    NUMBER
 FILE_SIZE                                          NUMBER
 FILE_MAXSIZE                                       NUMBER
 CHANGESCN_BASE                                     NUMBER
 CHANGESCN_WRAP                                     NUMBER
 FLAG                                               NUMBER
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

    • 根据定义这张视图包含了所有表空间的使用的具体情况，事实上也是如此，之前有些文章里面有关DBA_TABLESPACE_USAGE_METRICS的定义里，其信息都是从这张视图中取出来的。在我测试的oracle版本是11.2.0.3，基础表的信息是由x$kttets统计的

对比了一下由v$filespace_usage和x$kttets 统计出来的基础表的信息，从x$kttets 统计出的信息比v$filespace_usage 要准确得多，这里主要是因为ALLOCATED_SPACE字段并不是指的已使用的空间的缘故，但这里FILE_MAXSIZE字段是没有问题的

sys.WRH$_TABLESPACE_SPACE_USAGE

前面说过使用DBA_TABLESPACE_USAGE_METRICS查询表空间使用情况在99%的情况下没有问题，但有1%的情况有可能因为 x$kttets 表没有被触发更新，会导致基础表的信息不准确，这种情况是有可能发生的，我就不巧碰到此情况，这有可能是oraclebug，我试验了几次，在没有重现过此情况。

下面查询dba_tablespace_usage_metrics结果(block_size为8K)

`

select * from dba_tablespace_usage_metrics

`

TABLESPACE_NAME | USED_SPACE | TABLESPACE_SIZE | USED_PERCENT
—
— | | DATABAK | 396538250 | 402669384 | 98.4773777

GZCDR | 96887456 | 2684354560 | 3.609339

GZUNIBPMN | 2856 | 67108832 | .004255774

这是传统查询出来的结果

Tablespace_name | Size | Used | Free | Max | Usage% | Usageofmax%
—
—
—
| DATABAK | 3040.02GB | 3025.31GB | 14.71GB | 3232GB | 99.52 | 93.6

GZCDR | 1024GB | 739.19GB | 284.81GB | 20480GB | 72.19 | 3.61

GZUNIBPMN | 1003.52MB | 0MB | 1003.52MB | 512GB | 0 | 0

两者并不一致，我在查询之前给DATABAK添加了3个自增长的数据文件，但x$kttets 只更新了新增了一个数据文件的数据，非常的奇怪，在此之前没发生过这种情况，一般来说在新增了数据文件后，x$kttets 也会跟着更新，但这次没有，我也不知道这张表是如何触发更新从何处取得数据，因为有关这张表的信息实在找不到。

就在我绝望之时，我发现了表WRH$_TABLESPACE_SPACE_USAGE

SQL> desc sys.WRH$_TABLESPACE_SPACE_USAGE
 名称                                    是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 DBID                                      NOT NULL NUMBER
 SNAP_ID                                            NUMBER
 TABLESPACE_ID                                      NUMBER
 TABLESPACE_SIZE                                    NUMBER
 TABLESPACE_MAXSIZE                                 NUMBER
 TABLESPACE_USEDSIZE                                NUMBER
 RTIME                                              VARCHAR2(25)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

    • select * from sys.WRH$_TABLESPACE_SPACE_USAGE

执行完这条语句后我惊喜的发现这是一个有关系统表空间使用情况的快照表，每个整点都会有所有表空间的使用数据情况的更新，例如这样

select * from sys.WRH$_TABLESPACE_SPACE_USAGE where to_date(rtime,'MM/dd/yyyy hh24:mi:ss')>sysdate-1/24
  • 1

    • 或者

select * from sys.WRH$_TABLESPACE_SPACE_USAGE where snap_id=(select max(snap_id ) from sys.WRH$_TABLESPACE_SPACE_USAGE)
  • 1

    • DBID | SNAP_ID | TABLESPACE_ID | TABLESPACE_SIZE | TABLESPACE_MAXSIZE | TABLESPACE_USEDSIZE | RTIME
    —
    —
    —
    | 2272203595 | 8208 | 6 | 398475082 | 423624502 | 396543370 | 02/05/2016 16:00:04

2272203595 | 8208 | 7 | 131072 | 67108832 | 3680 | 02/05/2016 16:00:04

2272203595 | 8208 | 8 | 134217728 | 2684354560 | 125680016 | 02/05/2016 16:00:04

这里的最后的快照数据与传统方法查询出来的结果是一致的

TABLESPACE_ID与TABLESPACE_NAME的关联关系可以用sys.ts$来关联

保证快速及准确的查询表空间

有了上面的讨论，为了快速的查询表空间的使用情况，我们可以这样

select tablespace_name,
       round(used_space*(select value from v$parameter where name='db_block_size')/power(2,30),2) USED_GB,
       round(tablespace_size*(select value from v$parameter where name='db_block_size')/power(2,30)) MAXSIZE_GB,
       round(used_percent,2) as "PCT%" 
from dba_tablespace_usage_metrics;
  • 1
  • 2
  • 3
  • 4
  • 5

    • 这个有99%的准确率，1%的情况是意外（也有可能是bug）

也可以这样

select t.name tablespace_name,
       round(s.tablespace_size*(select value from v$parameter where name='db_block_size')/power(2,30)) SIZE_GB,
       round(s.tablespace_maxsize*(select value from v$parameter where name='db_block_size')/power(2,30)) MAXSIZE_GB,
       round(s.tablespace_usedsize*(select value from v$parameter where name='db_block_size')/power(2,30),2) USED_GB,
       round((s.tablespace_usedsize/s.tablespace_maxsize)*100,2) as "PCT%"
from sys.WRH$_TABLESPACE_SPACE_USAGE s,
     sys.ts$ t
where s.snap_id=(select max(snap_id ) from sys.WRH$_TABLESPACE_SPACE_USAGE)
and s.tablespace_id=t.ts#
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

    • 这个数据可能是最新的，也可能不是，但在你最近1小时内没有新增数据文件的情况下，它一定是最快最准确的