Oracle DBA Interview Questions
Oracle DBA Interview Questions
Here are a few of my favorite Oracle DBA Interview Questions
1) WHAT’S A TYPICAL RATE THAT ORACLE CAN PERFORM SEQUENTIAL READS?
Single block reads are typically performed at rates above 100/sec. On many systems, the rate can be above 200 seq reads/sec.
2) HOW CAN YOU ESTIMATE THE SEQUENTIAL READ RATE ACHIEVABLE BY A SINGLE SESSION?
There are several good ways to do this. In a given Statspack or AWR report, sequential reads are listed. Very often, sequential reads will be listed as part of the “Top 5 Timed Events.”
Even if sequential reads are not a “top 5” event, this information will be listed, along with all wait events, in the “Wait Event” section. In this section, the average wait (ms) is shown. Simply take the reciprocal to get the read rate.
Besides reports, you can easily estimate the rate achievable (for a single thread) by querying a view such as V$FileStat, or V$System_Event. Here’s one possibility:
Select EVENT, TOTAL_WAITS, TIME_WAITED , Round(100*Total_Waits/Time_Waited) Rate
From V$System_Event Where Event Like 'db file sequential read%';
EVENT TOTAL_WAITS TIME_WAITED RATE
--------------------------- ------------------ ----------------- -------
db file sequential read 6232709774 226760222 274
Note that the rate above is an estimate of what a single thread could reasonably achieve.
Another possibility is to examine the appropriate wait event in a tkprof output. This will not be as accurate, however, since the listing shows the wait times just for the particular session being traced, rather than an overall rate for a long period of time.
3) HOW CAN TABLE PARTITIONING ACTUALLY HURT PERFORMANCE?
Depending on the exact queries that are executed, it is very possible that queries on partitioned tables will run worse than a non-partitioned table. Although people are often surprised by this result, it is not at all unusual. In fact, one of the main tasks when partitioning tables is to ensure against this possibility.
The key point is this: Is the partitioning key contained in the where clause? If not, the optimizer will not be able to restrict the scope of work to just a few partitions. That is, partition pruning will not occur.
Depending on the type of indexes (local versus global), this can have a terrible performance impact. Consider a very large table, Emp_History, which has been partitioned into 52 weekly partitions (based on employee start date.). Now look at the query below:
Select * from Emp_History where Dept = 101;
In the above query, Oracle can use a local index on Dept. However, the where clause does not include the partition key. So, if the local index on Dept is used, Oracle has no choice but to scan all 52 partitions. Without partitioning, the same query would have resulted in only 1 index range scan.
I hope you find these sample questions helpful. It is important to ask FAIR questions of candidates, which is why I wrote an entire book just on the subject of Oracle DBA interviews.
Do not try to "trick" the candidate.
Don't be Afraid
Oracle DBA Interview Questions
