Oracle Magazine Issue Archive
TECHNOLOGY: Ask Tom
Tips for Migrating, Indexing, and Using Packaged ProceduresBy Tom Kyte
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: 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
becomes a series of somewhat complicated loops and arrays:
In Oracle8i, rather than change my code from the simple to the complex, I can simply recompile with:
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:
and recode it as:
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:
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 KeysI 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: If you frequently query:
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: 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 CompilingI'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:
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:
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:
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.
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>
|
Printer View