Session Stat Summary Interval (PI_PIS2)
- Organization of this page
Function
The Session Stat Summary Interval (PI_PIS2) record stores performance data, taken at specific intervals, about each session and performance indicator of an instance. PFM - Agent for Oracle creates one record for each session in an instance. This is a multi-instance record.
If you cannot view the performance data in this record, create Oracle's static data dictionary view DBA_WAITERS. To create this view, you must execute the CATBLOCK.SQL script that is provided by Oracle.
This record is useful when you want to monitor session information in the monitored Oracle Database that has a long connection period, such as a connection pool. However, if you repeatedly connect and disconnect during system operation, you can monitor session information using the Session Statistics Summary (PD_PDS2) record. If sessions are frequently connected and disconnected, valid data cannot be obtained under the following circumstance: when attempting to obtain the difference for values of delta items between a current connection and a previous connection, information derived from another session might be substituted for the previous connection values because the key field is PI_PIS2_SID.
Default and changeable values
Item |
Default value |
Changeable |
---|---|---|
Collection Interval |
600 |
Y |
Collection Offset |
105 |
Y |
Log |
No |
Y |
LOGIF |
(Blank) |
Y |
Over 10 Sec Collection Time |
No |
N |
ODBC key fields
PI_PIS2_SID
Lifetime
From the start to the end of a session
Record size
-
Fixed part: 678 bytes
-
Variable part: 904 bytes
Fields
PFM - View name (PFM - Manager name) |
Description |
Summary |
Format |
Delta |
Supported version |
Data source |
---|---|---|---|---|---|---|
Block Changes/Tran (BLOCK_CHANGES_PER_TRANSACTION) |
Rate at which each transaction executed database manipulation language (DML) statements#2 |
AVG |
double |
No |
All |
db block changes / user commits |
Block Visits/Tran (BLOCK_VISITS_PER_TRANSACTION) |
Number of work database read operations executed per transaction#2 |
AVG |
double |
No |
All |
(db block gets + consistent gets) / user commits |
Blocking Locks (BLOCKING_LOCKS) |
Number of locks owned by the session that are blocking another lock.#2 Correct values cannot be collected for this field, and 0 is always displayed. |
AVG |
double |
No |
All |
Agent Collector |
Cache Hit % (CACHE_HIT_PERCENTAGE) |
Buffer cache usage#2 |
AVG |
double |
No |
All |
(1 - (physical reads cache / (consistent gets from cache + db block gets from cache))) * 100 |
Calls/Tran (CALLS_PER_TRANSACTION) |
Rate at which client requests were executed per transaction#2 |
AVG |
double |
No |
All |
user calls / user commits |
Changed Block % (CHANGED_BLOCK_PERCENTAGE) |
Ratio (as a percent) of the number of queries that change data (insertion, update, and deletion) to the number of queries (search, insertion, update, deletion) executed on the database#2 |
AVG |
double |
No |
All |
(db block changes / (block gets + consistent gets)) * 100 |
Consistent Change % (CONSISTENT_CHANGE_PERCENTAGE) |
Ratio (as a percent) of the number of times rollback entries were applied to maintain read consistency to the number of read consistency requests by the application#2 |
AVG |
double |
No |
All |
(consistent changes / consistent gets) * 100 |
Continued Row % (CONTINUED_ROW_PERCENTAGE) |
Percentage of rows obtained that were longer than one block or had been moved#2 |
AVG |
double |
No |
All |
(table fetch continued row / (table fetch by rowid + table scan rows gotten)) * 100 |
Deadlocks (LOCK_DEADLOCKS) |
Number of process deadlocks caused by enqueuing (locking) of DML processing#2 |
AVG |
double |
Yes |
All |
V$SESSTAT.VALUE |
Disk Sorts (SORTS_DISK) |
Number of disk sort operations#2 |
AVG |
double |
Yes |
All |
V$SESSTAT.VALUE |
Lock Conversions (LOCK_CONVERSIONS) |
Number of enqueues (locks) whose mode changed (such as from SHARE to EXCLUSIVE)#2 |
AVG |
double |
Yes |
All |
V$SESSTAT.VALUE |
Lock Releases (LOCK_RELEASES) |
Number of times enqueuing (locking) was released (this statistic is the same as the lock request count)#2 |
AVG |
double |
Yes |
All |
SUM(V$SESSTAT.VALUE) |
Lock Requests (LOCK_REQUESTS) |
Number of times enqueuing (locking) was requested#2 |
AVG |
double |
Yes |
All |
V$SESSTAT.VALUE |
Lock Timeouts (LOCK_TIMEOUTS) |
Number of times enqueuing (locking) request was not permitted within the specified wait time#2 |
AVG |
double |
Yes |
All |
V$SESSTAT.VALUE |
Lock Waits (LOCK_WAITS) |
Number of times lock request was placed in wait status (the number of lock requests not placed in wait status equals the number of lock requests minus the number of enqueuing waits)#2 |
AVG |
double |
Yes |
All |
V$SESSTAT.VALUE |
Logical Reads (LOGICAL_READS) |
Sum of the number of logical read operations in read consistency mode and the number of requests to the current copy of the block#2 |
AVG |
double |
Yes |
All |
db block gets + consistent gets |
Memory Sorts (SORTS_MEMORY) |
Number of sort operations in memory#2 |
AVG |
double |
Yes |
All |
V$SESSTAT.VALUE |
Non-Index Lookups % (NON_INDEX_LOOKUPS) |
Percentage of full table scans for which no caching is performed#2 |
AVG |
double |
No |
All |
(table scans (long tables) / (table scans (short tables) + table scans (long tables))) * 100 |
PGA Memory (PGA_MEMORY) |
Amount of PGA memory in use during data collection (in bytes)#2 |
AVG |
double |
No |
All |
V$SESSTAT.VALUE |
Physical Writes (PHYSICAL_WRITES) |
Number of physical write operations onto disk by DBWR#2 |
AVG |
double |
Yes |
All |
V$SESSTAT.VALUE |
Physical Reads (PHYSICAL_READS) |
Number of times a database block was actually read from disk#2 |
AVG |
double |
Yes |
All |
physical reads - physical reads direct - physical reads direct (lob) |
Program (PROGRAM) |
OS's program name#1 |
COPY |
string(48) |
No |
All |
V$SESSION.PROGRAM |
Record Time (RECORD_TIME) |
Collection termination time for the performance data stored in the record#1 |
COPY |
time_t |
No |
All |
Agent Collector |
Record Type (INPUT_RECORD_TYPE) |
Record name (always PIS2)#1 |
COPY |
string(4) |
No |
All |
Agent Collector |
Recursive Calls (RECURSIVE_CALLS) |
Number of user calls processed#2 |
AVG |
double |
Yes |
All |
V$SESSTAT.VALUE |
Recursive to User Call % (RECURSIVE_TO_USER_CALL_PERCENTAGE) |
Correct values cannot be collected for this field. Percentage indicating overhead#2 |
AVG |
double |
No |
All |
(recursive calls / user calls) * 100 |
Redo Log Space Requests (REDO_LOG_SPACE_REQUESTS) |
Number of times that, because the active log file was full, the Oracle server had to wait for disk space to be allocated for a REDO log entry.#2 |
AVG |
double |
Yes |
All |
V$SESSTAT.VALUE |
Redo Log Space Wait % (REDO_LOG_SPACE_WAIT_PERCENTAGE) |
Wait rate for allocations of the disk area to the REDO log entry#2 |
AVG |
double |
No |
All |
(redo log space requests / redo entries) * 100 |
Row Source % (ROW_SOURCE_PERCENTAGE) |
Percentage of obtained rows that were obtained by full-table scans#2 |
AVG |
double |
No |
All |
(table scan rows gotten / (table fetch by rowid + table scan rows gotten)) * 100 |
SID (SID) |
Session ID#1 |
COPY |
ulong |
No |
All |
V$SESSION.SID |
SQL Net Bytes Rcvd (SQL_NET_BYTES_RECEIVED) |
Number of bytes received from clients via SQL*Net#2 |
AVG |
double |
Yes |
All |
V$SESSTAT.VALUE |
SQL Net Bytes Sent (SQL_NET_BYTES_SENT) |
Number of bytes sent to clients via SQL*Net#2 |
AVG |
double |
Yes |
All |
V$SESSTAT.VALUE |
Session Cursor Cache Count (SESSION_CURSOR_CACHE_COUNT) |
Number of session cursors cached (the maximum number of cursors that can be cached is determined by the SESSION_CACHED_CURSORS parameter in the init.ora file)#2 |
AVG |
double |
Yes |
All |
V$SESSTAT.VALUE |
Session Cursor Cache Hit % (SESSION_CURSOR_CACHE_HIT_PERCENTAGE) |
Percentage of the number of session cursors stored in the cache that were found in the cache after an access was made#2 |
AVG |
double |
No |
All |
(session cursor cache hits / session cursor cache count) * 100 |
Session Cursor Cache Hits (SESSION_CURSOR_CACHE_HITS) |
Recorded session cursor cache hit count#2 |
AVG |
double |
Yes |
All |
V$SESSTAT.VALUE |
Sort Overflow % (SORT_OVERFLOW_PERCENTAGE) |
Percentage of sorts that used temporary segments#2 |
AVG |
double |
No |
All |
(sorts (disk) / (sorts (memory) + sorts (disk))) * 100 |
Start Time (START_TIME) |
Collection start time for the performance data stored in the record#1 |
COPY |
time_t |
No |
All |
Agent Collector |
Statement CPU (STATEMENT_CPU) |
Total CPU time used by active statements during data collection in hundredths of a second#2 |
AVG |
double |
Yes |
All |
V$SESSTAT.VALUE |
UGA Memory (UGA_MEMORY) |
Amount of session memory used (in bytes)#2 |
AVG |
double |
No |
All |
V$SESSTAT.VALUE |
User (USERNAME) |
Oracle user name#1 |
COPY |
string(30) |
No |
All |
V$SESSION.USERNAME |
User Calls (USER_CALLS) |
Number of user calls processed#2 |
AVG |
double |
Yes |
All |
V$SESSTAT.VALUE |
User Calls / Parse (USER_CALLS_PER_PARSE) |
Percentage indicating how well the application is managing the context area#2 |
AVG |
double |
No |
All |
user calls / parse count (total) |
User Commits (USER_COMMITS) |
Number of transactions#2 |
AVG |
double |
Yes |
All |
V$SESSTAT.VALUE |
User Rollback % (USER_ROLLBACK_PERCENTAGE) |
Percentage of application transactions that failed#2 |
AVG |
double |
No |
All |
(user rollbacks / (user commits + user rollbacks)) * 100 |
User Rollbacks (USER_ROLLBACKS) |
Number of rollbacks#2 |
AVG |
double |
Yes |
All |
V$SESSTAT.VALUE |
Waiting Locks (WAITING_LOCKS) |
Number of locks owned by another session that this session is waiting for. Correct values cannot be collected for this field, and 0 is always displayed.#2 |
AVG |
double |
No |
All |
-- |
Write % (WRITE_PERCENTAGE) |
Ratio (as a percent) of physical writes to all physical I/Os (reads and writes)#2 |
AVG |
double |
No |
All |
(physical writes / (physical reads + physical writes)) * 100 |