Oracle slows down... why?
David Reid
Before I get into my question here's a little info
about my computer.
System:
- P3 - 1.0GHz
- 512 MB Memory (133MHz SDRAM)
Oracle:
- Version 8.1.7.0.0
- Automatic archiving is enabled
- Use 3 redo log files
- Each log file is 30 MB in size
- The redo log files and archive log files
- are stored on seperate disks
Programming IDE:
- Delphi 5
- Use DOA (Direct Oracle Access) to connect to oracle via a NET8 connection
The question:
In this scenario there are two programs, let's call
them program A and program B. Both programs have
been developed by my team using Delphi 5.
Program A is an updating program. The company I
work for is in insurance and we use an old dBase
system to keep track of customer information. The
system is called FAST if you're curious. I've
created a table space in the Oracle database that
duplicates FAST's table structure. Every 12 hours
the tables in the duplicate table space are truncated
and then updated with FAST's current information.
Program B is an accounting program. It creates a
very complex report by querying large amounts of data
from the Oracle database and then manipulates that
information. This program is strictly read-only.
Now here's the problem: If program B is ran after a
fresh boot the full process may take 4 or 5 seconds.
But if program A is ran first and then program B is
ran, the process will now take about 1 minute.
Why does program A slow-down the SQL queries
that program B executes?
Things I've tried:
1. Looked at the amount of memory free
- I always have at least 200MB free
2. Closing program A before running program B
- This did nothing
3. Stepped through program A pausing the program after every table was
updated.
- Noticed that program B became progressively slower after every table
was updated.
Thank-you,
David Reid