Oracle Performance Diagnosis - Learning Statspack Notes (4) [Extended StatsPack Collection Server Statistics]

zhaozj2021-02-16 49

Author: Liuying Bo

Time: 2004-3-22

Mail: liuyingbo@126.com, please correct

Reprint, please indicate the source and the author

Explanation: Since Statspack does not access all the information required to fully analyze performance issues, you need to extend the statistics of its collection servers. (This article redhat linux7.2)

VMSTAT introduction

The server information is collected through the StatsPack, which is mainly to display the server status by collecting VMSTAT. The VMSTAT tool is the most common UNIX monitoring tool, which can present the status value of the servers of the given time interval.

The usual VMSTAT tool is done by two numeric parameters, the first parameter is the time interval of the sample, the unit is second, the second parameter is the number of samples. Such as:

[Oracle @ Brucelau Oracle] $ VMSTAT 1 2 2

Procs Memory Swap Io System CPU

R b W SWPD Free Buff Cache Si So Bo In CS US Sy ID

1 0 0 0 271844 186052 255852 0 0 2 6 102 10 0 0 100

0 0 0 0 271844 186052 255852 0 0 0 0 104 11 0 0 100

(Note: The current system is almost idle, and the content of VMSTAT output in different operating systems is different)

Currently, the metrics for server monitoring are:

R (run queue)

Pi (page import)

US (user CPU)

SY (System CPU)

ID (idle)

Identify CPU bottlenecks via VMSTAT

R (run queue) demonstrates the number of tasks that are performing and waiting for the CPU resource. When this value exceeds the number of CPUs, the CPU bottleneck will appear.

Get commands for the number of CPUs (Linux Environment):

CAT / PROC / CPUINFO | GREP Processor | WC -L

When the R value exceeds the number of CPUs, there will be a CPU bottleneck, and the solution is generally several:

1. The simplest is to increase the number of CPUs

2. Implementation by adjusting the task execution time, if the big task is placed in the system is not busy, the Balance system task

3. Adjust the priority of existing tasks

Identify CPU full load via VMSTAT

First, it is necessary to declare that the measurement of the CPU in VMSTAT is percentage. When the value of US SY is close to 100, it means that the CPU is approaching full load. However, it should be noted that the CPU full load does not explain what, Unix always tries to be busy as possible, so that the throughput of the task maximizes. The only value that can determine the CPU bottleneck or R (run queue). Identify RAM bottlenecks via VMSTAT

Both database servers have only limited RAM, and there is a common problem with Oracle.

First check the number of RAMs, the command is as follows (Linux environment):

[root @Brucelau root] #free

Total Used Free Shared Buffers Cached

MEM: 1027348 873312 154036 185736 187496 293964

- / Buffers / Cache: 391852 635496

SWAP: 2096440 0 2096440

Of course, other commands such as TOP can be used to display the RAM.

When the demand of memory is greater than the number of RAM, the server launches the virtual memory mechanism. With virtual memory, the RAM segment can be moved to the special disk segment of SWAP Disk, which will appear on the page export and page import pages of virtual memory, page export and You cannot explain the RAM bottleneck, and the virtual memory system often leads to the memory segment, but the page import operation indicates that the server needs more memory, page import needs to copy the memory segment back to RAM from the SWAP Disk, resulting in server speed slow.

There are several ways to solve:

1. The simplest, increase RAM

2. Change small SGA to reduce RAM demand

3. Reduce the demand of RAM (such as: Reduce PGA)

We basically understand the VMSTAT work, below is StatsPack to collect server performance data through VMSTAT statistics.

Statspack collects server information via VMSTAT

First, a table of the storage server information is built at the Perfstat user:

Construction form:

Create Table Stats $ VMSTAT

(

START_DATE DATE, - System Time

Duration Date, - Time Interval

Server_name varchar2 (20), - server name

Runque_Waits Number, - Run queue data

Page_in number, - Page Import Data

Page_out Number, - Page Export Data

User_cpu number, - User CPU data

System_cpu number, - system CPU data

IDLE_CPU Number, - Idle CPU Data

WAIT_CPU NUMBER - Waiting for CPU data (just AIX existence)

)

TABLESPACE PERFSTAT;

Then, through the unix / linux's shell becomes, the result of the VMSTAT is used to obtain the corresponding server information and store it in the table.

About Shell Programming, may have exceeded the content of this article, and honestly, I don't have the experience of shell programming, I hope that the brothers can complete the content of shell programming, and to drive Mail to share, thank you first! !

Attachment:

Help Manual for VMSTAT on Linux: (Suest of Man Vmstat)

VMSTAT (8) Linux Administrator's Manual Vmstat (8)

Name

VMSTAT - Report Virtual Memory Statistics

Synopsis

Vmstat [-n] [delay [count]]

Vmstat [-v]

Description

Vmstat Reports Information About Processes, Memory, Paging, Block Io, Traps, and CPU Activity.

The First Report Productued Gives Average Since The Last Reboot. Additional Reports Give Information On A SAM-

PLING Period of Length Delay. The process and memory report...............

Options

The --n switch causes the header to be displayed Only Once Rather Than Periodical.

DELAY Is The Delay Between Updates In Seconds. if no delay isot isprinted with the

Average values since boot.

Count Is The Number of Updates. if no count isot, count defaults to infinity.

The -v Switch Results in Displaying Version Information.

Field Descriptions

Procs

R: The number of processes waiting for run time.

B: The Number of Processes in UninterRuptable Sleep.

W: The Number of Processes Swapped Out But Otherwise Runnable. this

Field is Calculate, But Linux Never Desperation Swaps.

Memory

SWPD: The Amount of Virtual Memory Used (KB).

Free: The Amount of iDLE Memory (KB).

BUFF: The Amount of Memory Used As Buffers (KB).

SWAP

Si: Amount of Memory Swapped in from disk (kb / s).

SO: Amount of Memory Swapped to Disk (KB / S).

BI: Blocks Sent to a Block Device (blocks / s).

BO: Blocks Received from A Block Device (blocks / s).

SYSTEM

IN: The Number of Interrupts Per Second, Including The Clock.

CS: The Number of Context Switches Per Second.

: CPU

The release is percentages of total CPU Time.

US: User Timeesy: System Time

ID: IDLE TIME

Notes

VMSTAT DoES NOT Require Special Permissions.

THESE Reports Are Intended to Help Identify System Bottlenecks. Linux Vmstat Does Not Count Itself As a Run-

NING process.

All Linux Blocks Are Currently 1k, Except for CD-ROM Blocks Which Are 2K.

Files

/ proc / meminfo

/ proc / stat

/ proc / * / stat

9cbs

New Post(0)