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

Source: Internet
Author: User
Tags sorts

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)


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


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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.