Summary of Oracle performance view 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.

 

By v $ sysstatInstance efficiency ratios)

 

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:

 

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

 

LSoft parse Ratio: This will show whether 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 )';

 

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

 

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

 

LParse CPU to total CPU Ratio: Shows the total CPU usage rate for 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 ';

 

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

 

From v $ sysstatObtain the load Profile)Data

 

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