Executar o Segment Advisor pode ser útil em situações em que há a necessidade de Tuning de segmento, conforme abaixo reportado pelo ADDM.
Recommendation 1: Segment Tuning Estimated benefit is .24 active sessions, 24% of total activity. ---------------------------------------------------------------- Action Run "Segment Advisor" on TABLE "BRUNO_DATA.TECH_DATABASKET" with object ID 164493. Related Object Database object with ID 164493.
Com isso, neste post estarei abordando de maneira simples a execução do mesmo.
O procedimento consiste nas seguintes etapas:
-
Execução da dbms_advisor com os seguintes parâmetros create_task, create_object, set_task_parameter e execute_task;
variable id number; begin declare name varchar2(100); descr varchar2(500); obj_id number; begin name:='BRUNO_DATA_check'; descr:='Segment Advisor Example'; dbms_advisor.create_task ( advisor_name => 'Segment Advisor', task_id => :id, task_name => name, task_desc => descr); dbms_advisor.create_object ( task_name => name, object_type => 'TABLE', attr1 => 'BRUNO_DATA', attr2 => 'TECH_DATABASKET', attr3 => NULL, attr4 => NULL, attr5 => NULL, object_id => obj_id); dbms_advisor.set_task_parameter( task_name => name, parameter => 'recommend_all', value => 'TRUE'); dbms_advisor.execute_task(name); end; end; /
*A variável name é o nome da task.
Verificar a recomendação de ação na view DBA_ADVISOR_RECOMMENDATIONS. SQL> set pages 2000 SQL> set lines 2000 SQL> set long 9999 SQL> select * from DBA_ADVISOR_RECOMMENDATIONS where TASK_NAME='BRUNO_DATA_check'; OWNER REC_ID TASK_ID TASK_NAME EXECUTION_NAME FINDING_ID TYPE RANK ------------------------------ ---------- ---------- ------------------------------ ------------------------------ ---------- ------------------------------ ---------- PARENT_REC_IDS -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- BENEFIT_TYPE -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- BENEFIT ANNOTATION_ FLAGS F ---------- ----------- ---------- - SYS 1 40869 BRUNO_DATA_check 1 Enable row movement of the table BRUNO_DATA.TECH_DATABASKET and perform shrink, estimated savings is 2983436642 bytes. N
SQL> set pages 2000 SQL> set lines 2000 SQL> select * from DBA_ADVISOR_ACTIONS where TASK_NAME='BRUNO_DATA_check'; OWNER TASK_ID TASK_NAME EXECUTION_NAME REC_ID ACTION_ID OBJECT_ID COMMAND COMMAND_ID FLAGS ------------------------------ ---------- ------------------------------ ------------------------------ ---------- ---------- ---------- ---------------------------------------------------------------- ---------- ---------- ATTR1 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ATTR2 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ATTR3 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ATTR4 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ATTR5 ATTR6 NUM_ATTR1 NUM_ATTR2 NUM_ATTR3 NUM_ATTR4 NUM_ATTR5 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- MESSAGE -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- F - SYS 40869 BRUNO_DATA_check 1 1 1 SHRINK SPACE 36 alter table "BRUNO_DATA.TECH_DATABASKET" shrink space alter table "BRUNO_DATA.TECH_DATABASKET" shrink space COMPACT alter table "BRUNO_DATA.TECH_DATABASKET" enable row movement N
Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)