Kevin Kempf's Blog

September 20, 2012

The trouble with FND_LOBS, part II

Filed under: 11i — kkempf @ 12:25 pm

Unconstrained Growth

Almost 2 years ago, I posted regarding the unmitigated growth of fnd_lobs in Ebusiness suite 11i here.

Since then, my datafile and index spaces with my 11i database have continued to grow crazy fast, fed by random uploads from many different users at a steady rate.

Since I haven’t yet implemented a work around, I decided today that I would start tracking usage in more detail than simply growth.  In short, when my boss asks me why I need more disk for the ERP, I wanted to be able to give quantifiable information to that effect.

As I’m often inclined to do, I decided the easiest way to track historical data is in Enterprise Manger (11g still). By implementing a view and a custom EM metric, I can say “each day, we’re averaging 150mb growth in LOBS”.

Creating the view

You can easily create a detail view for simplicity’s sake, similar to this one:

create or replace view apps.xxx_fnd_lob_usage
as
select   
  fd.document_id    
 ,to_char(pp.full_name)  full_name
 ,fd.creation_date file_date  
 ,fl.file_name  
 ,to_number(round(dbms_lob.getlength(fl.file_data)/1024/1024,1)) Size_Mb    
 ,fdt.description
from   
  fnd_documents fd  
 ,fnd_documents_tl fdt  
 ,fnd_lobs fl  
 ,fnd_user fu  
 ,per_people_x pp
where   
  fd.document_id = fdt.document_id
and   
  fdt.media_id = fl.file_id(+)
and   
  fd.created_by = fu.user_id
and   
  fu.employee_id = pp.person_id(+)
;

Monitoring

The next step is rather simple: create a custom metric in EM which runs each day at 11:59pm to capture how many MB’s worth of files were uploaded that day:

select 
  sum(size_mb) used_space
from
  apps.xxx_fnd_lob_usage
where
  trunc(sysdate) = trunc(file_date)
and
  size_mb is not null;

Ad-hoc information

If you really want to dig into who is doing what, the following queries may be helpful for you:

See your grand total of LOBs stored in Oracle:

select sum (size_mb) from xxx_fnd_lob_usage;

See a list of who were your biggest consumers (yesterday)

select 
  full_name
 ,sum(size_mb) used_space
from
  apps.xxx_fnd_lob_usage
where
  trunc(sysdate-1) = trunc(file_date)
and
  size_mb is not null
group by
  full_name
order by used_space desc
;

Blog at WordPress.com.