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:
Statistic #: identifier
Name: Statistical Item Name
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:
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.
Db block changes: the number of insert, update, or delete operations that cause data block changes in SGA. This statistics shows the overall database status. At the transaction level, this statistics indicates the dirty cache ratio.
Execute count: Number of SQL statements executed (including recursive SQL statements)
Logons current: The sessions currently connected to the instance. If two snapshots exist, the average value is obtained.
Logons cumulative: the total number of logins since the instance is started.
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.
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.
Parse time CPU: total CPU resolution time (unit: 10 ms ). Including hard parsing and soft parsing.
Parse time elapsed: total time spent on completing resolution calls.
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.
Physical writes: number of data blocks written directly by dbwr to the disk from the SGA cache and the PGA process.
Redo log space requests: The Waiting Space of the service process in the redo logs, indicating a longer log switch.
Redo size: the total number of times redo occurs (and therefore logs are written to the buffer), in bytes. This statistics shows the update activity.
Session logical reads: Number of logical read requests.
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.
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.
Table fetch by rowid: Total number of columns returned by rowid (because 'where rowid = & rowid' is used in index access or SQL statements)
Table scans (rows gotten): Total number of columns read during full table Scan
Table scans (blocks gotten): the total number of blocks read from the full table scan, excluding the split columns.
User commits + User rollbacks: 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 shared 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:

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 ';

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 )';

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 )';

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 ';

Parse CPU to total CPU ratio: This item shows the total CPU consumption rate in 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 ';

Parse time CPU to parse time elapsed: Usually, 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:
Blocks changed for each read: This shows the ratio 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 ';

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 )';

 

-- Partial Content Extraction