| Procedure performance issue on new DB import? [message #482705] |
Mon, 15 November 2010 10:41  |
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
|
Member |
|
|
Good morning,
Testing our 9i to 11g upgrade, we've imported the entire DB into the new machine.
We've found that certain procedures are really suffering performance problems. BUT, we've also found, that if we check out a production copy of the procedure from our source code control, and reinstall it, the performance issue goes away. Just alter the procedure and recompiling does NOT work.
The new machine where the 11g database exists is slightly different than the source, but it's not like we have this problem with every procedure. It's only a couple.
Can anyone think of any possible reason that we'd have to re-install a procedure to correct a performance problem? We'd really like to understand why this is happening.
Thanks
|
|
|
|
| Re: Procedure performance issue on new DB import? [message #482707 is a reply to message #482705] |
Mon, 15 November 2010 10:47  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
No idea whatsoever and really that makes no sense.
I would strongly suggest the next time it happens, instead of replacing the procedure you trace the session to see where the time is being spent.
Without knowing the root cause there isn't much point guessing at the reason for something that really shouldn't be happening.
EDIT: typos
[Updated on: Mon, 15 November 2010 10:47] Report message to a moderator |
|
|
|
|
|
| Re: Procedure performance issue on new DB import? [message #482737 is a reply to message #482708] |
Mon, 15 November 2010 12:04 |
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
|
Member |
|
|
Sorry for not providing this earlier.
11:23:52 >select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
I guess it would've been a good idea to turn tracing on before we re-installed the checked in version...so we'll have to do re-import (11 hours) before I could get that info.
I was more or less curious is anyone had ever seen something like that. It seems really weird...and I suppose we were most concerned with it coming back. The procedure in question goes from 2+ minutes down to nearly instant after the re-install and is a core app in our system.
Thanks all.
|
|
|
|
|
|
| Re: Procedure performance issue on new DB import? [message #482741 is a reply to message #482739] |
Mon, 15 November 2010 12:16 |
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
|
Member |
|
|
Thanks BlackSwan.
I completely understand. It was basically a shot in the dark without all the tracing info anyway.
Just for the record though, I ran the app nearly 20 times, all with terrible performance, before reinstalling the procedure which immediately fixed the issue.
And we've done this import 4 times over the past few weeks and have experienced the same exact issue every time.
Not that that info helps solve the problem, just wanted to put it out there.
I'll keep digging, and I'm sure we'll eventually perform the import again before our go-live, so I'll make sure to get some tracing info then.
Again, thanks for the responses.
|
|
|
|
|
|
| Re: Procedure performance issue on new DB import? [message #482745 is a reply to message #482742] |
Mon, 15 November 2010 12:32 |
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
|
Member |
|
|
BlackSwan wrote on Mon, 15 November 2010 13:22>
Are statistics exported/imported?
No. Statistics are gathered fresh during the nightly maintenance window with the automated stats gathering job BEFORE we ever test the applications or run any queries against the db.
BlackSwan wrote on Mon, 15 November 2010 13:22
Does SQL use Bind Variables (Bind Variable peeking?)
Yes, we are using bind variable. This is basically an "item search results" query, where our users can enter partial item numbers, descriptions, etc to provide a list of part numbers. Their search string is the bind variable.
BlackSwan wrote on Mon, 15 November 2010 13:22
Are new statistics gathered after import but before running any SQL?
yes sir.
Thanks again.
|
|
|
|
| Re: Procedure performance issue on new DB import? [message #482783 is a reply to message #482745] |
Mon, 15 November 2010 14:42 |
chris32680
Messages: 92
Registered: January 2006
Location: Charlotte
|
Member |
|
|
Ok...this has gotten a little weirder.
We started seeing this app slow down again so I thought it might be a good idea to get tracing turning on so I could see what was happening.
Since this is a web app going through the Oracle HTTP server mod_plsql module, I just issued
alter system SET EVENTS '10046 trace name context forever, level 8';
As soon as i did that i went and resubmitted the page again and it was instantaneous!
I turned off the event tracing and tried again, and it took nearly 2 minutes.
Tracing back on, instant. Back off, slow.
Does THAT give any clues?
I'm getting ready to start digging through the trace file to see if i can find the explain plan.
Thanks again
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
