If I had a view (or table) which contained millions of rows and I executed these two queries from different sessions, would one query be adversely affected by another? (Please note no DML will be going on)
如果我有一个包含数百万行的视图(或表),并且我从不同的会话中执行了这两个查询,那么一个查询是否会受到另一个查询的不利影响? (请注意,不会有DML)
e.g. Select * from t1 where sex = 'M'; (Returns 20 columns and 10,000 rows) select sex from t1 where rownum < 2;
例如从t1中选择*,其中sex ='M'; (返回20列和10,000行)从t1中选择性别,其中rownum <2;
What about if I had multiple sessions executing query 1? Would they all be equally slow until one of them had been cached (provided it was large enough)?
如果我有多个会话执行查询1呢?在它们中的一个被缓存之前它们都会同样缓慢(前提是它足够大)吗?
I am currently experiencing degraded performance when executing similar queries in a load balancing test for the quicker queries, however when executed separately (even when the result hasn't been cached) I am getting 'normal' response times.
我在负载平衡测试中执行类似查询以获得更快的查询时遇到性能下降,但是当单独执行时(即使结果尚未缓存),我得到“正常”的响应时间。
2 个解决方案
#1
If the tables are not being modified and the queries are using base tables, then it would be surprising if these two queries were interfering with each other. In particular, the second query:
如果没有修改表并且查询使用基表,那么如果这两个查询相互干扰将会令人惊讶。特别是第二个查询:
select sex
from t1
where rownum < 2;
Should simply be fetching one row and going very fast.
应该只是取一行而且速度非常快。
The first can take advantage of an index on t1(sex).
第一个可以利用t1(性别)的指数。
If t1 is really a view, then Oracle probably has to do all the processing for the view. Twice, once for each query. If the view is complex, then this would put a load on the server and slow everything down.
如果t1实际上是一个视图,那么Oracle可能必须对视图进行所有处理。每次查询一次两次。如果视图很复杂,那么这会给服务器带来负担并减慢一切。
#2
Have you looked at what is happening in the buffer cache, in particular V$DB_CACHE_ADVICE for buffer hit/miss ration? Are there any candidates (in the underlying tables) for adding to the "KEEP" buffer to avoid IO? To be fair it can take a while to monitor this and understand what the picture is before deciding what action to take, but it is worth looking at. More information here: https://docs.oracle.com/database/121/TGDBA/tune_buffer_cache.htm#TGDBA555 .
您是否查看了缓冲区缓存中发生的情况,特别是V $ DB_CACHE_ADVICE缓冲区命中/未命中率?是否有任何候选者(在基础表中)添加到“KEEP”缓冲区以避免IO?公平地说,在决定采取什么行动之前,可能需要一段时间才能监控并理解图片是什么,但值得一看。更多信息请访问:https://docs.oracle.com/database/121/TGDBA/tune_buffer_cache.htm#TGDBA555。
#1
If the tables are not being modified and the queries are using base tables, then it would be surprising if these two queries were interfering with each other. In particular, the second query:
如果没有修改表并且查询使用基表,那么如果这两个查询相互干扰将会令人惊讶。特别是第二个查询:
select sex
from t1
where rownum < 2;
Should simply be fetching one row and going very fast.
应该只是取一行而且速度非常快。
The first can take advantage of an index on t1(sex).
第一个可以利用t1(性别)的指数。
If t1 is really a view, then Oracle probably has to do all the processing for the view. Twice, once for each query. If the view is complex, then this would put a load on the server and slow everything down.
如果t1实际上是一个视图,那么Oracle可能必须对视图进行所有处理。每次查询一次两次。如果视图很复杂,那么这会给服务器带来负担并减慢一切。
#2
Have you looked at what is happening in the buffer cache, in particular V$DB_CACHE_ADVICE for buffer hit/miss ration? Are there any candidates (in the underlying tables) for adding to the "KEEP" buffer to avoid IO? To be fair it can take a while to monitor this and understand what the picture is before deciding what action to take, but it is worth looking at. More information here: https://docs.oracle.com/database/121/TGDBA/tune_buffer_cache.htm#TGDBA555 .
您是否查看了缓冲区缓存中发生的情况,特别是V $ DB_CACHE_ADVICE缓冲区命中/未命中率?是否有任何候选者(在基础表中)添加到“KEEP”缓冲区以避免IO?公平地说,在决定采取什么行动之前,可能需要一段时间才能监控并理解图片是什么,但值得一看。更多信息请访问:https://docs.oracle.com/database/121/TGDBA/tune_buffer_cache.htm#TGDBA555。