If you have the Tuning Pack licensed, a couple of things become available for you in SQL Developer:

If you DO NOT have this pack licensed, then you can disable these features in the SQL Developer UI via the preferences:

Enable the feature by database. For new connections, you'll be prompted before the feature is accessed.
Enable the feature by database. For new connections, you’ll be prompted before the feature is accessed.

Semi-Rant: consider giving your developers access to this feature in their development and UAT environments. SQL Developer, EM, or just grant them exec privs on the packages and selects on the views for crying out loud.

I have a nasty query, can you help me make it better?

Let’s ask the SQL Tuning Advisor.

Using this toolbar button will create a tuning advisor task and display the results below.
Using this toolbar button will create a tuning advisor task and display the results below.

The first thing I see is that there’s an INDEX it’s recommending. So let’s click on the ‘Index’ bit in the tree.

I click on index, and then on details.
I click on index, and then on details.

Isn’t there more info available? Like, what the plans will look like, before and after?

Yes, go back to the top and look at the Overview page.

remember to use a fixed width font if you want the plan text/lines to align nicely.
remember to use a fixed width font if you want the plan text/lines to align nicely.

Ok, I read the ‘stuff’, now what?

Send the ‘code’ to a worksheet. Look at it. Think about it. Change it, if necessary, and test it.

Click this button to get the code to a worksheet.
Click this button to get the code to a worksheet.

System named INDEXES, no joy. Change the name first. And whatever else due diligence requires.

Ta-da.
Ta-da.

Now what?

Try running your query again. Pull up a Real Time SQL Monitoring report to see how it’s going. Or maybe run AutoTrace.

The FULL Report

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : staName4451
Tuning Task Owner  : SQLDEVDEMO
Tuning Task ID     : 3134
Workload TYPE      : Single SQL Statement
Execution COUNT    : 1
CURRENT Execution  : EXEC_3147
Execution TYPE     : TUNE SQL
Scope              : COMPREHENSIVE
TIME LIMIT(seconds): 1800
Completion STATUS  : COMPLETED
Started at         : 02/26/2016 11:45:16
Completed at       : 02/26/2016 11:45:23
 
-------------------------------------------------------------------------------
Schema Name   : SQLDEVDEMO
Container Name: PDB1
SQL ID        : 619286m4662n9
SQL Text      : SELECT /*+MONITOR*/ beer.brewery
                , tastes_good_with
                , COUNT(*)
                FROM demo.itunes_music
                , demo.beer
                WHERE tastes_good_with IS NOT NULL
                AND beer.id             = itunes_music.tastes_good_with
                GROUP BY tastes_good_with
                , beer.brewery
                HAVING COUNT(*) > 2
                ORDER BY 3 DESC
 
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
 
1- 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: 77.81%)
  ------------------------------------------
  - Consider running the Access Advisor TO improve the physical schema design
    OR creating the recommended INDEX.
    CREATE INDEX DEMO.IDX$$_0C3E0001 ON DEMO.ITUNES_MUSIC("TASTES_GOOD_WITH");
 
  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: 4187090322
 
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name         | ROWS  | Bytes | Cost (%CPU)| TIME     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |              |   300 |  9300 |   169   (1)| 00:00:01 |       |       |
|   1 |  SORT ORDER BY            |              |   300 |  9300 |   169   (1)| 00:00:01 |       |       |
|*  2 |   FILTER                  |              |       |       |            |          |       |       |
|   3 |    HASH GROUP BY          |              |   300 |  9300 |   169   (1)| 00:00:01 |       |       |
|*  4 |     HASH JOIN             |              |  6000 |   181K|   168   (0)| 00:00:01 |       |       |
|*  5 |      TABLE ACCESS FULL    | ITUNES_MUSIC |  6000 | 18000 |   136   (0)| 00:00:01 |       |       |
|   6 |      PARTITION HASH SINGLE|              | 11952 |   326K|    32   (0)| 00:00:01 |     1 |     1 |
|   7 |       TABLE ACCESS FULL   | BEER         | 11952 |   326K|    32   (0)| 00:00:01 |     1 |     1 |
----------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   5 - SEL$1 / ITUNES_MUSIC@SEL$1
   7 - SEL$1 / BEER@SEL$1
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - FILTER(COUNT(*)>2)
   4 - access("BEER"."ID"="ITUNES_MUSIC"."TASTES_GOOD_WITH")
   5 - FILTER("TASTES_GOOD_WITH" IS NOT NULL)
 
COLUMN Projection Information (IDENTIFIED BY operation id):
-----------------------------------------------------------
 
   1 - (#keys=1) COUNT(*)[22], "BEER"."BREWERY"[VARCHAR2,100], "TASTES_GOOD_WITH"[NUMBER,22]
   2 - "TASTES_GOOD_WITH"[NUMBER,22], "BEER"."BREWERY"[VARCHAR2,100], COUNT(*)[22]
   3 - (#keys=2) "TASTES_GOOD_WITH"[NUMBER,22], "BEER"."BREWERY"[VARCHAR2,100], COUNT(*)[22]
   4 - (#keys=1) "ITUNES_MUSIC"."TASTES_GOOD_WITH"[NUMBER,22], "BEER"."BREWERY"[VARCHAR2,100]
   5 - "TASTES_GOOD_WITH"[NUMBER,22]
   6 - "BEER"."BREWERY"[VARCHAR2,100], "BEER"."ID"[NUMBER,22]
   7 - "BEER"."BREWERY"[VARCHAR2,100], "BEER"."ID"[NUMBER,22]
 
2- USING NEW Indices
--------------------
Plan hash VALUE: 1088718284
 
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name           | ROWS  | Bytes | Cost (%CPU)| TIME     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                |   300 |  9300 |    37   (0)| 00:00:01 |       |       |
|   1 |  SORT ORDER BY            |                |   300 |  9300 |    37   (0)| 00:00:01 |       |       |
|*  2 |   FILTER                  |                |       |       |            |          |       |       |
|   3 |    HASH GROUP BY          |                |   300 |  9300 |    37   (0)| 00:00:01 |       |       |
|*  4 |     HASH JOIN             |                |  6000 |   181K|    37   (0)| 00:00:01 |       |       |
|*  5 |      INDEX FAST FULL SCAN | IDX$$_0C3E0001 |  6000 | 18000 |     5   (0)| 00:00:01 |       |       |
|   6 |      PARTITION HASH SINGLE|                | 11952 |   326K|    32   (0)| 00:00:01 |     1 |     1 |
|   7 |       TABLE ACCESS FULL   | BEER           | 11952 |   326K|    32   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   5 - SEL$1 / ITUNES_MUSIC@SEL$1
   7 - SEL$1 / BEER@SEL$1
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - FILTER(COUNT(*)>2)
   4 - access("BEER"."ID"="ITUNES_MUSIC"."TASTES_GOOD_WITH")
   5 - FILTER("TASTES_GOOD_WITH" IS NOT NULL)
 
COLUMN Projection Information (IDENTIFIED BY operation id):
-----------------------------------------------------------
 
   1 - (#keys=1) COUNT(*)[22], "BEER"."BREWERY"[VARCHAR2,100], "TASTES_GOOD_WITH"[NUMBER,22]
   2 - "TASTES_GOOD_WITH"[NUMBER,22], "BEER"."BREWERY"[VARCHAR2,100], COUNT(*)[22]
   3 - (#keys=2) "TASTES_GOOD_WITH"[NUMBER,22], "BEER"."BREWERY"[VARCHAR2,100], COUNT(*)[22]
   4 - (#keys=1) "ITUNES_MUSIC"."TASTES_GOOD_WITH"[NUMBER,22], "BEER"."BREWERY"[VARCHAR2,100]
   5 - "TASTES_GOOD_WITH"[NUMBER,22]
   6 - "BEER"."BREWERY"[VARCHAR2,100], "BEER"."ID"[NUMBER,22]
   7 - "BEER"."BREWERY"[VARCHAR2,100], "BEER"."ID"[NUMBER,22]
 
-------------------------------------------------------------------------------

Hey, what about the automatic tuning advisory reports?

The database automatically LOOKS for bad SQL and tries to fix them for you. We don’t have a GUI for these findings, but you can build one. Here’s an example.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

6 Comments

    • You talk to your Oracle account manager. Traditionally it requires Enterprise Edition on the database and then more for Diagnostics and Tuning Packs. Cloud changes things, or can based on type of service. You pay by CPU (sockets or oCPUs in the cloud) the box or by named users.

  1. Dave Moore Reply

    Scope : COMPREHENSIVE – Is there a way to adjust SQL Developer to make it LIMITED by default, so we get simple suggestions back very quickly (find typically < 1 minute) vs. waiting for long analysis.

  2. One developer then only need CREATE SESSION, SELECT ANY DICTIONARY, ADVISOR , ADMINISTER SQL TUNING SET and EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY

    The DBA thanks you for providing this functionality 🙂

Write A Comment