Strange Temporary Tablespace problem

Yesterday morning, one user from Application group sent me an email regarding a failed production procedure of loading process.

The error was
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

From the error itself, it's easy to make you believe this is TEMP tablespace space issue. Or the procedure doing large sorting operation. However, this production database has 32G temporary tablespace the combined production data is only around 10G.

So it's rather something went wrong than real space problem. And this procedure was running ok before.

With the help of OEM Grid Control and AWR report snapshot. I quickly find out the culprit query, which is

SELECT A.PRODUCT_ID,A.VENDOR_ID,A.PROD_AREA,B.ALT_GROUP,B.GRADE_SET,B.GRADE,C.BOG_ID,C.AT_STEP,C.STEP_PRIORITY,C.POWER_SPEED,C.ROUTE,C.TOPMARK, CFI,MIN_LOT_SIZE MINLOT,MAX_LOT_SIZE MAXLOT,STD_LOT_SIZE STDLOT,INCR_LOT_SIZE INCRLOT FROM MDMSCP.SAT_PRODUCT_VENDOR A, MDMSCP.SAT_BOM B, TMP_SAT_BOG CWHERE A.PRODUCT_ID = B.PARENT_PART_ID AND B.CHILD_PART_ID = C.BOG_ID AND B.ALT_GROUP = C.NAME ORDER BY A.PRODUCT_ID,B.GRADE DESC,STEP_PRIORITY

However, it's hard for me to make sense the problem. The execute plan revealed that optimizer has chosen a very bad execute path for this particular query. Instead of join A and B with correct condition, the optimizer used a MERGE JOIN CARTESIAN to join A and C first. Which went terribly wrong, with 150K records in each table, Oracle is merging a whopping 22500000000 records! It's easily defeated our temporary tablespace.
From the wrong plan I noticed that optimizer somehow think table A only have 1 row. A checking on statistics revealed that both A and B has wrong statistics that reporting these two are empty tables. So optimizer just did whatever.
After collection of statistics, the execution plan make a lot more sense, it started join A and B first and refer C later.

It's again approved how important to have correct statistics collected for your schema. Otherwise even a small query can screw up your database big time.

P.S. While doing investigation on this issue, I come accross Janaton's good write up about MERGE JOIN CARTESIAN

http://jonathanlewis.wordpress.com/2006/12/13/cartesian-merge-join/