RE: DBA Interview Questions

This is a pretty small shop. We have two pairs of DBAs. One pair each in our transactional and data warehouse environments. One full time Health Dept employee married up with a contract DBA. I have six instances on 4 servers and they are all small. Prod is about 50G. We're not quite into maintenance but 95% of the development is done. So, we don't really need a coder. All our ETL is done using Informatica and we have 2 full time experienced Powermart users. All our reporting is done using Cognos and we have 2 full time Cognos developers. And, as far as it goes, they are all pretty good at their jobs, and within the limits of the tools they are using they write good code. We have no performance issues. We are using Oracle OMS for job scheduling and are having an ongoing problem with a bug causing our daily load job to hang. It wasn't fixed in 9.2.0.6. I need someone who can stay and do stuff during the bi-weekly maintenance periods, weekends etc. Although we can   hire another DBA we can't pay me overtime because, "<insert some reason here>."  

The contract DBA will be doing light developer support and maintenance. Thats about it. Going forward we are going to RAC, SAN, maybe 10G this year (05), Data Guard and disaster recovery site in 06. I want a guy hired who knows something that I don't know in order to further train me and to provide something approaching 24/7 coverage.  

I don't really have much hope that the average DBA is going to know about wait interface tuning. I asked this guy today what resources he could call on to find help and information and the best I could get out of him was 'Google.' I personally think there is a plethora of good and bad information available and its important where you get it. If somebody said that they were an avid reader of this list, again, I would be impressed.  

I arrived at this list because I bought the Milsap & Holt performance tuning book, the list subscription instructions were in the book, and the troubleshooting principles espoused in the book were similiar to the methods I used during my previous career as a radar tech. I'm not sure how far that information has penetrated into the Oracle community.  

There is a whole lot more to Oracle but not a great deal more to this job. We will not be getting a face to face interview. The contractor is responsible for filling the position and most of these guys have a history of successful contracts with the contractor. From their perspective the applicants are a known quantity and we'll simply try to pick the best available. If they don't work out, we'll get another one. At least 50% of this job will involve getting along with me<g>.
-----Original Message-----

From: ryan_gaffuri_at_comcast.net [mailto:ryan_gaffuri_at_comcast.net] Sent: Wednesday, November 24, 2004 3:08 PM To: dofreeman_at_state.pa.us; Oracle-L (E-mail) Cc: Freeman, Donald
Subject: Re: DBA Interview Questions  

is this a developer (coder) or a dba spot or a hybrid? also what is most important... someone very good at datawarehousing and ETL tools or someone intimately familiar with the database who can tune? If its the latter, you will have a better shot grabbing a production DBA and having the person write PL/SQL. Warehouse jobs typically pay well so it may not be as hard as it would be to grab a DBA and have the person do OLTP code. A good selling point for a prod DBA also is that data warehouse experience is far more valuable than OLTP experience in todays marketplace and its hard to break into the warehouse market.  

 its very hard to find a programmer who knows much about tuning. we had to reject 95% of our phone screens to get people who can do it here... and we are near DC with a large pool of applicants. Can't hire consultants so that does cut down on the top candidates somewhat.  

are you using RAC? if not, doesn't matter if they know it.  

Some questions that might help:

1. when do you use a bitmap index(since you have a warehouse). this is interesting because the oracle documentation is wrong. Most people recite the docs. Jonathan Lewis has some excellent articles about this on dbasupport(I think that is the write one...). 
2. in an explain plan what does cardinality and bytes mean and in what cases would either be useful? Cardinality is more useful when tuning oltp type queries and bytes is more useful when tuning bulk processing. 
3. the true senior question on the 10046 trace(curteous of cary milsaps book) what does it mean the your timing values = 0 in the 10046 trace? it means time_statistics= false and you need to turn it to true and retrace. 
4. for datawarehousing being able to interpret a query plan in advance of running the query is very important since queries can run for hours(even if they are optimized). I think the 10046 trace is less important than reading a query plan in your case. I would focus more on that. It's very hard to tune a query that takes hours based strictly off of a plan especially complex onces... 
5.  basic one, how do you speed up bulk processing? See if they atleat know to turn off constraints and triggers, etc... 
6. what does nologging mean? In a warehouse you will probably use that alot. 
 

if you find someone who has read dan tows sql tuning book and uses his strategy, you probably found a sql pro...

--

http://www.freelists.org/webpage/oracle-l Received on Wed Nov 24 2004 - 14:29:26 CST