You may receive the error ORA-01652: unable to extend temp segment by 32 in tablespace TEMP while running the ETL, specially when tables are huge. If the SELECT statement is running against the tables which have 70-80 million rows, and the optimizer is doing the hash join, it will make use of TEMP and hash-area size to build the hash. If the TEMP tablespace is not big enough, then you may receive this error. There is no one value fit all the sizes, but in our case, with 100GB of space for TEMP tablespace resolve this error. You may have to set this to higher value if you have more than 100 million of rows in the tables.
Do not change the explain plan to make use of NESTED LOOP, because initially optimizer will take more space and time to build the hash but once the hash is built, query results will be much faster and it will perform better than NESTED_LOOP.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment