Home » RDBMS Server » Server Administration » SQL Advisor Job failed
SQL Advisor Job failed [message #204861] Wed, 22 November 2006 07:32 Go to next message
skoskos
Messages: 19
Registered: November 2005
Location: Hell(as)
Junior Member
Hi ,
Trying to execute the following pl/sql block as user SYS in a Oracle10g v.2 database...

DECLARE taskname varchar2(30);task_desc varchar2(256);task_or_template varchar2(30);task_id number;wkld_name varchar2(30);saved_rows number;failed_rows number;num_found number;BEGINtask_id := 0;saved_rows := 0;failed_rows := 0;taskname := 'SQLACCESS6026890';task_desc := 'SQL Access Advisor';task_or_template := 'null';wkld_name := 'SQLACCESS6026890_wkld';/* Create Task */dbms_advisor.create_task(DBMS_ADVISOR.SQLACCESS_ADVISOR,task_id,taskname,task_desc,task_or_template);/* Reset Task */dbms_advisor.reset_task(taskname);/* Create Workload */select count(*) into num_found from user_advisor_sqlw_sum where workload_name = wkld_name;IF num_found = 0 THENdbms_advisor.create_sqlwkld(wkld_name,null);END IF;/* Reset Workload */dbms_advisor.reset_sqlwkld(wkld_name);/* Link Workload to Task */select count(*) into num_found from user_advisor_sqla_wk_map where task_name = taskname and workload_name = wkld_name;IF num_found = 0 THENdbms_advisor.add_sqlwkld_ref(taskname,wkld_name);END IF;/* Set Workload Parameters */dbms_advisor.set_sqlwkld_parameter(wkld_name,'ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);dbms_advisor.set_sqlwkld_parameter(wkld_nam e,'MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);dbms_advisor.set_sqlwkld_parameter(wkld_name,'SQL_LIMIT',DBMS_ADVISOR.ADVISOR_UNLIMITED) ;dbms_advisor.set_sqlwkld_parameter(wkld_name,'ORDER_LIST','PRIORITY,OPTIMIZER_COST');dbms_advisor.set_sqlwkld_parameter(wkld_name,'U SERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);dbms_advisor.set_sqlwkld_parameter(wkld_name,'VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSE D);dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);dbms_advisor.set_sqlwkld_parameter( wkld_name,'INVALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_USERNAME_LIST',DBM S_ADVISOR.ADVISOR_UNUSED);dbms_advisor.set_sqlwkld_parameter(wkld_name,'INVALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);dbms_adviso r.set_sqlwkld_parameter(wkld_name,'VALID_SQLSTRING_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);dbms_advisor.set_sqlwkld_parameter(wkld_name,'I NVALID_SQLSTRING_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);dbms_advisor.set_sqlwkld_parameter(wkld_name,'JOURNALING','9');dbms_advisor.set_s qlwkld_parameter(wkld_name,'DAYS_TO_EXPIRE','30');dbms_advisor.import_sqlwkld_sqlcache(wkld_name,'REPLACE',2,saved_rows,failed_rows); /* Set Task Parameters */dbms_advisor.set_task_parameter(taskname,'EXECUTION_TYPE','FULL');dbms_advisor.set_task_parameter(taskname,'MODE','COMPREHENSIVE'); dbms_advisor.set_task_parameter(taskname,'STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED);dbms_advisor.set_task_parameter(taskname,'D ML_VOLATILITY','TRUE');dbms_advisor.set_task_parameter(taskname,'ORDER_LIST','PRIORITY,OPTIMIZER_COST');dbms_advisor.set_task_paramet er(taskname,'WORKLOAD_SCOPE','FULL');dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);dbm s_advisor.set_task_parameter(taskname,'DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);dbms_advisor.set_task_parameter(taskname,'DEF_MV IEW_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED); dbms_advisor.set_task_parameter(taskname,'DEF_MVLOG_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);dbms_advisor.set_task_parameter(taskname ,'CREATION_COST','TRUE');dbms_advisor.set_task_parameter(taskname,'EVALUATION_ONLY','FALSE');dbms_advisor.set_task_parameter(taskname ,'JOURNALING','9');dbms_advisor.set_task_parameter(taskname,'DAYS_TO_EXPIRE','30');/* Execute Task */dbms_advisor.execute_task(taskname);END;

I get the following error messages...
ORA-13600:error encountered in Advisor string
ORA-13635:The value provided for parameter string cannot be converted to a number. The parameter string is "ADJUSTED_SCALEUP_GREEN_THRESH"
ORA-06512: in "SYS.PRVT_ADVISOR", line 3902
ORA-06512: in "SYS.DBMS_ADVISOR", line 102
ORA-06512: in line 21


Whowwww!!!
What can I get rid of these errors?????

Thanks , a lot for time and interest!!!
Simon
Re: SQL Advisor Job failed [message #205296 is a reply to message #204861] Fri, 24 November 2006 03:25 Go to previous messageGo to next message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member
The value provided for parameter string cannot be converted to a number
Re: SQL Advisor Job failed [message #205544 is a reply to message #204861] Sat, 25 November 2006 13:38 Go to previous messageGo to next message
skoskos
Messages: 19
Registered: November 2005
Location: Hell(as)
Junior Member
Hi ,
Thanks a lot for your interest....

The pl/sql block - i pasted in my previous post- was generated automatically in EM 10g Advisor Job....
However...which is the parameter string that cannot be converted to a number ...????

Regards,
Simon
Re: SQL Advisor Job failed [message #205681 is a reply to message #204861] Mon, 27 November 2006 01:52 Go to previous messageGo to next message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member
this one maybe : The parameter string is "ADJUSTED_SCALEUP_GREEN_THRESH"
Re: SQL Advisor Job failed [message #205794 is a reply to message #204861] Mon, 27 November 2006 10:19 Go to previous messageGo to next message
skoskos
Messages: 19
Registered: November 2005
Location: Hell(as)
Junior Member
Hi ,
Yes that it is ..but what value to set and where..????
The pl/sql block - i pasted in my previous post- was generated automatically in EM 10g Advisor Job....

Thanks , a lot
Simon
Re: SQL Advisor Job failed [message #488929 is a reply to message #205794] Wed, 12 January 2011 05:52 Go to previous messageGo to next message
RCMorilla
Messages: 1
Registered: January 2011
Junior Member
This problem also occurred in my environment, so I did:

export NLS_LANG = "AMERICAN_AMERICA.WE8ISO8859P1"
and performed the task and worked in sqlplus
Re: SQL Advisor Job failed [message #639482 is a reply to message #488929] Wed, 08 July 2015 08:59 Go to previous messageGo to next message
acravo1
Messages: 2
Registered: April 2010
Location: Portugal
Junior Member
The problem persists in cloudcontrol.
This is associated with the following issue:
Bug 12861432 : ADVISOR PARAMETER PROBLEMS WHEN NLS_NUMERIC_CHARACTERS=',.'
Use cloud control to generate the code you want, and copy it to a text editor.
Change the line:
task_or_template VARCHAR2 (30) := 'null';

to this:
task_or_template VARCHAR2 (30) := NULL;

Go to sqlplus and set the environment to the one Oracle wants:
alter session set nls_numeric_characters='.,';

execute the code changed asdescribed above.


Re: SQL Advisor Job failed [message #639483 is a reply to message #639482] Wed, 08 July 2015 09:10 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
THANKS for contributing, but why resurrect this 8+ year old thread?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Re: SQL Advisor Job failed [message #639488 is a reply to message #639483] Wed, 08 July 2015 10:14 Go to previous message
acravo1
Messages: 2
Registered: April 2010
Location: Portugal
Junior Member
Because it's a bug and the bug persists in Cloud Control 12c...
Anyone that checks this forum will see this like I did when I've looked for a solution to the issue

Best regards
Previous Topic: Register Pluggable database listener
Next Topic: NOLOGGING TABLES in DATAGUARD
Goto Forum:
  


Current Time: Sat Dec 03 13:44:12 CST 2016

Total time taken to generate the page: 0.09330 seconds