似て非なるSQLを探す

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