Home » SQL & PL/SQL » SQL & PL/SQL » performance of procedures
performance of procedures [message #320555] Thu, 15 May 2008 08:26 Go to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
hi ? woked with few examples ,
and wrote different procedures to observe "PERFORMANCES"
and used the timestapms to view performances ,
sorry cud not provide the explain plan


SQL>   1  create or replace  procedure forloop1 is
  2    cursor c1  is select * from emp1 ;
  3    c1row  c1%rowtype ;
  4  begin
  5   for c1row in c1
  6   loop
  7   insert into t3 values(c1row.empno , c1row.empno , c1row.job , c1row.mgr ,c1row.hiredate , c1ro
  8   end loop ;
  9* end ;
17:16:47  10  /

Procedure created.
17:16:59 SQL> exec forloop1;

PL/SQL procedure successfully completed.

 real: 645984
17:27:55 SQL> 

--------------------------------------------------------------------------------------------------------------

 1  create or replace procedure bulkcollect is
 2    cursor ee1 is select * from emp1 ;
 3    type emptype is table of ee1%rowtype;
 4    v_emptype emptype;
 5  begin
 6    open ee1 ;
 7    loop
 8    fetch ee1 bulk collect into v_emptype limit 10000;
 9    exit when v_emptype.count<1 ;
10    forall i in 1 .. v_emptype.count
11    insert into t2 values v_emptype(i);
12    end loop ;
13    close ee1 ;
14* end;
Procedure created.

 real: 16
17:37:15 SQL> /

17:34:09 SQL>  execute  bulkcollect ;

---------------------------------------------------------------------------------------------------------------
  1  create or replace  procedure forloop is
  2    cursor c1  is select * from emp1 ;
  3    c1row  c1%rowtype ;
  4  begin
  5   open c1;
  6   loop
  7    fetch c1 into c1row ;
  8   exit when c1%notfound ;
  9   insert into t3 values(c1row.empno , c1row.empno , c1row.job , c1row.mgr ,c1row.hiredate , c1ro
 10   end loop ;
 11   close c1 ;
 12* end ;

17:38:22 SQL> execute forloop;

PL/SQL procedure successfully completed.

 real: 784890
17:51:32 SQL> 

PL/SQL procedure successfully completed.

 real: 110250
17:36:04 SQL> 

-----------------------------------------------------------------------------------------------------------------
----18:03:46 SQL> insert into t2 select * from emp1 ;

10564428 rows created.

 real: 108359
18:05:47 SQL> 

---------------------------------------------------------------------------------------------------------------------


PL/SQL procedure successfully completed.

 real: 121082
18:25:35 SQL> select count(*) from t2;

 COUNT(*)
---------
 10564428

 real: 12094
18:26:00 SQL> select count(*) from emp1 ;

 COUNT(*)
---------
 10564428

 real: 16266
18:26:49 SQL> 
---------------------------------------------------------------

Re: performance of procedures [message #320556 is a reply to message #320555] Thu, 15 May 2008 08:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well done.

Is there a question to follow?
Re: performance of procedures [message #320776 is a reply to message #320556] Fri, 16 May 2008 04:03 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
may be it would be reply to some one's query wrongly posted as new topic
Re: performance of procedures [message #320777 is a reply to message #320776] Fri, 16 May 2008 04:09 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Conceivable, but a little tricky to achieve.
I'll lock it and let it sleep.
Previous Topic: Table partitioning
Next Topic: Create view problem
Goto Forum:
  


Current Time: Sat Dec 10 03:16:24 CST 2016

Total time taken to generate the page: 0.09087 seconds