JL Computer Consultancy

System Event procedure for File logging  performance

Prior to Aug 1999


--
--      Script:   loop_evt.prc
--      Author:   J P Lewis
--      Dated:    22-Jan-1999
--      Purpose:  Code for procedure system_event
--
procedure system_events (
      i_directory       in     varchar2,
      i_TimeStamp       in     varchar2,
      i_current_loop    in     number
) is
      fOutput           utl_file.file_type;
begin
      if (i_current_loop = 0) then
            for r in c_system_event loop
                  w_list(r.indx).event := r.event;
                  w_list(r.indx).total_waits := r.total_waits;
                  w_list(r.indx).time_waited := r.time_waited;
            end loop;
      else
      
            fOutput :=  utl_file.fopen(
                              i_directory,
                              'system_events.'||i_Timestamp,
                              'w'
                        );
      
            utl_file.put_line(fOutput,rpad('-',46,'-'));
            utl_file.put_line(
                        fOutput,
                        'System Events in ' || 
                        to_char(i_time_lapse) ||
                        ' minutes to ' ||
                        to_char(sysdate,'dd-Mon hh24:mi:ss') 
            );
            utl_file.put_line(fOutput,rpad('-',46,'-'));
      
      
            utl_file.put_line(
                        fOutput,
                        rpad('Event',43) ||
                        lpad('Waits',12) ||
                        lpad('Csec',12) ||
                        lpad('Avg Csec',12)
            );
      
            utl_file.put_line(
                        fOutput,
                        rpad('-----',43) ||
                        lpad('-----',12) ||
                        lpad('----',12) ||
                        lpad('--------',12)
            );
      
            for r in c_system_event loop
                  if (not w_list.exists(r.indx)) then
                        w_list(r.indx).total_waits := 0;
                        w_list(r.indx).time_waited := 0;
                  end if;
      
                  if (
                           (w_list(r.indx).total_waits != r.total_waits)
                        or (w_list(r.indx).time_waited != r.time_waited)
                  ) then
                        utl_file.put(fOutput,rpad(substr(r.event,1,43),43));
                        utl_file.put(
                             fOutput,
                              to_char( 
                                    r.total_waits - w_list(r.indx).total_waits,
                                    '999,999,990'
                              )
                        );
                        utl_file.put(
                             fOutput,
                              to_char( 
                                    r.time_waited - w_list(r.indx).time_waited,
                                    '999,999,990'
                              )
                        );
                        utl_file.put_line(
                              fOutput,
                              to_char( 
                                    (      r.time_waited - 
                                          w_list(r.indx).time_waited
                                   ) /
                                    greatest(
                                          r.total_waits - 
                                        w_list(r.indx).total_waits, 1
                                    ),
                                    '999,999.990'
                              )
                        );
      
                  end if;
      
                  w_list(r.indx).event := r.event;
                  w_list(r.indx).total_waits := r.total_waits;
                  w_list(r.indx).time_waited := r.time_waited;
            end loop;
            utl_file.fclose(fOutput);
      
      end if;
end system_events;

Back to main package

Back to Main Index of Topics