Re: Table use investigation
- From: "Tim Gorman" <tim@xxxxxxxxx>
- To: anthony.ballo@xxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 22 Aug 2012 17:31:36 +0000
Anthony,
I guess that's what you get with a 5-yr-old running EBS R12, eh? :-)
Looking within another production EBS R12.1.1 database at the same standard
table, I see...
SQL> select owner, name, type, referenced_owner, referenced_type
2 from dba_dependencies
3 where referenced_name = 'QP_PREQ_LINE_ATTRS_TMP_T';
OWNER NAME TYPE REFERENCED_O
REFERENCED_TYPE
------------ ------------------------------ ----------------- ------------
-----------------
APPS QP_PREQ_LINE_ATTRS_TMP_T SYNONYM QP TABLE
APPS OZF_ORDER_PRICE_PVT PACKAGE BODY APPS
SYNONYM
APPS QP_PREQ_LINE_ATTRS_TMP VIEW APPS
SYNONYM
APPS QP_NPREQ_LINE_ATTRS_TMP VIEW APPS
SYNONYM
ARCHIVE QP_PREQ_LINE_ATTRS_TMP_T SYNONYM APPS
SYNONYM
MERGE QP_PREQ_LINE_ATTRS_TMP_T SYNONYM APPS
SYNONYM
6 rows selected.
No triggers.
I'll bet someone created that trigger to populate the "mystery" table as
custom-objects on the "seeded" standard Oracle global temporary table, probably
for tuning/debugging/troubleshooting purposes (i.e. capturing actual data
values stored in the GTT, etc), and it sounds like it can be an enormous
problem.
I think you need to find out why that trigger is there, if it is indeed a
"valid" custom object, and if it isn't, then disable it immediately.
Auditing the usage of the QP_PREQ_LINE_ATTRS_TMP_T table is not going to help,
because pretty much everybody with pricing responsibility is causing that
trigger to fire. It might be useful to audit all SELECT operations from the
"mystery" table, but if I'm right, and someone was using it to gather data
values for tuning a query using the QP_PREQ_LINE_ATTRS_TMP_T table, and they
forgot to clean up after they were done, then you won't find any SELECTs on
table, as they were gone 186m rows ago.
Either way, check to see if "mystery" table is a valid customization, and if
not, disable or drop that trigger as soon as possible.
...and don't let any more 5-yr-olds on to your EBS R12 system...
Hope this helps...
Tim Gorman
consultant => Evergreen Database Technologies, Inc.
postal => PO Box 352151, Westminster CO 80035
email => Tim@xxxxxxxxx
mobile => +1-303-885-4526
fax => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...
-----Original Message-----
From: Anthony Ballo [mailto:anthony.ballo@xxxxxxxxxxx]
Sent: Wednesday, August 22, 2012 11:02 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Table use investigation
I have assumed responsibility for a 5 year old running EBS 12 database and came
across a mysterious table that has grown to 186m rows. It is created via
trigger on a standard EBS (Advanced Pricing) table named:
QP.QP_PREQ_LINE_ATTRS_TMP_T. This trigger basically archives the data from this
GTT from Advanced Pricing in EBS.I have searched dba_objects with: select *
from dba_source where upper(text) like'%XXQP_PREQ_LINE_ATTRS_TMP_T%'- but the
only object returned is the trigger that writes to it. I have also searched SQL
with:SELECT * FROM DBA_HIST_ACTIVE_SESS_HISTORY WHERE SQL_ID IN ( select SQL_ID
from DBA_HIST_SQL_PLAN where OBJECT_NAME = 'XXQP_PREQ_LINE_ATTRS_TMP_T')-
nothing returned their either. Since we use Discoverer, I searched: select *
from EUL_US.EUL5_OBJSwhere SOBJ_EXT_TABLE like '%XQP_PREQ_LINE_ATTRS_TMP_T%'And
with: SELECT
Obj.Obj_Name,Obj.Obj_Ba_Id,Seg.Seg_Chunk1,Seg.Seg_Chunk2,Seg.Seg_Chunk3,Seg.Seg_Chunk4FROM
Eul_Us.Eul5_Segments Seg, Eul_Us.Eul5_Objs Obj WHERE Seg.Seg_Seg_Type = 5AND
Seg.Seg_Cuo_Id = Obj.Obj_IdAND ( upper(Seg.Seg_Chunk1) LIKE
'%XXQP_PREQ_LINE_ATTRS_TMP_T%'OR upper(Seg.Seg_Chunk2) LIKE
'%XXQP_PREQ_LINE_ATTRS_TMP_T%'OR upper(Seg.Seg_Chunk3) LIKE
'%XXQP_PREQ_LINE_ATTRS_TMP_T%'OR upper(Seg.Seg_Chunk4) LIKE
'%XXQP_PREQ_LINE_ATTRS_TMP_T%')ORDER BY Obj.Obj_Id, Seg.Seg_Sequence;- both
return nothing also. Another observation: Since there are no indexes on this
table, I wonder what really could be using this table as any reads would
require a very long full table scan (FTS) of 186m rows?Is there any other place
I can look to see? I was thinking that we have a customization to Advanced
Pricing and I was going to check code on the EBS Application Server next but
based on the above, I'm doubtful that it would return anything. I also did
"Find in Files" on every file on our IT share- nothing turned up there
also.There are two other tables that mimic the same characteristics - I found
them being used in a Custom Folder in Discoverer and have a plan ready
forthese.Thanks,Anthony--//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
