近段时间，发现生产库的alert日志出现了如下几次600错误：
Errors in file /oracle/product/admin/abcd/udump/abcd2_ora_15891.trc:
ORA-00600: internal error code, arguments: [32695], [hash aggregation can't be done], [], [], [], [], [], []
cxdb：
Tue Apr 15 15:00:47 2013
Errors in file /home/oracle/admin/cxdb/udump/cxdb1_ora_4432.trc:
ORA-00600: 内部错误代码, 参数: [32695], [hash aggregation can't be done], [], [], [], [], [], []
Tue Apr 15 15:00:56 2013
Trace dumping is performing id=[cdmp_20120515150056]
Tue Apr 15 15:34:10 2013
Thread 1 advanced to log sequence 27240 (LGWR switch)
Tue Apr 15 17:35:51 2013
Errors in file /home/oracle/admin/cxdb/udump/cxdb1_ora_1205.trc:
ORA-00600: 内部错误代码, 参数: [32695], [hash aggregation can't be done], [], [], [], [], [], []
Tue Apr 15 17:36:01 2013
Trace dumping is performing id=[cdmp_20120515173601]
跟踪trace文件，发现里面有如下一段代码：
ORA-00600: internal error code, arguments: [32695], [hash aggregation can't be done], [], [], [], [], [], []
cxdb：
Tue Apr 15 15:00:47 2013
Errors in file /home/oracle/admin/cxdb/udump/cxdb1_ora_4432.trc:
ORA-00600: 内部错误代码, 参数: [32695], [hash aggregation can't be done], [], [], [], [], [], []
Tue Apr 15 15:00:56 2013
Trace dumping is performing id=[cdmp_20120515150056]
Tue Apr 15 15:34:10 2013
Thread 1 advanced to log sequence 27240 (LGWR switch)
Tue Apr 15 17:35:51 2013
Errors in file /home/oracle/admin/cxdb/udump/cxdb1_ora_1205.trc:
ORA-00600: 内部错误代码, 参数: [32695], [hash aggregation can't be done], [], [], [], [], [], []
Tue Apr 15 17:36:01 2013
Trace dumping is performing id=[cdmp_20120515173601]
跟踪trace文件，发现里面有如下一段代码：
SELECT COUNT(1)
FROM (SELECT DISTINCT T.TICKET_NUM,
SENDER_PROV || SENDER_CITY || SENDER_COUNTY "senderDistrict",
RCVER_PROV || RCVER_CITY || RCVER_COUNTY "receiverDistrict",
T.ESTI_ARRIVE_TIME "arriveTime",
T.BACK_CNT "backCount",
T.IS_BILLING "billing",
T.AMOUNT "billingAmount",
T.UNIT "billingUnit",
T.BILLING_WEIGHT "billingWeight",
T.BUSI_CATEGORY "businessCategory",
T.BUSI_RANGE "businessRange",
T.CDN_NUM "cdnNmu",
T.CHECK_TYPE "checkType",
T.CO_DLV_FLAG "coDeliveryFlag",
T.COLLECT_ORG "collectOrg",
COLTORG.COLLECT_NAME "collectOrgName",
T.CLCT_TIME "collectTime",
T.CRT_DT "createDate",
T.CRT_ORG "createOrgId",
CRTORG.ORG_BRIEF "createOrgIdName",
CRTORG.TEL "createOrgTel",
T.CRT_PSN "createPerson",
T.CUST_ID "custId",
CMS_CUSTOMER.CUST_CD "custCode",
CMS_CUSTOMER.CUST_NAME "custIdName",
T.CUST_ORDER_TYPE "custOrderType",
T.CUST_TICKET_ID "custTicketId",
T.DAMAGED "damaged",
T.DECLARE_VAL "declareValue",
T.DLV_MODE "deliverMode",
T.DLV_CNT "deliveryCount",
T.DLV_ORG "deliveryOrgId",
T.DLV_FEE "dlvFee",
T.IS_EBILL_RTN "electroRtn",
T.IS_EMS "ems",
T.END_TIME "endTime",
T.EXT_COL1 "extCol1",
T.EXT_COL10 "extCol10",
T.EXT_COL2 "extCol2",
T.EXT_COL3 "extCol3",
T.EXT_COL4 "extCol4",
T.EXT_COL5 "extCol5",
T.EXT_COL6 "extCol6",
T.EXT_COL7 "extCol7",
T.EXT_COL8 "extCol8",
T.EXT_COL9 "extCol9",
T.FACT_ORDER_CNT "factOrderCount",
T.IS_FACT_RTN "factRtn",
T.FACT_WEIGHT "factWeight",
T.FIRST_DLV_TIME "firstDeliveryTime",
T.FIRST_SEND_TIME "firstTime",
T.FLOW_STATUS "flowStatus",
T.IS_FOAM "foam",
T.GOODS_NAME "goodsName",
T.GOODS_TYPE "goodsType",
T.HOLD_ORG_ID "holdOrg",
T.HOLD_TYPE "holdType",
T.TICKET_NUM "id",
T.INSURANCE_FEE "insuranceFee",
T.MERGE_FLAG "merge",
T.MERGE_TICKET_COUNT "mergeTicketCount",
T.MERGE_TICKET_NUM "mergeTicketId",
T.MODEL_CD "modelCd",
T.MODEL_TYPE "modelType",
T.ORDER_TYPE "orderType",
T.OTHRE_FEE "othreFee",
T.PACKAGE_KIND "packageKind",
T.PIECES "pieces",
T.PREV_FLOW_STATUS "prevFlowStatus",
T.PRD_TYPE "productType",
OMS_BUSI_TYPE.BUSI_TYPE_NAME "productTypeName",
T.PRJCT_CD "projectCd",
T.PRJCT_ID "projectId",
PRJ_PROJECT.PRJCT_NAME "projectIdName",
PRJ_PROJECT.BYPRINT_TYPE "printType",
PRJ_PROJECT.PRJ_BRIEF "prjBrief",
T.ALL_REBATE_FEE "rebateFee",
T.RECV_ADDR "receiverAddr",
T.RCVER_CITY "receiverCity",
T.RCVER_CITY_CD "receiverCityCode",
SE_DIST5.SHORT_DIST_NAME "receiverCityName",
T.RCVER_COMPANY "receiverCompany",
T.RCVER_CNTCT "receiverContact",
T.RCVER_CNTCT_TEL "receiverContactTel",
T.RCVER_COUNTY "receiverCounty",
T.RCVER_COUNTY_CD "receiverCountyCode",
SE_DIST6.SHORT_DIST_NAME "receiverCountyName",
T.RCVER_ID "receiverId",
T.RCVER_POST "receiverPost",
T.RCVER_PROV "receiverProv",
T.RCVER_PROV_CD "receiverProvCode",
SE_DIST4.SHORT_DIST_NAME "receiverProvName",
T.RCVER_STREET "receiverStreet",
T.RCVER_TYPE "receiverType",
T.REF_TICKET_NUM "refrenceId",
T.REMARK "remark",
T.RPAY_GOODS_FEE "rpayGoodsFee",
T.IS_RPAY_TRAFFIC "rpayTraffic",
T.RPAY_TRAFFIC_FEE "rpayTrafficFee",
T.SALESMAN "salesMan",
T.SELF_FLAG "selfFlag",
T.SELF_ORG_ADDR "selfOrgAddr",
T.SELF_ORG_ID "selfOrgId",
SELFORG.ORG_BRIEF "selfOrgIdName",
T.SELF_ORG_POST "selfOrgPost",
T.SENDER_ADDR "senderAddr",
T.SENDER_CITY "senderCity",
T.SENDER_CITY_CD "senderCityCode",
SE_DIST2.SHORT_DIST_NAME "senderCityName",
T.SENDER_COMPANY "senderCompany",
T.SENDER_CNTCT "senderContact",
T.SENDER_CNTCT_TEL "senderContactTel",
T.SENDER_COUNTY "senderCounty",
T.SENDER_COUNTY_CD "senderCountyCode",
SE_DIST3.SHORT_DIST_NAME "senderCountyName",
T.SENDER_ID "senderId",
T.SENDER_POST "senderPost",
T.SENDER_PROV "senderProv",
T.SENDER_PROV_CD "senderProvCode",
SE_DIST1.SHORT_DIST_NAME "senderProvName",
T.SENDER_STREET "senderStreet",
T.SENDER_TYPE "senderType",
T.START_TIME "startTime",
T.SUCC_DLV_TIME "successDeliveryTime",
T.TASK_LEVEL "taskLevel",
T.ALL_TOTAL_FEE "totalFee",
T.TOTAL_GOODS_QTY "totalGoodsQty",
T.TOTAL_VOLUME "totalVolume",
T.TRAFFIC_FEE "trafficFee",
T.TRAFFIC_LIMIT "trafficLimit",
T.TRAFFIC_MODE "trafficMode",
T.TRAFFIC_WAY "trafficWay",
T.UPD_DT "updateDate",
T.UPD_ORG "updateOrgId",
UPDORG.ORG_NAME "updateOrgIdName",
T.UPD_PSN "updatePerson",
T.VOUCHER_CD "voucherNumber",
T.PRD_WEIGHT_TYPE "weightType"
FROM OMS_XQDXINB T
LEFT OUTER JOIN CMS_CLCT_INFO COLTORG ON T.COLLECT_ORG =
COLTORG.ORG_ID
LEFT OUTER JOIN RES_ORG CRTORG ON T.CRT_ORG =
CRTORG.ORG_ID
LEFT OUTER JOIN CMS_CUSTOMER CMS_CUSTOMER ON T.CUST_ID =
CMS_CUSTOMER.CUST_ID
LEFT OUTER JOIN OMS_BUSI_TYPE OMS_BUSI_TYPE ON T.PRD_TYPE =
OMS_BUSI_TYPE.BUSI_TYPE_CD
LEFT OUTER JOIN PRJ_PROJECT PRJ_PROJECT ON T.PRJCT_ID =
PRJ_PROJECT.PRJCT_ID
LEFT OUTER JOIN SE_DIST SE_DIST5 ON T.RCVER_CITY_CD =
SE_DIST5.DIST_ID
LEFT OUTER JOIN SE_DIST SE_DIST6 ON T.RCVER_COUNTY_CD =
SE_DIST6.DIST_ID
LEFT OUTER JOIN SE_DIST SE_DIST4 ON T.RCVER_PROV_CD =
SE_DIST4.DIST_ID
LEFT OUTER JOIN RES_ORG SELFORG ON T.SELF_ORG_ID =
SELFORG.ORG_ID
LEFT OUTER JOIN SE_DIST SE_DIST2 ON T.SENDER_CITY_CD =
SE_DIST2.DIST_ID
LEFT OUTER JOIN SE_DIST SE_DIST3 ON T.SENDER_COUNTY_CD =
SE_DIST3.DIST_ID
LEFT OUTER JOIN SE_DIST SE_DIST1 ON T.SENDER_PROV_CD =
SE_DIST1.DIST_ID
LEFT OUTER JOIN RES_ORG UPDORG ON T.UPD_ORG =
UPDORG.ORG_ID)
这是一张很大的分区表OMS_XQDXINB和多张码表关联，对sql优化比较多的，其实不用查看执行计划，就应该看看这个这么长的sql的写法是不是合理的，我大致扫描了这个sql，发现这里面除了关联多外，还用了个distinct，好在我对这个系统的业务以及数据模型比较清楚，就在想这里面distinct有没有必要，不过这个需要验证，另外这个sql没有where条件，我其实知道OMS_XQDXINB这个表是一张非常大的分区表，记录数大概4000万左右，里面记录的是运单信息，里面保存了08年到现在的数据，业务特征是绝大多数用户i查询近期7天最多半个月的数据，因为非常少的人还去查几个月前的运单情况，因此这个地方没有where条件是不是合理？这些先暂且放在一边不说，先来分析分析执行计划看看：
FROM (SELECT DISTINCT T.TICKET_NUM,
SENDER_PROV || SENDER_CITY || SENDER_COUNTY "senderDistrict",
RCVER_PROV || RCVER_CITY || RCVER_COUNTY "receiverDistrict",
T.ESTI_ARRIVE_TIME "arriveTime",
T.BACK_CNT "backCount",
T.IS_BILLING "billing",
T.AMOUNT "billingAmount",
T.UNIT "billingUnit",
T.BILLING_WEIGHT "billingWeight",
T.BUSI_CATEGORY "businessCategory",
T.BUSI_RANGE "businessRange",
T.CDN_NUM "cdnNmu",
T.CHECK_TYPE "checkType",
T.CO_DLV_FLAG "coDeliveryFlag",
T.COLLECT_ORG "collectOrg",
COLTORG.COLLECT_NAME "collectOrgName",
T.CLCT_TIME "collectTime",
T.CRT_DT "createDate",
T.CRT_ORG "createOrgId",
CRTORG.ORG_BRIEF "createOrgIdName",
CRTORG.TEL "createOrgTel",
T.CRT_PSN "createPerson",
T.CUST_ID "custId",
CMS_CUSTOMER.CUST_CD "custCode",
CMS_CUSTOMER.CUST_NAME "custIdName",
T.CUST_ORDER_TYPE "custOrderType",
T.CUST_TICKET_ID "custTicketId",
T.DAMAGED "damaged",
T.DECLARE_VAL "declareValue",
T.DLV_MODE "deliverMode",
T.DLV_CNT "deliveryCount",
T.DLV_ORG "deliveryOrgId",
T.DLV_FEE "dlvFee",
T.IS_EBILL_RTN "electroRtn",
T.IS_EMS "ems",
T.END_TIME "endTime",
T.EXT_COL1 "extCol1",
T.EXT_COL10 "extCol10",
T.EXT_COL2 "extCol2",
T.EXT_COL3 "extCol3",
T.EXT_COL4 "extCol4",
T.EXT_COL5 "extCol5",
T.EXT_COL6 "extCol6",
T.EXT_COL7 "extCol7",
T.EXT_COL8 "extCol8",
T.EXT_COL9 "extCol9",
T.FACT_ORDER_CNT "factOrderCount",
T.IS_FACT_RTN "factRtn",
T.FACT_WEIGHT "factWeight",
T.FIRST_DLV_TIME "firstDeliveryTime",
T.FIRST_SEND_TIME "firstTime",
T.FLOW_STATUS "flowStatus",
T.IS_FOAM "foam",
T.GOODS_NAME "goodsName",
T.GOODS_TYPE "goodsType",
T.HOLD_ORG_ID "holdOrg",
T.HOLD_TYPE "holdType",
T.TICKET_NUM "id",
T.INSURANCE_FEE "insuranceFee",
T.MERGE_FLAG "merge",
T.MERGE_TICKET_COUNT "mergeTicketCount",
T.MERGE_TICKET_NUM "mergeTicketId",
T.MODEL_CD "modelCd",
T.MODEL_TYPE "modelType",
T.ORDER_TYPE "orderType",
T.OTHRE_FEE "othreFee",
T.PACKAGE_KIND "packageKind",
T.PIECES "pieces",
T.PREV_FLOW_STATUS "prevFlowStatus",
T.PRD_TYPE "productType",
OMS_BUSI_TYPE.BUSI_TYPE_NAME "productTypeName",
T.PRJCT_CD "projectCd",
T.PRJCT_ID "projectId",
PRJ_PROJECT.PRJCT_NAME "projectIdName",
PRJ_PROJECT.BYPRINT_TYPE "printType",
PRJ_PROJECT.PRJ_BRIEF "prjBrief",
T.ALL_REBATE_FEE "rebateFee",
T.RECV_ADDR "receiverAddr",
T.RCVER_CITY "receiverCity",
T.RCVER_CITY_CD "receiverCityCode",
SE_DIST5.SHORT_DIST_NAME "receiverCityName",
T.RCVER_COMPANY "receiverCompany",
T.RCVER_CNTCT "receiverContact",
T.RCVER_CNTCT_TEL "receiverContactTel",
T.RCVER_COUNTY "receiverCounty",
T.RCVER_COUNTY_CD "receiverCountyCode",
SE_DIST6.SHORT_DIST_NAME "receiverCountyName",
T.RCVER_ID "receiverId",
T.RCVER_POST "receiverPost",
T.RCVER_PROV "receiverProv",
T.RCVER_PROV_CD "receiverProvCode",
SE_DIST4.SHORT_DIST_NAME "receiverProvName",
T.RCVER_STREET "receiverStreet",
T.RCVER_TYPE "receiverType",
T.REF_TICKET_NUM "refrenceId",
T.REMARK "remark",
T.RPAY_GOODS_FEE "rpayGoodsFee",
T.IS_RPAY_TRAFFIC "rpayTraffic",
T.RPAY_TRAFFIC_FEE "rpayTrafficFee",
T.SALESMAN "salesMan",
T.SELF_FLAG "selfFlag",
T.SELF_ORG_ADDR "selfOrgAddr",
T.SELF_ORG_ID "selfOrgId",
SELFORG.ORG_BRIEF "selfOrgIdName",
T.SELF_ORG_POST "selfOrgPost",
T.SENDER_ADDR "senderAddr",
T.SENDER_CITY "senderCity",
T.SENDER_CITY_CD "senderCityCode",
SE_DIST2.SHORT_DIST_NAME "senderCityName",
T.SENDER_COMPANY "senderCompany",
T.SENDER_CNTCT "senderContact",
T.SENDER_CNTCT_TEL "senderContactTel",
T.SENDER_COUNTY "senderCounty",
T.SENDER_COUNTY_CD "senderCountyCode",
SE_DIST3.SHORT_DIST_NAME "senderCountyName",
T.SENDER_ID "senderId",
T.SENDER_POST "senderPost",
T.SENDER_PROV "senderProv",
T.SENDER_PROV_CD "senderProvCode",
SE_DIST1.SHORT_DIST_NAME "senderProvName",
T.SENDER_STREET "senderStreet",
T.SENDER_TYPE "senderType",
T.START_TIME "startTime",
T.SUCC_DLV_TIME "successDeliveryTime",
T.TASK_LEVEL "taskLevel",
T.ALL_TOTAL_FEE "totalFee",
T.TOTAL_GOODS_QTY "totalGoodsQty",
T.TOTAL_VOLUME "totalVolume",
T.TRAFFIC_FEE "trafficFee",
T.TRAFFIC_LIMIT "trafficLimit",
T.TRAFFIC_MODE "trafficMode",
T.TRAFFIC_WAY "trafficWay",
T.UPD_DT "updateDate",
T.UPD_ORG "updateOrgId",
UPDORG.ORG_NAME "updateOrgIdName",
T.UPD_PSN "updatePerson",
T.VOUCHER_CD "voucherNumber",
T.PRD_WEIGHT_TYPE "weightType"
FROM OMS_XQDXINB T
LEFT OUTER JOIN CMS_CLCT_INFO COLTORG ON T.COLLECT_ORG =
COLTORG.ORG_ID
LEFT OUTER JOIN RES_ORG CRTORG ON T.CRT_ORG =
CRTORG.ORG_ID
LEFT OUTER JOIN CMS_CUSTOMER CMS_CUSTOMER ON T.CUST_ID =
CMS_CUSTOMER.CUST_ID
LEFT OUTER JOIN OMS_BUSI_TYPE OMS_BUSI_TYPE ON T.PRD_TYPE =
OMS_BUSI_TYPE.BUSI_TYPE_CD
LEFT OUTER JOIN PRJ_PROJECT PRJ_PROJECT ON T.PRJCT_ID =
PRJ_PROJECT.PRJCT_ID
LEFT OUTER JOIN SE_DIST SE_DIST5 ON T.RCVER_CITY_CD =
SE_DIST5.DIST_ID
LEFT OUTER JOIN SE_DIST SE_DIST6 ON T.RCVER_COUNTY_CD =
SE_DIST6.DIST_ID
LEFT OUTER JOIN SE_DIST SE_DIST4 ON T.RCVER_PROV_CD =
SE_DIST4.DIST_ID
LEFT OUTER JOIN RES_ORG SELFORG ON T.SELF_ORG_ID =
SELFORG.ORG_ID
LEFT OUTER JOIN SE_DIST SE_DIST2 ON T.SENDER_CITY_CD =
SE_DIST2.DIST_ID
LEFT OUTER JOIN SE_DIST SE_DIST3 ON T.SENDER_COUNTY_CD =
SE_DIST3.DIST_ID
LEFT OUTER JOIN SE_DIST SE_DIST1 ON T.SENDER_PROV_CD =
SE_DIST1.DIST_ID
LEFT OUTER JOIN RES_ORG UPDORG ON T.UPD_ORG =
UPDORG.ORG_ID)
这是一张很大的分区表OMS_XQDXINB和多张码表关联，对sql优化比较多的，其实不用查看执行计划，就应该看看这个这么长的sql的写法是不是合理的，我大致扫描了这个sql，发现这里面除了关联多外，还用了个distinct，好在我对这个系统的业务以及数据模型比较清楚，就在想这里面distinct有没有必要，不过这个需要验证，另外这个sql没有where条件，我其实知道OMS_XQDXINB这个表是一张非常大的分区表，记录数大概4000万左右，里面记录的是运单信息，里面保存了08年到现在的数据，业务特征是绝大多数用户i查询近期7天最多半个月的数据，因为非常少的人还去查几个月前的运单情况，因此这个地方没有where条件是不是合理？这些先暂且放在一边不说，先来分析分析执行计划看看：
SQL>SELECT * FROM TABLE(dbms_xplan.display_cursor('83fc00h1j6px0',0));
SQL_ID 83fc00h1j6px0, child number 0
-------------------------------------
SELECT /*ccccc*/COUNT(1) FROM (SELECT DISTINCT TRAFFICORDER.TICKET_NUM, SENDER_PROV ||
SENDER_CITY || SENDER_COUNTY "senderDistrict", RCVER_PROV || RCVER_CITY || RCVER_COUNTY
"receiverDistrict", TRAFFICORDER.ESTI_ARRIVE_TIME "arriveTime",
TRAFFICORDER.BACK_CNT "backCount", TRAFFICORDER.IS_BILLING "billing",
TRAFFICORDER.AMOUNT "billingAmount", TRAFFICORDER.UNIT "billingUnit",
TRAFFICORDER.BILLING_WEIGHT "billingWeight", TRAFFICORDER.BUSI_CATEGORY "businessCategory",
TRAFFICORDER.BUSI_RANGE "businessRange", TRAFFICORDER.CDN_NUM "cdnNmu",
TRAFFICORDER.CHECK_TYPE "checkType", TRAFFICORDER.CO_DLV_FLAG
"coDeliveryFlag", TRAFFICORDER.COLLECT_ORG "collectOrg",
Plan hash value: 3462057465
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 6933K(100)| | | |
| 1 | SORT AGGREGATE | | 1 | | | | | | |
| 2 | VIEW | | 30M| | | 6933K (1)| 23:06:38 | | |
| 3 | HASH UNIQUE | | 30M| 21G| 47G| 6933K (1)| 23:06:38 | | |
|* 4 | HASH JOIN RIGHT OUTER | | 30M| 21G| | 2152K (1)| 07:10:34 | | |
| 5 | TABLE ACCESS FULL | CMS_CLCT_INFO | 3824 | 123K| | 16 (0)| 00:00:01 | | |
|* 6 | HASH JOIN RIGHT OUTER | | 30M| 20G| | 2152K (1)| 07:10:31 | | |
| 7 | TABLE ACCESS FULL | CMS_CUSTOMER | 27040 | 792K| | 223 (1)| 00:00:03 | | |
|* 8 | HASH JOIN RIGHT OUTER | | 30M| 19G| | 2152K (1)| 07:10:25 | | |
| 9 | TABLE ACCESS FULL | OMS_BUSI_TYPE | 26 | 442 | | 3 (0)| 00:00:01 | | |
|* 10 | HASH JOIN RIGHT OUTER | | 30M| 18G| | 2151K (1)| 07:10:22 | | |
| 11 | TABLE ACCESS FULL | PRJ_PROJECT | 4911 | 139K| | 66 (2)| 00:00:01 | | |
|* 12 | HASH JOIN RIGHT OUTER | | 30M| 17G| 2080K| 2151K (1)| 07:10:18 | | |
| 13 | TABLE ACCESS FULL | RES_ORG | 75887 | 1185K| | 390 (2)| 00:00:05 | | |
|* 14 | HASH JOIN RIGHT OUTER | | 30M| 17G| | 1252K (1)| 04:10:34 | | |
| 15 | TABLE ACCESS FULL | SE_DIST | 3342 | 23394 | | 16 (0)| 00:00:01 | | |
|* 16 | HASH JOIN RIGHT OUTER | | 30M| 17G| | 1252K (1)| 04:10:30 | | |
| 17 | TABLE ACCESS FULL | SE_DIST | 3342 | 23394 | | 16 (0)| 00:00:01 | | |
|* 18 | HASH JOIN RIGHT OUTER | | 30M| 16G| | 1252K (1)| 04:10:27 | | |
| 19 | TABLE ACCESS FULL | SE_DIST | 3342 | 23394 | | 16 (0)| 00:00:01 | | |
|* 20 | HASH JOIN RIGHT OUTER | | 30M| 16G| | 1251K (1)| 04:10:24 | | |
| 21 | TABLE ACCESS FULL | RES_ORG | 75887 | 963K| | 388 (1)| 00:00:05 | | |
|* 22 | HASH JOIN RIGHT OUTER | | 30M| 16G| | 1251K (1)| 04:10:16 | | |
| 23 | TABLE ACCESS FULL | SE_DIST | 3342 | 23394 | | 16 (0)| 00:00:01 | | |
|* 24 | HASH JOIN RIGHT OUTER | | 30M| 16G| | 1251K (1)| 04:10:13 | | |
| 25 | TABLE ACCESS FULL | SE_DIST | 3342 | 23394 | | 16 (0)| 00:00:01 | | |
|* 26 | HASH JOIN RIGHT OUTER | | 30M| 15G| | 1250K (1)| 04:10:10 | | |
| 27 | TABLE ACCESS FULL | SE_DIST | 3342 | 23394 | | 16 (0)| 00:00:01 | | |
|* 28 | HASH JOIN RIGHT OUTER| | 30M| 15G| 2896K| 1250K (1)| 04:10:07 | | |
| 29 | TABLE ACCESS FULL | RES_ORG | 75887 | 2000K| | 388 (1)| 00:00:05 | | |
| 30 | PARTITION RANGE ALL | | 30M| 14G| | 478K (2)| 01:35:43 | 1 | 52 |
| 31 | TABLE ACCESS FULL | OMS_TRAFFIC | 30M| 14G| | 478K (2)| 01:35:43 | 1 | 52 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TRAFFICORDER"."COLLECT_ORG"="COLTORG"."ORG_ID")
6 - access("TRAFFICORDER"."CUST_ID"="CMS_CUSTOMER"."CUST_ID")
8 - access("TRAFFICORDER"."PRD_TYPE"="OMS_BUSI_TYPE"."BUSI_TYPE_CD")
10 - access("TRAFFICORDER"."PRJCT_ID"="PRJ_PROJECT"."PRJCT_ID")
12 - access("TRAFFICORDER"."CRT_ORG"="CRTORG"."ORG_ID")
14 - access("TRAFFICORDER"."RCVER_CITY_CD"="SE_DIST5"."DIST_ID")
16 - access("TRAFFICORDER"."RCVER_COUNTY_CD"="SE_DIST6"."DIST_ID")
18 - access("TRAFFICORDER"."RCVER_PROV_CD"="SE_DIST4"."DIST_ID")
20 - access("TRAFFICORDER"."SELF_ORG_ID"="SELFORG"."ORG_ID")
22 - access("TRAFFICORDER"."SENDER_CITY_CD"="SE_DIST2"."DIST_ID")
24 - access("TRAFFICORDER"."SENDER_COUNTY_CD"="SE_DIST3"."DIST_ID")
26 - access("TRAFFICORDER"."SENDER_PROV_CD"="SE_DIST1"."DIST_ID")
28 - access("TRAFFICORDER"."UPD_ORG"="UPDORG"."ORG_ID")
里面有大量的hash join，还有一个 HASH UNIQUE 。
-------------------------------------
SELECT /*ccccc*/COUNT(1) FROM (SELECT DISTINCT TRAFFICORDER.TICKET_NUM, SENDER_PROV ||
SENDER_CITY || SENDER_COUNTY "senderDistrict", RCVER_PROV || RCVER_CITY || RCVER_COUNTY
"receiverDistrict", TRAFFICORDER.ESTI_ARRIVE_TIME "arriveTime",
TRAFFICORDER.BACK_CNT "backCount", TRAFFICORDER.IS_BILLING "billing",
TRAFFICORDER.AMOUNT "billingAmount", TRAFFICORDER.UNIT "billingUnit",
TRAFFICORDER.BILLING_WEIGHT "billingWeight", TRAFFICORDER.BUSI_CATEGORY "businessCategory",
TRAFFICORDER.BUSI_RANGE "businessRange", TRAFFICORDER.CDN_NUM "cdnNmu",
TRAFFICORDER.CHECK_TYPE "checkType", TRAFFICORDER.CO_DLV_FLAG
"coDeliveryFlag", TRAFFICORDER.COLLECT_ORG "collectOrg",
Plan hash value: 3462057465
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 6933K(100)| | | |
| 1 | SORT AGGREGATE | | 1 | | | | | | |
| 2 | VIEW | | 30M| | | 6933K (1)| 23:06:38 | | |
| 3 | HASH UNIQUE | | 30M| 21G| 47G| 6933K (1)| 23:06:38 | | |
|* 4 | HASH JOIN RIGHT OUTER | | 30M| 21G| | 2152K (1)| 07:10:34 | | |
| 5 | TABLE ACCESS FULL | CMS_CLCT_INFO | 3824 | 123K| | 16 (0)| 00:00:01 | | |
|* 6 | HASH JOIN RIGHT OUTER | | 30M| 20G| | 2152K (1)| 07:10:31 | | |
| 7 | TABLE ACCESS FULL | CMS_CUSTOMER | 27040 | 792K| | 223 (1)| 00:00:03 | | |
|* 8 | HASH JOIN RIGHT OUTER | | 30M| 19G| | 2152K (1)| 07:10:25 | | |
| 9 | TABLE ACCESS FULL | OMS_BUSI_TYPE | 26 | 442 | | 3 (0)| 00:00:01 | | |
|* 10 | HASH JOIN RIGHT OUTER | | 30M| 18G| | 2151K (1)| 07:10:22 | | |
| 11 | TABLE ACCESS FULL | PRJ_PROJECT | 4911 | 139K| | 66 (2)| 00:00:01 | | |
|* 12 | HASH JOIN RIGHT OUTER | | 30M| 17G| 2080K| 2151K (1)| 07:10:18 | | |
| 13 | TABLE ACCESS FULL | RES_ORG | 75887 | 1185K| | 390 (2)| 00:00:05 | | |
|* 14 | HASH JOIN RIGHT OUTER | | 30M| 17G| | 1252K (1)| 04:10:34 | | |
| 15 | TABLE ACCESS FULL | SE_DIST | 3342 | 23394 | | 16 (0)| 00:00:01 | | |
|* 16 | HASH JOIN RIGHT OUTER | | 30M| 17G| | 1252K (1)| 04:10:30 | | |
| 17 | TABLE ACCESS FULL | SE_DIST | 3342 | 23394 | | 16 (0)| 00:00:01 | | |
|* 18 | HASH JOIN RIGHT OUTER | | 30M| 16G| | 1252K (1)| 04:10:27 | | |
| 19 | TABLE ACCESS FULL | SE_DIST | 3342 | 23394 | | 16 (0)| 00:00:01 | | |
|* 20 | HASH JOIN RIGHT OUTER | | 30M| 16G| | 1251K (1)| 04:10:24 | | |
| 21 | TABLE ACCESS FULL | RES_ORG | 75887 | 963K| | 388 (1)| 00:00:05 | | |
|* 22 | HASH JOIN RIGHT OUTER | | 30M| 16G| | 1251K (1)| 04:10:16 | | |
| 23 | TABLE ACCESS FULL | SE_DIST | 3342 | 23394 | | 16 (0)| 00:00:01 | | |
|* 24 | HASH JOIN RIGHT OUTER | | 30M| 16G| | 1251K (1)| 04:10:13 | | |
| 25 | TABLE ACCESS FULL | SE_DIST | 3342 | 23394 | | 16 (0)| 00:00:01 | | |
|* 26 | HASH JOIN RIGHT OUTER | | 30M| 15G| | 1250K (1)| 04:10:10 | | |
| 27 | TABLE ACCESS FULL | SE_DIST | 3342 | 23394 | | 16 (0)| 00:00:01 | | |
|* 28 | HASH JOIN RIGHT OUTER| | 30M| 15G| 2896K| 1250K (1)| 04:10:07 | | |
| 29 | TABLE ACCESS FULL | RES_ORG | 75887 | 2000K| | 388 (1)| 00:00:05 | | |
| 30 | PARTITION RANGE ALL | | 30M| 14G| | 478K (2)| 01:35:43 | 1 | 52 |
| 31 | TABLE ACCESS FULL | OMS_TRAFFIC | 30M| 14G| | 478K (2)| 01:35:43 | 1 | 52 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TRAFFICORDER"."COLLECT_ORG"="COLTORG"."ORG_ID")
6 - access("TRAFFICORDER"."CUST_ID"="CMS_CUSTOMER"."CUST_ID")
8 - access("TRAFFICORDER"."PRD_TYPE"="OMS_BUSI_TYPE"."BUSI_TYPE_CD")
10 - access("TRAFFICORDER"."PRJCT_ID"="PRJ_PROJECT"."PRJCT_ID")
12 - access("TRAFFICORDER"."CRT_ORG"="CRTORG"."ORG_ID")
14 - access("TRAFFICORDER"."RCVER_CITY_CD"="SE_DIST5"."DIST_ID")
16 - access("TRAFFICORDER"."RCVER_COUNTY_CD"="SE_DIST6"."DIST_ID")
18 - access("TRAFFICORDER"."RCVER_PROV_CD"="SE_DIST4"."DIST_ID")
20 - access("TRAFFICORDER"."SELF_ORG_ID"="SELFORG"."ORG_ID")
22 - access("TRAFFICORDER"."SENDER_CITY_CD"="SE_DIST2"."DIST_ID")
24 - access("TRAFFICORDER"."SENDER_COUNTY_CD"="SE_DIST3"."DIST_ID")
26 - access("TRAFFICORDER"."SENDER_PROV_CD"="SE_DIST1"."DIST_ID")
28 - access("TRAFFICORDER"."UPD_ORG"="UPDORG"."ORG_ID")
里面有大量的hash join，还有一个 HASH UNIQUE 。
我们查看MOS，看看HASH UNIQUE 究竟是有谁决定的，发现HASH UNIQUE 行为由隐藏参数”_gby_hash_aggregation_enabled”决定，oracle10gR2中此参数默认是true，oracle建议将隐藏参数：_gby_hash_aggregation_enabled设置为false来避免这个问题。通过关闭这个隐含参数应该能解决问题，但是需要想领导汇报，流程会有点长，但是现在应该要处理这个问题，应该要想想这个sql本身有没有问题，其实前面隐含的讲一下，我是对这个sql有很大意见的：
1.没有where条件。没必要统计全部的数据，因为绝大多数用户只查询近一周甚至半个月的运单号，查询全部的数据是让数据库做很多额外的无用功；
2.这里面的distinct有没有必要，我知道这个OMS_XQDXINB里面的数据本身有主键约束，他自己不会存在重复的数据，他和其他码表关联也都是用其维度信息和码表的主键关联，因此不可能产生重复的数据。这里用上distinct也是让数据库做额外的无用功。
通过和开发经理的沟通，他同意了我的观点。改正了代码，这个sql查询快了几十倍，也没有出现这个600错误。
总结，通过这个案例，
1.发现oracle的600错误有时也并不可怕，好多也是由于开发代码的不当导致的，重要的是去分析；
2.当mos上提出修改某个参数时要谨慎，是不是必须要改，特别是隐含参数，修改后有没有什么后遗症等，如果不改会不会有其他解决办法，不要不加分析的就去修改。
3.懂一些业务真的对优化很有帮助。
参考文档：ORA-600 [32695] [hash aggregation can't be done] [ID 729447.1]
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12129601/viewspace-758973/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12129601/viewspace-758973/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
