oracle 如何增加 sort_area_size
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1874044/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to sort_area_size increase
提问by Haid
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
如何在 oracle 10g 中设置 sort_area_size 以及大小应该是多少，因为我在单个表中有超过 2.2m 的行。请告诉我 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
由于我的查询速度太慢，他们需要 1 个多小时才能完成。（主要是）请建议我优化查询和调整数据库 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]
[1]: http://C:\Documents and Settings\Hussain\My Documents\My Pictures\explain plan.jpg
[1]: http://C:\Documents and Settings\Hussain\My Documents\My Pictures\explain plan.jpg
采纳答案by Guru
Check here for Oracle Documentation for SORT_AREA_SIZE. You can use alter session set sort_area_size=10000command to modify this for the session and alter systemfor system. It is the same way for SORT_AREA_RETAINED_SIZE.
在此处查看SORT_AREA_SIZE 的Oracle 文档。您可以使用alter session set sort_area_size=10000命令为会话和alter system系统修改它。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?
您是否在结果集中提取了整个表（有 2.2 m 行）？里面有什么排序操作吗？
There could be some other reasons for the query to perform badly. Can you share the query and explain plan?
查询执行不佳可能还有其他一些原因。你能分享查询并解释计划吗？
回答by David Aldridge
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.
当您使用 DBMS_Xplan.Display 方法为查询运行执行计划时，oracle 将估计（通常相当合理）您需要执行它的临时表空间存储的大小。
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.
顺便说一下，220 万行可能与排序大小无关。MAX 和 SUM 等聚合操作所需的内存与结果集的大小有关，而不是与源数据的大小有关。
Providing a link to a jpg file stored on your pc does not count as having provided an execution plan, btw.
提供指向存储在您的 PC 上的 jpg 文件的链接不算作提供了执行计划，顺便说一句。
回答by Gary Myers
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
您知道我们在数据库中有 DATE 数据类型，对吗？使用不正确的数据类型使 Oracle 更难确定数据分布、谓词选择性和适当的查询计划
