Segment Advisor

De Segment Advisor voert analyse van de fragmentatie van gespecificeerd tablespaces, segmenten of voorwerpen uit en doet aanbevelingen inzake hoe de ruimte kan worden teruggewonnen. De adviseur is toegankelijk via de Enterpise Manager (Home > Advisor Central > Segment Advisor) of via PL/SQL door gebruik te maken van de DBMS_ADVISOR package:

Creeer een segment advisor taak voor de SCOTT.EMP tabel.

DECLARE
l_object_id NUMBER;
BEGIN

DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => 'EMP_SEGMENT_ADVISOR',
task_desc => 'Segment Advisor For EMP');

DBMS_ADVISOR.create_object (
task_name => 'EMP_SEGMENT_ADVISOR',
object_type => 'TABLE',
attr1 => 'SCOTT', 
attr2 => 'EMP', 
attr3 => NULL, 
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);

DBMS_ADVISOR.set_task_parameter (
task_name => 'EMP_SEGMENT_ADVISOR',
parameter => 'RECOMMEND_ALL',
value => 'TRUE');

DBMS_ADVISOR.execute_task(task_name => 'EMP_SEGMENT_ADVISOR');

Creeer een segment advisor task voor de the USERS tablespace.

DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => 'USERS_SEGMENT_ADVISOR',
task_desc => 'Segment Advisor For USERS');

DBMS_ADVISOR.create_object (
task_name => 'USERS_SEGMENT_ADVISOR',
object_type => 'TABLESPACE',
attr1 => 'USERS', 
attr2 => NULL, 
attr3 => NULL, 
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);

DBMS_ADVISOR.set_task_parameter (
task_name => 'USERS_SEGMENT_ADVISOR',
parameter => 'RECOMMEND_ALL',
value => 'TRUE');

DBMS_ADVISOR.execute_task(task_name => 'USERS_SEGMENT_ADVISOR');
END;
/ 

Laat de resultaten zien.

SET LINESIZE 250
COLUMN task_name FORMAT A20
COLUMN object_type FORMAT A20
COLUMN schema FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_name FORMAT A30
COLUMN message FORMAT A40
COLUMN more_info FORMAT A40

SELECT f.task_name,
f.impact,
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name IN ('EMP_SEGMENT_ADVISOR', 'USERS_SEGMENT_ADVISOR')
ORDER BY f.task_name, f.impact DESC;