Home » RDBMS Server » Performance Tuning » How to get recordcount & elapsed time without get result set while run proc in sql*plus? (oracle 9.2.0.1.0)
How to get recordcount & elapsed time without get result set while run proc in sql*plus? [message #306107] Wed, 12 March 2008 18:31 Go to next message
gksenthilkumar
Messages: 23
Registered: November 2007
Location: india
Junior Member
How to get the record count & elapsed time without get/populate the result set, while execute/run the procedure in the sql*plus ?

This is my earilier code:
set lines 155
set pages 100
set autoprint on
set serveroutput on size 1000000
set timing on
set feedback on
set echo on
variable cv refcursor
exec proc_name1(input1, input2, :cv);
exec proc_name2(input1, input2, :cv);
exec proc_name3(input1, input2, :cv);
exec proc_name4(input1, input2, :cv);
..
..
exec proc_name9(input1, input2, input3, :cv);

After ran the each procedure, it is result-set, record-count & elapsed time. But, I want display/capture only the record-count & elapsed time with any result-set display in the SQL8Plus promt.

Then I have tried to alternate using ananimous block like...

This is for execute for one procedure.

declare
disp SYS_REFCURSOR;
cv SYS_REFCURSOR;
cnt number :=0;
begin
proc_name (input1, input2, :cv);
FOR disp in cv --here cv is the set of record set
LOOP
--FETCH cv INTO disp;
EXIT WHEN cv%NOTFOUND;
cnt := cnt + 1;
END LOOP;
dbms_output.put_line(cnt);
dbms_output.put_line(cv%rowcount);
CLOSE cv;

end;

getting below error...
LOOP
*
ERROR at line 8:
ORA-06550: line 8, column 2:
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:
. ( % ; for
The symbol "; was inserted before "LOOP" to continue.
ORA-06550: line 13, column 2:
PLS-00103: Encountered the symbol "DBMS_OUTPUT"
ORA-06550: line 13, column 27:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
. ( , * % & - + / at mod rem <an identifier>
<an exponent (**)> as
from into || bulk

I know that we cannt use disp as a sys_refcursor... But I dont know how to get the record-count & elapsed time without getting result-set
Here- :cv is the ref_cursor variable and it is un-known record type set. Please help me....

Re: How to get recordcount & elapsed time without get result set while run proc in sql*plus? [message #306113 is a reply to message #306107] Wed, 12 March 2008 19:21 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
WOW! Never confuse movement with progress.
OP has lots of movement, but not much progress.

what happens if you do like:

SQL> SET TIME ON
SQL> SET AUTOTRACE TRACEONLY STATISTICS
SQL> SELECT .......
Re: How to get recordcount & elapsed time without get result set while run proc in sql*plus? [message #306116 is a reply to message #306113] Wed, 12 March 2008 20:01 Go to previous messageGo to next message
gksenthilkumar
Messages: 23
Registered: November 2007
Location: india
Junior Member
Thank you, Thank lot!. its working excellant
Re: How to get recordcount & elapsed time without get result set while run proc in sql*plus? [message #306142 is a reply to message #306116] Thu, 13 March 2008 01:11 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I did some benchmarking on this a while ago. If memory serves, the server still sends the results to SQL*Plus, SQL*Plus still receives them - it just discards them.

So you still get the network overhead, just not the rendering overhead as the results scroll down your screen (or spool to a file).

SET TERMOUT OFF
works the same way

Ross Leishman
Previous Topic: AWR report
Next Topic: Waits During Inserts .
Goto Forum:
  


Current Time: Thu Dec 08 04:32:47 CST 2016

Total time taken to generate the page: 0.22210 seconds