Wait event asynch descriptor resize

Oracle Create Materialized View hangs and waiting for asynch descriptor resize - event 

Oracle Database 11g Release 2 x64 (11.2.0.1.0), Windows Server 2008 R2 64bit

Recently I wanted to create a materialized view for a usual query, which took about 1 minute to execute while the creation of the materialized view didn't stop for hours until I killed the process.

Monitoring the session showed me the wait event asynch descriptor resize. The following article explains the event very detailed and suggests to contact oracle support if it causes bigger problems: Asynch descriptor resize wait event in Oracle. Upgrading the database could also solve the issue.

As for me those solutions weren't possible I started to trace the session and saw lots of sequential reads: 

"WAIT #6: nam='db file sequential read' ela= 4384 file#=5 block#=2092840 blocks=1 obj#=77661 tim=7422676905762"

After hours of tracing and googleing I tried to simplify my query step by step and came to the conclusion that some 'special' SQL constructs cause that the materialized view creation never stops. 

The causing SQL part was a subquery that returns the latest record of a table:
(select a, b, c, day

from table x
where day = (select max(day) from table y where x.a=y.a))

I could rewrite the query using oracle analytic functions (Analytic functions by Example) and voilà my materialized view was created in about 1 minute.

The same problem occured in a query with 'NOT IN(' followed by a subquery. Also in that case there are several ways to rewrite and probably tune the query (NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: Oracle).

Another try could be setting SGA values manually to fixed values instead of using AMM and memory target.

Sorry that I don't have an explanation why those query constructs cause the materialized view creation to hang. Obviously it's a bug and probably solved in later releases. Anyway, if you have the same issue, I hope my post helps you to identify the cause of it.