How to tune a SQL Query using Oracle Advisory.

SQL> select name, open_mode from v$database;

 

NAME      OPEN_MODE

--------- --------------------

DB11GDEV  READ WRITE

 

SQL> select distinct sid from v$mystat;

 

       SID

    ----------

        62
 

Identify the sqlid of query that creating issue. For a demo purpose I identified a query which creates the issue (i.e  bkrm52bzasgd2). Below query will fetch the top sqls running on the database.

col type for a10
col username for a10
select
                (select username from dba_users where user_id = a.user_id) usernm,
                sql_id,
                plan_hash,
                type,
                cpu,
                wait,
                io,
                total
from (
select
     ash.inst_id, ash.user_id, ash.SQL_ID , ash.SQL_PLAN_HASH_VALUE Plan_hash, aud.name type,
     sum(decode(ash.session_state,'ON CPU',1,0))     cpu,
     sum(decode(ash.session_state,'WAITING',1,0))    -
     sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0))    wait ,
     sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0))    io ,
     sum(decode(ash.session_state,'ON CPU',1,1))     total
from gv$active_session_history ash,
     audit_actions aud
where SQL_ID is not NULL
   and ash.sql_opcode=aud.action
   and ash.sample_time > sysdate - &minutes /( 60*24)
group by inst_id, user_id, sql_id, SQL_PLAN_HASH_VALUE   , aud.name
order by sum(decode(session_state,'ON CPU',1,1))   desc
) a where  rownum <= 20
/

Enter value for minutes: 20 

USERNM     SQL_ID         PLAN_HASH TYPE              CPU       WAIT         IO      TOTAL

---------- ------------- ---------- ---------- ---------- ---------- ---------- ----------
FINANCE    bkrm52bzasgd2 3244424597 SELECT              5          0         54         59

SYS        5h7w8ykwtb2xt 4166561850 INSERT              0          0          1          1

SYS        1cq3qr774cu45 1031030155 INSERT              0          0          1          1

SYS        84qubbrsr0kfn 3675384499 INSERT              1          0          0          1

4 rows selected.

Elapsed: 00:00:00.01
 

Let’s create a tuning task id from below procedure.

 

SQL> set echo on

set time on

set timing on

set serveroutput on

declare

stmt_task VARCHAR2(40);

begin

stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'bkrm52bzasgd2');

DBMS_OUTPUT.put_line('task_id: ' || stmt_task );

end;

/

task_id: TASK_5745

 

PL/SQL procedure successfully completed.

 

__________________________________________________________________________________

****************************Run the SQL TUNING TASK********************************

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

 

14:21:55 SQL> EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_5745');

 

PL/SQL procedure successfully completed.

 

__________________________________________________________________________________

**********You can monitor the processing of the tuning task with the statement ************

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

 

14:22:38 SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_5745';

 

TASK_NAME                      STATUS

------------------------------ -----------

TASK_5745                      COMPLETED

 

14:22:44 SQL> SELECT TASK_NAME, STATUS,pct_completion_time,progress_metric FROM DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_5745';

 

TASK_NAME                      STATUS      PCT_COMPLETION_TIME PROGRESS_METRIC

------------------------------ ----------- ------------------- ---------------

TASK_5745                      COMPLETED                     0               0

 

We can see our tuning advisory completed. Let’s see the recommendation as below:

 

14:22:50 SQL> set long 10000

set longchunksize 10000

set linesize 132

set pagesize 200

SQL> spool SQL_TUNE_bkrm52bzasgd2.log

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_5745') AS recommendations FROM dual;

 

RECOMMENDATIONS

------------------------------------------------------------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name   : TASK_5745

Tuning Task Owner  : SYS

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 1800

Completion Status  : COMPLETED

Started at         : 07/17/2018 14:21:55

Completed at       : 07/17/2018 14:22:03

 

-------------------------------------------------------------------------------

Schema Name: FINANCE

SQL ID     : bkrm52bzasgd2

SQL Text   : select EMP_CODE,EMPLOYEE_NAME from

             finance.EMPLOYEE_RECORDS where EMPLOYEE_CODE>

             '41002'

 

-------------------------------------------------------------------------------

FINDINGS SECTION (2 findings)

-------------------------------------------------------------------------------

 

1- SQL Profile Finding (see explain plans section below)

--------------------------------------------------------

  A potentially better execution plan was found for this statement.

 

  Recommendation (estimated benefit: 93.06%)

  ------------------------------------------

  - Consider accepting the recommended SQL profile to use parallel execution

    for this statement.

execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_5745',

task_owner => 'SYS', replace => TRUE, profile_type =>

DBMS_SQLTUNE.PX_PROFILE);

 

  Executing this query parallel with DOP 16 will improve its response time

  93.07% over the original plan. However, there is some cost in enabling

  parallel execution. It will increase the statement's resource consumption by

  an estimated 10.91% which may result in a reduction of system throughput.

  Also, because these resources are consumed over a much smaller duration, the

  response time of concurrent statements might be negatively impacted if

  sufficient hardware capacity is not available.

 

  The following data shows some sampled statistics for this SQL from the past

  week and projected weekly values when parallel execution is enabled.

 

                                 Past week sampled statistics for this SQL

                                 -----------------------------------------

  Number of executions                                                   0

  Percent of total activity                                              0

  Percent of samples with #Active Sessions > 2*CPU                       0

  Weekly DB time (in sec)                                                0

 

                              Projected statistics with Parallel Execution

                              --------------------------------------------

  Weekly DB time (in sec)                                                0

 

2- Index Finding (see explain plans section below)

--------------------------------------------------

  The execution plan of this statement can be improved by creating one or more indices.

 

  Recommendation (estimated benefit: 99.92%)

  ------------------------------------------

  - Consider running the Access Advisor to improve the physical schema design

    or creating the recommended index.

create index FINANCE.IDX$$_166D0001 on

FINANCE.EMPLOYEE_RECORDS(EMP_CODE","EMPLOYEE_NAME");

 

  Rationale

  ---------

    Creating the recommended indices significantly improves the execution plan

    of this statement. However, it might be preferable to run "Access Advisor"

    using a representative SQL workload as opposed to a single statement. This

    will allow to get comprehensive index recommendations which takes into

    account index maintenance overhead and additional space consumption.

 

-------------------------------------------------------------------------------

EXPLAIN PLANS SECTION

-------------------------------------------------------------------------------

 

1- Original

-----------

Plan hash value: 3244424597

 

----------------------------------------------------------------------------------------------

| Id  | Operation         | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                          | 40817 |   438K| 92696   (1)| 00:18:33 |

|*  1 |  TABLE ACCESS FULL| MATERIAL_RECORDS_TESTBKP | 40817 |   438K| 92696   (1)| 00:18:33 |

----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("ILI_MATERIAL_NUMBER">'A41002')

 

2- Using New Indices

--------------------

Plan hash value: 944928231

 

---------------------------------------------------------------------------------------

| Id  | Operation            | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                | 40817 |   438K|    65   (2)| 00:00:01 |

|*  1 |  INDEX FAST FULL SCAN| IDX$$_166D0001 | 40817 |   438K|    65   (2)| 00:00:01 |

---------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("ILI_MATERIAL_NUMBER">'A41002')

 

3- Using Parallel Execution

---------------------------

Plan hash value: 1097799476

 

------------------------------------------------------------------------------------------------------------------------------

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

------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                          | 40817 |   438K|  6425   (0)| 00:01:18 |        |      |            |

|   1 |  PX COORDINATOR      |                          |       |       |            |          |        |      |            |

|   2 |   PX SEND QC (RANDOM)| :TQ10000                 | 40817 |   438K|  6425   (0)| 00:01:18 |  Q1,00 | P->S | QC (RAND)  |

|   3 |    PX BLOCK ITERATOR |                          | 40817 |   438K|  6425   (0)| 00:01:18 |  Q1,00 | PCWC |            |

|*  4 |     TABLE ACCESS FULL| MATERIAL_RECORDS_TESTBKP | 40817 |   438K|  6425   (0)| 00:01:18 |  Q1,00 | PCWP |            |

------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   4 - filter("ILI_MATERIAL_NUMBER">'A41002')

 

-------------------------------------------------------------------------------

 

 

14:23:05 SQL> select * from dba_sql_profiles;

 

no rows selected

 

We have two findings by advisor. Either we can do SQL profiling or create one or more indexes. Profiling will execute the query fast but required more resources. Let’s implement 2^nd advisory which will improve the performance of query by creating one or more indices. This will reduce the cost from  92696 to 65.  

 

14:32:18 SQL> create index FINANCE.IDX$$_166D0001 on

FINANCE.EMPLOYEE_RECORDS(EMP_CODE","EMPLOYEE_NAME");

 

Now we are gathering the stat to get the effective result.

SQL>

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'FINANCE' , tabname => ' EMPLOYEE_RECORDS',cascade => true, estimate_percent => 5,method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 1);

 

Let’s check the active plan and see if the cost reduced as below query.

 

8:10:19 SQL>  select plan_table_output from table(dbms_xplan.display_cursor('&sql_id',&child));

 

Enter value for sql_id: bkrm52bzasgd2

Enter value for child: 0

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  bkrm52bzasgd2, child number 0

-------------------------------------

select EMP_CODE,EMPLOYEE_NAME from

             finance.EMPLOYEE_RECORDS where EMPLOYEE_CODE>

             '41002'

 

 

Plan hash value: 944928231

 

---------------------------------------------------------------------------------------

| Id  | Operation            | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                |       |       |    57 (100)|          |

|*  1 |  INDEX FAST FULL SCAN| IDX$$_166D0001 | 34637 |   372K|    57   (2)| 00:00:01 |

---------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("ILI_MATERIAL_NUMBER">'A41002')

 

 

19 rows selected.

 

We can see the cost reduced to 57 and now the query takes only 00:00:01 seconds to complete.