OraFAQ Forum: Performance Tuning » DBMS SQL TUNE Problem

Home » RDBMS Server » Performance Tuning » DBMS SQL TUNE Problem (Oracle 10.2.0.4/AIX 5)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DBMS SQL TUNE Problem [message #452920]

Mon, 26 April 2010 04:43

dbcop
Messages: 37
Registered: September 2006
Location: india

Member

Hi

I am trying to run dbms_sqltune package on a specific sql . DB version is 10.2.4.

I did the following steps

Step1.
SQL> DECLARE
2 my_task_name VARCHAR2 (30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := 'SELECT T_00 . "KNUMA_TEMP" , T_00 . "VBELN" , T_00 . "AEDAT" , T_00 .
6 "ERDAT" , T_00 . "POSNR" , T_00 . "MEINS" , T_00 . "NETPR" , T_00 .
7 "WAERK" , T_00 . "ABGRU" , T_00 . "GEWEI" , T_00 . "LGORT" , T_00 .
8 "VSTEL" , T_00 . "VRKME" , T_00 . "ROUTE" , T_00 . "WERKS" , T_00 .
9 "MATNR" , T_00 . "MATKL" , T_00 . "VGPOS" , T_00 . "VGBEL" , T_00 .
10 "PRODH" , T_00 . "FAKSP" , T_01 . "KUNNR" , T_01 . "VSBED" , T_01 .
11 "VBTYP" , T_01 . "ERNAM" , T_01 . "VKORG" , T_01 . "AUART" , T_01 .
"AUGRU" , T_01 . "BNDDT" , T_01 . "ANGDT" , T_01 . "KVGR5" , T_01 .
12 13 "KVGR4" , T_01 . "KVGR3" , T_01 . "KVGR2" , T_01 . "KVGR1" , T_01 .
14 "VKGRP" , T_01 . "VKBUR" , T_01 . "AUDAT" , T_01 . "VGTYP" , T_01 .
15 "SPART" , T_01 . "VTWEG" , T_01 . "VDATU" , T_01 . "AUTLF" , T_01 .
16 "LIFSK" , T_01 . "FAKSK" , T_01 . "VBELN" FROM "SAPR3"."VBAP" T_00 , "SAPR3"."VBAK"
T_01 WHERE ( T_01 . "MANDT" = :A0 AND T_01 . "VBELN" = T_00 . "VBELN"
17 18 ) AND T_00 . "MANDT" = :A1 AND T_00 . "ERDAT" BETWEEN :A2 AND :A3';
19 my_task_name := dbms_sqltune.create_tuning_task (sql_text=> my_sqltext,
20 bind_list => sql_binds (anydata.convertnumber (9)),
21 user_name => 'SYS',
22 scope => 'COMPREHENSIVE',
time_limit => 60,
23 24 task_name => 'm41b_tuning_task',
25 description => 'Tuning Task'
26 );
27 END;
28 /

Step2:
BEGIN
dbms_sqltune.execute_tuning_task (task_name => 'm41b_tuning_task');
END;
/ 2 3 4

PL/SQL procedure successfully completed.

Step3:
SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'm41b_tuning_task';

STATUS
-----------
COMPLETED

SQL> SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'm41b_sql_tuning_task')
FROM DUAL;

SQL> ERROR:
ORA-13605: The specified task or object m41b_sql_tuning_task does not exist for the current user.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ADVISOR", line 4744
ORA-06512: at "SYS.DBMS_SQLTUNE", line 1082
ORA-06512: at line 1

But I see the task is owned by SYS user
SQL> select task_name,owner from dba_advisor_log where owner in ('SYS','SAPR3') and task_name like'm41%';

TASK_NAME OWNER
------------------------------ ------------------------------
m41b_tuning_task SYS

Please help me resolving this.

Thanks

Report message to a moderator

Re: DBMS SQL TUNE Problem [message #452965 is a reply to message #452920]

Mon, 26 April 2010 10:47

mherlindo
Messages: 16
Registered: April 2010
Location: México

Junior Member

Are you executing DBMS_SQLTUNE.REPORT_TUNING_TASK with SYS account?

Report message to a moderator

Previous Topic:	Tuning query having not exists clause.
Next Topic:	DBMS SQL Tuning Advisor

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 25 14:17:10 CST 2024