How can i find the execution plan Hash value used by query in Oracle?

Question

How can I find the execution plan Hash value used by query in Oracle. I have one query which is behaving very weirdly as some times it runs in 5 sec and sometimes it is taking more than 2 hours.

I have checked with DBA and he told that there are multiple execution plans available against this query and out of them 2 are bad and only 1 is good. So , time taken by query to execute depends on the plan it picked .

Is there a query which can be used to check which execution plan was used along with its hash value so that same plan can be pinned against the query.

score 2 · Accepted Answer · answered Jan 13 '21 at 14:10

Calling dbms_xplan.display_cursor immediately after running a query will give you its plan:

create table t (
  c1 int, c2 int
);

insert into t 
with rws as (
  select level x from dual
  connect by level <= 10
)
  select x, x from rws;

set serveroutput off

select * from t where c1 = 1;

select * 
from   table(dbms_xplan.display_cursor(format => 'BASIC LAST'));

PLAN_TABLE_OUTPUT                    
EXPLAINED SQL STATEMENT:              
------------------------              
select * from t where c1 = 1          
                                      
Plan hash value: 1601196873           
                                      
----------------------------------    
| Id  | Operation         | Name |    
----------------------------------    
|   0 | SELECT STATEMENT  |      |    
|   1 |  TABLE ACCESS FULL| T    |    
----------------------------------

create index i on t ( c1 );

select * from t where c1 = 1;

select * 
from   table(dbms_xplan.display_cursor(format => 'BASIC LAST'));

PLAN_TABLE_OUTPUT                                      
EXPLAINED SQL STATEMENT:                                
------------------------                                
select * from t where c1 = 1                            
                                                        
Plan hash value: 3241032591                             
                                                        
----------------------------------------------------    
| Id  | Operation                           | Name |    
----------------------------------------------------    
|   0 | SELECT STATEMENT                    |      |    
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    |    
|   2 |   INDEX RANGE SCAN                  | I    |    
----------------------------------------------------

This only applies to getting the plan for queries run in your session. Getting the plan hash for queries in other sessions is trickier. If it's still executing, you can get this by looking at the from v$session:

select sid, serial#, sql_id, sql_hash_value
from   v$session
where  username is not null

Once it's finished running, you might be able to get the plan by looking in v$active_session_history; this uses sampling so may not include the SQL you're looking for.

To control which plan the statement uses, look into SQL plan management. Using this you can create baselines that define which plans the optimizer is able to use for a query.

score 1 · Answer 2 · answered Jan 13 '21 at 13:58

1

You can use DBA_HIST_SQL_PLAN view to check for the execution plan and related details.

You will see plan_hash_value from this view.

More details can be found here

answered Jan 13 '21 at 13:58

Popeye

35,427
4
10
31

How can i find the execution plan Hash value used by query in Oracle?

2 Answers2