Oracle Pro Tips, Trends & Technology eNewsletter
Pinnacle Publishing http://www.pinnaclepublishing.com
Issue 2.26
January 9, 2002

TABLE OF CONTENTS

1) Pop Quiz (answer at the end)
2) Did You Know...
3) URLs of the Issue: Oracle Books
4) SQL TIP: Multi-threading to Improve Performance
5) Industry News
6) Answer to the Pop Quiz

---------------------------------------------------------
1) POP QUIZ (answer at the end)
---------------------------------------------------------

How you do find out object dependencies in Oracle? In
other words, how do you determine hierarchy of
relationships among the objects -- which object calling
which object?

---------------------------------------------------------
2) DID YOU KNOW...
---------------------------------------------------------

* that you can return a set of records from Oracle into
Java via JDBC? In particular, you can exchange cursor
variables (pointers to cursors) between Java and Oracle.
A detailed example can be found in the Oracle JDBC
documentation at http://technet.oracle.com/ as well as
the following link:

http://iron.gps.caltech.edu/doc/java.815/a64685/samapp3.htm - 1001596.

---------------------------------------------------------
3) URLs OF THE ISSUE: Oracle Books
---------------------------------------------------------
O'Reilly has published a new book titled "Java
Programming with Oracle JDBC." You can find out more at:

http://www.oreilly.com/catalog/jorajdbc/.

A chapter of the book is available for review at:

http://www.oreilly.com/catalog/jorajdbc/chapter/ch19.html.

---------------------------------------------------------
4) SQL TIP: Multi-threading to Improve Performance
---------------------------------------------------------
An easy way to improve performance of a long-running
process is to break it up into concurrent processes (most
servers have multiple CPUs these days). The idea is
simple and easy to implement.

You can do this with any process which can be divided
into multiple processes without having to rewrite it.
Normally, this applies to processes which have one query
and one loop to process the records. So you just add a
condition in the query to do a range of records rather
than all.

We've developed generic routines using DBMS_JOB and
DBMS_PIPE for this purpose. Let's see if they meet your
needs.

procedure p_spawn_threads
-- Input is a PL/SQL table (one record per thread) which
contains the call to the actual procedure that needs to
be executed along with all of the parameters for the
procedure.
-- This routine uses DBMS_PIPE to establish a unique pipe
-- And it uses DBMS_JOB to spawn each thread (by invoking
p_execute_thread).
-- It uses DBMS_PIPE.receive to wait for each thread to
finish.
-- The routine returns success or error message with
error string.

procedure p_execute_thread
-- It executes the actual procedure.
-- It uses DBMS_PIPE.send to return success or failure to
the calling routine, p_spawn_threads.

Let me know if you've come up with other ideas.

---------------------------------------------------------
5) IDUSTRY NEWS: Hot Jobs for 2002
---------------------------------------------------------

Amid the gloom and doom in the IT industry, there still
seems to be quite a few opportunities available. The
following article talks about where these opportunities
may be:

http://www.eweek.com/article/0,3658,s%253D703%2526a%253D20562,00.asp.

---------------------------------------------------------
6) ANSWER TO THE POP QUIZ
---------------------------------------------------------

Oracle provides a utility script, called utldtree.sql,
that displays object dependencies. It is stored in the
$ORACLE_HOME/rdbms/admin directory.

Log into the account whose objects you wish to analyze,
and then run the script, as illustrated in the following
example:

SQL> @c:\oracle\ora81\rdbms\admin\utldtree.sql

The utility creates a stored procedure called
deptree_fill and a couple of database views, namely,
deptree and ideptree. You use the deptree_fill routine to
specify the object for which you wish to display the
dependencies. It has the following format:

deptree_fill('table', '<table_owner>', '<table_name>');

For instance, we can reveal the dependencies for the
"dual" table owned by the "sys" database user as follows:

SQL> execute deptree_fill('table', 'sys', 'dual');

The deptree_fill procedure creates data that can be
viewed using "deptree" or "ideptree." The "ideptree"
shows the dependencies on indented rows. Each level of
indentation shows the invocation of an object (package,
view, etc.) by the object in the indented row. In the
sample output below, the "SYS.DUAL" table is invoked by
the "SYS.STANDARD" package body, and the "sys.exu7cpo"
view, etc. Note that it is also invoked by the
"CASE.SDD_A20"view, and the "CASE.SDD_A20" view is in
turn invoked by the "CASE.CI_NETWORK_CONNECTIONS" view.

SQL> select * from ideptree;

DEPENDENCIES
---------------------------------------------------------
-----------------------
TABLE SYS.DUAL
   PACKAGE BODY SYS.STANDARD
   VIEW SYS.EXU7CPO
   PACKAGE BODY SYS.DBMS_PCLXUTIL
   PACKAGE BODY SYS.DBMS_LOCK
   PACKAGE BODY SYS.DBMS_JOB
   PACKAGE BODY SYS.DBMS_PRVTAQIP
   PACKAGE BODY SYS.DBMS_IREFRESH
   PACKAGE BODY SYS.DBMS_SNAP_INTERNAL
   PACKAGE BODY SYS.DBMS_SUMREF_CHILD
   PACKAGE BODY SYS.DBMS_PLUGTS
   PACKAGE BODY SYS.DBMS_RCVMAN
   PACKAGE BODY SYS.DBMS_BACKUP_RESTORE
   PACKAGE BODY SYS.DBMS_LOGMNR_D
   PACKAGE BODY SYS.DBMS_EPGC
   PACKAGE BODY SYS.DBMS_HS_EXTPROC
   PACKAGE BODY SYS.DBMS_HS_UTL
   PACKAGE BODY CASE.JR_GEN
   PACKAGE BODY CASE.JR_META
   PACKAGE BODY CASE.JR_NAME
   PACKAGE BODY CASE.JR_SYS_PRIVS
   PACKAGE BODY CASE.JR_WORKAREA
   VIEW CASE.SDD_A20
      VIEW CASE.CI_NETWORK_CONNECTIONS
      VIEW CASE.RM_ELEMENTS
         PACKAGE BODY CASE.CIMTREE
         PACKAGE BODY CASE.RMMTXI
      PACKAGE BODY CASE.CIONETWORK_CONNECTION
      FUNCTION CASE.PK_SDD_A20
         TRIGGER CASE.SDD_A20_VAL
      TRIGGER CASE.SDD_A20_D
      TRIGGER CASE.SDD_E20_D

---------------------------------------------------------
Well, that's it for this week. I welcome your feedback,
input, tips, suggestions, Web sites, and other Oracle-
related news. If you send me something, please let me
know whether I can use your name with your comments. I
apologize in advance if I don't respond personally to
each of your questions or suggestions, but I'll get to as
many as I can in the eNewsletter if not personally.

Garry Chan, Editor
Database Architect
mailto:GChan@ProcaseConsulting.com

ORACLE PRO eXTRA (BI-WEEKLY)
To be removed from this mailing list, forward this message to
pinnacleor-unlist@dsi-epubs.net. (be sure to forward the ENTIRE
message, or it will not unsubscribe you!)

**********************************************************************