如何加快ORACLE本地OCI的调用速度

答案：

 此文摘自 developers.sun.com 写的很精采, 我自己试用了一下, 性能果然有所提高 developers.sun.com  
 
Technical Articles & Tips Cache OCI Calls to Improve Performance of 32-Bit or 64-Bit Oracle Clients 
By Nagendra Nagarajayya, October 2003 E-mail  Printable Page  Downloadend comment tag-->Contents:Introduction Identifying the Problem The Workaround: Cache oraus.msb in Memory Building cache_oraus.so How to Use cache_oraus.so How the Caching Works
  6.1  Interposition of the open() Function Call
  6.2  Interposition of the fcntl() Function Call
  6.3  Interposition of the lseek(), read(), and close() Function Calls Performance Improvement
  7.1  Without LD_PRELOAD and cache_oraus.so
  7.2  With LD_PRELOAD and cache_oraus.so
Caveat Conclusion Acknowledgments References
        A.  Appendix: Test Programs and Wrappers
               A1.  README
               A2.  cache_oraus.c
               A3.  test.c
               A4.  test.sh
               A5.  test1.sh
               A6.  test_v.c
               A7.  test_v.sh
               A8.  c.sh
               A9.  c64.sh
               A10.  Cache_open_calls.c
1. Introduction
If you work with Oracle clients that make use of OCI (Oracle Call Interface), you may have noticed that the OCI driver in 8.1.7.x makes thousands of calls to translate messages from the oraus.msb file. This can degrade application performance by 5 percent or more (quite severely in some cases). This problem has been documented by Oracle under bug ID 2142623. 

The problem can be overcome by caching the oraus.msb file in memory, and translating the file access and system calls to user calls and memory operations. The caching solution is dynamic, and code changes are not needed. The solution can improve the performance of the Oracle client application. Recently, this solution resulted in bringing down the application runtime from 15 minutes to a few seconds at a major customer – about 100x times performance improvement. 

The performance benefits can be seen in applications like sqlplus and Oracle clients (C and C++) making use of the OCI driver. Java technology-based applications using JDBC (native driver) should also see similar benefits. 
2. Identifying the Problem
An Oracle client application can be trussed on the Solaris Operating System (OS) to see the calls being made to this file -- "truss" is a system utility available on the Solaris platform, and it can be used to trace system calls made by an application. A running Oracle client application can be trussed as follows: 

truss -p [oracle client pid]

The truss command will show all the system calls being made by the application. The ones of interest are the open(), fcntl(), lseek(), read(), and close() calls. These calls are made by the OCI driver repeatedly to translate messages. Here is a truss snippet showing the problem calls: 

open("/oracle/app/oracle/product/8.1.7/rdbms/mesg/oraus.msb", O_RDONLY) = 9fcntl(9, F_SETFD, 0x00000001) = 0lseek(9, 0, SEEK_SET) = 0read(9, "1513 "011303\t\t\0\0\0\0".., 256) = 256lseek(9, 512, SEEK_SET) = 512read(9, "1C88 Y r ~ M\0\0\0\0\0\0".., 512) = 512lseek(9, 1024, SEEK_SET) = 1024read(9, "\018\0 $\0 7\0 @\0 J\0 V".., 512) = 512lseek(9, 39936, SEEK_SET) = 39936read(9, "\0\t0519\0\0\0 >051A\0\0".., 512) = 512close(9) = 0

These system calls can be expensive. The number of times these system calls are executed depends on the client application and the duration of the application run. 
3. The Workaround: Cache oraus.msb in Memory
The workaround is to cache the contents of the oraus.msb in memory and not make these system calls. This can be done dynamically by using the LD_PRELOAD technique available on the Solaris OE. (For further information, see the References section.) LD_PRELOAD is an environment variable on Solaris that allows shared libraries to be preloaded before an application begins execution. To make use of this technique, we need to build a shared library that will interpose on the open(), fcntl(), lseek(), read(), and close() calls. 
4. Building cache_oraus.so
The shared library can be built with the Forte C compiler (now part of the Sun ONE Compiler Collection) using the following switches: 

32-bit: 

cc -G -o cache_oraus.so -fast cache_oraus.c -l dl

64-bit: 

cc -G -o cache_oraus.so -fast -xarch=v9a cache_oraus.c -l dl
5. How to Use cache_oraus.so
The following environment variables need to be set to use the caching mechanism: 

export LD_PRELOAD=./cache_oraus.soexport oraus_msb_file=/ora/app/oracle/product/8.1.7/rdbms/mesg/oraus.msb

This can be set in a shell script used to start the client application. 
6. How the Caching Works
The caching works by interposing the open(), fcntl(), lseek(), read(), and close() calls. The first time the application executes one of these calls, the control is first transferred to the interposed function code. 
6.1 Interposition of the open() Function Call
Whenever a file is opened, the control is transferred to the interposed open() from cache_oraus.so. The interposed open() checks to see if the file being opened is the oraus.msb (see STEP 3 in the following code example). If so, the file is opened, and memory mapped (STEP 5.1). The descriptor returned by open() is also cached. For all other opens, the control is transferred to the original libc.so (STEP 7). 

int open(const char *path, int oflag, mode_t mode) { static int(*fptr)() = 0; static char* msb_path;STEP 1 if (fptr == 0) { fptr = (int (*)())dlsym(RTLD_NEXT, "open"); if (fptr == NULL) { fprintf(stderr, "dlopen: %s \n", dlerror()); return 0; }STEP 1.1 msb_path = (char*)getenv("oraus_msb_file"); }STEP 2 if (!msb_path) { msb_path = "/oracle/app/oracle/product/8.1.7/rdbms/mesg/oraus.msb"; }STEP 3 if (strcmp(path, msb_path) == 0) {STEP 4 if (k_bm_fd == -1) { k_bm_fd = ((*fptr)(path, oflag, mode)); if (k_bm_fd <= 0) { perror(path); exit(1); }STEP 5 fstat(k_bm_fd, &k_bm_stat_buf);STEP 5.1 k_bm_buf = (char*)mmap((caddr_t) 0, k_bm_stat_buf.st_size, (PROT_READ), MAP_SHARED, k_bm_fd, 0); assert(k_bm_buf != MAP_FAILED); return k_bm_fd; } else {STEP 6 return k_bm_fd; } }STEP 7 return ((*fptr)(path, oflag, mode));}
STEPSDescription1Use dlysym() to get a pointer to the original libc.so open() call, so that we can chain to it.1.1We use an environment variable, oraus_msb_file, to find the location of the oraus.msb file.2If this variable is not set, we use a default path.3We make sure the open call is related to the oraus_msb_file. For all other open calls, we chain to the original open() call.4We make sure this is the first time we are going through this code path as we want to map the file into memory only once. We open the file and cache the returned descriptor in k_bm_fd.5We get some information about the file itself, such

上一个：VB与ORACLE数据库中的日期转换
下一个：Oracle 和 MIcrosoft SQL 的不同