Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1
  • 2

TOPIC: drilling into RPC EXEC calls to db link

drilling into RPC EXEC calls to db link 2 years 5 months ago #326

Hello everyone,
I'm trying to drill into information i'm seeing in my trace file with mrskew that has to do with RPC EXEC calls. From what i can see with TKPROF, the problematic query/function is a view that goes through a db link to another database and that query is poor.
What is the next step to get the actual query after i have this from mrskew to drill in deeper?
oradbp01: mrls semsp01_ora_10777_NKATZ_MOA.trc
R ORA START END FILE
75.366 10.2 2012-05-29T08:47:52.946-0400 2012-05-29T08:49:08.312-0400 semsp01_ora_10777_NKATZ_MOA.trc
oradbp01: mrskew semsp01_ora_10777_NKATZ_MOA.trc
CALL-NAME DURATION % CALLS MEAN MIN MAX
RPC EXEC 49.067544 66.7% 62 0.791412 0.000000 48.063693SQL*Net message from client 22.543986 30.7% 99 0.227717 0.000112 22.482086
SQL*Net message from dblink 1.529351 2.1% 1,241 0.001232 0.000050 0.342145
db file sequential read 0.091459 0.1% 127 0.000720 0.000449 0.008926
row cache lock 0.065538 0.1% 364 0.000180 0.000023 0.000673
SQL*Net more data from dblink 0.059608 0.1% 4,764 0.000013 0.000000 0.035042
library cache lock 0.032649 0.0% 142 0.000230 0.000004 0.000458
EXEC 0.026995 0.0% 25 0.001080 0.000000 0.015997
direct path read temp 0.025273 0.0% 8,542 0.000003 0.000000 0.000046
log file sync 0.014761 0.0% 6 0.002460 0.001903 0.003768
single-task message 0.013556 0.0% 1 0.013556 0.013556 0.013556
gc current grant busy 0.009166 0.0% 40 0.000229 0.000091 0.000406
gc current grant 2-way 0.008271 0.0% 44 0.000188 0.000112 0.000268
gc cr block busy 0.007151 0.0% 3 0.002384 0.002072 0.002610
gc current block 2-way 0.006182 0.0% 23 0.000269 0.000183 0.000479
gc cr grant 2-way 0.004839 0.0% 33 0.000147 0.000078 0.000241
PARSE 0.001999 0.0% 3 0.000666 0.000000 0.001000
gc cr block 2-way 0.001811 0.0% 5 0.000362 0.000292 0.000471
FETCH 0.000999 0.0% 23 0.000043 0.000000 0.000999
SQL*Net message to dblink 0.000936 0.0% 1,240 0.000001 0.000000 0.000004
5 others 0.000667 0.0% 105 0.000006 0.000000 0.000311
TOTAL (25) 73.512741 100.0% 16,892 0.004352 0.000000 48.063693

I tried this and didn't help me: oradbp01: mrskew semsp01_ora_10777_NKATZ_MOA.trc --name='RPC EXEC' --where='$p3 == 1' --group='$sqlid' --gl=SQLID
mrskew: no calls match $name=~/RPC EXEC/i where '(($p3 == 1) and ($dep==$depmin))' is true
The administrator has disabled public write access.

Re:drilling into RPC EXEC calls to db link 2 years 5 months ago #327

Lyall,

The reason your predicate is returning no calls is that $p3 is always 0 for RPC EXEC calls. If you leave off the «--where='$p3==1'» predicate, then I think you'll get the matches you're looking for.

In this version of mrskew, the tool does not map the RPC CALL text to an RPC EXEC like it maps PARSING IN CURSOR section content to a PARSE/EXEC/FETCH call. We have a case open for that deficiency (case 4057).

The workaround is to use «--group='"$file $line"'» to find the line number in the input trace file of the expensive RPC EXEC lines. Then use «grep -n "RPC CALL" filename» to see which RPC CALL line most immediately precedes the RPC EXEC that you're interested in.


--Cary
The administrator has disabled public write access.

Re:drilling into RPC EXEC calls to db link 2 years 5 months ago #328

Hi Cary,
That all worked and I was able to find the Function (package actually) that started the call. There's ALOT of trace data in between the RPC EXEC then the RPC CALL, 118803-31496 lines to be exact, 87307.
With mrskew, how can i find the sql that's run in those 87k lines that's problematic, like tkprof found?
This is the info from the trace file of the poor performing query that tkprof sees:
85305 EXEC #108:c=1000,e=867,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1306929373379329
85306 FETCH #108:c=1000,e=502,p=0,cr=69,cu=0,mis=0,r=1,dep=1,og=1,tim=1306929373379853
85307 =====================
85308 PARSING IN CURSOR #99 len=135 dep=1 uid=61 oct=3 lid=61 tim=1306929373379983 hv=2930248180 ad='de0e50d0'
85309 SELECT (ORIG_AMT - OWING_AMT) AMT_PAID FROM DA.CREDIT_REVIEW_AR_VW WHERE COMP_CODE = :B2 AND DOC_TYPE = 'IN' AND DOC_NO = TO_CHAR(:B1 )
85310 END OF STMT
85311 PARSE #99:c=0,e=96,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1306929373379982
85312 =====================
85313 PARSING IN CURSOR #121 len=37 dep=2 uid=0 oct=3 lid=0 tim=1306929373380067 hv=1398610540 ad='ddd70fb0'
85314 select text from view$ where rowid=:1
85315 END OF STMT
85316 PARSE #121:c=0,e=9,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1306929373380065

I'm trying to calculate the "tim"'s and it's not making sense to me.
The administrator has disabled public write access.

Re:drilling into RPC EXEC calls to db link 2 years 5 months ago #329

By default, mrskew uses the --where1=($dep==$depmin) predicate, which restricts your queries to the top level of recursive depth in the trace file. This is so that your queries will result in profiles without double-counting.

This is where the Method R Profiler gives you a big advantage, because it looks across recursive depths, un-double-counts, and gives you a stack-aware picture of where your time is going.

With mrskew, using --where1=($dep == 1) will give you a profile of your recursive depth 1 time consumption. If you want, you can further restrict your mrskew queries to the portion of the input trace file you know you're interested in, by using something like --where='85305 <= $line and $line <= 85316'. Your key here, though, is in the --where1 clause.
The administrator has disabled public write access.

Re:drilling into RPC EXEC calls to db link 2 years 5 months ago #330

I got a syntax error with --where1=($dep==1)
oradbp01: mrskew semsp01_ora_10777_NKATZ_MOA.trc --where1=($dep==1)
-bash: syntax error near unexpected token `('

with --where1=1, i don't get an error, but i don't think i'm seeing what i need to see.
I'll keep playing with it.
The administrator has disabled public write access.

Re:drilling into RPC EXEC calls to db link 2 years 5 months ago #331

You will need to quote any arguments that contain special characters like $. Depending on whether you're on Windows or not, you'll need to use either double or single quotes. The details are listed in the documentation (see mrskew --man) for reference.

So for your example, it should be written like:
mrskew semsp01_ora_10777_NKATZ_MOA.trc --where1='($dep==1)'
The administrator has disabled public write access.
  • Page:
  • 1
  • 2