In this video, we will list the configuration parametes used for sizing the SGA and PGA
- [Narrator] Let's put some theory into practice. Now that we know what's inside the Oracle SGA and what is the role of the PGA let's talk about how we can size both of them correctly. This is super important. As it's up to the database expert to decide how much memory will be allocated to Oracle and how much memory will be provided for the SGA compared to the PGA. We can control the size of the SGA using two parameters. SGA Target and SGA Max Size.
Based on these parameters Oracle by itself will derive the sizing for other individual SGA components. Such as the Buffer Cache, the Shared Pool, and the Radio Log Buffer. As an example, we can configure an SGA Target for 64 GB and the SGA Max Size for 128 GB and have Oracle dynamically resize the SGA between 64 GB and 128 GB of memory depending on workload characteristics.
On the other hand, when sizing the PGA we set the PGA Aggregate Target parameter to equal the total amount of memory. We want available 40 PGA but across all server processes. Each server process will allocate a subset of this total amount of PGA memory that is available to the user it's serving based on the user workload requirements. Tuning the size of the SGA as well as the PGA is really done based on workload.
The SGA with it's Buffer Cache is usually sized larger compared to the PGA. However when you have a lot of users connecting to a database, a lot of sessions, probably running complex sequel queries relying heavily on sorting data or joining large tables, you might want to increase the size of the PGA accordingly. There is no magical number for SGA or PGA Size. It really depends on the amount of memory you have available in your Oracle database server as well as the workloads your applications generate.
Now, would you like me to make your life as a database administrator just a little bit easier? Well it's your lucky day. We also have another option, where instead of explicitly specifying the SGA and PGA Size as we just described, we can just set a single parameter. We can use Oracles Memory Target parameter and let Oracle automatically partition the memory between the SGA and the PGA.
We provide a value for this parameter as the total amount of memory we want Oracle to use for both the PGA and the SGA. Oracle will automatically and dynamically split the amount of memory available for the SGA and for the PGA based on what is currently running as well as past workloads. We just set a single value based on the amount of memory we have on the server and let Oracle do all of the work in terms of SGA and PGA Sizing.
You may ask which option is best? Manually sizing the SGA and PGA separately or having Oracle divide up the memory all by itself? Well it usually depends on how much fine grain control you want over database memory allocation as well as how well you know your database as well as your applications. Which option is best? Manually sizing the SGA and PGA separately or having Oracle divide up the memory between them all by itself? Well it usually depends on how much fine grain control you want over database memory allocation.
It also depends on how well you know your database and your application. As someone who is just getting started with Oracle it's okay to rely on Oracle for some help. Just set the Memory Target parameter and let Oracle do all the rest. Actually, even some of us veteran DBAs sometimes just want Oracle to do the work for us and configure the memory based on its own understanding of our workloads. You know, it's a smart piece of technology after all.
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