Sybex OCA Oracle 10g Administration I Study Guide phần 6 potx

288 Chapter 5 Oracle Shared Server

Managing Shared Server

After you configure the Oracle Shared Server parameters, you need to understand how to view

information about Oracle Shared Server. Oracle provides a set of dynamic performance views that

you can use to gather information about the Oracle Shared Server configuration and the perfor￾mance of the Oracle Shared Server. You can also gather information about Oracle Shared Server

connections by using the lsnrctl utility.

In the following sections, we will explain how to display information about Oracle Shared

Server connections using the listener utility and discuss the various dynamic performance views

used to manage Shared Server.

Displaying Information about Shared Server Connections

Using the Listener Utility

You can use the lsnrctl command-line listener utility to display information about the dis￾patcher processes. Remember from the previous section that the Oracle background process

PMON registers dispatcher information with the listener. The listener keeps track of the current

connection load for all the dispatchers.

Use the lsnrctl services query to view information about dispatchers. The following

example shows a listener listening for two TCP/IP dispatchers:

D:\>lsnrctl services

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 21-APR-2004

20:50:35

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MJW01)

(PORT=1521)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0

LOCAL SERVER

Instance "MJW", status READY, has 3 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

4367.book Page 288 Monday, October 4, 2004 2:19 PM

Managing Shared Server 289

"D001" established:11 refused:1 current:1 max:1002 state:ready

DISPATCHER <machine: MJW01, pid: 504>

(ADDRESS=(PROTOCOL=tcp)(HOST=MJW01)

(PORT=4152))

"D000" established:15 refused:3 current:2 max:1002 state:ready

DISPATCHER <machine: MJW01, pid: 3846>

(ADDRESS=(PROTOCOL=tcp)(HOST=MJW01)

(PORT=3845))

The command completed successfully

Notice that the listing displays how many connections each dispatcher is managing, the lis￾tening location of the dispatcher, and the process ID of the dispatcher. This example has three

active connections, two belong to dispatcher D000 and one belongs to dispatcher D001. The

display also shows how many total client connections were established and how many were

refused by each dispatcher since the time it was started. This summary information can be help￾ful when looking at how well the connections are balanced across all the dispatchers. It also can

be helpful to see how many connections were refused. A connection can be refused if a user sup￾plies an invalid user ID or password or reaches the MAX_SHARED_SERVER limit.

Using Dynamic Performance Views for Shared Server

Oracle provides dynamic performance views, also known as the V$ views that provide the DBA

with a wealth of system statistics and performance information, including information about

Oracle Shared Server. These views provide information about the number of dispatchers and

shared servers configured, the activity among the shared servers and dispatchers, the activity in

the request and response queue, as well as the clients that are connected with shared server con￾nections. We will look at the dynamic performance views in the following sections.

For a complete listing of all the column definitions for the V$ views, consult the

Oracle Database Reference 10g Release 1 (10.1), Part Number B10755-01.

Using the V$DISPATCHER Dictionary View

The V$DISPATCHER view contains information about the dispatchers, including dispatcher

activity, the number of connections the dispatchers are currently handling, and the total number

of connections each dispatcher has handled since instance startup.

Here is sample output from the V$DISPATCHER view:

SQL> select name,status,messages,idle,busy,bytes,breaks from

2 v$dispatcher

4367.book Page 289 Monday, October 4, 2004 2:19 PM

290 Chapter 5 Oracle Shared Server

NAME STATUS MESSAGES IDLE BUSY BYTES BREAKS

---- ------ --------- ------- ---- ------ ------

D000 WAIT 168 389645 108 12435 0

D001 WAIT 94 389668 48 6940 0

This example lists two dispatchers: D000 and D001. The WAIT status indicates that the dis￾patchers are both idle and waiting for a client request to process. The IDLE and BUSY columns

display information on how many hundredths of a second each dispatcher has either been wait￾ing to process a client request or actually processing a client request. The MESSAGES column dis￾plays the number of messages that a dispatcher has processed since instance startup and the

BYTES column is the total size of all messages processed by the dispatcher. Finally, the BREAKS

column displays the total number of breaks that the dispatcher has handled since instance start￾up. A break is an interrupt passed from a client that allows a transaction to be stopped prior to

completion. For example, pressing Ctrl+C in some applications will cause a break request to be

generated and passed to the dispatcher process. SQL*Plus is an example of an application that

will cause a break request to be sent by pressing this keyboard combination.

Using the V$DISPATCHER_CONFIG Dictionary View

The V$DISPATCHER_CONFIG view is a new Oracle 10g view that contains configuration infor￾mation about the dispatchers. This view summarizes the dispatcher configuration and gives

information such as the protocol, the listener or listening address of the dispatcher, maximum

settings for connection and sessions, and service names information.

The following sample shows a system configured with three dispatchers listening for TCP/IP

connections to the orcl service. (The maximum connections and sessions are set at the system

at 1,002.)

SQL> select conf_indx,dispatchers,connections,sessions “SESS”,service

2 from v$dispatcher_config

3 where network like ‘%TCP%’

CONF_INDX DISPATCHERS CONNECTIONS SESS SERVICE

--------- ----------- ----------- ------ -------

0 3 1002 1002 orcl

Using the V$DISPATCHER_RATE Dictionary View

The V$DISPATCHER_RATE view shows statistics for the dispatchers, such as the average num￾ber of bytes processed, the maximum number of inbound and outbound connections, and the

average rate of bytes processed per client connection. The columns in the table that begin with

CUR show current statistics. Columns that begin with AVG or MAX show historical statistics

taken at some time interval. The time interval is typically measured in hundredths of a second.

The scale measurement periods used for each of the column types is contained in the columns

that begin with SCALE. This information can be useful when you are taking load measure￾ments for the dispatchers.

4367.book Page 290 Monday, October 4, 2004 2:19 PM

Managing Shared Server 291

Here is a sample of the output from this view.

SQL>select name,cur_event_rate,cur_msg_rate,

cur_svr_byte_rate

from v$dispatcher_rate

NAME CUR_EVENT_RATE CUR_MSG_RATE CUR_SVR_BYTE_RATE

---- -------------- ------------ -----------------

D000 5300 4 0 0

D001 5205 3 0 1

This code example shows two dispatchers, D000 and D001, running on the database. The

CUR_EVENT_RATE column is a measure of how quickly dispatchers are responding to client

requests. From this example, we can determine that client requests are being responded to at a

rate of 5,300 per minute for D000 and 5,205 per minute for D001. This is not the actual number

of events handled, but the measure of the rate of requests per minute that the dispatcher is han￾dling. The CUR_MSG_RATE is a measure of how quickly the dispatcher has been sending a client

request to the Shared Servers. We can see in this example that on average D000 has been passing

four requests per second to the Shared Servers for processing and that D001 has been passing

three requests per minute to the Shared Servers for processing. Notice the scale for the CUR_

EVENT_RATE is events per minute and that the scale for CUR_MSG_RATE is messages per second.

These measures give some indication as to how quickly the Shared Servers and dispatchers are

responding to client requests. The other columns provide information on the maximum and

average rates at which the dispatcher is servicing client requests.

Using the V$QUEUE Dictionary View

The V$QUEUE view contains information about the request and response queues, such as how

long requests are waiting in the queues. This information is valuable when you are trying to

determine if more shared servers are needed.

The following example shows the COMMON request queue and two response queues:

SQL> select * from v$queue;

PADDR TYPE QUEUED WAIT TOTALQ

-------- ---------- --------- --------- ---------

00 COMMON 0 0 152

03C6C244 DISPATCHER 0 0 91

03C6C534 DISPATCHER 0 0 71

The PADDR column lists the address of the process that owns the queue. This example shows

that no items are waiting in the queue because the QUEUED column is zero. We also have not

experienced any queue waits because all WAIT values are zero. We would want to make sure that

the WAIT column stays close to zero so no processes are waiting in the queues. The TOTALQ col￾umn represents the total number of messages that have ever been in the queue.

4367.book Page 291 Monday, October 4, 2004 2:19 PM

292 Chapter 5 Oracle Shared Server

Using the V$CIRCUIT Dictionary View

The V$CIRCUIT dictionary view displays information about Oracle Shared Server virtual cir￾cuits, such as the volume of information that has passed between the client and the dispatcher

and the current status of the client connection. The SADDR column displays the session address

for the connected session. This can be joined to the V$SESSION view to display information

about the user to whom this connection belongs.

Here is a sample output from this view:

SQL> select circuit,dispatcher,server,waiter WTR,

2 status,queue,bytes from v$circuit;

CIRCUIT DISPATCH SERVER WTR STATUS QUEUE BYTES SADDR

-------- -------- -------- --- ------ ------ ----- ------

03E2A624 03C6C244 00 00 NORMAL NONE 47330 03C7AB68

03E2A724 03C6C534 03C6BC64 00 NORMAL SERVER 43572 03C79BE8

You can see from this example that two active connections are being managed by the dis￾patchers. The value of SERVER in the QUEUE column displayed for the second circuit tells us that

the circuit is currently active and processing a user request.

Using the V$SHARED_SERVER Dictionary View

The V$SHARED_SERVER view contains information about the shared server processes. It displays

information about the number of requests and the amount of information processed by the shared

servers. It also indicates the status of the shared server (that is, whether it is active or idle).

Here is a sample output from this view:

SQL> select name,status,messages,bytes,idle,busy,

requests from v$shared_server;

NAME STATUS MESSAGES BYTES IDLE BUSY REQUESTS

---- ------ -------- ----- ----- ---- --------

S000 EXEC 372 86939 98472 300 175

S001 EXEC 26 9851 98703 38 13

The sample depicts two shared servers: S000 and S001. The status of EXEC signifies that the

shared servers are currently executing SQL statements. You can see that S000 has had more

activity than S001. This would typically be the case in a lightly loaded system. S000 is the first

shared server in our sequence and will be the first shared server used when a client request comes

in if it is available. If the S000 shared server is not available, S001 is used.

Using the V$SHARED_SERVER_MONITOR Dictionary View

The V$SHARED_SERVER_MONITOR view contains information that can assist in tuning the Oracle

Shared Server, including the maximum number of concurrent connections attained since

instance startup and the total number of servers started since instance startup.

4367.book Page 292 Monday, October 4, 2004 2:19 PM