Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

After the DBAs fiddled with some parameters in our Oracle 10g Production Environment, most queries became a little faster. But a single query (a quite complex one) became dramaticaly slower - it went from 4 min per execution to rougly 2 hours per execution when ran from the application server (Jboss). And the question was active in the DB during all that time, so it wasn't just the jboss. I never mananged to explain why this happened, because when I copied it from active session, bound the variables to the data logged in the application, it ran on 4min directly in the DB. The wait events were mostly scattered and sequential reads, no weird stuff. My best guess is typecasting issues in some form resulting in a different execution plan than the one i got directly in the DB.

Then, all of a sudden, it stopped being an issue. After rougly 1,5days, it's back on it's normal execution times. The maintainance crew says they did nothing. The DBAs says they did nothing. It was rougly 12h off from the latest statistics being gathered, and the server wasn't under alot of strain at the time so shouldn't be resource bound.

Is there any factor that can make Oracle suddenly change execution plans? Have anyone seen anything like this before? The SQL contains a view - is there any way I can see when the structure of that view was last changed? Does anyone have any other tips on how to find out what really happened?

share|improve this question
3  
What version of Oracle are you using? What edition? Are you licensed to use the AWR? Is Statspack installed? Do you see different query plans when the query is fast vs. when it was slow? You state that there are bind variables-- are there histograms on those bind variables? Might this be an issue of bind variable peeking? – Justin Cave Nov 26 '12 at 6:41
Off-topic; belongs on Database Administrators, but in its present form with no details it is likely to be closed as "not a real question". – Jim Garrison Nov 26 '12 at 6:53
I dont know more specific than 10g on version. No AWR. I've cheched wait_events with ASHMON. I've never been able to run the query in "slow mode", even though I copied it and bound variables to the same value as in the questions being slow through the application. How can I see histograms on the bind vars? – Emil Nov 26 '12 at 7:42
@JimGarrison - How do I make it "a real question"? It cant be not real just because I dont know the specific issue - thats what the question is about finding out :) – Emil Nov 26 '12 at 7:44
@Emil - What does select * from v$version return? I'm also a bit confused by your answer regarding your access to the AWR. If you are saying that you are using this ASHMON perfvision.com/ashmon.php then that tool is querying the AWR tables. If you are licensed to be able to use ASHMON, you should be licensed to query the AWR tables. If you aren't licensed to use the AWR, do you have Statspack installed? – Justin Cave Nov 26 '12 at 9:53
show 3 more commentsadd comment (requires an account with 50 reputation)

migrated from stackoverflow.com Nov 26 '12 at 11:45

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.