Friday, June 09, 2006

First batch of questions from my lastest interview. I will provide my suggested answers later when I edit this post. Pretty swamped with projects at work lately.


  1. How LOB is handled/saved in Oracle?
  2. What is DBWR? What it does in Oracle?
  3. When you set Oracle parameter global_name = true, what it means?
  4. How many types of partition in oracle?
  5. What is a latch? What’s difference between latch and lock?
    Oracle Metalink Note:22908.1 is a good source for this question.
    What is a latch?
    Latches are low level serialization mechanisms used to protect shared data structures in the SGA. The implementation of latches is operating system dependent, particularly in regard to whether a process will wait for a latch and for how long.
    A latch is a type of a lock that can be very quickly acquired and freed. Latches are typically used to prevent more than one process from executing the same piece of code at a given time. Associated with each latch is a cleanup procedure that will be called if a process dies while holding the latch. Latches have an associated level that is used to prevent deadlocks. Once a process acquires a latch at a certain level it cannot subsequently acquire a latch at a level that is equal to or less than that level (unless it acquires it nowait).
  6. Difference between shared pool latch and library cache latch?
  7. What’s the difference between Library cache bin and library cache lock? Which one is worse to have?
  8. Will a un-commit insert block other insert when no index. What if there’s bitmap index in place?
    Usually an insert should never block other insert or update in the table. It's a brand new row in the table there shouldn't be any contention.
    But there are 2 senarios, insert could block others,
    1. Waits for TX in mode 4 can also occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value thesecond session has to wait to see if an ORA-0001 should be raised or not.
    2. Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each ’entry’ in bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4
  9. What’s the difference between soft parse and hard parse? Is there any way to get rid of parse all along?
  10. How do you find out the information about blocking locks?
    Check the v$lock table. Where BLOCK <> 0 is blocker and REQUEST <> 0 is waiter
  11. List a few common wait events you see in statspack.
  12. When you check v$lock, there’s a lock type is ‘NQ’. What’s lock is it?
    This is an interesting one. I think many DBAs probably don't know this. The lock type in DBA_LOCK and v$lock is actually Enqueue name. The complete list of enqueue name can be found here . NQ is one of the library cache pin locks.
  13. How do you recover the database if you lost the control files.
  14. If you know the block_id and file_id of an object which table will be used to find out the name of the object?
    DBA_EXTENTS

No comments: