Re: Oracle slows down, why?

Thanks!

Fortunately I tried flushing the shared pool first and no last because that seems to work just fine. Running time for program B dropped back down to the normal running time.

Trevor Bourget

"damorgan" <damorgan_at_exesolutions.com> wrote in message news:3CAA1269.A8936607_at_exesolutions.com...
> 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 - 15:35:42 CST