Re: QSM-00781 on DBMS_ADVISOR.TUNE_MVIEW

From: Robbert van der Hoorn <reply_at_forum.only>
Date: Fri, 6 Oct 2006 12:30:45 +0200
Message-ID: <4526306c$0$4517$e4fe514c@news.xs4all.nl>

I'm as puzzled as you are. The example you use does not work at my server, but if you use my example and get the same errors, it's obvious that dual is not the cause of your problem (as you already mentioned). If I use your example I get:

ERROR at line 1:

ORA-13600: error encountered in Advisor
QSM-03113: Cannot tune the MATERIALIZED VIEW statement
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 202
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1075
ORA-06512: at "SYS.DBMS_ADVISOR", line 754
ORA-06512: at line 8

Strange thing is that QSM-00781 is not documented anywhere (at least: I can not find any reference). Another thing I observe is: most DBMS_ADVISOR problems I find on the internet are related to, or posted by people using Chinese or Japanese language, so there may be a problem there (I mean with the database, not those people)!

But I found one possible cause: there is a view called dba_advisor_def_parameters. In this view is a record for PARAMETER_NAME='EXECUTION_TYPE'. PARAMETER_VALUE='FULL' (in my database). Try this so see if your value is the same:

SQL> column PARAMETER_NAME format 'A30';
SQL> column PARAMETER_VALUE format 'A30';
SQL> select parameter_name, parameter_value from dba_advisor_def_parameters 

where advisor_name like 'Tune%' and parameter_name like 'EXEC%';

If this is not 'FULL', there's your problem. (Now we must find how to set this value...)

Please respond when you tried this!

Greetings,

Robbert van der Hoorn
OSA it BV
The Netherlands Received on Fri Oct 06 2006 - 05:30:45 CDT