SERVER/USER PROCESS!

Question 1

USER PROCESS

Answer 1

Is used when a USER runs an application(SQLPLUS) program.

Runs the tool/application considered the CLIENT

Passes SQL to the SERVER PROCESS and receives results.

Question 2

SERVER PROCESS

Answer 2

1. Converts SQL statement into BINARY format ( machine Language)./Parse.
2. It is either DEDICATED or SHARED by USER PROCESS.
3. Places and must place Data in the Database BUFFER cache.
4. Parses and Executes SQL statements.
5. Reads Data Blocks from Disk into the SHARED Database BUFFERS of the SGA.
6. Returns results to USER PROCESS.

Question 3

RULE of THUMB #1

Answer 3

Enable SHARED SERVERS when CONCURRENT USER connections would be greater than 50.

NB:

No PGA with shared SERVERS.

PGA is only with DEDICATED servers.

Question 4

RULE of THUMB #2

Answer 4

When creating a Database, set the SGA to not more than 40% of the SERVER’S MEMORY so as to have room for growth/ future growth of the SGA, PGA, O/S and other processes.

Question 5

BACKGROUND PROCESSES (DBWr)

Answer 5

1. Writes CONTENTS (dirty buffers) to Data_Files.
2. Primary Job is to keep the Oracle Database buffer CLEAN.
3. Writes Least Recently Used (LRU) DIRTY buffers to DISK first.
4. Writes to Data_Files in optimal batch.
5. Only process that writes directly to Data_Files.
6. Mandatory Process.

NB:

***DBWn (n=0-9) Writes to Data_Files when:

a. A Server Process CANNOT find a CLEAN reusable buffer. i.e There are NO FREE buffers/FREE buffers NOT available.

A USER’S SERVER process has searched too long for a FREE buffer when reading a buffer cache.

b. At same Checkpoint.
c. When dirty buffers reach a certain THRESHOLD.

Question 6

CTr (Change Track Writer)

Answer 6

Records info about blocks that have CHANGED since RMAN backup.

Question 7

RvWr (Recovery Writer)

Answer 7

Writes MODIFIED blocks to FLASHBACK logs.

Question 8

LGWr (Log Writer Process)

Answer 8

Writes Redo log buffer to the Redo log file on Disk.

Writes when:

a. A User COMMITS a transaction.
b. Redo log buffer is ONE THIRD full.

COMMIT command - Allows USERS to save transactions that have been made against a Database.

Question 9

SMON

Answer 9

1. Performs Automatic Instance Recovery.
2. Reclaims Space used by temporary Segments no longer in use i.e De-allocated.
3. COALESCES - i.e Put together free spaces for use.
4. Resolves Segment Fragmentation.
5. Primarily CLEANS UP SERVE side.
6. Mandatory Process.[ps -ef | grep smon]

If you don see smon after grep, then it means NO Data on the machine.

Question 10

PMON

Answer 10

1. Performs automatic process recovery.

Cleans up abnormally terminated connections.

Rolls back non committed transactions.

Releases resources held by abnormally terminated transactions.

2. RESTARTS failed Shared Server and Dispatcher Process.
3. Wakes up regularly to check whether it is needed or it may be called directly.
4. Primarily cleans up CLIENT-side failures(USERS).