How to 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
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!

How to Quickly Rename all Primary Keys in Oracle

Are you working with someone else’s schema and they haven’t declared nice names for all their constraints?

Unfortunately, it is all too easy to create a table like this:

CREATE TABLE order1 (
  order_id NUMBER(18) NOT NULL PRIMARY KEY
);

Or like this:

CREATE TABLE order2 (
  order_id NUMBER(18) NOT NULL,

  PRIMARY KEY (order_id)
);

Sure, you get a little convenience when writing the table. But from now on, you’re stuck with weird, system generated names both for the constraint and for the backing index. For instance, when doing execution plan analyses:

EXPLAIN PLAN FOR
SELECT *
FROM order1
JOIN order2 USING (order_id)
WHERE order_id = 1;

SELECT * FROM TABLE (dbms_xplan.display);

The simplified execution plan (output of the above queries) is this:

-------------------------------------------
| Id  | Operation          | Name         |
-------------------------------------------
|   0 | SELECT STATEMENT   |              |
|   1 |  NESTED LOOPS      |              |
|*  2 |   INDEX UNIQUE SCAN| SYS_C0042007 |
|*  3 |   INDEX UNIQUE SCAN| SYS_C0042005 |
-------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ORDER2"."ORDER_ID"=1)
   3 - access("ORDER1"."ORDER_ID"=1)

So, I got these system generated index names called SYS_C0042007 and SYS_C0042005. What do they mean? I can derive the actual meaning perhaps from the predicate information, as SYS_C0042007 is accessed in operation #2, which uses an access predicate on ORDER2. Fine. But do I really need to look these things up all the time?

Just name your constraints. Always!

Don’t be fooled into this convenience. It’ll hurt you time and again, not just when doing analyses. You might not be able to easily import / export your schema to some other database, because another database might already occupy these generated names.

So, do this instead:

CREATE TABLE order2 (
  order_id NUMBER(18) NOT NULL,

  CONSTRAINT pk_order2 PRIMARY KEY (order_id)
);

Find a naming schema (any naming scheme), like for instance PK_[table name]. If you’re cleaning up an existing database, this might help:

SET SERVEROUTPUT ON
BEGIN
  FOR stmts IN (
    SELECT 
      'ALTER TABLE ' || table_name || 
      ' RENAME CONSTRAINT ' || constraint_name || 
      ' TO PK_' || table_name AS stmt
    FROM user_constraints
    WHERE constraint_name LIKE 'SYS%'
    AND constraint_type = 'P'
  ) LOOP
    dbms_output.put_line(stmts.stmt);
    EXECUTE IMMEDIATE stmts.stmt;
  END LOOP;
  
  FOR stmts IN (
    SELECT 
      'ALTER INDEX ' || index_name || 
      ' RENAME TO PK_' || table_name AS stmt
    FROM user_constraints
    WHERE index_name LIKE 'SYS%'
    AND constraint_type = 'P'
  ) LOOP
    dbms_output.put_line(stmts.stmt);
    EXECUTE IMMEDIATE stmts.stmt;
  END LOOP;
END;
/

The above yields (and runs)

ALTER TABLE ORDER1 RENAME CONSTRAINT SYS_C0042005 TO PK_ORDER1
ALTER TABLE ORDER2 RENAME CONSTRAINT SYS_C0042007 TO PK_ORDER2
ALTER INDEX SYS_C0042005 RENAME TO PK_ORDER1
ALTER INDEX SYS_C0042007 RENAME TO PK_ORDER2

You can of course repeat the exercise for unique constraints, etc. I omit the example here because the naming scheme might be a bit more complicated there. Now re-calculate the execution plan and check this out:

EXPLAIN PLAN FOR
SELECT *
FROM order1
JOIN order2 USING (order_id)
WHERE order_id = 1;

SELECT * FROM TABLE (dbms_xplan.display);

The simplified execution plan (output of the above queries) is this:

----------------------------------------
| Id  | Operation          | Name      |
----------------------------------------
|   0 | SELECT STATEMENT   |           |
|   1 |  NESTED LOOPS      |           |
|*  2 |   INDEX UNIQUE SCAN| PK_ORDER2 |
|*  3 |   INDEX UNIQUE SCAN| PK_ORDER1 |
----------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ORDER2"."ORDER_ID"=1)
   3 - access("ORDER1"."ORDER_ID"=1)

There! That’s much more like it.

How to get Oracle execution plans with Starts, E-Rows, A-Rows and A-Time columns

This can probably be found elsewhere as well, but here’s a short wrap-up how to get the most out of your execution plans, quickly

1. Be sure the actual rows and time statistics are collected.

You can do this with

-- login as user sys
alter system set statistics_level = all;

2. Execute your bad SQL.

I can’t give you an example, because I don’t write bad SQL.

3. 😉

4. Find your sql_id with this statement

-- these are the most important columns
select last_active_time, sql_id, child_number, sql_text
from v$sql
-- filter for your statement
where upper(sql_fulltext) like
  upper('%[put some text from your SQL statement here]%')
-- this orders by the most recent activity
order by last_active_time desc;

5. Get the cursor and plan for that statement

select rownum, t.* from table(dbms_xplan.display_cursor(
  -- Put the previously retrieved sql_id here
  sql_id => '6dt9vvx9gmd1x',
  -- The cursor child number, in case there are
  -- several plans per cursor
  cursor_child_no => 0,
  -- Some formatting instructions to get Starts,
  -- E-Rows, A-Rows and A-Time
  FORMAT => 'ALL ALLSTATS LAST')) t;

6. Purge the cursors, if needed:

select address || ',' || hash_value from v$sqlarea
where sql_id = '6dt9vvx9gmd1x';

begin
  sys.dbms_shared_pool.purge(
    '00000000F3471988,2224337167','C',1);
end;

7. Delete all execution plans

-- login as user sys
alter system flush shared_pool;

8. Delete buffer cache (IO cache)

-- login as user sys
alter system flush buffer_cache;