Oracleは意識的にプレースホルダを使わないと、共有プールのSQL領域を食いつぶしてSQLのキャッシュ率が悪くなります。
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
