OCP-043session_longops

V$SESSION_LONGOPS

This view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release.

To monitor query execution progress, you must be using the cost-based optimizer and you must:

• Set the TIMED_STATISTICS or SQL_TRACE parameter to true

• Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package

You can add information to this view about application-specific long-running operations by using the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information on  DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS

Column	Datatype	Description
SID	NUMBER	Session identifier
SERIAL#	NUMBER	Session serial number
OPNAME	VARCHAR2(64)	Brief description of the operation
TARGET	VARCHAR2(64)	The object on which the operation is carried out
TARGET_DESC	VARCHAR2(32)	Description of the target
SOFAR	NUMBER	The units of work done so far
TOTALWORK	NUMBER	The total units of work
UNITS	VARCHAR2(32)	The units of measurement
START_TIME	DATE	The starting time of operation
LAST_UPDATE_TIME	DATE	Time when statistics last updated
TIMESTAMP	DATE	Timestamp
TIME_REMAINING	NUMBER	Estimate (in seconds) of time remaining for the operation to complete
ELAPSED_SECONDS	NUMBER	The number of elapsed seconds from the start of operations
CONTEXT	NUMBER	Context
MESSAGE	VARCHAR2(512)	Statistics summary message
USERNAME	VARCHAR2(30)	User ID of the user performing the operation
SQL_ADDRESS	RAW(4 | 8)	Used with the value of the SQL_HASH_VALUE column to identify the SQL statement associated with the operation
SQL_HASH_VALUE	NUMBER	Used with the value of the SQL_ADDRESS column to identify the SQL statement associated with the operation
SQL_ID	VARCHAR2(13)	SQL identifier of the SQL statement associated with the operation
QCSID	NUMBER	Session identifier of the parallel coordinator

An Oracle-Supplied Plan

Oracle Database provides one default resource manager plan, SYSTEM_PLAN,which gives priority to system sessions. SYSTEM_PLAN is defined as follows:

Resource Consumer Group	CPU Resource Allocation
	Level 1	Level 2	Level 3
SYS_GROUP	100%	0%	0%
OTHER_GROUPS	0%	100%	0%
LOW_GROUP	0%	0%	100%

The database-provided groups in this plan are:

• SYS_GROUP is the initial consumer group for the users SYS and SYSTEM.

• OTHER_GROUPS applies collectively to all sessions that belong to a consumer group that is not part of the currently active plan schema.

• LOW_GROUP provides a group having lower priority than SYS_GROUP and OTHER_GROUPS in this plan. It is up to you to decide which user sessions will be part of LOW_GROUP. Switch privilege is granted to PUBLIC for this group.

These groups can be used, or not used, and can be modified or deleted.

You can use this simple database-supplied plan if it is appropriate for your environment.

Monitoring and Tuning the Database Resource Manager

To effectively monitor and tune the Database Resource Manager, you must design a representative environment. The Database Resource Manager works best in large production environments in which system utilization is high. If a test places insufficient load on the system, measured CPU allocations can be very different from the allocations specified in the active resource plan. This is because the Database Resource Manager does not attempt to enforce CPU allocation percentage limits as long as consumer groups are getting the resources they need.

84. From the V$SESSION_LONGOPS view, you find that some of the database users have longrunning
queries and are consuming a lot of CPU time. This causes problems for you when you try to log on as the
system user and perform small maintenance tasks. You would like to ensure that users SYS and
SYSTEM get priority over all other users. Which method would you use to achieve this objective?
A.create a plan directive in the SYS_GROUP
B.create the pending area for the consumer group
C.activatethe provided SYSTEM_PLANin Resource Manager
D.set the TIMED_STATISTICS parameter to TRUE in the parameter file
E.set the execution time limit for all users, except SYS and SYSTEM, in their profiles
Answer: C

85. From the V$SESSION_LONGOPS view, you find that some of the database users have longrunning
queries that consume a lot of CPU time. This causes performance problems for other users in the
database, who have much shorter queries.
You would like to make sure that the users with large queries do not use all the CPU time, but you still do
not want to terminate them with an error message.
Which method would you follow to achieve this?
A.set the CPU time per call in the users profile
B.set the CPU time per session in the users profiles
C.set the CPU levels for the users' group using Resource Manager
D.set the TIMED_STATISTICS parameter to TRUE in the parameter file
Answer: C