Question Re: Using PL/SQL in SQL Command Editor__ORA-13639 SQL_TUNING_ADVISOR in Technology Q&A

Using PL/SQL in SQL Command Editor__ORA-13639 SQL_TUNING_ADVISOR

hannover_sap-basis — Thu, 01 Mar 2012 12:20:01 GMT

Hello,

when i use the SQL Tuning Advisor in dbacockpit the SQL Tune Job Fails With ORA-13639.

To fix this in sqlplus i Increased the job "Time Limit" by using the below script (Oracle Support Automatic SQL Tune Job Fails With ORA-13639 [ID 1363111.1]):

BEGIN

DBMS_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(

parameter => 'TIME_LIMIT', value => '7200'

);

END;

When i try to execute this PL/SQL statement in the SQL command Editor i get a parsing Error.

How can ii fix this failure in the SAP- environment?

kind regards

Lutz

Re: Using PL/SQL in SQL Command Editor__ORA-13639 SQL_TUNING_ADVISOR

Former Member — Thu, 01 Mar 2012 13:28:11 GMT

Hi Lutz,

Execute the PL/SQL statement on SQLPlus at the operating system level, not on "SQL Command Editor" on SAP

Best regards,

Orkun Gedik

Re: Using PL/SQL in SQL Command Editor__ORA-13639 SQL_TUNING_ADVISOR

hannover_sap-basis — Thu, 01 Mar 2012 13:32:14 GMT

Hi Orkun,

i have doen this but the Oracle error ORA-13639 in dbacockpit still occurs.

kind regards,

Lutz

Re: Using PL/SQL in SQL Command Editor__ORA-13639 SQL_TUNING_ADVISOR

Former Member — Thu, 01 Mar 2012 13:34:53 GMT

>> parameter => 'TIME_LIMIT', value => '7200'

Then try to increase this value to 21600, for example.

Best regards,

Orkun Gedik

Re: Using PL/SQL in SQL Command Editor__ORA-13639 SQL_TUNING_ADVISOR

hannover_sap-basis — Thu, 01 Mar 2012 13:40:47 GMT

Hi Orkun;

when i increase the time_limit on operating system level and then execute the "SQL Advisor" the error still exists. Is there a possibility to execute the PL/SQL- Statent in the SAP- Evironment?

kind regards

Lutz

Re: Using PL/SQL in SQL Command Editor__ORA-13639 SQL_TUNING_ADVISOR

Former Member — Thu, 01 Mar 2012 13:58:28 GMT


Hi Orkun;
> when i increase the time_limit on operating system level and then execute the "SQL Advisor" the error still exists. Is there a possibility to execute the PL/SQL- Statent in the SAP- Evironment?
> 
> kind regards
> Lutz

If you executed the DBMS_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER script on Sqlplus, it will be affect the SAP side, as well. So, you don't need to execute this statement, at SAP level.

The thing you stuck is the time-out error. So, you should configure the time-out value long enough.

One another way is; if you would like to see the statement analysis report for an sql statement, executed previously, you can follow the path DBACOCKPIT -> Performance -> SQL Statement Analysis -> Shared Cursor Cache -> Select the statement on the list -> "Analysis Report"

Best regards,

Orkun Gedik

Re: Using PL/SQL in SQL Command Editor__ORA-13639 SQL_TUNING_ADVISOR

hannover_sap-basis — Thu, 01 Mar 2012 14:09:59 GMT

Hi Orkun,

the "SQL Tuning Advisor" and the "Analysis Report" are two different reports.

kind regards

Lutz

Re: Using PL/SQL in SQL Command Editor__ORA-13639 SQL_TUNING_ADVISOR

Former Member — Thu, 01 Mar 2012 15:21:28 GMT


Hi Orkun,
> the "SQL Tuning Advisor" and the "Analysis Report" are two different reports.
> 
> 
> kind regards
> Lutz

Of course, they are different. I just wanted to show another way to have statistical values about the statement. They are not same, but you can have information about the statement.

Best regards,

Orkun Gedik