Kevin Kempf's Blog

October 19, 2009

Weekly 11i reports via email

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

I recently covered in some detail how to customize Enterprise Manger Grid Control to keep tabs on critical 11i components.  This morning I received a report (in error, no less!) which is sent out weekly (if there is any data returned… this was the part in error!) to tell me if there is walltime abuse in the ERP.  I may be dating myself to my mainframe roots by using the word walltime, but essentially what the report tells me is this:  Did any concurrent requests run in the last week which took longer than 2 hours to complete?  The 2 hour limit, of course, can be altered; I arrived at that number after watching what a “normal” weekly snapshot of jobs looks like, and found that in general, if something runs more than 2 hours the user probably entered parameters incorrectly.  To be more specific, in most cases, this means the user ran a report “wide open” or without parameters, returning every row from some massive query and outputting about 4000 pages of report after business hours.  This in turn probably caused this printer to run out of paper… you get the picture.  Another possibility is that a user erroneously ran some update job which took so long that it blows up the undo tablespace and causes problems across the whole ERP.

Regardless, I thought I’d take a moment to walk through another tool I use to keep tabs on users.  In a nutshell, it’s a scheduled job (via EM) which simply emails me every week if there’s long running jobs:

Big brother report waiting in my Inbox Monday morning

Big brother report waiting in my Inbox Monday morning

I already mentioned that this is just PL/SQL running as an EM job, and I won’t go into setting up an EM job in this post (it’s pretty self-explanatory).

UTL_SMTP Setup

I’m a big fan of utl_smtp, but it’s too awkward to use. I’m a big fan of easy, so here are some procedures to simplify the syntax.  If you’re familiar with utl_smtp, prefer to go “old school”, or just don’t want to use procedures to do these tasks, you can skip this and hard code your utl_smtp calls the long way.  In my case, I just put all these procedures in a package and call it as needed from the actual report.

mail_compose

This procedure accepts 3 parameters as input

  1. address to: who is getting the email
  2. subject line: what is the email subject
  3. (optional) recipient name: name of email recipient

You must have c_domain defined as a constant which is your domain name.

procedure mail_compose
(
 p_address_to      varchar2
,p_subject_line    varchar2
,p_recipient_name  varchar2 default null
)
is
  c_domain   varchar2(20) := 'wordpress.com'
begin
 g_smtp_conn := utl_smtp.open_connection('smtp.' || c_domain)  -- c_domain is a constant, your domain name
 ;
 utl_smtp.helo(g_smtp_conn
              ,c_domain
              )
 ;
 utl_smtp.mail(g_smtp_conn,'<' || p_address_to || '>');
 utl_smtp.rcpt(g_smtp_conn,'<' || p_address_to || '>')
 ;
 utl_smtp.open_data(g_smtp_conn)
 ;
 utl_smtp.write_data(g_smtp_conn
                    ,'From: "Oracle (' || database_name || ')" '
                     || utl_tcp.crlf
                    )
 ;
 utl_smtp.write_data(g_smtp_conn
                    ,'To: "'
                    || nvl(p_recipient_name
                          ,p_address_to
                          )
                    || '" <'
                    || p_address_to
                    || '>'
                    || utl_tcp.crlf
                    )
 ;
 utl_smtp.write_data(g_smtp_conn
                    ,'Subject: (' || database_name || ')" '
                    || p_subject_line
                    || utl_tcp.crlf
                    )
 ;
end mail_compose
;

mail_text

You can call this as many times as you want, adding lines of text to the email you’re preparing… pretty straight forward

procedure mail_text
(
 p_text  varchar2
)
is
begin
 utl_smtp.write_data(g_smtp_conn,utl_tcp.crlf || p_text);
end mail_text
;

mail_send

This final procedure simply closes the composition and sends it on its way

procedure mail_send
is
begin
 utl_smtp.close_data(g_smtp_conn)
 ;
 utl_smtp.quit(g_smtp_conn)
 ;
 g_smtp_conn := null
 ;
end mail_send
;

database_name

I realized I had another function call in here, which simply returns the database name…

function database_name
 return varchar2
is
 cursor c_dn is
 select
 name
 from
 v$database
 ;
begin
 for c_dn_rec in c_dn
 loop
 return(c_dn_rec.name);
 end loop c_dn
 ;
 return(null)
 ;
end database_name
;

Finally, the walltime abuse report itself, with calls to the above code..

procedure weekly_walltime_abuse
is
cursor c0 is
 select
  fu.user_name
 ,fu.email_address
 from
 applsys.fnd_user fu
 where
 fu.user_name = g_long_run_notify1  -- global constant, which you can just as easily hard code to username
 or
 fu.user_name = g_long_run_notify2  -- global constant, which you can just as easily hard code to username
 ;
cursor c1 is    -- this cursor is the "crux" of the report; it returns information about concurrent requests which ran > 2 hours
 select
  user_concurrent_program_name
 ,fcr.description
 ,fcr.argument_text
 ,fu.user_name
 ,fcr.actual_start_date
 ,fcr.actual_completion_date
 ,request_id
 ,decode(fcr.status_code
        ,'A','Waiting'
        ,'C','Normal'
        ,'D','Cancelled'
        ,'G','Warning'
        ,'H','On Hold'
        ,'I','Normal'
        ,'M','No Manager'
        ,'Q','Standby'
        ,'R','Normal'
        ,'T','Terminating'
        ,'W','Paused'
        ,'X','Terminated'
        ,'Z','Waiting'
        ,'E','Error'
        )    status
 from
   fnd_conc_requests_form_v fcr
  ,fnd_user fu
 where
  (sysdate - fcr.actual_start_date) < 7  -- within the last week
 and
  (fcr.actual_completion_date - fcr.actual_start_date) > .0833 -- 2 hours, 2/24ths of a day
 and
   fcr.requested_by = fu.user_id
 and
   user_concurrent_program_name != 'Gather Schema Statistics'
 order by
   fcr.actual_start_date
 ;
 v_violation_count number
 ; 

begin
 select    -- see how many violations there are
   count(*)
 into
   v_violation_count
 from
   fnd_conc_requests_form_v fcr
 where
   (sysdate - fcr.actual_start_date) < 7  -- within the last week
 and
   (fcr.actual_completion_date - fcr.actual_start_date) > .0833 -- 2 hours, 2/24ths of a day
 ; 

 if
   v_violation_count > 0  -- 1 or more jobs ran more than 2 hours
 then
   for c0_rec in c0 loop  -- send an email to everyone in the c0 designation cursor list
     mail_compose(c0_rec.email_address
                 ,'Excessive wall Time Report'
                 ,c0_rec.email_address
                 )
     ;
     mail_text('Request Name              Parameters                     User       Begin       End         Rqst ID Disposition');
     mail_text(null)
     ;
     for c1_rec in c1 loop      -- send a nicely formatted line
       mail_text(rpad(ltrim(nvl(c1_rec.description,c1_rec.user_concurrent_program_name),25),25,' ')
         || ' '
         || rpad(ltrim(c1_rec.argument_text,30),30,' ')
         || ' '
         || rpad(ltrim(c1_rec.user_name,10),10,' ')
         || ' '
         || rpad(to_char(c1_rec.actual_start_date,'MM/DD HH24:MI'),11,' ')
         || ' '
         || rpad(to_char(c1_rec.actual_completion_date,'MM/DD HH24:MI'),11,' ')
         || ' '
         || rpad(c1_rec.request_id,7,' ')
         || ' '
         || rpad(c1_rec.status,11,' ')
        )
        ;
   end loop c1
   ;
   mail_send
   ;
 end loop c0
 ;
 end if
 ;
end weekly_walltime_abuse
;
Advertisements

Create a free website or blog at WordPress.com.