Fetch Multiple Oracle Execution Plans in One Nice Query

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!