Category Archives: OCP FlashCards

OCP 12C – ADR and Network Enhancements

ADR enhancements

  • In oracle 12c the Automatic Diagnostic Repository contains a new log directory 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.
  • The DDL logging feature is part of 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 – ADR and Network Enhancements

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
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

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

Continue reading OCP 12C – SQL Tuning