表的优化相关知识

zcs0237

---相同内容的表，使用表空间默认压缩要小很多？请专家告知原因

10.01-在表级别为特定表指定压缩选项（相同的表压缩后HWM下的块=1090）

一、建表t101插入数据

ZCS@11g>  create table t101 (id int,name varchar2(19)) segment creation IMMEDIATE tablespace system;

begin

for i in 1..123456 loop

insert into t101 values(i,lpad('z',19,'z'));

commit;

end loop;

end;

/

二、t101表默认是未压缩的

ZCS@11g>   select '--',compression,compress_for from  dba_tables where table_name='T101';

-- DISABLED

ZCS@11g>    analyze table t101 compute statistics;

select '--',t.blocks,t.empty_blocks,s.blocks from user_tables t,dba_segments s

where t.table_name=s.segment_name and t.table_name='T101';

--1369=hwm之下曾用过的块   38=hwm之外已分配给表未用过的块  1408=段大小

三、将t101压缩

ZCS@11g>    alter table t101 move compress;

select '--',compression,compress_for from  dba_tables where table_name='T101';

-- ENABLED  BASIC

ZCS@11g> analyze table t101 compute statistics;

select '--',t.blocks,t.empty_blocks,s.blocks from user_tables t,dba_segments s

where t.table_name=s.segment_name and t.table_name='T101';

--1090=hwm之下曾用过的块   61=hwm之外已分配给表未用过的块  1152=段大小

10.02-在表空间级别指定默认压缩选项（相同的表压缩后HWM下的块=458）

一、在表空间上设置默认压缩选项

ZCS@11g>   alter tablespace system default compress;

二、建t102表并插入数据

ZCS@11g>  create table t102 (id int,name varchar2(19)) segment creation IMMEDIATE tablespace system;

begin

for i in 1..123456 loop

insert into t102 values(i,lpad('z',19,'z'));

commit;

end loop;

end;

/

三、t102表已自动从表空间继承压缩属性

select '--',compression,compress_for from  dba_tables where table_name='T102';

-- ENABLED  BASIC

ZCS@11g>  analyze table t102 compute statistics;

select '--',t.blocks,t.empty_blocks,s.blocks from user_tables t,dba_segments s

where t.table_name=s.segment_name and t.table_name='T102';

--458=hwm之下曾用过的块   53=hwm之外已分配给表未用过的块  512=段大小

---同样的数据表空间默认压缩要小很多？

四、单独将t101表的压缩属性去除

ZCS@11g>   alter table t102 nocompress;

select '--',compression,compress_for from  dba_tables where table_name='T102';

-- DISABLED

zcs0237

一、设置KEEP池

SYS@zcs11G>  alter system set db_keep_cache_size=60m scope=spfile;

shutdown immediate;

show parameter db_keep

--db_keep_cache_size                   big integer 60M

二、查看数据库中已经有那些表被标记为热点表

SYS@zcs11G> select '--',owner,segment_type,segment_name,buffer_pool from dba_segments where buffer_pool != 'DEFAULT';

-- no rows selected

三、设置t11表的storage(buffer_pool keep)属性

SYS@zcs11G>  drop table t11 purge;

create table t11 as select level id,lpad('z',4000,'z') name from dual  connect by level <= 5000;

alter table t11 storage(buffer_pool keep);

select '--',bytes/1024/1024,'MB' from dba_segments where segment_name='T11';

--              40 MB

四、查看是否在缓存keep中

SYS@zcs11G>  set pagesize 0

set autotrace off

col segment_name for a12

select '--',segment_name,buffer_pool from dba_segments where buffer_pool != 'DEFAULT';

-- T11           KEEP

SYS@zcs11G>  set linesize 999

select '--',a.status, count(*)  from V$BH a, user_objects b  

where a.objd=b.object_id and object_name='T11' group by object_name, a.status;

-- xcur             5001   T11中的数据已经全部放入KEEP池

五、体会keep池的优势

SYS@zcs11G>  set autotrace trace statistics;

select count(*) from t11;

Statistics

----------------------------------------------------------

        140  recursive calls

          0  db block gets

       5090  consistent gets

       5000  physical reads

SYS@zcs11G>  select count(*) from t11;

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       5008  consistent gets

          0  physical reads

zcs0237

syslong

谢谢分享，感谢楼主，文章给力。

zcs0237

syslong 发表于 2013-8-13 21:12
谢谢分享，感谢楼主，文章给力。

第一个回帖

vage

刚兄又有新作了，

zcs0237

vage 发表于 2013-8-13 22:33
刚兄又有新作了，

V哥过节回来啦

vage

zcs0237 发表于 2013-8-13 22:43
V哥过节回来啦

一直在地税这边。

desert_xu

看看哦，非常精彩！

www_xylove

学习了；
不错，小刚，加油。