Database, SQL and PL/SQL

Tips for Migrating, Indexing, and Using Packaged Procedures

Looking for advice on upgrading database servers, speeding queries, or avoiding problems with compiling? Our Oracle expert has all the answers.

By Tom Kyte Oracle Employee ACE

September/October 2000

Looking for advice on upgrading database servers, speeding queries, or avoiding problems with compiling? Our Oracle expert has all the answers.

Our clients are migrating their databases from Oracle7 Release 7.3 to Oracle8 Release 8.0. Can you give us some strategies for migrating to Oracle8 in order to improve performance?

We have the following questions:
1. Is it possible to enjoy their new features without changing our applications code?
2. Which features are possible to use with old applications, and which ones are only possible to use with new ones?
3. How significant is the impact on the applications?

Hopefully you are moving to Oracle8i Release 8.1, not 8.0.

A simple migration of an application from Oracle7 Release 7.3 to Oracle8i probably will not gain you an immediate performance improvement. You'll need a "port" of the application from 7.3 to 8i to see that in most cases.

In some cases, you can take advantage of new features without changing anything. For example, one of the easiest ways to speed up a Pro*C application is to employ array processing, especially on SELECTS. This used to involve substantially changing the logic in the client. For example, the code

exec sql open c;
for( i=0;;i++ )
{
EXEC SQL WHENEVER NOTFOUND do break;
EXEC SQL FETCH C INTO :value;
printf( "row %d = %.*s\n", i, value.len, value.arr };
}
EXEC SQL WHENEVER NOTFOUND CONTINUE;
EXEC SQL CLOSE C;

becomes a series of somewhat complicated loops and arrays:

EXEC SQL OPEN C;
for( rows_fetched = 0, done = 0;
!done;
rows_fetched=sqlca.sqlerrd[2] )
{
EXEC SQL FETCH C INTO :value;
done=(sqlca.sqlcode==1403); for( i = 0; i < sqlca.sqlerrd[2] - rows_fetched; i++ )
{
printf( "row %d is '%.*s'\n",
rows_fetched+i+1,
value[i].len, value[i].arr );
}
}
EXEC SQL CLOSE C;

In Oracle8i, rather than change my code from the simple to the complex, I can simply recompile with:

$ proc .... PREFETCH=200

Now Pro*C will implement array fetching for me, transparently. When I ask for 1 row, it'll get 200 and save them for me. The next 199 times I ask for a row, it'll just get it from memory—no network hits. I get all of the advantages of array fetching but none of the pain.

Other features, such as the ability to do array processing in PL/SQL, will require a modification to your code. For example, I can take a simple routine, such as the following:

ops$tkyte@8i> begin
2 for j in 1 .. 5000 loop
3 insert into insert_into_table values ( j );
4 end loop;
5 commit;
6 end;
7 / PL/SQL procedure successfully completed.
Elapsed: 00:00:00.72

and recode it as:

ops$tkyte@8i> declare
2 type numTab is table of number(4) index by binary_integer;
3 data numTab;
4 begin
5 for j in 1 .. 5000 loop
6 data(j) := j;
7 end loop;
8
9 forall i in 1 .. 5000
10 insert into insert_into_table? values( data(i) );
11 commit;
12 end;
13 / PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10

They do the same thing; the second one just does it much faster. There is an array fetch in PL/SQL as well now: BULK COLLECT. It will speed queries in a similar fashion.

Other new features, such as the NOCOPY feature on PL/SQL parameters—making parameter passing of large structures/tables much faster—are very simple to implement. Just add one word to the definition of the stored procedure, and away you go.

Function-based indexes are another feature you can take advantage of without changing anything. Say your application has tons of queries with:

select * from T where upper(some_column) = :x;

In the past, you could not use any indexes on some_column to speed that query. Now you can.

Oracle8i Release 8.0 and 8.1 added hundreds of features, so I cannot possibly list each one you could use out of the box without changing your code—especially without knowing what your code looks like. Take a look and decide for yourself which new features apply and which do not. Some, such as partitioning, you can slide in "under" the application without change. Others, such as BULK COLLECT in PL/SQL, require you to make modifications before you can take advantage of them.

Creating Indexes on Foreign Keys

I have read several books that repeatedly mention creating indexes on foreign keys. I know one advantage is that this eliminates table-level locks, and I have seen the benefit, since I encountered a similar problem. However, I would like to know if you would recommend doing this for all foreign-key constraints or only for those that create the locking problem.

Table locks can arise if you delete a parent record or update the primary key of a parent record and have unindexed foreign keys, so the issue is a real one. You'll find a script for detecting unindexed foreign keys at http://asktom.oracle.com.

In addition to the table-lock issue that might hit you, an unindexed foreign key is bad in the following cases as well: 1. When you have an on delete cascade and have not indexed the child table. For example, EMP is child of DEPT. Delete deptno = 10 should cascade to EMP. If deptno in emp is not indexed, you will get a full table scan of EMP. This full scan is probably undesirable, and if you delete many rows from the parent table, the child table will be scanned once for each parent row deleted. 2. When you query from the PARENT to the CHILD. Consider the EMP, DEPT example again. It is very common to query the EMP table in the context of a deptno.

If you frequently query:

select * from dept, empwhere emp.deptno = dept.deptno and dept.deptno = :X;

to generate a report or something, you'll find that not having the index in place will slow down the queries.

So when do you not need to index a foreign key? In general, when the following conditions are met: 1. When you do not delete from the parent table (especially with delete cascade—this is a double whammy). 2. When you do not update the parent table's unique/primary key value. 3. When you do not join from the PARENT to the CHILD (such as DEPT->EMP).

If you satisfy all three conditions above, feel free to skip the index: You don't need it. If you do any of the above, be aware of the consequences.

As for the effect of an index on a foreign-key index on an OLTP system—it depends. If you do not frequently update the foreign key, the overhead is during the insert and might not be noticed. If you update it frequently, the overhead might be worse. It's like any other index—you just have more reasons to consider adding that index than would normally be the case.

Automatic Compiling

I've got two procedures. A calls B to do something. If I compile B, then A will become invalid. Is there any setting I can have in the database in order to compile A automatically when B is compiled?

I can't stress this to our readers enough: Don't use standalone procedures! Use packages instead. Packages break the dependency chain.

If procedure A calls procedure B and B is "changed," then A is directly affected and must be recompiled. There is no "auto recompile A when B changes" option—although A will recompile itself automatically the next time it is run.

On the other hand, if I create a package PKG_A with a procedure A and a package PKG_B with a procedure B, then PKG_A will be dependent on PKG_B's specification. I can compile and recompile PKG_B's body as often as I like without affecting PKG_A 's state. As long as PKG_B's specification or interface does not change, dependent objects are not affected by the recompilation of the body.

What follows is a small example showing the differences:

ops$tkyte@8i> create or replace procedure B
2 as
3 begin
4 null;
5 end;
6 /
Procedure created.
ops$tkyte@8i>
ops$tkyte@8i> create or replace procedure A
2 as
3 begin
4 B;
5 end;
6 / Procedure created.

Here, procedure A calls B. One of the downsides to procedures, in addition to the direct dependency, is that you have to create them in the "right" order if you want everything valid in the database after an install. I had to create B before A. Packages don't have this nuance either—as shown below:

ops$tkyte@8i> create or replace package pkg_a
2 as
3 procedure a;
4 end;
5 / Package created.
ops$tkyte@8i> create or replace package? pkg_b
2 as
3 procedure b;
4 end;
5 / Package created.

This time, I created the specs for the packages. They are not dependent on each other; in fact, you can create the specs in any order:

ops$tkyte@8i>
ops$tkyte@8i> create or replace package body pkg_a
2 as
3 procedure a
4 is
5 begin
6 pkg_b.b;
7 end;
8 end;
9 / Package body created. ops$tkyte@8i> create or replace package body pkg_b
2 as
3 procedure b
4 is
5 begin
6 null;
7 end;
8 end;
9 / Package body created.

Here, I create the bodies. In general, I can create the bodies in any order after the specs. PKG_A is dependent on PKG_B's spec—not its body—so we don't have an ordering problem.

ops$tkyte@8i>
ops$tkyte@8i> @invalid
ops$tkyte@8i> break on object_type skip 1
ops$tkyte@8i> column status format a10
ops$tkyte@8i> select object_type, object_name, status
2 from user_objects
3 where status = 'INVALID'
4 order by object_type, object_name
5 / no rows selected

Nothing is invalid in this example. Now, let's "reimplement" procedure B in the standalone procedure and in the packaged procedure. As you can see, standalone procedure A immediately goes invalid. B was changed, so A needs to be recompiled (see Listing 1).

In contrast, PKG_A never goes invalid and thus never needs to be recompiled, since only PKG_B's body was modified. As long as the spec doesn't change, PKG_A (see Listing 2) will remain valid. I urge people to use only packages for "real" code. This can improve performance in a database by dramatically reducing the number of times things get compiled. If you do everything in standalone procedures, a simple fix to one procedure might make your entire set of routines go invalid.

Code Listing 1:Changes to B Invalidate A

ops$tkyte@8i>
ops$tkyte@8i> create or replace procedure B
 2 as
 3 begin
 4   null;
 5 end;
 6 /
Procedure created.
ops$tkyte@8i>
ops$tkyte@8i> @invalid
ops$tkyte@8i> break on object_type skip 1
ops$tkyte@8i> column status format a10
ops$tkyte@8i> select object_type, object_name, status
 2 from user_objects
 3 where status = "INVALID"
 4 order by object_type, object_name
 5 /
OBJECT_TYPE     OBJECT_NAME     STATUS
---------       -----------     -------
PROCEDURE       A               INVALID

Code Listing 2: Changes to B Do Not Affect A

ops$tkyte@8i>
ops$tkyte@8i> create or replace package body pkg_b
 2 as
 3   procedure b
 4   is
 5   begin
 6     null;
 7   end;
 8 end;
 9 /
Package body created.
ops$tkyte@8i> @invalid
ops$tkyte@8i> break on object_type skip 1
ops$tkyte@8i> column status format a10
ops$tkyte@8i> select object_type, object_name, status
 2 from user_objects
 3 where status = "INVALID"
 4 order by object_type, object_name
 5 /
OBJECT_TYPE     OBJECT_NAME     STATUS
---------       -----------     -------
PROCEDURE       A               INVALID
ops$tkyte@8i>
Next Steps

 ASK Tom
Tom Kyte answers your most difficult technology questions. Highlights from that forum appear in this column.

 FOLLOW Tom on Twitter

READ
 more Tom
 Oracle Database Concepts 11g Release 2 (11.2)
 Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions,Second Edition

 DOWNLOAD Oracle Database 11g Release 2

FOLLOW Oracle Database
 on Twitter
 on Facebook



 

DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.