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
3gv1fd3dcj3b0 SQL_ID 3gv1fd3dcj3b0, child number 0
3gv1fd3dcj3b0 -------------------------------------
3gv1fd3dcj3b0 SELECT /*+GATHER_PLAN_STATISTICS*/ first_name, last_name,
3gv1fd3dcj3b0 count(fa.actor_id) AS c FROM actor a LEFT JOIN film_actor fa ON
3gv1fd3dcj3b0 a.actor_id = fa.actor_id WHERE last_name LIKE 'A%' GROUP BY a.actor_id,
3gv1fd3dcj3b0 first_name, last_name ORDER BY c DESC
3gv1fd3dcj3b0
3gv1fd3dcj3b0 Plan 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
3gv1fd3dcj3b0 Predicate 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
3gv1fd3dcj3b0 Note
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
6a3nrpcw22avr SQL_ID 6a3nrpcw22avr, child number 0
6a3nrpcw22avr -------------------------------------
6a3nrpcw22avr SELECT /*+GATHER_PLAN_STATISTICS*/ first_name, last_name, ( SELECT
6a3nrpcw22avr count(*) FROM film_actor fa WHERE a.actor_id = fa.actor_id ) AS c
6a3nrpcw22avr FROM actor a WHERE last_name LIKE 'A%' ORDER BY c DESC
6a3nrpcw22avr
6a3nrpcw22avr Plan 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
6a3nrpcw22avr Predicate 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!
Like this:
Like Loading...
