SET SERVEROUTPUT ON;
BEGIN
DBMS_ADVISOR.quick_tune(
advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
task_name => 'CONSULTA_SQL_0001_20130319',
attr1 => 'SELECT /*+ INDEX_ASC(T0 PROG_DTSUL##PRGDTSL_ID) */ PROGRESS_RECID
FROM emsfnd.PROG_DTSUL T0 WHERE ((upper(COD_PROG_DTSUL) = upper(:1)))
order by upper(COD_PROG_DTSUL)');
END;
/
SELECT * FROM dba_advisor_recommendations WHERE task_name = 'CONSULTA_SQL_0001_20130319';
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('CONSULTA_SQL_0001_20130319'), 'BACKUP', 'CONSULTA_SQL_0001_20130319.sql');
Rem SQL Access Advisor: Version 10.2.0.5.0 - Production
Rem
Rem Username: SYS
Rem Task: CONSULTA_SQL_0001_20130319
Rem Execution date: 19/03/2013 10:09
Rem
/* RETAIN INDEX "EMSFND"."PROG_DTSUL##PRGDTSL_ID" */
BEGIN
DBMS_ADVISOR.quick_tune(
advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
task_name => 'CONSULTA_SQL_0002_20130319',
attr1 => 'SELECT /*+ INDEX_ASC(T0 PROCEDIMENTO##PRCDMNT_ID) */ PROGRESS_RECID
FROM emsfnd.PROCEDIMENTO T0 WHERE ((upper(COD_PROCED) = upper(:1)))
order by upper(COD_PROCED)');
END;
/
SELECT * FROM dba_advisor_recommendations WHERE task_name = 'CONSULTA_SQL_0002_20130319';
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('CONSULTA_SQL_0002_20130319'), 'BACKUP', 'CONSULTA_SQL_0002_20130319.sql');
Rem SQL Access Advisor: Version 10.2.0.5.0 - Production
Rem
Rem Username: SYS
Rem Task: CONSULTA_SQL_0002_20130319
Rem Execution date: 19/03/2013 10:10
Rem
/* RETAIN INDEX "EMSFND"."PROCEDIMENTO##PRCDMNT_ID" */
DECLARE
l_object_id NUMBER;
BEGIN
-- Create a segment advisor task for the MAXIMO.WORKORDER table.
DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => 'WORKORDER_SEGMENT_ADVISOR',
task_desc => 'Segment Advisor For WORKORDER');
DBMS_ADVISOR.create_object (
task_name => 'WORKORDER_SEGMENT_ADVISOR',
object_type => 'TABLE',
attr1 => 'MAXIMO',
attr2 => 'WORKORDER',
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);
DBMS_ADVISOR.set_task_parameter (
task_name => 'WORKORDER_SEGMENT_ADVISOR',
parameter => 'RECOMMEND_ALL',
value => 'TRUE');
DBMS_ADVISOR.execute_task(task_name => 'WORKORDER_SEGMENT_ADVISOR');
-- Create a segment advisor task for the USERS tablespace.
DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => 'USERS_SEGMENT_ADVISOR',
task_desc => 'Segment Advisor For USERS');
DBMS_ADVISOR.create_object (
task_name => 'USERS_SEGMENT_ADVISOR',
object_type => 'TABLESPACE',
attr1 => 'USERS',
attr2 => NULL,
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);
DBMS_ADVISOR.set_task_parameter (
task_name => 'USERS_SEGMENT_ADVISOR',
parameter => 'RECOMMEND_ALL',
value => 'TRUE');
DBMS_ADVISOR.execute_task(task_name => 'USERS_SEGMENT_ADVISOR');
END;
/
-- Display the findings.
SET LINESIZE 250
COLUMN task_name FORMAT A20
COLUMN object_type FORMAT A20
COLUMN schema FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_name FORMAT A30
COLUMN message FORMAT A40
COLUMN more_info FORMAT A40
SELECT f.task_name,
f.impact,
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name IN ('WORKORDER_SEGMENT_ADVISOR', 'USERS_SEGMENT_ADVISOR')
ORDER BY f.task_name, f.impact DESC;