Oracle Performance Diagnostics-Learning Statspack Notes (iv) [Extended Statspack Collection Server statistics]

Source: Internet
Author: User
Tags count manual mail time interval linux
oracle| Notes | server | statistics | performance
Oracle Performance Diagnostics-Learning Statspack Notes (iv) [Extended Statspack Collection Server statistics]



Author: Liu Yingbo

Time: 2004-3-22

Mail:liuyingbo@126.com, please correct me.



Reprint please indicate the source and the author



Note: Because Statspack does not have all the information needed to fully analyze performance issues, it needs to extend its collection server statistics. (This article Environment Redhat Linux7.) 2)



Vmstat Introduction

Collecting server information through Statspack, mainly by collecting vmstat information to show the server status. The Vmstat tool is the most common UNIX monitoring tool that can show the status values of servers at a given time interval.

The use of general vmstat Tools is done with two numeric parameters, the first parameter is the number of time intervals sampled, the unit is seconds, and the second parameter is the number of times the sample is sampled. Such as:

[Oracle@brucelau oracle]$ Vmstat 1 2

Procs Memory Swap IO system CPU

R b w swpd free buff cache si and bi 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 system is almost idle, and different operating systems vmstat output content)



For now, the metrics for server monitoring are:

R (Run queue)

Pi (page import)

US (user CPU)

Sy (System CPU)

ID (Idle)



Identify CPU bottlenecks through Vmstat

R (Run queue) shows the number of tasks that are executing and waiting for CPU resources. When this value exceeds the number of CPUs, there is a CPU bottleneck.

Commands to get 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 CPU bottlenecks, the solution is generally several:

1. The simplest is to increase the number of CPUs

2. Through the adjustment of task execution time, such as large tasks to be carried out in the context of the system is not busy to carry out, the incoming balance system tasks

3. Adjust the priority of an existing task



Identify CPU full load through Vmstat

The first thing to declare is that the CPU metric in Vmstat is a percentage. When the value of Us+sy is close to 100, it means that the CPU is approaching full load. Note, however, that the CPU's full workload does not explain what Unix always tries to get the CPU as busy as possible, maximizing the throughput of the task. The only thing that can determine the CPU bottleneck is the value of R (Run queue).



Identifying Ram bottlenecks through Vmstat

The database server has only limited RAM, and memory contention is a common problem for Oracle.

First look at the amount of RAM, which commands the following (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, you can use other commands such as top to display RAM.

When the memory requirements are greater than the amount of RAM, the server starts the virtual memory mechanism, through virtual memory, you can move the RAM segment to the special disk segment of swap disk, this will appear the Virtual memory page export and page import phenomenon, page export does not explain the ram bottleneck, Virtual Memory systems often page export of memory segments, but the page import operation indicates that the server needs more memory, and page import needs to replicate the memory segment from swap disk back to RAM, causing the server to slow down.



There are several solutions:

1. The simplest, increased RAM

2. Change the small SGA so that the ram demand is reduced

3. Reduce RAM requirements (e.g., reduce PGA)



We have a basic understanding of vmstat work, the following is statspack through Vmstat statistics collection server performance data.



Statspack collects server information through Vmstat

First, create a table under the Perfstat user that stores server information: such as

Build table:

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,--free CPU data

WAIT_CPU number– waits for CPU data (only AIX exists)

)

Tablespace Perfstat;

Then, the Unix/linux shell becomes, using the results of the vmstat to obtain the appropriate server information, and stored in the table.



About Shell programming, may have been beyond the content of this article, and honestly, I do not have shell programming experience, I hope that brother can complete the shell programming content, and please mail to me to share, thank you first!!









Report:

Help manual for Vmstat on Linux: (Results 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 produced gives averages since the last reboot. Additional reports give information on a sam-

Pling period of length delay. The process and memory reports are instantaneous in either case.



Options

The-n switch causes the header to is displayed only once rather than.



Delay is the delay between updates in seconds. IF no delay is specified, only one the be printed with the

Average values since boot.



The count is the number of updates. If no count is specified and delay are defined, 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.

W:the number of processes swapped out but otherwise runnable. This

The field is calculated, 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).



Io

Bi:blocks sent to a block device (BLOCKS/S).

Bo:blocks received from the Block device (BLOCKS/S).



System

In:the number of interrupts per second, including the clock.

Cs:the Number of context switches/second.

: CPU

These are percentages of total CPU time.

Us:user time

Sy: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



ALSO

PS (1), Top (1), free (1)



BUGS

Does not tabulate the "block IO" Per Device or count the number of system calls.



AUTHOR

Written by Henry Ware <al172@yfn.ysu.edu>.



Throatwobbler Ginkgo Labs July 1994 VMSTAT (8)





adjourned

................................................................................................

Reference

Donald K.burleson "ORACLE high-performance tuning with Statspack"








Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.