New Features in performance tuning of Oracle Database 12c release 12.1.0.1

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Apr 11, 2017

New Features in performance tuning of Oracle Database 12c release 12.1.0.1

Real-Time Automatic Database Diagnostic Monitor (ADDM): Real-Time ADDM helps us to analyze and resolve problems in hung databases without having it to restart. In OEM 12c helps us to analyze and resolve problems.

Introduction: In this two part blog series, I will cover the new features/updates in performance tuning of Oracle databases 12.1.0.1 and 12.1.0.2. In this blog, part 1, I will cover Oracle database 12.1.0.1. 

New Features in Oracle Database 12.1.01:

The following features are new: 

  • Real-Time Automatic Database Diagnostic Monitor (ADDM)
  • Limiting the size of the Program Global Area (PGA)
  • Active Session History (ASH) Enhancements
  1. A) Real-Time Automatic Database Diagnostic Monitor(ADDM): Real-Time ADDM helps us to analyze and resolve problems in hung databases without having it to restart. In OEM 12c helps us to analyze and resolve problems. 

Let’s get started on the following:

  • Real-Time ADDM Connection Modes
  • Real-Time ADDM Triggers
  • Real-Time ADDM Trigger Controls 

Real-Time ADDM Connection Modes:

Real-Time ADDM uses two different types of connection modes when connecting to the database using OEM, depending on the database state: 

  • Normal Connection

In this mode, Real-Time ADDM performs a normal Java Database Connectivity (JDBC) to the database. This mode performs an extensive performance analysis of the database when some connectivity is available. 

  • Diagnostic Connection

In this mode, Real-Time ADDM performs a latch-less connection to the database. This mode is used in for extreme hang situations when a normal JDBC connection is not possible. 

Real-Time ADDM Triggers

Real-Time ADDM runs automatically every three seconds and uses in-memory data to diagnose any performance issues in the database. Real-Time ADDM triggers an analysis automatically when a performance problem is detected: 

  1. Every three seconds, the manageability monitor process (MMON) performs an action to obtain performance statistics without lock or latch. 
  1. The MMON process checks these statistics and triggers a Real-Time ADDM analysis if any of the issues listed in Table A.1 are found. 
  1. The MMON slave process creates the report and stores it in the AWR. For more details, we can check the DBA_HIST_REPORTS view and DBA_HIST_REPORTS_DETAILS view.

To manually generate a report we can use the following procedure:

SQL> select dbms_addm.real_time_addm_report() from dual; 

Table A.1 lists the issues and conditions that trigger a Real-Time ADDM analysis.

Issue

Condition

High load

Average active sessions are greater than three times the number of CPU cores

I/O bound

I/O impact on active sessions based on single block read performance

CPU bound

Active sessions are greater than 10% of total load and CPU utilization is greater than 50%

Over-allocated memory

Memory allocations are over 95% of physical memory

Interconnect bound

Based on single block interconnect transfer time

Session limit

Session limit is close to 100%

Process limit

Process limit is close to 100%

Hung session

Hung sessions are greater than 10% of total sessions

Deadlock detected

Any deadlock is detected

Real-Time ADDM Trigger Controls

To make sure that the automatic triggers do not consume too many system resources, Real-Time ADDM deploys the following controls: 

  • Duration Between Reports

If a Real-Time ADDM report was created in the past five minutes by the automatic trigger, then no new reports will be generated. 

  • Oracle RAC Control

Automatic triggers are local to the database instance. For Oracle RAC, only one database instance can create a Real-Time ADDM report at a given time. 

  • Repeated Triggers

An automatic trigger for any issue must have an impact of 100% or higher than the previous report with the same triggering issue within the past 45 minutes. 

  • Newly Identified Issues

If a new issue is detected which was not previously detected within the past 45 minutes, then a new report is generated. 

  1. B) Limiting the size of the Program Global Area (PGA)

Excessive PGA usage can lead to high rates of swapping. When this occurs, the system may become unresponsive and unstable. In this case, we should consider using the PGA_AGGREGATE_LIMIT initialization parameter to limit overall PGA usage. 

Below we will find an explanation of how to limit the size of the PGA using the PGA_AGGREGATE_LIMIT initialization parameter in Oracle Database 12c R1 (12.1.0.1) and higher, using the outlined sub-points: 

  • About PGA_AGGREGATE_LIMIT
  • Setting PGA_AGGREGATE_LIMIT 

About PGA_AGGREGATE_LIMIT

If the PGA_AGGREGATE_LIMIT value is exceeded, Oracle Database aborts or terminates the sessions or processes that are consuming the most untunable PGA memory in the following order: 

  • Calls for sessions that are consuming the most untunable PGA memory and aborts them.
  • If PGA memory usage is still over the PGA_AGGREGATE_LIMIT, then the sessions and processes that are consuming the most untunable PGA memory are terminated. 

Oracle Database treats parallel queries as a single unit. By default, the PGA_AGGREGATE_LIMIT parameter is set to the greater of 2 GB, 200% of the PGA_AGGREGATE_TARGET value, or 3 MB times the value of the PROCESSES parameter. However, it will not exceed 120% of the physical memory size minus the total SGA size. 

Setting PGA_AGGREGATE_LIMIT

The PGA_AGGREGATE_LIMIT initialization parameter can be set dynamically; a database restart is not necessary. We can set the value of PGA_AGGREGATE_LIMIT regardless of whether automatic memory management is being used. 

To set PGA_AGGREGATE_LIMIT:

Set the PGA_AGGREGATE_LIMIT initialization parameter to a desired value in number of bytes. Setting the value to 0 disables the hard limit on PGA memory. 

Actions Taken When PGA_AGGREGATE_LIMIT is exceeded:

First, the sessions that are using the most untunable memory will have their calls aborted. Then, if the total PGA memory usage is still over the limit, the sessions that are using the most untunable memory will be terminated. 

SYS processes and background processes other than job queue processes will not be under any of the actions. Instead, if they are using the most untunable memory, they will write a brief summary of their PGA usage to a trace file periodically.

  1. C) Active Session History(ASH) Enhancements
  • The ASH data visualization has changed in Oracle 12c, In EM we can now access a new pages called “ASH Analytics“. It is now possible to drilldown into logical dimensions. In addition, we can now send these Active Reports to users who can view them offline.

    oracle data visualization

    Picture Source URL: http://www.dba-scripts.com/articles/ocp-flashcards/ocp-12c-emergency-monitoring-real-time-addm/

    Conclusion: These new features collectively will help us to troubleshoot and enhance the database performance effectively for 12cR1 versions. In my next blog, I will explain new features and changes in performance tuning for Oracle Database 12.1.0.2. For any questions on the topic click below:

    Ask Virat