How to identify incidents with really big LOB records

Products

• Data Loss Prevention Enforce

Issue/Introduction

There have been many many cases where the databse appears to be growing really fast, but the number of incidents does not seem to match the growth; few incidents but big database growth.

 

Resolution

The number of incidents is not indicative of the growth of the database.

A system can have millions of incidents but still be relatively small.  Conversely, there are systems that have few incidents, but are fairly large.

The key is in the size of the message being collected.  If you have a million incidents where all the incidents are associated with small messages, the database remains small.  If a system has 10000 incidents, but each associated message is 30MB or larger, then the DB will be large in proportion to the number of incidents.

Often times it is looking into what the message size is for incidents that the growth rate can be explained.

The following set of scripts will list the top 10 LOB column incidents for each of the LOB columns.  View the identifed incidents through enforce to see message information being stored.  Note: while the system can be set to not retain attachments, the original message may still be retained.  The last script is a list of incidents based on the top collective large LOBs.

Run the following from SQLPlus logged in as protect:

-- =-=-=-=-=-=

--
select sysdate from dual
/

--TOP 10 for messagelob.networkoriginalmessage
--
select i.incidentid,m.messageid, dbms_lob.getlength(m.networkoriginalmessage) max_net_orig_mess_len
from messagelob m,
     incident i
where m.messageid = i.messageid
and rownum<=10
order by 3 desc
/

--TOP 10 for messagecomponentlob.crackedcomponent
--
select i.incidentid,m.messageid, dbms_lob.getlength(mc.crackedcomponent) max_cracked_len
from messagecomponent m,
     messagecomponentlob mc,
     incident i
where m.messageid = i.messageid
and  mc.messagecomponentid = m.messagecomponentid
and (dbms_lob.getlength(mc.crackedcomponent)) is not null
and rownum<=10
order by 3 desc
/

--TOP 10 for messagecomponentlob.uncrackedcomponent
--
select i.incidentid,m.messageid, dbms_lob.getlength(mc.uncrackedcomponent) max_uncracked_len
from messagecomponent m,
     messagecomponentlob mc,
     incident i
where m.messageid = i.messageid
and  mc.messagecomponentid = m.messagecomponentid
and (dbms_lob.getlength(mc.uncrackedcomponent)) is not null
and rownum<=10
order by 3 desc
/

--TOP 10 for conditionviolationlob.crackedcomponentmarkers
--
select i.incidentid,m.conditionviolationid, dbms_lob.getlength(mc.crackedcomponentmarkers) max_cracked_markers_len
from conditionviolation m,
     conditionviolationlob mc,
     incident i
where m.incidentid = i.incidentid
and  mc.conditionviolationid = m.conditionviolationid
and rownum<=10
order by 3 desc
/

Select i.incidentid, m.messageid,
dbms_lob.getlength(m.networkoriginalmessage)+dbms_lob.getlength(mcl.crackedcomponent)+dbms_lob.getlength(mcl.uncrackedcomponent) mess_incident_len
from protect.messagelob m,
protect.incident i,
protect.messagecomponent mc,
protect.messagecomponentlob mcl
where  m.messageid = i.messageid
and m.messageid = mc.messageid(+)
and mc.messagecomponentid = mcl.messagecomponentid
and (dbms_lob.getlength(m.networkoriginalmessage)+dbms_lob.getlength(mcl.crackedcomponent)+dbms_lob.getlength(mcl.uncrackedcomponent)) is not null
and rownum<=10
order by 3 desc
/
-- =-=-=-=-=