Monday, January 19, 2009

ORA-13600, QSM-00775, ORA-06512 when running DBMS_ADVISOR

Error Description
When using DBMS_ADVISOR on a table in SYS or SYSTEM schema, the following errors returned.

ORA-13600: error encountered in Advisor
QSM-00794: the statement can not be stored due to a violation of the invalid table reference filter

Below is an example.
SQL> conn system/s
Connected.

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

BEGIN
*
ERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-00775: the specified SQL statement cannot be stored in the workload due to invalid table references
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 1501
ORA-06512: at "SYS.WRI$_ADV_SQLACCESS_ADV", line 176
ORA-06512: at "SYS.PRVT_ADVISOR", line 2594
ORA-06512: at "SYS.DBMS_ADVISOR", line 726
ORA-06512: at line 2

SQL> create table emp(empno number);

Table created.

SQL> BEGIN
DBMS_ADVISOR.quick_tune(
advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
task_name => 'emp_quick_tune',
attr1 => 'SELECT * FROM emp WHERE EMPNO = 7788');
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 1501
ORA-06512: at "SYS.WRI$_ADV_SQLACCESS_ADV", line 176
ORA-06512: at "SYS.PRVT_ADVISOR", line 2594
ORA-06512: at "SYS.DBMS_ADVISOR", line 726
ORA-06512: at line 2

Cause of the Problem
The quick_tune procedure performs an analysis and generates recommendations for a single SQL statement.
SQL Access Advisor maintains an internal list of non-tunable tables regardless of the contents of the INVALID_TABLE_LIST parameter.
The table owned by SYS, SYSTEM or any other pre-defined Oracle schema can be tuned and hence will return error.

Solution of The problem

Create the table in other schama rather than SYS, SYSTEM or any other pre-defined Oracle schema and run quick_tune procedure.

SQL> create table shaik.emp(empno number);
Table created.

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


PL/SQL procedure successfully completed.

No comments:

Post a Comment