97 - [Oracle 19C] Segment Advisor & Example

 

 

• Managing Storage Space - Segment Advisor

Managing Storage Space
Segment Advisor
• We use the Segment Advisor to identify segments that would benefit from online segment shrink.
• Only segments in locally managed tablespaces with automatic segment space management (ASSM) are eligible.
• Segment Advisor can be automatic or manual.
• Running the Segment Advisor Manually with PL/SQL using package DBMS_ADVISOR

DBMS_ADVISOR package procedures relevant to the Segment Advisor:
 CREATE_TASK
 CREATE_OBJECT
 SET_TASK_PARAMETER
 EXECUTE_TASK

For more info
https://docs.oracle.com/database/121/ARPLS/d_advis.htm#ARPLS350

 

 

 

Segment Advisor and shrink exercise

show user;
show con_name;
/*
USER is "PDB10_ADMIN"
CON_NAME 
------------------------------
PDB10
*/

 

 

 

CREATE TABLESPACE tbsalert
DATAFILE '/u01/app/oracle/oradata/ORCL/PDB10/tbsalert.dbf'
SIZE 50M  LOGGING EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
/*
TABLESPACE TBSALERT created.
*/



# 테이블 스페이스 여유공간 확인.
SELECT df.tablespace_name tablespace, fs.bytes free, df.bytes, 
fs.bytes*100/ df.bytes PCT_FREE
FROM dba_data_files df ,dba_free_space fs
WHERE df.tablespace_name = fs.tablespace_name
AND df.tablespace_name = 'TBSALERT';
/*
TABLESPACE                           FREE      BYTES   PCT_FREE
------------------------------ ---------- ---------- ----------
TBSALERT                         51380224   52428800         98
*/

 

 

 

create table test100 ( emp_id number, name varchar2(100) )
 tablespace TBSALERT;
/*
Table TEST100 created.
*/


# 프로시져 실행 
 begin
 for i in 1..1000000
 loop
 insert into test100 values ( i, 'any dummy name' );
 end loop;
 commit;
 end;
/*
PL/SQL procedure successfully completed.
*/



# 테이블 스페이스 여유공간 확인.
SELECT df.tablespace_name tablespace, fs.bytes free, df.bytes, 
fs.bytes*100/ df.bytes PCT_FREE
FROM dba_data_files df ,dba_free_space fs
WHERE df.tablespace_name = fs.tablespace_name
AND df.tablespace_name = 'TBSALERT';
/*
TABLESPACE                           FREE      BYTES   PCT_FREE
------------------------------ ---------- ---------- ----------
TBSALERT                         22020096   52428800         42
*/


delete from test100;
commit;
/*
1,000,000 rows deleted.
Commit complete.
*/


# 테이블 스페이스 여유공간 확인. 데이터를 삭제했지만 아직도 사용 공간이 반환되지 않았다.
SELECT df.tablespace_name tablespace, fs.bytes free, df.bytes, 
fs.bytes*100/ df.bytes PCT_FREE
FROM dba_data_files df ,dba_free_space fs
WHERE df.tablespace_name = fs.tablespace_name
AND df.tablespace_name = 'TBSALERT';
/*
TABLESPACE                           FREE      BYTES   PCT_FREE
------------------------------ ---------- ---------- ----------
TBSALERT                         22020096   52428800         42
*/

 

 

 

--Create a Segment Advisor Task
 DECLARE
 tname VARCHAR2(128) := 'my_seg_task3';
 tname_desc VARCHAR2(128) := 'Get shrink advice for segments in TBSALERT';
 task_id NUMBER;
 object_id NUMBER;
 objectname VARCHAR2(100);
 objecttype VARCHAR2(100);
 BEGIN
 dbms_advisor.create_task('Segment Advisor', task_id,tname,tname_desc,NULL);---select * from DBA_ADVISOR_DEFINITIONS
 dbms_advisor.create_object(tname,'TABLESPACE','TBSALERT',' ',' ',NULL, ' ',object_id) ;
 dbms_advisor.set_task_parameter(tname,'RECOMMEND_ALL','TRUE');
 END;
/*
PL/SQL procedure successfully completed.
*/

 

 

 

다른 테이블 스페이스에 적용하려면 붉은색 부분만 변경하면 됩니다.

 

 

 

 

# Execute the task.
DECLARE
tname VARCHAR2(128) := 'my_seg_task3';
BEGIN
dbms_advisor.EXECUTE_TASK(tname);
END;
/*
PL/SQL procedure successfully completed.
*/



SELECT DESCRIPTION FROM dba_advisor_tasks WHERE TASK_NAME='my_seg_task3';
/*
DESCRIPTION                                       
--------------------------------------------------
Get shrink advice for segments in TBSALERT
*/

 

 

 

# After that you run some queries to give you recommendation from Oracle what you have to do.
SELECT f.task_name, attr1, attr2, message
FROM dba_advisor_findings f, dba_advisor_objects o
 WHERE f.task_name = o.task_name 
 AND f.object_id = o.object_id 
 AND f.task_name = 'my_seg_task3';
/*
ATTR1           ATTR2           MESSAGE                                                                                                       
--------------- --------------- --------------------------------------------------------------------------------------------------------------
PDB10_ADMIN     TEST100         Enable row movement of the table PDB10_ADMIN.TEST100 and perform shrink, estimated savings is 24140078 bytes. 
*/



# This is in order to keep the row ID without any changing.
alter table test100 enable row movement;
/*
Table TEST100 altered.
*/



ALTER TABLE test100 SHRINK SPACE;
/*
Table TEST100 altered.
*/



SELECT df.tablespace_name tablespace, fs.bytes free, df.bytes, 
fs.bytes*100/ df.bytes PCT_FREE
FROM dba_data_files df ,dba_free_space fs
WHERE df.tablespace_name = fs.tablespace_name
AND df.tablespace_name = 'TBSALERT';
/*
TABLESPACE                           FREE      BYTES   PCT_FREE
------------------------------ ---------- ---------- ----------
TBSALERT                         51314688   52428800     97.875
*/