trumped Interview Question

[tekpr00]

Hello,

Recently, I attended an Oracle DBA interview. The question was - Two users submitted two identical READ ONLY SQL Statements.

- The second user (user2)noticed that the result took longer to return that the first user(user1). Explain.

I answered as follow:
This is one of those "gotcha" questions, there could be various reasons.

1)There could be resource locked down by user1 depending on how much data is being fetched by user1 statement.
2)Also, there is a possibility that the data block being is no longer available in the buffer cache, that is, it has been flushed from the Buffer Cache by another user process (not user1) or put at the LRU end of the LRU list in the buffer cache.
3)If there is a sort in the select statement, large pool might being involved and the size of the large pool.

I did not get any feedback from my interview, hence this has been bothering me.

Please let me know if you have different take on this. Also, let me know if there are any oracle interview questions out there that I can study.

Thanks,
tekpr00

 

[SantaMufasa]

TekPro,

Your Response #1 does not apply since "READ ONLY SQL Statements" do not produce locks on any rows.

If all else is equal, then Reponse #3 should not apply since the sorting should be identical, using the same resouces for both queries.

We don't know from your description if there is a meaningful amount of time between the two executions of the query. If the two queries are run close together, then, if anything, query 2 should run faster than query 1 because we would expect the data block(s) to still be in the buffer cache.

But, the most likely cause is a variation of your Reponse #2: the necessary block(s) happened to be in the cache for query 1, but were not in the cache for query 2, thus having to be re-read in to the buffer cache.

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[karluk]

I think you are absolutely right that this is something of a trick question with no single right or wrong answer. The intent is to make you dig into your knowledge of Oracle to come up with one or more plausible scenarios of why this might happen. I also agree with SantaMufasa that Response #2 is the best of your answers. Here are some other possibilities if you get the same question in the future:

1. The second query happened to be running concurrently with one or more updates to the same table, thus forcing Oracle to retrieve data from undo segments in order to present a read-consistent result set.
2. The second query was run in a session with a different optimizer mode (say first_rows instead of all_rows), thus resulting in the query using a less efficient access path.
3. An index used by the first query execution was later dropped, thus causing the second execution to do a full table scan of a large table.
4. The second execution happened to run during a period of peak usage, when overall system performance was more sluggish than during the first execution.

 

[jimirvine]

Just for fun, if you are looking for a REAL gotcha:

Two users submitted two identical READ ONLY SQL Statements.

Let's take the statement
SELECT * FROM employees.

User1's employees table has 10 rows, User2s employees table has 10000 rows (i.e. they both have employees tables in their schemas)