Suggestions for manually generating segments in Oracle (Segment Advisor)

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. Functions

• When 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 consultants

• Suitable for CIDR blocks to be reclaimed by CIDR blocks.
• Segment with a large number of row links
• Segments that may benefit from OLTP Compression

Iv. manual operation section advisor steps

1. Create a segment advisor task
2. Assign an object to this task (specify to run at the table object or table space level, and specify the user name, table name, or table space name)
3. Set task parameters (some parameters during execution of the execution segment consultant, such as running duration limit parameters, production-only recommendations related to space/suggestions for generating all types of objects)
4. Execute this task

V. Code of manual operation segment Advisor

Declare
My_task_id number;
Obj_id number;
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 is null.
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 advisor. If it is TRUE, it is recommended to generate all types of objects. If it is FALSE, only 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 => 'commend _ 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
From 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_name = 'advisor _ test tab Advice'
Order by f. task_name;
(2)
Query TABLE (dbms_space.asa_recommendations (all_runs => 'true', show_manual => 'true', show_findings => 'false') to view the suggestion;
The first parameter "true" indicates the running result of each operation, and "false" indicates the result of the last operation.
The second parameter "true" indicates that the result of the manual operation segment advisor is returned, and "false" indicates that the result of the automatic operation segment advisor is returned.
The third parameter "true" indicates that only the analysis results are displayed, and "false" indicates that the analysis results and suggestions are displayed.
Note: Use the (1) Statement in the case to view the analysis result.

For more details, please continue to read the highlights on the next page:

1 2 Next Page