| SQL Tunning Advisor [message #207915] |
Thu, 07 December 2006 05:56  |
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 #257081 is a reply to message #257059] |
Tue, 07 August 2007 09:38  |
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 #257173 is a reply to message #257090] |
Tue, 07 August 2007 12:47 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
