Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
Your question may be overly broad for this forum, have you read dba.stackexchange.com/faq –  James Jenkins May 30 '13 at 13:06
add comment

1 Answer 1

up vote 2 down vote accepted

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:

  • shared pool
  • buffer cache
  • log buffer

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.

share|improve this answer
    
That is perfect! Thanks you very much! –  makeMonday May 30 '13 at 12:52
    
Why accepting the answer so fast? There may be other interesting answers given. I would just vote up, and when a number of good answers is posted, accept the best one later ;-) –  Yasir Arsanukaev May 30 '13 at 12:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.