Case 2: The statement is in SQL Area but it has a wrong execution plan
This case is like the previous one. The difference is that the statement is already run. First you have to run the DBMS_SQLTUNE.CREATE_TUNING_TASK procedure.
I remember that the 4 steps are:
(a) Create a tuning task
(b) Execute the tuning task
(c) Report the tuning task
(d) Accept the tuning task
Respect to the previous case, now the procedure that you have to run is:
declare
task_name VARCHAR2(30);
begin
task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '&sqlid',
scope => 'COMPREHENSIVE',
time_limit => 30, -- nr di secondi di analisi
task_name => 'task_&sqlid',
description => 'Task to tune a query');
end;
/
As you can see, we loose "sqltext" and "user_name" parameter. This is because now you have the sql_id (instead of sqltext) and the statement is not related to any user.
For this example, I use "&sqlid" so I generalize the pl/sql code. And, in order to show how this case and previous one are really close, I'm going to use the same statement:
select
ooo.owner
, oao.object_name
from
OSL_ALL_OBJECTS oao
join OSL_OWNER_OBJECTS ooo
on
(ooo.object_id = oao.object_id);
Obviously, I start in a clear environment, where there is neither SQL Profile nor Tuning Task. You can find the tables and index definitions, here.
Looking for the sql_id in sql_area, I find:
SQL> select sql_id, sql_text from v$sql where sql_text like '%ooo%'
gwwazjphk20wc 3252867984
select ooo.owner , oao.object_name from OSL_ALL_OBJECTS oao jo
in OSL_OWNER_OBJECTS ooo on (ooo.object_id = oao.object_id)
The output of the explain plan is here: there are 2 FULL TABLE ACCESS.
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 16476 (100)| |
| 1 | HASH JOIN | | 9999K| 371M| 7712K| 16476 (1)| 00:00:03 |
| 2 | TABLE ACCESS FULL| OSL_OWNER_OBJECTS | 262K| 4613K| | 113 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL| OSL_ALL_OBJECTS | 9999K| 200M| | 5516 (1)| 00:00:01 |
------------------------------------------------------------------------------------------------
Step a) Create a tuning task
DECLARE
task_name VARCHAR2(30);
BEGIN
task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => 'gwwazjphk20wc',
scope => 'COMPREHENSIVE',
time_limit => 30, -- nr of seconds of analisys
task_name => 'task_select',
description => 'Task to tune a query');
END;
/
The "task_name" is just a name that I was given. You have to choose a different name (if you want).
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'task_select');
END;
/
Step c) Report the tuning task
set long 10000
set longchunksize 1000
set linesize 230
set heading off
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('task_select') from DUAL;
set heading on
You can find the full output here. Accepting the reccomandation, the new plan will be
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999K| 371M| 3136 (1)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 9999K| 371M| 3136 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 9999K| 371M| 3136 (1)| 00:00:01 | Q1,00 | PCWP | |
| 4 | TABLE ACCESS FULL | OSL_OWNER_OBJECTS | 262K| 4613K| 63 (2)| 00:00:01 | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 9999K| 200M| 3063 (1)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| OSL_ALL_OBJECTS | 9999K| 200M| 3063 (1)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
Step d) Accept the tuning task
In the "SQL Profile Finding" section, you find the command that you have to run, if you want to accept the reccomandation:
execute dbms_sqltune.accept_sql_profile( -
, task_name => 'task_select' -
, task_owner => 'ASALZANO' -
, replace => TRUE
, profile_type => DBMS_SQLTUNE.PX_PROFILE);
I prefere to give to the SQL Profile a my name, so I mofify a little the previous statement:
execute dbms_sqltune.accept_sql_profile( -
task_name => 'task_select' -
, task_owner => 'ASALZANO' -
, replace => TRUE -
, name => 'profile_7srkyyv9jxhzm' -
, profile_type => DBMS_SQLTUNE.PX_PROFILE);
At this point it will use the SQL Profile:
SQL> set autot trace exp
SQL> select
ooo.owner
, oao.object_name
from
OSL_ALL_OBJECTS oao
join OSL_OWNER_OBJECTS ooo
on
(ooo.object_id = oao.object_id); 2 3 4 5 6 7 8
Execution Plan
----------------------------------------------------------
Plan hash value: 3357147682
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9999K| 371M| 3136 (1)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 9999K| 371M| 3136 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 9999K| 371M| 3136 (1)| 00:00:01 | Q1,00 | PCWP | |
| 4 | TABLE ACCESS FULL | OSL_OWNER_OBJECTS | 262K| 4613K| 63 (2)| 00:00:01 | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 9999K| 200M| 3063 (1)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| OSL_ALL_OBJECTS | 9999K| 200M| 3063 (1)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OOO"."OBJECT_ID"="OAO"."OBJECT_ID")
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
- SQL profile "profile_7srkyyv9jxhzm" used for this statement
Delete/Disable/Modify SQL Profile
If you wnat to drop or modify the SQL Profile and/or the Tuning task, you have to run DBMS_SQLTUNE package. For my example:
- Drop the Tuning task
exec DBMS_SQLTUNE.DROP_TUNING_TASK ('task_select')
- Drop the SQL Profile
exec DBMS_SQLTUNE.DROP_SQL_PROFILE ('profile_7srkyyv9jxhzm')
- Disable (modify) the SQL Profile
exec DBMS_SQLTUNE.ALTER_SQL_PROFILE ( -
name => 'profile_7srkyyv9jxhzm' -
, attribute_name => 'STATUS' -
Nessun commento:
Posta un commento