Shulou(Shulou.com)06/01 Report--

V$SYSSTAT contains several statistics, and this section introduces some key v$sysstat statistics that are useful for tuning. The following is in alphabetical order:

Some key metrics of database usage status:

L CPU used by this session: cpu usage for all session, excluding background processes. The unit of this statistic is x per cent of a second. Fully call no more than 10ms at a time

L db block changes: the statistics on the number of insert,update or delete operands that cause changes in data blocks in SGA give a rough picture of the overall database state. At each transaction level, this statistic indicates the dirty cache ratio.

L execute count: number of sql statements executed (including recursive sql)

L logons current: the Sessions currently connected to the instance. If there are currently two snapshots, take the average.

L logons cumulative: the total number of logins since the instance was launched.

L parse count (hard): parses the number of misses of the call in shared pool. Hard parsing occurs when a sql statement is executed and the statement is not in shared pool or, although in shared pool, cannot be used because of a partial difference between the two. Hard parsing occurs if the original text of a sql statement is the same as the one that currently exists, but if the query table is different, they are considered to be two different statements. Hard parsing can be costly for cpu and resource usage because it requires oracle to reallocate memory in shared pool and then determine the execution plan before the final statement is executed.

L parse count (total): the total number of parsing calls, including soft parsing and hard parsing. Soft parsing occurs when session executes a sql statement that already exists in shared pool and can be used. When statements are used (that is, shared) all existing sql statements related to data (such as optimized execution plans) must also apply to the current declaration. These two statistics can be used to calculate the soft parsing hit ratio.

L parse time cpu: total cpu parsing time (in 10ms). Including hard parsing and soft parsing.

L parse time elapsed: the total time it takes to complete the parsing call.

L physical reads:OS blocks read number. This statistic, including physical reads inserted into the SGA cache and direct reads in PGA, is not the number of iUnix requests.

L physical writes: the number of blocks written to disk by DBWR from the SGA cache and directly written by the PGA process.

L redo log space requests: the waiting space for the service process in redo logs, indicating the log switch that takes longer.

The total number of times redo size:redo has occurred (and therefore written to log buffer), in byte. This statistic shows that update is active.

L session logical reads: the number of logical read requests.

L sorts (memory) and sorts (disk): sorts (memory) is the number of sort operations that are appropriate in SORT_AREA_SIZE (and therefore do not need to be sorted on disk). Sorts (disk) is the number of sorting operations that have to be performed on disk because the space required for sorting is too large for SORT_AREA_SIZE. These two statistics are usually used to calculate in-memory sort ratio.

L sorts (rows): the total number of columns sorted. This statistic is divisible by the 'sorts (total)' statistic to determine which columns are sorted each time. This item indicates the data volume and application characteristics.

L table fetch by rowid: the total number of columns returned using ROWID (due to index access or the use of 'where rowid=&rowid' in the sql statement)

L table scans (rows gotten): total number of columns read in a full table scan

L table scans (blocks gotten): the total number of blocks read in a full table scan, excluding those split columns.

L user commits + user rollbacks: the number of times the system transaction was initiated. This item can be used as a divisor when the ratio of each transaction in other statistics needs to be calculated. 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 can be divided into soft parsing soft parse and hard parsing hard parse. Here are 5 steps:

1: whether the grammar is legal (written in sql)

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

3: check whether the sql exists in the shared pool

If it exists, skip 4 and 5 and run sql. Now it counts as soft parse.

4: select the execution plan

5: generate an implementation plan

-- if you do all five steps, it's called hard parse.

Pay attention to physical Icano

Oracle reports that physical reads may not result in the actual physical disk Iripple O operation. This is entirely possible because most operating systems have cache files, probably those blocks that are being read. Blocks may also be stored on disk or in a control-level cache to avoid the actual Icano again. Oracle reports physical reads that may simply indicate that the requested block is not in the cache.

Derive the instance efficiency ratio (Instance Efficiency Ratios) from V$SYSSTAT

Here are some typical instance efficiency ratios calculated from v$sysstat data, and each ratio should be as close to 1 as possible:

L Buffer cache hit ratio: this item shows whether the buffer cache size is appropriate.

Formula: 1-((physical reads-physical reads direct-physical reads direct (lob)) / session logical reads)

Execute:

Select 1-((a.value-b.value-c.value) / d.value)

From v$sysstat a recordsysstat bmemorialsysstat crecoversysstat 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 Soft parse ratio: this item will show whether the system has too much hard parsing. This value will be compared with the original statistics to ensure accuracy. For example, a soft resolution of 0.2 means that the hard resolution is too high. However, this value can be ignored if the total amount of resolution (parse count total) is low.

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

Execute:

Select 1-(a.value/b.value)

From v$sysstat a minute vanguard sysstat b

Where a.name='parse count (hard) 'and b.name='parse count (total)'

L In-memory sort ratio: this item shows the percentage of sorting completed in memory. Ideally, in OLTP systems, most sorts are not only small but also can be sorted completely in memory.

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

Execute:

Select a.value/ (b.value+c.value)

From v$sysstat a recordsysstat bmemorie sysstat c

Where a.name='sorts (memory) 'and

B.name='sorts (memory) 'and c.name='sorts (disk)'

L Parse to execute ratio: in a production environment, ideally, a sql statement parses most of the runs at a time.

Formula: 1-(parse count/execute count)

Execute:

Select 1-(a.value/b.value)

From v$sysstat a minute vanguard sysstat b

Where a.name='parse count (total) 'and b.name='execute count'

L Parse CPU to total CPU ratio: this item shows the percentage of total CPU spent on execution and parsing. If this ratio is low, the system is performing too much parsing.

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

Execute:

Select 1-(a.value/b.value)

From v$sysstat a minute vanguard sysstat b

Where a.name='parse time cpu' and

B.name='CPU used by this session'

L Parse time CPU to parse time elapsed: typically, this item shows the lock contention ratio. The calculation of this ratio

Whether time is spent parsing and allocating periodic operations to CPU (that is, production work). Parsing time is not spent on CPU cycle operations usually indicate that time is spent due to lock competition

Formula: parse time cpu / parse time elapsed

Execute:

Select a.value/b.value

From v$sysstat a minute vanguard sysstat b

Where a.name='parse time cpu' and b.name='parse time elapsed'

Get load interfile (Load Profile) data from V$SYSSTAT

Load interval is an important part of monitoring system throughput and load changes, which provides the following statistical information per second and per transaction: logons cumulative, parse count (total), parse count (hard), executes, physical reads, physical writes, block changes, and redo size.

The formatted data can check whether the 'rates' is too high, or it can be used to compare other baseline data settings to identify how the system profile changes during the period. For example, the following formula is available for calculating block changes in each transaction:

Db block changes / (user commits + user rollbacks)

Execute:

Select a.value/ (b.value+c.value)

From v$sysstat a recordsysstat bmemorie 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 are as follows:

L Blocks changed for each read: this item shows the proportion of block changes in block reads. It will indicate whether the system is mainly used for read-only access or mainly for many data operations (such as inserts/updates/deletes)

Formula: db block changes / session logical reads

Execute:

Select a.value/b.value

From v$sysstat a minute vanguard 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))

Execute:

Select a.value/ (b.value+c.value)

From v$sysstat a recordsysstat bmemorie sysstat c

Where a.name='sorts (rows) 'and

B.name='sorts (memory) 'and c.name='sorts (disk)'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.