Welcome, Guest
Username: Password: Remember me

TOPIC: mrskew summary by sqlid

mrskew summary by sqlid 1 year 1 month ago #393

Hi, I would like to be able to see the totals for each sqlid execution in a trace file. I can do this manually by inserting a module and action, but that's tedious and I don't think realistic to expect from application code. Here's an example of what I would like to be able to do:

$ mrskew --group='"$mod.$act"' oiwrite.trc
'"$mod.$act"' DURATION % CALLS MEAN MIN MAX
crgtcfu67vyjw.First occurance 440.763644 17.9% 110,147 0.004002 0.000000 15.435654
crgtcfu67vyjw.Fifth occurance 378.207571 15.3% 110,095 0.003435 0.000000 15.261680
crgtcfu67vyjw.Second occurance 355.637168 14.4% 110,158 0.003228 0.000000 14.620777
crgtcfu67vyjw.Fourth occurance 347.623768 14.1% 110,138 0.003156 0.000000 14.892735
crgtcfu67vyjw.Third occurance 297.480382 12.1% 110,117 0.002701 0.000000 14.516793
. 262.105017 10.6% 61,065 0.004292 0.000000 0.748586
crgtcfu67vyjw.Sixth occurance 206.804353 8.4% 96,901 0.002134 0.000000 11.301282
JDBC Thin Client. 171.700762 7.0% 45,041 0.003812 0.000000 4.809269
crgtcfu67vyjw.13th occurance 0.788074 0.0% 44 0.017911 0.000000 0.505923
crgtcfu67vyjw.Seventh occurance 0.702111 0.0% 51 0.013767 0.000000 0.437933
crgtcfu67vyjw.15th occurance 0.698163 0.0% 92 0.007589 0.000000 0.425935
crgtcfu67vyjw.16th occurance 0.693601 0.0% 52 0.013338 0.000000 0.475927
crgtcfu67vyjw.Ninth occurance 0.625782 0.0% 53 0.011807 0.000000 0.447932
crgtcfu67vyjw.Eleventh occurance 0.612187 0.0% 39 0.015697 0.000000 0.457929
crgtcfu67vyjw.Twelth occurance 0.610815 0.0% 41 0.014898 0.000000 0.456931
crgtcfu67vyjw.Tenth occurance 0.572372 0.0% 16 0.035773 0.000000 0.508923
crgtcfu67vyjw.14th occurance 0.437273 0.0% 8 0.054659 0.000000 0.408938
crgtcfu67vyjw.Eighth occurance 0.425514 0.0% 12 0.035459 0.000000 0.407938
TOTAL (18) 2,466.488557 100.0% 754,070 0.003271 0.000000 15.435654
The administrator has disabled public write access.

Re:mrskew summary by sqlid 1 year 1 month ago #394

  • Jeff Holt
  • Jeff Holt's Avatar
  • OFFLINE
  • Posts: 103
  • Thank you received: 2
Hi, Andy.

Have you tried either or these?

mrskew --group='"$sqlid"' oiwrite.trc # cumulative c time

mrskew --group='"$sqlid"' oiwrite.trc --select='$e' # cumulative e time

These will report aggregations of dbcall lines where the dep value is the smallest in the file. In many cases, this will be time for statements at recursive depth zero.
The administrator has disabled public write access.

Re:mrskew summary by sqlid 1 year 1 month ago #395

Hi Jeff, hope things are going well. That's not quite what I want, but that gave me an idea. I thought what might work would be to group by duration for each sqlid, but it didn't quite work out:

$ mrskew --group='$dur' --where='$sqlid eq "crgtcfu67vyjw"' oiwrite.trc
'$dur' DURATION % CALLS MEAN MIN MAX
436.814451 15.435654 0.7% 1 15.435654 15.435654 15.435654
374.371032 15.261680 0.7% 1 15.261680 15.261680 15.261680
343.687835 14.892735 0.7% 1 14.892735 14.892735 14.892735
351.885802 14.620777 0.7% 1 14.620777 14.620777 14.620777
293.591199 14.516793 0.7% 1 14.516793 14.516793 14.516793
203.761257 11.301282 0.5% 1 11.301282 11.301282 11.301282
0.000276 0.946680 0.0% 3,432 0.000276 0.000000 0.000276
0.000274 0.933792 0.0% 3,409 0.000274 0.000000 0.000274
0.000268 0.923992 0.0% 3,445 0.000268 0.000268 0.001000
0.000271 0.923568 0.0% 3,409 0.000271 0.000000 0.000271
0.000278 0.921570 0.0% 3,316 0.000278 0.000000 0.000278
0.000277 0.920471 0.0% 3,326 0.000277 0.000000 0.000277
0.000272 0.911472 0.0% 3,351 0.000272 0.000272 0.000272
0.000273 0.903630 0.0% 3,311 0.000273 0.000000 0.000273
0.000279 0.897543 0.0% 3,220 0.000279 0.000000 0.000279
0.000275 0.895400 0.0% 3,257 0.000275 0.000000 0.000275
0.000283 0.891167 0.0% 3,149 0.000283 0.000283 0.000283
0.000270 0.886680 0.0% 3,285 0.000270 0.000000 0.000270
0.000269 0.886355 0.0% 3,295 0.000269 0.000269 0.000269
0.000280 0.885920 0.0% 3,166 0.000280 0.000000 0.000280
26,137 others 2,075.664368 95.5% 624,455 0.003324 0.000000 0.790401
TOTAL (26,157) 2,174.421529 100.0% 670,832 0.003241 0.000000 15.435654

I really just want the totals for each execution of a specific sqlid. I don't think $dur is the right variable. I'd like to go from a group by sqlid to a breakdown for each cursor execution of a specific sqlid. Don't know if that makes any sense.
The administrator has disabled public write access.

Re:mrskew summary by sqlid 1 year 1 month ago #396

  • Jeff Holt
  • Jeff Holt's Avatar
  • OFFLINE
  • Posts: 103
  • Thank you received: 2
All is well, here, Andy. I trust it is with you too. :)

I think I understand what you want but I should clarify just in case.

Let's say that you see these trace data:

PARSE #1:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=2
EXEC #1:c=2,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=4
FETCH #1:c=4,e=4,p=0,cr=0,cu=0,mis=0,r=3,dep=0,og=0,plh=0,tim=8
EXEC #1:c=8,e=8,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=16
FETCH #1:c=16,e=16,p=0,cr=0,cu=0,mis=0,r=4,dep=0,og=0,plh=0,tim=32

Would you consider this to be 2 executions of the same statement, where the first execution's elapsed time is 7 and the second execution's elapsed time is 24? Or would you consider it to be something else?
The administrator has disabled public write access.

Re:mrskew summary by sqlid 1 year 1 month ago #397

Yep, everything is good.

All the same. I want to group everything between the parse and the close.
The administrator has disabled public write access.

Re:mrskew summary by sqlid 1 year 1 month ago #398

  • Jeff Holt
  • Jeff Holt's Avatar
  • OFFLINE
  • Posts: 103
  • Thank you received: 2
How about these?

mrskew --group='"$sqlid:$exec_id"' oiwrite.trc # cumulative c time

mrskew --group='"$sqlid:$exec_id"' oiwrite.trc --select='$e' # cumulative e time

This will group by individual executions.
The administrator has disabled public write access.