9i筆記-最佳化資料儲存
-- ==== 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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- EntityFramework Core筆記：儲存資料（4）Framework筆記
- 讀書筆記5-資料儲存篇筆記
- spark學習筆記--資料讀取與儲存Spark筆記
- 學習筆記|AS入門（七） 資料儲存篇筆記
- LOB儲存筆記筆記
- 9i筆記-最佳化表空間筆記
- Python爬蟲學習筆記（三、儲存資料）Python爬蟲筆記
- 資料儲存-領存高速海量資料記錄儲存模組產品介紹
- [轉]LOB儲存筆記筆記
- 爬蟲學習筆記（2）使用MongoDB儲存拉鉤資料爬蟲筆記MongoDB
- 七牛儲存使用筆記筆記
- 客戶端儲存筆記客戶端筆記
- CUUG筆記 儲存技術筆記
- 記憶體中的資料儲存記憶體
- 資料儲存
- 【大資料】BigTable分散式資料儲存系統分散式資料庫 | 複習筆記大資料分散式資料庫筆記
- Python零基礎學習筆記（二）——資料的儲存Python筆記
- Redis記憶體使用最佳化與儲存Redis記憶體
- 資料儲存(1)：從資料儲存看人類文明-資料儲存器發展歷程
- DataLeap資料資產實戰：如何實現儲存最佳化？
- 大話儲存後傳筆記筆記
- 資料倉儲維度建模筆記筆記
- 資料倉儲學習筆記(ZT)筆記
- 資料儲存：CoreData
- iOS 資料儲存iOS
- IOS資料儲存iOS
- 【儲存管理】SAN儲存 Storage Area Network 儲存區域網路 學習筆記筆記
- 【NUMBER】Oracle資料庫最佳化之理解NUMBER儲存機制Oracle資料庫
- Docker筆記2——儲存自己的LNMPDocker筆記LNMP
- GlusterFS分散式儲存學習筆記分散式筆記
- OS學習筆記五：儲存模型筆記模型
- CUDA 學習筆記之儲存器筆記
- 記錄一次資料儲存出錯
- 9i筆記-undo筆記
- Flutter持久化儲存之資料庫儲存Flutter持久化資料庫
- 資料儲存--面向列的儲存設計
- 資料儲存(歸檔解檔，沙河儲存)
- 聚焦資料時代新儲存需求，浪潮儲存的新儲存之道