Page 68
query given below from decimal to hexadecimal, you can match the Windows NT thread ID with the background process from the Oracle side.
SELECT spid, name FROM v$process, v$bgprocess WHERE addr = paddr;
(See Appendix B, "Oracle on Windows NT," for more information on tuning and tracking Windows NT background threads.)
Numerous dynamic performance views are available to the DBA to display instance information. These views are invaluable when attempting to discover the current state of the database instance, and troubleshoot problems related to the instance.
Monitoring Database Connections Both user and background processes that are connected to the instance can be monitored using the v$ views. The v$process view displays information about all processes that are connected to the database, including background and user processes. V$bgprocess contains a list of all possible background processes, with an additional column, PADDR, that contains the hexadecimal address of running background processes (or 00 for those that are not running).
The columns of interest to us from the v$process table are as follows:
Column | Usage |
ADDR | Oracle Address of the Process |
PID | Oracle Process ID |
SPID | OS System Process ID |
USERNAME | OS Process Owner |
SERIAL# | Oracle Process Serial# |
TERMINAL | OS Terminal Identifier |
PROGRAM | OS Program Connection |
BACKGROUND | 1 for Background Process, NULL for User Process |
The columns of interest to us from the v$bgprocess table are as follows:
Column | Usage |
PADDR | Oracle Process Address (Same as ADDR column of v$process) |
NAME | Name of the Background Process |
DESCRIPTION | Description of the Background Process |
ERROR | Error State Code (0 for no error) |
You can display the addresses and names of all running background processes by joining the v$process and v$bgprocess table, as in the following query:
Page 69
SELECT spid, name FROM v$process, v$bgprocess WHERE paddr(+) = addr;
Information about user sessions that are connected to the database are stored in the v$session view. The v$session view contains many fields, and a great deal of valuable information can be accessed from this view.
The columns of interest from the v$session view are as follows:
Column | Usage |
SID | Session Identifier |
SERIAL# | Session Serial# |
PADDR | Address of Parent Session |
USER# | Oracle User Identifier (from the SYS.USER$ table) |
USERNAME | Oracle Username |
COMMAND | Current Command in Progress for this Session. For number to command translations, see the sys.audit_actions table |
STATUS | Status of the Session (ACTIVE, INACTIVE, KILLED) |
SERVER | Type of Server Connection the Session Has DEDICATED, SHARED, PSEDUO, or NONE) |
OSUSER | OS Username the Connection Has Been Made From |
PROGRAM | OS Program Making the Connection into the Database |
TERMINAL | Type of Terminal the Connection Is Made From |
TYPE | Type of Session (BACKGROUND or USER) |
SQL_HASH_VALUE and SQL_ADDRESS | Used to Uniquely Identify the Currently Executing SQL Statement |
The following query displays important information on connected processes. It also demonstrates the manner in which the process views relate to each other:
col bgproc format a6 heading `BGProc' col action format a10 heading `DB Action' col program format a10 col username format a8 col terminal format a10
Page 70
SELECT b.name bgproc, p.spid, s.sid, p.serial#, s.osuser, s.username, s.terminal, DECODE(a.name, `UNKNOWN', `-----', a.name) action FROM v$process p, v$session s, v$bgprocess b, sys.audit_actions a WHERE p.addr=s.paddr(+) AND b.paddr(+) = s.paddr AND a.action = NVL(s.action, 0) ORDER BY sid;
By querying the v$access view, you can display information on what database objects users are currently accessing. This is useful when trying to figure out what a third-party application or undocumented procedure is doing, and can also be used to resolve security problems. By using a DBA account to run an application or procedure that is giving you security problems, you can determine the exact objects to which security should be granted.
Finally, the v$mts view contains tracking information for shared server processes. This view contains columns for maximum connections, servers started, servers terminated, and servers highwater.
Monitoring the Shared SQL Area Often it is useful to be able to look into the RDBMS engine and see what SQL statements are being executed. The v$sqlarea view contains information on SQL statements in the shared SQL area, including the text of SQL statements executed, the number of users accessing the statements, disk blocks and memory blocks accessed while executing the statement, and other information.
NOTE |
The disk_reads and buffer_gets columns that are found in v$sqlarea track the number of blocks that are read from disk and from the buffer cache. These two columns are quick and easy ways to find queries that are utilizing large amounts of database resources. |
The v$open_cursor view is also useful to investigate cursors that have not yet been closed. The following query displays all open cursors for a given user's SID:
SELECT b.piece, a.sql_text FROM v$open_cursor a, v$sqltext b WHERE a.sid = &SID and a.address = b.address and a.hash_value = b.hash_value ORDER BY b.address, b.hash_value, b.piece asc;
The v$sqltext view can also be used to determine what SQL statements are passed to the database engine. Unlike v$sqlarea, which only stores the first 80 characters of the SQL statement, this view holds the entire SQL statement. The v$sqltext_with_newlines view is identical to
Page 71
NOTE |
v$sqltext, except that the newline characters in the SQL statements have been left in place. |
The SQL statements stored in v$sqltext are split into pieces. To retrieve the entire statement, you have to retrieve all the parts of the SQL statement and order by the PIECE column.
Monitoring the SGA There are two v$ views available that provide information about the operation of the SGA. The v$sga view displays the size (in bytes) of each major component of the SGA, including the redo log cache, the database buffer cache, and the shared pool. The v$sgastat contains much more interesting information. Within this view you find the specific size for each individual memory structure contained in the SGA, including the memory set aside for stack space and PL/SQL variables and stacks. You can also query this view to find the amount of free memory available in the SGA:
SELECT bytes FROM v$sgastat WHERE name = `free memory';
Monitoring the Library and Dictionary Cache Two views exist that contain information regarding the library and data dictionary cache. v$librarycache contains library cache performance information for each type of object in the library cache. The v$rowcache view contains performance information for the data dictionary cache. (See Chapter 31 for more information on these views and the information contained in them.)
Monitoring the Parallel Query Processes The v$pq_sysstat and v$pq_tqstat views contain information on the parallel server processes and their behavior. Query v$pq_sysstat to display current runtime information on parallel query servers, such as the number of query servers busy and idle and dynamic server creation and termination statistics. The v$pq_tqstat view contains information on queries that have previously run that used parallel query servers. (See Chapter 26, "Parallel Query Management," for more information on tracking the parallel servers.)
Monitoring the Archiver Processes Archiver activity is stored in the v$archive view. You can retrieve information on the archived logs written by ARCH from this view. (For an explanation of the columns in this view, see Chapter 27.)
Monitoring the Multi-Threaded Server Processes The v$mts, v$dispatcher, and v$shared_server views contain information on the status of the MTS processes and memory structures. v$mts contains tracking information on the shared server processes such as the number of servers started, terminated, and the highwater value for running servers. v$dispatcher contains information on the dispatcher processes running. From this view you can query the name, supported protocol, number of bytes processed, number of messages processed, current status, and other runtime information relating to the dispatcher processes. The v$shared_server view provides the same type of information, but for the shared running shared server processes.
(See Chapter 19, "Oracle Networking Fundamentals," for more information on setting up and tuning the shared server and dispatcher processes.)