Home » RDBMS Server » Performance Tuning » Find speed (oracle 10 g)
Find speed [message #358764] Wed, 12 November 2008 07:17 Go to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Hi,

I have a package that is given below.
I want a best method to find the time taken by the procedure used in the procedure.

Is execution time different from fetching time..? How to find the
two timings in sql * plus..?




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;



Regards,
Pointers.
Re: Find speed [message #358775 is a reply to message #358764] Wed, 12 November 2008 08:09 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

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....

Re: Find speed [message #358777 is a reply to message #358764] Wed, 12 November 2008 08:19 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
but how to apply the same code to a procedure which returns a refcursor....
Re: Find speed [message #358807 is a reply to message #358764] Wed, 12 November 2008 09:44 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
I ran the following query it is giving the output....But that is not consistent. I mean when i run second time it is giving some other elapsed time...The reasont may be caching the result...
if so how can we get the accurate time..

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  /


Regards,
Pointers.
Re: Find speed [message #358822 is a reply to message #358807] Wed, 12 November 2008 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is accurate time?

Regards
Michel
Re: Find speed [message #358833 is a reply to message #358764] Wed, 12 November 2008 10:46 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
It is actual time....
Re: Find speed [message #358835 is a reply to message #358833] Wed, 12 November 2008 10:55 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you got actual time.

Regards
Michel
Previous Topic: Query taking very long time
Next Topic: High Logical Reads
Goto Forum:
  


Current Time: Fri Dec 02 14:30:36 CST 2016

Total time taken to generate the page: 0.16382 seconds