Performance Engineering Interview Questions #5

Following are the Performance Engineering Interview Questions Set #5.

Q. 21 What is PGA?

Ans: Program Global Area or PGA is a non-shared memory area used for the server process. PGA is only read and written by Oracle code acting on behalf of that process and used for operations like sorting and hashing. There is one PGA for each server process.

Q. 22 What is SGA?

Ans: System Global Area or SGA is a shared memory region used by all server processes containing structures like buffer cache, shared pool and redo log buffer. It contains data and control information for one Oracle database instance. Also, multiple users can access the data in the SGA simultaneously.

Q. 23 What are the tables available in AWR to get the information about PGA metrics?

Ans. Following are the tables available in AWR:

PGA Aggr Summary: PGA cache hit %
PGA Aggr Target Stats: It provides information about work area memory target in the beginning and ending of the snapshot. It gives insights into PGA memory usage, including total PGA allocated and in-use statistics.
PGA Memory Advisory

Q. 24 How does the ‘pga_aggregate_target value’ setting affect database performance?

Ans: The ‘pga_aggregate_target’ value defines the PGA memory available to all the server processes. Appropriate sizing of pga_aggregate_target value ensures efficient sorting and hashing operations, reducing disk I/O and improving query performance.

Q. 25 What does a high ‘Estd PGA Overalloc Count’ in the ‘PGA Memory Advisory’ table indicate?

Ans: A high value of ‘Estd PGA Overalloc Count’ indicates that the PGA usage has exceeded the PGA aggregate target multiple times, leading to potential performance issues due to insufficient memory for optimal processing.