Modified: 20-APR-01
Ref #: ID-2397
Is it feasible to use OCI release 7.3.3 in non-thread safe mode within a multi-threaded program?
No. If you have multiple threads of execution, then you must always call opinit(OCI_EV_TSF).
Modified: 20-APR-01
Ref #: ID-2398
What level of locking should be maintained between the different threads after I have called opinit(OCI_EV_TSF)?
After you have called opinit(OCI_EV_TSF), if a connection is not shared between multiple threads, then you do not have to serialize calls going to that connection. If, however, more than one thread can make an OCI call on a connection, then you must serialize access to that connection.
Modified: 20-APR-01
Ref #: ID-2399
Can I link an OCI release 7.3.x multi-threaded program with Oracle8i OCI and obtain correct multi-threading behavior without making any changes?
You do not have to make any changes. Your OCI release 7.3.x multi-threaded program should run when linked with Oracle8i OCI libraries.
Modified: 20-APR-01
Ref #: ID-2400
What is a LOB locator?
A LOB (large object) is an Oracle datatype that can hold up to 4 GB of binary (BLOB) or character (CLOB) data. In the database, an opaque data structure called a LOB locator is stored in a LOB column of a database row, or in the place of a LOB attribute of an object. The locator serves as a pointer to the actual LOB value, which is stored in a separate location.
The OCI LOB locator is used to perform OCI operations against a LOB (BLOB or CLOB) or FILE (BFILE). OCI functions do not take actual LOB values as parameters; all OCI calls operate on the LOB locator. This descriptor--OCILobLocator--is also used for operations on FILEs.
The LOB locator is allocated with a call to OCIDescriptorAlloc(), by passing OCI_DTYPE_LOB as the type parameter for BLOBs or CLOBs, and OCI_DTYPE_FILE for BFILEs.
Warning:
The two LOB locator types are not interchangeable. When binding or
defining a BLOB or CLOB, the application must take care that the locator is properly allocated using OCI_DTYPE_LOB. Similarly, when binding or defining a BFILE, the application must be sure to allocate the locator using OCI_DTYPE_FILE.
|
An OCI application can retrieve a LOB locator from the server by issuing a SQL statement containing a LOB column or attribute as an element in the select list. In this example, the application first allocates the LOB locator and then uses it to define an output variable. Similarly, a LOB locator can be used as part of a bind operation to create an association between a LOB and a placeholder in a SQL statement. The LOB locator datatype (OCILobLocator) is not a valid datatype when connected to an Oracle7 database.
Modified: 23-APR-01
Ref #: ID-2401
What is the size of the Oracle8i LOB locator?
If the LOB contents are smaller than 4 K:
- Oracle8i stores the LOB in the row containing the LOB column. This is because for smaller LOBs there is no extra disk input/output (I/O).
If the LOB contents are larger than 4 K:
- the LOB column stores a LOB locator (20 bytes) and some additional information for efficient LOB access. If the storage option for the LOB specifies out-of-row storage (that is, the LOB is always stored in a block different from the containing row), then only the LOB locator is stored in the row, which is 20 bytes long.
Modified: 23-APR-01
Ref #: ID-2402
How many bytes are used when the LOB column value is NULL?
When a LOB column is NULL, only the NULL indication is stored, which is 1 byte.
Modified: 23-APR-01
Ref #: ID-2403
Are there any other things to note when using a binary FILE (BFILE) other than it is read-only?
Yes. In order to associate an operating system file to a BFILE, it is necessary to first create a DIRECTORY object, which is an alias for the full pathname to the operating system file. The READ privilege on the DIRECTORY object allows you to read files located under that directory. The creator of the DIRECTORY object automatically earns the READ privilege.
It is important to note that the READ privilege is defined only on the DIRECTORY object. The physical directory that it represents may or may not have the corresponding operating system privileges (READ in this case) for the Oracle Server process. It is the database administrator's (DBA's) responsibility to ensure that the physical directory exists, and read permission for the Oracle Server process is enabled on the file, the directory, and the path leading to it. It is also the DBA's responsibility to make sure that the directory remains available, and the read permission remains enabled, for the entire duration of file access by database users.
The READ privilege just implies that as far as the Oracle8i database is concerned, you may read from files in the directory. These privileges are checked and enforced by the PL/SQL DBMS_LOB package and OCI APIs at the time of the actual file operations.
Modified: 23-APR-01
Ref #: ID-2404
What happens when the operating system file changes? For example, if I delete the file and do not change anything on the BFILE field.
If you delete the operating system file, nothing happens to the BFILE field in the database. If the file is deleted while it is being used, the next operation on the file produces an error. If the deletion occurs after the file usage is complete, your next attempt at FILEOPEN fails.
Modified: 23-APR-01
Ref #: ID-2405
What happens if the DIRECTORY objects are dropped or replaced when the database is in operation?
DIRECTORY objects should not be arbitrarily dropped or replaced when the database is in operation. If this happens, DBMS_LOB or OCI operations from all sessions on all files associated with this directory object fails. Further, if a DROP or REPLACE command is executed before these files are successfully closed, the references to these files will be lost in the programs, and system resources associated with these files are not released until the session or sessions are shut down.
Modified: 23-APR-01
Ref #: ID-2406
Can I put several LOBs in the same block or do I have to put each LOB in its own block?
For small LOBs that are stored in the row, it is possible that multiple LOB values can share the same block.
For larger LOBs, the LOB values are stored outside the row. In this case, each LOB starts in a different chunk. Therefore for large LOBs, at most a chunk can be unused. It is not recommended to mix pieces of several LOBs into one chunk because this destroys the locality and contiguity for each LOB data, which degrades the I/O performance.
Note:
A chunk is different from a data block. The chunk size for LOBs can be
specified as part of the LOB storage specification.
|
Modified: 23-APR-01
Ref #: ID-2407
Is it possible to use OCI to create a new persistent object with a LOB attribute and write to that LOB attribute?
Yes. The application follows these steps:
- Call OCIObjectNew() to create a persistent object with a LOB attribute.
- Mark the object as dirty.
- Flush the object, thereby inserting a row into the table.
- Repin the latest version of the object (or refresh the object), thereby retrieving the object from the database and acquiring a valid locator for the LOB.
- Call OCILobWrite() using the LOB locator in the object to write the data.
Modified: 23-APR-01
Ref #: ID-2408
Is there a way to test if the LOB data associated with the OCILobLocator() is NULL before performing an OCILobRead() or OCILobGetLength()?
No. The only way to find out if the LOB data has a length of 0 is by calling OCILobGetLength(). If the LOB is NULL, then the LOB column stores NULL and there is no LOB locator nor is there a LOB. Therefore, a SQL select of a NULL LOB returns NULL instead of a LOB locator.
If the LOB exists, the LOB locator is stored in the LOB column and the LOB data length can range anywhere from 0 to 4 GB. A SQL SELECT of a LOB with a length of 0 returns a valid LOB locator that can be passed to OCILobGetLength().
In order to create a LOB with a length of 0, you can issue a SQL INSERT statement passing EMPTY_BLOB() or EMPTY_CLOB() for the internal LOB column.
Modified: 23-APR-01
Ref #: ID-2409
What are the recommendations regarding the parameter "Enable/Disable storage in row" when using small BLOBs? For example, if you are using 1K BLOBs and 5K BLOBs.
The default for LOBs is that the system automatically figures out whether or not a particular LOB should be stored inline in the row, or outside the row. If the LOB is greater than approximately 4000 bytes, it is automatically be stored outside the row.
Using 1K BLOBs: Allow the system to keep its default setting and store the LOBs inline in the row. If you explicitly say "Disable storage in row" when you create the LOB column, you cause all those LOBs to be stored in their own separate tablespace.
The Enable/Disable storage in row option is designed to allow you to enforce pure data separation. For example, if you decide that you want to keep all the LOB data on a separate device.
If you are mostly using large LOBs, you may decide to enforce this separation to avoid extra disk contention on the disk that stores the table data.
Using 5K BLOBs: As long as all LOBs are greater than 4000 bytes, the LOBs are stored outside the row in their own tablespace by default. The parameter Enable/Disable storage in row option has no effect.
If the size of these LOBs varies, and are sometimes less than 4000 bytes, then the system keeps the smaller ones inline. Generally, performance is better for LOBs 4000 bytes and smaller if you allow them to be inline because you will not have to go through an extra I/O for the LOB index dereference.
Modified: 23-APR-01
Ref #: ID-2410
How do you decide the optimal chunk size regarding the block size and the file system physical
I/O? For example, if you are using 1K BLOBs, 5K BLOBs, and 40K BLOBs.
The largest chunk allowed is 32k. The smallest chunk is one database block.
Using 1K BLOBs: It is recommended to choose a chunk size of 1 database block size (assuming it is larger than 1k). If you are allowing storage of LOBs inline, the chunk size is actually irrelevant.
Using 5K BLOBs: It is recommended to choose a chunk size of 2k, 4k, or 8k blocks. The chunk size you choose depends on whether you are concerned about wasting disk space. If your chunk size is 8k, that is the increment in which space is allocated. Of course, if your database block is 8k, then you cannot choose 2k or 4k as a chunk size.
Although it seems that chunk size plays a significant role in optimizing access to LOBs, in reality it does not have much influence. The difference in performance between 2k and 32k chunks is not very significant for large LOBs. For 5k LOBs, avoid 8k, 16k, and 32k chunks if possible. But, you may decide you need an 8k database block size for other reasons.
In general, each chunk of a LOB has a separate LOB index entry, so with larger chunks there are smaller index entries and fewer index entries to traverse. Also, there may be a benefit in the retrieval of multiple guaranteed contiguous blocks (all blocks in a chunk are contiguous).
For the 5k choice, the chunk size value is influenced by the database block size. If you have control over the database block size, examine the operating system block size, and then choose 2k or 4k for the chunk size depending on which seems to minimize I/Os.
Using 40K BLOBs: It is recommend to choose a chunk size of 8k.
Modified: 23-APR-01
Ref #: ID-2411
When reading in stream mode, does Oracle8i use synchronous or asynchronous reads? That is, does control return to the client after the first chunk read or only at the end of a chunk read?
You can read BLOBs in two modes:
- Piecewise fetch mode.
See Oracle8 Application Developer's Guide - Large Objects (LOBs) for more information.
- Polling or streaming mode
For polling or streaming reads, you can specify an offset and an amount. Streaming does not mean you read the complete BLOB at once, and there is no "chunk" mode. Whether or not Oracle8i uses synchronous or asynchronous reads depends on whether the user specifies CACHE or NOCACHE during the creation of the LOB column. If the user specifies NOCACHE, then reads and writes are performed asynchronously, or "directly" and the LOB blocks do not go through the buffer cache.
Modified: 23-APR-01
Ref #: ID-2412
What are some tips to optimize performance when working with LOBs in OCI?
- Use an array bind to perform INSERTs.
- Use an array fetch to retrieve all LOB locators.
- Open multiple connections to the same database, and parallelize the INSERTS, FETCHES, OCILobWrites(), and OCILobReads() on these connections.
- Turn logging off when loading or unloading 300 to 400 GB of LOB data.
- Use the maximum Oracle block size and chunk size (32 k) if the individual LOBs are large.
- Use a different tablespace for TIFF BLOB DATA, TIFF BLOB INDEX, SGML CLOB DATA, and SGML CLOB INDEX.
- Since LOBs are big, you can obtain the best performance by reading and writing large chunks of a LOB value at a time.
This helps in several respects:
- If accessing the LOB from the client side and the client is at a different node than the server, large reads/writes reduce network overhead.
- If using the 'NOCACHE' option, each small read/write incurs an I/O. Reading/writing large quantities of data reduces the I/O.
- Writing to the LOB creates a new version of the LOB CHUNK. Therefore, writing small amounts at a time incurs the cost of a new version for each small write. If logging is on, the CHUNK is also stored in the redo log.
- Use LOB buffering if you need to read/write small pieces of LOB data on the client.
- Use OCILobWrite() and OCILobRead() with a callback so data is streamed to or from the LOB. Ensure that the length of the entire write is set in the amount parameter on input. Whenever possible, read and write in multiples of the LOB chunk size.
- Use a checkout/checkin model for LOBs. LOBs are optimized for the following:
- SQL UPDATE, which replaces the entire LOB value.
- Copy the entire LOB data to the client, modify the LOB data on the client side, and copy the entire LOB data back to the database. This can be done using OCILobRead() and OCILobWrite() with streaming.
Modified: 23-APR-01
Ref #: ID-2415
At what time does an implicit transaction start?
The transaction context is initialized at the end of the OCITransStart() call. However, the transaction context is opaque to the client program and you cannot retrieve information about an implicit transaction. Implicit transaction starts when the client program makes its first database change after OCISessionBegin() or the previous commit/rollback.
Modified: 27-APR-01
Ref #: ID-2413
If I do not have access to an implicit transaction, how do I find out what features this transaction has?
The transaction has the usual (ACID) properties. Unlike global transactions (that are started explicitly), implicit transactions cannot be migrated between service contexts. Unless you are planning on building a multi-tier application, implicit transactions should be sufficient.
Modified: 27-APR-01
Ref #: ID-2414
Tell me about OCI features to manage transactions?
Oracle8i OCI provides a set of API calls to support operations on both local and global transactions. These calls include object support, so that if an OCI application is running in object mode, the commit and rollback calls synchronizes the object cache with the state of the transaction.
The functions listed below perform transaction operations. Each call takes a service context handle that should be initialized with the proper server context and user session handle. The transaction handle is the third element of the service context; it stores specific information related to a transaction. When a SQL statement is prepared, it is associated with a particular service context. When the statement is executed, its effects (query, fetch, insert) become part of the transaction that is currently associated with the service context.
- OCITransStart() - marks the start of a transaction
- OCITransDetach() - detaches a transaction
- OCITransCommit() - commits a transaction
- OCITransRollback() - rolls back a transaction
- OCITransPrepare() - prepares a transaction to be committed in a distributed processing environment
- OCITransForget() - causes the server to forget a heuristically completed global transaction.
Depending on the level of transactional complexity in your application, you may need all or only a few of these calls. The following section discusses this in more detail.
Levels of Transactional Complexity
OCI supports three levels of transaction complexity. Each level is described in one of the following sections.
- Simple Local Transactions
- Serializable or Read-Only Local Transactions
- Global Transactions
Simple Local Transactions
Many applications work with only simple local transactions. In these applications, an implicit transaction is created when the application makes database changes. The only transaction-specific calls needed by such applications are:
OCITransCommit() - to commit the transaction
OCITransRollback() - to roll back the transaction
As soon as one transaction has been committed or rolled back, the next modification to the database creates a new implicit transaction for the application.
Only one implicit transaction can be active at any time on a service context. Attributes of the implicit transaction are opaque to the user.
If an application creates multiple authorizations, each one can have an implicit transaction associated with it.
Serializable or Read-Only Local Transactions
Applications requiring serializable or read-only transactions require an additional OCI call beyond those needed by applications operating on simple local transactions. To initiate a serializable or read-only transaction, the application must create the transaction by calling OCITransStart() to start the transaction.
The call to OCITransStart() should specify OCI_TRANS_SERIALIZABLE or OCI_TRANS_READONLY, as appropriate, for the flags parameter. If no flag is specified, the default value is OCI_TRANS_READWRITE for a standard read-write transaction.
Specifying the read-only option in the OCITransStart() call saves the application from performing a server round-trip to execute a SET TRANSACTION READ ONLY statement.
Global Transactions
Global transactions are necessary only in more sophisticated transaction-processing applications. As they are not further discussed in this guide, see "OCI Programming Advanced Topics" of Oracle Call Interface Programmer's Guide, Volume I for more information.
Modified: 27-APR-01
Ref #: ID-2416
Do I need to allocate the transaction handle before starting an implicit transaction?
No. You do not need to allocate the transaction handle. But you will not be able to get any attributes of this transaction later in the program.
Modified: 27-APR-01
Ref #: ID-2417
What steps should be performed to start a serializable or read-only transaction?
To initiate a serializable or read-only transactions:
- Call OCISessionBegin().
- Call OCITransStart() to start the transaction. The call to OCITransStart() should specify OCI_TRANS_SERIALIZABLE or OCI_TRANS_READONLY, as appropriate, for the flags parameter. If no flag is specified, the default value is OCI_TRANS_READWRITE for a standard read-write transaction.
- Specifying the read-only option in the OCITransStart() call saves the application from performing a server round-trip to execute a SET TRANSACTION READ ONLY statement.
Modified: 27-APR-01
Ref #: ID-2418
How does TAF (Transparent Application Failover) fail over end user connections transparently?
The failover is handled by the OCI8 libraries. If they detect a failure, they will automatically use the information in the connect string to reestablish the session.
Modified: 04-FEB-02
Ref #: ID-2488
Does TAF SELECT failover work with any type of database configuration (replication, standby, cold failover), or is OPS required?
TAF SELECT failover is supported with any type of database, Real Application Clusters or OPS is not required. A cluster failover (Fail Safe, ServiceGuard, FirstWatch, etc) would work just as well--it's the same database, just running on a different node. Also, SELECT failover will work with a replicated or standby database. However, because these databases are not identical to the primary database (a standby may be a few logs behind and a replicated database can have other records), the likelihood that the first n rows returned by the restarted query not corresponding to the n rows returned before the failure increases. Oracle (OCI library) detects this by calculating a checksum on those first n rows and comparing it to the running checksum it was calculating before the failure. If they are the same, Oracle assumes the first n rows are identical and discards those rows because they have already been returned. If they are not identical, Oracle returns an error message.
Modified: 04-FEB-02
Ref #: ID-2491
Does Developer, Forms or Reports support TAF?
No. Forms does not support TAF. The Forms development group decided not to support it because most of their customers utilize a lot of PLSQL packages running in the DB, and the package state is not preserved with TAF. In the event of a failure, they would lose state and have to go back to the top level screen anyway, so they didn't see this as a big win.
Reports does not support TAF. In order to failover a reports session, there is important session state that must be restored. In theory, this could be accomplished via a callback function. The callback function is also used to notify the user a failback is in progress and explain there may be a delay while the query is reissued. However, Reports does not support registering such a callback function. They currently do not have plans to offer this support.
Modified: 04-FEB-02
Ref #: ID-2492
Does Transparent Application Failover (TAF) support SELECT failover of queries using bind variables?
Yes. The only caveat is you cannot free or change the bind handles after execute. They will be reused at failover time.
Modified: 04-FEB-02
Ref #: ID-2493
Tell me more about JDBC and TAF?
Any JDBC/OCI driver built using OCI8 or later should work with TAF's basic features. However, if you want to use the callback, you need to use the version of the driver that supports callback functions. Without the callback function, failover happens below the level of the JDBC driver (in the OCI layer). The JDBC client simply sees a delay during failover. After failover any package or session state will be lost. Any transactions in flight will be lost, and the client will be forced to issue a rollback to acknowledge the transaction is dead. If you want to support transactions, the application must cache the DML statements and replay them after failover/rollback.
In 9i, you can register callback functions. Unfortunately, there are bugs. These bugs have been fixed for 9iR2, but have not been backported yet to 9.0.1 (backport has not been requested). The callback support is all client code, so JDBC and TAF 9i drivers ought to work against an older database.
The callback can be used to replay session state, and other house cleaning you may require. It doesn't change the fact that any in flight tranactions are dead, and the client must still issue a rollback.
TAF with or without callbacks requires the JDBC/OCI driver. The thin driver, or the Merant drivers, do not use the client side OCI libraries, and that's where the failure detection and failover work is actually done--hence they will not work with TAF.
Modified: 04-FEB-02
Ref #: ID-2486
How do you register a callback function when using Pro* pre-compilers and TAF?
Below is a simple function to register the callback function...
void RegisterCallback()
{
OCISvcCtx *oschp;
OCIFocbkStruct failover; /* failover callback structure */
sword status;
OCIError *errhp;
OCIServer *srvhp;
OCIEnv *envhp;
status= SQLEnvGet((dvoid *)0, &envhp);
if (status != OCI_SUCCESS){
printf("Error occurred in getting OCI env handle: %ld\n", status);
exit(-1);
}
printf("Got env\n");
/* allocate an error handle */
status = OCIHandleAlloc((dvoid *)envhp, (dvoid **)&errhp,
(ub4) OCI_HTYPE_ERROR, (ub4 )0, (dvoid **) 0);
if(status != OCI_SUCCESS)
{
printf("Error occurred in getting OCI err handle: %ld\n", status);
exit(-1);
}
printf("Got err\n");
status = SQLSvcCtxGet((dvoid *)0, (text *)"", 0, &oschp);
if (status != OCI_SUCCESS){
printf("Error occurred in getting OCI scv ctx handle: %ld\n", status);
exit(-1);
}
printf("Got svcctx\n");
status = OCIAttrGet((dvoid *)oschp, (ub4)OCI_HTYPE_SVCCTX,
(dvoid *)&srvhp, (ub4*)0, OCI_ATTR_SERVER, errhp);
if (status != OCI_SUCCESS){
printf("Error occurred in getting OCI server handle: %ld\n", status);
exit(-1);
}
printf("Got server\n");
/* set up failover cbk handle */
failover.callback_function = &callback_fn;
failover.fo_ctx = (dvoid *)0;
status = OCIAttrSet((dvoid *)srvhp, (ub4) OCI_HTYPE_SERVER,
(dvoid *) &failover, (ub4) 0,
(ub4) OCI_ATTR_FOCBK, errhp);
if (status != OCI_SUCCESS){
printf("Error occurred in setting cbk: %ld\n", status);
exit(-1);
}
printf("callback registered. \n");
}
Modified: 04-FEB-02
Ref #: ID-2495
Do database links support TAF?
DB Links do not use the complete OCI8 code path--thus TAF will not work. If you have a DB Link activiated and pointing to a database that has failed, you will get an ORA-03113 EOF on comm. channel error (even if the database has been recovered). Once you get this error, the link will become invalid, and the next time you attempt to use the link it will be re-established using the connect string with which it was originally defined.
Modified: 25-SEP-03
Ref #: ID-2496
What versions of the OCI client libraries are supported with what versions of the database?
Client and server software is tested for compatibility when new versions are released. We test all new clients with old servers that, on the day the new client releases, are still within the Primary Error Correction phase of their support life. We also test new servers with old clients
that, on the day the new server releases, are in the Primary Error Correction
phase of their support life, or are in the first two years of the Extended
Maintenance phase of their support life. If we do not test the combination, it
is not supported.
When 10.1 was released, client and server versions 8.1.7 and 9.2 were within
their Primary Error Correction support life. Those combinations of client and
server were tested and are supported. Version 9.0.1 was tested, but is not
supported, since it does not have an Extended Maintenance phase.
The matrix below summarizes clients and server combinations that were tested.
| Server Version |
| Client Version |
8.1.7 |
9.0.1 |
9.2 |
10.1 |
| 8.1.7 |
Yes |
Was |
Yes |
Yes |
| 9.0.1 |
Was |
Was |
Was |
Was |
| 9.2 |
Yes |
Was |
Yes |
Yes |
| 10.1 |
Yes |
Was |
Yes |
Yes |
*=Supported for customers under Extended Maintenance (EMS) only.
Since new database servers are compatible with a limited set of older OCI clients, it may not be necessary to upgrade the client software when upgrading the database. However, some new features may not work without upgrading the client software.
Old features will work with the non-upgraded clients, but newer features have no
such guarantees.
Note this FAQ only includes information on currently supported and terminal releases. For information about
official compatibility of older releases, refer to MetaLink note 207303.1.
Also, to check the schedule for database desupport, see MetaLink note
161818.1.
&nbs
Bookmark