You upgraded from a previous Oracle database version to Oracle Database version to Oracle Database
12c. Your database supports a mixed workload. During the day, lots of insert, update, and delete operations
are performed. At night, Extract, Transform, Load (ETL) and batch reporting jobs are run. The ETL jobs
perform certain database operations using two or more concurrent sessions.
After the upgrade, you notice that the performance of ETL jobs has degraded. To ascertain the cause of
performance degradation, you want to collect basic statistics such as the level of parallelism, total database
time, and the number of I/O requests for the ETL jobs.
How do you accomplish this?
A.
Examine the Active Session History (ASH) reports for the time period of the ETL or batch reporting runs.
B.
Enable SQL tracing for the queries in the ETL and batch reporting queries and gather diagnostic data
from the trace file.
C.
Enable real-time SQL monitoring for ETL jobs and gather diagnostic data from the V$SQL_MONITOR
view.
D.
Enable real-time database operation monitoring using the
DBMS_SQL_MONITOR.BEGIN_OPERATION function, and then use the
DBMS_SQL_MONITOR.REPORT_SQL_MONITOR function to view the required information.
Explanation:
* Monitoring database operations
Real-Time Database Operations Monitoring enables you to monitor long running database tasks such asbatch jobs, scheduler jobs, and Extraction, Transformation, and Loading (ETL) jobs as a composite
business operation. This feature tracks the progress of SQL and PL/SQL queries associated with the
business operation being monitored. As a DBA or developer, you can define business operations for
monitoring by explicitly specifying the start and end of the operation or implicitly with tags that identify the
operation.