How to decide good plan or bad plan

• The optimized plan can be different according to the following factors.
  
  • Bind input can be checked in sqlhc_*_SQL_ID_13_all_bind_values.txt which is one of sqlhc outputs.
    • Between :b1 and :b2 : When the range of input is wide, the sub-optimized plan can be selected.
    • IN (:b1, ...:bn) : Too many IN bind variable inputs can make the SQL use sub-optimized plan.
    • Like :b1 : Unselective bind input such as '%A%' can cause the sub-optimized plan.
    • CONTAINS ( PartyEO.PARTY_NAME,  :B1, 1) > 0 : When :B1 is unselective, sub-optimized plan can be followed.
    • tab.col = NVL(:b1, tab.col) : When :b1 is null, sub-optimized plan can be used.
    • tab.col = DECODE(:b1,null,tab.col, :b1) : When :b1 is null, sub-optimized plan can be used.
    • etc.
  • VPD
    • Even if current cursor doesn't have VPD (DSP), there is no guarantee that another will work without VPD (DSP).
    • => Reduce the complexity of VPD logic
    • => Restrict number of user.
  • Table volume access can be checked in sma_[SQL_ID].sql rows. 
    
    • If the SQL uses too many rows in the global temporary table or the pl/sql table, the SQL can use some full table scan with hash join.
    • => Use reasonable number of rows per execution during the process.
    • => Avoid inconsistent volume per execution, if possible.
• The plan (phv) should not have met the error. The error can be found in gv$sql_monitor or dba_hist_reports.
• Conclusion
  • Use selective bind input
  • Minimize usage of VPD
  • Minimize data in global temporary table and pl/sql table in the SQL.
  • And then select Plan Hash Value for your purpose and pin it via sqlprofile.