If you have an inefficient query, and you add an index to help out performance, should the query "instantly" start using the index?
Or do you need to clear out the Oracle "cache" (v$sql I believe) by running alter system flush shared_pool;?
views:
89answers:
4
A:
Take a look at:
This discussion may be interesting:
how do i clear oracle execution plan cache for benchmarking?
Read this one too:
Leniel Macaferi
2010-07-22 16:21:44
I can tell when the index is being used by looking at the explain plan.. I just wanted to know if I have to tell Oracle to "recompute" the explain plan when I've created a new index. Could be that Oracle is using cache - or could be Oracle just isn't using my query. I'm trying to determine why the index isn't being used..
Marcus
2010-07-22 16:24:06
From what I know Oracle starts using your index right after it's been created.
Leniel Macaferi
2010-07-22 16:27:23
So your index isn't being used when you issue the explain plan?
Leniel Macaferi
2010-07-22 16:28:18
Correct, not being used. But it appears to be used after I clear the cache..
Marcus
2010-07-22 16:29:21
A bit of research in google for "oracle cursor invalidation" reveals that dependent cursors are marked for rolling invalidation, not immediately invalidated. See the fine explanation here: http://prutser.wordpress.com/2009/07/16/rolling-cursor-invalidation/
Adam Musch
2010-07-22 17:25:59
+3
A:
As the DBA loves to answer, "it depends."
It depends on if Oracle thinks the index will help performance. If Oracle thinks the index isn't the best choice for the query, Oracle's not using it anyway.
It depends on whether you're using prepared statements. A prepared statement isn't reparsed during its lifetime, so if a running app uses a prepared statement you're trying to fix, you'lll need to restart the app.
Flushing the shared pool will force Oracle to reparse and reoptimize all statements (a hard parse), so if Oracle thinks the index will help performance, flushing the shared pool will do trick. However, it can also have far reaching consequences in a live production system -- causing a "parse storm", as every statement in use must be reparsed and reoptimized -- and should only be undertaken as a last resort.
Adam Musch
2010-07-22 16:26:54
Why would restarting the app cause the prepared statements to be reparsed (and for the explain plan to be updated)?
Marcus
2010-07-22 16:28:14
Because a preparedStatement is a cursor in Oracle, and Oracle knows that your app is holding on to that cursor until you close the preparedStatement or your session ends. Ending the session by terminating the app is usually the only method of control one has. Once you're not holding that cursor (after app restart) Oracle will note that the new index has invalidated the statement in the shared pool, and reparse/reoptimize the statement. See the flowchart on page 3 here: http://www.oracle.com/technology/books/pdfs/jdbc_ch5.pdf
Adam Musch
2010-07-22 16:46:14
Deleted my last comment.. Ok.. it seems what is happening is I start he app and the query is fast.. but then it slows down and doesn't use the index any more. Why would this be? There has been no data added?
Marcus
2010-07-22 17:33:26
+3
A:
You should regather statistics on the table. You can compute or estimate statistics. Example usage
Compute
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'ENROLLMENT'
,TabName => 'STUDENTS'
,Estimate_Percent => 0
,Degree => 4
,Cascade => TRUE
,No_Invalidate => FALSE);
END;
/
Note the cascade argument is telling oracle to also gather stats on any indexes on the table as well.
Estimate
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => 'ENROLLMENT'
,TabName => 'STUDENTS'
,Estimate_Percent => DBMS_STATS.AUTO_SAMPLE_SIZE
,Degree => 4
,Cascade => TRUE
,No_Invalidate => FALSE);
END;
/
Brian
2010-07-22 16:43:09
Thx, that does work.. what exactly does it mean to gather statistics? Why would one need to do this in my case?
Marcus
2010-07-22 18:13:29
+1 to Brian, I just encountered this today! Was mad that my awesome new index was not being used :/ Ran stats on table and index and it worked. Marcus, Table and Index Statistics help the optimizer model the best way to execute your query. Usually a job runs in the evening to update statistics (10g,11g) but you want to re-gather asap so the Optimizer has the information immediately. There are whole books devoted to this so I won't cover all in my 500 characters here. "Cost Based Oracle Fundamentals" by Jonathan Lewis is probably the best place to start for in-depth coverage.
David Mann
2010-07-22 19:53:29
Probably the reason this changes the performance of the query is that it invalidated the cursor, not because the new index was otherwise "invisible" to the optimizer. Statistics on the table shouldn't have changed, and having statistics on the new index would mostly affect the choice among other indexes on the table.
dpbradley
2010-07-22 20:21:03
Possibly a bit of both. Depends on what parameters are used to gather states. If stats for the columns in the new index haven't previously been gathered (eg you use FOR ALL INDEXED COLUMNS), then the database might not have sufficient data to decide when it is best to use the index. Gathering the stats again could fix that. At the least, if you create an index, then do a GATHER_INDEX_STATS
Gary
2010-07-22 23:24:46
A:
Shared pool is not used to cache data.
Oracle Server has two performance measurement, logical read and physical read. Physical read is a measurement of disk read performance. Logical read is a measurement of read data from memory.
In any read method (index, full table scan or something), rows in blocks must be retrieved into buffer cache. It's the action of physical read.
Logical read is return result from cache if hit, if you use index to improve SQL performance, it's the improvement of logical read.
So in short, it's not necessary.
shiki
2010-07-22 17:00:16
Yes, the shared pool does not cache data; but in this case, the problem is that the query plan is being cached, and that *is* in the shared pool.
Jeffrey Kemp
2010-07-23 00:39:24