Oracle DBA Interview Questions focusing on Performance Tuning

Situational ( Performance Tuning )
Q. Customer reports a application slowness issue, and you need to evaluate database performance. What do you look at for 9i and for 11g.

Possible answer: On oracle 9i, look at the statspack report , on oracle 11g look at the AWR report. In both reports look top sqls listed in elapsed time or cpu time. At sqlplus look sql_text from v$sql where disk_reads is high.

Q. You have found a long running sql in your evaluation of system health of database, what do you look for to determine why sql is slow?

Possible answer: Use explain plan to determine the execution plan of the sql. When looking at execution plan look for indexes being used, full table scans on large tables.

Q. You have a windows service is crashing, how can you determine the sqls related to the windows service?

Possible answer: Use sql trace to trace the username and program associated with the trace file. Use tkprof to analyze the sql trace and determine the long running sqls.