how to sort_area_size increase

Question

how set sort_area_size in oracle 10g and what size should be as i have more than 2.2m rows in single table. and please tell me the suggested size of SORT_AREA_RETAINED_SIZE

as my queries are too much slow they takes more than 1 hours to complete. (mostly) please suggest me the way by which i can optimize my queries and tune the database oracle 10g

thanks

---

updated with query

the query is

SELECT   A.TITLE,C.TOWN_VILL U_R,F.CODE TOWN_CODE,F.CITY_TOWN_MAKE,A.FRM,A.PRD_CODE,A.BR_CODE,A.SIZE_CODE ,B.PRICES, 
       A.PROJECT_YY,A.PROJECT_MM,d.province ,D.BR_CODE  BRANCH_CODE,D.STRATUM,L.LSM_GRP LSM, 
         SUM(GET_FRAC_FACTOR_ALL_PR_NEW(A.FRM,A.PRD_CODE,A.BR_CODE,A.SIZE_CODE,A.PROJECT_YY,A.PROJECT_MM,A.FRAC_CODE ,B.PRICES,A.QTY_USED,A.VERIF_CODE, A.PACKING_CODE, J.TYPE ,'R') )  
        * MAX(D.UNIVERSE) / MAX(E.SAMPLE)   /1000000   MARKET ,  D.UNIVERSE ,E.SAMPLE  
FROM A2_FOR_CPMARKETS  A, 
     BRAND  J, 
     PRICES B,CP_SAMPLE_ALL_MONTHS C ,
     CP_LSM L, 
     HOUSEHOLD_GL D, 
     SAMPLE_CP_ALL_MONTHS E , 
     City_Town_ALL F   
WHERE A.PRD_CODE = B.PRD_CODE 
AND A.BR_CODE = B.BR_CODE 
AND DECODE(A.SIZE_CODE,NULL,'L',A.SIZE_CODE) = B.SIZE_CODE -- for unbranded loose  
AND DECODE(B.VAR_CODE,'X','X',A.VAR_CODE) = B.VAR_CODE  
AND DECODE(B.COL_CODE,'X','X',A.COL_CODE) = B.COL_CODE  
AND DECODE(B.PACK_CODE,'X','X',A.PACKING_CODE) = B.PACK_CODE  
AND A.project_yy||A.project_MM BETWEEN B.START_DATE AND B.END_DATE  
AND A.PRD_CODE=J.PRD_CODE 
AND A.BR_CODE=J.BR_CODE 
AND A.FRM = C.FRM 
AND A.PROJECT_YY=L.YEAR 
AND A.frm=L.FORM_NO 
AND C.TOWN_VILL= D.U_R 
AND C.CLASS = D.CLASS 
AND D.TOWN=F.GRP 
AND D.TOWN = E.TOWN_CODE 
AND A.PROJECT_YY = E.PROJECT_YY 
AND A.PROJECT_MM = E.PROJECT_MM 
AND A.PROJECT_YY = C.PROJECT_YY 
AND A.PROJECT_MM = C.PROJECT_MM 
 -- FOR HOUSEJOLD_GL  
AND A.PROJECT_YY = D.YEAR 
AND A.PROJECT_MM = D.MONTH 


-- END HOUSEHOLD_GL  
AND C.TOWN_VILL = E.TOWN_VILL 
AND C.CLASS = E.CLASS 

AND C.TOWN_VILL = F.TOWN_VILL 
AND C.TOWN_CODE=F.CODE 

 AND (DECODE(e.PROJECT_YY,'1997','1','1998','1','1999','1','2000','1','2001','1','2002','1','2') = F.TYP ) 
GROUP BY A.TITLE,C.TOWN_VILL,F.CODE ,F.CITY_TOWN_MAKE,A.FRM,A.PRD_CODE,A.BR_CODE,A.SIZE_CODE ,B.PRICES, 
      A.PROJECT_YY,A.PROJECT_MM,d.province,D.BR_CODE ,D.STRATUM,L.LSM_GRP , 
      UNIVERSE ,E.SAMPLE

![alt text][1]

[1]: http://C:\Documents and Settings\Hussain\My Documents\My Pictures\explain plan.jpg

Answer 1

Check here for Oracle Documentation for SORT_AREA_SIZE. You can use alter session set sort_area_size=10000 command to modify this for the session and alter system for system. It is the same way for SORT_AREA_RETAINED_SIZE.

Is you entire table (with 2.2 m rows) fetched in the result set? Is there some sort operation in it?

There could be some other reasons for the query to perform badly. Can you share the query and explain plan?

Answer 2

When you run an execution plan for the query using the DBMS_Xplan.Display method oracle will estimate (usually pretty reasonably) what size of temporary tablespace storage you would need to execute it.

2.2 Million rows may be irrelevant to the sort size by the way. the memory required for aggregate operations such as MAX and SUM are more related to the size of the result set than to the size of the source data.

Providing a link to a jpg file stored on your pc does not count as having provided an execution plan, btw.

Answer 3

A.project_yy||A.project_MM BETWEEN B.START_DATE AND B.END_DATE

You know we have DATE datatypes in databases, right ? Using the incorrect datatypes makes it harder for Oracle to determine data distributions, predicate selectivity and appropriate query plans