Table use investigation

From: Anthony Ballo <anthony.ballo_at_onecall.com>
Date: Wed, 22 Aug 2012 10:02:29 -0700
Message-ID: <CC5A5B03.47087%anthony.ballo_at_onecall.com>

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_OBJS
where 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_Chunk4

FROM Eul_Us.Eul5_Segments Seg, Eul_Us.Eul5_Objs Obj WHERE Seg.Seg_Seg_Type = 5
AND Seg.Seg_Cuo_Id = Obj.Obj_Id
AND ( 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 for these.

Thanks,

Anthony

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

Received on Wed Aug 22 2012 - 12:02:29 CDT