Performance Issues

Posted on 2007-08-08
Last Modified: 2013-12-19
I am the DB for an oracle database of around 200 users recently I have been getting people phoning me complaining of the database running slow. This seems to occur randomly but often on the afternoon.
I use an Oracle 9I database running on windows 2000 advanced server with 8 GB of memory
Shared Pool = 456mb
Buffer cache = 104mb
java pool = 32mb
SGA Max Size = 657mb
The PGA Settings are I believe handled automatically as I have the WORKAREA_SIZE_POLICY = AUTO
But it does have PGA Aggregate = 70mb
I have set 2 groups of 5 undo logs over 2 harddisks each 40mb in size.

what I would like to know is are the above settings to high or too low as I seem to get conflicting issues over this.

Any help would be great


Question by:DarrenJackson
    LVL 29

    Expert Comment


    First of all check if your tables are ANALYZED.

    Then you could instal the DBMS_STATSPACK package:

    Author Comment


    not sure what you mean by analyzed but I have stats pack in stalled and have taken a snapshot of todays database performance issue
    LVL 29

    Accepted Solution


    By "Analyzed" I mean you periodically use the DBMS_STATS package (or the ANALYZE statement) to gather optimizer statistics.

    For help in interpreting the statspack report go to:

    Author Comment


    Great website thanks will look into this


    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now