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

Thread: Urgent Tuning required .....

  1. #1
    Join Date
    Jun 2000
    Posts
    179

    Talking

    Environment :
    Oracle Sever Ver. 8.0.4 on NT Server SP3 , 128 M RAM + 600M Swap File For NT

    Application :
    HR


    Symptoms:
    After running the incoming select statement it happins that the server FREEZ , even the screen save stops running.
    At some times it tells that it needs temporary space.

    ------------------------------------------------------
    SELECT PL_EXITPROCEDURES.EP_DONE, PL_EXITPROCEDURES.EP_DATE,
    PL_EXITPROCEDURES.EP_REMARKS, PL_EMPLOYEE.EM_CODE, PL_EMPLOYEE.EM_NAME, PL_LEAVEPROCEDURES.LP_NAME, PL_EMPLOYEE1.EM_NAME
    FROM SM.PL_EXITINTERVIEW PL_EXITINTERVIEW, SM.PL_EXITPROCEDURES PL_EXITPROCEDURES,
    SM.PL_EMPLOYEE PL_EMPLOYEE, SM.PL_LEAVEPROCEDURES PL_LEAVEPROCEDURES,
    SM.PL_EMPLOYEE PL_EMPLOYEE1 WHERE PL_EXITINTERVIEW.EI_REFNUM =
    PL_EXITPROCEDURES.EP_EXITINTERVIEWREF (+) AND PL_EXITINTERVIEW.EI_EMPLOYEECODE =
    PL_EMPLOYEE.EM_CODE AND PL_EXITINTERVIEW.EI_EM_COMPANYSYMBOL = PL_EMPLOYEE.EM_COMPANYSYMBOL
    AND PL_EXITPROCEDURES.EP_LEAVEPROCEDUREREF = PL_LEAVEPROCEDURES.LP_REFNUM AND
    PL_EXITPROCEDURES.EP_EM_CODE = PL_EMPLOYEE1.EM_CODE (+) AND
    PL_EXITPROCEDURES.EP_EM_COMPANYSYMBOL = PL_EMPLOYEE1.EM_COMPANYSYMBOL (+) AND
    PL_Employee.EM_CompanySymbol = 'N'
    AND
    TO_NUMBER(PL_Employee.EM_Code)
    IN ( SELECT PC_PERSONCODE FROM SM_Person_Char WHERE PC_PERSONCHARREF = 13 )
    ORDER BY PL_EMPLOYEE.EM_CODE ASC;
    ------------------------------------------------------
    The main table that the previous statment run aginst hols only 2700 rows of data ( Employees main records).

    What I made :
    - Increased size of Temp datafiles in quadruble
    - made new set of rollback segments with the double of initial and optimal values.
    - ensured that all datfiles are up and running - no offline ones.
    - doubled SGA and Sort Area sizes

    And still happins.

    Do we need to increase physical memory of the server for Instance ???

    I need an indepth consult to solve this problem.
    Hisham Nagia
    IT Manager For Development
    Oracle Consultant - OCP

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Probably the INITIAL and NEXT extent sizes defined in TEMP tablespace may be small. Change those values into big size and rerun the query again.

    Also check that SGA size is less than 128 MB.

  3. #3
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Red face maybe ...


    Just a thought:

    Uh, have you checked to make sure your TEMP tablespace is designated as "temporary"?

    Maybe even clean up the select statement. Seems pretty discombobulated. Make sure it's using indexes etc.

    Also, have you tried this statement from the command line/within the server?

    - Magnus

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    This is not helping:
    TO_NUMBER(PL_Employee.EM_Code) IN ( SELECT PC_PERSONCODE FROM SM_Person_Char WHERE PC_PERSONCHARREF = 13 )

    I see to things wrong with this:
    1. (assuming your not using a function based index) You will never be able to use and index on pl_employee if you are doing a type conversion on it.

    2. Why the IN? This is a perfect case for a join. (assuming the two fields are the same data type)
    Jeff Hunter

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