Recommended Oracle manual generation segment (SegmentAdvisor)

I. Description
Starting from oracle 10 Gb, oracle introduced Segment Advisor to check whether there are bucket-related recommendations in the database, and starting from 10gR2, oracle automatically schedules and runs a segment advisor job, regularly analyzes the segments in the database, and puts the analysis results in internal tables. However, in many cases, as a DBA, we disable various scheduling jobs (statistical information collection, segment advisor, SQL advisor, and so on) that come with oracle, then, execute similar jobs through manual control (or to save resources ). Therefore, in many cases, we have not used the very practical function of segment advisor. This article describes how to manually run the segment advisor to generate a suggested segment at the table object and table space level.Ii. FunctionsWhen optimizing SQL statements, we can more accurately determine whether to recycle fragments in the table. If you do not recommend that you use the create table as select temporary table method, you must know whether it is necessary to perform tablespace recovery and space recovery. When optimizing SQL statements, we can accurately determine whether to recreate or move a table to eliminate row links in the table. You can think about how much work we need to do without this suggestion. During routine active maintenance, we can actively find the list of table objects with more fragments and more serious row links in the table, which helps us to handle the problem in advance and avoid similar problems.Iii. analysis result types of segment consultantsSegments that have a large number of row links may benefit from OLTP compression.Iv. manual operation section advisor stepsCreate a segment advisor task and assign an object to the task (specify to run at the table object or table space level, and specify the user name and table name or table space name) set the task parameters (some parameters during execution of the execution segment consultant, such as the running duration limit parameter, only suggestions related to production and space/suggestions for generating all types of objects) to execute this task.V. Code of manual operation segment AdvisorDeclare
My_task_idNumber;
Obj_idNumber;
My_task_name varchar2 (100 );
My_task_desc varchar2 (500 );
Begin
My_task_name: = 'advisor _ test tab Advice '; -- Name of the running task, which can be specified at will, but it is recommended to use a meaningful name.
My_task_desc: = 'Manual Segment Advisor run'; -- Description of the running task, which can be specified at will, but it is recommended that it be a meaningful description.
----- Step 1
/* Create a segment advisor task */
Dbms_advisor.create_task (
Advisor_name => 'segment Advisor ', -- this parameter must be specified as the Segment Advisor
Task_id => my_task_id,
Task_name => my_task_name,
Task_desc => my_task_desc );
----- Step 2
/* Assign an object to this task */
Dbms_advisor.create_object (
Task_name => my_task_name,
Object_type => 'table', -- specifies the object level. If it is a TABLE object, it is 'table'. If it is a TABLE object, it is 'tablespace'
Attr1 => 'dbmon', --- if the table object level is run, this attribute is the user name, And the tablespace level attribute is the tablespace name.
Attr2 => 'advisor _ test', --- if the table object level is run, this attribute is the table name, And the tablespace level attribute isNull
Attr3 =>NULL,
Attr4 =>Null,
Attr5 =>Null,
Object_id => obj_id );
----- Step 3
/* Set task parameters */
Dbms_advisor.set_task_parameter (
Task_name => my_task_name,
/* Set the value of the "ecommend_all" parameter for the segment advisorTRUEIt is recommended to generate all types of objectsFALSEOnly suggestions related to space are generated */
/* Another rolling query running parameter "time_limit" is used to set the time limit for the advisor running. The default value is unlimited */
Parameter=> 'Recommend _ all ',---
Value=> 'True ');
----- Step 4
/* Execute this task */
Dbms_advisor.execute_task (my_task_name );
End;
/
6. Statement for querying the analysis result of the segment Consultant(1)
Select
/* "| Chr (13) | chr (10)" is a line break for windows platforms. For linux and other platforms, use "chr (10)" instead */
'Task name: '| f. task_name | chr (13) | chr (10) |
'Segment name: '| o. attr2 | chr (13) | chr (10) |
'Sement type: '| o.Type| Chr (13) | chr (10) |
'Partition name: '| o. attr3 | chr (13) | chr (10) |
'Message: '| f. Message | chr (13) | chr (10) |
'More info: '| f. more_info TASK_ADVICE
FromDba_advisor_findings f, dba_advisor_objects o
WhereO. task_id = f. task_id
AndO. object_id = f. object_id
AndF. task_name = 'advisor _ test tab Advice'
Order ByF. task_name;
(2)
QueryTABLE(Dbms_space.asa_recommendations (all_runs => 'true', show_manual => 'true', show_findings => 'false') to view the suggestions;
First ParameterTrueIndicates the running result of a previous operation,FalseIndicates the last result.
Second ParameterTrueReturns the results of the manual operation segment advisor,FalseReturns the results of the automatic operation segment advisor.
Third ParameterTrueOnly the analysis results are displayed,FalseDisplays analysis results and analysis suggestions
Note: Use the (1) Statement in the case to view the analysis result.
VII,Case 1 (Table object-level operation)SQL>Create TableAdvisor_testAs Select*FromDba_objects;
TableCreated
SQL>Insert IntoAdvisor_testSelect*FromAdvisor_test;
72525RowsInserted
SQL>/
145050RowsInserted
SQL>/
290100RowsInserted
SQL>Commit;
CommitComplete
SQL>DeleteAdvisor_testWhereRownum: <100000;
99999RowsDeleted
SQL>/
99999RowsDeleted
SQL>Commit;
CommitComplete

Declare
My_task_idNumber;
Obj_idNumber;
My_task_name varchar2 (100 );
My_task_desc varchar2 (500 );
Begin
My_task_name: = 'advisor _ test tab Advice ';
My_task_desc: = 'Manual Segment Advisor run ';
----- Step 1
Dbms_advisor.create_task (
Advisor_name => 'segment ad ',
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 => 'dbmon ',
Attr2 => 'advisor _ test ',
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;
/Executed



SQL>Select
2/* "| chr (13) | chr (10)" is a line break for windows platforms. For linux and other platforms, use "chr (10)" instead */
3 'Task name: '| f. task_name | chr (13) | chr (10) |
4 'segment name: '| o. attr2 | chr (13) | chr (10) |
5 'sement type: '| o.Type| Chr (13) | chr (10) |
6 'partition name: '| o. attr3 | chr (13) | chr (10) |
7 'message: '| f. Message | chr (13) | chr (10) |
8 'more info: '| f. more_info TASK_ADVICE
9FromDba_advisor_findings f, dba_advisor_objects o
10WhereO. task_id = f. task_id
11AndO. object_id = f. object_id
12AndF. task_name = 'advisor _ test tab Advice'
13Order ByF. task_name;

TASK_ADVICE
--------------------------------------------------------------------------------
TaskName: Advisor_test tab Advice
SegmentName: ADVISOR_TEST
SementType:TABLE
PartitionName:
Message: enables the row of table DBMON. ADVISOR_TEST to move and perform contraction, which is estimated to save 285435.
31 bytes.
MoreInfo: allocated space: 75497472: used space: 46953941: recycled space: 28543531:
VIII. Case 2 (table space-level operation)Declare
My_task_idNumber;
Obj_idNumber;
My_task_name varchar2 (100 );
My_task_desc varchar2 (500 );
Begin
My_task_name: = 'tablespace Advice ';
My_task_desc: = 'Manual Segment Advisor run ';
----- Step 1
Dbms_advisor.create_task (
Advisor_name => 'segment ad ',
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 => 'tablespace ',
Attr1 => 'users ',
Attr2 =>Null,
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;
/



SQL>Select
2/* "| chr (13) | chr (10)" is a line break for windows platforms. For linux and other platforms, use "chr (10)" instead */
3 'Task name: '| f. task_name | chr (13) | chr (10) |
4 'segment name: '| o. attr2 | chr (13) | chr (10) |
5 'sement type: '| o.Type| Chr (13) | chr (10) |
6 'partition name: '| o. attr3 | chr (13) | chr (10) |
7 'message: '| f. Message | chr (13) | chr (10) |
8 'more info: '| f. more_info TASK_ADVICE
9FromDba_advisor_findings f, dba_advisor_objects o
10WhereO. task_id = f. task_id
11AndO. object_id = f. object_id
12AndF. task_name = 'tablespace Advice'
13Order ByF. task_name;

TASK_ADVICE
--------------------------------------------------------------------------------
TaskName: Tablespace Advice
SegmentName: T_SCEGAOKAOQUERY_GZ
SementType:TABLE
PartitionName:
Message: the free space in this object is less than 10 MB.
MoreInfo: allocated space: 65536: used space: 8192: recycled space: 57344:

TaskName: Tablespace Advice
SegmentName: T_SCEGAOKAOQUERY_GZ
SementType:TABLE
PartitionName:

TASK_ADVICE
--------------------------------------------------------------------------------
Message: the free space in this object is less than 10 MB.
MoreInfo: allocated space: 3145728: used space: 2871921: recycled space: 273807:

TaskName: Tablespace Advice
SegmentName: T_SCEGAOKAOQUERY
SementType:TABLE
PartitionName:
Message: the free space in this object is less than 10 MB.
MoreInfo: allocated space: 46137344: used space: 44837534: recycled space: 1299810:
-------- For typographical purposes, the following output is omitted --------------
Note: You can run delete_task to delete the task, as shown in exec dbms_advisor.delete_task (task_name => 'tablespace Advice ');