Native Compilation

11g Release 1 and above introduced  the real "native" compilation for PL/SQL  objects . Prior to this release ,  when we compile the objects in "native" format  , it was converted into "c" code . Now additional layer is gone .

By default , the objects are in INTERPRETED format . This is good for most of the applications .
But , if  your PL/ SQL objects are arithmetic intensive , native compilation may give better performance .

This native compilation applies to PL/SQL only ... does not have any impact on SQL.

In my test , the performance was 12% better than it was in INTERPRETED format.

( your mileage may vary )


I tried this exercise in Windows version of 11g R1 . Though  I compiled in native format  , not all PL/SQL were converted into NATIVE . Some package bodies / functions were still in the INTERPRETED format.

In RHEL , it worked perfectly fine .

Here are the steps .


SQL> alter system set plsql_code_type=native;

System altered.

SQL> alter system set plsql_optimize_level=3;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

You will be need to start the instance in upgrade mode.

SQL> startup upgrade;

....

SQL> @$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql
.....
.....

Upon completion of the above script , you will see the following message .

DOC>#######################################################################
DOC>   dbmsupgnv.sql completed successfully. All PL/SQL procedures,
DOC>   functions, type bodies, triggers, and type bodies objects in the
DOC>   database have been invalidated and their settings set to native.
DOC>
DOC>   Shut down and restart the database in normal mode and
DOC>   run utlrp.sql to recompile invalid objects.
DOC>#######################################################################
DOC>#######################################################################

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 3240239104 bytes
Fixed Size                  2148760 bytes
Variable Size            1577059944 bytes
Database Buffers         1644167168 bytes
Redo Buffers               16863232 bytes
Database mounted.
Database opened.
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

.....

Upon completion of the above script , you will see the following message .

SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> SET serveroutput on
SQL> EXECUTE dbms_registry_sys.validate_components;
Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error

PL/SQL procedure successfully completed.

SQL> SET serveroutput off.

SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL>

You can check the compilation mode for all the objects in the database using the following sql . 

  1     SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*)
  2     FROM DBA_PLSQL_OBJECT_SETTINGS
  3     WHERE PLSQL_CODE_TYPE IS NOT NULL
  4     GROUP BY TYPE, PLSQL_CODE_TYPE
  5*    ORDER BY TYPE, PLSQL_CODE_TYPE
SQL> /

FUNCTION        NATIVE                 386
PACKAGE         NATIVE                1288
PACKAGE BODY    NATIVE                1225
PROCEDURE       NATIVE                 655
TRIGGER         NATIVE                 488
TYPE            INTERPRETED           2288
TYPE            NATIVE                 321
TYPE BODY       NATIVE                 224

8 rows selected.

Bear in mind , TYPES will not in NATIVE mode , as it does not have a executable code.

Good luck.

Analytics 2.0

Jumping ahead from basic analytics to analytics 2.0 ( new feature in 11g R2) 

One simple yet  neat , nicer addition to analytics family in 11g R2.
There is a new analytic function called LISTAGG  , where I could concatenate the list of the data values in a
particular group .

Prior to this release , we would have used hierarchical  query ... SYS_PATH to a achieve this result set.


Let us proceed with a simple example


SQL> Create table  t as select * from all_objects;

Table created

SQL>
SQL>         Select owner , object_type , listagg(object_name , '~')
  2           within group
  3           (order by object_name )
  4           from t
  5           where owner in ('SCOTT' , 'OUTLN')
  6           group by owner , object_type
  7           order by object_type
  8  ;

OWNER                          OBJECT_TYPE         LISTAGG(OBJECT_NAME,'~')WITHIN
------------------------------ ------------------- --------------------------------------------------------------------------------
OUTLN                          INDEX               OL$HNT_NUM~OL$NAME~OL$NODE_OL_NAME~OL$SIGNATURE
SCOTT                          INDEX               PK_DEPT~PK_EMP
OUTLN                          PROCEDURE           ORA$GRANT_SYS_SELECT
OUTLN                          TABLE               OL$~OL$HINTS~OL$NODES
SCOTT                          TABLE               BONUS~DEPT~EMP~SALGRADE


Note : ~ is the delimiter

Bear in mind , the result set is of datatype varchar2( unless otherwise the columns defined in the group are RAW) , so you may be hit the limitation of varchar2(4000).

SQL>        Select owner , object_type , listagg(object_name , '~')
  2           within group
  3           (order by object_name )
  4           from t
  5           where owner in ('SCOTT' , 'SYS')
  6           group by owner , object_type
  7           order by object_type
  8  ;

Select owner , object_type , listagg(object_name , '~')
         within group
         (order by object_name )
         from t
         where owner in ('SCOTT' , 'SYS')
         group by owner , object_type
         order by object_type

ORA-01489: result of string concatenation is too long

Have a good time with analytcs.
Please refer to http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions087.htm for additional informaiton .