create or replace package body pkg_mine

is
/************************************************************************
 Procedure sp_mine
*************************************************************************/
    PROCEDURE sp_mine(rc_infranum OUT tp_refcursor) 
    
    as 
    
    begin 
    dbms_output.put_line(to_char(sysdate,'dd/mm/yy hh:mi:ss'));
      
      open rc_infranum for
        select BAC_num_sessions,
               BAC_num_hosts,
               BAC_num_TXs,
               mine_num_apps,
               mine_num_hosts,
               mine_num_TXs
          from 
          (select count(distinct em.session_id) BAC_num_sessions,
                  count(distinct em.em_host_id) BAC_num_hosts,
                  count(distinct em.em_transaction_id) BAC_num_TXs,
                  1 table_id
             from bac62aaprofile2.event_meter em,
                  bac62topazmgm.sessions s
            where em.em_date_time between trunc(sysdate-7) and trunc(sysdate)
              and em.session_id = s.session_id
              and upper(s.session_name) not like '%BURN%'
              --and upper(s.session_name) not like '%TEST%'
              --and upper(s.session_name) not like '%TST%'
              ) a,     
          (select count(distinct app_id) mine_num_apps,
                  count(distinct host_id) mine_num_hosts,
                  count(distinct transaction_id) mine_num_TXs,
                  1 table_id
             from t_transaction_summary_day
            where DATE_TIME between trunc(sysdate-7) and trunc(sysdate)
              and upper(app_name) not like '%BURN%'
              --and upper(app_name) not like '%TEST%'
              --and upper(app_name) not like '%TST%'
              ) b
         where a.table_id = b.table_id;
         
         dbms_output.put_line(to_char(sysdate,'dd/mm/yy hh:mi:ss'));
     

      exception when others 
      then null;
    
    END sp_mine;

    
end pkg_mine;

DECLARE
l_time   NUMBER;
l_cnt number;
BEGIN
l_time := DBMS_UTILITY.get_time;

select count(*) into l_cnt from dual connect by level < 100000;

DBMS_OUTPUT.put_line ((DBMS_UTILITY.get_time - l_time) / 100
                      || ' seconds....'
                      );
END;
/
,15 seconds....

SQL> declare
  2  b pkg_mine.tp_refcursor;
  3  d number;
  4  e number;
  5  f number;
  6  g number;
  7  h number;
  8  i number;
  9  j number;
 10  k number;
 11  begin
 12  pkg_mine.sp_mine(b);
 13  
 14  j:=dbms_utility.get_time;
 15  dbms_output.put_line('before  '||j);
 16  loop
 17  
 18  fetch b into d,e,f,g,h,i;
 19  exit when b%notfound;
 20  end loop;
 21  
 22  k:=dbms_utility.get_time;
 23  dbms_output.put_line('after  '||k);
 24  dbms_output.put_line(round((k-j)/100,2));
 25  end;
 26  /