Using Segment Advisor

View to check space related issues of objects

DBA_ADVISOR_EXECUTIONS
DBA_ADVISOR_FINDINGS
DBA_ADVISOR_OBJECTS

SELECT
'Segment Advice --------------------------'|| chr(10) ||
'TABLESPACE_NAME : ' || tablespace_name || chr(10) ||
'SEGMENT_OWNER : ' || segment_owner || chr(10) ||
'SEGMENT_NAME : ' || segment_name || chr(10) ||
'ALLOCATED_SPACE : ' || allocated_space || chr(10) ||
'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10) ||
'RECOMMENDATIONS : ' || recommendations || chr(10) ||
'SOLUTION 1 : ' || c1 || chr(10) ||
'SOLUTION 2 : ' || c2 || chr(10) ||
'SOLUTION 3 : ' || c3 Advice
FROM
TABLE(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));

----------------------------------------------------------------------

You can also directly query the data dictionary views to view the advice of the Segment Advisor.Here’s a query that displays Segment Advisor advice generated within the last day:

select
'Task Name : ' || f.task_name || chr(10) ||
'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) ||
'Segment Name : ' || o.attr2 || chr(10) ||
'Segment Type : ' || o.type || chr(10) ||
'Partition Name : ' || o.attr3 || chr(10) ||
'Message : ' || f.message || chr(10) ||
'More Info : ' || f.more_info || chr(10) ||
'------------------------------------------------------' Advice
FROM dba_advisor_findings f
,dba_advisor_objects o
,dba_advisor_executions e
WHERE o.task_id = f.task_id
AND o.object_id = f.object_id
AND f.task_id = e.task_id
AND e. execution_start > sysdate - 1
AND e.advisor_name = 'Segment Advisor'
ORDER BY f.task_name;
Here is some sample output:
Task Name : SYS_AUTO_SPCADV_53092205022011
Start Run Time : 05-feb-11 22:09
Segment Name : CWP_USER_PROFILE
Segment Type : TABLE
Partition Name :
Message : Compress object REP_MV.CWP_USER_PROFILE, estimated savings is
3933208576 bytes.
More Info : Allocated Space:3934257152: Used Space:10664: Reclaimable Spa
ce :3933208576:
------------------------------------------------------


Enable compression for all DML Operations

create table regs
(reg_id number
,reg_name varchar2(2000)
) compress for oltp;

------------------------------------------------------
select table_name, compression, compress_for
from user_tables
where table_name='REGS';

-------------------------------------------------------
I tried to reclaim space of a table but got below error message

alter table farhat.user_log shrink space;

ora-10635 invalid segment or tablespace type shrink space

When i checked table properties i found that compression is enabled for table and for a table which has compression enabled, we can't shrink space so i uncompressed table.

alter table farhat.user_log move nocompress;

Shrink space

alter table farhat.user_log shrink space;

Again enable compression for table

alter table farhat.user_log move nocompress;

Analyze table to update optimizer stats

EXEC DBMS_STATS.gather_table_stats('FARHAT', 'USER_LOG', estimate_percent => 15, cascade => TRUE);

Rebuild any indexes on table and you will see a great deal of improvement in responce time on queries.
--------------------------------------------------------