Performace difference between sql and pl/sql : Timing « SQL Plus « Oracle PL / SQL






  1. Oracle PL / SQL
  2. SQL Plus
  3. Timing

Performace difference between sql and pl/sql

    
SQL>
SQL>
SQL> create table myTable as select * from all_objects;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats( user, 'myTable' );

PL/SQL procedure successfully completed.

SQL>
SQL> create table myTable2 as select * from myTable;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats( user, 'myTable2' );

PL/SQL procedure successfully completed.

SQL>
SQL> set timing on
SQL>
SQL> update myTable set object_name = lower(object_name);

12599 rows updated.

Elapsed: 00:00:00.15
SQL> begin
  2     for x in ( select rowid rid, object_name, rownum r from myTable2 )
  3     loop
  4          update myTable2 set object_name = lower(x.object_name)
  5           where rowid = x.rid;
  6          if ( mod(x.r,100) = 0 ) then
  7             commit;
  8          end if;
  9     end loop;
 10     commit;
 11  end;
 12  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.59
SQL>
SQL> set timing off
SQL>
SQL> drop table myTable;

Table dropped.

SQL> drop table myTable2;

Table dropped.








Related examples in the same category

1.Time command
2.Time and autotrace a big table
3.Time query for a huge table
4.time a query with where clause
5.Time lower text function
6.Loop timing
7.timing unconditional loop
8.Statistics query Timing on index
9.TIMING table copying
10.Timing passing table collection parameter to a procedure