Application Code

There are some features that aid in

•  The run-time performance of applications, namely procedures, functions, and triggers

•  Better design of SQL statements

•  The use of unrecoverable transactions

•  Pinning application code in the shared pool

•  External procedures

Oracle8 implemented a new ROWID format. It uses 64-bit encoding. For sizing purposes, ROWID takes 10 positions in the row. The ROWID contains an object number (6 positions), file number (3 positions), block number (6 positions), and slot number (3 positions). Oracle8 refers to the old ROWID format as restricted ROWID).

Oracle supports the compilation of functions, procedures, and triggers (procedural objects). The resulting p-code is stored in the data dictionary, along with the procedural object. The p-code is then used when the objects are referenced, saving considerable run-time time by not having to create the p-code on demand. The compiler is automatically invoked when the procedural object is created or altered. Changes to the table structures can invalidate procedural objects, requiring them to be compiled again at run time. The following code shows the SQL syntax for invoking the compiler.

alter procedure <PROCEDURE NAME> compile

alter function <FUNCTION NAME> compile

alter trigger <TRIGGER NAME> compile

If the procedure or function is part of a package, the whole package can be compiled or just the body.

alter package <PACKAGE NAME> compile package

alter package <PACKAGE NAME> compile body

Some SQL statements with nested subqueries can create an enormous amount of I/O traffic. Let’s consider the examples in Listings 24.11 and 24.12. Listing 24.11 is a correlated subquery where the inner query is executed one time for each row returned by the outer query. Listing 24.12 produces the same result but with less I/O as it queries the look up table (the subquery of Listing 24.11) once, not once per each row.

Listing 24.11. Correlated SQL statement.

update EMP

set sal = sal * 10

where exists

    (select 'x' from DEPT

     where DEPT.deptno = EMP.deptno) 

Listing 24.12. PL/SQL loop example.

DECLARE

cursor c1 is select deptno from dept;

work_deptno  number;

BEGIN

open c1;

loop

    fetch c1 into work_deptno;

    EXIT when c1%NOTFOUND

update emp

    set sal = sal * 10

    where deptno = work_deptno;

end loop;

END; 

Unrecoverable transactions are simply transactions that are not recoverable via the Oracle recovery processes. They do not use the rollback segments, the online redo, or, if implemented, the archive log processes. The UNRECOVERABLE clause can be used with create table <TABLE NAME> as select …, create index <INDEX NAME>, and alter table <TABLE NAME> add constraint …. SQL*Loader with the direct path option does not use any of Oracle’s transaction-consistency or recovery features either. If any of these processes fails, the creator may need to manually drop the objects being loaded or created and re-execute the process.

The dbms_shared_pool package can be used to lock packages, procedures, and functions in the shared pool. Likewise, when these packages, procedures, and functions are no longer required, the dbms_shared_pool package can also remove these locked packages from the shared pool (see the following example syntax).

dbms_shared_pool.keep('<PROCEDURE NAME>')

dbms_shared_pool.unkeep('<PROCEDURE NAME>') 

External procedures were introduced with Oracle8. They allow additional functionality between the database and other external systems. These external procedures can be referenced in PL/SQL, which utilizes the Net8 listener process to call the external routine (C program today, futures include C++, Java, and CORBA). The Net8 listener calls extproc, which in turn executes the external routine. The extproc will also pass any returned values back to the originating PL/SQL routine via the listener. Figure 24.5 illustrates a simple call from a PL/SQL routine on one machine, utilizing the listener, accessing a “c” program that sends a fax.

Figure 24.5.  External procedure call.