这几天数据库上的用户表空间疯涨,一天的增长量达到了20~30GB,平时就4~5GB的增长量。问了开发商,他们也说不出个所以然。PS:那些鸟开发商,现在我已经对他们绝望了,什么都不知道。情急之下直接对该表空间进行了扩容,问题总算解决了。事后总想弄个明白,想知道是那张表导致的表空间增长如此之快,以免下次出现同样的问题又是不知所措。
google了一番后发现DBA_HIST_SEG_STAT可以看出对象的使用趋势
构造如下SQL查询出每个时间段内数据库对象的增长量,其中DB_BLOCK_CHANGES_DELTA为块个数
select c.SNAP_ID,to_char(c.END_INTERVAL_TIME,'yyyy-mm-dd') SNAP_TIME,a.OWNER ,a.OBJECT_NAME, a.OBJECT_TYPE, b.DB_BLOCK_CHANGES_DELTA
from dba_objects a,
(select SNAP_ID,obj#,DB_BLOCK_CHANGES_DELTA from DBA_HIST_SEG_STAT where DB_BLOCK_CHANGES_DELTA > 20000 order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc) b,
DBA_HIST_SNAPSHOT c
where a.object_id=b.obj# and object_type='TABLE' and b.SNAP_ID=c.SNAP_ID
order by DB_BLOCK_CHANGES_DELTA
下面的SQL查询出某个时间段内,数据库对象的变化大小,单位以换算成GB
select a.OBJECT_NAME, to_char(c.END_INTERVAL_TIME,'yyyy-mm-dd') SNAP_TIME, sum(b.DB_BLOCK_CHANGES_DELTA)/1024/1024 GB from dba_objects a,(select SNAP_ID,obj#,DB_BLOCK_CHANGES_DELTA from DBA_HIST_SEG_STAT where DB_BLOCK_CHANGES_DELTA > 20000 order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc) b,DBA_HIST_SNAPSHOT c
where a.object_id=b.obj# and object_type='TABLE' and b.SNAP_ID=c.SNAP_ID
group by a.OBJECT_NAME,c.END_INTERVAL_TIME
order by GB 简单介绍下DBA_HIST_SEG_STAT
DBA_HIST_SEG_STAT视图内记录了数据库实例启动以来段的变化信息,该视图的数据来自于v$segstat
Column
Datatype
NULL
Description
SNAP_ID
NUMBER
Unique snapshot ID
DBID
NUMBER
Database ID for the snapshot
INSTANCE_NUMBER
NUMBER
Instance number for the snapshot
TS#
NUMBER
Tablespace number
OBJ#
NUMBER
Dictionary object number
DATAOBJ#
NUMBER
Data object number
LOGICAL_READS_TOTAL
NUMBER
Cumulative value for logical reads
LOGICAL_READS_DELTA
NUMBER
Delta value for logical reads
BUFFER_BUSY_WAITS_TOTAL
NUMBER
Cumulative value for buffer busy waits
BUFFER_BUSY_WAITS_DELTA
NUMBER
Delta value for buffer busy waits
DB_BLOCK_CHANGES_TOTAL
NUMBER
Cumulative value for db block changes
DB_BLOCK_CHANGES_DELTA
NUMBER
Delta value for db block changes
PHYSICAL_READS_TOTAL
NUMBER
Cumulative value for physical reads
PHYSICAL_READS_DELTA
NUMBER
Delta value for physical reads
PHYSICAL_WRITES_TOTAL
NUMBER
Cumulative value for physical writes
PHYSICAL_WRITES_DELTA
NUMBER
Delta value for physical writes
PHYSICAL_READS_DIRECT_TOTAL
NUMBER
Cumulative value for physical reads direct
PHYSICAL_READS_DIRECT_DELTA
NUMBER
Delta value for physical reads direct
PHYSICAL_WRITES_DIRECT_TOTAL
NUMBER
Cumulative value for physical writes direct
PHYSICAL_WRITES_DIRECT_DELTA
NUMBER
Delta value for physical writes direct
ITL_WAITS_TOTAL
NUMBER
Cumulative value for ITL waits
ITL_WAITS_DELTA
NUMBER
Delta value for ITL waits
ROW_LOCK_WAITS_TOTAL
NUMBER
Cumulative value for row lock waits
ROW_LOCK_WAITS_DELTA
NUMBER
Delta value for row lock waits
GC_CR_BLOCKS_SERVED_TOTAL
NUMBER
Cumulative value for global cache CR blocks served
GC_CR_BLOCKS_SERVED_DELTA
NUMBER
Delta value for global cache CR blocks served
GC_CU_BLOCKS_SERVED_TOTAL
NUMBER
Cumulative value for global cache current blocks served
GC_CU_BLOCKS_SERVED_DELTA
NUMBER
Delta value for global cache current blocks served
GC_BUFFER_BUSY_TOTAL
NUMBER
Cumulative value for global cache buffer busy
GC_BUFFER_BUSY_DELTA
NUMBER
Delta value for global cache buffer busy
GC_CR_BLOCKS_RECEIVED_TOTAL
NUMBER
Cumulative value for global cache CR blocks received
GC_CR_BLOCKS_RECEIVED_DELTA
NUMBER
Delta value for global cache CR blocks received
GC_CU_BLOCKS_RECEIVED_TOTAL
NUMBER
Cumulative value for global cache current blocks received
GC_CU_BLOCKS_RECEIVED_DELTA
NUMBER
Delta value for global cache current blocks received
SPACE_USED_TOTAL
NUMBER
Cumulative value for space used
SPACE_USED_DELTA
NUMBER
Delta value for space used
SPACE_ALLOCATED_TOTAL
NUMBER
Cumulative value for space allocated
SPACE_ALLOCATED_DELTA
NUMBER
Delta value for space allocated
TABLE_SCANS_TOTAL
NUMBER
Cumulative value for table scans
TABLE_SCANS_DELTA
NUMBER
Delta value for table scans
顺便说一句DBA_HIST_TBSPC_SPACE_USAGE视图可以监控表空间的使用趋势
感谢杨帅军的指点
参考至:http://oracle.erkansaka.org/2007/09/how-to-determine-tables-with-highest.html
http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_3165.htm
本文原创,转载请注明出处、作者
如有错误,欢迎指正
邮箱:czmcj@163.com