Summary of dynamic performance View

The dynamic performance view is summarized as follows:
1 v $ sysstat 2
2 v $ sesstat 7
3 v $ SQL 9
4 V $ SQL _PLAN 11
5 V $ SQLTEXT 13
6 V $ SQLAREA 14
7 V $ SESSION 15
8 V $ SESSION_WAIT 17
9 V $ SESSION_EVENT 20
10 V $ PROCESS 20
11 V $ LOCK 23
12 V $ LOCKED_OBJECT 27
13 V $ FILESTAT 28
14 V $ SESSION_LONGOPS 29
15 V $ LATCH 30
16 V $ LATCH_CHILDREN 33
17 V $ DB_OBJECT_CACHE 34
18 V $ OPEN_CURSOR 34
19 V $ PARAMETER & V $ SYSTEM_PARAMETER 36
19 V $ ROLLSTAT 37
20 V $ ROWCACHE 40
21 V $ SEGSTAT 41
22 V $ SEGMENT_STATISTICS 42
23 V $ SYSTEM_EVENT 43
24 V $ UNDOSTAT 43

 
Learning Dynamic Performance Table
1 v $ sysstat
 
According to the description in OracleDocument, v $ sysstat stores the resource usage of the entire instance (instance-wide) from the moment the database instance runs.
 
Similar to v $ sesstat, this view stores the following statistics:
1>. Event Occurrence Count statistics (for example, user commits)
2>. total column of data generation, access, or operation (such as redo size)
3>. If the value of TIMED_STATISTICS is true, the total time spent on the operation is counted (for example, CPU used by this session)
 
Common columns in the v $ sysstat View:
L STATISTIC #: ID
L NAME: Statistical Item NAME
L VALUE: resource usage
This view also has a column of class-statistical categories, which are rarely used. The following information is displayed:
1. Example Activity
2 represents the Redo buffer Activity
4 represents the lock
8 indicates data buffer Activity
16 represents OS Activity
32 represents parallel activities
64 represents Table Access
128 indicates debugging information
Note: The value of Statistic # varies with different versions. The value of statistic # is used as the query condition instead of the value of Statistic.
 
Use Data in v $ sysstat
 
Data in this view is often used to monitor system performance. For example, the buffer cache hit rate and soft resolution rate can be calculated from the View data.
The data in this view is also used to monitor system resource usage and changes in system resource utilization. With so much performance data, you can check the usage of system resources in a certain range. You can create a view data snapshot at the beginning of a period, and then create another view data snapshot at the end of the period, the difference between the two statistical item values (end value-begin value) is the resource consumption in this period of time. This is a common method for oracle tools, such as Statspack and BSTAT/ESTAT.
To compare the data of a certain range, the source data can be formatted (each transaction, every execution, every second or every login). After formatting, the data is easier to identify the differences between the two. Before the upgrade, you may want to see how the increase in the number of users or the increase in data affects resource usage in a period of time.
You can also use the v $ sysstat data to check resource consumption and resource recycling by querying the v $ system_event view.
 
Common statistics in V $ SYSSTAT
 
V $ SYSSTAT contains multiple statistical items. This section describes some key v $ sysstat statistical items, which are quite useful in tuning. Sort the following in alphabetical order:
 
Key indicators of database usage status:
L CPU used by this session: cpu usage of all sessions, excluding background processes. The unit of this statistics is x second. The number of calls at a time cannot exceed 10 ms.
L db block changes: the number of insert, update, or delete operations that cause changes in data blocks in SGA. This statistics shows the overall database status. At the transaction level, this statistics indicates the dirty cache ratio.
L execute count: Number of SQL statements executed (including recursive SQL statements)
L logons current: The Sessions currently connected to the instance. If two snapshots exist, the average value is obtained.
L logons cumulative: the total number of logins after the instance is started.
L parse count (hard): number of times that the parsing call did not hit in the shared pool. When an SQL statement is executed and the statement is not in the shared pool or is in the shared pool, it cannot be used due to some differences between the two. If the original SQL statement is the same as the existing one, but the query table is different, it is considered that they are two different statements, then hard parsing will happen. Hard parsing will incur high cpu and resource usage expenses, because it requires oracle to re-allocate the memory in the shared pool, and then determine the execution plan, the final statement will be executed.
L parse count (total): total number of resolution calls, including soft resolution and hard resolution. When a session executes an SQL statement that already exists in the shared pool and can be used, soft resolution is generated. When the statement is used (that is, shared), all existing SQL statements related to data (such as the optimal execution plan) must also apply to the current statement. These two statistics can be used to calculate the soft resolution hit rate.
L parse time cpu: total cpu resolution time (unit: 10 ms ). Including hard parsing and soft parsing.
L parse time elapsed: total time spent on completing resolution calls.
L physical reads: OS blocks read count. This includes physical reads inserted into the SGA cache and direct reads in the PGA. This is not the number of I/o requests.
L physical writes: number of data blocks written directly by DBWR to the disk from the SGA cache and the PGA process.
L redo log space requests: The Waiting space of the service process in the redo logs, indicating a longer log switch.
L redo size: the total number of times redo occurs (and therefore log buffer is written), in bytes. This statistics shows the update activity.
L session logical reads: Number of logical read requests.
L sorts (memory) and sorts (disk): sorts (memory) is the number of sorting operations suitable for SORT_AREA_SIZE (so you do not need to sort on disk. Sorts (disk) is the number of SORT_AREA_SIZE tasks that have to be sorted on the disk because the size required for sorting is too large. These two statistics are usually used to calculate in-memory sort ratio.
L sorts (rows): Total number of column sorting. This statistical item can be used in addition to 'sorts (total) 'to determine the columns for each sort. This item indicates the volume and application features.
L table fetch by rowid: Total number of columns returned by ROWID (because 'where rowid = & rowid' is used in index access or SQL statements)
L table scans (rows gotten): Total number of columns read during full table Scan
L table scans (blocks gotten): the total number of blocks read from the full table scan, excluding the split columns.
L user commits + user rollbacks: Number of system transaction start times. When you need to calculate the ratio of each transaction in other statistics, this item can be used as a divisor. For example, to calculate logical reads in a transaction, you can use the following formula: session logical reads/(user commits + user rollbacks ).
 
Note: the parsing of SQL statements includes soft parsing soft parse and hard parsing hard parse. The following are five steps:
1: whether the syntax is legal (SQL statement)
2: whether the semantics is legal (permission, whether the object exists)
3: Check whether the SQL statement exists in the public pool.
-- If yes, skip step 4 and Step 5 and run the SQL statement. soft parse is calculated.
4: select an execution plan
5. generate an execution plan
-- If five steps are complete, this is hard parse.
 
Note physical I/O
 
Oracle Reports that physical reads may not result in actual physical disk I/O operations. This is entirely possible because most operating systems have cached files, which may be read by those blocks. Blocks may also be stored on disks or control-level caches to avoid actual I/O again. The physical read in the Oracle report may only indicate that the requested block is not in the cache.
 
Instance Efficiency ratio (Instance Efficiency Ratios) obtained by V $ SYSSTAT)
 
The following are some typical instance efficiency ratios calculated by v $ sysstat data. The ratio of each instance should be as close as possible to 1:
 
L Buffer cache hit ratio: indicates whether the buffer cache size is suitable.
Formula: 1-(physical reads-physical reads direct (lob)/session logical reads)
Run:
Select 1-(a. value-b.value-c.value)/d. value)
From v $ sysstat a, v $ sysstat B, v $ sysstat c, v $ sysstat d
Where a. name = 'physical reads' and
B. name = 'physical reads direct 'and
C. name = 'physical reads direct (lob) 'and
D. name = 'session logical reads ';
 
L Buffer hit ratio: this parameter indicates the buffer hit rate.
Formula: 1-(physical reads/(db block gets + consistent gets ))
Run:
Select 1-(sum (decode (name, 'Physical reads', value, 0 ))/
(Sum (decode (name, 'db block gets', value, 0) +
Sum (decode (name, 'consistent gets', value, 0 ))))
"Buffer Hit Ratio"
From v $ sysstat;
 
L Soft parse ratio: this will show if the system has too many hard resolutions. This value is compared with the original statistical data to ensure accuracy. For example, if the soft resolution rate is only 0.2, the hard resolution rate is too high. However, if the total number of partitions (parse count total) is low, this value can be ignored.
Formula: 1-(parse count (hard)/parse count (total ))
Run:
Select 1-(a. value/B. value)
From v $ sysstat a, v $ sysstat B
Where a. name = 'parse count (hard) 'and B. name = 'parse count (total )';
 
L In-memory sort ratio: displays the proportion of sorted tasks In the memory. Ideally, In the OLTP system, most of the sorting tasks are small and can be fully sorted in the memory.
Formula: sorts (memory)/(sorts (memory) + sorts (disk ))
Run:
Select a. value/(B. value + c. value)
From v $ sysstat a, v $ sysstat B, v $ sysstat c
Where a. name = 'sorts (memory) 'and
B. name = 'sorts (memory) 'and c. name = 'sorts (disk )';
 
L Parse to execute ratio: in the production environment, the ideal state is that an SQL statement is parsed at a time for most operations.
Formula: 1-(parse count/execute count)
Run:
Select 1-(a. value/B. value)
From v $ sysstat a, v $ sysstat B
Where a. name = 'parse count (total) 'and B. name = 'execute count ';
 
L Parse CPU to total CPU ratio: This item shows the ratio of total CPU consumption to execution and resolution. If this ratio is low, the system performs too many resolutions.
Formula: 1-(parse time cpu/CPU used by this session)
Run:
Select 1-(a. value/B. value)
From v $ sysstat a, v $ sysstat B
Where a. name = 'parse time cpu 'and
B. name = 'cpu used by this session ';
 
L Parse time CPU to parse time elapsed: normally, this item shows the lock competition rate. Calculation of this ratio
Whether the time is spent in parsing and allocating it to the CPU for periodic computation (that is, production work ). Resolution time spent is not in the CPU cycle operation, which usually indicates the time spent due to lock competition.
Formula: parse time cpu/parse time elapsed
Run:
Select a. value/B. value
From v $ sysstat a, v $ sysstat B
Where a. name = 'parse time CPUs 'and B. name = 'parse time elapsed ';
 
Obtain Load Profile data from V $ SYSSTAT
 
The Inter-Load file is an important part of monitoring system throughput and load changes. It provides the following statistics per second and each transaction: logons cumulative, parse count (total ), parse count (hard), executes, physical reads, physical writes, block changes, and redo size.
 
Formatted data can be used to check whether 'Rates' is too high or to compare other baseline data to identify how system profile changes during this period. For example, the following formula can be used to calculate block changes in each transaction:
Db block changes/(user commits + user rollbacks)
Run:
Select a. value/(B. value + c. value)
From v $ sysstat a, v $ sysstat B, v $ sysstat c
Where a. name = 'db block changes' and
B. name = 'user commits 'and c. name = 'user rollbacks ';
 
 
Other calculation statistics are used to measure the load as follows:
L Blocks changed for each read: This shows the proportion of block changes in block reads. It indicates whether the system is mainly used for read-only access or many data operations (such as inserts/updates/deletes)
Formula: db block changes/session logical reads
Run:
Select a. value/B. value
From v $ sysstat a, v $ sysstat B
Where a. name = 'db block changes' and
B. name = 'session logical reads ';
 
L Rows for each sort:
Formula: sorts (rows)/(sorts (memory) + sorts (disk ))
Run:
Select a. value/(B. value + c. value)
From v $ sysstat a, v $ sysstat B, v $ sysstat c
Where a. name = 'sorts (rows) 'and
B. name = 'sorts (memory) 'and c. name = 'sorts (disk )';
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:
 
L event occurrence count statistics, such as the number of user submissions.
L total column (for example, redo size) for data generation, access, or operation)
L accumulation of time spent on executing the operation, such as session CPU usage (if the value of TIMED_STATISTICS 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:
L v $ sesstat only saves session data, while v $ sysstat stores the cumulative values of all sessions.
L 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.
L 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:
L high resource usage
L high average resource usage ratio (resource usage after login)
L 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 $ SESSTAT
L SID: unique session ID
L STATISTIC #: unique resource ID
L 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 ');