Learning Dynamic Performance Table (1)--v$sysstat

Learned from dynamic performance tables

First Article --v$sysstat 2007.5.23

As described in Oracledocument, V$sysstat stores the resource usage of the cumulative full instance (Instance-wide) Since the moment the DB instance runs.

similar to V$sesstat , the view stores the following statistics:

1>: Statistics on the number of occurrences (e.g. user commits)

2> The total column of data generation, access, or operation (e.g. redo size)

3> If the Timed_statistics value is true, the total time spent executing the operation is counted (for example: CPU used by this session)

V$sysstat View Common columns Description:

• statistic#: Identification
• Name: Statistic Item names
• VALUE: Resource Usage

The view also has a list of class-statistics categories but is rarely used and the information is as follows:

1 Representative Case activity

2 represents Redo buffer activity

4 means lock

8 represents data buffering activity

16 represents OS Activity

32 represents a parallel activity

64 Delegate Table Access

128 for debug information

Note: The value of statistic# differs in different versions, use the name as the query condition instead of the statistic# value as the condition.

using V$sysstat the data in

The data in this view is often used to monitor system performance. such as buffer cache hit ratio, soft resolution rate, etc. can be calculated from this view data.

The data in this view is also used to monitor system resource usage, as well as changes in system resource utilization. As a result of so much performance data, check system resource usage in a certain interval you can do this by creating a snapshot of the view data at the beginning of a time period, and then creating one at the end, with the difference in the value of each statistic (end Value-begin value) that is the resource consumption in this period. This is a common approach for Oracle tools, such as Statspack and Bstat/estat.

In order to compare the data of an interval segment, the source data can be formatted (each transaction, every execution, every second, or every login), and the data is easier to differentiate from the two after formatting. This type of comparison is more useful before upgrading, or just looking for a period of time when the number of users is growing or how data growth affects the use of resources.

You can also use V$sysstat data to check resource consumption and resource recycling by querying the v$system_event view.

V$sysstat the available statistics in

V$sysstat contains several statistical items that describe some of the key v$sysstat statistics that are useful in tuning. The following alphabetically sorted by:

Some key metrics for database usage status:

• CPU used by this session: CPU usage for all sessions, excluding background processes. The unit of this statistic is x seconds. Full call no more than 10ms
• DB block changes: The insert,update or delete operand that partially causes changes in the data block in the SGA this statistic can probably see the overall database state. At each transaction level, this statistic indicates the dirty cache ratio.
• Execute count: Number of SQL statements executed (including recursive SQL)
• Logons current: The sessions that is currently connected to the instance. If there are currently two snapshots, the average value is taken.
• Logons Cumulative: The total number of logins since the instance was started.
• Parse count (hard): The number of misses in the shared pool that resolved the call. Hard parsing occurs when the SQL statement is executed and the statement is not in a shared pool or although it cannot be used because there is a partial difference between the two in a shared pool. If the original SQL statement is the same as the current one, but the query tables are different, they are considered to be two different statements, and hard parsing occurs. Hard parsing can lead to high CPU and resource usage because it requires Oracle to reallocate memory in the shared pool and then determine the execution plan before the final statement is executed.
• Parse count (total): Resolves the number of calls, including soft parsing and hard parsing. When the session executes an SQL statement, the statement already exists in the shared pool and can be used to produce soft parsing. When a statement is used (that is, shared) all data related to an existing SQL statement (such as the most optimized execution plan) must also apply to the current declaration. These two statistics can be used to calculate the soft-parse hit ratio.
• Parse Time CPU: Total CPU resolution (unit: 10MS). Includes hard parsing and soft parsing.
• Parse Time elapsed: The total cost of completing the parse call.
• Physical Reads:os blocks read number. Includes physical reads inserted into the SGA buffer and direct read in the PGA this statistic is not the number of I/O requests.
• Physical writes: The number of data blocks written to disk by DBWR from the SGA buffer and the amount of data blocks written directly by the PGA process.
• Redo Log Space requests: The waiting space for the service process in redo logs, which represents a log switch that takes a longer time.
• Redo Size:redo The total number of occurrences (and therefore writes to log buffer), in bytes. This statistic shows the update activity.
• Session logical reads: Number of logical read requests.
• Sorts (memory) and sorts: sorts (memory) is the number of sort operations that are suitable for sort_area_size (and therefore do not need to be sorted on disk). Sorts (disk) is the number of sort_area_size that have to be sorted on the disk because the space required for sorting is too large to satisfy. These two statistics are commonly used to calculate in-memory sort ratio.
• Sorts (rows): The total number of column sorts. This statistic can be removed by the ' sorts (total) ' Statistic to determine the column to be sorted each time. This item can indicate data volumes and application characteristics.
• Table fetch by ROWID: The total number of columns returned by using ROWID (resulting from the use of ' where Rowid=&rowid ' in an indexed access or SQL statement)
• Table scans (rows gotten): Total number of columns read in full table scan
• Table scans (blocks gotten): Total number of blocks read in a full table scan, excluding those split columns.
• User commits + user rollbacks: Number of system transactions. The item can be counted as a divisor when it is necessary to calculate the ratio of each transaction in other statistics. 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 has soft parsing soft parse and hard parse, the following is a 5-step process:

1: Whether the syntax is legal (SQL notation)

2: Whether the semantics are legitimate (permissions, whether the object exists)

3: Check if the SQL exists in the public pool

--if present, skip 4 and 5 directly and run SQL. This is soft parse.

4: Select execution Plan

5: Generate execution plan

-If all 5 steps are done, this is called hard parse.

Note Physical I/O

Oracle reporting physical reads may not result in actual physical disk I/O operations. This is entirely possible because most operating systems have cache files, possibly those blocks being read. Blocks may also be stored on disk or control-level caches to avoid actual I/O again. The Oracle report has physical reads that may simply indicate that the requested block is not in the cache.

by V$sysstat To obtain the instance efficiency ratio (Instance efficiency Ratios)

The following are typical instance efficiency ratios are calculated from the V$sysstat data and each ratio should be as close to 1 as possible:

• buffer Cache Hit Ratio: This item shows if the buffer cache size is appropriate.

Formula: N ((physical reads-physical reads direct-physical reads direct (LOB))/session logical reads)

Perform:

Select N ((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 ';

• buffer hit Ratio: This item shows the buffer hits.

Formula: Physical reads/(db block gets+consistent gets)

Perform:

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;

• Soft Parse ratio: This will show if the system has too much hard parsing. This value will be compared to the original statistic to ensure accuracy. For example, a soft resolution rate of only 0.2 indicates that the hard resolution rate is too high. However, this value can be ignored if the total resolution is low (parse count complete).

Formula: 1-(Parse count (hard)/Parse count (total))

Perform:

Select (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: This item shows the percentage of the sort that is completed in memory. Ideally, in an OLTP system, most of the sorting is not only small, but can be done completely in memory.

Formula: Sorts (memory)/(Sorts (memory) + sorts (disk))

Perform:

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 a production environment, the most desirable state is an SQL statement that resolves most runs at once.

Formula: 1-(parse Count/execute count)

Perform:

Select (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 overall CPU spent on execution and resolution ratios. If this ratio is low, it means that the system performs too many parsing.

Formula: 1-(Parse time CPU/CPU used by this session)

Perform:

Select (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 elapsed: Typically, the item shows the lock contention ratio. This ratio is calculated

Whether the time is spent in parsing and allocating to the CPU for periodic operations (that is, production work). Parsing the time spent is not usually expressed in CPU cycle operations due to the lock contention that caused the time spent

Formula: Parse Time Cpu/parse time elapsed

Perform:

Select A.value/b.value

From V$sysstat A,v$sysstat b

Where A.name= ' parse Time CPU ' and b.name= ' parse time elapsed ';

from V$sysstat obtaining a Load profile Data

A load file is an important part of monitoring system throughput and load changes, which provides statistics for each second and per transaction as follows: Logons cumulative, parse count (total), parse count (hard), executes, Physical reads, physical writes, block changes, and redo size.

The formatted data checks to see if ' rates ' is too high, or is used to compare other baseline data settings to identify how the system profile changes during the period. For example, calculating the block changes in each transaction can be as follows:

DB block changes/(user commits + user rollbacks)

Perform:

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 to measure the load mode are as follows:

• Blocks changed for each read: This shows the ratio of block changes in block reads. It will indicate whether the system is primarily for read-only access or primarily for many data operations (e.g. Inserts/updates/deletes)

Formula: DB block changes/session logical reads

Perform:

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

Perform:

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

Learning Dynamic Performance Table (1)--v$sysstat