How to use segment advisor
来源:互联网 发布:mysql数据库管理员招聘 编辑:程序博客网 时间:2024/06/05 08:10
1、查看segment advisor
方法一:
select * fromtable(dbms_space.asa_recommendations('TRUE','FALSE','FALSE'));
dbms_space.asa_recommentdations的三个参数:
all_runs TURE返回历次运行的结果,FALSE则返回最近一次运行的结果
show_manual TURE返回手工执行segment advisor的执行结果,FALSE返回自动执行的segment advisor的执行结果
show_finding TURE仅显示结果不显示建议,FALSE显示结果和建议
方法二:
select f.task_name,
execution_start,
o.attr2,
o.type,
o.attr3,
f.message,
f.more_info
from dba_advisor_executions e,
dba_advisor_findings f,
dba_advisor_objects o
where o.task_id = f.task_id
and o.object_id = f.object_id
and f.task_id = e.task_id
and e.advisor_name = 'Segment Advisor'
order by f.task_name;
segment advisor会自动的定期执行,查看其执行情况的视图是dba_auto_segadv_summary
查看ora advisor的视图
dba_advisor_executions
dba_advisor_findings
dba_advisor_objects
2、手工生成segment advisor
查看advisor name
select * fromdba_advisor_definitions;
ADVISOR_IDADVISOR_NAME PROPERTY
---------------------------------------- ----------
1 ADDM 1
2 SQL Access Advisor 271
3 Undo Advisor 1
4 SQL Tuning Advisor 935
5 Segment Advisor 67
6 SQL Workload Manager 0
7 Tune MView 31
8 SQL Performance Analyzer 935
9 SQL Repair Advisor 679
10 Compression Advisor 3
生成segmentadvisor
DECLARE
my_task_id number;
obj_id number;
my_task_name varchar2(100);
my_task_desc varchar2(500);
BEGIN
my_task_name := 'BIG_TABLE Advice';
my_task_desc := 'Manual Segment Advisor Run';
-- Step 1创建一个任务
dbms_advisor.create_task(advisor_name =>'Segment Advisor',
task_id => my_task_id,
task_name => my_task_name,
task_desc => my_task_desc);
-- Step 2为这个任务分配一个对象
dbms_advisor.create_object(task_name => my_task_name,
object_type =>'TABLE',
attr1 => 'SYSTEM',
attr2 => 'AL_APPLY',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);
-- Step 3设置任务参数
dbms_advisor.set_task_parameter(task_name=> my_task_name,
parameter=> 'recommend_all',
value => 'TRUE');
-- Step 4执行这个任务
dbms_advisor.execute_task(my_task_name);
END;
/
说明:
Table 14-2 DBMS_ADVISOR packageprocedures relevant to the Segment Advisor
Package Procedure Name
Description
CREATE_TASK
Use this procedure to create the Segment Advisor task. Specify 'Segment Advisor' as the value of the ADVISOR_NAME parameter.
CREATE_OBJECT
Use this procedure to identify the target object for segment space advice. The parameter values of this procedure depend upon the object type. Table 14-3 lists the parameter values for each type of object.
Note: To request advice on an IOT overflow segment, use an object type of TABLE, TABLE PARTITION, or TABLE SUBPARTITION. Use the following query to find the overflow segment for an IOT and to determine the overflow segment table name to use with CREATE_OBJECT:
select table_name, iot_name, iot_type from dba_tables;
SET_TASK_PARAMETER
Use this procedure to describe the segment advice that you need. Table 14-4 shows the relevant input parameters of this procedure. Parameters not listed here are not used by the Segment Advisor.
EXECUTE_TASK
Use this procedure to execute the Segment Advisor task.
Table 14-3 Input forDBMS_ADVISOR.CREATE_OBJECT
Input Parameter
OBJECT_TYPE
ATTR1
ATTR2
ATTR3
ATTR4
TABLESPACE
tablespace name
NULL
NULL
Unused. Specify NULL.
TABLE
schema name
table name
NULL
Unused. Specify NULL.
INDEX
schema name
index name
NULL
Unused. Specify NULL.
TABLE PARTITION
schema name
table name
table partition name
Unused. Specify NULL.
INDEX PARTITION
schema name
index name
index partition name
Unused. Specify NULL.
TABLE SUBPARTITION
schema name
table name
table subpartition name
Unused. Specify NULL.
INDEX SUBPARTITION
schema name
index name
index subpartition name
Unused. Specify NULL.
LOB
schema name
segment name
NULL
Unused. Specify NULL.
LOB PARTITION
schema name
segment name
lob partition name
Unused. Specify NULL.
LOB SUBPARTITION
schema name
segment name
lob subpartition name
Unused. Specify NULL.
Table 14-4 Input forDBMS_ADVISOR.SET_TASK_PARAMETER
Input Parameter
Description
Possible Values
Default Value
time_limit
The time limit for the Segment Advisor run, specified in seconds.
Any number of seconds
UNLIMITED
recommend_all
Whether the Segment Advisor should generate findings for all segments.
TRUE: Findings are generated on all segments specified, whether or not space reclamation is recommended.
FALSE: Findings are generated only for those objects that generate recommendations for space reclamation.
TRUE
Example:
variable id number;
begin
declare
name varchar2(100);
descr varchar2(500);
obj_id number;
begin
name:='Manual_Employees';
descr:='Segment Advisor Example';
dbms_advisor.create_task (
advisor_name => 'Segment Advisor',
task_id => :id,
task_name => name,
task_desc => descr);
dbms_advisor.create_object (
task_name => name,
object_type => 'TABLE',
attr1 => 'HR',
attr2 => 'EMPLOYEES',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);
dbms_advisor.set_task_parameter(
task_name => name,
parameter => 'recommend_all',
value => 'TRUE');
dbms_advisor.execute_task(name);
end;
end;
/
查看segmentadvisor结果
SELECT
'Segment Advice --------------------------'||chr(10) ||
'TABLESPACE_NAME : ' || tablespace_name || chr(10) ||
'SEGMENT_OWNER : ' || segment_owner || chr(10) ||
'SEGMENT_NAME : ' || segment_name || chr(10) ||
'ALLOCATED_SPACE : ' || allocated_space || chr(10) ||
'RECLAIMABLE_SPACE: ' || reclaimable_space ||chr(10) ||
'RECOMMENDATIONS : ' || recommendations || chr(10) ||
'SOLUTION 1 : ' || c1 || chr(10) ||
'SOLUTION 2 : ' || c2 || chr(10) ||
'SOLUTION 3 : ' || c3 Advice
FROM
TABLE(dbms_space.asa_recommendations('TRUE','TRUE', 'FALSE'));
或者:
SELECT
'Task Name : ' || f.task_name || chr(10) ||
'Segment Name : ' || o.attr2 || chr(10) ||
'Segment Type : ' || o.type || chr(10) ||
'Partition Name : ' || o.attr3 || chr(10) ||
'Message : ' || f.message || chr(10) ||
'More Info : ' || f.more_info TASK_ADVICE
FROMdba_advisor_findings f
,dba_advisor_objects o
WHERE o.task_id =f.task_id
AND o.object_id =f.object_id
AND f.task_name like'BIG_TABLE Advice'
ORDER BYf.task_name;
删除segmentadvisor
execdbms_advisor.delete_task('BIG_TABLE Advice');
释放未使用的表的空间
alter tabletablename enable rowmovement;
alter tabletablename shrink spcace;
alter table tablename shrink spcace cascade;(级联收缩索引空间)
alter table tablename shrink spcace compact;(只整理碎片,不调整高水位线)--如果整理碎片和降低高水位话费时间会很长,用于分开操作
- How to use segment advisor
- How to use the Sql Tuning Advisor
- How to use STA(sql tuning advisor)
- Segment Advisor
- Segment Advisor
- How to implement segment tree
- How To Use DataGird
- how to use typedef
- how to use gz
- How to use chkconfig
- How to use ,,,,
- how to use dialog
- How to use UIDs
- How to use dmalloc
- How to use abld
- How to use BSTR
- How to use Ant
- how to use mstsclib
- 最短路径问题(Dijkstra)
- CSU-ACM暑假集训基础组训练赛(5-1) A - Problem A
- POJ1005
- Android实现数据存储技术
- C#.NET开发Winform使用MySQL数据库 无法将“System.Byte[]”的对象强制转换为类型“System.IConvertible”
- How to use segment advisor
- 某公司运维第一次电话面试
- 【CC150-8.2】走格子路径
- 菜鸟练手,程序员必会的十大算法——快排
- 【转】 解决 checkbox和文字对不齐的问题
- 转一个J2EE开发时的包命名规则,养成良好的开发习惯
- HDU 4927 Series 1 杨辉三角形
- PHP ajax页面参数传递
- -App自动更新之通知栏下载