Re: Finding similar statements in database for ORA-4031

  • From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
  • To: Ajay_Thotangare@xxxxxx
  • Date: Thu, 20 Sep 2007 23:20:20 +0200

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


Other related posts: