Home » RDBMS Server » Performance Tuning » DBMS_ADVISOR ORA-13699 (11.2.0.1, Linux and Windows 32 bit)
DBMS_ADVISOR ORA-13699 [message #481335] Tue, 02 November 2010 09:46 Go to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
If I create a quick tuning task for the access advisor, I cannot get the report out:
orcl> exec dbms_advisor.quick_tune(dbms_advisor.sqlaccess_advisor,'quick','select count(n) from t3 where n=20');

PL/SQL procedure successfully completed.

orcl> select dbms_advisor.get_task_report('quick') from dual;
ERROR:
ORA-13699: Advisor feature is not currently implemented.
ORA-06512: at "SYS.PRVT_ADVISOR", line 3147
ORA-06512: at "SYS.DBMS_ADVISOR", line 590
ORA-06512: at line 1



no rows selected

orcl>
Am I missing something? There is nothing relevant in Metalink. It isn't a new feature.

Report message to a moderator

Re: DBMS_ADVISOR ORA-13699 [message #481337 is a reply to message #481335] Tue, 02 November 2010 09:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
works for me
SQL> @quick_tune
SQL> VARIABLE task_name VARCHAR2(255);
SQL> VARIABLE sql_stmt VARCHAR2(4000);
SQL> exec :sql_stmt := 'select count(*) from  item'

PL/SQL procedure successfully completed.

SQL> exec:task_name	:= 'MY_QUICKTUNE_TASK';

PL/SQL procedure successfully completed.

SQL> exec DBMS_advisor.quick_tune (dbms_advisor.sqlaccess_advisor, :task_name, :sql_stmt);

PL/SQL procedure successfully completed.

Report message to a moderator

Re: DBMS_ADVISOR ORA-13699 [message #481343 is a reply to message #481337] Tue, 02 November 2010 11:34 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
Thank you for looking at this, but it doesn't get me any further:
jw> create table item as select * from all_users;

Table created.

jw> VARIABLE task_name VARCHAR2(255);
jw> VARIABLE sql_stmt VARCHAR2(4000);
jw> exec :sql_stmt := 'select count(*) from  item'

PL/SQL procedure successfully completed.

jw>
jw> exec:task_name      := 'MY_QUICKTUNE_TASK';

PL/SQL procedure successfully completed.

jw> exec DBMS_advisor.quick_tune (dbms_advisor.sqlaccess_advisor, :task_name, :sql_stmt);

PL/SQL procedure successfully completed.

jw>
jw> select dbms_advisor.get_task_report('MY_QUICKTUNE_TASK') from dual;
ERROR:
ORA-13699: Advisor feature is not currently implemented.
ORA-06512: at "SYS.PRVT_ADVISOR", line 3147
ORA-06512: at "SYS.DBMS_ADVISOR", line 590
ORA-06512: at line 1



no rows selected

jw> variable rep varchar2(4000);
jw> exec :rep:=dbms_advisor.get_task_report('MY_QUICKTUNE_TASK');
BEGIN :rep:=dbms_advisor.get_task_report('MY_QUICKTUNE_TASK'); END;

*
ERROR at line 1:
ORA-13699: Advisor feature is not currently implemented.
ORA-06512: at "SYS.PRVT_ADVISOR", line 3147
ORA-06512: at "SYS.DBMS_ADVISOR", line 590
ORA-06512: at line 1


jw>

Report message to a moderator

Re: DBMS_ADVISOR ORA-13699 [message #481344 is a reply to message #481343] Tue, 02 November 2010 11:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I do not see GET_TASK_REPORT as existing for DBMS_ADVISOR
bcm@bcm-laptop:~$ grep PROC dbms_advisor.lis 
PROCEDURE ADD_SQLWKLD_REF
PROCEDURE ADD_SQLWKLD_STATEMENT
PROCEDURE ADD_STS_REF
PROCEDURE CANCEL_TASK
PROCEDURE CHECK_PRIVS
PROCEDURE CHECK_READ_PRIVS
PROCEDURE COPY_SQLWKLD_TO_STS
PROCEDURE CREATE_FILE
PROCEDURE CREATE_OBJECT
PROCEDURE CREATE_OBJECT
PROCEDURE CREATE_SQLWKLD
PROCEDURE CREATE_TASK
PROCEDURE CREATE_TASK
PROCEDURE CREATE_TASK
PROCEDURE DELETE_DIRECTIVE
PROCEDURE DELETE_SQLWKLD
PROCEDURE DELETE_SQLWKLD_REF
PROCEDURE DELETE_SQLWKLD_STATEMENT
PROCEDURE DELETE_SQLWKLD_STATEMENT
PROCEDURE DELETE_STS_REF
PROCEDURE DELETE_TASK
PROCEDURE EXECUTE_TASK
PROCEDURE GET_ACCESS_ADVISOR_DEFAULTS
PROCEDURE GET_REC_ATTRIBUTES
PROCEDURE IMPLEMENT_TASK
PROCEDURE IMPORT_SQLWKLD_SCHEMA
PROCEDURE IMPORT_SQLWKLD_SQLCACHE
PROCEDURE IMPORT_SQLWKLD_STS
PROCEDURE IMPORT_SQLWKLD_STS
PROCEDURE IMPORT_SQLWKLD_SUMADV
PROCEDURE IMPORT_SQLWKLD_USER
PROCEDURE INSERT_DIRECTIVE
PROCEDURE INTERRUPT_TASK
PROCEDURE MARK_RECOMMENDATION
PROCEDURE QUICK_TUNE
PROCEDURE RESET_SQLWKLD
PROCEDURE RESET_TASK
PROCEDURE RESUME_TASK
PROCEDURE SETUP_REPOSITORY
PROCEDURE SETUP_USER_ENVIRONMENT
PROCEDURE SET_DEFAULT_SQLWKLD_PARAMETER
PROCEDURE SET_DEFAULT_SQLWKLD_PARAMETER
PROCEDURE SET_DEFAULT_TASK_PARAMETER
PROCEDURE SET_DEFAULT_TASK_PARAMETER
PROCEDURE SET_SQLWKLD_PARAMETER
PROCEDURE SET_SQLWKLD_PARAMETER
PROCEDURE SET_TASK_PARAMETER
PROCEDURE SET_TASK_PARAMETER
PROCEDURE TUNE_MVIEW
PROCEDURE UPDATE_DIRECTIVE
PROCEDURE UPDATE_OBJECT
PROCEDURE UPDATE_REC_ATTRIBUTES
PROCEDURE UPDATE_SQLWKLD_ATTRIBUTES
PROCEDURE UPDATE_SQLWKLD_STATEMENT
PROCEDURE UPDATE_SQLWKLD_STATEMENT
PROCEDURE UPDATE_TASK_ATTRIBUTES

Report message to a moderator

Re: DBMS_ADVISOR ORA-13699 [message #481376 is a reply to message #481344] Tue, 02 November 2010 14:43 Go to previous message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
dbms_advisor.get_task_report definitely exists! And is documented (even back in 10g), it just doesn't seem to report anything following dbms_advisor.quick_tune. At least, not for me. How are you getting your reports out? dbms_advisor.quick_tune doesn't seem to populate dba_advisor_recommendations, and dbms_advisor.get_task_script doesn't give me anything either.

Report message to a moderator

Previous Topic: Query
Next Topic: How do I justify using different disks (2 merged)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Nov 22 07:30:43 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.