April 18, 2012
I saw an interesting thread on the OTN forums this morning that forced me to stop and think about several items. The thread contains a question posed during a recent job interview – one of the best questions that I have seen (dare I say, better than one I might have crafted myself). Consider this: you are given a SQL statement, and a raw 10046 trace captured at level 12. The question is very simple: Is the SQL statement efficient or not?
What is the point of the interview question? Barely scratching the surface:
- It tests how you will approach unfamiliar problems if you encounter the problem once hired – it may not be this exact question, but this may be similar to a real problem that was faced by the company’s DBAs. Do you panic and say that it cannot be solved, do you say that the job belongs to someone else, do you say that there is no point in looking at the raw trace file, or do you dive into the problem and think about what could happen if… (yes, there is something here, but I do not want to spoil the fun for other candidates that may face this exact problem).
- It determines in part what types of material you have read to further enhance your knowledge of Oracle Database.
- It determines whether or not you recognize the potential problems that may be associated with specific Oracle Database releases (unpatched and feeling ANSI?)
- It determines whether or not you recognize differences in predicated cardinality and the actual number of rows returned, and how that may affect the performance outcome. Is it better to filter early or filter late, and does that apply to this situation?
- Is there a DISTINCT possibility that the ROWNUM function may allow an arbitrary set of rows to be returned, possibly dependent on the value of the OPTIMIZER_FEATURES_ENABLE parameter (see *** item below)?
- It determines whether or not you follow logic or magic when troubleshooting problems.
Reflecting on the question a bit, did the person in the interview have access to Google, TKPROF, the Oracle Database documentation, books with red covers, books with yellow covers, books with white covers, books with blue covers, etc.
*** A year ago in an OTN thread I made the following comment that applies to at least one of the above bullet point and also the answer to the question posed by the interviewer:
“Other than the “HASH GROUP BY” operation on the second line of the printed execution plan for 11.2.0.2, and the “SORT GROUP BY” operation on the second line of the printed execution plan for 10.1.0.3, the execution plans are identical. Keep in mind that 11.2.0.2, due to adaptive cursor sharing, is capable of changing the execution plan for future executions (there was a single parse call in this case, so adaptive cursor sharing likely did not take place). Also, keep in mind that 11.2.0.2 by default in a 10046 trace will output the row source operation execution plan after the first execution, rather than when the cursor was closed – this explains the difference in the ROWS column in the execution plan. If we look closely at the summary information, 11.2.0.2 performed 113,319 consistent gets in 673 executions, while 10.1.0.3 performed 175,168 consistent gets in 644 executions. Each execution in 11.2.0.2 is in theory more efficient than each execution in 10.1.0.3, yet the average time per execution is much longer.”
—
In your opinion, what do you think of the interview question?
(Please do not provide an exact answer to the interviewer’s question for at least 14 days, there is no sense in disrupting the interview process.)
Charles Hooper's Oracle Notes 
Hi Charles,
I agree that it’s a great interview question, although I don’t think that the interviewed person was expected to go to a high level of detail. If I remember correctly, the question wasn’t even whether or not the statement was efficient — it was whether it performed well (i.e. did it run fast). At least that’s the way I understood it.
Best regards,
Nikolay
I think it’s a great question because it allows you to go into a high level of detail and demonstrate knowledge.
For me it also leads to wider questions such as whether this query gives the correct answer (as you’ve hinted to with DISTINCT and ROWNUM Charles), what the schema is (can the query be more efficient – and does that matter) and is this execution representative – e.g. do the bind variable values have an effect?
Charles, this doesn’t strike me as a good interview question because it begs a “European of African?” type answer: please define efficiency? How do you define efficiency of a SQL statement? I haven’t seen any generally accepted definitions for SQL efficiency yet.
That would bring the bridge keeper, in this case the interviewer, into danger of ending up just as the one in the Monty Python and the Holly Grail. To remind everybody, the original question was to determine the airspeed velocity of an unladen swallow.
Mladen,
The success of this question probably depends heavily on the knowledge of the interviewer. If the interviewer simply read the question from a book, and is expecting that the person being interviewed will simply provide the answer found in the book, then the value of the question is about as useful as a day old lottery ticket (I believe that this is what you are stating using humor). However, if this question is the spark that starts a two way conversation with an educated interviewer, there is definite potential with this question.
* Maybe the interviewer finds that the person talks extensively without providing an answer to the question… might that describe the potential efficiency of the person being interviewed? Or might that demonstrate the extensive depth of knowledge of the person being interviewed, but their ability to communicate that knowledege requires a lot of improvement?
* Maybe the interviewer finds that the person answers a question other than what was asked… might that describe the ability of the person to stay on task and do as instructed? Or might that demonstrate to the interviewer that the person being interviewed is a deep thinker, and that the posed question is insufficient for the presented problem statement?
* Etc.
—
I have used interview questions of this type in the past – not necessarily expecting the person to provide the correct answer, but I was more interested in seeing a correct approach. For example:
Here is another one that I have used in the past:
Charles, as an interviewer, I quite like this style of question. What I particularly like is that there is quite clearly not a correct or incorrect answer. The ambiguities of the question (as posited by Mladen) are certainly there, but for me that enhances the question. It begs the candidate to ask further questions, and it affords the candidate the chance to answer with the level of detail at which they are most comfortable. The level that they choose is generally reflective of their experience level.
In the context of interviewing systems administrators, one of our favorite “questions” was to ask the candidate to describe the process involved in an HTTP GET request, from browser to server and back. Some candidates spent 15 seconds explaining what happened, while other candidates could spend 15 minutes providing details about DNS lookups, name caches, TCP SYN/ACKs, etc. It very quickly weeded out “senior” candidates whose seniority was simply time spent in a chair learning little. The interview question above strikes me as a similar type of question.
Hmmmm…… Every single time I see a DISTINCT in a subquery or inline view, I see a missing join – lazyness or just plain lack of nsight into the schema – or just plain bad design of the db to start with…
The rest is just decoration.
[…] do you think of the interview question of Oracle DBAs? Charles has a great […]
I’d have to wonder if returning a bunch of zero row queries and internally constructed views could ever be considered efficient, by definition.
That depends on the measue used to measure efficiency. If you use the cost of the duhveloper hours as an efficiency measure, then…..
It is in my notes that we really do not know much about the CI_USR_ACC_GRP_VW view other than it accesses CI_DAR_USR with the help of the XC691P0 and XC692P0 indexes. Strange that this restriction is applied outside the inline view. 🙂
I will try to remember to post everything that I see as interesting about the 10046 trace contents in about 2 weeks.
—
That is an interesting point, is the goal to determine if the developers are efficient at coding, or is the goal to determine if the 10046 trace shows an efficient use of the Oracle database? It could be argued (probably not successfully) that the CPU and disk resources were just going to waste, and in pursuit of efficient fighting of the depreciation of computing assets, the developers are obligated to create inefficient code (great, we are now using 95% of our CPU capacity, rather than just 5%, so we are now more efficient at getting our money’s worth from the computer assets 😉 ).
Hi CHarles,
I would beg to answer this question in general terms rather than specifics.
I think it is more important for a DBA of any discipline to know “how to go about solving the problem” than “how to solve the problem”. Most DBAs seem to be bugged on approaching the problem. In other words the ability to think out of the box is very important. To know how to approach a problem certainly requires exposure and past experience. It really boils down to “Why” as opposed to “How to”. How to answers are pretty easy to work out given the power of web and googling. Once the individual establishes the correct approach i.e. “How to go about”, the rest will be a matter of digging in and finding it out. The developers tend to treat the database more of a black box these days and hence it is crucial to get the expertise from the DBA at every stage of development. Without the knowledgeable DBA and just considering the automated tools and off shore support with little or no exposure to the application itself, the support becomes pre-packed, shallow, generalistic and inflexible. It will also cost more to the client.
The success of the question is wholey dependent on the answer the interviewer expects.
If they’re just looking to spark a conversation and see what direction you go then good.
If, like most corporate interviewers, they have a very precise, scripted answer they’re looking – and an answer that is probably wrong – and perhaps think that “it depends” is a sign of weakness, then chances are that it’s not going to be a great question.
There was a brief discussion at the last occasion of the London Oracle Beers informal gathering about the interview question “Tell me how the update statement works?” and the consensus was broadly that the right answer was “Do you want the abridged or unabridged version?” and then to hope that they really didn’t want the full array of possibilities and walk throughs of all the layers involved.
Cheers,
Dominic
It tests your experience with SQL and with performance tuning.
Putting a subquery in the Where clause is a good way to make things go slow. Better to put it in an inline view. Like the other inline view. Or, if it returns a single row, and you are using PLSQL, snag the value to a variable, and simplify the query.
Looks like there could be a cartesian product in the query. That will also help things go really slow.
Evidence: Why is there a Distinct clause? Well formed queries usually have no need for a Distinct. Distincts are often used to cover up cartesian products. Distinct will cover up returning many multiples of rows. But it will not cover up the performance issues: caused by too many rows, then requiring Distinct processing to only return a small number.
Filter conditions are better in the where clauses. Not the join clauses. Putting filters in the join clauses can help you miss doing proper joins. Which then leads to cartesian products.
You also need to check all those brackets and OR clauses. That can also lead to a cartesian product.
See my extensive presentation on cartesian products:
http://rodgersnotes.wordpress.com/2010/09/15/stamping-out-cartesian-products/
Alternatively, the interview question is used to get free advice from consultants, without actually paying them.
Because none of the other db people on staff can solve the performance problems.
Few days ago in one of the forums a DBA who went for a job interview (combined Oracle and Sybase) was asked if he knew any way of creating a database without using the available commands. When he asked the hiring manager for further details, the question was “do you know of any *undocumented* way of creating a database.. The guy was asking the forum for any hint on this matter. As usual there were the usual “this is the correct way to do it” to trench stories on how one could work around his/her way to create a database.
Regardless of any answer my point was “Is it ethically correct” to ask such question in the first place? Well, if this is an interview question, my first question I would ask myself is “is this someone I want to work for?”. It’s NOT a valid/pertinent interview question. My answer would be, “I might be able to figure that out, but is this a required skill?”. This is not something you have to know in order to perform a “normal” job as a DBA. Forget it. “Create database” IS the ONLY way to reliably create a database. It is like asking a unix administrator how you could create a filesystem without using the filesystem commands like “mkfs”. Ignore it, it is not relevant and there is NO good answer other than… “no”.
Sadly there are times these type of questions are asked often by senior managers that tend to google some smart questions and I feel that by asking these type of questions, they show their “old hat” knowledge so to speak. It also paves the way to the assumption that the mangaer gives his/her approval to smart/unsupported work. This type of work regardless of the criticality of work should not be encouraged.
Mich,
I think that I recall seeing a similar message thread on the OTN forums not very long ago. I think that maybe the person who was being interviewed might have remembered the question incorrectly – at least I hope that is the case. Here is a similar question from October 2011, where a person in an interview was asked if it was possible to create a database without using DBCA: https://forums.oracle.com/forums/thread.jspa?threadID=2292666
Well sure, from http://shahiddba.blogspot.com/2012/05/dba-interview-questions-with-answers_14.html
And if a person were to provide that answer to the interview question, that person should probably expect not to be hired (on Windows servers, oradim is used to create and manage the Windows service for the instance – it does not create a database).
Maybe the question was attempting to test the person’s depth of knowledge – did the person stop reading about Oracle Database once he determined how to use DBCA? The question might have been intended to prompt one or more of the following responses:
* Well, you can create a database using just SQL commands rather than usinf DBCA, but that is a documented procedure.
* Well, if you tell the Oracle installer to create a database, it does not actually create a database, but instead copies a pre-existing database (I believe that this is also documented).
* Well, if you have a instance running without a database, you can use RMAN to clone an existing database to the instance without a database, but that is a documented procedure.
* Well, it would be a silly suggestion to use an undocumented procedure to create the database, should the person also be expected to use an undocumented procedure to back up the database?
* Well, …
Regarding the point about “Is it ethically correct” to ask the question, I guess that the answer partially depends on the intended point of the question.
* Is the question geared to determine how the person being interviewed would respond to a non-sense suggestion?
* Is the question geared to determine what methods the person being interviewed has used to create databases, and why/when different methods were used?
* Is the question geared to determine what web sites the person has used to prepare for the interview?
* Is the question geared to determine if the person is a risk taker, willing to execute procedures that might risk the company’s data?
* Is the question simply geared to prove that the interviewer is the senior DBA because he learned about using the oradim utility?
—
Anyone else with an opinion regarding this question being asked during an interview?
Thanks Charles. Your points are valid. I guess one cannot be a seasoned DBA without taking reisk and responsibility. I work in Financial sector and occasionally you in conjunction with production support (normally early mornings on call etc) have to kill a session on the basis of which one is lesser of evil. Shall the long running report be killed as it has passed its SLA and the traders on far eeast are complaining about the system response or else. So taking a risk is part of DBSA life.
Now going back to the undocumented feature, I would put the interview question as “How do you feel about using undocumented features if you have to”? From the response one can guess whether the candidate follows certain guidelines or could be a loose canon so to speak.
Years ago myself I went for an interview and I was asked how to shrink a Sybase database. At that time there was no documented way of shrinking a database. Think of it as shrinking an Oracle schema. I knew of some notes on web about it but I equally knew that someone followed that method and few days after shrinking their database, it came to a standstill. My simple answer to three men interview panel was My answer was – no I do not know – because I still want to work in DBA world for few more years. To me If a feature is not practically applicable, then it is practically useless.
But sometimes even a valid question can get an interesting response. I recall I was doing a telephone interview with a junior DBA for a contract role few years ago. I asked the candidate if he could explain what the **Outer Join*** meant. He told me it was a join that involved two tables; one in the current database and the other one in a remote database. I then asked how far physically these two databases needed to be for the join to be efficient. He said he believed they had to be on the same VLAN and in the same building, otherwise the query performance will degrade. As you could imagine that made my day.
He might have been thinking of “outer space” join which happens when the table is joined to a table in a remote database over a spiritual dababase link. It’s also known as an “LSD join”.
Guys,
I am fascinated by expert and mature interpretation that Charles and Mich are covering the interview questions. I am a relatevely junior DBA but gest intimidated by interview questions like above. It is very difficult to answer because you don'[t want to disagree with your hiring manager. On the other hand you don’t want to look clueless. Personally I feel more at home if someone asked me direct technical questions as opposed to finding out if I surf the net for every obscure database question. My two cents.
Ashok
Ashok, a response like “I don’t know” or “I have never used that” is better than inventing an outlandish response. It shows your candidness and will result in better chances to get hired than making up a “LSD join” like the candidate above. If some konwledge is required for the position, it’s better to walk away then to pretend to have the knowledge you don’t have and mess up things. I have recently walked away when told that a part of the job would be managing MS SQL databases. I know nothing of MS SQL, cannot do the job, period. On the other hand, not knowing what an outer join is definitely disqualifies anybody for the position of an Oracle DBA.
Thank you for your wise words sir. I will remember it.
Best,
Ashok