On 6/19/07, pompomJuice <[EMAIL PROTECTED]> 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.
