Oracle表变更趋势追踪记录(转)

最新推荐文章于 2024-08-21 13:45:00 发布

jgmydsai

最新推荐文章于 2024-08-21 13:45:00 发布

阅读量2.5k

点赞数

分类专栏：经典语句

经典语句专栏收录该内容

12 篇文章 1 订阅

订阅专栏

注意文章中的object_type='TABLE' 条件忽略了分区表“TABLE PARTITION”，各位看客使用的时候修改下即可

from http://www.myexception.cn/database/489598.html

这几天数据库上的用户表空间疯涨，一天的增长量达到了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