Re: Finding similar statements in database for ORA-4031

From: Alberto Dell'Era <alberto.dellera_at_gmail.com>
Date: Thu, 20 Sep 2007 23:20:20 +0200
Message-ID: <4ef2fbf50709201420j7b3a13e9lce1de5c8ecb27bf8@mail.gmail.com>

On 9/20/07, Thotangare, Ajay (GTI) <Ajay_Thotangare_at_ml.com> 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"
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 20 2007 - 16:20:20 CDT