Dion Cho – Oracle Performance Storyteller

We are natural born scientists

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

with 3 comments

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.

About these ads

Written by Dion Cho

May 26, 2009 at 8:39 am

3 Responses

Subscribe to comments with RSS.

  1. Is it possible to have this wait event but no database link involved ?

    Ours is an E-Bus 11i instance running on Oracle 9.2.0.7 and we recently upgraded our apps from 11.5.9 to 11.5.10 and one of our session experienced heavy waits on this wait event. While checking the session, I see only Oracle code against standard Oracle tables and there’s no database link involved.

    I’m really confused on seeing this. Even the previous SQL had no database link. I checked with the developer and he said this particular session is a form session and there’s no way it queries any remote database.

    Thanks
    Muthu

    Muthu

    April 27, 2010 at 11:43 pm

  2. Pardon me, it DOES involve dblink. Though the developer said no dblink, I did a trace of the session and found out that the dblink is within the database from schema to schema (That itself is weird to me, as I wasn’t sure who decided to have that way, instead of simple grant)

    The thing that confuses more is the fact that for 1 parse/fetch/execute, it waits for 503 times on this event. Here’s the excerpts from the trace output :

    call count cpu elapsed disk query current rows
    ——- —— ——– ———- ———- ———- ———- ———-
    Parse 1 0.01 0.11 0 103 1 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 1 0.04 0.20 6 521 0 4
    ——- —— ——– ———- ———- ———- ———- ———-
    total 3 0.05 0.32 6 624 1 4

    The SQL in question is doing a select from a view that joins some local tables (meaning, from it’s own schema) and some other from the other schema for which it has dblink.

    Thanks
    Muthu

    Muthu

    April 28, 2010 at 2:08 pm

    • Hi, Muthu.

      The count of SQL*Net related wait is equal to the network round trip. If you should have 100 network round trips to retrieve a certain amount of data, you might have 100 wait of SQL*Net xxx wait event. See following samples.

      insert into t1 
      values
      (:b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10)
      
      
      call     count       cpu    elapsed       disk      query    current        rows
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      Parse        1      0.00       0.00          0          0          0           0
      Execute      1     10.64     146.30        130      44734     280770           1
      Fetch        0      0.00       0.00          0          0          0           0
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      total        2     10.64     146.30        130      44734     280770           1
      
      Misses in library cache during parse: 1
      Misses in library cache during execute: 1
      Optimizer mode: ALL_ROWS
      Parsing user id: 95  
      
      Rows     Row Source Operation
      -------  ---------------------------------------------------
            0  LOAD TABLE CONVENTIONAL  (cr=45232 pr=130 pw=130 time=0 us)
      
      
      Elapsed times include waiting on following events:
        Event waited on                             Times   Max. Wait  Total Waited
        ----------------------------------------   Waited  ----------  ------------
        db file sequential read                       121        0.19          3.86
        direct path write                             110        0.10          0.46
        direct path read                                9        0.03          0.07
        SQL*Net more data from client               22444        0.85         10.02
        buffer busy waits                               1        0.01          0.01
        log file sync                                   1        0.00          0.00
        SQL*Net message to client                       1        0.00          0.00
        SQL*Net message from client                     1        0.49          0.49
      

      The insert statement(just 1 execution) has 22444 network round trip to send the data to the database server.

      Dion Cho

      April 29, 2010 at 12:56 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 60 other followers

%d bloggers like this: