SQL & PL/SQL

Is there anything one should know when trying to insert HINTS into a CREATE TABLE ... AS SELECT ... (CTAS) command? Hints sometimes do not seem to work for CTAS. Among other things, I get the impression that FIRST_ROWS is treated as ALL_ROWS because the command does not transactionally introduce the first row to the database till the entire table is released.

Where should hints be placed in a CTAS?
If the command has subqueries where should their hints be placed, and is there a special syntax to reference nested query?

How can I get V$SESSION_LONGOPS to show long-running CTAS commands?

One big issue we are having that is generating all these queries is that sometimes, and for no apparent reason, the optimizer will execute a plan for a CTAS that loads a 100 million row table into memory for a MULTI-PASS HASH JOIN that changes the execution time from 15 minutes to 5 days. I noticed other are having this same problem. It seem the optimizer does not consider the cost of multiple passes when a hash join exceeds memory. Also, the NO_USE_HASH hint does not seem to work.

I'd be happy to get suggestions on any of the above questions.