Tuesday, October 11, 2011
4 Managing Oracle Database Processes
Here describes how to manage and monitor the processes of an Oracle Database instance and contains the following topics:
In general, it is better to be connected through a dispatcher and use a shared server process. This is illustrated in Figure 4-2, "Oracle Database Shared Server Processes". A shared server process can be more efficient because it keeps the number of processes required for the running instance low.
In the following situations, however, users and administrators should explicitly connect to an instance using a dedicated server process:
Shared server architecture eliminates the need for a dedicated server process for each connection (see Figure 4-2).
In a shared server configuration, client user processes connect to a dispatcher. The dispatcher can support multiple client connections concurrently. Each client connection is bound to a virtual circuit, which is a piece of shared memory used by the dispatcher for client database connection requests and replies. The dispatcher places a virtual circuit on a common queue when a request arrives.
An idle shared server process picks up the virtual circuit from the common queue, services the request, and relinquishes the virtual circuit before attempting to retrieve another virtual circuit from the common queue. This approach enables a small pool of server processes to serve a large number of clients. A significant advantage of shared server architecture over the dedicated server model is the reduction of system resources, enabling the support of an increased number of users.
For even better resource management, shared server can be configured for connection pooling. Connection pooling lets a dispatcher support more users by enabling the database server to time-out protocol connections and to use those connections to service an active session. Further, shared server can be configured for session multiplexing, which combines multiple sessions for transmission over a single network connection in order to conserve the operating system's resources.
Shared server architecture requires Oracle Net Services. User processes targeting the shared server must connect through Oracle Net Services, even if they are on the same machine as the Oracle Database instance.
This section discusses how to enable shared server and how to set or alter shared server initialization parameters. It contains the following topics:
Shared server can be started dynamically by setting the
In typical systems, the number of shared servers stabilizes at a ratio of one shared server for every ten connections. For OLTP applications, when the rate of requests is low, or when the ratio of server usage to request is low, the connections-to-servers ratio could be higher. In contrast, in applications where the rate of requests is high or the server usage-to-request ratio is high, the connections-to-server ratio could be lower.
The PMON (process monitor) background process cannot terminate shared servers below the value specified by
If you know the average load on your system, you can set
Assume a database is being used by a telemarketing center staffed by 1000 agents. On average, each agent spends 90% of the time talking to customers and only 10% of the time looking up and updating records. To keep the shared servers from being terminated as agents talk to customers and then spawned again as agents access the database, a DBA specifies that the optimal number of shared servers is 100.
However, not all work shifts are staffed at the same level. On the night shift, only 200 agents are needed. Since
The following statement reduces the number of shared servers:
The primary reason to limit the number of shared servers is to reserve resources, such as memory and CPU time, for other processes. For example, consider the case of the telemarketing center discussed previously:
The DBA wants to reserve two thirds of the resources for batch jobs at night. He sets
Another reason to limit the number of shared servers is to prevent the concurrent run of too many server processes from slowing down the system due to heavy swapping, although
Still other reasons to limit the number of shared servers are testing, debugging, performance analysis, and tuning. For example, to see how many shared servers are needed to efficiently support a certain user community, you can vary
This parameter has no default value. If it is not specified, the system can create shared server sessions as needed, limited by the
A protocol address is required and is specified using one or more of the following attributes:
The following attribute specifies how many dispatchers this configuration should have. It is optional and defaults to 1.
The following attributes tell the instance about the network attributes of each dispatcher of this configuration. They are all optional.
For example, assume a system that can support 970 connections for each process, and that has:
Some examples of setting the
Example: Typical
This is a typical example of setting the
Example: Forcing the IP Address Used for Dispatchers
The following hypothetical example will create two dispatchers that will listen on the specified IP address. The address must be a valid IP address for the host that the instance is on. (The host may be configured with multiple IP addresses.)
Example: Forcing the Port Used by Dispatchers
To force the dispatchers to use a specific port as the listening endpoint, add the
Monitor the following views to determine the load on the dispatcher processes:
To dynamically alter the number of dispatchers when the instance is running, use the
When you reduce the number of dispatchers for a particular dispatcher configuration, the dispatchers are not immediately removed. Rather, as users disconnect, Oracle Database terminates dispatchers down to the limit you specify in
For example, suppose the instance was started with this
If fewer than three dispatcher processes currently exist for TCP/IP, the database creates new ones. If more than one dispatcher process currently exists for TCP/IP with SSL, then the database terminates the extra ones as the connected users disconnect.
Suppose that instead of changing the number of dispatcher processes for the TCP/IP protocol, you want to add another TCP/IP dispatcher that supports connection pooling. You can do so by entering the following statement:
To shut down dispatcher
To terminate dispatchers once all shared server clients disconnect, enter this statement:
Table 4-1 describes the basic Oracle Database background processes, many of which are discussed in more detail elsewhere in this book. The use of additional database server features or options can cause more background processes to be present. For example, when you use Advanced Queuing, the queue monitor (QMNn) background process is present. When you specify the
This section describes how to manage parallel processing of SQL statements. In this configuration Oracle Database can divide the work of processing an SQL statement among multiple parallel processes.
The execution of many SQL statements can be parallelized. The degree of parallelism is the number of parallel execution servers that can be associated with a single operation. The degree of parallelism is determined by any of the following:
The following topics are contained in this section:
The parallel execution servers are enabled by default, because by default the value for
Parallelism can be used by a number of features, including transaction recovery, replication, and SQL execution. In the case of parallel SQL execution, the topic discussed in this book, parallel server processes remain associated with a statement throughout its execution phase. When the statement is completely processed, these processes become available to process other statements.
The following statement disables parallel DDL operations:
A DML statement can be parallelized only if you specifically issue an
The following statement forces parallel execution of subsequent statements and sets the overriding degree of parallelism to 5:
These callable routines are stored in a dynamic link library (DLL), or a libunit in the case of a Java class method, and are registered with the base language. Oracle Database provides a special-purpose interface, the call specification (call spec), that enables users to call external procedures from other languages.
To call an external procedure, an application alerts a network listener process, which in turn starts an external procedure agent. The default name of the agent is
To control access to DLLs, the database administrator grants execute privileges on the appropriate DLLs to application developers. The application developers write the external procedures and grant execute privilege on specific external procedures to other users.
The environment for calling external procedures, consisting of
You terminate a current session using the SQL statement
When an inactive session has been terminated, the
In the following example, an inactive session is terminated. First,
This section describes some of the options available to you for monitoring the operation of your database.
Alerts are automatically generated when a problem occurs or when data does not match expected values for metrics, such as the following:
Background processes periodically flush the data to the Automatic Workload Repository to capture a history of metric values. The alert history table and
The most convenient way to set and view threshold values is to use Enterprise Manager. To manage threshold-based alerts through Enterprise Manager:
Next you must associate a database user with the subscribing agent, because only a user associated with the subscribing agent can access queued messages in the secure
Optionally, you can register with the
To read an alert message, you can use the
The alert file, or alert log, is a special trace file. The alert file of a database is a chronological log of messages and errors, and includes the following items:
Initialization parameters controlling the location and size of trace files are:
Oracle Database also writes values of initialization parameters to the alert file, in addition to other important statistics.
You can safely delete the alert file while the instance is running, although you should consider making an archived copy of it first. This archived copy could prove valuable if you should have a future problem that requires investigating the history of an instance.
Trace files are written on behalf of server processes whenever internal errors occur. Additionally, setting the initialization parameter
Optionally, you can request that trace files be generated for server processes. Regardless of the current value of the
Use the
A deadlock can occur when two or more users are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work. Oracle Database automatically detects deadlock situations and resolves them by rolling back one of the statements involved in the deadlock, thereby releasing one set of the conflicting row locks.
Oracle Database is designed to avoid deadlocks, and they are not common. Most often they occur when transactions explicitly override the default locking of the database. Deadlocks can affect the performance of your database, so Oracle provides some scripts and views that enable you to monitor locks.
The
A second script,
The following views can help you to monitor locks:
When a session is waiting for resources, it is not doing any useful work. A large number of waits is a source of concern. Wait event data reveals various symptoms of problems that might be affecting performance, such as latch contention, buffer contention, and I/O contention.
Oracle provides several views that display wait event statistics. A discussion of these views and their role in instance tuning is contained in Oracle Database Performance Tuning Guide.
- About Dedicated and Shared Server Processes
- Configuring Oracle Database for Shared Server
- About Oracle Database Background Processes
- Managing Processes for Parallel SQL Execution
- Managing Processes for External Procedures
- Terminating Sessions
- Monitoring the Operation of Your Database
About Dedicated and Shared Server Processes
Oracle Database creates server processes to handle the requests of user processes connected to an instance. A server process can be either of the following:- A dedicated server process, which services only one user process
- A shared server process, which can service multiple user processes
Dedicated Server Processes
Figure 4-1, "Oracle Database Dedicated Server Processes" illustrates how dedicated server processes work. In this diagram two user processes are connected to the database through dedicated server processes.In general, it is better to be connected through a dispatcher and use a shared server process. This is illustrated in Figure 4-2, "Oracle Database Shared Server Processes". A shared server process can be more efficient because it keeps the number of processes required for the running instance low.
In the following situations, however, users and administrators should explicitly connect to an instance using a dedicated server process:
- To submit a batch job (for example, when a job can allow little or no idle time for the server process)
- To use Recovery Manager (RMAN) to back up, restore, or recover a database
SERVER=DEDICATED
clause in the connect descriptor.See Also: Oracle Net Services Administrator's Guide for more information about requesting a dedicated server connection |
Shared Server Processes
Consider an order entry system with dedicated server processes. A customer phones the order desk and places an order, and the clerk taking the call enters the order into the database. For most of the transaction, the clerk is on the telephone talking to the customer. A server process is not needed during this time, so the server process dedicated to the clerk's user process remains idle. The system is slower for other clerks entering orders, because the idle server process is holding system resources.Shared server architecture eliminates the need for a dedicated server process for each connection (see Figure 4-2).
In a shared server configuration, client user processes connect to a dispatcher. The dispatcher can support multiple client connections concurrently. Each client connection is bound to a virtual circuit, which is a piece of shared memory used by the dispatcher for client database connection requests and replies. The dispatcher places a virtual circuit on a common queue when a request arrives.
An idle shared server process picks up the virtual circuit from the common queue, services the request, and relinquishes the virtual circuit before attempting to retrieve another virtual circuit from the common queue. This approach enables a small pool of server processes to serve a large number of clients. A significant advantage of shared server architecture over the dedicated server model is the reduction of system resources, enabling the support of an increased number of users.
For even better resource management, shared server can be configured for connection pooling. Connection pooling lets a dispatcher support more users by enabling the database server to time-out protocol connections and to use those connections to service an active session. Further, shared server can be configured for session multiplexing, which combines multiple sessions for transmission over a single network connection in order to conserve the operating system's resources.
Shared server architecture requires Oracle Net Services. User processes targeting the shared server must connect through Oracle Net Services, even if they are on the same machine as the Oracle Database instance.
See Also: Oracle Net Services Administrator's Guide for more detailed information about shared server, including features such as connection pooling and session multiplexing |
Configuring Oracle Database for Shared Server
Shared memory resources are preconfigured to allow the enabling of shared server at run time. You need not configure it by specifying parameters in your initialization parameter file, but you can do so if that better suits your environment. You can start dispatchers and shared server processes (shared servers) dynamically using theALTER SYSTEM
statement.This section discusses how to enable shared server and how to set or alter shared server initialization parameters. It contains the following topics:
- Initialization Parameters for Shared Server
- Enabling Shared Server
- Configuring Dispatchers
- Monitoring Shared Server
See Also:
Oracle Database SQL Reference for further information about theALTER
SYSTEM
statement
Initialization Parameters for Shared Server
The following initialization parameters control shared server operation:-
SHARED_SERVERS
: Specifies the initial number of shared servers to start and the minimum number of shared servers to keep. This is the only required parameter for using shared servers. -
MAX_SHARED_SERVERS
: Specifies the maximum number of shared servers that can run simultaneously. -
SHARED_SERVER_SESSIONS
: Specifies the total number of shared server user sessions that can run simultaneously. Setting this parameter enables you to reserve user sessions for dedicated servers. -
DISPATCHERS
: Configures dispatcher processes in the shared server architecture. -
MAX_DISPATCHERS
: Specifies the maximum number of dispatcher processes that can run simultaneously. This parameter can be ignored for now. It will only be useful in a future release when the number of dispatchers is auto-tuned according to the number of concurrent connections. -
CIRCUITS
: Specifies the total number of virtual circuits that are available for inbound and outbound network sessions.
See Also: Oracle Database Reference for more information about these initialization parameters |
Enabling Shared Server
Shared server is enabled by setting theSHARED_SERVERS
initialization parameter to a value greater than 0. The other shared server initialization parameters need not be set. Because shared server requires at least one dispatcher in order to work, a dispatcher is brought up even if no dispatcher has been configured. Dispatchers are discussed in "Configuring Dispatchers".Shared server can be started dynamically by setting the
SHARED_SERVERS
parameter to a nonzero value with the ALTER SYSTEM
statement, or SHARED_SERVERS
can be included at database startup in the initialization parameter file. If SHARED_SERVERS
is not included in the initialization parameter file, or is included but is set to 0, then shared server is not enabled at database startup.Note: For backward compatibility, if SHARED_SERVERS is not included in the initialization parameter file at database startup, but DISPATCHERS is included and it specifies at least one dispatcher, shared server is enabled. In this case, the default for SHARED_SERVERS is 1. However, if neither SHARED_SERVERS nor DISPATCHERS is included in the initialization file, you cannot start shared server after the instance is brought up by just altering the DISPATCHERS parameter. You must specifically alter SHARED_SERVERS to a nonzero value to start shared server. |
Determining a Value for SHARED_SERVERS
The SHARED_SERVERS
initialization parameter specifies the minimum number of shared servers that you want created when the instance is started. After instance startup, Oracle Database can dynamically adjust the number of shared servers based on how busy existing shared servers are and the length of the request queue.In typical systems, the number of shared servers stabilizes at a ratio of one shared server for every ten connections. For OLTP applications, when the rate of requests is low, or when the ratio of server usage to request is low, the connections-to-servers ratio could be higher. In contrast, in applications where the rate of requests is high or the server usage-to-request ratio is high, the connections-to-server ratio could be lower.
The PMON (process monitor) background process cannot terminate shared servers below the value specified by
SHARED_SERVERS
. Therefore, you can use this parameter to stabilize the load and minimize strain on the system by preventing PMON from terminating and then restarting shared servers because of coincidental fluctuations in load.If you know the average load on your system, you can set
SHARED_SERVERS
to an optimal value. The following example shows how you can use this parameter:Assume a database is being used by a telemarketing center staffed by 1000 agents. On average, each agent spends 90% of the time talking to customers and only 10% of the time looking up and updating records. To keep the shared servers from being terminated as agents talk to customers and then spawned again as agents access the database, a DBA specifies that the optimal number of shared servers is 100.
However, not all work shifts are staffed at the same level. On the night shift, only 200 agents are needed. Since
SHARED_SERVERS
is a dynamic parameter, a DBA reduces the number of shared servers to 20 at night, thus allowing resources to be freed up for other tasks such as batch jobs.Decreasing the Number of Shared Server Processes
You can decrease the minimum number of shared servers that must be kept active by dynamically setting theSHARED_SERVERS
parameter to a lower value. Thereafter, until the number of shared servers is decreased to the value of the SHARED_SERVERS
parameter, any shared servers that become inactive are marked by PMON for termination.The following statement reduces the number of shared servers:
ALTER SYSTEM SET SHARED_SERVERS = 5;Setting
SHARED_SERVERS
to 0 disables shared server. For more information, please refer to "Disabling Shared Servers".Limiting the Number of Shared Server Processes
TheMAX_SHARED_SERVERS
parameter specifies the maximum number of shared servers that can be automatically created by PMON. It has no default value. If no value is specified, then PMON starts as many shared servers as is required by the load, subject to these limitations:- The process limit (set by the
PROCESSES
initialization parameter) - A minimum number of free process slots (at least one-eighth of the total process slots, or two slots if
PROCESSES
is set to less than 24) - System resources
Note:
On Windows NT, take care when settingMAX_SHARED_SERVERS
to a high value, because each server is a thread in a common process.
SHARED_SERVERS
overrides the value of MAX_SHARED_SERVERS
. Therefore, you can force PMON to start more shared servers than the MAX_SHARED_SERVERS
value by setting SHARED_SERVERS
to a value higher than MAX_SHARED_SERVERS
. You can subsequently place a new upper limit on the number of shared servers by dynamically altering the MAX_SHARED_SERVERS
to a value higher than SHARED_SERVERS
.The primary reason to limit the number of shared servers is to reserve resources, such as memory and CPU time, for other processes. For example, consider the case of the telemarketing center discussed previously:
The DBA wants to reserve two thirds of the resources for batch jobs at night. He sets
MAX_SHARED_SERVERS
to less than one third of the maximum number of processes (PROCESSES
). By doing so, the DBA ensures that even if all agents happen to access the database at the same time, batch jobs can connect to dedicated servers without having to wait for the shared servers to be brought down after processing agents' requests.Another reason to limit the number of shared servers is to prevent the concurrent run of too many server processes from slowing down the system due to heavy swapping, although
PROCESSES
can serve as the upper bound for this rather than MAX_SHARED_SERVERS
.Still other reasons to limit the number of shared servers are testing, debugging, performance analysis, and tuning. For example, to see how many shared servers are needed to efficiently support a certain user community, you can vary
MAX_SHARED_SERVERS
from a very small number upward until no delay in response time is noticed by the users.Limiting the Number of Shared Server Sessions
TheSHARED_SERVER_SESSIONS
initialization parameter specifies the maximum number of concurrent shared server user sessions. Setting this parameter, which is a dynamic parameter, lets you reserve database sessions for dedicated servers. This in turn ensures that administrative tasks that require dedicated servers, such as backing up or recovering the database, are not preempted by shared server sessions.This parameter has no default value. If it is not specified, the system can create shared server sessions as needed, limited by the
SESSIONS
initialization parameter.Protecting Shared Memory
TheCIRCUITS
parameter sets a maximum limit on the number of virtual circuits that can be created in shared memory. This parameter has no default. If it is not specified, then the system can create circuits as needed, limited by the DISPATCHERS
initialization parameter and system resources.Configuring Dispatchers
TheDISPATCHERS
initialization parameter configures dispatcher processes in the shared server architecture. At least one dispatcher process is required for shared server to work.If you do not specify a dispatcher, but you enable shared server by setting SHARED_SERVER
to a nonzero value, then by default Oracle Database creates one dispatcher for the TCP protocol. The equivalent DISPATCHERS
explicit setting of the initialization parameter for this configuration is:dispatchers="(PROTOCOL=tcp)"You can configure more dispatchers, using the
DISPATCHERS
initialization parameter, if either of the following conditions apply:- You need to configure a protocol other than TCP/IP. You configure a protocol address with one of the following attributes of the DISPATCHERS parameter:
-
ADDRESS
-
DESCRIPTION
-
PROTOCOL
-
- You want to configure one or more of the optional dispatcher attributes:
-
DISPATCHERS
-
CONNECTIONS
-
SESSIONS
-
TICKS
-
LISTENER
-
MULTIPLEX
-
POOL
-
SERVICE
-
DISPATCHERS Initialization Parameter Attributes
This section provides brief descriptions of the attributes that can be specified with theDISPATCHERS
initialization parameter.A protocol address is required and is specified using one or more of the following attributes:
Attribute | Description |
---|---|
ADDRESS | Specify the network protocol address of the endpoint on which the dispatchers listen. |
DESCRIPTION | Specify the network description of the endpoint on which the dispatchers listen, including the network protocol address. The syntax is as follows: (DESCRIPTION=(ADDRESS=...)) |
PROTOCOL | Specify the network protocol for which the dispatcher generates a listening endpoint. For example: (PROTOCOL=tcp)See the Oracle Net Services Reference Guide for further information about protocol address syntax. |
The following attributes tell the instance about the network attributes of each dispatcher of this configuration. They are all optional.
You can specify either an entire attribute name a substring consisting of at least the first three characters. For example, you can specify
SESSIONS=3,
SES=3
, SESS=3
, or SESSI=3
, and so forth.See Also: Oracle Database Reference for more detailed descriptions of the attributes of the DISPATCHERS initialization parameter |
Determining the Number of Dispatchers
Once you know the number of possible connections for each process for the operating system, calculate the initial number of dispatchers to create during instance startup, for each network protocol, using the following formula:Number of dispatchers =
CEIL ( max. concurrent sessions / connections for each dispatcher )
CEIL
returns the result roundest up to the next whole integer.For example, assume a system that can support 970 connections for each process, and that has:
- A maximum of 4000 sessions concurrently connected through TCP/IP and
- A maximum of 2,500 sessions concurrently connected through TCP/IP with SSL
DISPATCHERS
attribute for TCP/IP should be set to a minimum of five dispatchers (4000 / 970), and for TCP/IP with SSL three dispatchers (2500 / 970:DISPATCHERS='(PROT=tcp)(DISP=5)', '(PROT-tcps)(DISP=3)'Depending on performance, you may need to adjust the number of dispatchers.
Setting the Initial Number of Dispatchers
You can specify multiple dispatcher configurations by settingDISPATCHERS
to a comma separated list of strings, or by specifying multiple DISPATCHERS
parameters in the initialization file. If you specify DISPATCHERS
multiple times, the lines must be adjacent to each other in the initialization parameter file. Internally, Oracle Database assigns an INDEX
value (beginning with zero) to each DISPATCHERS
parameter. You can later refer to that DISPATCHERS
parameter in an ALTER SYSTEM
statement by its index number.Some examples of setting the
DISPATCHERS
initialization parameter follow.Example: Typical
This is a typical example of setting the
DISPATCHERS
initialization parameter.DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=2)"
Example: Forcing the IP Address Used for Dispatchers
The following hypothetical example will create two dispatchers that will listen on the specified IP address. The address must be a valid IP address for the host that the instance is on. (The host may be configured with multiple IP addresses.)
DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(HOST=144.25.16.201))(DISPATCHERS=2)"
Example: Forcing the Port Used by Dispatchers
To force the dispatchers to use a specific port as the listening endpoint, add the
PORT
attribute as follows:DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(PORT=5000))"
DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(PORT=5001))"
Altering the Number of Dispatchers
You can control the number of dispatcher processes in the instance. Unlike the number of shared servers, the number of dispatchers does not change automatically. You change the number of dispatchers explicitly with theALTER SYSTEM
statement. In this release of Oracle Database, you can increase the number of dispatchers to more than the limit specified by the MAX_DISPATCHERS
parameter. It is planned that MAX_DISPATCHERS
will be taken into consideration in a future release.Monitor the following views to determine the load on the dispatcher processes:
-
V$QUEUE
-
V$DISPATCHER
-
V$DISPATCHER_RATE
See Also:
Oracle Database Performance Tuning Guide for information about monitoring these views to determine dispatcher load and performance
To dynamically alter the number of dispatchers when the instance is running, use the
ALTER SYSTEM
statement to modify the DISPATCHERS
attribute setting for an existing dispatcher configuration. You can also add new dispatcher configurations to start dispatchers with different network attributes.When you reduce the number of dispatchers for a particular dispatcher configuration, the dispatchers are not immediately removed. Rather, as users disconnect, Oracle Database terminates dispatchers down to the limit you specify in
DISPATCHERS
,For example, suppose the instance was started with this
DISPATCHERS
setting in the initialization parameter file:DISPATCHERS='(PROT=tcp)(DISP=2)', '(PROT=tcps)(DISP=2)'To increase the number of dispatchers for the TCP/IP protocol from 2 to 3, and decrease the number of dispatchers for the TCP/IP with SSL protocol from 2 to 1, you can issue the following statement:
ALTER SYSTEM SET DISPATCHERS = '(INDEX=0)(DISP=3)', '(INDEX=1)(DISP=1)';or
ALTER SYSTEM SET DISPATCHERS = '(PROT=tcp)(DISP=3)', '(PROT-tcps)(DISP=1)';
Note: You need not specify ( DISP=1 ). It is optional because 1 is the default value for the DISPATCHERS parameter. |
Suppose that instead of changing the number of dispatcher processes for the TCP/IP protocol, you want to add another TCP/IP dispatcher that supports connection pooling. You can do so by entering the following statement:
ALTER SYSTEM SET DISPATCHERS = '(INDEX=2)(PROT=tcp)(POOL=on)';The
INDEX
attribute is needed to add the new dispatcher configuration. If you omit (INDEX=2
) in the preceding statement, then the TCP/IP dispatcher configuration at INDEX 0 will be changed to support connection pooling, and the number of dispatchers for that configuration will be reduced to 1, which is the default when the number of dispatchers (attribute DISPATCHERS
) is not specified.Notes on Altering Dispatchers
- The
INDEX
keyword can be used to identify which dispatcher configuration to modify. If you do not specifyINDEX
, then the first dispatcher configuration matching theDESCRIPTION
,ADDRESS
, orPROTOCOL
specified will be modified. If no match is found among the existing dispatcher configurations, then a new dispatcher will be added. - The
INDEX
value can range from 0 ton
-1, wheren
is the current number of dispatcher configurations. If yourALTER SYSTEM
statement specifies anINDEX
value equal ton
, wheren
is the current number of dispatcher configurations, a new dispatcher configuration will be added. - To see the values of the current dispatcher configurations--that is, the number of dispatchers, whether connection pooling is on, and so forth--query the
V$DISPATCHER_CONFIG
dynamic performance view. To see which dispatcher configuration a dispatcher is associated with, query theCONF_INDX
column of theV$DISPATCHER
view. - When you change the
DESCRIPTION
,ADDRESS
,PROTOCOL
,CONNECTIONS
,TICKS
,MULTIPLEX
, andPOOL
attributes of a dispatcher configuration, the change does not take effect for existing dispatchers but only for new dispatchers. Therefore, in order for the change to be effective for all dispatchers associated with a configuration, you must forcibly kill existing dispatchers after altering theDISPATCHERS
parameter, and let the database start new ones in their place with the newly specified properties.
The attributesLISTENER
andSERVICES
are not subject to the same constraint. They apply to existing dispatchers associated with the modified configuration. AttributeSESSIONS
applies to existing dispatchers only if its value is reduced. However, if its value is increased, it is applied only to newly started dispatchers.
Shutting Down Specific Dispatcher Processes
With theALTER SYSTEM
statement, you leave it up to the database to determine which dispatchers to shut down to reduce the number of dispatchers. Alternatively, it is possible to shut down specific dispatcher processes. To identify the name of the specific dispatcher process to shut down, use the V$DISPATCHER
dynamic performance view.SELECT NAME, NETWORK FROM V$DISPATCHER;Each dispatcher is uniquely identified by a name of the form Dnnn.
To shut down dispatcher
D002
, issue the following statement:ALTER SYSTEM SHUTDOWN IMMEDIATE 'D002';The
IMMEDIATE
keyword stops the dispatcher from accepting new connections and the database immediately terminates all existing connections through that dispatcher. After all sessions are cleaned up, the dispatcher process shuts down. If IMMEDIATE
were not specified, the dispatcher would wait until all of its users disconnected and all of its connections terminated before shutting down.Disabling Shared Servers
You disable shared server by settingSHARED_SERVERS
to 0. No new client can connect in shared mode. However, when you set SHARED_SERVERS
to 0, Oracle Database retains some shared servers until all shared server connections are closed. The number of shared servers retained is either the number specified by the preceding setting of SHARED_SERVERS
or the value of the MAX_SHARED_SERVERS
parameter, whichever is smaller. If both SHARED_SERVERS
and MAX_SHARED_SERVERS
are set to 0, then all shared servers will terminate and requests from remaining shared server clients will be queued until the value of SHARED_SERVERS
or MAX_SHARED_SERVERS
is raised again.To terminate dispatchers once all shared server clients disconnect, enter this statement:
ALTER SYSTEM SET DISPATCHERS = '';
Monitoring Shared Server
The following views are useful for obtaining information about your shared server configuration and for monitoring performance.View | Description |
---|---|
V$DISPATCHER | Provides information on the dispatcher processes, including name, network address, status, various usage statistics, and index number. |
V$DISPATCHER_CONFIG | Provides configuration information about the dispatchers. |
V$DISPATCHER_RATE | Provides rate statistics for the dispatcher processes. |
V$QUEUE | Contains information on the shared server message queues. |
V$SHARED_SERVER | Contains information on the shared servers. |
V$CIRCUIT | Contains information about virtual circuits, which are user connections to the database through dispatchers and servers. |
V$SHARED_SERVER_MONITOR | Contains information for tuning shared server. |
V$SGA | Contains size information about various system global area (SGA) groups. May be useful when tuning shared server. |
V$SGASTAT | Contains detailed statistical information about the SGA, useful for tuning. |
V$SHARED_POOL_RESERVED | Lists statistics to help tune the reserved pool and space within the shared pool. |
See Also:
|
About Oracle Database Background Processes
To maximize performance and accommodate many users, a multiprocess Oracle Database system uses background processes. Background processes consolidate functions that would otherwise be handled by multiple database programs running for each user process. Background processes asynchronously perform I/O and monitor other Oracle Database processes to provide increased parallelism for better performance and reliability.Table 4-1 describes the basic Oracle Database background processes, many of which are discussed in more detail elsewhere in this book. The use of additional database server features or options can cause more background processes to be present. For example, when you use Advanced Queuing, the queue monitor (QMNn) background process is present. When you specify the
FILE_MAPPING
initialization parameter for mapping datafiles to physical devices on a storage subsystem, then the FMON process is present. Table 4-1 Oracle Database Background Processes
See Also: Oracle Database Concepts for more information about Oracle Database background processes |
Managing Processes for Parallel SQL Execution
Note: The parallel execution feature described in this section is available with the Oracle Database Enterprise Edition. |
The execution of many SQL statements can be parallelized. The degree of parallelism is the number of parallel execution servers that can be associated with a single operation. The degree of parallelism is determined by any of the following:
- A
PARALLEL
clause in a statement - For objects referred to in a query, the
PARALLEL
clause that was used when the object was created or altered - A parallel hint inserted into the statement
- A default determined by the database
The following topics are contained in this section:
- About Parallel Execution Servers
- Altering Parallel Execution for a Session
See Also: - Oracle Database Concepts for a description of parallel execution
- Oracle Database Performance Tuning Guide for information about using parallel hints
About Parallel Execution Servers
When an instance starts up, Oracle Database creates a pool of parallel execution servers which are available for any parallel operation. A process called the parallel execution coordinator dispatches the execution of a pool of parallel execution servers and coordinates the sending of results from all of these parallel execution servers back to the user.The parallel execution servers are enabled by default, because by default the value for
PARALLEL_MAX_SERVERS
initialization parameter is set >0. The processes are available for use by the various Oracle Database features that are capable of exploiting parallelism. Related initialization parameters are tuned by the database for the majority of users, but you can alter them as needed to suit your environment. For ease of tuning, some parameters can be altered dynamically.Parallelism can be used by a number of features, including transaction recovery, replication, and SQL execution. In the case of parallel SQL execution, the topic discussed in this book, parallel server processes remain associated with a statement throughout its execution phase. When the statement is completely processed, these processes become available to process other statements.
See Also: Oracle Data Warehousing Guide for more information about using and tuning parallel execution, including parallel SQL execution |
Altering Parallel Execution for a Session
You control parallel SQL execution for a session using theALTER SESSION
statement.Disabling Parallel SQL Execution
You disable parallel SQL execution with anALTER SESSION DISABLE PARALLEL DML|DDL|QUERY
statement. All subsequent DML (INSERT
, UPDATE
, DELETE
), DDL (CREATE
, ALTER
), or query (SELECT
) operations are executed serially after such a statement is issued. They will be executed serially regardless of any PARALLEL
clause associated with the statement or parallel attribute associated with the table or indexes involved.The following statement disables parallel DDL operations:
ALTER SESSION DISABLE PARALLEL DDL;
Enabling Parallel SQL Execution
You enable parallel SQL execution with anALTER SESSION ENABLE PARALLEL DML|DDL|QUERY
statement. Subsequently, when a PARALLEL
clause or parallel hint is associated with a statement, those DML, DDL, or query statements will execute in parallel. By default, parallel execution is enabled for DDL and query statements.A DML statement can be parallelized only if you specifically issue an
ALTER SESSION
statement to enable parallel DML:ALTER SESSION ENABLE PARALLEL DML;
Forcing Parallel SQL Execution
You can force parallel execution of all subsequent DML, DDL, or query statements for which parallelization is possible with theALTER SESSION FORCE PARALLEL DML|DDL|QUERY
statement. Additionally you can force a specific degree of parallelism to be in effect, overriding any PARALLEL
clause associated with subsequent statements. If you do not specify a degree of parallelism in this statement, the default degree of parallelism is used. However, a degree of parallelism specified in a statement through a hint will override the degree being forced.The following statement forces parallel execution of subsequent statements and sets the overriding degree of parallelism to 5:
ALTER SESSION FORCE PARALLEL DDL PARALLEL 5;
Managing Processes for External Procedures
External procedures are procedures written in one language that are called from another program in a different language. An example is a PL/SQL program calling one or more C routines that are required to perform special-purpose processing.These callable routines are stored in a dynamic link library (DLL), or a libunit in the case of a Java class method, and are registered with the base language. Oracle Database provides a special-purpose interface, the call specification (call spec), that enables users to call external procedures from other languages.
To call an external procedure, an application alerts a network listener process, which in turn starts an external procedure agent. The default name of the agent is
extproc
, and this agent must reside on the same computer as the database server. Using the network connection established by the listener, the application passes to the external procedure agent the name of the DLL or libunit, the name of the external procedure, and any relevant parameters. The external procedure agent then loads, DLL or libunit, runs the external procedure, and passes back to the application any values returned by the external procedure.To control access to DLLs, the database administrator grants execute privileges on the appropriate DLLs to application developers. The application developers write the external procedures and grant execute privilege on specific external procedures to other users.
Note: The external library (DLL file) must be statically linked. In other words, it must not reference any external symbols from other external libraries (DLL files). Oracle Database does not resolve such symbols, so they can cause your external procedure to fail. |
tnsnames.or
a and listener.ora
entries, is configured by default during the installation of your database. You may need to perform additional network configuration steps for a higher level of security. These steps are documented in the Oracle Net Services Administrator's Guide.See Also: Oracle Database Application Developer's Guide - Fundamentals for information about external procedures |
Terminating Sessions
Sometimes it is necessary to terminate current user sessions. For example, you might want to perform an administrative operation and need to terminate all nonadministrative sessions. This section describes the various aspects of terminating sessions, and contains the following topics:- Identifying Which Session to Terminate
- Terminating an Active Session
- Terminating an Inactive Session
You terminate a current session using the SQL statement
ALTER SYSTEM KILL SESSION
. The following statement terminates the session whose system identifier is 7 and serial number is 15:ALTER SYSTEM KILL SESSION '7,15';
Identifying Which Session to Terminate
To identify which session to terminate, specify the session index number and serial number. To identify the system identifier (SID) and serial number of a session, query theV$SESSION
dynamic performance view. For example, the following query identifies all sessions for the user jward
:SELECT SID, SERIAL#, STATUSA session is
FROM V$SESSION
WHERE USERNAME = 'JWARD';
SID SERIAL# STATUS
----- --------- --------
7 15 ACTIVE
12 63 INACTIVE
ACTIVE
when it is making a SQL call to Oracle Database. A session is INACTIVE
if it is not making a SQL call to the database.See Also: Oracle Database Reference for a description of the status values for a session |
Terminating an Active Session
If a user session is processing a transaction (ACTIVE
status) when you terminate the session, the transaction is rolled back and the user immediately receives the following message:ORA-00028: your session has been killedIf, after receiving the
ORA-00028
message, a user submits additional statements before reconnecting to the database, Oracle Database returns the following message:ORA-01012: not logged onAn active session cannot be interrupted when it is performing network I/O or rolling back a transaction. Such a session cannot be terminated until the operation completes. In this case, the session holds all resources until it is terminated. Additionally, the session that issues the
ALTER SYSTEM
statement to terminate a session waits up to 60 seconds for the session to be terminated. If the operation that cannot be interrupted continues past one minute, the issuer of the ALTER SYSTEM
statement receives a message indicating that the session has been marked to be terminated. A session marked to be terminated is indicated in V$SESSION
with a status of KILLED
and a server that is something other than PSEUDO
.Terminating an Inactive Session
If the session is not making a SQL call to Oracle Database (isINACTIVE
) when it is terminated, the ORA-00028
message is not returned immediately. The message is not returned until the user subsequently attempts to use the terminated session.When an inactive session has been terminated, the
STATUS
of the session in the V$SESSION
view is KILLED
. The row for the terminated session is removed from V$SESSION
after the user attempts to use the session again and receives the ORA-00028
message.In the following example, an inactive session is terminated. First,
V$SESSION
is queried to identify the SID
and SERIAL#
of the session, and then the session is terminated.SELECT SID,SERIAL#,STATUS,SERVER
FROM V$SESSION
WHERE USERNAME = 'JWARD';
SID SERIAL# STATUS SERVER
----- -------- --------- ---------
7 15 INACTIVE DEDICATED
12 63 INACTIVE DEDICATED
2 rows selected.
ALTER SYSTEM KILL SESSION '7,15';
Statement processed.
SELECT SID, SERIAL#, STATUS, SERVER
FROM V$SESSION
WHERE USERNAME = 'JWARD';
SID SERIAL# STATUS SERVER
----- -------- --------- ---------
7 15 KILLED PSEUDO
12 63 INACTIVE DEDICATED
2 rows selected.
Monitoring the Operation of Your Database
It is important that you monitor the operation of your database on a regular basis. Doing so not only informs you about errors that have not yet come to your attention but also gives you a better understanding of the normal operation of your database. Being familiar with normal behavior in turn helps you recognize when something is wrong.This section describes some of the options available to you for monitoring the operation of your database.
Server-Generated Alerts
A server-generated alert is a notification from the Oracle Database server of an impending problem. The notification may contain suggestions for correcting the problem. Notifications are also provided when the problem condition has been cleared.Alerts are automatically generated when a problem occurs or when data does not match expected values for metrics, such as the following:
- Physical Reads Per Sec
- User Commits Per Sec
- SQL Service Response Time
-
Snapshot Too Old
-
Resumable Session Suspended
-
Recovery Area Space Usage
ALERT_QUE
owned by the user SYS
. Oracle Enterprise Manager reads this queue and provides notifications about outstanding server alerts, and sometimes suggests actions for correcting the problem. The alerts are displayed on the Enterprise Manager console and can be configured to send email or pager notifications to selected administrators. If an alert cannot be written to the alert queue, a message about the alert is written to the Oracle Database alert log.Background processes periodically flush the data to the Automatic Workload Repository to capture a history of metric values. The alert history table and
ALERT_QUE
are purged automatically by the system at regular intervals.The most convenient way to set and view threshold values is to use Enterprise Manager. To manage threshold-based alerts through Enterprise Manager:
- On the Database Home page, click on the Manage Metrics link at the bottom of the page to display the Thresholds page.
- On the Thresholds page, you can edit the threshold values.
See Also: Oracle Enterprise Manager Concepts for information about alerts available with Oracle Enterprise Manager |
Using APIs to Administer Server-Generated Alerts
You can view and change threshold settings for the server alert metrics using theSET_THRESHOLD
and GET_THRESHOLD
procedures of the DBMS_SERVER_ALERTS
PL/SQL package. The DBMS_AQ
and DBMS_AQADM
packages provide procedures for accessing and reading alert messages in the alert queue.See Also: PL/SQL Packages and Types Reference for information about the DBMS_SERVER_ALERTS , DBMS_AQ , and DBMS_AQADM packages |
Setting Threshold Levels
The following example shows how to set thresholds with theSET_THRESHOLD
procedure for CPU time for each user call for an instance:DBMS_SERVER_ALERT.SET_THRESHOLD(In this example, a warning alert is issued when CPU time exceeds 8000 microseconds for each user call and a critical alert is issued when CPU time exceeds 10,000 microseconds for each user call. The arguments include:
DBMS_SERVER_ALERT.CPU_TIME_PER_CALL, DBMS_SERVER_ALERT.OPERATOR_GE, '8000',
DBMS_SERVER_ALERT.OPERATOR_GE, '10000', 1, 2, 'inst1',
DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE, 'main.regress.rdbms.dev.us.oracle.com');
-
CPU_TIME_PER_CALL
specifies the metric identifier. For a list of support metrics, see PL/SQL Packages and Types Reference. - The observation period is set to 1 minute. This period specifies the number of minutes that the condition must deviate from the threshold value before the alert is issued.
- The number of consecutive occurrences is set to 2. This number specifies how many times the metric value must violate the threshold values before the alert is generated.
- The name of the instance is set to
inst1
. - The constant
DBMS_ALERT.OBJECT_TYPE_SERVICE
specifies the object type on which the threshold is set. In this example, the service name ismain.regress.rdbms.dev.us.oracle.com
.
Retrieving Threshold Information
To retrieve threshold values, use theGET_THRESHOLD
procedure. For example:DECLAREYou can also check specific threshold settings with the
warning_operator BINARY_INTEGER;
warning_value VARCHAR2(60);
critical_operator BINARY_INTEGER;
critical_value VARCHAR2(60);
observation_period BINARY_INTEGER;
consecutive_occurrences BINARY_INTEGER;
BEGIN
DBMS_SERVER_ALERT.GET_THRESHOLD(
DBMS_SERVER_ALERT.CPU_TIME_PER_CALL, warning_operator, warning_value,
critical_operator, critical_value, observation_period,
consecutive_occurrences, 'inst1',
DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE, 'main.regress.rdbms.dev.us.oracle.com');
DBMS_OUTPUT.PUT_LINE('Warning operator: ' || warning_operator);
DBMS_OUTPUT.PUT_LINE('Warning value: ' || warning_value);
DBMS_OUTPUT.PUT_LINE('Critical operator: ' || critical_operator);
DBMS_OUTPUT.PUT_LINE('Critical value: ' || critical_value);
DBMS_OUTPUT.PUT_LINE('Observation_period: ' || observation_period);
DBMS_OUTPUT.PUT_LINE('Consecutive occurrences:' || consecutive_occurrences);
END;
/
DBA_THRESHOLDS
view. For example:SELECT metrics_name, warning_value, critical_value, consecutive_occurrences
FROM DBA_THRESHOLDS
WHERE metrics_name LIKE '%CPU Time%';
Additional APIs to Manage Server-Generated Alerts
If you use your own tool rather than Enterprise Manager to display alerts, you must subscribe to theALERT_QUE
, read the ALERT_QUE
, and display an alert notification after setting the threshold levels for an alert. To create an agent and subscribe the agent to the ALERT_QUE
, use the CREATE_AQ_AGENT
and ADD_SUBSCRIBER
procedures of the DBMS_AQADM
package.Next you must associate a database user with the subscribing agent, because only a user associated with the subscribing agent can access queued messages in the secure
ALERT_QUE
. You must also assign the enqueue privilege to the user. Use the ENABLE_DB_ACCESS
and GRANT_QUEUE_PRIVILEGE
procedures of the DBMS_AQADM
package.Optionally, you can register with the
DBMS_AQ.REGISTER
procedure to receive an asynchronous notification when an alert is enqueued to ALERT_QUE
. The notification can be in the form of email, HTTP post, or PL/SQL procedure.To read an alert message, you can use the
DBMS_AQ.DEQUEUE
procedure or OCIAQDeq
call. After the message has been dequeued, use the DBMS_SERVER_ALERT.EXPAND_MESSAGE
procedure to expand the text of the message.Viewing Alert Data
The following dictionary views provide information about server alerts:-
DBA_THRESHOLDS
lists the threshold settings defined for the instance. -
DBA_OUTSTANDING_ALERTS
describes the outstanding alerts in the database. -
DBA_ALERT_HISTORY
lists a history of alerts that have been cleared. -
V$ALERT_TYPES
provides information such as group and type for each alert. -
V$METRICNAME
contains the names, identifiers, and other information about the system metrics. -
V$METRIC
andV$METRIC_HISTORY
views contain system-level metric values in memory.
See Also: Oracle Database Reference for information on static data dictionary views and dynamic performance views |
Monitoring the Database Using Trace Files and the Alert File
Each server and background process can write to an associated trace file. When an internal error is detected by a process, it dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, Other information is for Oracle Support Services. Trace file information is also used to tune applications and instances.The alert file, or alert log, is a special trace file. The alert file of a database is a chronological log of messages and errors, and includes the following items:
- All internal errors (
ORA-600
), block corruption errors (ORA-1578
), and deadlock errors (ORA-60
) that occur - Administrative operations, such as
CREATE
,ALTER
, andDROP
statements andSTARTUP
,SHUTDOWN
, andARCHIVELOG
statements - Messages and errors relating to the functions of shared server and dispatcher processes
- Errors occurring during the automatic refresh of a materialized view
- The values of all initialization parameters that had nondefault values at the time the database and instance start
Initialization parameters controlling the location and size of trace files are:
-
BACKGROUND_DUMP_DEST
-
USER_DUMP_DEST
-
MAX_DUMP_FILE_SIZE
See Also: Oracle Database Reference for information about initialization parameters that control the writing to trace files |
Using the Trace Files
Check the alert file and other trace files of an instance periodically to learn whether the background processes have encountered errors. For example, when the log writer process (LGWR) cannot write to a member of a log group, an error message indicating the nature of the problem is written to the LGWR trace file and the database alert file. Such an error message means that a media or I/O problem has occurred and should be corrected immediately.Oracle Database also writes values of initialization parameters to the alert file, in addition to other important statistics.
Specifying the Location of Trace Files
All trace files for background processes and the alert file are written to the directory specified by the initialization parameterBACKGROUND_DUMP_DEST
. All trace files for server processes are written to the directory specified by the initialization parameter USER_DUMP_DEST
. The names of trace files are operating system specific, but each file usually includes the name of the process writing the file (such as LGWR and RECO).See Also: Your operating system specific Oracle documentation for information about the names of trace files |
Controlling the Size of Trace Files
You can control the maximum size of all trace files (excluding the alert file) using the initialization parameterMAX_DUMP_FILE_SIZE
, which limits the file to the specified number of operating system blocks. To control the size of an alert file, you must manually delete the file when you no longer need it. Otherwise the database continues to append to the file.You can safely delete the alert file while the instance is running, although you should consider making an archived copy of it first. This archived copy could prove valuable if you should have a future problem that requires investigating the history of an instance.
Controlling When Oracle Database Writes to Trace Files
Background processes always write to a trace file when appropriate. In the case of the ARCn background process, it is possible, through an initialization parameter, to control the amount and type of trace information that is produced. This behavior is described in "Controlling Trace Output Generated by the Archivelog Process". Other background processes do not have this flexibility.Trace files are written on behalf of server processes whenever internal errors occur. Additionally, setting the initialization parameter
SQL_TRACE = TRUE
causes the SQL trace facility to generate performance statistics for the processing of all SQL statements for an instance and write them to the USER_DUMP_DEST
directory.Optionally, you can request that trace files be generated for server processes. Regardless of the current value of the
SQL_TRACE
initialization parameter, each session can enable or disable trace logging on behalf of the associated server process by using the SQL statement ALTER SESSION SET SQL_TRACE
. This example enables the SQL trace facility for a specific session:ALTER SESSION SET SQL_TRACE TRUE;
Caution: The SQL trace facility for server processes can cause significant system overhead resulting in severe performance impact, so you should enable this feature only when collecting statistics. |
DBMS_SESSION
or the DBMS_MONITOR
package if you want to control SQL tracing for a session.Reading the Trace File for Shared Server Sessions
If shared server is enabled, each session using a dispatcher is routed to a shared server process, and trace information is written to the server trace file only if the session has enabled tracing (or if an error is encountered). Therefore, to track tracing for a specific session that connects using a dispatcher, you might have to explore several shared server trace files. To help you, Oracle provides a command line utility program,trcsess
, which consolidates all trace information pertaining to a user session in one place and orders the information by time.See Also: Oracle Database Performance Tuning Guide for information about using the SQL trace facility and using TKPROF and trcsess to interpret the generated trace files |
Monitoring Locks
Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource. The resources can be either user objects, such as tables and rows, or system objects not visible to users, such as shared data structures in memory and data dictionary rows. Oracle Database automatically obtains and manages necessary locks when executing SQL statements, so you need not be concerned with such details. However, the database also lets you lock data manually.A deadlock can occur when two or more users are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work. Oracle Database automatically detects deadlock situations and resolves them by rolling back one of the statements involved in the deadlock, thereby releasing one set of the conflicting row locks.
Oracle Database is designed to avoid deadlocks, and they are not common. Most often they occur when transactions explicitly override the default locking of the database. Deadlocks can affect the performance of your database, so Oracle provides some scripts and views that enable you to monitor locks.
The
utllockt.sql
script displays, in a tree fashion, the sessions in the system that are waiting for locks and the locks that they are waiting for. The location of this script file is operating system dependent.A second script,
catblock.sql
, creates the lock views that utllockt.sql
needs, so you must run it before running utllockt.sql
.The following views can help you to monitor locks:
View | Description |
---|---|
V$LOCK | Lists the locks currently held by Oracle Database and outstanding requests for a lock or latch |
DBA_BLOCKERS | Displays a session if it is holding a lock on an object for which another session is waiting |
DBA_WAITERS | Displays a session if it is waiting for a locked object |
DBA_DDL_LOCKS | Lists all DDL locks held in the database and all outstanding requests for a DDL lock |
DBA_DML_LOCKS | Lists all DML locks held in the database and all outstanding requests for a DML lock |
DBA_LOCK | Lists all locks or latches held in the database and all outstanding requests for a lock or latch |
DBA_LOCK_INTERNAL | Displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch |
See Also:
|
Monitoring Wait Events
Wait events are statistics that are incremented by a server process to indicate that it had to wait for an event to complete before being able to continue processing. A session could wait for a variety of reasons, including waiting for more input, waiting for the operating system to complete a service such as a disk write, or it could wait for a lock or latch.When a session is waiting for resources, it is not doing any useful work. A large number of waits is a source of concern. Wait event data reveals various symptoms of problems that might be affecting performance, such as latch contention, buffer contention, and I/O contention.
Oracle provides several views that display wait event statistics. A discussion of these views and their role in instance tuning is contained in Oracle Database Performance Tuning Guide.
Process and Session Views
This section lists some of the data dictionary views that you can use to monitor an Oracle Database instance. These views are general in their scope. Other views, more specific to a process, are discussed in the section of this book where the process is described.View | Description |
---|---|
V$PROCESS | Contains information about the currently active processes |
V$LOCKED_OBJECT | Lists all locks acquired by every transaction on the system |
V$SESSION | Lists session information for each current session |
V$SESS_IO | Contains I/O statistics for each user session |
V$SESSION_LONGOPS | 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. More operations are added for every Oracle Database release. |
V$SESSION_WAIT | Lists the resources or events for which active sessions are waiting |
V$SYSSTAT | Contains session statistics |
V$RESOURCE_LIMIT | Provides information about current and maximum global resource utilization for some system resources |
V$SQLAREA | Contains statistics about shared SQL area and contains one row for each SQL string. Also provides statistics about SQL statements that are in memory, parsed, and ready for execution |
V$LATCH | Contains statistics for nonparent latches and summary statistics for parent latches |