A new report process containing a complex query is written, with high impact on the database. You want to
collect basic statistics about query, such as the level of parallelism, total database time, and the number of I/O
requests.For the database instance STATISTICS_LEVEL, the initialization parameter is set to TYPICAL and the
CONTROL_MANAGEMENT_PACK_ACCESS parameter is set to
DIAGNOSTIC+TUNING.
What should you do to accomplish this task?
A.
Execute the query and view Active Session History (ASH) for information about the query.
B.
Enable SQL trace for the query.
C.
Create a database operation, execute the query, and use the
DBMS_SQL_MONITOR.REPORT_SQL_MONITOR function to view the report.
D.
Use the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure to monitor query execution
and view the information from the V$SESSION_LONGOPS view.
Explanation:
The REPORT_SQL_MONITOR function is used to return a SQL monitoring report for a specific SQL
statement.
Incorrect:
Not A: Not interested in session statistics, only in statistics for the particular SQL query.
Not B: We are interested in statistics, not tracing.
Not D: SET_SESSION_LONGOPS Procedure
This procedure sets a row in the V$SESSION_LONGOPS view. This is a view that is used to indicate the ongoing progress of a long running operation. Some Oracle functions, such as parallel execution and Server
Managed Recovery, use rows in this view to indicate the status of, for example, a database backup.
Applications may use the SET_SESSION_LONGOPS procedure to advertise information on the progress of
application specific long running tasks so that the progress can be monitored by way of the
V$SESSION_LONGOPS view.