Segment Advisory with dbms advisor package in Oracle

Segment Advisory with DBMS ADVISORY Package in Oracle Database

Segment Advisory used to analyse the segments that have space available for reclamation.
It determines that an object has a significant amount of free space for released, it recommends online segment shrink.
It also let us know about segment is having row chaining above a threshold.

Run the Segment Advisory on TABLE OR INDEX
Use your schema and table name as mentioned in the script with comment sign. Enter Object type like Tablespace, Table, Index or LOB.

DECLARE
l_object_id  NUMBER;
BEGIN
-- Create a segment advisor task for the SCOTT.EMP table.
DBMS_ADVISOR.create_task (
advisor_name      => 'Segment Advisor',
task_name         => 'FRINDLY_ADVISOR',
task_desc         => 'Segment Advisor For EMP');

DBMS_ADVISOR.create_object (
task_name   => 'FRINDLY_ADVISOR', ---Task Name
object_type => 'TABLE', ---Object Type
attr1       => 'KSD2000',     ---SCHEMA Name
attr2       => 'KSD_PROSJEKT_DIFF',  ---TABLE NAME
attr3       => NULL, ---Partition Name
attr4       => 'null',
attr5       => NULL,
object_id   => l_object_id);

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

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

END;
/

For Drop the Segment Advisory

exec dbms_advisor.delete_task(task_name =>'FRINDLY_ADVISOR');

Check the Segment Advisory is dropped

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 ('FRINDLY_ADVISOR')
ORDER BY f.task_name, f.impact DESC;

Check the object is locked:

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S,
V$PROCESS P, V$SQL SQ
WHERE L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR
AND S.SQL_ADDRESS = SQ.ADDRESS;

Online Segment Shrink with row enable

-- Enable row movement.
ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

— Recover space, but don’t amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;

— Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;

— Recover space for the object and all dependant objects.
ALTER TABLE scott.emp SHRINK SPACE CASCADE;

— Disable row movement.
ALTER TABLE scott.emp DISABLE ROW MOVEMENT;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.