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"
