Guenadi N Jilevski's Oracle BLOG

Using PL/SQL to run the SQL tuning Advisor in 11gR2

Using PL/SQL to run the SQL tuning Advisor in 11gR2

In the article we will have a look at the steps to invoke and run the SQL Tuning Advisor using the interface of the PL/SQL packages provided by Oracle. The SQL Tuning Advisor can be run in an alternative way using the OEM 12c Cloud Control interface. We will not cover the OEM 12c CC interface.

The approach is simple we run a single SQL or a set of SQL statements and will stored them into a SQL tuning set. The created SQL Tuning set will be passed as an argument to the SQL Tuning Advisor.

For the analysis we will be using the SH sample schema in Oracle 11gR2.

1. We run the following SQL Statements in the sh schema as sh user.

 

SELECT /*+ monitor */ /* Catch_monitor */ /* test SQL Tuning */

    products.prod_category,

    products.prod_subcategory,

    times.calendar_year,

    SUM( sales.quantity_sold )

      AS total_quantity_sold,

    SUM( sales.amount_sold )

      AS total_amount_sold

  FROM

      sales

    INNER JOIN

      products

    USING( prod_id )

    INNER JOIN

      customers

    USING( cust_id )

    INNER JOIN

      countries

    USING( country_id )

    INNER JOIN

      channels

    USING( channel_id )

    INNER JOIN

      times

    USING( time_id )

    INNER JOIN

      times

    USING( time_id )

  WHERE

      products.prod_valid = ‘A’

    AND

      channels.channel_class = ‘Direct’

    AND

      customers.cust_valid = ‘A’

    AND

      countries.country_name = ‘United States of America’

  GROUP BY

    products.prod_category,

    products.prod_subcategory,

    times.calendar_year

  ORDER BY

    products.prod_category,

    products.prod_subcategory,

    times.calendar_year

;

 

2. Capture the SQL_ID.

 

select sql_id, sql_text from v$sql where sql_text like ‘%test SQL Tunin%’;

 

SQL> select sql_id, sql_text from v$sql where sql_text like ‘%test SQL Tunin%’;

 

SQL_ID

————-

SQL_TEXT

—————————————————————————————————-

5unjqtr8ffkfh

select sql_id, sql_text from v$sql where sql_text like ‘%test SQL Tunin%’

 

5camygz16b9b1

SELECT /*+ monitor */ /* Catch_monitor */ /* test SQL Tuning */     products.prod_category,      prod

ucts.prod_subcategory,    times.calendar_year,     SUM( sales.quantity_sold )          AS total_quanti

ty_sold,     SUM( sales.amount_sold )     AS total_amount_sold   FROM       sales  INNER JOIN

     products    USING( prod_id )     INNER JOIN    customers     USING( cust_id )          INNER JOI

N      countries     USING( country_id )     INNER JOIN       channels     USING( channel_id )

 

SQL_ID

————-

SQL_TEXT

—————————————————————————————————-

INNER JOIN     times    USING( time_id )   INNER JOIN     times    USING( time_id )   WHERE

      products.prod_valid = ‘A’     AND       channels.channel_class = ‘Direct’     AND       custom

ers.cust_valid = ‘A’  AND      countries.country_name = ‘United States of America’     GROUP BY

  products.prod_category,     products.prod_subcategory,     times.calendar_year   ORDER BY      prod

ucts.prod_category,   products.prod_subcategory,     times.calendar_year

 

3. Create a SQL Tuning Set and populate the SQL Tuning set.

 

 

BEGIN

dbms_sqltune.create_sqlset(sqlset_name => ‘SQL_TUNE_JAN1′, sqlset_owner =>’SYS’);

END;

/

 

 

DECLARE

 baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;

BEGIN

 OPEN baseline_cursor FOR

    SELECT VALUE(p)

    FROM TABLE (DBMS_SQLTUNE.SELECT_CURSOR_CACHE( basic_filter=>’SQL_ID=”5camygz16b9b1”’)) p;

 

    DBMS_SQLTUNE.LOAD_SQLSET(

             sqlset_name     => ‘SQL_TUNE_JAN1’,

             populate_cursor => baseline_cursor);

END;

/

 

 

4. Create a SQL Tuning Task.

 

 

 

SQL> VARIABLE task_name VARCHAR2(30)

EXEC :task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sqlset_name => ‘SQL_TUNE_JAN1’ );

PRINT task_name

 

SQL>

PL/SQL procedure successfully completed.

 

SQL>

TASK_NAME

—————————————————————————————————-

TASK_122

 

SQL> SQL>

 

5. Set the SQL Tuning Task parameters.

 

 

BEGIN

  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(

    task_name => ‘TASK_122’,

    parameter => ‘TIME_LIMIT’, value => 30000);

END;

/

 

BEGIN

  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(

    task_name => ‘TASK_122’,

    parameter => ‘LOCAL_TIME_LIMIT’, value => 30000);

END;

/

BEGIN

  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(

    task_name => ‘TASK_122’,

    parameter => ‘MODE’, value => ‘COMPREHENSIVE’);

END;

/

 

BEGIN

  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(

    task_name => ‘TASK_122’,

    parameter => ‘TEST_EXECUTE’, value => ‘FULL’);

END;

 

6. Execute the SQL Tuning Task.

 

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘TASK_122’ );

 

7. Report the SQL Tuning Task findings.

 

 

SQL>

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET LINESIZE 100

SET PAGESIZE 1000

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘TASK_122’)   FROM DUAL;

 

 

SQL> SQL> SQL> SQL> SQL>

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_122’)

—————————————————————————————————-

GENERAL INFORMATION SECTION

——————————————————————————-

Tuning Task Name               : TASK_122

Tuning Task Owner              : SYS

Workload Type                  : SQL Tuning Set

Scope                         : COMPREHENSIVE

Global Time Limit(seconds)      : 30000

Per-SQL Time Limit(seconds)     : 30000

Completion Status              : COMPLETED

Started at                    : 05/06/2016 09:35:24

Completed at                  : 05/06/2016 09:36:19

SQL Tuning Set (STS) Name       : SQL_TUNE_JAN1

SQL Tuning Set Owner           : SYS

Number of Statements in the STS   : 1

 

——————————————————————————-

SUMMARY SECTION

——————————————————————————-

                    Global SQL Tuning Result Statistics

——————————————————————————-

Number of SQLs Analyzed                  : 1

Number of SQLs in the Report              : 1

Number of SQLs with Findings              : 1

Number of SQLs with Statistic Findings           : 1

Number of SQLs with SQL profiles recommended : 1

 

——————————————————————————-

    SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID

——————————————————————————-

object ID  SQL ID     statistics profile(benefit) index(benefit) restructure

———- ————- ———- —————- ————– ———–

        2 5camygz16b9b1        1          98.76%

 

——————————————————————————-

    Objects with Missing/Stale Statistics (ordered by schema, object, type)

——————————————————————————-

Schema Name                Object Name               Type State  Cascade

—————————- —————————- —– ——- ——-

                       SH SALES                    TABLE MISSING NO

 

——————————————————————————-

DETAILS SECTION

——————————————————————————-

 Statements with Results Ordered by Maximum (Profile/Index) Benefit, Object ID

——————————————————————————-

Object ID  : 2

Schema Name: SH

SQL ID    : 5camygz16b9b1

SQL Text   : SELECT /*+ monitor */ /* Catch_monitor */ /* test SQL Tuning */

               products.prod_category,

               products.prod_subcategory,

               times.calendar_year,

               SUM( sales.quantity_sold )

                 AS total_quantity_sold,

               SUM( sales.amount_sold )

                 AS total_amount_sold

              FROM

                 sales

               INNER JOIN

                 products

               USING( prod_id )

               INNER JOIN

                 customers

               USING( cust_id )

               INNER JOIN

                 countries

               USING( country_id )

               INNER JOIN

                 channels

               USING( channel_id )

               INNER JOIN

                 times

               USING( time_id )

               INNER JOIN

                 times

               USING( time_id )

              WHERE

                 products.prod_valid = ‘A’

               AND

                 channels.channel_class = ‘Direct’

               AND

                 customers.cust_valid = ‘A’

               AND

                 countries.country_name = ‘United States of America’

              GROUP BY

               products.prod_category,

               products.prod_subcategory,

               times.calendar_year

              ORDER BY

               products.prod_category,

               products.prod_subcategory,

               times.calendar_year

 

——————————————————————————-

FINDINGS SECTION (2 findings)

——————————————————————————-

 

1- Statistics Finding

———————

  Table “SH”.”SALES” was not analyzed.

 

  Recommendation

  ————–

  – Consider collecting optimizer statistics for this table.

    execute dbms_stats.gather_table_stats(ownname => ‘SH’, tabname =>

           ‘SALES’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

           method_opt => ‘FOR ALL COLUMNS SIZE AUTO’);

 

  Rationale

  ———

    The optimizer requires up-to-date statistics for the table in order to

    select a good execution plan.

 

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

——————————————————–

  A potentially better execution plan was found for this statement.

 

  Recommendation (estimated benefit: 98.76%)

  ——————————————

  – Consider accepting the recommended SQL profile. A SQL plan baseline

    corresponding to the plan with the SQL profile will also be created.

    execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_122’,

           object_id => 2, task_owner => ‘SYS’, replace => TRUE);

 

  Validation results

  ——————

  The SQL profile was tested by executing both its plan and the original plan

  and measuring their respective execution statistics. A plan may have been

  only partially executed if the other could be run to completion in less time.

 

                        Original Plan  With SQL Profile  % Improved

                        ————-  —————-  ———-

  Completion Status:         COMPLETE        COMPLETE

  Elapsed Time (s):          .877908          .53621      38.92 %

  CPU Time (s):                .8755             .53      39.46 %

  User I/O Time (s):               0              0

  Buffer Gets:               260998            3171      98.78 %

  Physical Read Requests:           0              0

  Physical Write Requests:          0              0

  Physical Read Bytes:             0              0

  Physical Write Bytes:            0              0

  Rows Processed:                 88              88

  Fetches:                       88              88

  Executions:                      1              1

 

  Notes

  —–

1. Statistics for the original plan were averaged over 2 executions.
2. Statistics for the SQL profile plan were averaged over 2 executions.

 

——————————————————————————-

EXPLAIN PLANS SECTION

——————————————————————————-

 

1- Original With Adjusted Cost

——————————

 

——————————————————————————-

Error: cannot fetch explain plan for object: 2

——————————————————————————-

 

2- Original With Adjusted Cost

——————————

Plan hash value: 3988380047

 

—————————————————————————————————-

———–

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

rt| Pstop |

—————————————————————————————————-

———–

|   0 | SELECT STATEMENT       |          |   263 | 27089 |       |  4636 (2)| 00:00:56 |

  |      |

|   1 |  SORT GROUP BY         |          |   263 | 27089 |       |  4636 (2)| 00:00:56 |

  |      |

|*  2 |   HASH JOIN            |          |   321K|    31M|  1952K|  4622 (1)| 00:00:56 |

  |      |

|*  3 |    HASH JOIN           |          | 25607 |  1650K|    29M|  4521 (1)| 00:00:55 |

  |      |

|*  4 |     HASH JOIN          |          |   486K|    23M|    23M|  3063 (2)| 00:00:37 |

  |      |

|*  5 |      HASH JOIN         |          |   486K|    18M|    18M|  1862 (2)| 00:00:23 |

  |      |

|*  6 |       HASH JOIN        |          |   486K|    12M|       |   539 (4)| 00:00:07 |

  |      |

|*  7 |        TABLE ACCESS FULL  | CHANNELS  |     2 |    22 |       |     3     (0)| 00:00:01 |

  |      |

|   8 |        PARTITION RANGE ALL|        |   973K|    14M|       |   531 (3)| 00:00:07 |

1 |    28 |

|   9 |       TABLE ACCESS FULL | SALES     |   973K|    14M|       |   531      (3)| 00:00:07 |

1 |    28 |

|* 10 |       TABLE ACCESS FULL   | CUSTOMERS | 10417 |   122K|       |   406     (1)| 00:00:05 |

  |      |

|  11 |      TABLE ACCESS FULL         | TIMES     |  1826 | 21912 |       |    18       (0)| 00:00:01 |

  |      |

|* 12 |     TABLE ACCESS FULL   | COUNTRIES |     1 |    15 |       |     3       (0)| 00:00:01 |

  |      |

|* 13 |    TABLE ACCESS FULL    | PRODUCTS  |    72 |  2664 |       |     3       (0)| 00:00:01 |

  |      |

—————————————————————————————————-

———–

 

Predicate Information (identified by operation id):

—————————————————

 

   2 – access(“SALES”.”PROD_ID”=”PRODUCTS”.”PROD_ID”)

   3 – access(“CUSTOMERS”.”COUNTRY_ID”=”COUNTRIES”.”COUNTRY_ID”)

   4 – access(“SALES”.”TIME_ID”=”TIMES”.”TIME_ID”)

   5 – access(“SALES”.”CUST_ID”=”CUSTOMERS”.”CUST_ID”)

   6 – access(“SALES”.”CHANNEL_ID”=”CHANNELS”.”CHANNEL_ID”)

   7 – filter(“CHANNELS”.”CHANNEL_CLASS”=’Direct’)

  10 – filter(“CUSTOMERS”.”CUST_VALID”=’A’)

  12 – filter(“COUNTRIES”.”COUNTRY_NAME”=’United States of America’)

  13 – filter(“PRODUCTS”.”PROD_VALID”=’A’)

 

3- Using SQL Profile

——————–

Plan hash value: 4242716298

 

—————————————————————————————————-

———-

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

t| Pstop |

—————————————————————————————————-

———-

|   0 | SELECT STATEMENT            |          | 263 | 27089 | 991   (5)| 00:00:12 |

 |     |

|   1 |  SORT GROUP BY              |          | 263 | 27089 | 991   (5)| 00:00:12 |

 |     |

|*  2 |   HASH JOIN                 |          | 321K|   31M| 977   (3)| 00:00:12 |

 |     |

|   3 |    PART JOIN FILTER CREATE   | :BF0000   | 1826 | 21912 |   18   (0)| 00:00:01 |

 |     |

|   4 |     TABLE ACCESS FULL        | TIMES     | 1826 | 21912 |   18   (0)| 00:00:01 |

 |     |

|*  5 |    HASH JOIN                |          | 321K|   27M| 957   (3)| 00:00:12 |

 |     |

|*  6 |     TABLE ACCESS FULL        | PRODUCTS  |   72 | 2664 |    3   (0)| 00:00:01 |

 |     |

|*  7 |     HASH JOIN               |          | 321K|   16M| 952   (3)| 00:00:12 |

 |     |

|*  8 |      TABLE ACCESS FULL              | CHANNELS  |    2 |   22 |    3   (0)| 00:00:01 |

 |     |

|*  9 |      HASH JOIN              |          | 558K|   22M| 946   (3)| 00:00:12 |

 |     |

|* 10 |       HASH JOIN              |          | 6284 | 165K| 410   (1)| 00:00:05 |

 |     |

|* 11 |        TABLE ACCESS FULL     | COUNTRIES |    1 |   15 |    3   (0)| 00:00:01 |

 |     |

|* 12 |        TABLE ACCESS FULL     | CUSTOMERS | 10417 | 122K| 406   (1)| 00:00:05 |

 |     |

|  13 |       PARTITION RANGE JOIN-FILTER|        | 973K|   14M| 531   (3)| 00:00:07 |:BF000

0|:BF0000|

|  14 |        TABLE ACCESS FULL     | SALES     | 973K|   14M| 531   (3)| 00:00:07 |:BF000

0|:BF0000|

—————————————————————————————————-

———-

 

Predicate Information (identified by operation id):

—————————————————

 

   2 – access(“SALES”.”TIME_ID”=”TIMES”.”TIME_ID”)

   5 – access(“SALES”.”PROD_ID”=”PRODUCTS”.”PROD_ID”)

   6 – filter(“PRODUCTS”.”PROD_VALID”=’A’)

   7 – access(“SALES”.”CHANNEL_ID”=”CHANNELS”.”CHANNEL_ID”)

   8 – filter(“CHANNELS”.”CHANNEL_CLASS”=’Direct’)

   9 – access(“SALES”.”CUST_ID”=”CUSTOMERS”.”CUST_ID”)

  10 – access(“CUSTOMERS”.”COUNTRY_ID”=”COUNTRIES”.”COUNTRY_ID”)

  11 – filter(“COUNTRIES”.”COUNTRY_NAME”=’United States of America’)

  12 – filter(“CUSTOMERS”.”CUST_VALID”=’A’)

 

——————————————————————————-

 

 

SQL> SQL> SQL>

 

 

8. The SQL Tuning advisor recommended:

 

1. Gathering statistics
2. Accepting a SQL Profile

 

 

 

 

May 7, 2016 - Posted by gjilevski | oracle