DZone
Database Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Database Zone > Fetch Multiple Oracle Execution Plans in One Nice Query

Fetch Multiple Oracle Execution Plans in One Nice Query

Lateral unnesting is the key to simplifying the process of getting a SQL statement's SQL_ID and then fetching its plan while handling joins along the way.

Lukas Eder user avatar by
Lukas Eder
·
Apr. 03, 17 · Database Zone · Tutorial
Like (3)
Save
Tweet
5.43K Views

Join the DZone community and get the full member experience.

Join For Free

When looking at execution plans in Oracle, we’ll have to do several steps to be able to call the DBMS_XPLAN package functions. In fact, we have to find out the SQL_ID for a given statement first, and only then we can get its plan. I’ve blogged about this previously, here.

However, thanks to lateral unnesting, we can do the two steps in one go. More than that, we can even fetch several plans in one go this way! Check this out…

Let’s run the queries from the previous benchmarking blog post, and let’s add the /*+GATHER_PLAN_STATISTICS*/ hint to get some actual execution values, not just estimates:

SELECT /*+GATHER_PLAN_STATISTICS*/
  first_name, last_name, count(fa.actor_id) AS c
FROM actor a
LEFT JOIN film_actor fa
ON a.actor_id = fa.actor_id
WHERE last_name LIKE 'A%'
GROUP BY a.actor_id, first_name, last_name
ORDER BY c DESC
;

SELECT /*+GATHER_PLAN_STATISTICS*/
  first_name, last_name, (
    SELECT count(*)
    FROM film_actor fa
    WHERE a.actor_id =
    fa.actor_id
  ) AS c
FROM actor a
WHERE last_name LIKE 'A%'
ORDER BY c DESC
;


Both queries do the same thing. They try to find those actors whose last name starts with the letter A and counts their corresponding films. Now, what about the execution plans? Run the following query and you don’t have to know any SQL_ID in advance:

SELECT s.sql_id, p.*
FROM v$sql s, TABLE (
  dbms_xplan.display_cursor (
    s.sql_id, s.child_number, 'ALLSTATS LAST'
  )
) p
WHERE s.sql_text LIKE '%/*+GATHER_PLAN_STATISTICS*/%';


As you can see, with “LATERAL unnesting”, we can unnest a nested table (as returned by DBMS_XPLAN.DISPLAY_CURSOR) into the calling query, and we can pass column values from the V$SQL table to each function call. In other words, the above query reads as:

  • Get all SQL statements from the cursor cache V$SQL
  • Keep only those who have our GATHER_PLAN_STATISTICS hint in them (replace with your own query matching pattern)
  • Cross-join the unnested table from DBMS_XPLAN.DISPLAY_CURSOR where we get the plan per SQL_ID and CHILD_NUMBER

This implicit “LATERAL unnesting” is a bit obscure in my opinion (but its brief). More formally correct would be to use the actual LATERAL keyword, or better the SQL Server style CROSS APPLY

-- LATERAL: A bit verbose
SELECT s.sql_id, p.*
FROM v$sql s CROSS JOIN LATERAL (SELECT * FROM TABLE (
  dbms_xplan.display_cursor (
    s.sql_id, s.child_number, 'ALLSTATS LAST'
  )
)) p
WHERE s.sql_text LIKE '%/*+GATHER_PLAN_STATISTICS*/%';

-- CROSS APPLY: Very neat!
SELECT s.sql_id, p.*
FROM v$sql s CROSS APPLY TABLE (
  dbms_xplan.display_cursor (
    s.sql_id, s.child_number, 'ALLSTATS LAST'
  )
) p
WHERE s.sql_text LIKE '%/*+GATHER_PLAN_STATISTICS*/%';


In any case, the result is the same (I’ve removed some columns for brevity):

SQL_ID          PLAN_TABLE_OUTPUT
3gv1fd3dcj3b0SQL_ID  3gv1fd3dcj3b0, child number 0
3gv1fd3dcj3b0-------------------------------------
3gv1fd3dcj3b0SELECT /*+GATHER_PLAN_STATISTICS*/ first_name, last_name, 
3gv1fd3dcj3b0count(fa.actor_id) AS c FROM actor a LEFT JOIN film_actor fa ON 
3gv1fd3dcj3b0a.actor_id = fa.actor_id WHERE last_name LIKE 'A%' GROUP BY a.actor_id, 
3gv1fd3dcj3b0first_name, last_name ORDER BY c DESC
3gv1fd3dcj3b0 
3gv1fd3dcj3b0Plan hash value: 3014447605
3gv1fd3dcj3b0 
3gv1fd3dcj3b0-----------------------------------------------------------------------------------------------------
3gv1fd3dcj3b0| Id  | Operation                              | Name                    | Starts | E-Rows | A-Rows |
3gv1fd3dcj3b0-----------------------------------------------------------------------------------------------------
3gv1fd3dcj3b0|   0 | SELECT STATEMENT                       |                         |      1 |        |      7 |
3gv1fd3dcj3b0|   1 |  SORT ORDER BY                         |                         |      1 |      7 |      7 |
3gv1fd3dcj3b0|   2 |   HASH GROUP BY                        |                         |      1 |      7 |      7 |
3gv1fd3dcj3b0|*  3 |    HASH JOIN OUTER                     |                         |      1 |    154 |    196 |
3gv1fd3dcj3b0|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR                   |      1 |      6 |      7 |
3gv1fd3dcj3b0|*  5 |      INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME     |      1 |      6 |      7 |
3gv1fd3dcj3b0|   6 |     INDEX FAST FULL SCAN               | IDX_FK_FILM_ACTOR_ACTOR |      1 |   5462 |   5462 |
3gv1fd3dcj3b0-----------------------------------------------------------------------------------------------------
3gv1fd3dcj3b0 
3gv1fd3dcj3b0Predicate Information (identified by operation id):
3gv1fd3dcj3b0---------------------------------------------------
3gv1fd3dcj3b0 
3gv1fd3dcj3b0   3 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")
3gv1fd3dcj3b0   5 - access("A"."LAST_NAME" LIKE 'A%')
3gv1fd3dcj3b0       filter("A"."LAST_NAME" LIKE 'A%')
3gv1fd3dcj3b0 
3gv1fd3dcj3b0Note
3gv1fd3dcj3b0-----
3gv1fd3dcj3b0   - dynamic statistics used: dynamic sampling (level=2)
3gv1fd3dcj3b0   - this is an adaptive plan
3gv1fd3dcj3b0   - 1 Sql Plan Directive used for this statement
3gv1fd3dcj3b0 
6a3nrpcw22avrSQL_ID  6a3nrpcw22avr, child number 0
6a3nrpcw22avr-------------------------------------
6a3nrpcw22avrSELECT /*+GATHER_PLAN_STATISTICS*/ first_name, last_name, (   SELECT 
6a3nrpcw22avrcount(*)   FROM film_actor fa   WHERE a.actor_id =   fa.actor_id ) AS c 
6a3nrpcw22avrFROM actor a WHERE last_name LIKE 'A%'  ORDER BY c DESC
6a3nrpcw22avr 
6a3nrpcw22avrPlan hash value: 3873085786
6a3nrpcw22avr 
6a3nrpcw22avr---------------------------------------------------------------------------------------------------
6a3nrpcw22avr| Id  | Operation                            | Name                    | Starts | E-Rows | A-Rows |
6a3nrpcw22avr---------------------------------------------------------------------------------------------------
6a3nrpcw22avr|   0 | SELECT STATEMENT                     |                         |      1 |        |      7 |
6a3nrpcw22avr|   1 |  SORT AGGREGATE                      |                         |      7 |      1 |      7 |
6a3nrpcw22avr|*  2 |   INDEX RANGE SCAN                   | IDX_FK_FILM_ACTOR_ACTOR |      7 |     27 |    196 |
6a3nrpcw22avr|   3 |  SORT ORDER BY                       |                         |      1 |      6 |      7 |
6a3nrpcw22avr|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR                   |      1 |      6 |      7 |
6a3nrpcw22avr|*  5 |    INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME     |      1 |      6 |      7 |
6a3nrpcw22avr---------------------------------------------------------------------------------------------------
6a3nrpcw22avr 
6a3nrpcw22avrPredicate Information (identified by operation id):
6a3nrpcw22avr---------------------------------------------------
6a3nrpcw22avr 
6a3nrpcw22avr   2 - access("FA"."ACTOR_ID"=:B1)
6a3nrpcw22avr   5 - access("LAST_NAME" LIKE 'A%')
6a3nrpcw22avr       filter("LAST_NAME" LIKE 'A%')
6a3nrpcw22avr 


This is really neat!

Database Execution (computing) Fetch (FTP client)

Published at DZone with permission of Lukas Eder. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • SQL CTE: How to Master It in One Sitting With Easy Examples
  • How To Use Open Source Cadence for Polling
  • Legacy Modernization and Hybrid Cloud with Kafka in Healthcare
  • How to Build a Simple CLI With Oclif

Comments

Database Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo