| Why does monitoring index fix performance? [message #633315] |
Sun, 15 February 2015 12:26  |
 |
nait1234
Messages: 29
Registered: August 2013
Location: Edmonton
|
Junior Member |
|
|
So a few days ago we had an issue with our production server. No changes were made to the application (Oracle Forms 11g with 11.1.0.7.0 Database), but suddenly performance on one table dropped. I would do queries on it and they seem to be pretty quick, but when someone was running an update in a screen within Oracle Forms itself, it would hang for 10 seconds or so (where normally it would be unnoticable, less than a second). I will test this out a bit later, but when I did an update through TOAD, it would also be fast. I will test using another key.
Anyways, I was doing some poking around in TOAD and went into Index monitoring section and monitored two indexes. It showed they were being used (yes, I know this doesn't tell much, because some queries may not be using them, but read on). I then went in the app, and suddenly things were fine. I wrote that off as just the issue being fixed.
The next day, we got another call saying the problem was back. After trying some more stuff and nothing working, several hours later I decided I would try monitoring those indexes again. I tested the app immediately before monitoring and again, immediately after stopping monitoring. The app was slow before monitoring, and quick again after I turned monitoring off. Today, I log in, and find the app slow again, so I repeat this process again... monitor the two indexes, and bam, it's fast again.
What's going on here? Are these indexes potentially fragmented? Why would monitoring them temporarily fix the issue? (What goes on behind the scenes with that, that maybe temporarily corrects and also indicates what the problem is?)
I am an application developer, not a DBA, so while I have access to do quite a bit with the system, I will likely have to advise the DBAs on what's going on and perhaps a solution to permanently fix this.
Thanks!
|
|
|
|
|
|
| Re: Why does monitoring index fix performance? [message #633327 is a reply to message #633315] |
Mon, 16 February 2015 01:42  |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
THere are a few reasons why repeted execution of a statement will improve performance (which is what you are describing. For instance, cardinality feedback, adaptive cursor sharing, caching at various levels.
If you show the execution plans (the actual plans used, not the output of EXPLAIN PLAN, with statistics) you can work out what is happening, and fix the problem permanently.
|
|
|
|
| Re: Why does monitoring index fix performance? [message #633332 is a reply to message #633327] |
Mon, 16 February 2015 02:35 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
This is conjecture, but I would bet that monitoring is something which invalidates the plans around those indexes causing a reparse.
Get a hold of the information John is looking for, the problem will almost certainly be there, I'm just 95% sure that my first line is why it is "fixing it".
|
|
|
|
| Re: Why does monitoring index fix performance? [message #633436 is a reply to message #633315] |
Wed, 18 February 2015 12:24 |
|
nait1234
Messages: 29
Registered: August 2013
Location: Edmonton
|
Junior Member |
|
|
So I made some progress here.
I focused on one piece where I thought the slowness was occurring. It is a series of several update statements after each other. I then set up some timing within this piece and found that two updates were slow. I suspected they weren't using the index they should be. The explain plan to me looked good for these individual statements. Oddly enough, if I isolated them individually, they are quick. Also, if I do an explain plan on the statements individually, they show as using the proper index. If I run the whole update process as a whole (about 17 different updates), however, these two updates are consistently slow.
Next, I put in a hint to use the proper index in each of these statements. The whole update process was very quick now and the problem is gone (although I know that using hints is not a good idea, so I don't consider this a permanent solution).
Referring to this article, I took the suggestion of testing a RULE hint, and this also produced good results (the updates all finish very quickly).
So you know what kind of difference we're talking about, when I run with the hints added, the processing time completes in ~7% of the time (from 4.85s to 0.035s). I've tested this numerous times to ensure it wasn't due to caching, and the hint consistently drops the time down to ~ that quicker time. Without the hint, it runs around 4.85 seconds. It seems that there is an issue with the optimizer. I'll be discussing that with our DBAs this afternoon, but I am wondering if perhaps a daily load that happens each morning is perhaps the root of the issue (i.e. maybe the table involved needs to be re-analyzed after each load or something... they bring the database down nightly and I'm thinking that if any stats are done it is before this daily load).
[Updated on: Wed, 18 February 2015 12:37] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
