I am making a practice for school where one of the questions is if changing the SGA/PGA size would affect the queries performance and in what sense. I have been looking around for how to do that and found some ideas, but anytime I do it, I barely note any changes in the performance. So, does it affect in any way? How?
Thanks!
Best Answer
I'll outline SGA and PGA, and then will try to give you a couple of appropriate examples.
SGA consists of many structures, shared by many processes (both background and foreground). Just a few of them:
If you undersize, for example, buffer cache, the queries will perform slower. Buffer cache is the area of memory used to hold the blocks of data read from and written to data files. Whenever you query a table, data is read from the data files in the units called blocks. The requested data is not returned straight to the user process, instead it is copied to the buffer cache, and then is read from the buffers in the buffer cache if other queries request the same data. Undersized buffer cache leads to lots of I/O and thus adversely impacts performance.
PGA, on the other hand, is the area of memory private to one process. It cannot be shared shared by processes. It is used to maintain the process state. One of the parts of PGA is sort area. It is used to sort a set of rows. In the best case the sort is performed in memory, but if the PGA (sort area in particular) is undersized, the sorting involves the temporary tablespace segments residing on disk. This makes the queries perform slower because disk is one of the bottlenecks in the computer systems.
For more info read "Overview of the Program Global Area" and "Overview of the System Global Area" in Concepts.