On 9/20/07, Thotangare, Ajay (GTI) <Ajay_Thotangare@xxxxxx> wrote: > There are about 6000 versions of same sql not shared due to use of table > aliases which makes statement different. Below is one of the example. > 00000005ECD192B0 SELECT t3608.CURRENT_BUSDATE, t3608.BOM_DATE FROM > deldate0 t360800000005ECD192B0 WHERE t3608.NDAY_REGION = :"SYS_B_0" > 00000005ED1E0C90 SELECT t66732.CURRENT_BUSDATE, t66732.BOM_DATE FROM > deldate0 t6600000005ED1E0C90 732 WHERE t66732.NDAY_REGION = :"SYS_B_0" > 00000005ED5B2AE0 SELECT t110049.CURRENT_BUSDATE, t110049.BOM_DATE FROM > deldate0 t00000005ED5B2AE0 110049 WHERE t110049.NDAY_REGION = :"SYS_B_0" > 00000005ED6C55C0 SELECT t310076.CURRENT_BUSDATE, t310076.BOM_DATE FROM > deldate0 t00000005ED6C55C0 310076 WHERE t310076.NDAY_REGION = :"SYS_B_0" > > > Similarly there are lot of other sql statements having more than 5000 > version of same sql differing by table aliases. > > Is there any way I can group those statements and get its count. > I am looking for report something similar below. > eg I am replacing all T<number> with T@ so the statements will match > and I can group together. > > For eg > SQLTEXT > COUNT > ------- > ----- > SELECT t@.CURRENT_BUSDATE, t@.BOM_DATE FROM deldate0 t@ 6000 > WHERE t@.NDAY_REGION = :"SYS_B_0" > SELECT t@.ID from parameter t@ WHERE t@.CALCI = :"SYS_B_0" > 5000 > SELECT t@.ISSSD from TRANSITION t@ WHERE t@.TRANS_I = :"SYS_B_0" > 4000 I built this routine in 2003: http://www.adellera.it/scripts_etcetera/tokenizer/index.html exactly to cope with the "variable table alias" problem: SQL> select bvc_tokenizer_pkg.bound_stmt(sql_text) bound, count(*) cnt 2 from v$sql 3 where sql_text like '%BOM_DATE%' 4 group by bvc_tokenizer_pkg.bound_stmt(sql_text) 5 order by cnt desc; BOUND CNT ---------------------------------------------------------------------------------------------------- ---------- select t{0}.current_busdate,t{0}.bom_date from deldate{1} t{0} where t{0}.nday_region=:b 2 The "variable table aliases" come from a commercial common library whose name I don't remember - I know it's used in many products. I guess you're seeing also "variable bind variables" - where x = :ph0, ;ph1, :ph2 ... even those are handled by the routine. HTH Alberto -- Alberto Dell'Era "the more you know, the faster you go" -- //www.freelists.org/webpage/oracle-l