Learning Dynamic Performance Table (2)-V $ sesstat

According to the description in oracleonlinebook, V $ sesstat stores detailed resource usage statistics for sessions from login to logout.

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

Event Occurrence Count statistics, such as the number of user submissions.
Total column for data generation, access, or operation (for example, redo size)
The time spent on executing the operation, such as session CPU usage (if the timed_statistics value is true)
Note:
If the initial parameter statistics_level is set to typical or all, time statistics are automatically collected by the database. If statistics_level is set to basic, you must set timed_statistics to true to enable the collection function.

If you have set db_cache_advice, timed_statistics, or timed_ OS _statistics, or use alter_system or alter session in the initial parameter file, the value you set will overwrite the value of statistics_level.

The differences between V $ sysstat and V $ sesstat are as follows:
V $ sesstat only stores session data, while V $ sysstat stores the cumulative values of all sessions.
V $ sesstat only stores temporary data. After the session exits, the data is cleared. V $ sysstat is cumulative and is cleared only when the instance is shut down.
V $ sesstat does not include the statistical item name. To obtain the statistical item name, you must connect to V $ sysstat or V $ statname for query.

V $ sesstat can be used to find the following types of sessions:
High resource usage
High Average resource usage ratio (resource usage after login)
Default resource usage ratio (between two snapshots)

Use Statistics in V $ sesstat
The statistical reference in most v $ sesstat is a subset described by V $ sysstat, 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 ).

Description of common columns of V $ sesstatSid: unique session ID
Statistic #: unique resource ID
Value: resource usage

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

The following SQL statement shows all session logic and physical read ratio (per second) connected to the database ). The ratio of logical to physical I/O is calculated based on the time consumption after login. Long-period operations such as Session connections to the database may not be accurate enough, but an example is sufficient.

First obtain the statistic # value of the session logical read and physical read statistical items:
Select name, statistic #
From v $ statname
Where name in ('session logical reads', 'Physical reads ');
Name statistic #
----------------------------------------
Session logical reads 9
Physical reads 40

Use the statistic # value obtained above to execute the following statement:

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
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: query the information of a Sid through the V $ sesstat and V $ statname connections.
Select a. *, B. Name
From v $ sesstat A, V $ statname B
Where a. Sid = 10 and A. Statistic # = B. Statistic #;