If you have the Tuning Pack licensed, a couple of things become available for you in SQL Developer:
- SQL Tuning Advisor
- Real Time SQL Monitoring
If you DO NOT have this pack licensed, then you can disable these features in the SQL Developer UI via the preferences:
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.
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.
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.
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.
System named INDEXES, no joy. Change the name first. And whatever else due diligence requires.
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.
6 Comments
how can I get Tuning Pack licensed?
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.
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.
Thank you for demonstrating. This is helpful.
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 🙂
You’re welcome 🙂