QUESTION 126-SQL_MONITOR

Examine the commands executed to monitor database operations:
$> conn sys oracle/oracle@prod as sysdba
SQL > VAR eid NUMBER
SQL > EXEC: eid: = DBMS_SQL_MONITOR.BEGIN_OPERATION ('batch_job', FORCED_TRACKING => 'Y');
Which two statements are true? (Choose two.)
A. Database operations will be monitored only when they consume a significant amount of resources.
B. Database operations for all sessions will be monitored.
C. Database operations will be monitored only if the STATISTICS_LEVEL parameter is set to TYPICAL and CONTROL_MANAGEMENT_PACK_ACCESS is set to DIAGNISTIC + TUNING.
D. Only DML and DDL statements will be monitored for the session.
E. All subsequent statements in the session will be treated as one database operation and will be monitored.

Correct Answer: CE

用于监控长时间运行的事务，包括job，ETL相关操作，SQL查询等。可以很方便DBA监控和解决相关性能问题。

在11G中，Real-Time SQL Monitoring可以让你监控单个SQL或PL-SQL的执行情况。从12c开始，提供了实时监控更为复杂的操作。例如，并行查询，DML,DDL等语句。默认情况下，当SQL语句并行运行时，或在单个执行中消耗至少5秒的CPU或I / O时间时，Real-Time SQL Monitoring就会自动启动。

默认上开启的，会自动监控长时间运行的查询。statistics_level需要设置为TYPICAL（默认值）或ALL。但是前提条件是CONTROL_MANAGEMENT_PACK_ACCESS必须是DIAGNOSTIC+TUNING（默认值）。