Finding similar statements in database for ORA-4031

From: Thotangare, Ajay \(GTI\) <Ajay_Thotangare_at_ml.com>
Date: Thu, 20 Sep 2007 14:24:47 -0400
Message-ID: <C8C1CE9973039245BF52C0FC3DF02F60485BDC@MLNYA222MB.amrs.win.ml.com>

Hi,

In my database there is a big problem of shared pool getting fragmented(ORA-4031). I know its due to some sql statements not getting shared. I found some statements which are potential candidate causing fragmentation.

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

Appreciate your help.

This message w/attachments (message) may be privileged, confidential or proprietary, and if you are not an intended recipient, please notify the sender, do not use or share it and delete it. Unless specifically indicated, this message is not an offer to sell or a solicitation of any investment products or other financial product or service, an official confirmation of any transaction, or an official statement of Merrill Lynch. Subject to applicable law, Merrill Lynch may monitor, review and retain e-communications (EC) traveling through its networks/systems. The laws of the country of each sender/recipient may impact the handling of EC, and EC may be archived, supervised and produced in countries other than the country in which you are located. This message cannot be guaranteed to be secure or error-free. This message is subject to terms available at the following link: http://www.ml.com/e-communications_terms/. By messaging with Merrill Lynch you consent to the foregoing.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 20 2007 - 13:24:47 CDT