When do we have SQL*Net message from dblink wait events?

One of my colleagues asked me a very simple question like this.

When I execute the query containing dblink, exactly when do I have to wait the “SQL*Net message from dblink” event?

Simple, at first thought, but at second thought, it made me dumb.

So I made a very simple test case to investigate how Oracle processes the remote query. Here is my simple but stupid demonstration.

1. Make a query which needs long hard parse time. Following query joins 211 tables and needs tens of seconds to be optimized.

select count(*) from T_HIST@UKJA102, T_NO_PART@UKJA102, T_PART1@UKJA102,        
T_PART2@UKJA102, T_PART3@UKJA102, T_CURSOR@UKJA102, T_SOURCE@UKJA102,           
MV_SOURCE@UKJA102, RUN_STATS@UKJA102, T_BUG2@UKJA102, ..., T_PRED1@UKJA102,
 dual@UKJA102 where 1 = 0;                     

2. Genereate 2 10046 trace files. One with hard parse, the other with soft parse, like this.

connect id/pwd
alter session set events '10046 trace name context forever, level 8';
execute_above_long_parsed_remote_query;  -- this would be hard parse

connect id/pwd
alter session set events '10046 trace name context forever, level 8';
execute_above_long_parsed_remote_query;  -- this would be soft parse

From the trace files, I got following conclusion.
(This is a conclusion just based on my simple test case. Do not take it as a fact)

• In the soft parse phase(syntax check would be more appropriate expression), Oracle needs to have dblink communications to identify the remote objects. In my simple test case with 211 remote objects involved, Oracle exchanged 432 messages. Hence 432 SQL*Net message from dblink wait events.
• In the hard parse phase(optimization would be more appropriate expression), Oracle sends the remote-part query and waits for the remote database to optimize the query(if not yet optimized). Hence one long SQL*Net message from dblink here.
• In the fetch phase, as we already know, Oracle communicates with the remote database to retrieve the data. More fetches, more SQL*Net message from dblink events.

One thing to note is that Oracle always needs to validate the remote objects without regard to the hard parse being existent. In the above test case, Oracle always does 423 dblink communication at minimum before executing the query.

I’ve thought that the process waiting for the SQL*Net message from dblink is always in the fetch phase, but above test case made me realize it was too naive assumption. I would like to have more detailed and delicate investigation on this topic next time.

PS) And, the terminology of soft parse and hard parse is always confusing. It’s not technologically strict, but looks like kind of marketing term.