|
最近有朋友問到了DBMS_SPACE包的使用，也看了一下，大部分是關于dbms_space.space_usage的使用，space_usage這個過程的例子已經(jīng)很多了，我也就不再多說了，除了這個過程外，另外還有兩個過程也有著特殊的用處，但使用的人不多，我們也來看看這兩個過程有什么用。 這兩個過程為：CREATE_INDEX_COST和CREATE_TABLE_COST，分別用戶評估創(chuàng)建索引和創(chuàng)建表的存儲開銷（空間占用情況）。
CREATE_INDEX_COST的語法如下： DBMS_SPACE.CREATE_INDEX_COST ( ddl IN VARCHAR2, used_bytes OUT NUMBER, alloc_bytes OUT NUMBER, plan_table IN VARCHAR2 DEFAULT NULL);
1、準備相關表和數(shù)據(jù) SQL> set serveroutput on SQL> create table t(c char(100),d varchar2(200)); 表已創(chuàng)建。
SQL> begin 2 for i in 1..5000 loop 3 insert into t values(i,i); 4 end loop; 5 commit; 6 end; 7 / PL/SQL過程已成功完成。
2、分析表，注意：沒有統(tǒng)計信息，CREATE_INDEX_COST將無法計算索引的存儲開銷 SQL> analyze table t compute statistics; 表已分析。
SQL> declare 2 v1 number; 3 v2 number; 4 begin 5 DBMS_SPACE.CREATE_INDEX_COST('create index i on t(c)',v1,v2); 6 dbms_output.put_line(v1/1024||' '||v2/1024); 7 end; 8 / 488.28125 640 --計算出的索引將占用488K字節(jié)空間，為該索引需要分配640k存儲空間 PL/SQL過程已成功完成。
3、創(chuàng)建實際索引，確定索引存儲空間是否與計算的結果相符 SQL> create index i on t(c); 索引已創(chuàng)建。
SQL> select count(*) from user_extents where segment_name='I'; COUNT(*) ---------- 11 已選擇1行。 --11個64k的區(qū)，比計算出的大1個區(qū)
4、再次裝載數(shù)據(jù) SQL> begin 2 for i in 1..5000 loop 3 insert into t values(i,i); 4 end loop; 5 commit; 6 end; 7 / PL/SQL過程已成功完成。
SQL> declare 2 v1 number; 3 v2 number; 4 begin 5 DBMS_SPACE.CREATE_INDEX_COST('create index i on t(c)',v1,v2); 6 dbms_output.put_line(v1/1024||' '||v2/1024); 7 end; 8 / 488.28125 640 --沒有分析之前，獲得得仍然是根據(jù)以前分析結果計算的值 PL/SQL過程已成功完成。
SQL> analyze table t compute statistics; 表已分析。
SQL> declare 2 v1 number; 3 v2 number; 4 begin 5 DBMS_SPACE.CREATE_INDEX_COST('create index i on t(c)',v1,v2); 6 dbms_output.put_line(v1/1024||' '||v2/1024); 7 end; 8 / 976.5625 2048 --分析之后，得到新的結果 PL/SQL過程已成功完成。
5、再次驗證，16個64k的區(qū)和1個1024k的區(qū)，2048k，與估計值相同 SQL> select count(*) from user_extents where segment_name='I'; COUNT(*) ---------- 17
------------------------------------------------------------- 6、換了一個字段進行測試 SQL> declare 2 v1 number; 3 v2 number; 4 begin 5 DBMS_SPACE.CREATE_INDEX_COST('create index i on t(d)',v1,v2); 6 dbms_output.put_line(v1/1024||' '||v2/1024); 7 end; 8 / 39.0625 192 --計算出的索引將占用39K字節(jié)空間，為該索引需要分配192k存儲空間 PL/SQL過程已成功完成。
7、創(chuàng)建索引，新建的索引比估算的值大1個區(qū) SQL> create index i on t(d); 索引已創(chuàng)建。
SQL> select count(*) from user_extents where segment_name='I'; COUNT(*) ---------- 4
SQL> drop index i;
8、再次裝載數(shù)據(jù)并分析表 SQL> begin 2 for i in 1..10000 loop 3 insert into t values(i,i); 4 end loop; 5 commit; 6 end; 7 / PL/SQL過程已成功完成。
SQL> analyze table t compute statistics; 表已分析。
9、重新計算，得到新的估算值 SQL> declare 2 v1 number; 3 v2 number; 4 begin 5 DBMS_SPACE.CREATE_INDEX_COST('create index i on t(d)',v1,v2); 6 dbms_output.put_line(v1/1024||' '||v2/1024); 7 end; 8 / 78.125 320
PL/SQL過程已成功完成。
10、創(chuàng)建索引，新建的索引比估計的大2個區(qū) SQL> create index i on t(d); 索引已創(chuàng)建。
SQL> select count(*) from user_extents where segment_name='I'; COUNT(*) ---------- 7
11、順便測試shink space的效果 SQL> select count(*) from t; COUNT(*) ---------- 20000
SQL> delete t where rownum<=15000; 已刪除15000行。
SQL> commit; 提交完成。
SQL> alter table t enable row movement; 表已更改。
12、在刪掉15000行數(shù)據(jù)后，沒有整理空間之前進行統(tǒng)計信息收集 SQL> analyze table t compute statistics; 表已分析。
SQL> declare 2 v1 number; 3 v2 number; 4 begin 5 DBMS_SPACE.CREATE_INDEX_COST('create index i on t(d)',v1,v2); 6 dbms_output.put_line(v1/1024||' '||v2/1024); 7 end; 8 / 24.4140625 128 --基于新收集的統(tǒng)計信息計算，估算的索引需要分配128k存儲空間 PL/SQL過程已成功完成。
13、收縮表，釋放占用的存儲空間 SQL> alter table t shrink space; 表已更改。
SQL> analyze table t compute statistics; 表已分析。
SQL> declare 2 v1 number; 3 v2 number; 4 begin 5 DBMS_SPACE.CREATE_INDEX_COST('create index i on t(d)',v1,v2); 6 dbms_output.put_line(v1/1024||' '||v2/1024); 7 end; 8 / 24.4140625 128 --收縮后重新收集統(tǒng)計信息，與原統(tǒng)計信息一樣，因此計算出的大小一樣 PL/SQL過程已成功完成。
SQL> select count(*) from user_extents where segment_name='I'; COUNT(*) ---------- 7 --現(xiàn)有索引并沒有收縮，僅僅是表空間進行了收縮，因此現(xiàn)有索引仍保持原大小
14、重建索引，對比新的索引大小與計算出的索引大小一樣大 SQL> alter index i rebuild; 索引已更改。
SQL> select count(*) from user_extents where segment_name='I';
COUNT(*) ---------- 2 --重建索引后新的索引占用空間與計算出的空間一樣大 CREATE_TABLE_COST有兩種用法，因此包內進行了overload，具體的語法如下： DBMS_SPACE.CREATE_TABLE_COST ( tablespace_name IN VARCHAR2, avg_row_size IN NUMBER, row_count IN NUMBER, pct_free IN NUMBER, used_bytes OUT NUMBER, alloc_bytes OUT NUMBER);
DBMS_SPACE.CREATE_TABLE_COST ( tablespace_name IN VARCHAR2, colinfos IN CREATE_TABLE_COST_COLUMNS, row_count IN NUMBER, pct_free IN NUMBER, used_bytes OUT NUMBER, alloc_bytes OUT NUMBER);
CREATE TYPE create_table_cost_colinfo IS OBJECT ( COL_TYPE VARCHAR(200), COL_SIZE NUMBER);
1、測試創(chuàng)建一個表所需的存儲大小，預計該表平均行長度為100字節(jié)，10000行數(shù)據(jù) SQL> DECLARE 2 V1 NUMBER; 3 V2 NUMBER; 4 BEGIN 5 DBMS_SPACE.CREATE_TABLE_COST('USERS', 100, 10000, 10, V1, V2); 6 DBMS_OUTPUT.PUT_LINE('V1: '||V1/1024/8||' V2: '||V2/1024/8); 7 END; 8 / V1: 143 V2: 256 --估算出該表需要存儲空間143塊，所需分配空間256塊 PL/SQL過程已成功完成。
2、創(chuàng)建該表，并插入10000行數(shù)據(jù) SQL> CREATE TABLE T1(C CHAR(96)); --96字節(jié)的char字段平均行長度為100字節(jié) 表已創(chuàng)建。
SQL> BEGIN 2 FOR I IN 1..10000 LOOP 3 INSERT INTO T1 VALUES(I); 4 ENDLOOP; 5 COMMIT; 6 END; 7 / PL/SQL過程已成功完成。
3、分析表統(tǒng)計信息 SQL> ANALYZE TABLE T1 COMPUTE STATISTICS; 表已分析。
SQL> SELECT BLOCKS,EMPTY_BLOCKS,AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME='T1'; BLOCKS EMPTY_BLOCKS AVG_ROW_LEN ---------- ------------ ----------- 180 76 100 --經(jīng)檢查，高水平線之前的塊數(shù)180塊，高水平線之后的空塊數(shù)76塊，總存儲空間為256塊，與DBMS_SPACE.CREATE_INDEX_COST計算出的總需要存儲空間大小相符。
4、通過dbms_space.space_usage過程，可以進一步看到表中各個塊的使用情況 declare unf number; unfb number; fs1 number; fs1b number; fs2 number; fs2b number; fs3 number; fs3b number; fs4 number; fs4b number; full number; fullb number; own dba_tables.owner%type; tab dba_tables.table_name%type; yesno varchar2(3); type parts is table of dba_tab_partitions%rowtype; partlist parts; type cursor_ref is ref cursor; c_cur cursor_ref; begin own:=upper('&owner'); tab:=upper('&table_name'); dbms_output.put_line('--------------------------------------------------------------------------------'); open c_cur for select partitioned from dba_tables where wner=own and table_name=tab; fetch c_cur into yesno; close c_cur; dbms_output.put_line('Owner: '||own); dbms_output.put_line('Table: '||tab); dbms_output.put_line('------------------------------------------------'); if yesno='NO' then dbms_space.space_usage(own,tab,'TABLE',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb); dbms_output.put_line('unf: '||unf||' fs1: '||fs1||' fs2: '||fs2||' fs3: '||fs3||' fs4: '||fs4||' full: '||full); else open c_cur for select * from dba_tab_partitions where table_owner=own and table_name=tab; fetch c_cur bulk collect into partlist; close c_cur; for i in partlist.first .. partlist.last loop dbms_space.space_usage(partlist(i).table_owner,partlist(i).table_name,'TABLE PARTITION',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb,partlist(i).partition_name); dbms_output.put_line('Partition: '||partlist(i).partition_name); dbms_output.put_line('unf: '||unf||' fs1: '||fs1||' fs2: '||fs2||' fs3: '||fs3||' fs4: '||fs4||' full: '||full); end loop; end if; dbms_output.put_line('--------------------------------------------------------------------------------'); end; /
輸入owner的值: HR 原值 22: own:=upper('&owner'); 新值 22: own:=upper('HR'); 輸入table_name的值: T1 原值 23: tab:=upper('&table_name'); 新值 23: tab:=upper('T1'); -------------------------------------------------------------------------------- Owner: HR Table: T1 ------------------------------------------------ unf: 0 fs1: 1 fs2: 0 fs3: 0 fs4: 39 full: 140 -------------------------------------------------------------------------------- PL/SQL過程已成功完成。 --經(jīng)查看，發(fā)現(xiàn)該表寫滿數(shù)據(jù)的塊有140塊，3/4滿的塊有39塊，1/4滿的塊有1塊，該表存儲空間沒有有效利用，可以看到140+39+1=180，這些均為高水平線之下的塊。但與DBMS_SPACE.CREATE_INDEX_COST計算出的數(shù)據(jù)需要143塊不符。
5、對表進行空間整理并重新分析 SQL> ALTER TABLE T1 MOVE; 表已更改。
SQL> ANALYZE TABLE T1 COMPUTE STATISTICS; 表已分析。
SQL> SELECT BLOCKS,EMPTY_BLOCKS,AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME='T1'; BLOCKS EMPTY_BLOCKS AVG_ROW_LEN ---------- ------------ ----------- 155 101 100 --經(jīng)檢查，高水平線之前的塊數(shù)155，高水平線之后的空塊數(shù)101，平均行長度100字節(jié)
6、通過dbms_space.space_usage過程，可以進一步看到表中各個塊的使用情況 declare unf number; unfb number; fs1 number; fs1b number; fs2 number; fs2b number; fs3 number; fs3b number; fs4 number; fs4b number; full number; fullb number; own dba_tables.owner%type; tab dba_tables.table_name%type; yesno varchar2(3); type parts is table of dba_tab_partitions%rowtype; partlist parts; type cursor_ref is ref cursor; c_cur cursor_ref; begin own:=upper('&owner'); tab:=upper('&table_name'); dbms_output.put_line('--------------------------------------------------------------------------------'); open c_cur for select partitioned from dba_tables where wner=own and table_name=tab; fetch c_cur into yesno; close c_cur; dbms_output.put_line('Owner: '||own); dbms_output.put_line('Table: '||tab); dbms_output.put_line('------------------------------------------------'); if yesno='NO' then dbms_space.space_usage(own,tab,'TABLE',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb); dbms_output.put_line('unf: '||unf||' fs1: '||fs1||' fs2: '||fs2||' fs3: '||fs3||' fs4: '||fs4||' full: '||full); else open c_cur for select * from dba_tab_partitions where table_owner=own and table_name=tab; fetch c_cur bulk collect into partlist; close c_cur; for i in partlist.first .. partlist.last loop dbms_space.space_usage(partlist(i).table_owner,partlist(i).table_name,'TABLE PARTITION',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb,partlist(i).partition_name); dbms_output.put_line('Partition: '||partlist(i).partition_name); dbms_output.put_line('unf: '||unf||' fs1: '||fs1||' fs2: '||fs2||' fs3: '||fs3||' fs4: '||fs4||' full: '||full); end loop; end if; dbms_output.put_line('--------------------------------------------------------------------------------'); end; / 輸入owner的值: HR 原值 22: own:=upper('&owner'); 新值 22: own:=upper('HR'); 輸入table_name的值: T1 原值 23: tab:=upper('&table_name'); 新值 23: tab:=upper('T1'); -------------------------------------------------------------------------------- Owner: HR Table: T1 ------------------------------------------------ unf: 0 fs1: 0 fs2: 0 fs3: 0 fs4: 0 full: 143 -------------------------------------------------------------------------------- PL/SQL過程已成功完成。 --經(jīng)查看，發(fā)現(xiàn)該表寫滿數(shù)據(jù)的塊有143塊，與DBMS_SPACE.CREATE_INDEX_COST計算出的數(shù)據(jù)需要塊數(shù)完全相同
-- review the parameters SELECT argument_name, data_type, type_owner, type_name FROM all_arguments WHERE object_name = 'CREATE_TABLE_COST' AND verload = 2
-- examine the input parameter type SELECT text FROM dba_source WHERE name = 'CREATE_TABLE_COST_COLUMNS';
-- drill down further into the input parameter type SELECT text FROM dba_source WHERE name = 'create_table_cost_colinfo';
set serveroutput on DECLARE ub NUMBER; ab NUMBER; cl sys.create_table_cost_columns; BEGIN cl := sys.create_table_cost_columns( sys.create_table_cost_colinfo('NUMBER',10), sys.create_table_cost_colinfo('VARCHAR2',30), sys.create_table_cost_colinfo('VARCHAR2',30), sys.create_table_cost_colinfo('DATE',NULL)); DBMS_SPACE.CREATE_TABLE_COST('SYSTEM',cl,100000,0,ub,ab); DBMS_OUTPUT.PUT_LINE('Used Bytes: ' || TO_CHAR(ub)); DBMS_OUTPUT.PUT_LINE('Alloc Bytes: ' || TO_CHAR(ab)); END; / |
|
|
