9i笔记-优化数据存储

-- ==== 6.4 如何检测和消除行链接? ====--[@more@]

-- ==== 6.4 如何检测和消除行链接? ====--
-- 1 分析表
analyze table chn_tbl compute statistics;

-- 2 查看chain_cnt一列是否有数字?
select table_name,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,last_analyzed
from dba_tables
where owner = 'TUNER'
and table_name = 'CHN_TBL'

-- 3 创建chained_rows表 (注意: 如果要分析tuner的链接行,必须用tuner用户执行这个过程)
@c:oracleora92/rdbms/admin/utlchn1.sql

-- 4 分析表,插入每个链接行的行标
analyze table chn_tbl list chained rows;

-- 5 创建临时表,保存所有链接的行
SQL> create table chn_tmp as select * from chn_tbl where rowid in (select head_rowid from chained_rows);

Table created

SQL> select count(1) from chn_tmp;

COUNT(1)
----------
773

-- 6 删除原始表中所有链接的行
SQL> delete chn_tbl where rowid in (select head_rowid from chained_rows);

773 rows deleted

SQL> commit;

Commit complete

-- 7 重新定义表的pctfree(10)和pctused(60)
SQL> alter table chn_tbl pctfree 50 pctused 30;

Table altered

SQL> alter table chn_tbl move;

Table altered

-- 8 重新插入临时表中的所有行
SQL> insert into chn_tbl select * from chn_tmp;

773 rows inserted

SQL> commit;

Commit complete

-- 9 清除临时表
SQL> drop table chn_tmp;

Table dropped

SQL> drop table chained_rows;

Table dropped

--============补充说明==============--
/**
上述过程只消除了以前产生的链接行,如果有新的更新,还会产生链接行,但其数量会下降;
可以以SYSTEM用户运行以下脚本,来消除链接行.
*/
declare
v_owner varchar2(30) := upper('&owner');
v_table_name varchar2(30) := upper('&table_name');
v_pctfree number := &pct_free;
v_pctused number := &pct_uesd;
v_chain_cnt number := 0;
v_count number := 0;
begin
execute immediate 'analyze table ' || v_owner || '.' || v_table_name ||
' estimate statistics sample 10 percent';

select chain_cnt into v_chain_cnt
from dba_tables
where owner=v_owner and table_name=v_table_name;

if v_chain_cnt >0 then
select count(*) into v_count
from user_tables
where table_name= 'CHOCHO_CHAINED_ROWS';
if v_count>0 then
execute immediate 'DROP TABLE CHOCHO_CHAINED_ROWS';
end if;
execute immediate 'create table chocho_chained_rows ('||
'owner_name varchar2(30), '||
'table_name varchar2(30), '||
'cluster_name varchar2(30), '||
'partition_name varchar2(30), '||
'subpartition_name varchar2(30), '||
'head_rowid rowid, '||
'analyze_timestamp date ) ';
dbms_output.put_line('Number of chained rows for '> = '||to_char(v_chain_cnt));
dbms_output.put_line('');
dbms_output.put_line('');
execute immediate 'analyze table ' ||v_owner||'.'||v_table_name||
' list chained rows into chocho_chained_rows';
execute immediate 'create table chocho_chained_temp as select * from ' ||
v_owner ||'.'||v_table_name||' where rowid in '||
'(select head_rowid from chocho_chained_rows)'||
execute immediate 'delete from '||v_owner||'.'||v_table_name||' where rowid in '||
'(select head_rowid from chocho_chained_rows)';
execute immediate 'alter table ' ||v_owner||'.'||v_table_name||
' pctfree '||v_pctfree||' pctused '||v_pctused;
execute immediate 'insert into '||v_owner||'.'||v_table_name||
' select * from chocho_chained_temp';
execute immediate 'drop table chocho_chained_rows';
execute immediate 'drop table chocho_chained_temp';
dbms_output.put_line('chained rows eliminated.');
else
dbms_output.put_line('chained rows eliminated no required.');
end if;
exception
when others then dbms_output.put_line('error: '||sqlerrm);
end;
/

--==============示例脚本=================--
BEGIN
for i in 1..25000 loop
insert into chn_tbl values (
i,'this is a test text #' || i);
end loop;
commit;
end;

begin
for i in 1001..2000 loop
update chn_tbl set text=text||text||text||text||text
where num=i;
end loop;
commit;
end;

SQL> create table chn_tbl
2 (num number(6),
3 text varchar2(255)
4 )
5 pctfree 10
6 pctused 60
7 tablespace users
8 /

Table created

SQL> select table_name,pct_free,pct_used
2 from user_tables where table_name='CHN_TBL';

TABLE_NAME PCT_FREE PCT_USED
------------------------------ ---------- ----------
CHN_TBL 10

-- ===========================计算某个模式的每个表的最大行长 以system运行============================== --
select table_name "Tab_name",
sum( decode( substr(data_type,1,1),
'N',trunc((nvl(data_precision,38) + 1 )
/2,0 ) +1,
'D', 7, DATA_LENGTH) ) "Max_Len"
from dba_tab_columns
where owner= upper('&schema_owner')
group by table_name
having sum(decode( substr(data_type,1,1),
'N', trunc( ( nvl(data_precision,38) +1 )
/2,0) +1,
'D',7, data_length) )>0
order by 2 desc ,1
/
上述用于估计一个表的最大行长，再加上根据应用估计的平均行长，据以推算pctfree值
例如： 使用上面估计的product表的最大行长是1622, 按应用实际情况估计的行长是155
Est. Average Growth = (Max Row - Avg Row) /2
Est.AvgGrowth = (1622-155)/2 = 733.5

pctfree = (Est.AvgGrowth / MaxRowLength) *100
= 733.5/1622*100
= 45 percent
即估计预留的空闲空间45%

另外一种确定pctfree的方法是使用现有的统计数据估计
analyze table tuner.products
estimate statistics sample 30 percent;
select table_name,avg_row_len
from dba_tables
where owner='TUNER'
and table_name = 'PRODUCTS';

TABLE_NAME AVG_ROW_LEN
------------------------------ -----------
PRODUCTS 125

Est.AvgGrowth = (1622-125)/2 = 748.5

pctfree = (Est.AvgGrowth / MaxRowLength) *100
= 748.5/1622*100
= 46 percent
应该向上舍入到50%

-- =========================pctfree pctused一般设置原则================================ --
事务不常活动： pctfree=5 pctused=80

事务频繁：
更新小： pctfree=10
更新中：pctfree=30
更新大：pctfree=50
删除小：pctused=30
删除中：pctused=40
删除大：pctused=50

pctfree指定预留空闲空间的块的百分数的块的选项。
pctused指在块添加回空闲列表之前，使用的块的数量

-- ============================字典管理的表空间，指定pctfree示例============================= --
SQL> select tablespace_name,
2 extent_management,
3 allocation_type,
4 segment_space_management
5 from dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ ---------- --------- ------
SYSTEM DICTIONARY USER MANUAL
UNDOTBS1 LOCAL SYSTEM MANUAL
TEMP LOCAL UNIFORM MANUAL
INDX LOCAL SYSTEM AUTO
TOOLS LOCAL SYSTEM AUTO
USERS LOCAL SYSTEM AUTO

6 rows selected.

SQL> create tablespace dd_ts datafile
2 'c:oracleoradataorcl2dd_ts01.dbf' size 10m
3 extent management dictionary ;

Tablespace created.

SQL> select tablespace_name,
2 extent_management,
3 allocation_type,
4 segment_space_management
5 from dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ ---------- --------- ------
SYSTEM DICTIONARY USER MANUAL
UNDOTBS1 LOCAL SYSTEM MANUAL
TEMP LOCAL UNIFORM MANUAL
INDX LOCAL SYSTEM AUTO
TOOLS LOCAL SYSTEM AUTO
USERS LOCAL SYSTEM AUTO
DD_TS DICTIONARY USER MANUAL

7 rows selected.

-- 示例4
SQL> create table tbl
2 ( num number )
3 storage (
4 initial 1m
5 next 2m
6 minextents 3
7 maxextents 10
8 pctincrease 0
9 )
10 tablespace dd_ts;

Table created.

SQL> select segment_name,extent_id,bytes
2 from dba_extents
3 where segment_name = 'TBL'
4 order by 2;

SEGMENT_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
TBL 0 1064960
TBL 1 2129920
TBL 2 2129920

-- 示例5
drop table tbl;
create table tbl ( num number )
storage (
initial 500k
next 1m
minextents 4
maxextents unlimited
pctincrease 20
)
tablespace dd_ts;
select segment_name,extent_id,bytes
from dba_extents
where segment_name = 'TBL'
order by 2;
SEGMENT_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
TBL 0 532480
TBL 1 1064960
TBL 2 1269760
TBL 3 1515520

extents的解释：
500k
1m
1024*1024*1.2/8192 向上舍入 155块=1269760
1024*1024*1.2*1.2/8192 向上舍入 185块 = 1515520

-- 示例5
drop table tbl;
create table tbl ( num number )
storage (
initial 100k
next 200k
minextents 4
maxextents unlimited
pctincrease 100
)
tablespace dd_ts;
select segment_name,extent_id,bytes
from dba_extents
where segment_name = 'TBL'
order by 2;

SEGMENT_NAME EXTENT_ID BYTES
------------------------------ ---------- ----------
TBL 0 122880
TBL 1 204800
TBL 2 409600
TBL 3 819200

drop table tbl;

-- ===========================DMT数据字典盘区管理的============================== --
create table tbl_dd ( num number )
storage (
initial 1m
next 1m
minextents 4
--maxextents unlimited
--pctincrease 100
)
tablespace dd_ts;

-- v$tablespace中查看的dd_ts表空间号6

-- 查看所有用于dd_ts表空间的盘区
select * from uet$ where ts# = 6;

SEGFILE# SEGBLOCK# EXT# TS# FILE# BLOCK# LENGTH
---------- ---------- ---------- ---------- ---------- ---------- ----------
6 2 2 6 6 262 195
6 2 3 6 6 457 290
6 2 0 6 6 2 130
6 2 1 6 6 132 130

-- 查看所有属于表空间dd_ts表空间的文件扩展部分
select * from fet$ where ts# = 6;

TS# FILE# BLOCK# LENGTH
----- ---------- ---------- ----------
6 6 537 744

在DICT管理的表空间下，需要频繁更新这两个数据字典，因此IO成本较高。

-- =============================LMT的有关内容如下============================ --
SQL> create tablespace lm_ts
2 datafile
3 'c:oracleoradataorcl2lm_ts01.dbf' size 10m
4 extent management local ;

Tablespace created.

SQL> create table tbl_l1
2 (num number )
3 storage( initial 4m)
4 tablespace lm_ts;

Table created.

create table tbl_l2
(num number )
-- storage( initial 4m)
tablespace lm_ts;

-- 验证一下初始扩展值
select table_name,initial_extent
from user_tables
where table_name like 'TBL_L%';

TABLE_NAME INITIAL_EXTENT
------------------------------ --------------
TBL_L1 4194304
TBL_L2 65536

-- ==========================盘区报告 表空间是否用尽了最大扩展设置？=============================== --
select t.tablespace_name,
t.max_extents ,
s.total_extents ,
ceil(df.total_size/(1024*1024)) total_size,
ceil(s.total_used/(1024*1024)) total_used,
ceil(t.next_extent/(1024*1024)) next_extent
from dba_tablespaces t,
(select tablespace_name,
sum(extents) total_extents,
sum(bytes) total_used
from dba_segments
group by tablespace_name) s,
(select tablespace_name,
sum(bytes) total_size
from dba_data_files
group by tablespace_name) df
where t.tablespace_name = s.tablespace_name
and t.tablespace_name = df.tablespace_name
order by t.tablespace_name
/

TABLESPACE_NAME MAX_EXTENTS TOTAL_EXTENTS TOTAL_SIZE TOTAL_USED
------------------------------ ----------- ------------- ---------- ----------
DD_TS 505 4 10 5
LM_TS 2147483645 5 10 5
SYSTEM 505 1931 250 135
UNDOTBS1 2147483645 225 200 75
USERS 2147483645 207 25 23

从以上结果可以看到是否将要用尽允许的extents.

-- =========================Segment 段大小的设置================================ --
方法1： 使用最大行长
估计表大小 = 最大行长 * （1 +PCTFREE/100） * 行数 * 预留百分比
Estimated table size = MRL * (1 + PF/100) * NOR * FACTOR
Where :
MRL = Maximum row length
PF = pctfree
NOR = Number of rows
FACTOR = 20 percent or any desired percentage of number of rows as a fudge factor
预留百分比指的是未预料的增长，比如以下情况
表初始1000000行，预测每月增长10% ， 大量的insert update 以及少量的delete(pctfree = 50 )
select table_name "Tab_name",
sum( decode( substr(data_type,1,1),
'N',trunc((nvl(data_precision,38) + 1 )
/2,0 ) +1,
'D', 7, DATA_LENGTH) ) "Max_Len"
from
-- dba_tab_columns
user_tab_columns
where
--owner= upper('schema_owner')
table_name = 'CUSTOMERS'
group by table_name
having sum(decode( substr(data_type,1,1),
'N', trunc( ( nvl(data_precision,38) +1 )
/2,0) +1,
'D',7, data_length) )>0
order by 2 desc ,1

Tab_name Max_Len
------------------------------ ----------
CUSTOMERS 1367

MRL = 1367
PF = 50
一年的数据量（行总数） 100万* 12 * 10% = 1320万
1M * 10% * 12 = 13200000 ROWS
FACTOR = NOR*20% = 1320万 * 20% = 264 万

合计 = 1367 * (1 + 50/100) * (13200000 * 1.2)
= 10826640000
= 10826640000/(1024*1024*1024) = 10 GB
即1年需要10G空间。
不需要巨大的初始盘区，initial取100m即可.

create table customers ()
pctfree 50
pctused 30
initrans 1
maxtrans 255
storage ( initial 100m) ....
注意: 如果将这个表建LTM表空间上,pctused freelist next_extent pct_increase 等参数不再使用

方法二：
使用统计信息中的平均行长 略

-- ==========================6.4.3 分析表和统计表=============================== --
分析工具：
ANALYZE
DBMS_UTILITY.ANALYZE_DATABASE
DBMS_UTILITY.ANALYZE_SCHEMA
DBMS_STATS.GATHER_SCHEMA_STATS

查看统计信息：
DBA_TABLES
USER_TABLES
DBA_TAB_COLUMNS
USER_TAB_COLUMNS

删除统计信息：
DBMS_STATS.DELETE_SCHEMA_STATS('TUNER')

DBMS_UTILITY.ANALYZE_SCHEMA使用示例
完整统计
exec dbms_utility.analyze_schema('TUNER','COMPUTE');

统计前删除原有的统计
exec dbms_utility.analyze_schema('TUNER','DELETE');

只分析部分数据30%
exec dbms_utility.analyze_schema('TUNER','ESTIMATE',null,30);

-- ============================= 查看一个表的未使用空间的信息 ============================ --
declare
v_owner varchar2(30) := 'TUNER';
v_name varchar2(30) := 'CUSTOMERS';
v_type varchar2(30) := 'TABLE';

v_tot_blks number;
v_tot_bytes number;
v_unused_blks number;
v_unused_bytes number;
v_p1 number;
v_p2 number;
v_p3 number;
begin
dbms_space.unused_space(
v_owner,v_name,v_type,
v_tot_blks,
v_tot_bytes,
v_unused_blks,
v_unused_bytes,
v_p1,v_p2,v_p3);
dbms_output.put_line('OBJECT: '||v_owner||'.'||v_name||' - Type:'||v_type);
dbms_output.put_line('Total number of blocks = '||v_tot_blks);
dbms_output.put_line('Total number of bytes = '||v_tot_bytes);
dbms_output.put_line('Total number unused blocks = '||v_unused_blks);
dbms_output.put_line('Total number unused bytes = '||v_unused_bytes);
exception when others then dbms_output.put_line(sqlerrm);
end;
-- ========================================================= --

-- =========================6.5 索引段================================ --
查询索引名称
SQL> col column_name for a30
SQL> select index_name,column_name
from user_ind_columns
where table_name = 'ORDERS'
order by column_position
/
INDEX_NAME COLUMN_NAME
------------------------------ -----------------
SYS_C001578 ORDER_ID

分析索引
SQL> analyze index SYS_C001578 validate structure;

查询索引的不对称比率是否超过了20%(与analyze在一个会话中)
select round(del_lf_rows_len/lf_rows_len*100) balance_ratio
from index_stats
where name = 'SYS_C001578'
/

BALANCE_RATIO
-------------
0

重建大于20%的索引
alter index index_name rebuild online;

-- ========================================================= --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/271063/viewspace-1007324/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/271063/viewspace-1007324/