Home » RDBMS Server » Performance Tuning » Why does monitoring index fix performance? (Oracle Database/Forms 11g, Windows 7)
Why does monitoring index fix performance? [message #633315] Sun, 15 February 2015 12:26 Go to next message
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 #633316 is a reply to message #633315] Sun, 15 February 2015 14:17 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
> Why would monitoring them temporarily fix the issue?

Exactly what is the issue here?

To me, it is just more important to know what produces the SLOW behavior (in the first place) & to make the necessary change so it does not happen again in the future.
Re: Why does monitoring index fix performance? [message #633327 is a reply to message #633315] Mon, 16 February 2015 01:42 Go to previous messageGo to next message
John Watson
Messages: 7265
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 Go to previous messageGo to next message
Roachcoach
Messages: 1513
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 Go to previous messageGo to next message
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

Re: Why does monitoring index fix performance? [message #633437 is a reply to message #633436] Wed, 18 February 2015 12:56 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
You need to show the query and the plan, with the execution statistics. If your DBA is any good, that is what he will ask for.
My guess is that you need extended statistics. Most badly performing queries do.
Re: Why does monitoring index fix performance? [message #633438 is a reply to message #633437] Wed, 18 February 2015 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
http://docs.oracle.com/cd/B13789_01/server.101/b10752/hintsref.htm#30700

"The RULE hint disables the use of the query optimizer. This hint is unsupported and should not be used."
Re: Why does monitoring index fix performance? [message #633440 is a reply to message #633315] Wed, 18 February 2015 13:56 Go to previous messageGo to next message
nait1234
Messages: 29
Registered: August 2013
Location: Edmonton
Junior Member
BlackSwan,

I don't intend to use the RULE hint. I mentioned above I only used it as a test per the article I linked previously that stated to use this hint to force using rules based optimizer to see if the problem statement was faster (which the link said this indicates issue with cost based optimizer). I am not actually using the hint in the real code, just in a test I did.


And John, thanks for your help.. trying to gather the execution plan to pass along.
Re: Why does monitoring index fix performance? [message #633443 is a reply to message #633440] Wed, 18 February 2015 15:04 Go to previous message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just forget this site, it is b...

Previous Topic: Alert log file size can impact database performance
Next Topic: Execution plan
Goto Forum:
  


Current Time: Sat Feb 24 19:55:26 CST 2018

Total time taken to generate the page: 0.15497 seconds