RSS Feed

Method R Discussion Group

Based on your book MOTD2, I've got other case regarding Oceans itself, not part of any island, instead of connection pooling case, that's db link. Here is an example using --z=.05, --top=5 and --rc=exp.rc

    EXP-ID      DURATION      %  CALLS        MEAN        MIN        MAX
----------  ------------  ------  -----  ----------  ---------  ----------
        0  2,193.811841  99.9%    17  129.047755  23.789817  155.596334
      2339      1.396042    0.1%    69    0.020232  0.000000    1.298029
      2677      0.264419    0.0%    475    0.000557  0.000000    0.010000
      6347      0.195606    0.0%    69    0.002835  0.000000    0.053227
      733      0.186898    0.0%    217    0.000861  0.000000    0.048615
 14 others      1.182584    0.1%    992    0.001192  0.000000    0.049294
----------  ------------  ------  -----  ----------  ---------  ----------
TOTAL (19)  2,197.037390  100.0%  1,839    1.194691  0.000000  155.596334

This example is extracted from source connecting to remote via db link and doing some query and deletion. I could not figure out to solve this issue. Your suggestion is really appreciated.

Your 17 calls there with $exp_id=0 (lasting from 23.789817 to 155.596334 seconds) are all 'SQL*Net message from client' calls. This is how $exp_id is defined.

You can use the "Response time by line number for a given call name pattern" report to confirm this. You should see the line numbers for 17 'SQL*Net message from client' calls that last .05s or longer.

This will give you an idea where in the flow of your application these "think time" events occur. (You can also use the "Database calls in chronological order" report to see this.)

I think you're going to find that the time your application spends waiting on dblink calls will be restricted to your $exp_id != 0 experiences (1.396042s or less, in your report).

If you really do have a 'SQL\*Net .* dblink' problem, then you might try creating an AFTER LOGON trigger on the remote system to activate tracing for just the user(s) you're interested in. Or, if you know the SQLID of the statements you're interested in, you can use the "sql_trace[sql: sql_id=ds9j6z3j9n49k|95hh6uvjgspm2]" type syntax shown on page 58.

Good luck!