SQL Profile: Case 1 - Part 1: Create/Execute/Report/Accept

SQL Profile: Case 1 - Part 2: Data Dictionary

Case 1: The statement is never been run. In this case, we obviously know the sql text.

For this post, please see this for understand the tables envolved.

Suppose you want to run following query

select 
      ooo.owner
    , oao.object_name 
from 
      OSL_ALL_OBJECTS   oao 
join  OSL_OWNER_OBJECTS ooo 
on
      (ooo.object_id = oao.object_id);

and you never ran it. So you want that SQL Tuning Advisor, perform a tuing task for you.

Usually, you have to run 4 steps:

(a) Create a tuning task
(b) Execute the tuning task
(c) Report the tuning task
(d) Accept the tuning task

You have to run the last one, if you accept what the advisor tell you.



Step a) Create a tuning task

declare
    task_name VARCHAR2(30);
    sqltext CLOB;
begin
    sqltext := q'[select ooo.owner, oao.object_name ]';
    sqltext := sqltext||q'[from OSL_ALL_OBJECTS oao join OSL_OWNER_OBJECTS ooo ]';
    sqltext := sqltext|| q'[on (ooo.object_id=oao.object_id)]';
    
    task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_text => sqltext,
        user_name => 'ASALZANO',
        scope => 'COMPREHENSIVE',
        time_limit => 30, -- nr of seconds of analisys
        task_name => 'task_select',
        description => 'Task to tune a query');
end;
/

PL/SQL procedure successfully completed.

The "task_name" is just a name that I was given. You have to choose a different name (if you want).



Step b) Execute the tuning task

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 in this file. What you see?

• The original plan

1- Original
-----------
Plan hash value: 3252867984

------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |  9999K|   371M|       | 16476   (1)| 00:00:03 |
|*  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 |
------------------------------------------------------------------------------------------------

• The reccomandation:

2- Using Parallel Execution

---------------------------
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 |            |
------------------------------------------------------------------------------------------------------------------------

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);



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'       -
    , value =>  'DISABLED')



SQL Profile: Case 1 - Part 2: Data Dictionary