Query to find the different hash plan value of the sql id

Most of the time we will be in a situation where we need to fix the long running queries. Same query might have been running fine for the previous week but for some reason you might have hit with performance issue . Issue might be due optimizer changing its plan for the particular sql. First action plan of any performance tuning should be to look for an better execution plan. Please use the below query to find the all the hash plan values and its average elapsed time in seconds. 

WITH
p AS (
SELECT plan_hash_value
  FROM gv$sql_plan
WHERE sql_id = TRIM('&&sql_id.')
   AND other_xml IS NOT NULL
UNION
SELECT plan_hash_value
  FROM dba_hist_sql_plan
WHERE sql_id = TRIM('&&sql_id.')
   AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
       SUM(elapsed_time)/SUM(executions) avg_et_secs
  FROM gv$sql
WHERE sql_id = TRIM('&&sql_id.')
   AND executions > 0
GROUP BY
       plan_hash_value ),
a AS (
SELECT plan_hash_value,
       SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
  FROM dba_hist_sqlstat
WHERE sql_id = TRIM('&&sql_id.')
   AND executions_total > 0
GROUP BY
       plan_hash_value )
SELECT p.plan_hash_value,
       ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
  FROM p, m, a
WHERE p.plan_hash_value = m.plan_hash_value(+)
   AND p.plan_hash_value = a.plan_hash_value(+)
ORDER BY
       avg_et_secs NULLS LAST;

Output of the above query will be similar to below.

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
      789010715     352.173
     3399570226    1728.193
     3605882104
     3754937196
     3621879498
     1401120836

If you find any better hash plans based on AVG_ET_SECS, force the optimizer to use the particular plan_hash_value.As you can see from the above example, the hash_plan 789010715 performs better than the current 3399570226. So it advisable to force optimizer to user the hash_plan 789010715.

If you want more details about the runtime. Please run the following query.

set lines 155
col execs for 999,999
col sql_id for a20
col avg_etime for 999,999.999
col avg_lio for 9,999,999,999.9
col avg_pio for 9,999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
SELECT ss.snap_id,
       ss.instance_number       node,
       begin_interval_time,
       sql_id,
       plan_hash_value,
       Nvl(executions_delta, 0) execs,
       ( elapsed_time_delta / Decode(Nvl(executions_delta, 0), 0, 1,
                                                               executions_delta)
       ) /
       1000000                  avg_etime,
       ( buffer_gets_delta / Decode(Nvl(executions_delta, 0), 0, 1,
                                                              executions_delta)
       )
                                avg_lio,
       ( disk_reads_delta / Decode(Nvl(executions_delta, 0), 0, 1,
                                                             executions_delta) )
                                avg_pio,
       rows_processed_delta     total_rows
FROM   dba_hist_sqlstat S,
       dba_hist_snapshot SS
WHERE  sql_id = Nvl('&sql_id', '4dqs2k5tdfdsfs')
       AND ss.snap_id = S.snap_id
       AND ss.instance_number = S.instance_number
       AND s.instance_number LIKE Nvl('&instance_number', s.instance_number)
ORDER  BY 1,
          2,
          3;

Output of the above query will be similar to below.

SNAP_ID   NODE BEGIN_INTERVAL_TIME            SQL_ID               PLAN_HASH_VALUE    EXECS    AVG_ETIME          AVG_LIO          AVG_PIO TOTAL_ROWS
---------- ------ ------------------------------ -------------------- --------------- -------- ------------ ---------------- ---------------- ----------
    324145      1 04-NOV-19 12.00.06.380 PM      8pmwhghsv31pm             3399570226        0    1,067.929      2,417,258.0      1,224,221.0          0
    324146      1 04-NOV-19 01.00.23.280 PM      8pmwhghsv31pm                               1    4,873.173    145,318,579.0      5,066,567.0        174