當(dāng)前位置：首頁(yè) > 编程资源 > 编程问答 >内容正文

编程问答

DBMS_SPACE包的使用

發(fā)布時(shí)間：2024/8/26 编程问答 33 豆豆

生活随笔收集整理的這篇文章主要介紹了 DBMS_SPACE包的使用小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

最近有朋友問到了DBMS_SPACE包的使用，也看了一下，大部分是關(guān)于dbms_space.space_usage的使用，space_usage這個(gè)過程的例子已經(jīng)很多了，我也就不再多說了，除了這個(gè)過程外，另外還有兩個(gè)過程也有著特殊的用處，但使用的人不多，我們也來看看這兩個(gè)過程有什么用。

這兩個(gè)過程為：CREATE_INDEX_COST和CREATE_TABLE_COST，分別用戶評(píng)估創(chuàng)建索引和創(chuàng)建表的存儲(chǔ)開銷（空間占用情況）。

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);

下面是相關(guān)的測(cè)試代碼：

1、準(zhǔn)備相關(guān)表和數(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)計(jì)信息，CREATE_INDEX_COST將無法計(jì)算索引的存儲(chǔ)開銷

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???--計(jì)算出的索引將占用488K字節(jié)空間，為該索引需要分配640k存儲(chǔ)空間

PL/SQL過程已成功完成。

3、創(chuàng)建實(shí)際索引，確定索引存儲(chǔ)空間是否與計(jì)算的結(jié)果相符

SQL> create index i on t(c);

索引已創(chuàng)建。

SQL> select count(*) from user_extents where segment_name='I';

?COUNT(*)

----------

???????11

已選擇1行。?--11個(gè)64k的區(qū)，比計(jì)算出的大1個(gè)區(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ù)以前分析結(jié)果計(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?????????????????--分析之后，得到新的結(jié)果

PL/SQL過程已成功完成。???????

5、再次驗(yàn)證，16個(gè)64k的區(qū)和1個(gè)1024k的區(qū)，2048k，與估計(jì)值相同

SQL> select count(*) from user_extents where segment_name='I';

?COUNT(*)

----------

???????17

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

6、換了一個(gè)字段進(jìn)行測(cè)試

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???????--計(jì)算出的索引將占用39K字節(jié)空間，為該索引需要分配192k存儲(chǔ)空間

PL/SQL過程已成功完成。

7、創(chuàng)建索引，新建的索引比估算的值大1個(gè)區(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、重新計(jì)算，得到新的估算值

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)建索引，新建的索引比估計(jì)的大2個(gè)區(qū)

SQL> create index i on t(d);

索引已創(chuàng)建。

SQL> select count(*) from user_extents where segment_name='I';

?COUNT(*)

----------

????????7

11、順便測(cè)試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ù)后，沒有整理空間之前進(jìn)行統(tǒng)計(jì)信息收集

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)計(jì)信息計(jì)算，估算的索引需要分配128k存儲(chǔ)空間

PL/SQL過程已成功完成。

13、收縮表，釋放占用的存儲(chǔ)空間

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)計(jì)信息，與原統(tǒng)計(jì)信息一樣，因此計(jì)算出的大小一樣

PL/SQL過程已成功完成。

SQL> select count(*) from user_extents where segment_name='I';??

?COUNT(*)

----------

????????7

--現(xiàn)有索引并沒有收縮，僅僅是表空間進(jìn)行了收縮，因此現(xiàn)有索引仍保持原大小

14、重建索引，對(duì)比新的索引大小與計(jì)算出的索引大小一樣大

SQL> alter index i rebuild;

索引已更改。

SQL> select count(*) from user_extents where segment_name='I';

?COUNT(*)

----------

????????2

--重建索引后新的索引占用空間與計(jì)算出的空間一樣大

CREATE_TABLE_COST有兩種用法，因此包內(nèi)進(jìn)行了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);

下面是關(guān)于CREATE_TABLE_COST的測(cè)試代碼：

1、測(cè)試創(chuàng)建一個(gè)表所需的存儲(chǔ)大小，預(yù)計(jì)該表平均行長(zhǎ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?????????--估算出該表需要存儲(chǔ)空間143塊，所需分配空間256塊

PL/SQL過程已成功完成。

2、創(chuàng)建該表，并插入10000行數(shù)據(jù)

SQL> CREATE TABLE T1(C CHAR(96));?????????--96字節(jié)的char字段平均行長(zhǎng)度為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)計(jì)信息

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塊，總存儲(chǔ)空間為256塊，與DBMS_SPACE.CREATE_INDEX_COST計(jì)算出的總需要存儲(chǔ)空間大小相符。

4、通過dbms_space.space_usage過程，可以進(jìn)一步看到表中各個(gè)塊的使用情況

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塊，該表存儲(chǔ)空間沒有有效利用，可以看到140+39+1=180，這些均為高水平線之下的塊。但與DBMS_SPACE.CREATE_INDEX_COST計(jì)算出的數(shù)據(jù)需要143塊不符。

5、對(duì)表進(jìn)行空間整理并重新分析

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，平均行長(zhǎng)度100字節(jié)

6、通過dbms_space.space_usage過程，可以進(jìn)一步看到表中各個(gè)塊的使用情況

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計(jì)算出的數(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('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;

總結(jié)

以上是生活随笔為你收集整理的DBMS_SPACE包的使用的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

DBMS_SPACE

上一篇：如何估算Oracle数据库所需的UNDO
下一篇： Linux RH5平台下使用Oracle