Interview Question

  Super 

• Interview-question
• Interview Question:TOPIC

    
   Oracle Architecture 

• How-does-dml-statement-works-in-oracle
• How-does-select-statement-works-in-oracle
  
• oracle background process  
• sga-pga-automatic-memory-mgmt
  
• Library-cache-latch-contention
• Open_cursors & session_cached_cursor & Cursor Sharing
• Log-file-sync-log-file-parallel-write 
• Difference-in-checkpoint-and-commit
• Sequence of events when log switch happens
  
• Difference-in-roll forward-and-rollback
• Difference-between-instance-recovery and media recovery 
• Role of SCN in instance or media recovery
• Does-uncommitted-transaction-being written to data files 
• Oracle wait events  

   Database Structure 

• Difference-in-lmt-and-dmt
• Can we have 8kb and 16kb blcoks in databse.
• Non-standard-block-size-tablespace
• Oracle-database-startup-and-shutdown Sequence 

    Datagaurd 

• Dataguard

  Golden Gate 

• Golden Gate

RAC & ASM 

• RAC & ASM

   Security  

·   Security

Interview Question:TOPIC

   

   Oracle Architecture 

• Oracle wait events  

   Database Structure 

• Difference-in-lmt-and-dmt
• Can we have 8kb and 16kb blcoks in databse.
• Non-standard-block-size-tablespace
• Oracle-database-startup-and-shutdown Sequence 

    Datagaurd 

• Dataguard

     Golden Gate 

• Golden Gate

       RAC & ASM 

• RAC & ASM

interview Question: Wait Events

 1.) wait event related to wrong password?
      "failed long delay",  'Library cache lock' or 'row cache lock' can be observed when concurrent users         login with wrong password to the database. The 'row cache lock' is seen in 10.2 and 11.1 while the         'library cache lock' is seen in 11.2.

2.) if you run a procedure ( infinite loop) in one session,and try to compile and modify ,which wait event you may see?
      if you try and compile infinite_loop in another session,it'll hand,stuck on "Library cache pin";
      if ,yet in another session,you try and created a modified version of it,it'll hand on "library cahch lock".

3.) How Library cache lock and pins are handled in RAC?
       things are different in RAC. Library cache lock and pins are global resources and controlled by GES           layer .

Oracle Wait Events

The occurrence of a wait event is not important.The *impact* of a wait event (or CPU) is what matters.

library cache: mutex X
=> related to concurrency. Processes compete for shared resources in the library cache, and to control who has access at any given point in time, we use mutexes, which are like lightweight latches, ie, structures to ensure that two people (processes) are not changing the same memory structure at the same time.

log file switch (checkpoint incomplete)
=> we wanted to reuse a redo log file but the underlying checkpoint to ensure all the database changes backed by that redo log was flushed to disk.

resmgr:cpu quantum
=> resource manager throttled back some process or processes because they were consuming excessive CPU.

We can't really deduce much without the full report, but typically, when we start seeing 'library cache: mutex X' and 'resmgr:cpu quantum' together, it often means the box is under CPU pressure, often from excessive parsing.

 

Library cache lock

This lock is also obtained as part of the operation to locate an object in the library cache (a library cache child latch is obtained to scan a list of handles, then the lock is placed on the handle once the object has been found).