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, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)