Telephonic Interview - for DBA - SQL

C: What is your experience on handling large databases ?
P:  I have handled a database of size 2GB.

C: Can you tell me the memory structure of your database ?
P : The SGA  size was of 20MB. I mentioned the size of database buffer, shared pool and redo log buffer caches.

C: How do you arrive at the SGA size ?
P: It depends on various parameters such as number of concurrent transactions at the peak hours, transaction size, number of applications using the database, number of other processes using the system resources and system configuration (dedicated/multi-threaded). We have to also ensure that no paging and swapping occurs for SGA.

C: How do you find whether the allotted memory resources are sufficient ?
P: We have to look for possible contention for memeory structures. for example, if the hit ratio for database buffer cache is low, we can allot more database buffers. Likewise, we can monitor the contention for library cache, redolog buffers, redo log latches and rollback segments.

C: Can you tell me the procedure to monitor rollback contention ?
P: Well, Using V$waitstat and V$sysstat we can see the how many times a server process waited for a free extent in rollback segment. If the percentage of waiting is more than one, we can conclude that the number of rollback segments are insufficient.

C: Why is ‘OPTIMAL’ parameter used in rollback segment ?
P: OPTIMAL parameter is used to bring back the rollback segment size if it were to grow due to long transactions. To arrive at the OPTIMAL size, We can monitor using ‘MONITOR ROLLBACK’ from SQL*DBA. Using this, we can see how much space ever allocated for each rollback segment in bytes, Cumulative number of times a transaction writing from one extent in a rollback segment to another existing extent and average number of bytes in active extents in the rollback segment, measured over a period of time.   

C: What was your backup strategy ?
P: Everyday I used to take hot backup which includes taking of relevent datafiles and then control file. Maintenance of archived redologs,  taking of cold backup and maintaining ASCII version of a few tables data were the other backup strategies formulated.

C: Have you done recovery ?
P: Yes, I have done recovery a lot many times.

C: How do you recover if a datafile is lost/damaged ?
P: First step is to see whether the database is in archivemode. Secondly, Whether the disk containing the datafile is accessible. If not accessible, rename the datafile  to another usable disk. If  archived, restore the affected datafile and archived redologs from backups; if the damaged file was part of the system tablespace, keep the database mounted and closed; use alter database option to do recovery, if it belongs to a non-system tablespace, use alter tablespace command to recover it after taking the tablespace offline.

C: How to recover if the database was in noarchivemode ?
P: Only way to recover the database is to restore the most recent complete backup.
 
C: Have you done tuning ?
P: Yes, I have tuned both applications and database.

C: How to tune an application ?
P: First, we have to ensure that the application uses well tuned SQL statements. We can use SQL trace facility and TKPROF to see the execution plan for SQL statement. If we are not satisfied with the current execution plan, use EXPLAIN PLAN command to see the execution plan for different versions of the SQL  statement. We can choose the SQL statement which gives faster response time as well as consumes less system resources.     

C: OK. All right. You will get a call tomorrow.  Do have any question to ask ?
P: Yes, I want to know what will be my role in your project.

M: The system is under development. You will be involved at this stage itself. It is expected to go to production in Jan’97.