Method R Discussion Group

Method R helps developers, DBAs, and decision-makers optimize Oracle-based software in every phase of the software life cycle.

Discuss Method R products, techniques, and events.

Ask new questions or post your own responses.

Oceans via database link?

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.
Bundit Jianpinitnan Send private email
Friday, May 5, 2017
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!
Cary Millsap Send private email
Sunday, May 7, 2017

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Discussion Groups Main Page

Powered by FogBugz