예전에 원서보면서 vmstat 관련 정리 했던거...
일반적으로 모니터링을
OS / DB / WAS layer 에서 하는게 맞구
DB 에서는 Contention 이 없어도, OS 에서 Contention 이 발생하거든
EM 에서는 평이하게 나와도 OS 에서는 미친듯시 사용하는게 보이거든
이건 아마두 OS 에서 바라보는 View 와 DB 에서 바라보는 View 다 달라서 인듯..
암튼 내 경험상 그렇더라구..
EM 에서는 아주 깨끗한데.. 서비스가 안된다구 해서 OS 에서 보면 CPU 100% 치구 머 그래..
참고 하삼

What to Look for in vmstat ORACLE TUNING

2006/08/07 20:54

복사 http://blog.naver.com/darkturtle/50007239602

What to Look for in vmstat

: 중요 칼럼만 보고 금방 OS Level 의 Resource 현황을 파악 하고,

문제 없음을 확인 후 DB 나 Application 파악하는 습관을 가지도록 하자

모든 Software 는 OS Base 위에서 구동 된다는 것을 다시금 기억 하자

As you can see, each dialect of vmstat reports different information about the
current status of the server, Despite theses dialect differences, theses are only
a small number of metrics that are important for server monitoring.
Theses metrics include :

1. r ( runeueue) : The runqueue value show s the number of tasks executing and
   waiting for CPU resources. When this number exceeds the number of CPUs on the
   server, a CPU bottleneck exists, and some task are waiting for execution.

1. pi ( Page in) A page-in operation occurs when the server is experiencing a
   shotage of RAM memory. While all virtual memory servers will page out to
   the swap disk , page-in operations show that that the servers has exceeded
   the avaiable RAM storage. Any nonzero value for pi indicate excessive activity
   as RAM memory contents are read in from the swap disk.

1. us ( user CPU ) This is the amount of CPU that is servicing user tasks.

1. sy ( system CPU ) This is the percentage of CPU being used to service system tasks.

1. id ( idle ) This is percentage of CPU that is idle.

1. wa ( wait-IBM-AIX only ) This shows the percentage of CPU that is waiting on
   external operations such as disk I/O

Note that all of the CPU metrics are expressed as percentages. Hence, all of the
CPU values ( us + sy + id + wa ) will always sum to 100.

identifying CPU bottlenecks with vmstat ...
In short, the server is experiencing a CPU bottleneck when r is greater than
the number of CPUs on the server.

1. 1. 출처 : Oracle 9i High Performance Tuning with STATSPACK

저자는 DONALD K. BURLESON

예제 ) AIX 5.3 환경에서 vmstat 명령 결과

kthr memory page faults cpu
----- ----------- ------------------------ ------------ -----------
r b avm fre re pi po fr sr cy in sy cs us sy id wa
1 0 451376 1094 0 0 0 0 0 0 415 4513 58225 41 34 25 0
1 0 451436 1034 0 0 0 0 0 0 419 4789 57513 38 38 24 0

CPU Check

Display the Number of CPUs in IBM AIX and HP/UX

lsdev -C | grep Process |wc -l

Display NUmber of CPUs in Solaris

psrinfo -v | grep "Status of processor"| wc -l

Display NUmber of CPUs in Linux

cat /proc/cpuinfo | grep processor | wc -l

Memory Check

1. 1. Display RAM Size on DEC-UNIX
      uerf -r 300 | grep -i mem

1. 1. Display RAM Size on HP/UX
      dmesg

1. 1. Display RAM Size on AIX
      1. lsdev -C | grep mem
      mem0 Avaiable 00-00 Memory
      2. lsattr -El mem0
      size 3064 Total amount of physical memory

1. 1. Display RAM Size on Solaris
      prtconf | grep -i mem

1. 1. Display RAM Size in Linux
      free

##Using the Top Utility for Displaying RAM
top

CPU waits > 40% ( AIX version only )
This may indicate I/O-based contention. The solution is to spread files
across more disks or add buffer memory

Run queue > xxx ( where xxx is the number of CPUs on the server, 2 in this
example ) This indicates an overloaded CPU. The solution is to add additional
procesor to the server

Page_in > 2 with correlated scan rates
Page-in operates can indicate overlorded memory. The solution is to reduce the
size of the Oracle SGA, PGA, or add additional RAM memory to the server

User CPU + System CPU > 90%
This indicates periods where the CPU is highly utilized.

##

Also, because the MTS uses the shared_pool for process sorting, the DBA
will also see increased demand on the shared_pool.

Connections using the MTS will place the UGA inside the Oracle SGA. To hold
the UGA storage for MTS connections, Oracle has provided the large_pool init.ora
parameter. The LARGE pool is an area of the SGA similar to the SHARED pool,
but with restrictions on its usage such that only certain types and size of
memory can be allocated in this pool.

##

The Oracle DBA needs to consider the RAM cache on the disk array because it
changes the basic nature of disk I/O. When Oracle cannot find a data block in one
of the data buffers in the SGA, Oracle will issue a physical read request to the disk
array. This physical read request is received by the disk array, and the disk RAM cache
is checked for the desired block. if the desired block is in the RAM cache, the disk
arrary will return the block to Oracle without making a physical disk I/O. The fact
that Oracle physical requests may not match actual read requests is a very important
point because it can lead to misleading statistics. For example, the stats$filestatxs
table shows the number of reads and writes to files. If you are using a disk array
such as EMC, these I/O statistics will not correspond to the actual disk reads and
writes. The only conclusive way to check "real" disk I/O is to compare the physical
I/O as measured on the disk array with Oracle's read and write statistics. In many
cases, the disks are performing less than half the I/O reported by Oracle, and this
discrepancy is due to the caching of data blocks on the disk array RAM memory.

RAID 0
RAID 1
RAID 0 + 1 - Best
RAID 5 - in RAID 5, read performance is improved, but every write has to incur the
additional overhead of reading old parity, computing new parity, writing
new parity, and then the actual data, with the last two operations
happending whiel two disk drves are simultaneously locked.
This overhead is known as the RAID-5 write penalty.
Also, if a disk fails in a RAID-5 configuration, the I/O penalty incurred
during the disk rebuild is extremely high.

A raw device is defined as a disk that bypasses the I/O overhead created by
throughput, but only in cases where I/O is already the bottleneck for the Oracle
database. Furthermore, raw devices require a termendous amount of manual work for
the Oracle administrator nad the systems administrator. Oracle recommends that
raw devices should only be considered when the Oracle database is I/O bound.
However, for these types of Oracle database, raw device can dramatically improve
overall performance. if the database is not I/O bound, switching to raw devices
will have no impact on performance.

####

As u may know, the pctfree parameter tells Oracle when to unlink a data
block from the freelists and the pctused parameter and pctused parameter
tells Oracle when to relink a data block back onto the freelist. The values
for pctfree and pctused are heavily dependent upon row length and the desired
reserved space for row expansion.

Over the past several years, Oracle has gradually recongnized the benefits
of bitmap data structures. As Oracle hea evolved, we see the following
progressive introduction of bitmaps :

• Bitmap Indexes - Oracle 7.3.3.
• Locally Managed Tablespaces - Oracle 8.0
• Bitmap segment management - Oracle 9i

Locally Managed Tablespace

• SEGMENT_SPACE_MANAGEMENT - AUTO ==> Bitmap space management
• UNIFORM
• EXTENT_MANAGEMENT - DICTIONARY
• LOCAL
  Oracle automatically allocates new space management blocks when a new
  extent is created and maintains internal pointer to the bitmap blocks

• Slow inserts : insert operations run slowly and have excessive I/O.
  This happens when blocks on the freelist only have room for a few rows
  before Oracle is forced to grab another free block.
• Slow selects : Select statements have excessive I/O because of chanined
  rows. This occurs when rows "chain" and fragment onto several data blocks,
  causing additional I/O to fetch the blocks.
• Slow updates : Updates statements run very slowly with double the amount
  of I/O. This happens when update operations expaned a VARCHAR or BLOB cloumn
  and Oracle is foreced to chain the row contents onto additional data blocks.
• Slow deletes : Large delete statements can run slowly and cause segment
  header contention. This happens when rows are deleted and Oracle must
  relink the data block onto the freelist for the table .

####

The purpose of pctfree is to tell Oracle when to remove a block from the
object's freelists. Since the Oracle default is pctfree = 10, blocks remain
on the freelist while they are less than 90 percent full.

The pctused parameter tells Oracle when to add a previously full block
onto the freelists. As rows are deleted from a table, the database blocks
become eligible to accept new rows.
This happens when the amount of space in a database block falls below
putused, and a freelist relink operation is triggered.

The freelists parameter tells Oracle how many segment header blocks to
create for a table or index. Multiple freelists are used to prevent segment
header contention when several tasks competer to INSERT,UPDATE, or DELETE
from the table. The freelists parameter should be set to the maximum number
of concurrent update operations.

The freelists groups parameter is used in Oracle Parallel Server
(Real Application Clusters )
The freelists groups parameter should be set to the number of instance
that access the table.

For efficient space reuse
: A high value for pctused will effectively reuse space on data blocks,
but at the expense of additional I/O. A high pctused means that relatively
full blocks are placed on the freelist. Hence, these blocks will be able
to accept only a few rows before becoming full again, leading to more I/O.

For High performance
: A low value for pctused means that Oracle wiil not place a data block
onto the freelist until it is nearly empty. The block will be able to accept
many rows until it become full, thereby reducing I/O at insert time.
Remember that is always faster for Oracle to extent into new blocks than to
reuses existing blocks. it takes fewer resources for Oracle to extend
a table than to manage freelists.

The presence of chained rows in a table means that pctfree is too low or
that db_block_size is too small.

Whenever a request is made to insert a row into a table, Oracle goes to a
freelist to find a block with enough space to accept a row. As you may know,
the freelist chanin is kept in the first block of the table or index, and
this block is known as the segment header. The sole purpose of the pctfree
and pctused table allocation parameters is to controlthe movement of blocks
to and from freelists. While freelist link and unlink operations are simple
Oracle function, the setting for freelist link (pctused) and unlink(pctfree)
operations can have a dramatic impact on the performance of Oracle.

As you may know, linking and unlinking a block requires two writes: one to
the segment header for the freelist head node, and the other to the new
block to make it participate in the freelist chain. The following general
rules apply to freelists:

1. Oracle detects that free space is than pctfree for block 20 and invokes
the unlink operation. Since block 20 is the first block on the freelists chain,
Oracle read s the data block address(DBA) inside the block header and sees
that the next free block is block 60.

2. Oracle next adjust the freelist header node and moves the DBA for block
60 to the head of the freelist in the segment header. Block 20 no longer
participates in the freelist chain, and the firsth entry in the freelist
is now block 60.

Because the updated block is not at the head of the freelist chain, the prior
block's freelist pointer cannot be adjusted to omit the block. Note that the
dead block remains oh the freelist even though it does not have room to
accept a row.

The dead block remaining oh the list will cause additional Oracle overhead,
especially if there is a large number of "unavaiable" blocks on the freelist.

Freelists relinks can be reduced in two ways:

• Freelists relinks can be "turned down" by setting pctused to 1. Setting
  pctused to a low value means that data blocks are not relinked onto the
  freelist chain unless they are completely empty.

• Use the APPEND hint when adding rows, By using APPEND with inserts,
  you tell Oracle to bypass the freelists and raise the high-water mark for the
  table to grab a fresh, unused data block.

This first-in, fist-out linking method allows for very fast unlinking and
relinking of data blocks from the freelist.

Noete that the segment header nad data block waits are often related to
competing update tasks that have to wait on a single freelist in the
segment header.

We are discussing buffer waits now because buffer waits are usually associated
with segment header contetion that can be remedied by adding additional
freelists for the table or index.

Let's remember that a buffer busy wait occurs when a database block is found
in the data buffer but it is unavailable because another Oracle task is using
the data block.

As you may know, you can easily rebuild on Oracle index with the following
command :
ALTER INDEX index_name REBUILD TABLESPACE tablespace_name ;
We might want to rebuild an index if the block gets per access is greater
than 5, since excessive block gets indicate a fragmented B-tree structure.
Another rebuild condition would be cases where deleted leaf nodes comprise
more than 20 percent of the index nodes. Aother reason to rebuild is
when any index shows a depth of 4 or greater.

NOTE
It is interesting to note that Oracle indexing has change dramatically
between Oracle7 and Oracle8. While the functions of the indexes remain
the same, the sizes of the indexes have been reduced by almost 50 percent.

The rebuild command will perform the following steps:

• Walk the existing index to get the index keys.
• Populate temporary segments with the new tree structure
• Once the operation has completed successfully, drop the old tree,
  and rename the temporary segments to the new index.
  !! 실제 online rebuild (8i) 시에 문제 발생 한다. !!
  Index rebuilding with the alter index rebuild is a very safe command.
  If anything goes wrong, Oracle aborts the operation and leaves the existing
  index in place.

Most Oracle administrators run this scripts and then select the index that
they would like ot rebuild. Note that the TABLESPACE clause should always be
used with the alter index rebuild command to eusure that the index is rebuilt
in the same tablespace as the existing index. Be aware that you must have
enough room in that tablespace to hold all of temporary segments required
for the index rebuild, so most Oracle DBAs will double-size index tablespaces
with enough space for two full index trees.

Also note that if you use the PARALLEL option to increase speed when rebuilding
indexes, each parallel process will require enough space for the indexes'initial
extent size. Hence, if you rebuild indexes with PARALLEL DEGREE 4, you must
have four times the space in the target tablespace.

One of the most serious causes of poor DML performance is the existence of
unused index.

alter index index_name monitoring usage ;

• This command only works in Oracle9i or later.

1. 1. 스크립트에서 유용한 command line
2. Oracle SID
   cat /etc/oratab|grep ^$ORACLE_SID:|cut -f1 -d':'

1. Oracle Home
   cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'

1. Server Name
   uname -a|awk '
   Unknown macro: {print $2}
   '

[WEBINT]sel550b:/app/oracle/tg/STATSPACK> sqlplus -

SQL*Plus: Release 10.1.0.4.0 - Production

Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= -H | -V | [ [-C <v>] [-L] [-M <o>] [-R <n>] [-S] ]
<logon> ::= <username>[/<password>][@<connect_identifier>] | / | /NOLOG
<start> ::= @<URL>|<filename>[.<ext>] [<parameter> ...]
"-H" displays the SQL*Plus version banner and usage syntax
"-V" displays the SQL*Plus version banner
"-C" sets SQL*Plus compatibility version <v>
"-L" attempts log on just once
"-M <o>" uses HTML markup options <o>
"-R <n>" uses restricted mode <n>
"-S" uses silent mode

It is also imortant to remember that the tuning of SQL statements must
come after the tuning of the instance and the objects, for several
very goo reasons.

For more details about tuning Oracle SQL, see the Oracle Press book
Oracle High-Performance SQL Tuning, by Don Burleson.

The goals of SQL tuning are simple :

• Remove unnecessary large-table full scans
  :Unnecesary full table scans cause a huge amount of unnecessary I/O, and
  can drag down an entire database. The tuning expert evaluates the SQL based
  on the number of rows returned by the query, If the query returns less than
  40 percent of the table rows in an ordered table, or 7 percent of the
  rows in an unordered table, the query can be tuned to use and index in lieu
  of the full table scan.
  The most common tuning for unnecessary full table scans is adding indexes.
  Standard B-tree indexes can be added to tables, and bitmappd and function-based
  indexes can also eliminate full table scans.
  The decision to remove a full table scan should be base on a careful examination
  of the I/O costs the index scan vs. the costs of the full table scan, factoring
  in the multiblock reads and possible parallel execution. in some cases an
  unnecessary full table scan can be forced to use an index by adding an index
  hint to the SQL statement.

• Cache small-table full table scans
  : In cases where a full table scan is the fastest access method, the tuning
  professional should ensure that a dedicated data buffer is avaiable for the
  rows, In Oracle7 you can issue alter table xxx cache. In Oracle8 and beyond,
  the small table can be cached by forcing it into the KEEP pool.

• Verify optimal index usage
  : This is especially important for improving the speed of queries. Oracle
  somethimes has a choice of index, and the tuning professional must examine
  each index and ensure that Oracle is using the proper index. This also includes
  the use of bitmapped and function-based indexes.

• Verify optimal JOIN techniques
  : Some queries will perform faster with NESTED LOOP joins, other with HASH joins.

Caution
: The use of any SQL hint except the RULE hint forces the cost-based optimizer
to be invoked. Hence, make sure you have analyzed your tables and indexes
prior to using any hints.

Rule-Based Shortcomings
: Often chooses the wrong index to retrieve rows.

Cost-Based shortcomings
: Often performs unnecessary full tables scans, especially when more than
three are being joined.

Most Oracle SQL queries will only be retrieving a small subset of the rows
within the table and full table scans are only appropriate when more than
40 percent of the table rows are required.

Hence, the general guideline for replacing an index range scan is:

• For row-sequenced tables
  : Queries that retrieve less than 40 percent of the table rows
  should use an index range scan. Conversely, queries that read more than
  40 percent of the rows should use a full table scan.
• For unordered tables
  : Queries that retrieve less than 7 percent of the table should use and
  index range scan. Conversely, queries that read more than 7 percent of
  the table rows will probably be faster with a full table scan.

Also, unlinke the cost-based optimizer, the order of tables in the FROM
clause and the order of Booleans in the WHERE clause affecting the
execution plan for the query.

Changing the Rule-Based Driving Table
In Oracle's rule-based optimizer, the ordering of the table names in the
FROM cluase determines the driving table. The driving table is important
because it it retrieved first, and the rows from the second table are then
merged into the result set from the first table. Therefore, it is essential
that the second table return the least amount of rows based on the WHERE
clause.

With the rule-based optimizer, the table names are read from right to left.
Hence, the last table in the FROM clause should be the table that returns
the smallest number of rows.

When the Rule-Based Optimizer Fails to Use the Correct Index
: In Sum, we need to pay careful attention to the indexes that are chosen
by the rule-based optimizer, and either disable the indexes that we do not
want used in the query or force the use the index that we want. To review,
indexes can be explicitly specified with the INDEX hint, or unwanted indexes
can be disabled by mixing datatypes on the index
(i.e. WHERE numeric_column_value=123||''), or by specifying a FULL hint in
the query.

Tuning with Cost-Based Optimization ( CBO )

alter session set optimizer goal = rule

Here is a list of common hints that are used to change the execution plan
in the cost-based optimizer:

• HASH_AJ : This hint is palced in a NOT IN subquery to perform a hash anti-JOIN.
• MERGE_AJ : This hint is placed in a NOT IN subquery to perform an anti-JOIN.
• ORDERED : REquests that the tables should be joined in the order that they
  are specified ( left to right ). For example, if you know that a stat table has
  only 50 rows, you may want to use this hint to make state the driving table.
• PARALLEL(table_name degreee) : For full table scans, this hint requests
  that the table_name query be executed in parallel mode with degree processes
  servicing the table access.
• USE_CONCAT : Request that a UNION ALL be used for all OR conditions.
• USE_NL(table_name) : Requests a nested loop operation with the specified
  table as the driving table.

However, there are some general rules that can be applied :

• Pre-Oracle8i queries that join three or more large tables will generally
  benefit from the rule-based optimizer or the first_rows hint.
• Queries that access bitmapped or function-based indexes will benefit from
  the cost-based optimizer
• Queries that use start query hints need the cost-based optimizer.
• Databases at Oracle8i and beyond will benefit from the cost-based optimizer

Miscellaneous Tuning Techniques
Before we go into detail on the process of tuning, let's look at several
important ways to tune individual SQL Statements. These topics include :

• Tuning with hints
• Tuning subqueries
• The problem of literal SQL statements
• Tuning with temporary tables
• General rules for writing efficient SQL