OCP 12C – ADR and Network Enhancements

ADR enhancements

  • In oracle 12c the Automatic Diagnostic Repository contains a new log directories with 2 subdirectories :
    • DDL
    • Debug

The DDL log

  • When you active the DDL logging in Oracle 12c using enable_ddl_logging=true, Oracle writes all DDL operations into the specific DDL log instead of writting it to the alert log.
  • To enable DDL logging you need the Oracle Change Management Pack.

The Debug Log

  • The debug log contains unusual events which are not sufficiently critical to be recorded as incidents.
  • This information is not destinated directly to the DBA and is mainly created to help the Oracle Support in case of problem.

Continue reading


OCP 12C – Emergency Monitoring, Real-Time ADDM

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

  •  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 – SQL Tuning

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 hystograms
  • Finally there are significant improvements on SQL Plan Management.

Continue reading

OCP 12C – Real-Time Database Operation Monitoring

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 or running concurrently in multiple sessions.
  • There are two types of database operations :
    • Simple : A simple database operation is on SQL or PL/SQL statement.
    • Composite : A Composite database operation is consists in multiple SQL or PL/SQL statement running in a single session or multiple concurrent sessions (Application pool for example).

Continue reading