How can you detect the cause of the degraded performance?

You notice that the performance of your production 24×7 Oracle 12c database has
significantly degraded. Sometimes, you are not able to connect to the database instance
because it hangs.
How can you detect the cause of the degraded performance?

A.
by performing emergency monitoring using Real-Time Automatic Database Diagnostic
Monitor (ADDM) to fetch data directly from SGA for analysis

B.
by running ADDM to fetch information from the latest Automatic Workload Repository
(AWR) snapshots

C.
by using Active Session History (ASH) data and performing hang analysis

D.
by running ADDM in diagnostic mode

Show Hint

← Previous question

Next question →

Vonpire

Why not C? Has enterprise manager express a emergency monitor tab? Oracle Cloud Control has It but this is exam for 12 database not 12 cloud control

See here : https://oracle-base.com/articles/12c/emergency-monitoring-em12c

Command Line

You don’t need Cloud Control to perform emergency monitoring. Once connected to the database, you can query views such as V$SESS_TIME_MODEL, V$SESSTAT, V$SQLSTATS, V$ACTIVE_SESSION_HISTORY, DBA_BLOCKERS, DBA_WAITERS etc.

Hang analysis can be performed using oradebug hanganalyze, as described here.

$ sqlplus / as sysdba

SQL> oradebug hanganalyze 3
On versions prior to 11g, you can run hanganalyze from a preliminary connection, which may help if you are trying to connect to a hung database, so a normal connection is not possible.

$ sqlplus -prelim / as sysdba

SQL> oradebug hanganalyze 3
From 11g onward, this method of hanganalyze will still appear to work, but the resulting trace file will include an error message, as documented here.

Tanel Poder discussed a workaround for this here. Use the setospid to connect to an existing process and let that run the hanganalyze for you by including the dump keyword. Thanks to Martin Bach for pointing this out.

$ sqlplus -prelim / as sysdba

SQL> oradebug setospid
SQL> oradebug dump hanganalyze 3

Tri

Thanks Vonpire!

Tri