In this video, learn the responsibilities of the Oracle Server Processes and how the handle incoming user connections.
- [Instructor] Are you with me so far? Great. Now, let's divert our attention to the final piece of the Oracle puzzle. The last of the three major components, making up the Oracle database architecture. These are the Oracle SPs, or, in their full name, server processes. Their purpose, simple. Oracle starts up server processes to handle the requests of client processes connected to the Oracle database.
A client process, which is basically a fancy name to call a user session that is connected to the database, always communicates through a server process. Server processes are created on behalf of a session that initiated the connection to the Oracle database. These server processes performs one or more of the following tasks. The first role of the server processes is to pass and execute SQL statements issued by the connecting session.
So when a user issues a select statement, the server process is responsible for making sure the select statement is syntactually correct and find the best way to execute it. The second role for the server process is to act on behalf of a client session that needs to read data from disk. The server process is responsible for reading database data from the database storage and loading that data into the Oracle instance buffer cache.
We will talk more about the buffer cache in our Oracle instance dive chapter later on. For now, all you need to know is that the buffer cache is origin in memory inside the Oracle instance used for caching recently accessed data. Remember, Oracle aims to be a speedy database, and one of the best ways to achieve that is to cache frequently accessed data into memory so that we minimize disk rates. Moving along, the third rule for the server process is to return the results of an executed SQL statement back to the client.
So to summarize, the major rules and responsibilities of the server processes include one, they act on behalf of client sessions to pass SQL statements, two, they read data from disk and cache it into memory, three, they return the results back to the user. Simple, right? It's actually a very scalable approach, having a dedicated server process handle the processing for a specific user.
More users equals more dedicated server processes. In addition to the roles of the server processes we just reviewed, also note that each server process has its own cache dedicated for each connecting client. This cache is also known as the PGA, and stands for the program global area. The PGA used by each server process is a non-shared memory region reserved only for the specific user session connected to that specific server process.
What is the PGA used for? Great question. Generally speaking, it contains data and control information used by the server processes when sorting data, joining large tables together as part of a SQL statement, and so on. Essentially, data that should not persist after a user session has finished doing whatever it needed to do. Temporary data. This is in direct contrast to the various other caches used by the Oracle instance itself, which are shared across all connecting sessions.
In most Oracle deployments, you will usually find a one-to-one relation between connecting user sessions and server processes. So for each user that is connected to the database, you will also find a single server process. This is also known as the Oracle dedicated server process model. Can you think of a problem with this approach? Well this approach might work if you have two, 10, or maybe 100 users connected to a database.
But what happens if you have 100,000 users, a million users that need database access? Having the Oracle database start one million server processes for one million user sessions isn't very efficient and can be very resource-demanding from the Oracle server. The solution? What most database professional choose to do is introduce a connection-pooling middleware somewhere between the actual users and the database itself.
An example for such a middleware is having an application server between the actual user sessions and the database server so that our million users will connect to one or more application servers and these application servers will open a fixed set of database connections, thus translating into a fixed set of database server processes. So in our example here, we have a lot of end users connecting to an application server, but that application server is only opening two database connections, and thus the Oracle database only needs to start up two server processes.
Very very efficient. The application server here is basically acting as a connection pool for the users. It's not super-important for you to fully understand how connection-pooling works at this stage, only that you will know that because of the one-to-one user session to Oracle's server process relation, connection-pooling is definitely something you should explore in case the need arises.
After completing this course, you'll have fundamentals required for installation, configuration, and administration of an Oracle 12c database.
- Database instance and storage
- Instance memory pools
- Instance background processes
- Client connections
- Database storage file types
- Control files and backup files
- Multitenant databases
- Starting and stopping the database
- Installing Oracle 12c software
- Using the developer tools
- Database management