fetch execution plans

Two short ways to find an execution plan

Way A) Run statement then show the plan

1) Run the statement

Use the gather_plan_statistics hint

select /*+ gather_plan_statistics */ max(id), count(*)
FROM bigemp
where status = 'A';

2) Immediately after that, get the execution plan

select *
from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +peeked_binds'));

Hint: If you have serveroutput turned on, this might result in an oracle error message

Way B) Run and mark a statement then show the plan for that

We “mark” a statement using a commented hint. Then find the sql_id of this statement using the keyword in the v$sql view and use the sql_id with dbms_xplan.display_cursor

1) Run the statement

select /*+ gather_plan_statistics SYNTEGRIS_TUNING */ max(id), count(*)
FROM bigemp
where status = 'C';

2) get the execution plan

select t.plan_table_output
from v$sql s
,table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'TYPICAL ALLSTATS LAST +PEEKED_BINDS')) t
where upper(s.sql_text) like '%SYNTEGRIS_TUNING%'
and upper(s.sql_text) not like '%V$SQL%';

Some usage examples

setup script for the examples:

-- create dummy test data table
--drop table bigemp;
create table bigemp (id number(10), name varchar2(100), status varchar2(2));

-- create test data
insert into bigemp
select level as id
     , dbms_random.string(null,30) as empname
     , case when level between 0 and 175 then 'A'
            when level between 976 and 1013 then 'B'
            else 'C'
            end as status
from dual
connect by level <= 100000;

-- save
commit;

-- create index
create unique index bigemp_id_UK on bigemp(id);
create index bigemp_status on bigemp(status);

-- get statistics
execute dbms_stats.gather_table_stats(user,'BIGEMP');

-- save
commit;

Lets see two different execution plans

Find plan for status=A:

SQL_ID 579a93acwzasf, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id), count(*) FROM bigemp 
where status = 'A'
 
Plan hash value: 1218559537
 
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 |
| 2 | TABLE ACCESS BY INDEX ROWID| BIGEMP | 1 | 92 | 175 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN | BIGEMP_STATUS | 1 | 92 | 175 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
 3 - access("STATUS"='A')

We can see the index was used. If we do the same for status=C we will get a full table scan.

SQL_ID gp5m1dmxvwvky, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(id), count(*) FROM bigemp 
where status = 'C'
 
Plan hash value: 3162613071
 
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 639 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 639 |
|* 2 | TABLE ACCESS FULL| BIGEMP | 1 | 99844 | 99787 |00:00:00.03 | 639 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
 2 - filter("STATUS"='C')

Now we do the same, but with bind variables.

select /* SYNTEGRIS_TUNING2 */ max(id), count(*) FROM bigemp 
where status = :vStatus;

First enter A the second time enter C as the vStatus. The two executions plans differ. The difference can be explained by comparing the Peeked Binds section.

------------------------------------------------------------------------------------
| Id  | Operation         | Name          | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |        |       |     3 (100)|          |
|   1 |  SORT AGGREGATE   |               |      1 |     2 |            |          |
|*  2 |   INDEX RANGE SCAN| BIGEMP_STATUS |    175 |   350 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - (VARCHAR2(30), CSID=873): 'A'
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("STATUS"=:VSTATUS)

------------------------------------------------------------------------------
| Id  | Operation          | Name   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |        |       |  1652 (100)|          |
|   1 |  SORT AGGREGATE    |        |      1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| BIGEMP |    999K|  6834K|  1652   (1)| 00:00:20 |
------------------------------------------------------------------------------
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - (VARCHAR2(30), CSID=873): 'C'
 
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("STATUS"=:VSTATUS)

     2 - filter("STATUS"=:VSTATUS)