How to Tune Segment if ADDM report reported that segment is responsible for significant “User I/O”
Example:
Run “Segment Advisor” on TABLE “AR.AR_PAYMENT_SCHEDULES_ALL” with object ID 28886.
Step 1: Creating the tuning task and Execute.
variable id number;
begin
declare
name varchar2(100);
descr varchar2(500);
obj_id number;
begin
name:=’‘;
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 => ‘‘,
attr2 => ‘‘,
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;
/
Step 2: Check Task status.
select task_name, status from dba_advisor_tasks where owner = ‘SYS’ and advisor_name = ‘Segment Advisor’ and task_name=’‘;
Step 3: Displaying the recommendations
select af.more_info, af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message
from dba_advisor_findings af, dba_advisor_objects ao
where ao.task_id = af.task_id
and ao.object_id = af.object_id
and ao.owner = ‘SYS’ and af.task_name=’‘;
Step 4: If advisory show message:
The free space in the object is less than the size of the last extent.
Then follow bellow Action Plan:
A) Take the backup of objects
B) Backup the scripts by using TOAD
C) Check Function based index on objects if exist the copy scripts.
D) After copy scripts, drop the function based index on objects
E) Enable row movement on objects
SQL> alter table . enable row movement;
F) Shrink the space
SQL> alter table .
shrink space;
G) Create the function based index on table by executing copied create index scripts
Responses