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  |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Hi,
How to test the speed of a query or procedure etc..
Is this using
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 #348935 is a reply to message #348916] |
Thu, 18 September 2008 05:57   |
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   |
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   |
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  |
 |
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
|
|
|
Goto Forum:
Current Time: Tue Feb 11 10:21:39 CST 2025
|