Oracle技术服务|系统集成|技术开发

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 2933|回复: 3

oracle 11g表空间使用统计心得

[复制链接]

7

主题

1

好友

159

积分

注册会员

Rank: 2

发表于 2016-2-6 10:38:43 |显示全部楼层
本帖最后由 magic 于 2016-2-6 10:56 编辑



传统的oracle表空间统计
传统的oracle表空间的统计是通过查询视图dba_data_files及dba_free_space统计出来的
例如这样:
  1. select
  2.     tb1.Tablespace_name "Tablespace_name",
  3.     decode(sign(tb2.Sizes-0.99),1,round(tb2.Sizes,2) || 'GB',round(tb2.Sizes*1024,2) || 'MB') "Size",
  4.     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",
  5.     decode(sign(tb1.Free-0.99),1,round(tb1.Free,2) || 'GB',round(tb1.Free*1024,2) || 'MB') "Free",
  6.     decode(sign(tb2.Max-0.99),1,round(tb2.Max,2) || 'GB',round(tb2.Max*1024,2) || 'MB') "Max",
  7.     to_number(round(((tb2.Sizes-tb1.Free)/tb2.Sizes)*100,2)) "Usage%",
  8.     to_number(round(((tb2.Sizes-tb1.Free)/tb2.Max)*100,2)) "Usageofmax%"   
  9. from
  10.    (select
  11.          tablespace_name,
  12.          round(sum(bytes)/power(2, 30),2) as Free
  13.       from dba_free_space
  14.       group by tablespace_name) tb1,
  15.    (select
  16.          tablespace_name,
  17.          round(sum(user_bytes)/power(2, 30),2) as Sizes,
  18.          round(sum(decode(autoextensible,'YES',maxbytes,'NO',bytes)/power(2, 30))) as Max
  19.       from dba_data_files
  20.       group by tablespace_name) tb2  
  21. where tb1.Tablespace_name=tb2.Tablespace_name;
复制代码
这个查询无论数据文件是自增长还是非自增长的,统计的都是最准确的,因为dba_free_space的数据来源自最基础的系统表,准确性毋庸置疑。
但是在实际的使用过程中,我在某些数据库查询时,这条语句执行的非常缓慢,原因是dba_free_space的行数太多了,例如这样的解释计划:
  1. | Id  | Operation                           | Name             | Rows  | Bytes |   TempSpc| Cost (%CPU)| Time     |
  2. ……
  3. |  26 |   VIEW                              |                  |    16 |   480 |       |  3036K(100)| 10:07:17 |

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

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

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

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

  9. |* 42 |         HASH JOIN                   |                  |   398K|    13M|    8600K|  3003   (1)| 00:00:37 |
  10. ……
  11. Statistics
  12. ----------------------------------------------------------
  13.      420830  recursive calls
  14.        3626  db block gets
  15.      863221  consistent gets
  16.        3699  physical reads
  17.           0  redo size
  18.        1745  bytes sent via SQL*Net to client
  19.         524  bytes received via SQL*Net from client
  20.           2  SQL*Net roundtrips to/from client
  21.           0  sorts (memory)
  22.           0  sorts (disk)
  23.          15  rows processed
复制代码
由于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在线文档 ]
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 视图的定义
  1. CREATE OR REPLACE VIEW SYS.DBA_TABLESPACE_USAGE_METRICS AS
  2. SELECT  t.name,
  3.         tstat.kttetsused,
  4.         tstat.kttetsmsize,
  5.         (tstat.kttetsused / tstat.kttetsmsize) * 100
  6.   FROM  sys.ts$ t, x$kttets tstat
  7.   WHERE
  8.         t.online$ != 3 and
  9.         t.bitmapped <> 0 and
  10.         t.contents$ = 0 and
  11.         bitand(t.flags, 16) <> 16 and
  12.         t.ts# = tstat.kttetstsn
  13. union
  14. SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
  15.      (sum(f.allocated_space)/sum(f.file_maxsize))*100
  16.      FROM sys.ts$ t, v$filespace_usage f
  17.      WHERE
  18.      t.online$ != 3 and
  19.      t.bitmapped <> 0 and
  20.      t.contents$ <> 0 and
  21.      f.flag = 6 and
  22.      t.ts# = f.tablespace_id
  23.      GROUP BY t.name, f.tablespace_id, t.ts#
  24. union
  25. SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
  26.      (sum(f.allocated_space)/sum(f.file_maxsize))*100
  27.      FROM sys.ts$ t, gv$filespace_usage f, gv$parameter param
  28.      WHERE
  29.      t.online$ != 3 and
  30.      t.bitmapped <> 0 and
  31.      f.inst_id = param.inst_id and
  32.      param.name = 'undo_tablespace' and
  33.      t.name = param.value and
  34.      f.flag = 6 and
  35.      t.ts# = f.tablespace_id
  36.      GROUP BY t.name, f.tablespace_id, t.ts#;
  37. comment on table SYS.DBA_TABLESPACE_USAGE_METRICS is 'Description of all tablespace space usage metrics';
  38. comment on column SYS.DBA_TABLESPACE_USAGE_METRICS.TABLESPACE_NAME is 'Tablespace name';
  39. comment on column SYS.DBA_TABLESPACE_USAGE_METRICS.USED_SPACE is 'Total space consumed in the tablespace';
  40. comment on column SYS.DBA_TABLESPACE_USAGE_METRICS.TABLESPACE_SIZE is 'Total size of the tablespace';
  41. comment on column SYS.DBA_TABLESPACE_USAGE_METRICS.USED_PERCENT is '% of used space, as a function of maximum possible tablespace size';
复制代码
主要是从x$kttets系统表和v$filespace_usage视图取数据
显然我们从这张视图查表空间使用情况的速度快多了
事实也是如此
  1. Elapsed: 00:00:00.78
  2. ……
  3. Statistics
  4. ----------------------------------------------------------
  5.          80  recursive calls
  6.           0  db block gets
  7.       49438  consistent gets
  8.           0  physical reads
  9.           0  redo size
  10.        1694  bytes sent via SQL*Net to client
  11.         535  bytes received via SQL*Net from client
  12.           3  SQL*Net roundtrips to/from client
  13.           1  sorts (memory)
  14.           0  sorts (disk)
  15.          17  rows processed
复制代码
x$kttets

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

  1. SQL> desc x$kttets;
  2. 名称                                    是否为空? 类型
  3. ----------------------------------------- -------- ----------------------------
  4. ADDR                                               RAW(8)
  5. INDX                                               NUMBER
  6. INST_ID                                            NUMBER
  7. KTTETSTSN                                          NUMBER
  8. KTTETSMSIZE                                        NUMBER
  9. KTTETSSIZE                                         NUMBER
  10. KTTETSUSED                                         NUMBER
  11. KTTETSEMA                                          NUMBER
  12. KTTETSCTIME                                        NUMBER
  13. KTTETSCSCN                                         NUMBER
  14. KTTETSLASTRFN                                      NUMBER
复制代码
v$filespace_usage
V$FILESPACE_USAGE summarizes space allocation information of each datafile and tempfile. ——[ Oracle在线文档 ]
  1. SQL> desc v$filespace_usage
  2. 名称                                    是否为空? 类型
  3. ----------------------------------------- -------- ----------------------------
  4. TABLESPACE_ID                                      NUMBER
  5. RFNO                                               NUMBER
  6. ALLOCATED_SPACE                                    NUMBER
  7. FILE_SIZE                                          NUMBER
  8. FILE_MAXSIZE                                       NUMBER
  9. CHANGESCN_BASE                                     NUMBER
  10. CHANGESCN_WRAP                                     NUMBER
  11. FLAG
复制代码

根据定义这张视图包含了所有表空间的使用的具体情况,事实上也是如此,之前有些文章里面有关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)

  1. 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
MaxUsage%Usageofmax%
DATABAK
3040.02GB
3025.31GB
14.71GB
3232GB99.5293.6
GZCDR
1024GB
739.19GB
284.81GB
20480GB72.193.61
GZUNIBPMN
1003.52MB
0MB
1003.52MB
512GB00

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

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

  1. SQL> desc sys.WRH$_TABLESPACE_SPACE_USAGE
  2. 名称                                    是否为空? 类型
  3. ----------------------------------------- -------- ----------------------------
  4. DBID                                      NOT NULL NUMBER
  5. SNAP_ID                                            NUMBER
  6. TABLESPACE_ID                                      NUMBER
  7. TABLESPACE_SIZE                                    NUMBER
  8. TABLESPACE_MAXSIZE                                 NUMBER
  9. TABLESPACE_USEDSIZE                                NUMBER
  10. RTIME                                              VARCHAR2(25)
复制代码
  1. select * from sys.WRH$_TABLESPACE_SPACE_USAGE
复制代码
执行完这条语句后我惊喜的发现这是一个有关系统表空间使用情况的快照表,每个整点都会有所有表空间的使用数据情况的更新,例如这样
  1. 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)
复制代码
由于发帖文字限制,更多内容请移至oracle 11g表空间使用统计心得
或者看2楼



回复

使用道具 举报

7

主题

1

好友

159

积分

注册会员

Rank: 2

发表于 2016-2-6 10:55:07 |显示全部楼层
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$来关联

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

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

  1. select tablespace_name,
  2.        round(used_space*(select value from v$parameter where name='db_block_size')/power(2,30),2) USED_GB,
  3.        round(tablespace_size*(select value from v$parameter where name='db_block_size')/power(2,30)) MAXSIZE_GB,
  4.        round(used_percent,2) as "PCT%"
  5. from dba_tablespace_usage_metrics;
复制代码
这个有99%的准确率,1%的情况是意外(也有可能是bug)
也可以这样
  1. select t.name tablespace_name,
  2.        round(s.tablespace_size*(select value from v$parameter where name='db_block_size')/power(2,30)) SIZE_GB,
  3.        round(s.tablespace_maxsize*(select value from v$parameter where name='db_block_size')/power(2,30)) MAXSIZE_GB,
  4.        round(s.tablespace_usedsize*(select value from v$parameter where name='db_block_size')/power(2,30),2) USED_GB,
  5.        round((s.tablespace_usedsize/s.tablespace_maxsize)*100,2) as "PCT%"
  6. from sys.WRH$_TABLESPACE_SPACE_USAGE s,
  7.      sys.ts$ t
  8. where s.snap_id=(select max(snap_id ) from sys.WRH$_TABLESPACE_SPACE_USAGE)
  9. and s.tablespace_id=t.ts#
复制代码
这个数据可能是最新的,也可能不是,但在你最近1小时内没有新增数据文件的情况下,它一定是最快最准确的
回复

使用道具 举报

0

主题

0

好友

18

积分

新手上路

Rank: 1

发表于 2016-6-23 22:27:38 |显示全部楼层
问一个问题,表开启了自动增长怎么知道那个表最大是多少?是看max么?

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

回复

使用道具 举报

1

主题

0

好友

70

积分

版主

Rank: 7Rank: 7Rank: 7

发表于 2016-8-31 16:40:50 |显示全部楼层
不错
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

QQ|手机版|DB Support 技术联盟 ( 粤ICP备13057501号-1 )

GMT+8, 2018-4-23 13:20 , Processed in 0.504791 second(s), 21 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部