SGAを増やす手段もありますが、数ギガ規模にまで増やすとキャッシュの吐き出し(エージアウト)をしている間、SQLキャッシュ全体をロック(ラッチ)されて処理を何も受け付けなくなります。
そこで、リテラル値が含まれているSQLを定期的に探すといいでしょう。
この例では、SQL文を前方一致100文字だけで比較していますが、アプリケーションによっては50~150文字など調整するといいかと思います。
CNTが多いものを最優先に対応するといいでしょう。
実行例は次の通り。select "Stmt",cnt,"Mem/count","Exec" from ( SELECT row_number() over (order by count(*) desc) as rn, substr(sql_text,1,100) "Stmt", count(*) cnt, sum(sharable_mem)/count(*) "Mem/count", sum(executions) "Exec" FROM v$sqlstats GROUP BY substr(sql_text,1,100) HAVING count(*) > 10 ) where rn<=100;
Stmt CNT Mem/count Exec -------------------------------------------------------------------------------- ---------- ---------- ---------- SELECT msfr.free_rankavg_id, msfr.free_rankavg_nm, msfr.event_id 4308 36555.0975 4312 , msfr.team_flg, select substrb(dump(val,16,0,32),1,120) ep, cnt from (select /*+ no_parallel(t) 308 12900.4416 3365 no_parallel_index(t) select TABLE_NAME,TABLESPACE_NAME,CLUSTER_NAME,PCT_FREE,PCT_USED,INI_TRANS,MAX_T 170 44840.8471 380 RANS,INITIAL_EXTENT/ select column_name,data_type,data_length,nullable,data_default,data_precision,da 127 48466.2047 310 ta_scale from user_t select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact 104 11319.4615 1182 use_weak_name_resl d SELECT /*+ cursor_sharing_exact */ count(*) FROM "SYS"."KUPC$DATAPUMP_QUETAB" T 100 12426 0 AB, SYS.DUAL WHERE t select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact u 93 51273.5484 1069 se_weak_name_resl dy SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB 91 17089.3187 1338 ) opt_param('paralle SELECT tau.user_id, tau.aplus_level_no, taf.appr_stat_cd FROM 63 19573.8413 252 aplus001iuser.t select * from ( select rank() over ( order by last_access_dt desc , rownum) AS r 49 32231.8367 161 n, user_id,uid_2,car insert into TBL_ObjectiveEventLog (regist_dt,event_id,objective_id,carrier_id,us 34 10804.2353 397 er_id,event_clear_co SELECT COUNT(DISTINCT BAG_USER_ID), COUNT(DISTINCT CAP_USER_ID) FROM (SELECT CAS 32 22367.5 4622 E MBAG.BAG_KIND WHEN SELECT COUNT(DISTINCT APL_USER_ID), COUNT(DISTINCT SMS_USER_ID) FROM (SELECT CAS 32 23160.75 4458 E MAPP.SMST_FLG WHEN select item_id,category_id,item_nm,carrier_1_publish_flg,carrier_2_publish_flg,c 25 13244.16 88 arrier_3_publish_flg SELECT * FROM (SELECT DISTINCT user_id, 1 as carrier_id, row_number() over( orde 22 17519.6364 65 r by last_adm_dt ) SELECT alias000$."STYLESHEET" AS STYLESHEET FROM "SYS"."METASTYLESHEET" alias000 20 14730 992 $ WHERE 1 = 1 AND (( select user_regist_kind1, TO_CHAR(last_adm_dt_kind1,'YYYY/MM/DD HH24:MI:SS') as 20 24929.2 93 last_adm_dt_kind1 fr SELECT user_id, sum(prev_user_point_amt), sum(user_point_result_amt 18 15337.3333 19 ), trunc(update_ SELECT mfri.free_rankavg_incentive_id, mfri.free_rankavg_id, mfr 18 15336 18 i.team_flg, mfri sss01dbiuser. 17 22733.6471 291 tbl_usermyroom1 select * from ( select temp_.*, rownum as rownumber_ from ( SEL 16 35747.5 389 ECT mwp.aplu select * from ( select temp_.*, rownum as rownumber_ from ( sel 15 34043.2 13478 ect user_id, SELECT count(DISTINCT user_id) as datacount FROM MST_User1 WHERE user_id in (SEL 15 14788.8 42 ECT user_id FROM MST SELECT AP001_ID,USER_ID,AP001_LEVEL_NO,MISSION_COMPLETE_PCT,TOTAL_GAME_CNT,TOTAL 13 27131.0769 87 _COIN_AMT,TOTAL_DECO select user_id,nick_name,mail,mail_sec,mail_domain,user_name,user_zip,user_pref, 12 29984.6667 5073 user_address,to_char SELECT AP001_ITEM_ID,AP001_ID,AP001_ITEM_CATEGORY_CD,CARRIER_1_PUBLISH_FLG,CARRI 12 9270 31 ER_1_PUBLISH_DT,CARR