Emergency Monitoring

• Emergency monitoring is meant for extreme circumstances where it’s impossible for you to connect to the database because the database is hung.
• Emergency monitoring allows you to connect to the database in diagnostic mode and run a lightweight analysis to see what’s happening.
• You can access real-time performance data from ASH and access the hang analysis data.
• Unlike in 11g, you don’t have to explicitly activate the memory access mode, emergency monitoring connects directly to the SGA to retrieve the necessary data.

Real-Time ADDM

• Real-Time ADDM unlike Emergency Monitoring is used to analyze the root cause of the hung.
• Real-Time ADDM works like the normal ADDM but access only the last 10 minutes of ASH data to provide recommendations.
• Because Real-Time ADDM only uses data from memory you can still access it when the database is hung.
• You can use Real-Time ADDM triggers to proactively monitor the performance of an instance.
  • High load
  • I/O bound
  • CPU bound
  • Over-allocated memory
  • Interconnect bound
  • Session limit
  • Process limit
  • Hung session
  • Deadlock detected
• Reports can be viewed in DBA_HIST_REPORTS and DBA_HIST_REPORTS_DETAILS.
• To manually generate a report you can use the following procedure

SQL> select dbms_addm.real_time_addm_report() from dual;

•  You can also generate an ADDM report using the EM Database Express by going to the performance hub et click on “Current ADDM findings“.

Continue reading OCP 12C – Emergency Monitoring, Real-Time ADDM →

What’s new ?

• Oracle 12c introduces a major update called Adaptive Query Optimization which is based on :
  • Adaptive execution plans
  • Adaptive Statistics
• These two functionnalities are used to improve execution plans by using dynamic statistics gathered during the first part of the SQL execution. This allow to create more efficient plans that those using only the existing statistics which could be inaccurate or insufficient.
• Oracle 12c introduce 2 new types of histograms
• Finally there are significant improvements on SQL Plan Management.

Continue reading OCP 12C – SQL Tuning →

What is Real Time Database Operation Monitoring ?

• Real Time Database Operation Monitoring will help you track the progress of a set of sql statements and let you create a report.
• Real Time Database Operation Monitoring acts as a superset of all monitoring components like : ASH, DBMS_MONITOR …
• You can generate Active Reports which are available offline and don’t need access to the production system once generated.
• Real Time Database Operation Monitoring is part of the tuning pack and subject to license.

What is a Database Operation ?

• A database opration is one or more sql statements running inside a single session.
• There are two types of database operations :
  • Simple : A simple database operation is one SQL or PL/SQL statement.
  • Composite : A Composite database operation consists in multiple SQL or PL/SQL statement running in a single session.

Continue reading OCP 12C – Real-Time Database Operation Monitoring →