Architecture - 2

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 upadates 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 deallocate 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

Questions & Answers

1. What is the function of SMON in instance recovery?

A.It writes data to the archive log files.

B.It writes data to the online redo log files.

C.It frees resources held by user processes.

D.It synchronizes data file header and control files.

E.It roles forward by applying changes in the redo log.

F.It writes dirty buffers from the buffer cache to the data files.

correct answer: e

2. Which action occur during a checkpoint?

A.Oracle updates a control file.

B.Oracle performs a delayed block clean-out.

C.Oracle copies the online redo-log to the archive destinations. `

D.Oracle flushes the dirty blocks in the database buffer cache to disk.

correct answer: d

3. SMON process is used to write into LOG files

a.TRUE

b.FALSE

correct answer: b

4. Oracle does not consider a transaction committed until

A. The Data is written back to the disk by DBWR

B. The LGWR successfully writes the changes to redo

C. PMON Process commits the process changes

D. SMON Process Writes the data

correct answer: b

5. How many maximum DBWn(Db writers) we can invoke?

A. 3

B. 1

C. 10

D. 20

correct answer: d

6. Which activity would generate less undo data?

  

A.INSERT

B.UPDATE

C.SELECT

D.DELETE

correct answer: a

7. What happens when a user issues a COMMIT?

A. The CKPT process signals a checkpoint.

B. The DBWn process wrtes the transactions changed buffers to the datafiles.

C. The LGWR flushes the log buffer to the online redo log.

D. The ARCn process writes the change vectors to the archive redo log

correct answer: c

8.What happens when a user process fails?

    A.PMON performs process recovery.

    B.SMON performs ICR

    C.SMON frees resources held by user process.

    D.Oracle Updates a controlfile.

Answer:a

9.What are the free buffers in the database buffer cache.

    A.Buffer that have changed should be pushed to the disk.

    B.Buffers that are currently in use.

    C.Buffer that are being written to disk.

    D.Buffer that can be overwritten.

Answer:d

10.When thw SMON Procees perform ICR.

      A.For every startup

      B.Only at the time of startup after graceful shutdown

      C.Only at the time of startup after abort shutdown

Answer:c