Performance Problem on test enviroment
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 30

Thread: Performance Problem on test enviroment

  1. #1
    Join Date
    May 2005
    Location
    Boston
    Posts
    3

    Unhappy Performance Problem on test enviroment

    Hi DBA's
    I am having a problem running a sql against my test environment, this sql takes about 10-15 minutes to execute. I did a reorg/runstats against the ts, didn't work. I ran the same sql against my production environment it took 12 sec. can any one tell me why on my test environment this sql is taking over 10 min. to execute.
    Any help will be Helpfull,
    This is the sql:
    SELECT A.EMPLID, A.LAST_NAME, A.FIRST_NAME, A.MIDDLE_NAME, A.DEPTID, A.EMPL_STATUS, B.PHONE_TYPE, B.PHONE
    FROM PS_EMPLOYEES A, PS_PERSONAL_PHONE B, PS_PERS_SRCH_QRY1 B1
    WHERE B.EMPLID = B1.EMPLID
    AND B1.ROWSECCLASS = 'DPCOBALL'
    AND ( A.EMPLID = B.EMPLID
    AND A.DEPTID = '149000'
    AND A.EMPL_STATUS = 'A' )
    Thanks
    Omar:cool:

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997

    Re: Performance Problem on test enviroment

    What is the explain plan on prod?
    What is the explain plan on test?
    Are they different?
    this space intentionally left blank

  3. #3
    Join Date
    May 2005
    Location
    Boston
    Posts
    3

    Unhappy Performance Problem on test enviroment

    the explain plan in prod--->don't have any plan & Packages attached
    the explain plan in test--->don't have any plan & Packages attached
    no difference in clusting, in either enviroments,
    could it be the way statistics used for access path to to optermizer?
    Omar:cool:

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if you dont have plan no one here can help you

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: Performance Problem on test enviroment

    Originally posted by Sinbad69
    could it be the way statistics used for access path to to optermizer?
    Almost undoubtedly so, although there could be data volume or instance configuration issues here as well ... but you need as a minimum to get the explain plans from the two different servers.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997

    Re: Re: Performance Problem on test enviroment

    Originally posted by slimdave
    Almost undoubtedly so, although there could be data volume or instance configuration issues here as well ... but you need as a minimum to get the explain plans from the two different servers.
    He could also try the dart board of database solutions.
    this space intentionally left blank

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    How bout the basics boys.
    Is the hardware the same?
    The amount of data?
    What is the current state of the Hyperdiscombobulator?
    I remember when this place was cool.

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    I would write the SQL as such.
    Code:
    SELECT emp.emplid,        emp.last_name,  emp.first_name,  
           emp.middle_name,   emp.deptid,     emp.empl_status, 
           phone.phone_type,  phone.phone
      FROM ps_employees emp
     INNER JOIN
           ps_personal_phone    phone
        ON emp.emplid         = phone.emplid
     INNER JOIN
         ( SELECT emplid
             FROM ps_pers_srch_qry1 
            WHERE rowsecclass = 'DPCOBALL' ) psrch
        ON phone.emplid       = psrch.emplid
     WHERE emp.deptid         = '149000' 
       AND emp.empl_status    = 'A';
    Could it be that you are missing an index in test? Also you may want to make sure that the test server is not swapping memory to disk.
    this space intentionally left blank

  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    Originally posted by Mr.Hanky
    How bout the basics boys.
    There is nothing more basic than an explain plan.
    Originally posted by Mr.Hanky
    Is the hardware the same?
    Why would the hardware need to be the same?
    Originally posted by Mr.Hanky
    The amount of data?
    Why do you have to have exactly the same amount of data in test as you do in prod?
    Originally posted by Mr.Hanky
    What is the current state of the Hyperdiscombobulator?
    Is that your specialty???
    this space intentionally left blank

  10. #10
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Hun, just check if all the indexes are present or same with
    the prod. Then analyze compute statistics the tables and
    indexes in ur test db.

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