Re: QSM-00781 on DBMS_ADVISOR.TUNE_MVIEW

Robbert van der Hoorn 寫道：

> 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

Thanks for your investigation.

QSM-00781 is documented by the lovely oerr utility. (Don't know why this is not included this on the Windows version.) The documentation is not very useful however.

[oracle_at_oracleserver ~]$ oerr qsm 781
00781, 00000, "the specified value %s is invalid for parameter %s. Valid keywords are %s"
// *Cause: The user attempted to set a task parameter using invalid data.
// *Action: Adjust the data value and retry the operation

BTW, I am Chinese user. And the database character set is UTF8.

Result of your suggested query:

SQL> select parameter_name, parameter_value from dba_advisor_def_parameters
  2 where advisor_name like 'Tune%' and parameter_name like 'EXEC%';

EXECUTION_TYPE                 FULL

Not my problem :) Received on Sun Oct 08 2006 - 21:00:55 CDT