OraFAQ Forum: Performance Tuning » Interpreting tuning task Report

Home » RDBMS Server » Performance Tuning » Interpreting tuning task Report (Oracle 10.2.0.4,AIX)

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

Interpreting tuning task Report [message #453403]

Wed, 28 April 2010 09:44

dbcop
Messages: 37
Registered: September 2006
Location: india

Member

Hi,

I am trying to run dbms_sqltune package as follows on a specific sql statement . The problem is in interpreting the output obtained from REPORT_TUNING_TASK procedure.

I see the report is incomplete and shows up as Number of Error 1 . Now how do I identify the error code from this . Please help

SQL> DECLARE
2 my_task_name VARCHAR2 (30);
my_sqltext CLOB;
3 4 BEGIN
5 my_sqltext := 'SELECT "MANDT" , "CPIDENT" , "MESTYPE" , "PROCESS" , "TABNAME" , "TABKEY" , "FLDNAME" , "CRETIME" ,
6 "ACTTIME" , "USRNAME" , "CDOBJCL" , "CDOBJID" , "CDCHGNO" , "CDCHGID" FROM "SAPR3"."BDCPV" WHERE "MANDT" = ''990'' AND "MESTYPE" = ''ZEQUIWOM2'' AND "PROCESS" = '' '' AND "CRETIME" <= ''20100428240000'' AND "ACTTIME" <= ''20100428240000'' ORDER BY "MANDT" , "CPIDENT"';
my_task_name := dbms_sqltune.create_tuning_task (sql_text=> my_sqltext,
7 8 user_name => 'SYS',
9 scope => 'COMPREHENSIVE',
time_limit => 60,
10 11 task_name => 'M1_tuning_task',
12 description => 'Tuning Task'
13 );
14 END;
15 /

PL/SQL procedure successfully completed.

SQL> BEGIN
dbms_sqltune.execute_tuning_task (task_name => 'M1_tuning_task');
END;
/ 2 3 4

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'M1_tuning_task')
FROM DUAL; 2

DBMS_SQLTUNE.REPORT_TUNING_TASK('M1_TUNING_TASK')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : M1_tuning_task
Tuning Task Owner : SYS
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 04/28/2010 14:20:30
Completed at : 04/28/2010 14:21:32
Number of Errors : 1

DBMS_SQLTUNE.REPORT_TUNING_TASK('M1_TUNING_TASK')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : 9ur9n7hqufkja
SQL Text : SELECT "MANDT" , "CPIDENT" , "MESTYPE" , "PROCESS" , "TABNAME" ,
"TABKEY" , "FLDNAME" , "CRETIME" ,
"ACTTIME" , "USRNAME" , "CDOBJCL" , "CDOBJID" , "CDCHGNO" ,
"CDCHGID" FROM "SAPR3"."BDCPV" WHERE "MANDT" = '990' AND
"MESTYPE" = 'ZEQUIWOM2' AND "PROCESS" = ' ' AND "CRETIME" <=
'20100428240000' AND "ACTTI

Thanks

Report message to a moderator

Re: Interpreting tuning task Report [message #453417 is a reply to message #453403]

Wed, 28 April 2010 14:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sqltun.htm#i1006953

"Return Values

A CLOB containing the desired report. This means that you have to set the any SQL*Plus 'LONG' and 'LONGCHUNKSIZE' variables so that the report will print in entirety."

post results from sqlplus doing lines below

SET LONG 32000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('M1_TUNING_TASK', 'TEXT', 'TYPICAL', 'FINDINGS', 5) from dual;

Report message to a moderator

Re: Interpreting tuning task Report [message #453730 is a reply to message #453403]

Fri, 30 April 2010 07:28

dbcop
Messages: 37
Registered: September 2006
Location: india

Member

Hi

Thanks for your reply . Following is the output

DBMS_SQLTUNE.REPORT_TUNING_TASK('MP1A_TUNING_TASK')
------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : MP1a_tuning_task
Tuning Task Owner : SYS
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 04/30/2010 12:12:31
Completed at : 04/30/2010 12:13:32
Number of Errors : 1

DBMS_SQLTUNE.REPORT_TUNING_TASK('MP1A_TUNING_TASK')
------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : 871vmtj90yszw
SQL Text : SELECT "MANDT" , "CPIDENT" , "MESTYPE" , "PROCESS" , "TABNAME" ,
"TABKEY" , "FLDNAME" , "CRETIME" , "ACTTIME" , "USRNAME" ,
"CDOBJCL" , "CDOBJID" ,
"CDCHGNO" , "CDCHGID" FROM "SAPR3"."BDCPV" WHERE "MANDT" =
'990' AND "MESTYPE" = 'ZEQUIWOM2' AND "PROCESS" = ' ' AND
"CRETIME"
<= '20100430240000' AND "ACTTIME" <= '20100430240000' ORDER BY
"MANDT" , "CPIDENT"

DBMS_SQLTUNE.REPORT_TUNING_TASK('MP1A_TUNING_TASK')
------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------

-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- The current operation was interrupted because it timed out.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original

DBMS_SQLTUNE.REPORT_TUNING_TASK('MP1A_TUNING_TASK')
------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------
-----------
Plan hash value: 246931361

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15798 | 2206K| 3155 (1)| 00:00:20 |
| 1 | NESTED LOOPS | | 15798 | 2206K| 3154 (1)| 00:00:20 |
|* 2 | INDEX RANGE SCAN | BDCPS~1 | 15694 | 429K| 13 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| BDCP | 1 | 115 | 0 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | BDCP~0 | 1 | | 0 (0)| 00:00:01 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('MP1A_TUNING_TASK')
------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T1"."MANDT"='990' AND "T1"."MESTYPE"='ZEQUIWOM2' AND
"T1"."PROCESS"=' ')
3 - filter("T2"."CRETIME"<='20100430240000' AND
"T2"."ACTTIME"<='20100430240000')
4 - access("T2"."MANDT"='990' AND "T2"."CPIDENT"="T1"."CPIDENT")

DBMS_SQLTUNE.REPORT_TUNING_TASK('MP1A_TUNING_TASK')
------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------
-------------------------------------------------------------------------------

How do I diagnose the following
The current operation was interrupted because it timed out.

Any help is appreciated.

Thanks

[Updated on: Fri, 30 April 2010 07:39]

Report message to a moderator