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.
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.
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.
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.
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.
I looked for anything that had buffer gets, but ignored the fields. I was looking for statements ready to talk.
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.
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.
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.