Home » RDBMS Server » Server Administration » SQL Tunning Advisor
SQL Tunning Advisor [message #207915] Thu, 07 December 2006 05:56 Go to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
Hello All

I am testing the automatic SQL tunning

STEP1- i had created two tables dept1,emp1
without referential integrity
without indexes

STEP2-
DECLARE
tuning_task VARCHAR2(30);
sqltext varchar2(1000);
BEGIN
sqltext := 'select dname,count(ename) from emp1,dept1 where emp1.deptno=dept1.deptno;';
tuning_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => sqltext,
user_name => 'SYSTEM',
scope => 'COMPREHENSIVE',
time_limit => 30,
task_name => 'my_tuning_task',
description => 'Tuning effort for counting customer orders');
END;

STEP3- BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'my_tuning_task');
END;

STEP4-

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_tuning_task') FROM DUAL


DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK')
----------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------

I didn't find any recommendations which i was expected to have
can anybody help me in this regard?
thanx in advance
Re: SQL Tunning Advisor [message #257059 is a reply to message #207915] Tue, 07 August 2007 08:47 Go to previous messageGo to next message
v@to
Messages: 5
Registered: August 2007
Junior Member
M.Shakeel Azeem

Me too.

did you solve that ?
Re: SQL Tunning Advisor [message #257081 is a reply to message #257059] Tue, 07 August 2007 09:38 Go to previous messageGo to next message
cbruhn2
Messages: 41
Registered: January 2007
Member
Hi there,

have you checked the task has been done ?
SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT';

TASK_NAME                      STATUS
------------------------------ -----------
emp_dept_tuning_task           COMPLETED

could be that you haven't specified a large enough size for longs.

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('emp_dept_tuning_task')
  AS recommendations FROM dual;


that's my best guess.

kind regards
Carl Bruhn
Re: SQL Tunning Advisor [message #257090 is a reply to message #207915] Tue, 07 August 2007 10:14 Go to previous messageGo to next message
v@to
Messages: 5
Registered: August 2007
Junior Member
cbruhn2,

you are right. But got the message.

ORA-16951: Too many bind variables supplied for this SQL statement.
Re: SQL Tunning Advisor [message #257173 is a reply to message #257090] Tue, 07 August 2007 12:47 Go to previous message
v@to
Messages: 5
Registered: August 2007
Junior Member
Finlay I got the results from report_tuning_task. Does anybody know how to explain all these?

-------------------------------------------------------------------------------
ADDITIONAL INFORMATIONS SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 14 of the execution plan.
  The optimizer cannot merge a view that contains an "ORDER BY" clause unless
  the statement is a "DELETE" or an "UPDATE" and the parent query is the top
  most query in the statement.
- The optimizer could not merge the view at line ID 7 of the execution plan.
  The optimizer cannot merge a view that contains windowing functions.
- The optimizer could not merge the view at line ID 3 of the execution plan.
  The optimizer cannot merge a view that contains windowing functions.

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

1- Original
-----------
Plan hash value: 2293959492

--------------------------------------------------------------------------------
---------------------
| Id  | Operation   									| Name  				 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------

|   0 | INSERT STATEMENT 	 	        										 |   764K|   468M|       | 46099   (1)| 00:09:14 |       |       |
|      |            |
|   1 |  PX COORDINATOR                               |
       |       |       |       |            |         |       |       |
|      |            |
|   2 |   PX SEND QC (RANDOM)       						| :TQ10004	     			 |   764K|   468M|    	 | 46099   (1)| 00:09:14 |       |      |  Q1,04
| P->S | QC (RAND)  |
|   3 |    VIEW   														 |   764K|   468M|    	 | 46099   (1)| 00:09:14 |       |      |  Q1,04
| PCWP |            |
|   4 |     WINDOW SORT PUSHED RANK  									 		 |   764K|   462M|   995M| 46099   (1)| 00:09:14 |       |      |  Q1,04
| PCWP |            |
|   5 |      PX RECEIVE 				      						 		 |   764K|   462M|       | 46099   (1)| 00:09:14 |       |      |  Q1,04
| PCWP |            |
|   6 |       PX SEND HASH          						| :TQ10003    			 |   764K|   462M|       | 46099   (1)| 00:09:14 |       |      |  Q1,03
| P->P | HASH       |
|   7 |        VIEW                                 									 |       |       |       |            |          |       |      |  Q1,03
| PCWP |            |
|   8 |         WINDOW CHILD PUSHED RANK              								 |   764K|   462M|       | 46099   (1)| 00:09:14 |       |      |  Q1,03
| PCWP |            |
|   9 |          HASH JOIN RIGHT OUTER               									 |   764K|   462M|       | 17640   (2)| 00:03:32 |       |      |  Q1,03
| PCWP |            |
|  10 |           BUFFER SORT                        |
       |       |       |       |            |   																               |      |  Q1,03
| PCWC |            |
|  11 |            PX RECEIVE                 								 		 |  6048 |   183K|       |    32   (4)| 00:00:01 |       |      |  Q1,03
| PCWP |            |
|  12 |             PX SEND BROADCAST                				| :TQ10001       			 |  6048 |   183K|       |    32   (4)| 00:00:01 |       |      |
| S->P | BROADCAST  |
|  13 |              TABLE ACCESS FULL             				| DWH_DIM_DISTANCE_BAND		 |  6048 |   183K|       |    32   (4)| 00:00:01 |       |      |
|      |            |
|  14 |           VIEW                                |								 |   764K|   439M|       | 17605   (2)| 00:03:32 |       |      |  Q1,03
| PCWP |            |
|  15 |            SORT ORDER BY                 									 |   764K|   121M|   362M| 17605   (2)| 00:03:32 |       |      |  Q1,03
| PCWP |            |
|  16 |             PX RECEIVE                        |								 |   764K|   121M|       |  9788   (3)| 00:01:58 |       |      |  Q1,03
| PCWP |            |
|  17 |              PX SEND RANGE                    			| :TQ10002				 |   764K|   121M|       |  9788   (3)| 00:01:58 |       |      |  Q1,02
| P->P | RANGE      |
|  18 |               HASH JOIN                       |								 |   764K|   121M|       |  9788   (3)| 00:01:58 |       |      |  Q1,02
| PCWP |            |
|  19 |                BUFFER SORT                    |		
       |       |       |       |            |          |       |  																    |  Q1,02
| PCWC |            |
|  20 |                 PX RECEIVE                    |								 |     1 |    21 |       |   133   (0)| 00:00:02 |       |      |  Q1,02
| PCWP |            |
|  21 |                  PX SEND BROADCAST            			| :TQ10000				 |     1 |    21 |       |   133   (0)| 00:00:02 |       |      |
| S->P | BROADCAST  |
|  22 |                   TABLE ACCESS BY INDEX ROWID | DWH_ADM_KEY_TRANSFORMATION     			 |     1 |    21 |       |   133   (0)| 00:00:02 |       |      |
|      |            |
|  23 |                    BITMAP CONVERSION TO ROWIDS|     |       |       |       |            |     |       |
|      |            |
|  24 |                     BITMAP INDEX SINGLE VALUE | DWH_ADM_KEY_TRA_IDX$$_36100001 
|      |            |
|  25 |                PX BLOCK ITERATOR              |								 |  8002K|  1114M|       |  9630   (3)| 00:01:56 |     1 |     4 |  Q1,02
| PCWC |            |
|  26 |                 TABLE ACCESS FULL             | DWH_TMP_PRE_CALLDETAILS_6_1    			 |  8002K|  1114M|       |  9630   (3)| 00:01:56 |     1 |     4 |  Q1,02

[Updated on: Tue, 07 August 2007 12:52]

Report message to a moderator

Previous Topic: Oracle 10G ideal parameters
Next Topic: Info about droped objects
Goto Forum:
  


Current Time: Sat Dec 14 00:37:56 CST 2024