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

Thread: Performance drops with added volumes

  1. #1
    Join Date
    Jan 2001
    Posts
    642

    Performance drops with added volumes

    Hi,

    I have a query which takes 0.05 seconds on the development and 2.06 seconds on the production.

    The tables the query accesses are
    AGGR_PL_FISCAL_PERIOD = 7.85Million (Dev)
    9.2 Million (Prod)

    LU_ACCOUNT = 3.95 Million (Dev
    = 3.94 Million (Prod)

    LOD5 =70,440 (dev and Prod)

    The explain plans are the same -
    the only difference I see in the trace file is the "query column"
    While it's is 20,300 in Dev, it is 236788 in Production.

    Does this cause the addition (6times additional increase in the service times).? We have rebuild indexes, analyzed tables etc.

    BTW, there is no DML/updates going on simultaneously.

    My colleague felt that its related to the data volumes. We have done many tests to prove that the hardware is not a problem.
    (Even on the same instance - 2 separate schema, dev takes 0.05 and prod takes 2.07 sec).

    Should the volumes change the response times drastically?
    There is always a better way to do the things.

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    One of the reasons would be the "Load on the PRO servers is / will be very much higher than your DEV Server" .. Aint it?

    Anyways can u post query and plans on PRO/DEV?

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: Performance drops with added volumes

    Originally posted by badrinathn
    The explain plans are the same -
    the only difference I see in the trace file is the "query column"
    While it's is 20,300 in Dev, it is 236788 in Production.

    IMHO, this is probably your problem. The most likely cause of this is prod is using a different index than it should be during a query. Inspect your query plans and make sure they are EXACTLY the same (Same order, same path, same indexes, etc.).
    Jeff Hunter

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by abhaysk
    One of the reasons would be the "Load on the PRO servers is / will be very much higher than your DEV Server" .. Aint it?
    Could be, but that wouldn't explain the increase in LIOs. Post tkprof and explain plan of query on both machines so we can look at CPU time vs. elapsed time.
    Jeff Hunter

  5. #5
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Whats the HWM on those tables? From you explain plan, can you tell us if there are any FTS? (which would increase the LIOs if the HWM is significantly higher than the number of rows in the table).

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by marist89
    but that wouldn't explain the increase in LIOs.
    For this very reason, i asked him to post query & its plan..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    I have a feeling that it is more of a server load issues rather than a database performance issues.

    try comparing the idle time and swap utilization between two servers.

  8. #8
    Join Date
    Jan 2001
    Posts
    642
    Thanks Marist,

    Yes my concern was mainly the #of LIO's. I have created an additional index and that has taken care.

    Thanks for all your inputs.

    Badrinath
    There is always a better way to do the things.

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