You notice a performance change in your production Oracle 12c database. You want to know
which change caused this performance difference.
Which method or feature should you use?
A.
Compare Period ADDM report
B.
AWR Compare Period report
C.
Active Session History (ASH) report
D.
Taking a new snapshot and comparing it with a preserved snapshot
Explanation:
The awrddrpt.sql report is the Automated Workload Repository Compare Period
Report. The awrddrpt.sql script is located in the $ORACLE_HOME/rdbms/admin directory.
Incorrect:
Not A: Compare Period ADDM
Use this report to perform a high-level comparison of one workload replay to its capture or to
another replay of the same capture. Only workload replays that contain at least 5 minutes of
database time can be compared using this report.
The Compare Period ADDM, as opposed to the former method, performs a cause-to-effect
analysis.
1. It first identifies the system changes that may have caused the performance change. For
example, it detects a configuration change in DB version, or a workload change with SQL
changes. These causes can cause performance difference.
I think A
The Compare Period ADDM, compared to the AWR Compare Periods report, performs a cause-to-effect analysis.
B
B. AWR Compare Period report
Resolving Performance Degradation Over Time Performance degradation of the database occurs when your database was performing optimally in the past, such as 6 months ago, but has gradually degraded to a point where it becomes noticeable to the users. The Automatic Workload Repository (AWR) Compare Periods report enables you to compare database performance between two periods of time.
While an AWR report shows AWR data between two snapshots (or two points in time), the AWR Compare Periods report shows the difference between two periods (or two AWR reports with a total of four snapshots). Using the AWR Compare Periods report helps you to identify detailed performance attributes and configuration settings that differ between two time periods. The two time periods selected for the AWR Compare Periods report can be of different durations. The report normalizes the statistics by the amount of time spent on the database for each time period and presents statistical data ordered by the largest difference between the periods.
For example, a batch workload that historically completed in the maintenance window between 10:00 p.m. and midnight is currently showing poor performance and completing at 2 a.m. You can generate an AWR Compare Periods report from 10:00 p.m. to midnight on a day when performance was good and from 10:00 a.m. to 2 a.m. on a day when performance was poor. The comparison of these reports should identify configuration settings, workload profile, and statistics that were different in these two time periods. Based on the differences identified, you can diagnose the cause of the performance degradation.
A.
You may have noticed a performance change on a production system and would like to know why, or you may have implemented a change to a production system and want to know the effect of the change, such as increased concurrency waits.
The Compare Period ADDM compares the performance of the database server in two time periods, and returns a report describing the performance changes and the root origin of the changes
http://docs.oracle.com/database/121/TDPPT/tdppt_degrade.htm#TDPPT525
B
A – OK
https://www.youtube.com/watch?v=7Ag5uQcMS1Y
Nice you tube video. Thanks.
A
From the documentation:
Comparing Current System Performance to a Baseline Period
You may have noticed a performance change on a production system and would like to know why, or you may have implemented a change to a production system and want to know the effect of the change, such as increased concurrency waits.
The Compare Period ADDM compares the performance of the database server in two time periods, and returns a report describing the performance changes and the root origin of the changes. The Advisor can analyze any Oracle RDBMS version 10.2.0.4 or later monitored by Cloud Control. The following procedure explains how to initiate a report from the Compare Period ADDM.
Relo is Perfect!
Option A
http://docs.oracle.com/cd/E24628_01/server.121/e55047/tdppt_degrade.htm#TDPPT525
A
A
In addition to letting you compare performance between two time periods, the AWR Compare Periods Reports capability also lets you compare performance between DB Replay capture and replay-and even between two different DB replays.
Regardless of the nature of comparison, you still need to analyze the huge volumes of performance differentials between them. The AWR Compare Period Reports don’t map the root causes to performance changes.
Oracle 12c offers you something that takes you further in your search for the reason for a performance change – the Compare Period ADDM Report. This report performs a cause-to-effect analysis, making it simpler to understand why performance deviated from a base time.
Reference: Oracle 12c New Features – By Sam Alapati
Hence the definite answer is: A (Compare Period ADDM Report)
Looks like A: as per https://docs.oracle.com/database/121/TDPPT/tdppt_degrade.htm for compare addm report they have written:
4.Examine the sections of the report to understand the performance change between the two periods and the cause of the change:
A is perfectly correct. i choose B in my exam and failed it.
All questions from this dumps covers 12c New features exam and DBA skill sets. 85 questions are to be answer in 1Z0-060.
A
A
To find the problem ===> Reporting + Analyzing + Comparing
Oracle Database 12c: New Features for Administrators 15 – 5, Student Guide Vol II
The A·W·R: Compare Periods reports is interesting but still have to perform an analysis on these metrics to find the effects that were mapped to the root causes of the performance degradation or improvement.
Oracle Database 12c: New Features for Administrators 14 – 21, Student Guide Vol I
A·D·D·M: Aalyzes the information collected by the AWR for possible performance problems with Oracle Database, including high-load SQL statements.
Oracle Database 12c: New Features for Administrators 15 – 10, Student Guide Vol I
If you do not want to shutdown the database instance right away, perform Real-Time ADDM deeper analysis. It will collect data from SGA, perform an analysis to provide you with a report of the root causes of the situation with recommendations and possible acriotns.
Oracle Database 12c: New Features for Administrators 15 – 25, Student Guide Vol II
A·S·H: contains the history of recent session activity. Because recording session activity is expensive, ASH samples V$SESSION every second and records the events that the sessions are waiting for. Inactive sessions are not sampled. The sampling facility is very efficient because it directly access the internal database structures.
ASH is designed as a rolling buffer in memory; earlier information is overwritten when needed. The ASH statistics are available through the V$ACTIVE_SESSION_HISTORY view.
A is the best.
Excellent Summary!!
How do adaptive cursor sharing and SQL Plan Management interact
may be B
https://blogs.oracle.com/optimizer/entry/how_do_adaptive_cursor_sharing
sorry, this was meant to be for previous question.
for this A looks ok
A makes sense
A – ADDM Report