Home » RDBMS Server » Performance Tuning » Interpreting tuning task Report (Oracle 10.2.0.4,AIX)
Interpreting tuning task Report [message #453403] |
Wed, 28 April 2010 09:44 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
Re: Interpreting tuning task Report [message #453730 is a reply to message #453403] |
Fri, 30 April 2010 07:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
Goto Forum:
Current Time: Thu Sep 05 23:40:55 CDT 2024
|