通過dba_hist_sqltext 等字典分析生產庫某sql時快時慢

1,查詢歷史sql
select * from dba_hist_sqltext where sql_text like '%PORTAL_ORDERBILL%' and sql_text like '%PORTAL_CONSIGNEE%';
2,查詢歷史sql的執行計劃是否發生過變化，並在何時發生變化
select a.INSTANCE_NUMBER,                                                                                                              
       a.snap_id,                                                                                                                      
       a.sql_id,                                                                                                                       
       a.plan_hash_value,                                                                                                              
       to_char(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss')                                                                         
  from dba_hist_sqlstat a, dba_hist_snapshot b                                                                                         
       WHERE                                                                                             
   a.snap_id = b.snap_id                                                                                                               
   and a.sql_id in(select sql_id from dba_hist_sqltext where sql_text like '%PORTAL_ORDERBILL%' and sql_text like '%PORTAL_CONSIGNEE%' )
   order by a.sql_id;
3,查詢發生執行計劃變化的兩個差異
select plan_hash_value,                                                                                                                                                                                                                         
       id,                                                                                                               
       operation,                                                                                                        
       options,                                                                                                          
       object_name,                                                                                                      
       depth,                                                                                                            
       cost,                                                                                                             
       timestamp                                                                                                         
  from DBA_HIST_SQL_PLAN                                                                                                 
 where sql_id
 in(select sql_id from dba_hist_sqltext where sql_text like '%PORTAL_ORDERBILL%' and sql_text like '%PORTAL_CONSIGNEE%' )
                                                                                                                                                                       
   and plan_hash_value in (209417713, 2699309196)
   order by a.sql_id;
4,出現故障的執行計劃對portal_orderbill的orderbill_date列進行了降序索引，