In this section, we discuss when to use and how to debug Java stored procedures. Since Java stored procedures run within the Oracle database’s memory space and consume database resources, there are special considerations when using Oracle JVM.
In practice, because the Oracle XML DB provides high-performance native XML features, its use should be your first choice for XML processing in your applications. If these features do not meet your requirements, you can implement Java stored procedures or leverage midtier or client-side XML support.
However, for data-intensive Java code that constantly connects to a database, you need to deploy it inside Oracle JVM and run it as a Java stored procedure. Because Java stored procedures run inside the RDBMS, they have less round-trip overheads for data transport.
On the other hand, though the round-trip expenses for Java stored procedures are less than the midtier or client-side programs, they still have high costs for data round trips between Oracle JVM and the database server. This can be especially true if there are many datatype conversions between SQL and Java.
Another performance bottleneck can result if there is a high number of Java method calls within a Java stored procedure or overall database application. Every Java call needs to be translated into an internal Oracle JVM method call. This translation is performed by using a very large lookup table to find the name mapping between PL/SQL and Java. Even though this table is indexed, the lookup process time can easily exceed the Java code execution time. The trick is to put as much Java code into the Java source file and make very few Java calls from the Java stored procedure.
Therefore, when designing your Java code, you need to make sure to minimize the interactions between the Oracle database and the Java program. For example, if you would like to use SAX to extract data from an XML document using XPath, instead of designing a Java stored procedure that allows the user to pass one XPath expression at a time, you can design it to allow the user to pass in all the XPath expressions in an array, and let the Java code process all of them in one batch process.
To tune the performance of the Java stored procedures, you also need to properly set the SHARED_POOL_SIZE, JAVA_POOL_SIZE, and JAVA_MAX_SESSIONSPACE_SIZE. All of these affect the memory usage of Java stored procedures. To avoid getting Out Of Memory errors from Oracle JVM, you need to give a reasonably large size for SHARED_POOL_SIZE and JAVA_POOL_ SIZE and limit the size for JAVA_MAX_SESSIONSPACE_SIZE. These are set in your init.ora file and can be changed from Enterprise Manager or in SQL*Plus.
You can debug Java stored procedures by printing out success and error messages. In these examples, we printed all the messages to a StringWriter and passed the content back as a return value. Alternatively, you can output the debug messages using System.out or System.err. By default, the messages will be printed to the current database trace files. In Chapter 9, we discussed how to check the current session ID to locate the correct trace file in USER_DUMP_DIR. However, if you want to redirect the output to the SQL*Plus text buffer, you can use the following command:
SQL> SET SERVEROUTPUT ON SQL> CALL DBMS_JAVA.SET_OUTPUT(2000);
If this is not sufficient to discover the problem, you can also use the Remote PL/SQL Debugging feature of Oracle JDeveloper 10g to debug Java stored procedures.
To set up remote PL/SQL debugging you first need to open the Tools | Project Properties dialog box and check the Remote Debugging check box under the Profiles | Development | Debugger | Remote option. You then choose Listen for JPDA from the radio buttons and locate an appropriate database connection that you will run the PL/SQL code from.
Next, you can set breakpoints in the Java code and click the Debug button to start your debugging session. A dialog box will pop up asking you which port you want to listen on. You can enter any available port, “4000” as an example, and click OK. You will see a Debug Listen icon under the Processes folder in the View | Application Navigator window, which shows that the debugger is listening.
After this, you can open SQL*Plus and issue the following command:
ALTER SESSION SET PLSQL_DEBUG=TRUE CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '127.0.0.1', '4000' );
The address 127.0.0.1 is the IP address for the machine running Oracle JDeveloper, and 4000 is the port that the debugger is listening on. At this point, the Oracle JDeveloper debugger should have accepted the debugging connection, and you will see a new debugging process under the Processes folder. In addition, the log window should have the message Debugger Accepted Connection From Remote Process On Port 4000.
You then can return to SQL*Plus and issue a command that invokes your PL/SQL procedure calling the Java stored procedures. When the breakpoints are hit, the Oracle JDeveloper debugger will stop at the breakpoints and show all the related debugging information.
| Note |
To run the procedure to debug, you need to grant the DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE privileges to the database user. |
In addition to code debugging, you can check the validity of the Java classes using the database connection in Oracle JDeveloper 10g. Alternatively, you can use the following SQL command to query the USER_OBJECT view, which shows the Java classes owned by the current DB user and the status of the classes:
SELECT DBMS_JAVA.LONGNAME (object_name), status FROM USER_OBJECTS WHERE OBJECT_TYPE = 'JAVA CLASS';
You can use the ALL_OBJECTS view to check all the Java classes available to the current user. If the status of any Java class is shown as INVALID, you need to reload the class.