ORA-00600:內部錯誤程式碼,引數:[32695], [hash aggregation can't be done]的分析處理
近段時間，發現生產庫的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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORA-00600:內部錯誤程式碼,引數:[qertbFetchByRowID],[],[],[],[],[],[],[]分析與處理
- ORA-00600: 內部錯誤程式碼, 引數: [19004]
- 遇到ORA-00600: 內部錯誤程式碼, 引數: [kzsrgpw]
- ORA-00600: 內部錯誤程式碼, 引數: [kcbnew_3]
- ORA-00600: 內部錯誤程式碼, 引數: [qcisSetPlsqlCtx:tzi init]SQL
- ORA-00600: 內部錯誤程式碼, 引數: [kqlnrc_1]
- ORA-00600: 內部錯誤程式碼, 引數: [qertbFetchByRowID], [], [],
- ORA-00600: 內部錯誤程式碼，引數: [6122]
- ORA-00600: 內部錯誤程式碼, 引數: [kolaslGetLength-1], [], [], [], [], [], [], []
- ORA-00600: 內部錯誤程式碼, 引數: [kcbchg1_14]
- ORA-00600: 內部錯誤程式碼，引數: [kcbgtcr_12], [1], [], [], [], [], [], []
- ORA-00600: 內部錯誤程式碼, 引數: [12333]
- ORA-00600: 內部錯誤程式碼, 引數: [kcblasm_1], [103], [], [], [], [], [], []ASM
- ORA-00600: 內部錯誤程式碼，引數: [6002], [6], [28], [1], [52], [], [], []
- ORA-00600: 內部錯誤程式碼, 引數: [ktspScanInit-l1], [], [], [], [], [], [], []
- ORACLE ORA-00600: 內部錯誤程式碼, 引數: [kokbcvb1]與merge intoOracle
- ORA-00600: 內部錯誤程式碼, 引數: [kcrf_pvt_strand_bind1]CRF
- ORA-00600: 內部錯誤程式碼, 引數: [qosdDirRead: dircnt mismatch], [809], [808],
- 在刪除使用者時報ORA-00600: 內部錯誤程式碼, 引數: [13011]..
- ORA-00600: 內部錯誤程式碼, 引數: [ktspfmdb:objdchk_kcbnew_3], [9], [93111], [4],OBJ
- 【crontab】“bad minute”及“errors in crontab file, can't install”錯誤處理Error
- 如何在 Go 中優雅的處理和返回錯誤（1）——函式內部的錯誤處理Go函式
- RMAN的"rman: can't open target"錯誤
- 錯誤處理--pure specifier can only be specified for functionsFunction
- Laravel Excpetions(錯誤處理) 原始碼分析Laravel原始碼
- ORA-00600 [krcpop_badfile]錯誤處理
- 版本9204中的內部錯誤：ORA-00600[qmxiUnpPacked2]
- ORA-00600錯誤分析
- ORA-00600: [kcratr1_lastbwr]錯誤的處理辦法AST
- 主從故障處理--session 級別引數複製錯誤Session
- can't create PID file問題處理
- ORA-07445和ORA-00600系統內部錯誤查錯方法
- ORA-00600: internal error code, arguments: [4194] 錯誤處理Error
- 遠端連線錯誤程式碼及處理
- Bash 指令碼中的錯誤處理指令碼
- 【故障處理】修改maxuproc引數解決TNS-00519錯誤
- thinkphp原始碼分析（四）—錯誤及異常處理篇PHP原始碼
- 錯誤碼全域性處理（一）