Performance Engineering Interview Questions #11

Following are the Performance Engineering Interview Questions Set #11.

Q. 51 In the AWR report, where can you find information about PGA memory usage?

Ans: The “Memory Statistics” section, specifically under “PGA Aggr Target Stats,” provides insights into PGA memory usage, including total PGA allocated and in-use statistics.

Q. 52: What does a high ‘PGA Memory Over-Allocation Count’ indicate?

Ans: It suggests that the PGA usage has exceeded the PGA aggregate target multiple times, leading to potential performance issues due to insufficient memory for optimal processing.

Q. 53 How can you determine the appropriate SGA size using an AWR report?

A: Check the ‘Instance Efficiency Percentages’ and ‘Memory Statistics’ sections. High values in ‘Buffer Cache Hit Ratio’ and ‘Library Cache Hit Ratio’ generally indicate efficient SGA sizing.

Refer to read more about Buffer Cache Hit Ratio and Instance Efficiency Percentage.

Q. 54 What is the impact of an undersized shared pool in the SGA?

Ans: An undersized shared pool can lead to increased hard parses, library cache misses, and fragmentation, resulting in higher CPU usage and slower query performance.

Q. 55 How can you detect shared pool contention in an AWR report?

Ans: Look for wait events like ‘library cache lock’ or ‘library cache pin’ and high values in ‘Parse Time Elapsed’ relative to ‘Parse CPU Time’ in the ‘Time Model Statistics’ section.