Arcitecture-2 of Oracle Database - Best Interview Q & A for all

2. Architecture - 2

1. What is the basic element of Base configuration of an oracle Database?

It consists of one or more data files. One or more control files. Two or more redo log  groups. The Database contains multiple users/schemas one or more rollback segments
one or more tablespaces Data dictionary tables User objects (table,indexes,views etc.,) The server that access the database consists of SGA (Database buffer cache,Shared pool ,Redo log buffer)

SMON (System monitor)
PMON (Process MONitor)
LGWR (LoG Write)
DBWR (Data Base Write)
ARCH (ARCHiver)
CKPT (Check Point)
RECO
Dispatcher
User Process with associated PGA.

2. What is the function of Optimizer?
The goal of the optimizer is to choose the most efficient way to execute a SQL statement.

3. What is Execution Plan?
The combination of the steps the optimizer chooses to execute a statement is called an execution plan.

4. What are the different approaches used by Optimizer in choosing an execution plan?

Rule-based and Cost-based.

5. What are the different Components of SGA?

1. The fixed SGA (Fixed SGA)
2. Default block buffer (Db cache)
3. Different Standard Block buffers
4. Redo log buffer (Redo log buffer)
5. Java pool (Java pool)
6. Large pool (Large pool)
7. Shared pool (Shared pool)
8. Stream pool (Stream pool)
9. Result cache

6. What is dictionary cache?

The dictionary cache stores “metadata” (data about your tables and indexes) and it’s also known as the row cache. It is used to cache data dictionary related information in RAM for quick access. The dictionary cache is like the buffer cache, except it’s for Oracle data dictionary information instead of user information.

7. What is Database Buffers?

 Database buffers are cache in the SGA used to hold the data blocks that are read from the data    segments in the database such as tables, indexes and clusters DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size

8. What is the functionality of SYSTEM table space?
System tablespace is a main part of oracle database. All the database information is in it.it is created when database is created.It is managed by oracle server . dba cannot change its contents. it contains the data dictionary objects .

9. What is the function of checkpoint (CKPT)?
Checkpoint is a background process which ensures dbwn process has written data to datafiles and updates control file and datafile header to establish data consistency. The CKPT is also useful to get the point in time from where to begin the recovery in case of failure.

10. When does LGWR write to the database?

Log Writer (LGWR) writes redo log entries. it is generated in the redo log buffer of the SGA to on-line Redo Log File.LGWR   writes  redo  log  entries  into  an  on-line  redo  log  file  when transactions commit and the log buffer files are full.

11. What is Shared SQL Area ?

 A shared SQL area contains the parse tree and execution plan for a given SQL statement. Oracle saves memory by using one shared SQL area for SQL statements run multiple times, which often happens when many users run the same application.Oracle allocates memory from the shared pool when a new SQL statement is parsed, to store in the shared SQL area. The size of this memory depends on the complexity of the statement. If the entire shared pool has already been allocated, Oracle can de-allocate items from the pool using a modified LRU (least recently used) algorithm until there is enough free space for the new statement's shared SQL area.

12. What Does DBWR do?
 Database writer writes modified blocks from the database buffer cache to the data files.

13. What is server processes?
 A server process is one that handles user requests. When you type in a SQL statement, the server process handles the parsing and running of that SQL statement,

14. Name the process which carries the request to the memory components,And also fetches from disk to buffer?
  Server Process