Which three statements are true concerning the Automatic Workload Repository (AWR) in a RAC environment?
A.
Taking an AWR snapshot in a RAC database captures data from all instances that have the database opened.
B.
Taking an AWR snapshot in a RAC database captures data from the local instance only.
C.
Data captured by an AWR is aggregated across all instances before being stored in the database.
D.
The awr_snapshot_time_offset parameter specifies the number of seconds each instance waits after the top of the hour before taking its AWR snapshot.
E.
AWR snapshots are created when control_management_pack_access is set to “none”
I believe A, C and D are correct, but option C I am not sure.
I believe, too, A C D
I think “B”, “C” and “D”
I don’t have one rac instance here right now to test it, but:
Pro Oracle Database 11g RAC on Linux
“You can take a snapshot manually at any time ”’for the particular instance”’ you are connected to using the CREATE_SNAPSHOT procedure at the SQL*Plus prompt, as in this example:
SQL> EXECUTE dbms_workload_repository.create_snapshot;”
Expert Oracle RAC Performance Diagnostics and Tuning
Ok! “A”, “C” and “D” is correct!
Although the only documentary note I found on the subject says that this is “local”, see:
SQL> select instance_number, max(snap_id) as snap_id
from SYS.Dba_Hist_Snapshot
group by instance_number; 2 3
INSTANCE_NUMBER SNAP_ID
————— ———-
1 482
2 482
SQL> EXECUTE dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
SQL> select instance_number, max(snap_id) as snap_id
from SYS.Dba_Hist_Snapshot
group by instance_number; 2 3
INSTANCE_NUMBER SNAP_ID
————— ———-
1 483
2 483
SQL>
But, “C” is wrong, this is for the material of Oracle RAC Course:
AWR automatically generates snapshots of the performance data once every hour and
collects the statistics in the workload repository. In RAC environments, each AWR snapshot
captures data from all active instances within the cluster. The data for each snapshot set that is captured for all active instances is from roughly the same point in time. In addition, the data for each instance is stored separately and is identified with an instance identifier. For example, the buffer busy wait statistic shows the number of buffer waits on each instance. The AWR does not store data that is aggregated from across the entire cluster.
That is, the data is stored for each individual instance.
The statistics snapshots generated by the AWR can be evaluated by producing reports
displaying summary data such as load and cluster profiles based on regular statistics and wait events gathered on each instance.
The AWR functions in a similar way as Statspack. The difference is that the AWR
automatically collects and maintains performance statistics for problem detection and self
tuning purposes. Unlike in Statspack, in the AWR, there is only one snapshot_id per
snapshot across instances.
Agree C is wrong
Correct A,D and E
Source
https://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams018.htm
A, D and E
setting control_management_pack_access to none disables metric collection but not snapshots.
To disable snapshot collection you have to run the following
exec dbms_workload_repository.modify_snapshot_settings(interval=>0);
I’ve tested this by setting control_management_pack_access to none and the next day checking dba_hist_snapshot which confirmed hourly snapshots were still being taken.
You rock!, I was sure about A,D and I’ve discarded C and B, giving me as only result E, you confirm that, thanks…
the Documentation dint explain this specific point..
C is Wrong
The AWR does not store data that is aggregated from across the entire cluster. That is, the data is stored for each individual instance.
Ref:- Oracle Database 12c: RAC Administration 7 – 30
Correct Answer is A,D, E