DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Production Server Performance issue – Oracle 9.2.0.1 Windows

  1. #1
    Join Date
    Dec 2005
    Posts
    5

    Production Server Performance issue – Oracle 9.2.0.1 Windows

    I have a heavy report (Crystal Report – Oracle ODBC) that initially retrieve data through a db link. It took about 1hour in development environment, and in production environment it threw ‘Rollback segment too old’ and never ended.
    In order to improve the report, I made 2 materialized views to replace the db link.
    This change reduced time response by 50% or more, in development environment, everyone was happy, but in production it went on as is nothing had changed.
    I asked about memory configuration in both environments:

    Development: Windows 2000 -Sever 1 GB RAM. 2 Oracle 9.2.0.1 instances Dedicate Server
    SH Pool: 32 MB
    B. Cache: 72 MB
    L. Pool : 8 MB
    J. Pool :32 MB
    PGA: 24 MB
    Cache hit % : 60%

    Production: Windows 2000 - Sever 3.5 GB RAM. 2 Oracle 9.2.0.1 instances (about 700 MB to each…) about 70 users. Dedicated Server
    SH Pool: 580 MB
    B. Cache: 32 MB
    L. Pool : 8 MB
    J. Pool :32 MB
    PGA: 16 MB
    Cache hit % : 41%

    When I saw it, I was almost pretty sure, there was the problem, so I asked to change memory configuration in Production Server in this way:

    SH Pool: 220 MB
    B. Cache: 200 MB
    L. Pool : 8 MB
    J. Pool :32 MB
    PGA: 500 MB
    Cache hit % : 77%

    But there wasn’t any changes. In Development environment the report, (depending on settings), takes 8 minutes, the same report, same settings, in production has to be stopped after 40 minutes without response.
    Nobody complaint about performance in any other situation now or before, is just this only report (which is very important) with this problem.
    I asked for pagination problems, there weren’t.
    I asked to run analyze for tables and indexes…
    I asked about tablespaces storage they are ok, with enough space.

    Has anyone any idea or suggestion to try to improve performance in this case?

    Thanks in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    how about tracing the query, getting the explain plan, running a 10046 trace to see where the time is being taken instead of guessing and playing around with some memory parameters?

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    What is the refresh rate on the view? Are the objects refrenced by the query indexed alike? What was the datasize and load size differences between the DEV and PROD? As the previous suggestions, start with explain plan to see the query execution path? Also, check for the optimizer setup on PROD.

    Hope it would help you.

    Thanx
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Dec 2005
    Posts
    5
    Quote Originally Posted by sambavan
    What is the refresh rate on the view? Are the objects refrenced by the query indexed alike? What was the datasize and load size differences between the DEV and PROD? As the previous suggestions, start with explain plan to see the query execution path? Also, check for the optimizer setup on PROD.

    Hope it would help you.

    Thanx
    Sam
    Sambavan,
    Thanks a lot for your advice I'll follow it.
    The datasize and load size is the same in both instances, because dev is updated every day by an import, optimizer mode is CHOOSE in both env.
    I´ll go on with explain plan.

    Thank you very much.

  5. #5
    Join Date
    Dec 2005
    Posts
    5
    Thanks for your advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width