DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Saturday, March 19, 2016

MANUALLY CREATING SQL TUNING SET

Step 1: I have used the below script (sql_tuning_task.sql)

SET LONG 10000;
SET PAGESIZE 9999
SET LINESIZE 155
set verify off
col recommendations for a150
accept task_name -
       prompt 'Task_Name: '
 DECLARE
 ret_val VARCHAR2(4000);

BEGIN

ret_val := dbms_sqltune.create_tuning_task(task_name=>'&&Task_name', sql_id=>'&sql_id', time_limit=>&time_limit);


dbms_sqltune.execute_tuning_task('&&Task_name');

END;
/
SELECT DBMS_SQLTUNE.report_tuning_task('&&task_name') AS recommendations FROM dual;
undef task_name

Step 2 : I have kept the script in sql_tuning_task.sql and ran as below

Connect to sqlplus 

@sql_tuning_task.sql

It will prompt for the below inputs

Task_Name: xxxxx_tuning_task (Just give any name)

Enter value for sql_id : Give the sql_id which impacting the performance 

Enter value for time_limit: Give Time Limit (Example 300)


PL/SQL procedure successfully completed.


Sample output will be as below It will give recommendation like create sql profile,Index,base lines & Constraints and so on ( It will give the script also how to create sql profile,Index,base lines & Constraints


RECOMMENDATIONS
------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : xxxxxx_tuning_task
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 300
Completion Status  : COMPLETED
Started at         : 03/19/2016 00:06:39
Completed at       : 03/19/2016 00:06:41

-------------------------------------------------------------------------------
Schema Name: Under which schema the query has been fired
SQL ID     : Here it will give the sql_id details
SQL Text   : Here it will give sql text 

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Alternative Plan Finding
---------------------------
  Some alternative execution plans for this statement were found by searching
  the system's real-time and historical performance data.

  The following table lists these plans ranked by their average elapsed time.
  See section "ALTERNATIVE PLANS SECTION" for detailed information on each
  plan.

  id plan hash  last seen            elapsed (s)  origin          note
  -- ---------- -------------------- ------------ --------------- ----------------
   1  9326881567  2016-03-18/19:21:49    10445.574 Cursor Cache    not reproducible

  Information
  -----------
  - All alternative plans other than the Original Plan could not be
    reproduced in the current environment.
  - The plan with id 1 could not be reproduced in the current environment.
    For this reason, a SQL plan baseline cannot be created to instruct the
    Oracle optimizer to pick this plan in the future.

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

1- Original
-----------
Plan hash value: 504796698

----------------------------------------------------------------------------------------------------------------------
-
| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |     1 |    77 |   160   (4)| 00:00:01 |        |      |
          |
|   1 |  SORT AGGREGATE        |                 |     1 |    77 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |                 |       |       |            |          |        |      |
|
|   3 |    PX SEND QC (RANDOM) | :TQ10000        |     1 |    77 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |                 |     1 |    77 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |                 |   615M|    44G|   160   (4)| 00:00:01 |  Q1,00 | PCWC |
          |
|   6 |       TABLE ACCESS FULL| OL_NOTIFY_ALERT |   615M|    44G|   160   (4)| 00:00:01 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------
-

-------------------------------------------------------------------------------
ALTERNATIVE PLANS SECTION
-------------------------------------------------------------------------------

Plan 1
------

  Plan Origin                 :Cursor Cache
  Plan Hash Value             :9326881897
  Executions                  :1
  Elapsed Time                :10445.574 sec
  CPU Time                    :702.529 sec
  Buffer Gets                 :17915682
  Disk Reads                  :17888783
  Disk Writes                 :0

Notes:
  1. Statistics shown are averaged over multiple executions.
  2. The plan with id 1 could not be reproduced in the current environment. For this reason, a SQL plan baseline cannot be crea
ted to instruct the Oracle optimizer to pick this plan in the future.

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
--
|   0 | SELECT STATEMENT              |                 |       |       |   160 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE               |                 |     1 |   206 |            |          |        |      |
     |
|   2 |   PX COORDINATOR              |                 |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)        | :TQ10000        |     1 |   206 |            |          |  Q1,00 | P->S | QC
 (RAND)  |
|   4 |     SORT AGGREGATE            |                 |     1 |   206 |            |          |  Q1,00 | PCWP |            |
|   5 |      APPROXIMATE NDV AGGREGATE|                 |   615M|   118G|   160   (4)| 00:00:01 |  Q1,00 | PCWP
|            |
|   6 |       PX BLOCK ITERATOR       |                 |   615M|   118G|   160   (4)| 00:00:01 |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL      | OL_NOTIFY_ALERT |   615M|   118G|   160   (4)| 00:00:01 |  Q1,00 | P
CWP |            |
------------------------------------------------------------------------------------------------------------------------------


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


No comments: