In Oracle 10g, I have a SQL which executes reasonably faster(around 30-40 mins approx) today. When the same SQL was executed two days back, it took around 2.5 hours for the SQL to get completed. I thought the query's execution plan might have changed. But that was NOT the case. Both execution plans matched. So with DBA's help I got the AWR report generated to find the database activity during the period. Sure enough, this long running query featured along with couple other queries. In the AWR, this query featured in high number of buffer gets, disk reads list but there were atleast four other queries that were even higher(they constituted 98% together). In fact the top query in both lists were around 50% of buffer gets and 80% of disk reads while mine took only about 2% and 0.9% respectively. My query did not feature in any other lists including CPU time and Parsed calls.
So my question - Is it possible that high amount of buffer gets and disk reads by other queries impact my query's performance?
Absolutely. Without going into the depths of database design, a database system has a bunch of shared resources under the hood.
The largest is typically the data pages. These are maintained across the server and provide data for a query. If you are reading a table multiple times, for instance, then the second read is much, much, much more efficient when the page is already in memory. If other queries are using the page cache, then you fit fewer pages in the cache -- and it is more likely that your query needs to read from disk.
Of course, there can be direct contention as well. Multiple queries might be trying to fetch data from disk at the same time -- and your I/O calls may end up in a queue delaying them.
There can be more subtle interactions as well. One important optimization for database engines is "reading ahead". That is, if you read one data page, then it is likely you'll want the next one as well. While the engine is processing data in memory, the I/O system can go out and fetch the next one.
Well, if you have lots of queries running at the same time, the engine may not have the bandwidth to read ahead for your pages.
These are just some examples of how buffering and other queries affect performance. But the answer is a resounding "yes". Other queries can have an impact on the performance of your query.