Oracle DBMS_ADVISOR.SQLACCESS_ADVISOR

Oracle QUICK_TUNE
Oracle quick tune
SQLACCESS_ADVISOR


SQL> connect scott/tiger
Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE <<<<<<<<<<<<<<,---------------
SALGRADE TABLE
T TABLE



from sys



SQL> connect / as sysdba
Connected.
SQL>
SQL>
SQL>
SQL>
SQL> BEGIN
DBMS_ADVISOR.quick_tune(
advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
task_name => 'emp_quick_tune',
attr1 => 'SELECT * FROM scott.emp WHERE EMPNO = 7788');
END;
/

PL/SQL procedure successfully completed.


SQL> SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_script('emp_quick_tune') AS script
FROM dual;
SET PAGESIZE 24


SQL> SQL> 2
SCRIPT
--------------------------------------------------------------------------------
Rem SQL Access Advisor: Version 11.2.0.1.0 - Production
Rem
Rem Username: SYS
Rem Task: emp_quick_tune
Rem Execution date:
Rem

/* RETAIN INDEX "SCOTT"."PK_EMP" */



or



VARIABLE task_name VARCHAR2(255);
VARIABLE sql_stmt VARCHAR2(4000);
exec :sql_stmt := 'SELECT COUNT(*) FROM scott.emp WHERE empno = 999';
exec :task_name := 'ANUJ_QUICKTUNE_TASK';
exec DBMS_advisor.quick_tune (dbms_advisor.sqlaccess_advisor, :task_name, :sql_stmt);


SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_script('ANUJ_QUICKTUNE_TASK') AS script FROM dual;
SET PAGESIZE 24



======================================




begin
-- a task and a workload will be created then the task will be executed
DBMS_ADVISOR.QUICK_TUNE(
ADVISOR_NAME => DBMS_ADVISOR.SQLACCESS_ADVISOR,
TASK_NAME => 'anuj_quick_tune',
ATTR1 => 'select * from scott.emp where EMPNO=123');

end;



SQL> begin
-- a task and a workload will be created then the task will be executed
DBMS_ADVISOR.QUICK_TUNE(
ADVISOR_NAME => DBMS_ADVISOR.SQLACCESS_ADVISOR,
TASK_NAME => 'anuj_quick_tune',
ATTR1 => 'select * from scott.emp where EMPNO=123');

end;
/

PL/SQL procedure successfully completed.



col ERROR_MESSAGE format a50
select TASK_NAME, STATUS, PCT_COMPLETION_TIME, ERROR_MESSAGE from DBA_ADVISOR_LOG where TASK_NAME ='anuj_quick_tune';

TASK_NAME STATUS PCT_COMPLETION_TIME ERROR_MESSAGE
------------------------------ ----------- ------------------- --------------------------------------------------
anuj_quick_tune COMPLETED 100

1 row selected.


SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.GET_TASK_SCRIPT('anuj_quick_tune') AS script FROM dual;

SCRIPT
--------------------------------------------------------------------------------
Rem SQL Access Advisor: Version 11.2.0.1.0 - Production
Rem
Rem Username: SYS
Rem Task: anuj_quick_tune
Rem Execution date:
Rem

/* RETAIN INDEX "SCOTT"."SYS_C0022543" */



1 row selected.



-- if you need to terminate the executing task (may be time consuming)

exec DBMS_ADVISOR.CANCEL_TASK(TASK_NAME =>'anuj_quick_tune');



select REC_ID, RANK, BENEFIT, TYPE "Recommendation Type"
from DBA_ADVISOR_RECOMMENDATIONS
where TASK_NAME = 'anuj_quick_tune'
order by RANK;


REC_ID RANK BENEFIT Recommendation Type
---------- ---------- ---------- ------------------------------
1 1 0 RETAINS_ONLY

1 row selected.




select REC_ID, ACTION_ID, SUBSTR(COMMAND,1,30) AS COMMAND
from USER_ADVISOR_ACTIONS where TASK_NAME = 'anuj_quick_tune'
ORDER BY rec_id, action_id;



select SQL_ID, REC_ID, PRECOST, POSTCOST,(PRECOST-POSTCOST)*100/PRECOST AS PERCENT_BENEFIT
from dba_ADVISOR_SQLA_WK_STMTS
where TASK_NAME = 'anuj_quick_tune'

SQL_ID REC_ID PRECOST POSTCOST PERCENT_BENEFIT
------------- ---------- ---------- ---------- ---------------
3cx27884dw7ar 1 2 2 0

1 row selected.

DBMS_ADVISOR.quick_tune error

ORA-13600, QSM-00794, ORA-06512 when running DBMS_ADVISOR
ORA-13600: error encountered in Advisor

solution
Create the table in other schama rather than SYS, SYSTEM for DBMS_ADVISOR.quick_tune



SQL> show user
USER is "SYS"


SQL> create table anuj(x number)
/
Table created.


SQL> BEGIN
DBMS_ADVISOR.quick_tune(
advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
task_name => 'anuj_quick_tune',
attr1 => 'SELECT * FROM anuj WHERE x = 88');
END;
/
BEGIN
*
ERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-00794: the statement can not be stored due to a violation of the invalid table reference filter
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 1808
ORA-06512: at "SYS.WRI$_ADV_SQLACCESS_ADV", line 180
ORA-06512: at "SYS.PRVT_ADVISOR", line 3636
ORA-06512: at "SYS.DBMS_ADVISOR", line 711
ORA-06512: at line 2