Re: Oracle slows down, why?

I haven't a clue. So I sat back and watched to see if anyone else would jump in and help you.

Well as that hasn't happened yet so here are my thoughts.

1. Run Explain Plan both ways and see if the execution plan changes.
2. If so, perhaps Hints will solve the problem.
3. If not run TKProf and look for differences.
4. Run DMBS_Profiler and look for differences.
5. Are statistics being created each night at the conclusion of the load? If not they should be for the optimizer. To do so use the DBMS_STATS package not any of the older methods of doing so.
6. Try flushing the shared pool between runs to see what affect that may have.

If the problem persists apply the patches required to upgrade to 8.1.7.3.x And let the group know what you find.

Daniel Morgan

Trevor Bourget wrote:

> Hello,
> 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,
> Trevor Bourget
Received on Tue Apr 02 2002 - 14:19:49 CST