Skip to Content

Tuesday Morning, 8:34 AM, I was telecommuting from my home office off Martin Boulevard, working the performance squad (cue Dragnet police music, or for you under 40’s, the CSI chimes).  I had just generated an EarlyWatch report out of Solution Manager when something caught my eye.

image

It has been almost a month since our combined Unicode conversion and Oracle 10.2 upgrade on our R/3 system.  We’ve been systematically hunting down transactions and reports that went bad after the changes.  After the low hanging fruit of code fixes, statistics tweaks and new or modified indexes, the challenges become greater, as small changes to fix one program can backfire elsewhere.

It was fairly clear that a Z transaction stood out in the line-up.  But was it Unicode related, was it Oracle upgrade related, or had the ABAP code changed in the last  month?  Elsewhere in the EW report were hints that this Z code was using up to 10% of the system database time, so I suspected a DB tuning case.

I grabbed my hat and detective notebook (no gun, just a screen shooter) and headed downtown on the SAP-GUI freeway.

9:13 AM Tuesday

I knocked on the door at ST04, looking for a “Mr. ZPRDGI”.  No answer.

image

image

image

I realized that this approach wasn’t going to get me an answer, so I went next door, to ST03 and started asking questions.

What was running yesterday?  Was Mr. Z still in the neighborhood?  I canvassed ST03 by TOTAL and by application server, and found footprints.  The TOTAL view shows all users that ran the transaction, while the app view gave me more detail on steps and response time.  Early Watch was right, this was a case, however shaky.  ST03 didn’t tell me anything more though, clamming up on what triggered the runaway juror.  No stool pigeons here.

image

image

image

Well, if the weekly report didn’t tell me enough I was going to need to go to the last minutes analysis department of ST03.

9:21 AM Tuesday

I knocked on the classic ST03 entry point, flashing my badge and asking the whereabouts of Mr. Z.  Had he been seen lately?  Oh, yes. and he had a partner.

image

image

 

There it was, in black and white (and cyan).  Mr. ZPRDGI,  meet Mr. ZMM_PRDORD_ISSUE.  A couple of loan sharks if I ever seen one.  And hanging out with RFC and RSM13000 too; tough crowd.

Armed with the search warrant, I went back next door to ST04.

image

I looked for anything that had buffer gets, but ignored the fields.  I was looking for statements ready to talk.

image

So, any evidence of Mr. Z’s partner Mr Z?

Yes, but first a donut break, er, a conference call.

10:45 AM Tuesday.

ST04 reveals the evidence.

imageimage

 

Wait, hadn’t I seen this mug shot before?  Flipping through the SQL explain plan, I realized what I knew all along.  This Mr. Z was already on the 10 most wanted list.

image

Case closed. 

See What? Performance is slow? I’ll get right on it for details leading up to this crime.

The story you have just witnessed is true; only the user names have been changed to protect the innocent.

The two Mr. Zs have been remanded to the DBA squad for prosecution.  They are working with the Oracle Center of Excellence to straighten out the crooked optimizer that is Mr. Big behind these petty larcenies of response time.

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Former Member
    Jim:

    I really enjoy your blog -:) I’m both a Sherlock Holmes and Performance issues fan…So your blog really made my day -;)

    Keep on!

    Greetings,

    Blag.

    (0) 
  2. Paul Tomlinson
    Always the top of the poor performance wanted lists those Z programs.  Indexes, what are they for?

    Helps that friendly basis vs ABAP programmer banter carry on!

    Paul

    (0) 
    1. Jim Spath Post author
      Paul – while Z programs are the Usual Suspects (awesome movie, by the way), in this case it is the Oracle cost based optimizer that is acting differently after our 9->10 upgrade. This should be visible in the response time prior to early February.  I’ve added a link to my blog on our Unicode conversion, but I have written little about database adjustments.  I guess that’s a blog for another day,  I understand this issue relates to index caching and IN LISTS.  When I know more I will pass it on.  Jim
      (0) 

Leave a Reply