| data caching [message #493369] |
Mon, 07 February 2011 09:31  |
gamba
Messages: 10
Registered: June 2008
|
Junior Member |
|
|
|
sometimes when I re-run a query a few times, the speed after the first run become much faster. this is a problem for me when I'm trying to optimize a query. is there some sort of cache? can it be disabled?
|
|
|
|
| Re: data caching [message #493372 is a reply to message #493369] |
Mon, 07 February 2011 09:43  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Yes - the sga. And you can't really disable it and you shouldn't try.
Best thing to do is run the sql several times, ignore the first, and take the average time of the other runs.
|
|
|
|
|
|
| Re: data caching [message #493383 is a reply to message #493375] |
Mon, 07 February 2011 12:11 |
gamba
Messages: 10
Registered: June 2008
|
Junior Member |
|
|
"If SQL is only ever run infrequently, then your concern may be valid."
that's the problem, the query doesn't run enough times so that the caching will be helpful in a production environment, so the speed of the query after the first time is irrelevant.
it's query each user runs once when he enters the system.
the system doesn't even have that many users, but we are still getting complaints about the query taking 60-80 seconds to run.
the problem is, after the first time I run the query, the next time it only takes 5-10 seconds and it "seems" the problem is fixed. obviously, in an hour or whenever the next time a user comes again, he'll get the slow-uncached 80 second performance.
if I can't disable the SGA, is it possible to flood it with random data so that when I re-run the query, all the cached data will be irrelevant?
|
|
|
|
|
|
| Re: data caching [message #493444 is a reply to message #493383] |
Tue, 08 February 2011 00:17 |
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
|
Senior Member |
|
|
Oracle had introduced the "ALTER SYSTEM FLUSH BUFFER_CACHE" command for such *tests* (not for production). However, it still doesn't protect you from the effects of filesytem caching at the OS level (unless you are doing Direct I/O). Further down, you also have a Storage / SAN cache.
So, there really is no "guaranteed" way to flush the cache -- unless you reboot the server and storage between each test
(Don't do that !!!)
Hemant K Chitale
|
|
|
|
| Re: data caching [message #493454 is a reply to message #493444] |
Tue, 08 February 2011 02:18 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
|
Can you approach from the other angle in put the blocks in question into the keep cache? That way users would always see cached times. Note I've never actually tried this in anger, obviously depends on data sizes etc, all the usual caveats apply, ymmv etc etc but its probably worth a look.
|
|
|
|
| Re: data caching [message #493458 is a reply to message #493454] |
Tue, 08 February 2011 03:30 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
From the point of view of tuning I'd just go with my original suggestion. For any two queries the same one should be faster whether comparing the times with cached or uncached data. So just find what runs fastest with cached data and that should also be fastest with uncached data.
If that's still not fast enough then you want to look at keep caches or materialized views.
If you follow Blackswans link and provide the information described then we may be able to help further.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
