Oracle案例10

作者: 日期:2019-09-03

最近BI同事反馈说一张表的数据查询非常慢,这个表数据总共不到1W行数据,这么一说我们首先想到的是高水位带来的性能问题,即高水位线下占用过多数据块,而这些数据块其实是部分数据占用,大多数是空闲的数据块。

我们知道高水位线下的数据块在全表扫描时都要做,所以扫描的数据块可能远远多于实际的存数据的数据块。

一、表统计信息收集

要想得到准确的高水位信息,必须先收集统计信息,这样得到的才相对比较准确。

ANALYZE TABLE table_name ESTIMATE STATISTICS;
ANALYZE TABLE table_name COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;
execute dbms_stats.gather_table_stats;

二、表信息查看

查看表的块、行信息

select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.empty_blocks,t.LAST_ANALYZED from dba_tables t where table_name in ;
SELECT COUNT) USED_BLOCK FROM TABLE_NAME;

上述查询结果显示,当前表行数是9651行,有716119个数据块被使用,有0个未使用的数据块

实际数据占用的数据块数量为:152

综合可以看出,高水位线下其实有716119-152个数据块可以释放,这样每次全表扫描只需要扫描152个数据块即可。

通过查看段大小佐证记录数和表大小关系是否一致,通过下面的查看段大小为5.5G,记录9651行几乎不可能达到这个大小,所以基本可以断定个里面有很多空闲的块。

select segment_name,bytes/1024/1024/1024 TSize_GB from dba_segments where segment_name='table_name' ---5876219904

三、问题原因

什么情况会导致上面的问题呢,即高水位下存在很多未使用的数据块?一般是大表,经过批量删除delete操作,未释放高水位导致的。

1.全表扫描要读取高水位线下的所有数据块,无论是否含有数据。
2.如果在插入数据的时候使用了append关键字,即使高水位线下有空闲的数据库,也会从高水位线上面的数据库做分配,也就是高水位线会上升。


四、降低高水位方法

1. alter table table_name move;
此方法可释放高水位,但需要重建索引
2.alter table table_name shrink space;
此方法可释放高水位,但执行前需要开启行移动,alter table table_name enable row movement;
3.emp/imp的方式重建表数据
4.drop/create方式重建表
5.truncate表
6.alter  table  table_name  deallocate  unused  
DEALLOCATE UNUSED为释放HWM上面的未使用空间,但是并不会释放HWM下面的自由空间,也不会移动HWM的位置.
五、高水位调整实施
1.统计信息收集
2.执行计划查看












SQL set autotrace trace ;
SQL set timing on;
SQL SELECT count FROM TABLE_NAME;

3.表移动

alter table table_name move;

报错:ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
查看被锁对象:
select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id = o.object_id and l.session_id=s.sid; 

执行后再查看执行计划统计信息

看到统计信息访问的数据块已经降下来了,然后执行全表扫描,速度也是飞快。

4.索引重建

alter index  index_name  rebuild  online;

六、库高水位对象统计

①比较表的行数和表的大小关系。如果行数为0,而表的当前占用大小减去初始化时的大小后依然很大,那么说明该表有高水位。

②行数和块数的比率,即查看一个块可以存储多少行数据。如果一个块存储的行数少于5行甚至更少,那么说明有高水位。注意,这两种方法都不是十分准确,需要再对查询结果进行筛选。需要注意的是,在查询表的高水位时,首先需要分析表,以得到最准确的统计信息。

SELECT D.OWNER,
 ROUND,
 D.NUM_ROWS,
 D.BLOCKS,
 D.TABLE_NAME,
 ROUND/1024) t_size
 FROM DBA_TABLES D
 WHERE D.BLOCKS 10
 AND ROUND 5
 AND d.OWNER NOT LIKE '%SYS%' ;
SELECT OWNER,
 SEGMENT_NAME TABLE_NAME,
 SEGMENT_TYPE,
 GREATEST /
 GREATEST, 1)),
 0) WASTE_PER
 FROM )) / C.BLOCKSIZE,
 ROUND)) / C.BLOCKSIZE,
 0)) + 2 AVG_USED_BLOCKS,
 ROUND / GREATEST, 1)),
 2) CHAIN_PER,
 B.TABLESPACE_NAME O_TABLESPACE_NAME
 FROM SYS.DBA_SEGMENTS A, SYS.DBA_TABLES B, SYS.TS$ C
 WHERE A.OWNER = B.OWNER
 AND SEGMENT_NAME = TABLE_NAME
 AND SEGMENT_TYPE = 'TABLE'
 AND B.TABLESPACE_NAME = C.NAME)
 WHERE GREATEST /
 GREATEST, 1)),
 0) 50
 AND OWNER NOT LIKE '%SYS%'
 AND BLOCKS 100
 ORDER BY WASTE_PER DESC; 

 

首页
电话
短信
联系