Learning Dynamic Performance Table (2)--v$sesstat

Learning Dynamic Performance Table

Second article --v$sesstat 2007.5.25

As described in Oracleonlinebook, V$sesstat stores detailed resource usage statistics for the session from login to logout.

Similar to V$sysstat, this view stores statistics for the following categories:

• Statistics of the number of occurrences, such as the number of user submissions.
• Total column of data generation, access, or operation (e.g. redo size)
• The amount of time it takes to perform an operation, such as session CPU consumption (if the Timed_statistics value is true)

Attention:

If the initial parameter statistics_level is set to typical or all, the time statistics are automatically collected by the database if Statistics_level is set to basic, you must set the Timed_ The statistics value is true to open the collection feature.

If you have set db_cache_advice,timed_statistics or timed_os_statistics, or in the initial parameter file or using the Alter_system or alter SESSION, Then the value you set will override the value of Statistics_level.

The differences between V$sysstat and V$sesstat are as follows:

• V$sesstat only saves session data, while V$sysstat saves all sessions cumulative values.
• V$sesstat is just staging data, and the data is emptied after the session exits. V$sysstat are cumulative and only emptied if the instance is shutdown.
• V$sesstat does not include the name of the statistic, and it must be obtained with a v$sysstat or v$statname connection query if you want to obtain a statistical item name.

V$sesstat can be used to find the following types of session:

• High resource occupancy
• High average resource occupancy ratio (resource utilization after login)
• Default resource consumption ratio (two snapshots)

in V$sesstat usage Statistics in

The statistical reference in most v$sesstat is a subset of the V$sysstat description, including session logical reads, CPU used by this session, DB block changes, redo size, physical Writes, parse count (hard), parse count (total), sorts (memory), and sorts (disk).

V$sesstat Common Column Descriptions

• Sid:session Unique ID
• Statistic#: Resource Unique ID
• VALUE: Resource Usage

Example 1 : The following identifies the current session The highest logical in the and physical I/O ratios.

The following SQL statement shows all session logic, physical read ratio (per second) connected to the database. The logical and physical I/O ratios are calculated from the time consumption after logging in. It may not be accurate for long-term operations that sessions connect to a database, but it's enough to do an example.

Get the statistic# value of the session logical read and the physical read statistic item first:

SELECT name, statistic#

From V$statname

WHERE name in (' Session logical reads ', ' physical reads ');

NAME statistic#

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

Session Logical Reads 9

Physical reads 40

The following statements are executed by the statistic# value obtained above:

SELECT Ses.sid

, DECODE (Ses.action,null, ' online ', ' batch ') "User"

, MAX (DECODE (sta.statistic#,9,sta.value,0))

/greatest (3600*24* (Sysdate-ses.logon_time), 1) "Log io/s"

, MAX (DECODE (sta.statistic#,40,sta.value,0))

/greatest (3600*24* (Sysdate-ses.logon_time), 1) "Phy io/s"

, 60*24* (sysdate-ses.logon_time) "Minutes"

From v$session ses

, V$sesstat STA

WHERE ses.status = ' ACTIVE '

and Sta.sid = Ses.sid

and sta.statistic# in (9,40)

GROUP by Ses.sid, Ses.action, Ses.logon_time

ORDER by

SUM (DECODE (Sta.statistic#,40,100*sta.value,sta.value))

/Greatest (3600*24* (Sysdate-ses.logon_time), 1) DESC;

SID User Log io/s Phy io/s Minutes

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

1951 Batch 291 257.3 1

470 Online 6,161 62.9 0

730 Batch 7,568 43.2 197

2153 Online 1,482 98.9 10

2386 Batch 7,620 35.6 35

1815 Batch 7,503 35.5 26

1965 Online 4,879 42.9 19

1668 online 4,318 44.5 1

1142 Online 955 69.2 35

1855 Batch 573 70.5 8

1971 Online 1,138 56.6 1

1323 Online 3,263 32.4 5

1479 Batch 2,857 35.1 3

421 Online 1,322 46.8 15

2405 Online 258 50.4 8

Example 2 : Another example through V$sesstat and V$statname connection query for a SID the information.

Select A.*,b.name

From V$sesstat A,v$statname b

where a.sid=10 and a.statistic#=b.statistic#;

Learning Dynamic Performance Table (2)--v$sesstat