RE: Finding similar statements in database for ORA-4031
- From: "Thotangare, Ajay \(GTI\)" <Ajay_Thotangare@xxxxxx>
- To: "Nigel Thomas" <nigel_cl_thomas@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 Sep 2007 15:38:29 -0400
Thanks a lot for your update
1) what tool is doing that horrible thing with the aliases?
--Tool named PACE
2) you can try looking for statements that generate the same plan
(although same plan can be different text and vice versa)
--I tried this but was not getting much
3) but best is using regular expressions to replace Tnnnn. with T@.
--This is in 10g. My database is 9i. I can always copy this
table from 9i to 10g and do this but can I do this in 9i itself.
regards,
Ajay Thotangare
212-647-4312
-----Original Message-----
From: Nigel Thomas [mailto:nigel_cl_thomas@xxxxxxxxx]
Sent: Thursday, September 20, 2007 3:33 PM
To: Thotangare, Ajay (GTI); oracle-l
Subject: Re: Finding similar statements in database for ORA-4031
Ajay
Three thoughts
1) what tool is doing that horrible thing with the aliases?
2) you can try looking for statements that generate the same plan
(although same plan can be different text and vice versa)
3) but best is using regular expressions to replace Tnnnn. with T@.
select REGEXP_REPLACE(SQL_TEXT, '[Tt][0-9]*\.', 't@.') transformed_sql)
FROM ...
eg:
C:\>sqlplus test/test
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 20 20:27:56 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> select regexp_replace('t1.a t2.b t1.c t123.d','t[0-9]*\.', 't@.')
from dual
2 /
REGEXP_REPLACE('T1.
-------------------
t@.a t@.b t@.c t@.d
SQL>
You can then group by the transformed SQL_TEXT and count the clones...
HTH
Regards Nigel
----- Original Message ----
From: "Thotangare, Ajay (GTI)" <Ajay_Thotangare@xxxxxx>
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"
<etc>
--------------------------------------------------------
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.
--------------------------------------------------------
--
//www.freelists.org/webpage/oracle-l
Other related posts:
