Fundamentals I Ch2 - Oracle Overview and Architecture

Chapter 2 - Oracle Overview and Architecture

OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla


Review Questions


1. Which component is not part of the Oracle instance?


A. System Global Area
B. Process monitor
C. Control file
D. Shared pool
---
The Oracle instance consists of memory structures and background processes. The Oracle database consists of the physical components such as data files, redo log files, and the control file. The System Global Area and shared pool are memory structures. The process monitor is a background process.
Ans: C.


2. Which background process and associated database component guarantee that committed data is saved even when the changes have not been recorded in the data files?

A. DBWn and database buffer cache
B. LGWR and online redo log file
C. CKPT and control file
D. DBWn and archived redo log file

----
The LGWR process writes the redo log buffer entries when a COMMIT occurs. The redo log buffer holds information on the changes made to the database. The DBWn process writes dirty buffers to the data file, but it is independent of the COMMIT. The dirty buffers can be written to the disk before or after a COMMIT. Writing the committed changes to the online redo log file ensures that the changes are never lost in case of a failure.
Ans: B



3. What is the maximum number of database writer processes allowed in an Oracle instance?


A. 1
B. 10
C. 256
D. Limit specified by an operating system parameter

----
By default, every Oracle instance has one database writer process: DBW0.
Additional processes can be started by setting the initialization parameter DB_WRITER_PROCESSES(DBW1 through DBW9).
Ans: B.


4. Which background process is not started by default when you start up the Oracle instance?


A. DBWn

B. LGWR

C. CKPT

D. ARCn


5. Which of the following best describes a Real Application Cluster configuration?


A. One database, multiple instances
B. One instance, multiple databases
C. Multiple databases on multiple servers
D. Shared server process takes care of multiple user processes


6. Choose the correct hierarchy, from largest to smallest, from this list of logical database structures.

A. Database, tablespace, extent, segment, block
B. Database, tablespace, segment, extent, block
C. Database, segment, tablespace, extent, block
D. Database, extent, tablespace, segment, block


7. Which component of the SGA contains the parsed SQL code?


A. Buffer cache
B. Dictionary cache
C. Library cache
D. Parse cache


8. Julie, one of the database analysts, is complaining that her queries are taking longer and longer to complete, although they seem to produce the correct results. The DBA suspects that the buffer cache is not sized correctly and is causing delays due to data blocks not being available in memory. Which initialization parameter should the DBA use to monitor the usage of the buffer cache?


A. BUFFER_POOL_ADVICE
B. DB_CACHE_ADVICE
C. DB_CACHE_SIZE
D. SHARED_POOL_SIZE
----
The parameter DB_CACHE_ADVICE can be set to YES to enable cache usage monitoring. DB_CACHE_SIZE and SHARED_POOL_SIZE are sizing parameters for SGA structures; the parameter BUFFER_POOL_ ADVICE does not exist.
Ans: B.


9. Which background process is responsible for writing the dirty buffers to the database files?


A. DBWn
B. SMON
C. LGWR
D. CKPT
E. PMON


10. Which component in the SGA has the dictionary cache?


A. Buffer cache
B. Library cache
C. Shared pool
D. Program Global Area
E. Large pool


11. When a server process is terminated abnormally, which background process is responsible for releasing the locks held by the user?


A. DBWn
B. LGWR
C. SMON
D. PMON

---
D. PMON, or the process monitor, is responsible for cleaning up failed user processes. It reclaims all resources held by the user and releases all locks on tables and rows held by the user.
Ans: D


12. What is a dirty buffer?


A. Data buffer that is being accessed
B. Data buffer that is changed but is not written to the disk
C. Data buffer that is free
D. Data buffer that is changed and written to the disk
---
Dirty buffers are the buffer blocks that need to be written to the data files. The data in these buffers has changed and is not yet written to the disk. A block waiting to be written to disk is on the dirty list and cannot be overwritten.
Ans: B


13. If you are updating one row in a table using the ROWID in the WHERE clause (assume that the row is not already in the buffer cache), what will be the minimum amount of information read to the database buffer cache?


A. The entire table is copied to the database buffer cache.
B. The extent is copied to the database buffer cache.
C. The block is copied to the database buffer cache.
D. The row is copied to the database buffer cache.
---
The block is the smallest unit that can be copied to the buffer cache.
Ans: C.


14. What happens next when a server process is not able to find enough free buffers to copy the blocks from disk?


A. Signals the CKPT process to clean up the dirty buffers
B. Signals the SMON process to clean up the dirty buffers
C. Signals the CKPT process to initiate a checkpoint
D. Signals the DBWn process to write the dirty buffers to disk
---
To reduce disk I/O contention, the DBWn process does not write the changed buffers immediately to the disk. They are written only when the dirty buffers reach a threshold, when there are not enough free buffers available, or when the checkpoint occurs.
Ans: D.


15. Which memory structures are shared? Choose two.


A. Sort area
B. Program Global Area
C. Library cache
D. Large pool
---
The sort area is allocated to the server process as part of the PGA. The PGA is allocated when the server process starts and is de- allocated when the server process completes. The library cache and the large pool are part of the SGA and are shared. The SGA is created when the instance starts.
Ans: C and D.


16. Which of the following initialization parameters does NOT determine the size of the buffer cache?


A. DB_KEEP_CACHE_SIZE
B. DB_CACHE_SIZE
C. DB_BLOCK_SIZE
D. DB_RECYCLE_CACHE_SIZE
----
The parameter DB_BLOCK_SIZE does not change the size of the buffer cache. It changes only the size of each Oracle block written to and read from disk.
Ans: C.


17. Which memory structure records all database changes made to the instance?


A. Database buffer
B. Dictionary cache
C. Redo log buffer
D. Library cache
-----
The redo log buffer keeps track of all changes made to the database before writing them to the redo log files. The database buffer contains the data blocks that are read from the data files, and are most recently used. The dictionary cache holds the most recently used data dictionary information. The library cache holds the parsed SQL statements and PL/SQL code.
Ans: C.


18. What is the minimum number of online redo log files required in a database?


A. One
B. Two
C. Four
D. Zero


19. When are the system change numbers assigned?


A. When a transaction begins
B. When a transaction ends abnormally
C. When a checkpoint occurs
D. When a COMMIT is issued
----
A system change number (SCN) is assigned when the transaction is committed. The SCN is a unique number acting as an internal time- stamp, used for recovery and read-consistent queries.
Ans: D.


20. Which of the following is not part of the database buffer pool?


A. KEEP
B. RECYCLE
C. LIBRARY
D. DEFAULT
----
There is no database buffer cache named LIBRARY. The DBA can configure multiple buffer pools by using the appropriate initialization parameters for performance improvements. The KEEP buffer pool retains the data blocks in memory; they are not aged out. The RECYCLE buffer pool removes the buffers from memory as soon as they are not needed. The DEFAULT buffer pool contains the blocks that are not assigned to the other pools.
Ans: C


21. Memory granules are not allocated at instance startup for which of the following SGA components?


A. Database buffer cache
B. Shared pool
C. Redo log buffers
D. Large pool
E. None of the above
----
All of these SGA components are allocated in granule units. A minimum of three granules are allocated for the SGA at instance startup: one for the fixed portion of the SGA, one for the database buffer cache, and one for the shared pool.
Ans: E.

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

Other Answers


4. D. ARCn is the archiver process, which is started only when the LOG_ARCHIVE_START initialization parameter is set to TRUE. DBWn, LGWR, CKPT, SMON, and PMON are the default processes associated with all instances.

5. A. In a Real Application Cluster configuration, multiple instances (known as nodes) can mount one database. One instance can be associated with only one database. In a multithreaded configuration, one shared server process takes requests from multiple user processes.

6. B. The first level of logical database structure is the tablespace. A tablespace may have segments, segments have one or more extents, and extents have one or more contiguous blocks.

7. C. The library cache contains the parsed SQL code. If a query is executed again before it is aged out of the library cache, Oracle will use the parsed code and execution plan from the library cache. The buffer cache has data blocks that are cached. The dictionary cache caches data dictionary information. There is no SGA component named parse cache.

9. A. The DBWn process writes the dirty buffers to the data files under two circumstances—when a checkpoint occurs or when the server process searches the buffer cache for a set threshold.

10. C. The shared pool has three components: the library cache, the dictionary cache, and the control structures.

18. B. There should be at least two redo log files in a database. The LGWR process writes to the redo log files in a circular manner, so there should be at least two files.