Oracle SQL space advisor

SYS@rac1> connect anuj/v123
Connected.


ANUJ@rac1> create table anuj_big_table (id number, name char(200));

Table created.

ANUJ@rac1> insert into anuj_big_table select rownum,'a' from dual connect by rownum<100000;

99999 rows created.

ANUJ@rac1> commit;

Commit complete.


 SELECT trunc(SPACE_USED/1024/1024) SPACE_USED_Mb ,trunc(SPACE_ALLOCATED/1024/1024) SPACE_ALLOCATED_Mb,CHAIN_PCENT FROM TABLE(dbms_space.object_space_usage_tbf('ANUJ', 'ANUJ_BIG_TABLE', 'TABLE', NULL));

SPACE_USED_MB SPACE_ALLOCATED_MB CHAIN_PCENT
------------- ------------------ -----------
 22 24 0




ANUJ@rac1> delete from anuj_big_table where mod(id,10)<>0;

90000 rows deleted.

ANUJ@rac1> commit;

Commit complete.


SELECT trunc(SPACE_USED/1024/1024) SPACE_USED_Mb ,trunc(SPACE_ALLOCATED/1024/1024) SPACE_ALLOCATED_Mb,CHAIN_PCENT FROM TABLE(dbms_space.object_space_usage_tbf('ANUJ', 'ANUJ_BIG_TABLE', 'TABLE', NULL));

SPACE_USED_MB SPACE_ALLOCATED_MB CHAIN_PCENT
------------- ------------------ -----------
 4 24 0



Change folowing 

my_task_name
attr1 owner 
attr2 table name


DECLARE
 my_task_id NUMBER;
 obj_id NUMBER;
 my_task_name VARCHAR2(100);
 my_task_desc VARCHAR2(500);
BEGIN 
 my_task_name := 'tableBIG_TABLE';
 my_task_desc := 'Manual Segment Advisor Run';
 ---------
 -- Step 1
 ---------
 dbms_advisor.create_task ( advisor_name => 'Segment Advisor', task_id => my_task_id, task_name => my_task_name, task_desc => my_task_desc);
 ---------
 -- Step 2
 ---------
 dbms_advisor.create_object ( task_name => my_task_name,
 object_type => 'TABLE',
 attr1 => 'ANUJ',
 attr2 => 'ANUJ_BIG_TABLE',
 attr3 => NULL,
 attr4 => NULL,
 attr5 => NULL,
 object_id => obj_id);
 ---------
 -- Step 3
 ---------
 dbms_advisor.set_task_parameter( task_name => my_task_name, parameter => 'recommend_all',value => 'TRUE');
 ---------
 -- Step 4
 ---------
 dbms_advisor.execute_task(my_task_name);
END;
/

PL/SQL procedure successfully completed.

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.advisor_name = 'Segment Advisor'
AND e. execution_start > sysdate - 1
ORDER BY f.task_name;

ADVICE
--------------------------------------------------------------------------------
Task Name : tableBIG_TABLE
Start Run Time : 27-sep-17 02:11
Segment Name : ANUJ_BIG_TABLE
Segment Type : TABLE
Partition Name :
Message : Enable row movement of the table ANUJ.ANUJ_BIG_TABLE and perf
orm shrink, estimated savings is 20101729 bytes.
More Info : Allocated Space:25165824: Used Space:5064095: Reclaimable Spa
ce :20101729:
------------------------------------------------------


set pagesize 200 linesize 200
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(ALL_RUNS=>'TRUE', SHOW_MANUAL=>'TRUE',SHOW_FINDINGS=>'FALSE'));


ADVICE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Segment Advice --------------------------
TABLESPACE_NAME : USERS
SEGMENT_OWNER : ANUJ
SEGMENT_NAME : ANUJ_BIG_TABLE
ALLOCATED_SPACE : 25165824
RECLAIMABLE_SPACE:20101729
RECOMMENDATIONS : Enable row movement of the table ANUJ.ANUJ_BIG_TABLE and perform shrink, estimated savings is 20101729 bytes.
SOLUTION 1: alter table "ANUJ"."ANUJ_BIG_TABLE" shrink space
SOLUTION 2: alter table "ANUJ"."ANUJ_BIG_TABLE" shrink space COMPACT
SOLUTION 3: alter table "ANUJ"."ANUJ_BIG_TABLE" enable row movement





col BENEFIT_TYPE FOR a120
SELECT benefit_type FROM DBA_ADVISOR_RECOMMENDATIONS WHERE task_name='tableBIG_TABLE';

BENEFIT_TYPE
------------------------------------------------------------------------------------------------------------------------
Enable row movement of the table ANUJ.ANUJ_BIG_TABLE and perform shrink, estimated savings is 20101729 bytes.







SELECT tablespace_name,
-- segment_owner,
segment_name,
segment_type,
round (allocated_space/1024/1024,2) "Allocated, Mb",
round (used_space/1024/1024,2) "Used, Mb",
round (reclaimable_space/1024/1024,2) "Reclaimable, Mb",
recommendations,
c1,
c2,
c3
FROM TABLE (DBMS_SPACE.asa_recommendations ())
order by 4 desc; 


 SELECT task_id FROM dba_advisor_tasks WHERE task_name='tableBIG_TABLE';

 TASK_ID
----------
 11520




SELECT
 segment_owner,
 segment_name,
 ROUND(allocated_space/(1024*1024)) AS "Allocated (MB)",
 ROUND(used_space/(1024*1024)) AS "Used (MB)",
 ROUND(reclaimable_space/(1024*1024)) AS "Reclaimable (MB)",
 ROUND(reclaimable_space*100/allocated_space) AS "Percentage gain"
 FROM TABLE(dbms_space.asa_recommendations())
 WHERE task_id = (SELECT task_id FROM dba_advisor_tasks WHERE task_name='tableBIG_TABLE');



SET pages 50
SELECT recommendations,c3,c2,c1 FROM TABLE(dbms_space.asa_recommendations()) WHERE task_id = (SELECT task_id FROM dba_advisor_tasks WHERE task_name='tableBIG_TABLE');



set linesize 200 pagesize 200 
col TASK_NAME for a50
col EXECUTION_START FOR a30
col EXECUTION_END FOR a25
SELECT * FROM (SELECT task_name,TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') AS execution_start, TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_end,status, ROUND((execution_end-execution_start)*24*60,1) AS "Duration (min)"
FROM dba_advisor_tasks 
WHERE advisor_name='Segment Advisor' 
ORDER BY task_id DESC) 
WHERE rownum<=10;


TASK_NAME EXECUTION_START EXECUTION_END STATUS Duration (min)
-------------------------------------------------- ------------------------------ ------------------------- ----------- --------------
tableBIG_TABLE1 27-sep-2017 02:42:46 27-sep-2017 02:42:47 COMPLETED 0
SYS_AUTO_SPCADV932072319092017 19-sep-2017 23:07:32 19-sep-2017 23:07:46 COMPLETED .2




SET pages 100 lines 200
SELECT * FROM TABLE(dbms_space.object_growth_trend('ANUJ','ANUJ_BIG_TABLE','TABLE'))
ORDER BY timepoint;

TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY
--------------------------------------------------------------------------- ----------- ----------- --------------------
28-AUG-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
29-AUG-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
30-AUG-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
31-AUG-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
01-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
02-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
03-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
04-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
05-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
06-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
07-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
08-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
09-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
10-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
11-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
12-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
13-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
14-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
15-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
16-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
17-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
18-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
19-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
20-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
21-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
22-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
23-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
24-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
25-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
26-SEP-17 03.15.51.119584 AM 5064095 25165824 INTERPOLATED
27-SEP-17 03.15.51.119584 AM 5064095 25165824 GOOD








ANUJ@rac1> ALTER TABLE anuj.ANUJ_BIG_TABLE ENABLE ROW MOVEMENT;

Table altered.

ANUJ@rac1> ALTER TABLE anuj.ANUJ_BIG_TABLE SHRINK SPACE;

Table altered.

ANUJ@rac1> ALTER TABLE anuj.ANUJ_BIG_TABLE SHRINK SPACE COMPACT;

Table altered.

ANUJ@rac1> ALTER TABLE anuj.ANUJ_BIG_TABLE SHRINK SPACE CASCADE;

Table altered.



SELECT trunc(SPACE_USED/1024/1024) SPACE_USED_Mb ,trunc(SPACE_ALLOCATED/1024/1024) SPACE_ALLOCATED_Mb,CHAIN_PCENT FROM TABLE(dbms_space.object_space_usage_tbf('ANUJ', 'ANUJ_BIG_TABLE', 'TABLE', NULL));


SPACE_USED_MB SPACE_ALLOCATED_MB CHAIN_PCENT
------------- ------------------ -----------
 2 2 0



Table Size ---

with segment_rollup as (
select owner, table_name, owner segment_owner, table_name segment_name from dba_tables
union all
select table_owner, table_name, owner segment_owner, index_name segment_name from dba_indexes
union all
select owner, table_name, owner segment_owner, segment_name from dba_lobs
union all
select owner, table_name, owner segment_owner, index_name segment_name from dba_lobs ), ranked_tables as ( select rank() over (order by sum(blocks) desc) rank, sum(blocks) blocks, r.owner, r.table_name 
from segment_rollup r, dba_segments s
where s.owner =r.segment_owner 
and s.segment_name =r.segment_name
and r.owner=upper('&schema_name')
group by r.owner, r.table_name
)
select rank, round(blocks*8/1024) mb, table_name from ranked_tables
where 1=1
and table_name= 'ANUJ_BIG_TABLE'
-- and rank<=20;


 RANK MB TABLE_NAME
---------- ---------- --------------------------------------------------------------------------------------------------------------------------------
 2 24 ANUJ_BIG_TABLE



SELECT tablespace_name,
-- segment_owner,
segment_name,
segment_type, 
round (allocated_space/1024/1024,2) "Allocated, Mb",
round (used_space/1024/1024,2) "Used, Mb",
round (reclaimable_space/1024/1024,2) "Reclaimable, Mb",
recommendations,
c1,
c2,
c3
FROM TABLE (DBMS_SPACE.asa_recommendations ())
order by 4 desc; 



set pagesize 200 linesize 200
col JOB_START_TIME for a40
col JOB_DURATION for a30
select job_start_time,job_status,job_duration,job_error from DBA_AUTOTASK_JOB_HISTORY
where client_name='auto space advisor'
and job_start_time > sysdate - 1
order by job_start_time;


JOB_START_TIME JOB_STATUS JOB_DURATION JOB_ERROR
---------------------------------------- ------------------------------ ------------------------------ ----------
26-SEP-17 10.00.08.476056 PM EST5EDT SUCCEEDED +000 00:00:29 0
26-SEP-17 11.03.26.726343 PM EST5EDT SUCCEEDED +000 00:00:33 0
26-SEP-17 11.14.22.829939 PM EST5EDT SUCCEEDED +000 00:00:32 0
26-SEP-17 11.23.30.956927 PM EST5EDT SUCCEEDED +000 00:00:20 0
26-SEP-17 11.24.25.594535 PM EST5EDT SUCCEEDED +000 00:00:19 0
26-SEP-17 11.33.32.428398 PM EST5EDT SUCCEEDED +000 00:00:21 0
26-SEP-17 11.34.26.762093 PM EST5EDT SUCCEEDED +000 00:00:19 0
26-SEP-17 11.43.33.801698 PM EST5EDT SUCCEEDED +000 00:00:22 0
26-SEP-17 11.44.28.435450 PM EST5EDT SUCCEEDED +000 00:00:20 0



select ss.DATAOBJ#,sn.END_INTERVAL_TIME,ss.DB_BLOCK_CHANGES_DELTA,ss.PHYSICAL_WRITES_DELTA from DBA_HIST_SEG_STAT ss,DBA_HIST_SNAPSHOT sn,DBA_HIST_SEG_STAT_OBJ so
where so.OWNER='ANUJ' 
and so.OBJECT_NAME='ANUJ_BIG_TABLE' 
and so.OBJECT_TYPE='TABLE' 
and ss.OBJ#=so.OBJ# 
and ss.DATAOBJ#=so.DATAOBJ# 
and ss.snap_id=sn.snap_id
order by ss.snap_id,ss.DATAOBJ#;


 DATAOBJ# END_INTERVAL_TIME DB_BLOCK_CHANGES_DELTA PHYSICAL_WRITES_DELTA
---------- --------------------------------------------------------------------------- ---------------------- ---------------------
 142685 27-SEP-17 02.00.29.291 AM 115552 2962
 142685 27-SEP-17 02.00.29.358 AM 115552 2962



EXEC DBMS_ADVISOR.DELETE_TASK('tableBIG_TABLE');

PL/SQL procedure successfully completed.