1. Review your oracle 10g db and fix the "HUGE I/O" issues. 2. Why not do the lookups using oracle? Allocate the extra 5 gig to the oracle buffer cache. 3. If you want good lookup performance, try to use the Array level interface so that you don't need to take multiple trips (context switch) to the DB. 4. Use a Multi threaded approach for throughput with oracle since you have 16 cpu's.
Andrew Finkenstadt <[EMAIL PROTECTED]> wrote: On 6/19/07, pompomJuice wrote: > > > Running a huge Oracle 10G database > Running a 3rd party application that generates HUGE IO. > Part of this 3rd party application is my application that does lookups. > > 1.) Data comes in in the form of files. > 2.) 3rd party application decodes and prepares these tickets or records > for > insert into Oracle > 3.) As these records flow by, we use key fields inside them to do lookups > into this lightweight SQL database as doing it into oracle would be even > slower. > When doing lookup tables in Oracle (9i or later stable versions), you are better off doing an index-organized table to reduce by 1 seek the amount of I/O Oracle does, and using a hash-key index-organized table to reduce the seeks to (usually) ONLY 1. Without knowing where in your box the Oracle instance is, it's difficult to say for sure, but assuming its on the same machine, I would try this out, based on my experience: PIN the lookup table (index) into the buffer cache, giving Oracle the extra 5GB of memory space to do so. Assuming you have a persistent connection to Oracle from your fly-by-update process and your fly-by-lookup process, you might find Oracle performs adequately. Either way I'd measure what's taking the time in doing lookups in Oracle that would be even slower; I assume you use bind parameters and cached SQL cursors (you get this automatically with most PL/SQL constructs [excepting dynamic sql type 4]), and basically have 1 parse per cursor prepare at application startup time.