有效的使用数据块
???? 避免动态extent的缺点
???? 1.创建本地管理表空间
???? 2.适当的segment大小.
???? 3.监控segment适当扩展.
????
???? 显示少于10%闲置的blocks的表的segment
????? select owner,table_name,blocks,empty_blocks
????? from dba_tables
????? where empty_blocks/(blocks+empty_blocks)<1
????? and owner='SCOTT';
???? 备注:exec dbms_stats.gather_table_stats('SCOTT','EMP')不能统计空闲
???? 块dba_tables中empty_blocks,需要使用analyze table t compute statistics;
??手动申请extent
????? alter table scott.emp allocate extent;
??表空间的segment建议采用uniform;
??? create tablespace tbs_data
???? datafile '$ORACLE_HOME/oradata/tbs01_01.dbf'
???? size 200M
???? extent management local
???? uniform size 1M
???? segment space management auto;
????
??????? select bytes ,blocks ,extents
?????? from dba_segments
??? where owner='SCOTT'
??? and segment_name='EMP';
??
??select extent_id,block_id,bytes
??????????? from dba_extents
????? where owner='SCOTT'
?????? and segment_name='EMP';
????
??实例如下:
??????? create table t (id int ,name varchar(20));
??????? exec dbms_stats.gather_table_stats('SCOTT','T');
??????? analyze table t compute statistics;
??????? begin
??????????? for i in 1..10000
??????????? loop
????? insert into t values(i,'bbk');
??????????? end loop;????
??? end;
??? /
???
??? 使用大的extent的优缺点
??? 优点:1.减少extent的频繁动态扩展.
???????? 2.性能可能有一点优势.
?????
??? 缺点:1.空闲的extent可能被浪费.
???高水位的理解.
???使用truncate可以将高水位恢复到原来的比较低的位置.
???? 但是delete不会恢复到原来的比较低的位置.
???alter table t allocate ( size 100M);
???
???dbms_space包的使用;
???
???回收高水位空间
???? 1.使用导入导出工具,在重建.
???? 2.alter table table_name move tablespace_name;
???? 3.alter table table_name deallocate unused;
???
???
???
???PCTFREE:默认为10%;
?????????? PCTFREE=100*(UPD/Average row length);
???PCTUSED:默认为40%(不推荐使用)
?????????? PCTUSED=100-PCTFREE-100*ROWS*Average row length/Block_size;
???????? 建议采用自动管理方式即可,只使用PCTFREE的使用,不用设置PCTUSED.
???
???迁移和关联
?????? 检查迁移和关联
???? analyze table scott.emp compute statistics;
???? select num_rows,chain_cnt from dba_tables
???? where table_name='EMP';
????
???? 查看关联行
????? analyze table scott.emp list chained rows;
????? select owner_name,table_name,head_rowid
?????????? from chained_rows
??????? where table_name='EMP';
????
???索引重建
?????? 监控索引信息
?????? 1.收集索引统计信息
?????? exec dbms_stats.gather_index_stats('OE','Customer_PK');
?????? 2.查看索引收集信息
??????? select name,(del_lf_rows_len/lf_rows_len)*100 as wastage
?????? from index_stats;
?????? 3.重建索引
???????? alter index oe.Customer_PK rebuild online;
?????? 4.收集索引碎片
????????? alter index oe.Customer_PK coalesce;
????rebuild:
????? 1.可以很块迁移索引表空间.
????? 2.需要比较高的磁盘空间.
????? 3.创建一个新的索引.
????? 4.可以更改索引表空间的一些参数,比较慢.
????coalesce
????? 1.不能迁移表空间.
????? 2.比较低的消耗,不需要太多表空间.
????? 3.合并可能的块.
????? 4.索引更改比较块.
????
????在索引收集的时候收集统计信息
???? create index scott.emp_pk
???? ...
???? compute statistics;(oracle9i以后过时)
????
????重建所有的时候收集统计信息
????alter index scott.emp_pk rebuild
????? compute statistics;(oracle9i以后过时)
????
??????????????? 监控索引是否被使用
?? ????
?????? alter index scott.emp_pk monitoring usage;
??????????????? 查询使用的索引
??????????????????? select index_name ,used from v$object_usgae;
??????????????? 停止监控索引的使用情况
???????? alter index scott.emp_pk nomonitoring usage;