PGA

Oracle 12c New Features 08

Srinivas Maddali 

PGA Size Limit

This feature limits the total amount of Program Global Area (PGA) that an instance can allocate, using a parameter called PGA_AGGREGATE_LIMIT. When the instance has allocated the PGA_AGGREGATE_LIMIT amount of PGA, sessions with the highest amount of allocated PGA are stopped until the limit is complied with.

This feature is important for consolidation because, without a hard limit, the instance can become unstable due to excessive paging. Excessive paging is one of the leading causes of instance eviction in an Oracle RAC database and can cause a multitude of performance and stability problems.

Real-Time Database Operations Monitoring

Real-time database operations monitoring allows database administrators to easily monitor and troubleshoot performance problems in long running jobs. This feature helps make long running database operations like a batch job, an ETL (extraction, loading, and transformation) operation, or a scheduler job, transparent so that administrators can see exactly what the operation is doing and at what time. It does this by tracking the SQL and PL/SQL commands that make up a database operation along with their time lines.

As a result, DBAs can easily trace any issues with the database operation to the problem SQL or PL/SQL for more in-depth analysis.

Resource Manager Runaway Query Management

Runaway queries are a persistent problem in databases and can adversely impact overall performance if not managed properly. Resource Manager now provides information about and proactively manages offending queries. There are new views that allow a DBA to see the most recent SQL commands that have hit limits in each consumer group. These views are persisted to the Automatic Workload Repository (AWR) to allow post-mortem analysis. Also, there is new functionality to allow the DBA to take preemptive action on offending SQL execution plans.

The net result is that DBAs can now proactively prevent runaway queries, before they do any damage, rather than being reactive to queries which have already consumed too many resources.

Spot ADDM

Oracle Database has many automatic performance diagnostics advisors such as Automatic Database Monitor (ADDM), real-time ADDM, and compare period ADDM to allow DBAs to diagnose and resolve performance problems. While ADDM reports problems found in the last hour (by default), there are times when a critical problem occurs and DBAs need to be notified right away. Spot ADDM is a new advisor that is automatically triggered when a database begins to encounter performance issues and tries to identify the root cause of the problem. Some of the types of problems that trigger spot ADDM include high CPU load or I/O bound scenarios. The results of spot ADDM are persisted in Automatic Workload Repository (AWR) as reports.

Getting visibility into these problems enables DBAs to respond rapidly and prevent cascading problems that could ultimately create catastrophic failures.