1. List components of an Oracle instance?
An Oracle instance is comprised of memory structures and background processes.
The Systems Global Area (SGA) and shared pool are memory structures. The process monitor is a background process (DBWn, LGWR, ARCn, PMON, any others??). The Oracle database consists of the physical components such as data files, redo log files, and the control file.
2. Which background process and associated database component guarantees that committed data is saved even when the changes have not been recorded in the data files?
LGWR (log writer) and online redo log files. The log writer process writes data to the buffers when a transaction is committed. LGWR writes to the redo log files in the order of events (sequential order) in case of a failure.
3. What is the maximum number of database writer processes allowed in an Oracle instance?
The maximum is ten. Every Oracle instance begins with only one database writer process, DBW0. Additional writer processes may be started by setting the initialization parameter DB_WRITER_PROCESSES (DBW1 through DBW9).
4. Which background process is not started by default when you start up the Oracle instance?
ARCn. The ARCn process is available only when the archive log is running (LOG_ARCHIVE_START initialization parameter set to true). DBWn, LGWR, CKPT, SMON, and PMON are the default processes associated with all instances (start by default).
5. Describe a parallel server configuration.
In a parallel server configuration multiple instances known as nodes can mount one database. In other words, the parallel server option lets you mount the same database for multiple instances. In a multithreaded configuration, one shared server process takes requests from multiple user processes.
6. Choose the right hierarchy, from largest to smallest, from this list of logical database structures.
Database, tablespace, segment, extent, block.
7. Which component of the SGA contains the parsed SQL code?
The library cache contains the parsed SQL code. During parsing, Oracle allocates a shared SQL area for the statement in the library cache, and stores its parsed representation there. If a query is executed again before it?s aged out of the library cache, Oracle will use the parsed code and execution plan from the library cache.
8. Name the stages of processing a DML statement. What stages are part of processing a query?
When processing a query or select statement, the parsing operation occurs first, followed by the fetch operation and the execute operation. However, when processing a DML statement, the parse operation is conducted as well as the execute operation, but not the fetch operation.
9. Which background process is responsible for writing the dirty buffers to the database files?
The purpose if the DBWn is to write the contents of the dirty buffer to the database file.
This occurs under two circumstances ? when a checkpoint occurs or when the server process searches the buffer cache for a set threshold.
10. Which component in the SGA has the dictionary cache?
The dictionary cache is part of the shared pool. The shared pool also contains the library cache and control structures.
11. When a server process is terminated abnormally, which background process is responsible for releasing the locks held by the user?
The process monitor (PMON) releases the locks on tables and rows held by the user during failed processes and it reclaims all resources held by the user. PMON cleans up after failed user processes.
12. What is a dirty buffer?
A dirty buffer refers to blocks in the database buffer cache that are changed, but are not yet written to the disk.
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?
The block is the minimum amount of information read/copied to the database buffer cache.
14. What happens next when a server process is not able to find enough free buffers to copy the blocks from disk?
To reduce 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 or when there are not enough free buffers available or when the checkpoint occurs.
15. Which memory structures are shared? Name two.
The library cache contains the shared SQL areas, private SQL areas, PL/SQL proceduares, and packages, and control structures. The large pool is an optional area in the SGA.
16. When a SELECT statement is issued, which stage checks the user?s privileges?
Parse checks the user?s privileges, syntax correctness, and the column names against the dictionary. Parse also determines the optional execution plan and finds a shared SQL area for the statement.
17. Which memory structure records all database changes made to the instance?
The redo log files holds information on the changes made to the database data. Changes are made to the database through insert, update, delete, create, alter, or drop commands.
18. What is the minimum number of redo log files required in a database?
The minimum number of redo log files required in a database is two because the LGWR (log writer) process writes to the redo log files in a circular manner.
19. When are the system change numbers assigned?
System changed numbers (SCN) are assigned when a transaction is committed. The SCN is a unique number acting as an internal timestamp, used for recovery and read-consistent queries. In other words, the SCN number is assigned to the rollback statement to mark it as a transaction committed.
20. Name the parts of the database buffer pool.
The database buffer pool consists of the keep buffer pool, recycle buffer pool, and the default buffer pool.
The keep buffer pool retains the data block in memory.
The recycle buffer pool removes the buffers from memory when it?s not needed.
The default buffer pool contains the blocks that are not assigned to the other pools.