Segment Advisor的一点简单测试！

最新推荐文章于 2021-04-11 13:40:00 发布

clt3617

最新推荐文章于 2021-04-11 13:40:00 发布

阅读量122

点赞数

尽管可以通过em使用Segment Advisor，不过我还是想通过调用dbms_advisor来简单的了解一下Segment Advisor的大致流程！[@more@]

SQL> create table tt1 as select * from dba_objects;

表已创建。
SQL> SET SERVEROUTPUT ON
declare
uf_blocks number ;
uf_bytes number;
fs1_blocks number;
fs1_bytes number;
fs2_blocks number;
fs2_bytes number;
fs3_blocks number;
fs3_bytes number;
fs4_blocks number;
fs4_bytes number;
full_blocks number;
full_bytes number;
begin
dbms_space.space_usage('TEST','TT1','TABLE',
uf_blocks ,
uf_bytes ,
fs1_blocks,
fs1_bytes ,
fs2_blocks ,
fs2_bytes ,
fs3_blocks,
fs3_bytes ,
fs4_blocks,
fs4_bytes ,
full_blocks,
full_bytes
);
dbms_output.put_line('uf_blocks:'||uf_blocks);
dbms_output.put_line('fs1_blocks:'||fs1_blocks);
dbms_output.put_line('fs2_blocks:'||fs2_blocks);
dbms_output.put_line('fs3_blocks:'||fs3_blocks);
dbms_output.put_line('fs4_blocks:'||fs4_blocks);
dbms_output.put_line('full_blocks:'||full_blocks);
end;
SQL> /
uf_blocks:0
fs1_blocks:0
fs2_blocks:0
fs3_blocks:0
fs4_blocks:0
full_blocks:145

PL/SQL 过程已成功完成。

SQL>
--过程free_blocks只适合统计mssm=manual类型的上的segment
SQL> declare
2 free_blks number;
3 begin
4 dbms_space.free_blocks('TEST','TT1','TABLE',1,free_blks);
5 dbms_output.put_line('free_blks:'||free_blks);
6 end;
7 /
declare
*
第 1 行出现错误:
ORA-10618: Operation not allowed on this segment
ORA-06512: 在 "SYS.DBMS_SPACE", line 152
ORA-06512: 在 line 4

SQL> show user
USER 为 "SYS"
--把segment tt1移到system标空间看看
SQL> connect test/test
已连接。
SQL> alter table tt1 move tablespace system;

表已更改。

SQL> edit
已写入 file afiedt.buf

1 declare
2 free_blks number;
3 begin
4 dbms_space.free_blocks('TEST','TT1','TABLE',0,free_blks);
5 dbms_output.put_line('free_blks:'||free_blks);
6* end;
SQL> /

PL/SQL 过程已成功完成。

SQL> set serveroutput on
SQL> /
free_blks:0

PL/SQL 过程已成功完成。

SQL> delete from tt1 where object_id<1000;

已删除953行。

SQL> commit;

提交完成。

SQL> declare
2 free_blks number;
3 begin
4 dbms_space.free_blocks('TEST','TT1','TABLE',0,free_blks);
5 dbms_output.put_line('free_blks:'||free_blks);
6 end;
7 /
free_blks:11

PL/SQL 过程已成功完成。

SQL> edit
已写入 file afiedt.buf

1 declare
2 t_blocks number;
3 t_bytes number;
4 u_blocks number;
5 u_bytes number;
6 l_file_id number;
7 l_block_id number;
8 l_u_block number;
9 begin
10 dbms_space.unused_space('TEST','TT1','TABLE',
11 t_blocks,
12 t_bytes,
13 u_blocks,
14 u_bytes,
15 l_file_id,
16 l_block_id,
17 l_u_block
18 );
19 dbms_output.put_line('t_blocks:'||t_blocks);
20 dbms_output.put_line('t_bytes:' ||t_bytes);
21 dbms_output.put_line('u_blocks:'||u_blocks);
22 dbms_output.put_line('u_bytes:' ||u_bytes);
23 dbms_output.put_line('l_file_id:' ||l_file_id);
24 dbms_output.put_line('l_block_id:'||l_block_id);
25 dbms_output.put_line('l_u_block:' ||l_u_block);
26* end;
SQL> /
t_blocks:256
t_bytes:2097152
u_blocks:110
u_bytes:901120
l_file_id:1
l_block_id:34569
l_u_block:18

PL/SQL 过程已成功完成。

SQL> alter table tt move tablespace users;

表已更改。

SQL> declare
2 t_blocks number;
3 t_bytes number;
4 u_blocks number;
5 u_bytes number;
6 l_file_id number;
7 l_block_id number;
8 l_u_block number;
9 begin
10 dbms_space.unused_space('TEST','TT1','TABLE',
11 t_blocks,
12 t_bytes,
13 u_blocks,
14 u_bytes,
15 l_file_id,
16 l_block_id,
17 l_u_block
18 );
19 dbms_output.put_line('t_blocks:'||t_blocks);
20 dbms_output.put_line('t_bytes:' ||t_bytes);
21 dbms_output.put_line('u_blocks:'||u_blocks);
22 dbms_output.put_line('u_bytes:' ||u_bytes);
23 dbms_output.put_line('l_file_id:' ||l_file_id);
24 dbms_output.put_line('l_block_id:'||l_block_id);
25 dbms_output.put_line('l_u_block:' ||l_u_block);
26 end;
27 /
t_blocks:256
t_bytes:2097152
u_blocks:110
u_bytes:901120
l_file_id:1
l_block_id:34569
l_u_block:18

PL/SQL 过程已成功完成。

SQL>
--===================================
SQL> alter table tt1 shrink space;
alter table tt1 shrink space
*
第 1 行出现错误:
ORA-10636: ROW MOVEMENT is not enabled

SQL> alter table tt1 enable row movement;

表已更改。

SQL> select file_id,extent_id,block_id,blocks from dba_extents where segment_nam
e='TT1';

FILE_ID EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
4 0 1529 8
4 1 1537 8
4 2 6537 8
4 3 6545 8
4 4 6553 8
4 5 105 8
4 6 6561 8
4 7 6569 8
4 8 6577 8
4 9 6585 8
4 10 6593 8

FILE_ID EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
4 11 6601 8
4 12 6609 8
4 13 6617 8
4 14 6625 8
4 15 6633 8
4 16 6665 128

已选择17行。

SQL> alter table tt1 shrink space;

表已更改。

SQL> declare
2 uf_blocks number ;
3 uf_bytes number;
4 fs1_blocks number;
5 fs1_bytes number;
6 fs2_blocks number;
7 fs2_bytes number;
8 fs3_blocks number;
9 fs3_bytes number;
10 fs4_blocks number;
11 fs4_bytes number;
12 full_blocks number;
13 full_bytes number;
14 begin
15 dbms_space.space_usage('TEST','TT1','TABLE',
16 uf_blocks ,
17 uf_bytes ,
18 fs1_blocks,
19 fs1_bytes ,
20 fs2_blocks ,
21 fs2_bytes ,
22 fs3_blocks,
23 fs3_bytes ,
24 fs4_blocks,
25 fs4_bytes ,
26 full_blocks,
27 full_bytes
28 );
29 dbms_output.put_line('uf_blocks:'||uf_blocks);
30 dbms_output.put_line('fs1_blocks:'||fs1_blocks);
31 dbms_output.put_line('fs2_blocks:'||fs2_blocks);
32 dbms_output.put_line('fs3_blocks:'||fs3_blocks);
33 dbms_output.put_line('fs4_blocks:'||fs4_blocks);
34 dbms_output.put_line('full_blocks:'||full_blocks);
35 end;
36 /
uf_blocks:0
fs1_blocks:2
fs2_blocks:1
fs3_blocks:0
fs4_blocks:0
full_blocks:77

PL/SQL 过程已成功完成。

SQL> select file_id,extent_id,block_id,blocks from dba_extents where segment_nam
e='TT1';

FILE_ID EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
4 0 1529 8
4 1 1537 8
4 2 6537 8
4 3 6545 8
4 4 6553 8
4 5 105 8
4 6 6561 8
4 7 6569 8
4 8 6577 8
4 9 6585 8
4 10 6593 8

已选择11行。

SQL>
SQL> variable id number;
SQL> begin
2 declare
3 name varchar2(100);
4 descr varchar2(500);
5 obj_id number;
6 begin
7 name:='Manual_TT1';
8 descr:='Segment Advisor Test';
9
10 dbms_advisor.create_task (
11 advisor_name => 'Segment Advisor',
12 task_id => :id,
13 task_name => name,
14 task_desc => descr);
15
16 dbms_advisor.create_object (
17 task_name => name,
18 object_type => 'TABLE',
19 attr1 => 'TEST',
20 attr2 => 'TT1',
21 attr3 => NULL,
22 attr4 => NULL,
23 attr5 => NULL,
24 object_id => obj_id);
25
26 dbms_advisor.set_task_parameter(
27 task_name => name,
28 parameter => 'recommend_all',
29 value => 'TRUE');
30
31 dbms_advisor.execute_task(name);
32 end;
33 end;
34 /

PL/SQL 过程已成功完成。

SQL>
SQL> desc dba_advisor_findings
名称是否为空? 类型
----------------------------------------- -------- ----------------------------

OWNER VARCHAR2(30)
TASK_ID NOT NULL NUMBER
TASK_NAME VARCHAR2(30)
FINDING_ID NOT NULL NUMBER
TYPE VARCHAR2(11)
PARENT NOT NULL NUMBER
OBJECT_ID NUMBER
IMPACT_TYPE VARCHAR2(4000)
IMPACT NUMBER
MESSAGE VARCHAR2(4000)
MORE_INFO VARCHAR2(4000)

SQL> col message format a30
SQL> col more_info format a50
SQL> select message,more_info from dba_advisor_findings where owner='TEST';

MESSAGE
------------------------------
MORE_INFO
--------------------------------------------------
此对象中的空闲空间小于 10MB。
分配空间:720896: 已用空间:652249: 可回收空间:68647
:

SQL> select task_name,message,more_info from dba_advisor_findings where owner='T
EST';

TASK_NAME MESSAGE
------------------------------ ------------------------------
MORE_INFO
--------------------------------------------------
Manual_TT1 此对象中的空闲空间小于 10MB。
分配空间:720896: 已用空间:652249: 可回收空间:68647
:

SQL> select count(*) from tt1;

COUNT(*)
----------
6531

SQL> delete from tt1 where object_id<10000;

已删除4714行。

SQL> commit;

提交完成。

SQL> exec dbms_advisor.execute_task('Manual_TT1');
BEGIN dbms_advisor.execute_task('Manual_TT1'); END;

*
第 1 行出现错误:
ORA-13630: 任务 Manual_TT1 包含执行结果, 所以不能执行。
ORA-06512: 在 "SYS.PRVT_ADVISOR", line 1624
ORA-06512: 在 "SYS.DBMS_ADVISOR", line 186
ORA-06512: 在 line 1

SQL> exec dbms_advisor.delete_task('Manual_TT1');

PL/SQL 过程已成功完成。

SQL> variable id number;
SQL> begin
2 declare
3 name varchar2(100);
4 descr varchar2(500);
5 obj_id number;
6 begin
7 name:='Manual_TT1';
8 descr:='Segment Advisor Test';
9
10 dbms_advisor.create_task (
11 advisor_name => 'Segment Advisor',
12 task_id => :id,
13 task_name => name,
14 task_desc => descr);
15
16 dbms_advisor.create_object (
17 task_name => name,
18 object_type => 'TABLE',
19 attr1 => 'TEST',
20 attr2 => 'TT1',
21 attr3 => NULL,
22 attr4 => NULL,
23 attr5 => NULL,
24 object_id => obj_id);
25
26 dbms_advisor.set_task_parameter(
27 task_name => name,
28 parameter => 'recommend_all',
29 value => 'TRUE');
30
31 dbms_advisor.execute_task(name);
32 end;
33 end;
34 /

PL/SQL 过程已成功完成。

SQL> select task_name,message,more_info from dba_advisor_findings where owner='T
EST';

SQL>

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

转载于:http://blog.itpub.net/19602/viewspace-1012964/

clt3617

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Segment Advisor的一点简单测试！

尽管可以通过em使用Segment Advisor，不过我还是想通过调用dbms_advisor来简单的了解一下Segment Advisor的大致流程！[@more@]SQL> create table tt1 as s...
复制链接

扫一扫