Home » SQL & PL/SQL » SQL & PL/SQL » Time taken by query (oracle 10g)
Time taken by query [message #348916] Thu, 18 September 2008 05:14 Go to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Hi,

How to test the speed of a query or procedure etc..
Is this using
set timing on


I have a procedure which returns a refcursor. I wanted to test the speed of the procedure
the procedure is as follows.
PROCEDURE get_status_mv_combin_test( rc_status OUT tp_refcursor)
     AS
      BEGIN
     
     OPEN rc_status FOR
     
        SELECT  
 
 
        APP_NAME,	
        APP_ID,	
        APP_STATE_NAME,	
        APP_COST_CENTER	,
        APP_CLICKSTREAM,	
        APP_URL,	
        GNO	,
        GCO,	
        GSO	,
        COCKPIT_PAUSE,	
        COCKPIT_SORT,	
        CORP,	
        SSO_ENABLED	,
        PAUSE_REASON	,
        APP_SSO_PAIR	,
        APP_STATE_DT,
        NETWORK_STATUS,	
        SERVER_STATUS,	
        REST_STATUS	,
        YIELD_1HR,	
        YIELD_24HRS	,
        YIELD_7DAYS,	
        AVAIL_1HR	,
        AVAIL_24HRS	,
        AVAIL_7DAYS,	
        MV_DATE	,
        ALERT_DT,
        ALERT_TYPE,	
        ALERT_ID ,
        host_id,
        host_name,
        hoststatus,
        id,
        sla,
        transaction_name,
        location_name,
        t_status,
        response_time,
        transaction_id,
        --w_iter_date status_date,
        date_Time
  
  
                  FROM mv_app_tx_combin_status_test;
                   
 END get_status_mv_combin_test;

To test the speed of this,I have used "plsql developer tool(test window)" and I was able to see the last row in 3 minutes.
Later, I have used sql *plus(as sombody suggested) to test the speed. I have given

SQL> set timing on
SQL> variable a refcursor;
SQL> begin
  2  get_status_mv_combin_test(:a);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> set serveroutput on;
SQL> print a;

It took 2 hours to print the all the rows. Now what is the correct one between these two.
Am i doing in the right way (using sql*plus) to test the execution time.

Please advice.

Regards,
Pointers.
Re: Time taken by query [message #348933 is a reply to message #348916] Thu, 18 September 2008 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The problem is DEFINITIVELY NOT (as I said in your previous same topic) in the procedure, it is in the fetches (here "print a").

Now you have to know how you will use the returned cursor to know how you could optimize this step.

Regards
Michel

Re: Time taken by query [message #348935 is a reply to message #348916] Thu, 18 September 2008 05:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why have you started a new topic for this?
Were our answers not good enough last time?

By printing out the details, all you are testing is the speed at which SQL*Plus can print the data to the screen.

If you check the previous thread you opened on this problem, you will see that what @michel asked to to run was this:
set timing on
begin
  -- Call the procedure
  pkg_asb_trans_test.get_status_mv_combin_test(rc_status => :rc_status);
end;
/


That would tell you how long it took to open your ref cursor - everything else was going to be network related, or time incurren processing the data in your front end.

Re: Time taken by query [message #348940 is a reply to message #348935] Thu, 18 September 2008 06:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could also try this. It will open the cursor and fetch all the data. This will let you ee how long that process takes.
set timing on
DECLARE
  rc  tp_refcursor;

  <big list of variables to fetch ref cursor into>
OR
  <cursor rowtype>
BEGIN
  pkg_asb_trans_test.get_status_mv_combin_test(rc_status => rc);

  loop
    fetch rc into <list of values or rowtype>
    exit when rc%notfound
  end loop;
  close rc;
END;
/
Re: Time taken by query [message #348948 is a reply to message #348916] Thu, 18 September 2008 06:24 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Sorry JRowbottom..
My intention was to know how one can measure the speed of a query ingeneral using sql *plus. But not at all bothering about the previous one post.

Just for the sake of example I have used my previous one and gave the details.

I have used plsql developer but the timing is different.



Michel asked me to check in sql *plus, for that i have done
SQL> set timing on
SQL> variable a refcursor;
SQL> begin
  2  get_status_mv_combin_test(:a);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> set serveroutput on;
SQL> print a;

so i wanted to conform whether i am doing in the right way or not.
Any way sorry once again..

Re: Time taken by query [message #348955 is a reply to message #348948] Thu, 18 September 2008 06:45 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Michel asked me to check in sql *plus, for that i have done

I just asked for the first part without "print" as JRowbottom said.
In addition, it is strange you don't have the result of timing:
SQL> declare d date; begin select sysdate into d from dual; end;
  2  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

See Elapsed line. Why didn't you post it?

Regards
Michel
Previous Topic: PL/SQL suggestion
Next Topic: updated/new records fetch
Goto Forum:
  


Current Time: Tue Feb 11 10:21:39 CST 2025