5 Tuning and Troubleshooting

Early Binding of OO4O Objects

The early binding technique tightly typecasts OO4O objects to their native object types rather than the generic object type provided by Visual Basic. These objects are declared directly as OO4O objects, rather than as generic objects which are later reclassified as OO4O objects. Early binding improves performance by reducing frequent access to the OO4O type library. For example:

'Early binding of OO4O objects 
Dim OraSession as OraSession 
Dim OraDatabase as OraDatabase 
Dim OraDynaset as OraDynaset 
 
'Generic binding of OO4O objects 
 
Dim OraSession as Object 
Dim OraDatabase as Object 
Dim OraDynaset as Object 

To use early binding of OO4O objects, the Oracle In-Process Server type library must be referenced in the Visual Basic projects.

Tuning and Customization

Data access can be tuned and customized by altering the cache and fetch parameters of a dynaset. Setting the FetchLimit parameter to a higher value increases the number of rows that are fetched with each request, thus reducing the number of network trips to Oracle Database, and improving performance.

The cost of increasing the size of the FetchLimit parameter is that it increases memory requirements on the client side, and causes more data to be swapped to and from the temporary cache file on disk. The proper FetchLimit value should be set according to the client computer configuration and the anticipated size of the query result.

The FetchLimit value can be set in the following ways:

• By using the CreateCustomDynaset method

• By modifying parameters of the OO4O entry in the Windows registry

For Windows, the registry key is HKEY_LOCAL_MACHINE and the subkey is software\oracle\KEY_HOMENAME\oo4o, where HOMENAME is the appropriate Oracle home. The OO4O installation creates the following section in the registry:

"FetchLimit" = 100

Avoiding Multiple Object Reference

Improper coding techniques with unnecessary object references can also affect performance. During dynaset object navigation, you should reduce the number of object references to the OraFields collections and OraField objects. The following is an inefficient code block:

'Create the OraDynaset Object 
Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&) 
 
'Traverse until EOF is reached 
Do Until OraDynaset.EOF 
    msgbox OraDynaset.Fields("sal").value 
OraDynaset.MoveNext 
Loop 

The OraDynaset, OraFields collections, and OraField objects are referenced for each iteration. Although OO4O provides improvement in handling the field collections object, multiple references to the automation object goes though the underlying OLE/COM automation layer, which slows down the execution.

The following example shows how to reference fields through a field object and not through the fields collection of the dynaset. Testing has determined that this small amount of extra code greatly improves performance.

Dim flds() As OraField 
Dim i, fldcount As Integer 
 
' Create the OraDynaset Object 
Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&) 
' Get the field count, and output the names 
fldcount = OraDynaset.Fields.Count 
ReDim flds(0 To fldcount - 1) 
 
For i = 0 To fldcount - 1 
Set flds(i) = OraDynaset.Fields(i) 
Next I 
'Traverse until EOF is reached 
 
Do Until OraDynaset.EOF 
   msgbox Flds(5).Value 
   msgbox Flds(6).Value 
OraDynaset.MoveNext 
Loop 

Any method or object that is referenced through more than one object is potentially inefficient, but the extra coding to avoid this is not always worth the time saved. The best place to start is with field references, because they are most likely to occur multiple times.

Parameter Bindings

OO4O provides a way of enabling and disabling parameter object binding at the time it processes the SQL statement. This can be done through the AutoBindDisable and AutoBindEnable methods of the OraParameter object. If the SQL statement does not contain the parameter name, it is better to disable the OraParameter object because it avoids an unnecessary reference to the parameter object. This is most effective when the application is written primarily using PL/SQL procedures. For example:

Set OraDatabase = OraSession. OpenDatabase("Exampledb", "scott/tiger", 0&) 
 
'Add the job input parameter with initial value MANAGER. 
OraDatabase.Parameters.Add "job", "MANAGER", 1 
 
'Add the deptno input parameter with initial value 10. 
OraDatabase.Parameters.Add "deptno", 10, 1 
 
'Add the job input parameter with initial value MANAGER. 
OraDatabase.Parameters.Add "EmpCur", 0, 1 
OraDatabase.Parameters("Empcur").ServerType = ORATYPE_CURSOR 
 
'Disable the job parameter for now. 
OraDatabase.Parameters("job").AutoBindDisable 
 
set OraSqlStmt = CreateSQL("Begin GetEmpData(:Empcur, :deptno) End;",0&) 

Note how the job parameter object is not referenced while processing the PL/SQL statement.

Array Processing

OO4O supports an array interface to an Oracle database through the OraParamArray object. The array interface enables the transfer of bulk of data in single network trip. This is especially helpful while processing a PL/SQL or SQL statement through the ExecuteSQL or CreateSQL method. For example, in order to insert 100 rows into remote database without array processing, ExecuteSQL or CreateSQL must be called 100 times, which in turn makes 100 network trips. For example:

For I = 1 to 100 
   OraParameter("EMPNO").Value = xxxx 
   OraParameter("ENAME").Value = 'yyyy' 
   OraParameter("DEPTNO").Value = zz 
   OraDatabase.ExecuteSql("insert into emp values (:EMPNO,:ENAME,:DEPTNO)"); 
Next I 

The following example makes use of arrays and makes only one network trip.

'ENAMEARR,:EMPNOARR,:DEPTNOARR are parameter arrays 
 
For I = 1 to 100 
   OraParameter("EMPNOARR").Put_Value xxxx, I 
   OraParameter("ENAMEARR").Put_Value 'yyyy' ,I 
   OraParameter("DEPTNOARR").Put_Value zz, I 
Next I 
 
'Now call the ExecuteSQL only once 
OraDatabase.ExecuteSql("insert into emp values(:EMPNOARR," & _
              ":ENAMEARR, :DEPTNOARR)"); 

Using Read-Only, Forward-Only Dynaset

If your application does not make any updates to the dynaset, then you can create a read-only dynaset with the ORADYN_READONLY (H4) option. With this option, performance improvement can be gained by eliminating the overhead of parsing SQL statements locally and reducing network trips for SQL statement execution.

If your application does not need a scrollable dynaset, then you can create a forward-only dynaset with the ORADYN_NOCACHE (H8) option. With this option, performance improvement can be gained by eliminating the overhead of creating a local cache file and the overhead of reading/writing data from that file.

Using the PL/SQL Bulk Collection Feature

The PL/SQL bulk collection feature enables the selecting of bulk data in a single network trip using PL/SQL anonymous blocks. The OO4O OraDynaset object selects arrays of data during SQL statement execution. This involves overhead such as performing more network round-trips, creating more cache files and internal objects. If you do not want to use a dynaset due to its overhead, then this feature is useful for selecting arrays of data. The data to be selected can be bound either as an OraParamArray object or as an OraCollection object.

The following example illustrates PL/SQL bulk collection features using the OraCollection interface. It shows how arrays of enames are selected with one network round-trip and less overload.

Set OraDatabase = OraSession.OpenDatabase("Exampledb", "scott/tiger", 0&) 
 
'create a VARRAY type ENAMELIST in the database 
OraDatabase.ExecuteSQL ("create type ENAMELIST as VARRAY(50) OF VARCHAR2(20)")
 
'create a parameter for ENAMELIST VARRAY 
OraDatabase.Parameters.Add "ENAMES", Null, ORAPARM_OUTPUT, 247,"ENAMELIST"  

'execute the statement to select all the enames from ename column of emp table
OraDatabase.ExecuteSQL ("BEGIN select ENAME bulk collect into" & _ 
                ":ENAMES from emp; END;") 
 
'here OraParameter object returns EnameList OraCollection 
Set EnameList = OraDatabase.Parameters("ENAMES").Value 
 
'display all the selected enames 
FOR I = 1 to EnameList.Size 
   msgbox Enamelist(I) 
NEXT I 

Migration from LONG RAW to LOB or BFILE

Oracle8i introduced the following new types described in "Using Large Objects (LOBs)":

• BLOB

• CLOB

• BFILE

The design of these types allows OO4O to access them much faster than using LONG or LONG RAW types. For this reason, convert existing LONG RAW code to BLOB, CLOB, and BFILE, and only use LOBs and BFILEs for new applications. The OraLOB object should be used to access LOB and BFILE types, rather than these LONG RAW chunking methods, which are provided for backward compatibility only. Note that OraLOB offers maximum control.

LOB data types differ from LONG and LONG RAW data types in several ways:

• A table can contain multiple LOB columns, but can contain only one LONG column.

• A table containing one or more LOB columns can be partitioned, but a table containing a LONG column cannot be partitioned.

• The maximum size of a LOB is 4 gigabytes, but the maximum size of a LONG is 2 gigabytes.

• LOBs support random access to data, but LONGs data types support only sequential access.

• LOB data types (except NCLOB) can be attributes of a user-defined object type, but LONG data types cannot.

• LOB client-side buffering is used to optimize multiple small writes.

• LOB data can be stored in operating system files outside of database tablespaces (BFILE types).

To make migration easier, the following methods can be used with BLOB, CLOB, and BFILE types:

• AppendChunk Method

• AppendChunkByte Method

• GetChunk Method

• GetChunkByte Method

• GetChunkByteEx Method

• ReadChunk Method

For older applications using the LONG RAW chunking methods, migration should not require a lot of changes to the code. The primary code changes involve the requirement that null BLOB and CLOB types be updated with empty before being used.

Using Connection Pooling

The connection pool in OO4O is a pool of OraDatabase objects. An OO4O connection pool is a group of (possibly) already connected OraDatabase objects. For applications that require constant connections and disconnections to the database, such as ASP Web applications, using a connection pool results in enhanced performance.

OLE Automation Errors

The programmatic interface of the OO4O automation server is the OO4O In-Process Automation server. Errors that occur during execution of methods are frequently reported as an OLE Automation Error (ERR = 440, ERROR$="OLE Automation Error").

When an error occurs, check the LastServerErr property of the OraSession and OraDatabase objects to determine whether an Oracle database error has occurred. If the LastServerErr is not zero, then an error has been raised by the OO4O automation server.

To find OO4O automation server errors, scan the string returned by the ERROR$ function for the string "OIP-NNNN" where NNNN is an error number included in the Table 5-1.

Table 5-1 lists the Oracle OLE automation errors.

Nonblocking Errors

Table 5-2 lists the nonblocking errors.

Find Method Parser Errors

Find method parser errors occur when the parser cannot evaluate the expression in the Find method. These errors specify the part of the expression that caused the error.

Table 5-3 lists the Find method parser errors.

Find Method Run-Time Errors

Find method run-time errors occur when the system cannot evaluate a find expression. Such errors are rare. When one occurs, the parser could have generated incorrect code.

Table 5-4 lists the Find method run-time errors.

OraObject Instance Errors

Table 5-5 lists the OraObject instance errors.

LOB Errors

Table 5-6 lists the LOB errors.

Oracle Streams Advanced Queuing Errors

Table 5-7 lists the Oracle Streams Advanced Queuing errors.

OraCollection Errors

Table 5-8 lists the OraCollection errors.

OraNumber Errors

Table 5-9 lists the OraNumber errors.

Oracle Errors

The most recent Oracle error text is available from the LastServerErr and LastServerErrText properties of the OraSession or OraDatabase objects.

• OraSession object

  The LastServerErr and LastServerErrText properties of the OraSession object return all errors related to connections, such as errors on the OpenDatabase method.

• OraDatabase object

  The LastServerErr and LastServerErrText properties of the OraDatabase object return all errors related to an Oracle cursor, such as errors on the CreateDynaset, CreateSQL, and ExecuteSQL methods.

Oracle Data Control Errors

Oracle Data Control errors are specific to the Oracle data control. During the visual access of the data control, the OO4O automation server-specific errors are reported as OLE automation server errors with the error code of ODCERR_AUTOMATION. Specific Oracle Data Control error codes are retrieved from the DataErr parameter of the Error() event.

Table 5-10 lists the Oracle Data Control errors.

OLE Initialization or OLE Automation Errors

The most frequent cause of OLE initialization and automation errors is missing or incorrectly installed software. Ensure correct installation of the software specified. Then make sure that you have specified method and property names correctly and that you have declared all Oracle objects as type object.

Table 5-11 lists the causes and solutions for OLE errors.

Oracle Network Errors

The most frequent cause of Oracle network errors is incorrectly specified connection information. The connection information for Oracle Objects for OLE is specified differently than when using Open Database Connectivity (ODBC). Please verify that you specified connection information correctly, and then make sure your network connection is working properly before using Oracle Objects for OLE. The appropriate Oracle network documentation contains information about testing your connection and about any Oracle networking error that you may receive.

Table 5-12 lists the Oracle network errors.

Access Violations

The most frequent cause of access violations is installing Oracle Objects for OLE while other applications are running that require the OO4O automation server, Oracle Required Support Files, or OLE. To avoid this, install Oracle Objects for OLE immediately after starting Windows and before running any other application.

Table 5-13 lists the access violations.