Faster SQL in PL/SQL


An upgrade to 9i gives benefits for PL/SQL called from SQL straight out of the box.

First we'll have a look at 8.1.7

SQL> set timing on
SQL> select count(*) from
  2     ( select /*+ NO_MERGE */ func817(rownum) x from x$ksmmem
  3       where rownum < 10000000 )
  4  /

  COUNT(*)
----------
   9999999

Elapsed: 00:00:05.77
SQL> /

  COUNT(*)
----------
   9999999

Elapsed: 00:00:05.68
SQL> /

  COUNT(*)
----------
   9999999

Elapsed: 00:00:05.57

And then same experiment in 9i gives about a 10% improvement

SQL> set timing on
SQL> select count(*) from
  2     ( select /*+ NO_MERGE */ func92(rownum) x from x$ksmmem
  3       where rownum < 10000000 )
  4  /

  COUNT(*)
----------
   9999999

Elapsed: 00:00:05.07
SQL> /

  COUNT(*)
----------
   9999999

Elapsed: 00:00:05.01
SQL> /

  COUNT(*)
----------
   9999999

Elapsed: 00:00:05.02