Sample Telephonic Interview

J : What is your experience on ORACLE ?

P : I have approximately 5 years experience.

 

J : What is the procedure that you followed in tuning ?

P : First I will find the cause of poor performance. Depends on the cause I will tune SQL,

     Memory and I/O.

 

J : What is the first step that you do when there is a performance problem with SQL ?

P : I will generate trace file using the latest statistics to monitor the SQL execution path.

 

J : How will you say whether a SQL statement is well tuned ?

P : By seeing the execution path of SQL  one can conclude whether a SQL is tuned.

 

J : How to generate output using the trace files ?

P : I use TKPROF utility to generate the formatted output from trace files.

 

J : What is the content of TKPROF output ?

P : It will show the execution path it followed like index scan or full scan was used for retrieving

     data from a table.

 

J : How many years have you worked as a DBA ?

P : About 2.5 years I have worked as a DBA.

 

J : Give me an example where you tuned the badly performing SQL statement ?

P : I gave an example of SQL statement using internal sort/merge and how it was tuned to avoid

     internal sort. 

 

J : How do you use cost based optimizer?

P : Statistics must be generated for the objects using cost based optimizer. If it is available,

     Oracle by default use the cost based optimization for finding the best execution path.

 

J : How do you go about tuning init.ora parameters ?

P : Monitoring the contention level of objects like database buffers, library cache, redo log buffer

      over a period of time, we can alter the init.ora parameters for better performance.

 
J : How to identify badly written reports without using trace ?

P : Using v$sqlarea, one can monitor the performance of sql statements used in the reports.

 

J : Okay. Thank you. bye.

P : Okay. Thanks bye bye.