Why is Oracle OCI a better API for a scalable, multi-threaded application than Open Database Connectivity (ODBC) ?

Why is Oracle OCI a better API for a scalable, multi-threaded application than Open Database Connectivity (ODBC) ?

The only advantage of ODBC is that it is practically vendor neutral. However, this vendor neutrality reduces the flexibility that a native interface such as OCI allows.

ODBC is a "least common denominator" interface and limits a scalable, high performance architecture.  

Oracle8i OCI is a much better choice than ODBC for the following reasons: 

OCI is optimized for queries. Transparent prefetch buffers reduce round-trips and improve performance and scalability. As a result, there is reduced memory usage on the server.

OCI is optimized for round-trips. No-reply requests are batched until the next call is generated for the server. This allows certain calls to be lazily propagated.

OCI is thread safe. You do not need to mutex (use mutual exclusivity locks) any of the OCI handles. ODBC is not thread safe, so you have to mutex most data structures.

OCI provides an asynchronous event notification API for active databases.

OCI provides enhanced array data manipulation language (DML) operations that reduce round-trips.

OCI returns ROWIDs for all rows selected for update in a single round-trip. Using ROWID allows for more efficient SQL access.

ODBC has no concept of sessions. OCI decouples connections, sessions and transactions. Multiple users can use a single connection; they get serialized on the connection for SQL operations. Multiple transactions can exist per user. This allows users to scale and service more users than there are connections available. Sessions and transactions can be migrated between connections to the same server.

ODBC does not support object types, large objects (LOBs), and other new Oracle datatypes.

ODBC affects server scalability. Using ODBC and having n number of concurrent users forces the server to have n number of processes service the clients if Oracle8i is operating in dedicated server mode. Your operating system may or may not support so many connections and processes.

ODBC is a wrapper around OCI so it is slower.

Note:  Some of these comments may or may not be applicable to your particular application. For example, if an application invocation is always dedicated for a user, then sessions, transactions, multiplexing, and multi-threading are not issues.    

Modified:  23-JUL-02   Ref #:  ID-2329