Essential Oracle System Architecture Interview Questions & Answers:
Please share your answers.
2. Explain what is Shared SQL Area?
Shared SQL areas are shared memory areas; any Oracle process can use a shared SQL area. The use of shared SQL areas reduces memory usage on the database server, thereby increasing system throughput. Shared SQL areas are aged out of the shared pool by way of a least recently used algorithm (similar to database buffers). To improve performance and prevent reparsing, you may want to prevent large SQL areas from aging out of the shared pool
3. Explain what do Redo Log Buffers contain?
Redo log buffers consists of change vectors where each vector contains information about which block is changed and where is the change done in the memory. This information will be useful when you are performing recovery and while writing to disk while checkpointing. There is a 1-to-1 correspondence with redo log buffers and data buffer cache buffers.
4. What is Cursor in Oracle System Architecture?
A cursor can be viewed as a "Pointer" into the result table of an SQL SELECT statement. Cursors are needed whenever the result contains more than one row. 3rd generation programs like COBOL and C cannot handle more than one row at a time. SQL produces a set-at-a-time. The cursor allows the set to be processed one row-at-a-time, just like a sequential file. COBOL and C are happy, SQL is happy. I'm happy.
5. Tell me when does LGWR write to the database?
LGWr writes when:
1)at commit
2)when 1/3rd full
3)when there is 1mb of redo
4)every 3 seconds
5)before DBWn writes.
6. What is Shared Pool in Oracle System Architecture?
Actually Shared pool is composed of two parts one is Library Cache and another Data Dictionary cache The Library cache contains the shared sql areas, private sql areas, PL/SQL procedures and packages, and control structures such as locks and Library cache handles, The shared sql area contains the parse tree and execution plan where as the private sql area contains values for bind variables and runtime buffers.the data dictionary cache holds most recently used database dictionary informations.
7. What is SGA in Oracle System Architecture?
System Global Area (SGA) is component of instance which consist shared memory & control information of the instance It consist of shared pool, database buffer cached, redo log buffer, large pool, Java pool.
8. Explain when Does DBWR write to the database?
DBWn writes when:
1)checkpoint occurs
2)dirty buffers reach threshold
3)there are no free buffers
4)timeout occurs
5)RAC ping request is made
6)when any tablespace is taken offline,read only,drop or truncated & when begin backup
9. What is the function of checkpoint (CKPT)?
The checkpoint (CKPT) process frees up limited memory space and ensures instance recovery. It is a background process that monitors the number of dirty buffers that have not been written to disk. Depending on the LOG_CHECKPOINT_INTERVAL parameter, it also gives the DBWR a wakeup call to write dirty buffer to disk. At a specified time, it updates all the data and control files with the new log file control number. This process is optionally enabled if parameter CHECKPOINT_PROCESS contains a TRUE value
10. Tell me what does LGWR do?
LGWR is oracle background process.. all changes which have been made inthe database gets recorded in the redolog buffer.. so when the users fires an commit statement , LGWR writes these changes it into thr redolog files. and also writes it into files when the redolog buffer is one thrid full... and also when buffer is nealy 1mb full lgwr writes it into redologfiles.
https://InterviewQuestionsAnswers.ORG.