Home » RDBMS Server » Server Administration » SQL Advisor Job failed
| SQL Advisor Job failed [message #204861] |
Wed, 22 November 2006 07:32  |
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 #205544 is a reply to message #204861] |
Sat, 25 November 2006 13:38  |
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 #205794 is a reply to message #204861] |
Mon, 27 November 2006 10:19 |
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 #639482 is a reply to message #488929] |
Wed, 08 July 2015 08:59 |
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 #639488 is a reply to message #639483] |
Wed, 08 July 2015 10:14 |
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
|
|
|
|
Goto Forum:
Current Time: Sat Feb 01 21:04:21 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
