Enterprise Java Development@TOPIC@

Chapter 76. SQL Tuning

76.1. Reasons for Inefficient SQL Performance
76.2. Execution Plan
76.3. Diagnostic Tools
76.3.1. Client/DAO Result
76.3.2. EXPLAIN PLAN
76.3.3. AUTOTRACE
76.3.4. Display Cursor Execution Plan within V$PLAN
76.4. Summary

76.1. Reasons for Inefficient SQL Performance

76.2. Execution Plan

76.3. Diagnostic Tools

Figure 76.1. JUnit Benchmark Tool

NoColumnIndex.queryForMovieAndDir: [measured 1 out of 1 rounds, threads: 1 (sequential)]
 round: 3.85 [+- 0.00], round.block: 0.00 [+- 0.00], round.gc: 0.00 [+- 0.00], GC.calls: 0, GC.time: 0.00, time.total: 3.85, time.warmup: 0.00, time.bench: 3.85

Figure 76.2. EXPLAIN PLAN Example with GUI

EXPLAIN PLAN Example with GUI

Figure 76.3. EXPLAIN PLAN Example using Text SQL Commands

 
EXPLAIN PLAN FOR
select * from
    ( select
        movie0_.TITLE as col_0_0_,
        person2_.FIRST_NAME as col_1_0_,
        person2_.LAST_NAME as col_2_0_ 
    from JPATUNE_MOVIE movie0_ 
    inner join JPATUNE_DIRECTOR director1_ on movie0_.DIRECTOR_ID=director1_.PERSON_ID 
    inner join JPATUNE_PERSON person2_ on director1_.PERSON_ID=person2_.ID 
    order by title DESC ) 
where
    rownum <= :limit;
SET LINESIZE 100
SET PAGESIZE 0
select * from table(DBMS_XPLAN.DISPLAY());
plan FOR succeeded.
Plan hash value: 857441453
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                |   774K|   477M|       | 25766   (1)| 00:05:10 |
|*  1 |  COUNT STOPKEY            |                |       |       |       |            |          |
|   2 |   VIEW                    |                |   774K|   477M|       | 25766   (1)| 00:05:10 |
|*  3 |    SORT ORDER BY STOPKEY  |                |   774K|    45M|    53M| 25766   (1)| 00:05:10 |
|*  4 |     HASH JOIN             |                |   774K|    45M|    11M| 14333   (1)| 00:02:52 |
|*  5 |      HASH JOIN            |                |   271K|  8746K|  5568K|  5115   (1)| 00:01:02 |
|   6 |       INDEX FAST FULL SCAN| DIRECTOR_PK    |   271K|  2385K|       |   191   (1)| 00:00:03 |
|   7 |       TABLE ACCESS FULL   | JPATUNE_PERSON |  1637K|    37M|       |  1854   (1)| 00:00:23 |
|*  8 |      TABLE ACCESS FULL    | JPATUNE_MOVIE  |   774K|    20M|       |  7169   (1)| 00:01:27 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=TO_NUMBER(:LIMIT))
   3 - filter(ROWNUM<=TO_NUMBER(:LIMIT))
   4 - access("MOVIE0_"."DIRECTOR_ID"="DIRECTOR1_"."PERSON_ID")
   5 - access("DIRECTOR1_"."PERSON_ID"="PERSON2_"."ID")
   8 - filter("MOVIE0_"."DIRECTOR_ID" IS NOT NULL)

Figure 76.4. EXPLAIN PLAN Example using Named Plan

EXPLAIN PLAN 
SET STATEMENT_ID='myplan01'
FOR
select * from
    ( select
        movie0_.TITLE as col_0_0_,
        person2_.FIRST_NAME as col_1_0_,
        person2_.LAST_NAME as col_2_0_ 
...
select * from table(DBMS_XPLAN.DISPLAY('PLAN_TABLE','myplan01','typical',null));

Figure 76.5. AUTOTRACE Example with GUI

AUTOTRACE Example with GUI

AUTO TRACE Requires Special Role and Permission

grant SELECT_CATALOG_ROLE to (user);
grant SELECT ANY DICTIONARY to (user);

Note

DISPLAY_CURSOR requires select privileges on: V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL

Figure 76.6. Example: Connection Generates SQL Commands

select poo.first_name, poo.last_name
from jpatune_person poo;

Figure 76.7. Example: SQL Commands of Interest Located in V$PLAN

select sql_id, sql_fulltext from V$SQL where sql_fulltext like '%from jpatune_person poo%';
87d246wux9qag   "select poo.first_name, poo.last_name from jpatune_person poo"
9bzam4xu5q7p5   select sql_id, sql_fulltext from V$SQL where sql_fulltext like '%from jpatune_person poo%'

Figure 76.8. Example: SQL_ID from V$PLAN Used to Display Execution Plan

select PLAN_TABLE_OUTPUT from
TABLE(DBMS_XPLAN.DISPLAY_CURSOR('87d246wux9qag',null, 'TYPICAL'));
SQL_ID  87d246wux9qag, child number 0
-------------------------------------
select poo.first_name, poo.last_name from jpatune_person poo
 
Plan hash value: 1628338048
 
------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| JPATUNE_PERSON |    10 |   680 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used for this statement (level=2)

Figure 76.9. Example: DISPLAY_CURSOR witin Single Command

SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%JPATUNE_MOVIEGENRE%';

76.4. Summary