-
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.
-
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.
-
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
-
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)
