Friday, 28 December 2012

Buffer busy wait event


Buffer busy wait 

Oracle has several types of buffer classes, such as data block, segment header, undo header, and undo block. How you fix a buffer busy wait situation will depend on the types of buffer classes that are causing
the problem.

 Buffer Busy Waits usually happen on Oracle 10 and 11 mainly because of insert contention into tables or Indexes.  There are a few other rare cases of contention on old style RBS segments, file headers blocks and freelists
Before Oracle 10 and 11 there was one other major reason which was readers waiting for readers, ie one user does a phyiscal IO of a block into memory and a second user want to read that block. The second user waits until the IO is finished by the first user. Starting in 10g this wait has been given the name "read by other session".  Before Oracle 10g this was also a "buffer busy wait".


How can we find the block contention? 
àTo find the block on which currently  suffering from read by other session wait event.

SELECT sw.sql_id ,sw.p1 "file#", sw.p2 "block#", sw.p3 "class#" ,event
FROM v$session sw WHERE event= 'buffer busy wait';



àUsing blok_id or file_id we can find the segment


SELECT relative_fno, owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = &FILE
AND &BLOCK BETWEEN block_id AND block_id + blocks - 1;

àFind the sql causing issue using the sql_id we find in first query


SELECT
      s.p1 file_id, s.p2 block_id,o.object_name obj,
       o.object_type otype,
       s.SQL_ID,
       w.CLASS,event
FROM v$session s,
     ( SELECT ROWNUM CLASS#, CLASS FROM v$waitstat ) w,
      all_objects o
WHERE
 event IN ('buffer busy wait')
and
    w.CLASS#(+)=s.p3
   AND o.object_id (+)= s.row_wait_OBJ#
ORDER BY 1;



SELECT sql_text FROM   v$sqltext WHERE sql_id=&sq_id ORDER BY piece

àWe can also find block and sql on which causing “read by other session” using v$active_session_history


SELECT

     p1 file_id ,  p2  block_id ,o.object_name obj,
       o.object_type otype,
       ash.SQL_ID,
       w.CLASS
FROM v$active_session_history ash,
     ( SELECT ROWNUM CLASS#, CLASS FROM v$waitstat ) w,
      all_objects o
WHERE event='buffer busy wait'
   AND w.CLASS#(+)=ash.p3
   AND o.object_id (+)= ash.CURRENT_OBJ#
      AND ash.sample_time > SYSDATE - &MIN/(60*24)
ORDER BY 1;
 

 &Min  : time you want to see history


SELECT sql_text FROM   v$sqltext WHERE sql_id=&sq_id ORDER BY piece


SELECT
       bbw.cnt,
       bbw.obj,
       bbw.otype,
       bbw.sql_id,
       bbw.block_type,
       NVL(tbs.NAME,TO_CHAR(bbw.p1)) TBS,
       tbs_defs.assm ASSM
FROM (
    SELECT
       COUNT(*) cnt,
       NVL(object_name,CURRENT_OBJ#) obj,
       o.object_type otype,
       ash.SQL_ID sql_id,
       NVL(w.CLASS,'usn '||TO_CHAR(CEIL((ash.p3-18)/2))||' '||
                    DECODE(MOD(ash.p3,2),
                         1,'header',
                         0,'block')) block_type,
       --nvl(w.class,to_char(ash.p3)) block_type,
       ash.p1 p1
    FROM v$active_session_history ash,
        ( SELECT ROWNUM CLASS#, CLASS FROM v$waitstat ) w,
        all_objects o
    WHERE event='buffer busy wait'
      AND w.CLASS#(+)=ash.p3
      AND o.object_id (+)= ash.CURRENT_OBJ#
      AND ash.session_state='WAITING'
     AND ash.sample_time > SYSDATE - &min/(60*24)
      --and w.class# > 18
   GROUP BY o.object_name, ash.current_obj#, o.object_type,
         ash.sql_id, w.CLASS, ash.p3, ash.p1
  ) bbw,
    (SELECT   file_id,
       tablespace_name NAME
  FROM dba_data_files
   ) tbs,
    (SELECT
 tablespace_name    NAME,
        extent_management  LOCAL,
        allocation_type    EXTENTS,
        segment_space_management ASSM,
        initial_extent
     FROM dba_tablespaces
   ) tbs_defs
  WHERE tbs.file_id(+) = bbw.p1
    AND tbs.NAME=tbs_defs.NAME
ORDER BY bbw.cnt








The preceding queries will reveal the specific type of buffer causing the high buffer waits. Your fix
will depend on which buffer class causes the buffer waits, as summarized in the following subsections.



Contention for Segment Header

Every table and index segment has a header block. This block contains the following metadata:
information about the high watermark of the segment, a list of the extents making up the segment,
and information about the free space. To manage the free space, the header block contains
(depending on the type of segment space management that is in use) either freelists or a list of
blocks containing automatic segment space management information. Typically, contention
for a segment header block is experienced when its content is modified by several processes
concurrently. Note that the header block is modified in the following situations:
• If INSERT statements make it necessary to increase the high watermark
• If INSERT statements make it necessary to allocate new extents
• If DELETE, INSERT, and UPDATE statements make it necessary to modify a freelist
A possible solution for these situations is to partition the segment in order to spread the
load over several segment header blocks. Most of the time, this might be achieved with hash
partitioning, although, depending on the load and the partition key, other partitioning methods
might work as well. However, if the problem is because of the second or third situation, other
solutions exist. For the second, you should use bigger extents. In this way, new extents would
seldom be allocated. For the third, which does not apply to tablespaces using automatic segment
space management, freelists can be moved into other blocks by means of freelist groups. In
fact, when several freelist groups are used, the freelists are no longer located in the segment
header block (they are spread on a number of blocks equal to the value specified with the
parameter FREELIST GROUPS, so you will have less contention on them—you are not simply
moving the contention to another place!). Another possibility is to use a tablespace with automaticsegment space management instead of freelist segment space management

In short

If your queries show that the buffer waits are being caused by contention on the segment header, there’s free list contention in the database, due to several processes attempting to insert into the same data block—each of these processes needs to obtain a free list before it can insert data into that block. If you aren’t already using it, you must switch from manual space management to automatic segment space management (ASSM)—under ASSM, the database doesn’t use free lists. However, note that moving to ASSM may not be easily feasible in most cases. In cases where you can’t implement ASSM, you must increase the free lists for the segment in question. You can also try increasing the free list groups as well.


Contention for  Undo Header and Undo Block

Contention for these types of blocks occurs in two situations. The first, and only for undo
header blocks, is when few undo segments are available and lots of transactions are concurrently
committed (or rolled back). This should be a problem only if you are using manual undo
management. In other words, it usually happens if the database administrator has manually
created the rollback segments. To solve this problem, you should use automatic undo management.
The second situation is when several sessions modify and query the same blocks at the
same time. As a result, lots of consistent read blocks have to be created, and this requires you
to access both the block and its associated undo blocks. There is little that can be done about
this situation, other than reducing the concurrency for the data blocks, thereby reducing the
ones for the undo blocks at the same time.


Contention for Extent Map Blocks

As discussed in the section “Contention for Segment Header Blocks,” the segment header
blocks contain a list of the extents that make up the segment. If the list does not fit in the
segment header, it is distributed over several blocks: the segment header block and one or
more extent map blocks. Contention for a segment header block is experienced when concurrent
INSERT statements have to constantly allocate new extents. To solve this problem, you
should use bigger extents.


Contention for Freelist Blocks

As discussed in the section “Contention for Segment Header Blocks,” freelists can be moved
into other blocks, called freelist blocks, by means of freelist groups. Contention for a freelist
block is experienced when concurrent DELETE, INSERT, or UPDATE statements have to modify the freelists. To solve this problem, you should increase the number of freelist groups. Another possibility is to use a tablespace with automatic segment space management instead of freelist segment space management.

Contention for Data Blocks

All the blocks that make up a table or index which stor actual data are called data blocks. Contention for them has two main causes.

The first is when the frequency of table or index scans on a given segment is very high. This problem is because of inefficient execution plans causing frequent table or index scans over the same blocks. Usually it is because of inefficient related-combine operations (for example, nested loop joins). Here, even two or three SQL statements executed concurrently might be enough to cause contention.

The second is when the frequency of executions is very high. This problem is the execution of several SQL statements accessing the same block at the same time. In other words, it is the number of SQL statements executed concurrently against (few) blocks that is the problem. It might be that both happen at the same time. If this is the case, take care of solving the first problem before facing the second one. In fact, the second problem might disappear when the first is gone.


To solve the first problem, SQL tuning is necessary. An efficient execution plan must be
executed in place of the inefficient one.

To solve the second problem, several approaches are available. Which one you have to use
depends on the type of the SQL statement (that is, DELETE, INSERT, SELECT,1 and UPDATE) and on the type of the segment (that is, table or index).

However, before starting, you should always ask one question when the frequency of execution is high: is it really necessary to execute those SQL statements against the same data so often? Actually, it is not unusual to see applications that unnecessarily execute the same SQL
statement too often

. If the frequency of execution cannot be reduced, there are the following
possibilities.

• If there is contention for a table’s blocks because of DELETE, SELECT, and UPDATE statements, you should reduce the number of rows per block. Note that this is the opposite of
the common best practice to fit the maximum number of rows per block. To store fewer
rows per block, either a higher PCTFREE or a smaller block size can be used.

• If there is contention for a table’s blocks because of INSERT statements and freelist segment
space management is in use, the number of freelists can be increased. In fact, the goal of
having several freelists is precisely to spread concurrent INSERT statements over several
blocks. Another possibility is to move the segment into a tablespace with automatic
segment storage management.

• If there is contention for an index’s blocks, there are two possible solutions. First, the
index can be created with the option REVERSE. Note, however, that this method does not
help if the contention is on the root block of the index. Second, the index can be hash
partitioned, based on the leading column of the index key (this creates multiple root
blocks and so helps with root block contention if a single partition is accessed). Because
global hash-partitioned indexes are available as of Oracle Database 10g only, this is not
an option with Oracle9i.

The important thing to note about reverse indexes is that range scans on them cannot
apply restrictions based on range conditions (for example, BETWEEN, >, or <=). Of course, equality predicates are supported

A buffer busy wait indicates that more than one process is simultaneously accessing the same data block. One of the reasons for a high number of buffer busy waits is that an inefficient query is reading too many data blocks into the buffer cache, thus potentially keeping in wait other sessions that want to access one or more of those same blocks. Not only that, a query that reads too much data into the buffer cache may lead to the aging out of necessary blocks from the cache. You must investigate queries that involve the segment causing the buffer busy waits with a view to reducing the number of data blocks they’re reading into the buffer cache.
If your investigation of buffer busy waits reveals that the same block or set of blocks is involved most of the time, a good strategy would be to delete some of these rows and insert them back into the table, thus forcing them onto different data blocks.

Check your current memory allocation to the buffer cache, and, if necessary, increase it. A larger
buffer cache can reduce the waiting by sessions to read data from disk, since more of the data will
already be in the buffer cache. You can also place the offending table in memory by using the KEEP POOL in the buffer cache By making the hot block always available in memory, you’ll
avoid the high buffer busy waits.

Indexes that have a very low number of unique values are called low cardinality indexes. Low
cardinality indexes generally result in too many block reads. Thus, if several DML operations are
occurring concurrently, some of the index blocks could become “hot” and lead to high buffer busy waits.

As a long-term solution, you can try to reduce the number of the low cardinality indexes in your
database. Each Oracle data segment such as a table or an index contains a header block that records information such as free blocks available. When multiple sessions are trying to insert or delete rows from the same segment, you could end up with contention for the data segment’s header block.



Summary

data block
IF OTYPE =
INDEX , then the insert index leaf block is probably hot, solutions are
Hash partition the index
Use reverse key index
TABLE, then insert block is hot,solutions
Use free lists
Put Object in ASSM tablespace
Segment header - If "segment header" occurs at the same time as CLASS= "data block" on the same object and the object is of OTYPE= "TABLE"  then this is just a confirmation that the TABLE needs to use free lists or  ASSM.
File Header Block - Most likely extent allocation problems, look at extent size on tablespace and increase the extent size to there are few extent allocations and less contention on the File Header Block.
free lists - Add free list groups to the object
undo header - Not enough UNDO segments, if using old RBS then switch to AUM
undo block - Hot spot in UNDO, application issue 


Reference:-
Apress.Troubleshooting.Oracle.Performance.Jun.2008
oracle_database_11g_performance_tuning_recipes


Monday, 17 December 2012

Password File In oracle


Password File

To connect to Oracle Database as a privileged user over a nonsecure connection, you must be authenticated by a password file. When using password file authentication, the database uses a password file to keep track of database user names that have been granted the SYSDBA or SYSOPER system privilege

Location :-



Window
$OARCLE_HOME/database
unix
$OARCLE_HOME/dbs


The syntax of the ORAPWD command is as follows:
ORAPWD FILE=abc.txt ENTRIES=50 FORCE=y IGNORECASE=y password=pass

Command arguments are summarized in the following table


Argument
Description
FILE
Name to assign to the password file. You must supply a complete path. If you supply only a file name, the file is written to the current directory.
ENTRIES
(Optional) Maximum number of entries (user accounts) to permit in the file.
FORCE
(Optional) If y, permits overwriting an existing password file.
IGNORECASE
(Optional) If y, passwords are treated as case-insensitive.(11g)


Sharing and Disabling the Password File

You use the initialization parameter REMOTE_LOGIN_PASSWORDFILE to control whether a password file is shared among multiple Oracle Database instances. You can also use this parameter to disable password file authentication. The values recognized for REMOTE_LOGIN_PASSWORDFILE are:
•         NONE: Setting this parameter to NONE causes Oracle Database to behave as if the password file does not exist. That is, no privileged connections are allowed over nonsecure connections.
•         EXCLUSIVE: (The default) An EXCLUSIVE password file can be used with only one instance of one database. Only an EXCLUSIVE file can be modified. Using an EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USERcommand.
•         SHARED: A SHARED password file can be used by multiple databases running on the same server, or multiple instances of an Oracle Real Application Clusters (Oracle RAC) database. A SHARED password file cannot be modified. Therefore, you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error. All users needing SYSDBA orSYSOPER system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.
This option is useful if you are administering multiple databases or an Oracle RAC database.
Granting and Revoking SYSDBA and SYSOPER Privileges
If your server is using an EXCLUSIVE password file, use the GRANT statement to grant the SYSDBA or SYSOPER system privilege to a user, as shown in the following example:
GRANT SYSDBA TO scott;

Find all users who have been granted the SYSDBA privilege.

SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDBA='TRUE';

USERNAME
------------------------------
SCOTT

Use the REVOKE statement to revoke the SYSDBA or SYSOPER system privilege from a user, as shown in the following example:

REVOKE SYSDBA FROM scott;

Because SYSDBA and SYSOPER are the most powerful database privileges, the WITH ADMIN OPTION is not used in the GRANT statement. That is, the grantee cannot in turn grant the SYSDBA or SYSOPER privilege to another user. Only a user currently connected as SYSDBA can grant or revoke another user's SYSDBA orSYSOPER system privileges. These privileges cannot be granted to roles, because roles are available only after database startup. Do not confuse the SYSDBAand SYSOPER database privileges with operating system roles
Keeping Administrator Passwords Synchronized with the Data Dictionary
If you change the REMOTE_LOGIN_PASSWORDFILE initialization parameter from NONE to EXCLUSIVE or SHARED, or if you re-create the password file with a different SYS password, then you must ensure that the passwords in the data dictionary and password file for the SYS user are the same.
To synchronize the SYS passwords, use the ALTER USER statement to change the SYS password. The ALTER USER statement updates and synchronizes both the dictionary and password file passwords.
To synchronize the passwords for non-SYS users who log in using the SYSDBA or SYSOPER privilege, you must revoke and then regrant the privilege to the user, as follows:

--       Find all users who have been granted the SYSDBA privilege.

        SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDBA='TRUE';

--     Revoke and then re-grant the SYSDBA privilege to these users.

           REVOKE SYSDBA FROM non-SYS-user;
           GRANT SYSDBA TO non-SYS-user;

--       Find all users who have been granted the SYSOPER privilege.

           SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSOPER='TRUE';

--       Revoke and regrant the SYSOPER privilege to these users.

           REVOKE SYSOPER FROM non-SYS-user;

Thursday, 6 December 2012

Histogram


Histograms


Histograms tell the Optimizer about the distribution of data within a column. By default (without a histogram), the Optimizer assumes a uniform distribution of rows across the distinct values in a column. As described above, the Optimizer calculates the cardinality for an equality predicate by dividing the total number of rows in the table by the number of distinct values in the column used in the equality predicate. If the data distribution in that column is not uniform (i.e., a data skew) then the cardinality estimate will be incorrect. In order to accurately reflect a non-uniform data distribution, a histogram is required on the column. The presence of a histogram changes the formula used by the Optimizer to estimate the cardinality, and allows it to generate a more accurate execution plan. Oracle automatically determines the columns that need histograms based on the column usage information (SYS.COL_USAGE$), and the presence of a data skew. For example, Oracle will not automatically create a histogram on a unique column if it is only seen in equality predicates. There are two types of histograms, frequency or height-balanced. Oracle determines the type of

histogram to be created based on the number of distinct values in the column.

Frequency Histograms


Frequency histograms are created when the number of distinct values in the column is less than 254. Oracle uses the following steps to create a frequency histogram.

1. Let’s assume that Oracle is creating a frequency histogram on the PROMO_CATEGORY_ID column of the PROMOTIONS table. The first step is to select the PROMO_CATEGORY_ID from  the PROMOTIONS table ordered by PROMO_CATEGORY_ID.
2. Each PROMO_CATEGORY_ID is then assigned to its own histogram bucket3.

PROMO_CATEGORY_ID
2                                             Bucket 1 has endpoint 2
3                                             Bucket 2 has endpoint 3
3                                             Bucket 3 has endpoint 3
--------------                          -------------------------
3                                             Bucket 115 has endpoint 3
--------------                          -------------------------
9                                             Bucket 417 has endpoint 9
--------------                           -------------------------
9                                             Bucket 483 has endpoint 9
--------------                           -------------------------
10                                             Bucket 502 has endpoint 10
10                                             Bucket 503 has endpoint 10

3.At this stage we could have more than 254 histogram buckets, so the buckets that hold the same value are then compressed into the highest bucket with that value. In this case, buckets 2 through 115 are compressed into bucket 115, and buckets 484 through 503 are compressed into bucket 503, and so on until the total number of buckets remaining equals the number of  distinct values in the column Note the above steps are for illustration purposes. The DBMS_STATS package has been optimized to build compressed histograms directly

PROMO_CATEGORY_ID
2                                             Bucket 1 has endpoint 2
3                                             Bucket 3 has endpoint 3
9                                             Bucket 483 has endpoint 9
10                                             Bucket 503 has endpoint 10

4. The Optimizer now accurately determines the cardinality for predicates on the PROMO_CATEGORY_ID column using the frequency histogram. For example, for the predicate PROMO_CATEGORY_ID =10, the Optimizer would first need to determine how many buckets in the histogram have 10 as their end point. It does this by finding the bucket whose endpoint
is 10, bucket 503, and then subtracts the previous bucket number, bucket 483, 503 - 483 = 20.
Then the cardinality estimate would be calculated using the following formula
(number ofbucket endpoints / total number of bucket) X NUM_ROWS,
20/503 X 503,
so the number of rows in the PROMOTOINS table where PROMO_CATEGORY_ID =10 is 20.


Height balanced Histograms


Height-balanced histograms are created when the number of distinct values in the column is greater than 254. In a height-balanced histogram, column values are divided into buckets so that each bucke contains approximately the same number of rows. Oracle uses the following steps to create a heightbalanced histogram.


1. Let’s assume that Oracle is creating a height-balanced histogram on the CUST_CITY_ID
column of the CUSTOMERS table because the number of distinct values in the CUST_CITY_ID
column is greater than 254. Just like with a frequency histogram, the first step is to
select the CUST_CITY_ID from the CUSTOMERS table ordered by CUST_CITY_ID.
2. There are 55,500 rows in the CUSTOMERS table and there is a maximum of 254 buckets in a
histogram. In order to have an equal number of rows in each bucket, Oracle must put 219
rows in each bucket. The 219th CUST_CITY_ID from step one will become the endpoint for
the first bucket. In this case that is 51043. The 438th CUST_CITY_ID from step one will
become the endpoint for the second bucket, and so on until all 254 buckets are filled

ROW_COUNT        CUST_CITY_ID
1                                             51040
----                                             ---------                    ------------------------------219                                             51043                        Bucket 1 has endpoint 51043
5256                                           51166                        Bucket 24 has endpoint 51166
5475 5                                        1166                          Bucket 25 has endpoint 51166
55500                                         52631                        Bucket 254 has endpoint 52631
 
3. Once the buckets have been created Oracle checks to see if the endpoint of the first bucket is the minimum value for the CUST_CITY_ID column. If it is not, a "zero" bucket is added to the histogram that has the minimum value for the CUST_CITY_ID column as its end point.

ROW_COUNT                 CUST_CITY_ID
1                                             51040                            Bucket 0 has endpoint 51040
----                                         ---------                         ------------------------------
219                                          51043                          Bucket 1 has endpoint 51043
5256                                        51166                          Bucket 24 has endpoint 51166
5475                                        51166                          Bucket 25 has endpoint 51166
55500                                      52631                          Bucket 254 has endpoint 52631

4. Just as with a frequency histogram, the final step is to compress the height-balanced histogram, and remove the buckets with duplicate end points. The value 51166 is the end Understanding Optimizer Statistics point for bucket 24 and bucket 25 in our height-balanced histogram on the CUST_CITY_ID column. So, bucket 24 will be compressed in bucket 25

ROW_COUNT                 CUST_CITY_ID
1                                             51040                             Bucket 0 has endpoint 51040
----                                         ---------                          ------------------------------- 
219                                          51043                            Bucket 1 has endpoint 51043
5475                                        51166                            Bucket 25 has endpoint 51166
55500                                      52631                            Bucket 254 has endpoint 52631

5. The Optimizer now computes a better cardinality estimate for predicates on the CUST_CITY_ID column by using the height-balanced histogram. For example, for the predicate CUST_CITY_ID =51806, the Optimizer would first check to see how many buckets in the histogram have 51806 as their end point. In this case, the endpoint for bucket 136,137,138 and 139 is 51806(info found in USER_HISTOGRAMS). The Optimizer then uses the following formula .However, if the predicate was CUST_CITY_ID =52500, which is not the endpoint for any bucket then the Optimizer uses a different formula. For values that are the endpoint for only one bucket or are not an endpoint at all, the Optimizer uses the following formula:
DENSITY X number of rows in the table
where DENSITY is calculated ‘on the fly’ during optimization using an internal formula based  on information in the histogram. The value for DENSITY seen in the dictionary view
USER_TAB_COL_STATISTICS is not the value used by the Optimizer from Oracle Database 10.2.0.4 onwards. This value is recorded for backward compatibility, as this is the value used  in Oracle Database 9i and earlier releases of 10g. Furthermore, if the parameter  OPTIMIZER_FEATURES_ENABLE is set to version release earlier than 10.2.0.4, the value for  DENSITY in the dictionary view will be used.

Wednesday, 5 December 2012

EXECUTION PLAN

Introduction
The purpose of the Oracle Optimizer is to determine the most efficient execution plan for your queries. It makes these decisions based on the statistical information it has about your data and by leveraging Oracle database features such as hash joins, parallel query, partitioning, etc.
Still it isexpected that the optimizer will generate sub-optimal plans  for some SQL statements now and then. In cases where there is an alternative plan that performed better than the plan generated by the optimizer, the first step in diagnosing why the Optimizer picked the sub-optimal plan is to visually  inspect both of the execution plans.
Examining the different aspects of an execution plan, from selectivity to parallel execution and understanding what information you should be gleaming from  the plan can be overwhelming even for the most experienced DBA. This paper
offers a detailed explanation about each aspect of the execution plan and  an insight into what caused the CBO to make the decision it did.

Setup
If it is not already present create the SCOTT schema.
conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlsampl.sql

Create a PLAN_TABLE if it does not already exist.
conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
GRANT ALL ON sys.plan_table TO public;

EXPLAIN PLAN command - This displays an execution plan for a SQL statement without actually executing the statement.
V$SQL_PLAN - A dynamic performance view introduced in Oracle 9i that shows the execution plan for a SQL statement that has been compiled into a cursor and stored in the cursor cache. Under certain conditions the plan shown when using EXPLAIN PLAN can be different from the plan shown using V$SQL_PLAN. For example, when the SQL statement contains bind variables the plan shown from using EXPLAIN PLAN ignores the bind variable values while the plan shown in
V$SQL_PLAN takes the bind variable values into account in the plan generation process. Displaying an execution plan has been made easier since the introduction of the DBMS_XPLAN  package in Oracle 9i and by the enhancements made to it in subsequent releases. This package  provides several PL/SQL interfaces to display the plan from different sources:
• EXPLAIN PLAN command
• V$SQL_PLAN
• Automatic Workload Repository (AWR)
• SQL Tuning Set (STS)
• SQL Plan Baseline (SPM)

Using the EXPLAIN PLAN command and the DBMS_XPLAN.DISPLAY function
The following examples illustrate how to generate and display an execution plan for our original
SQLstatement using the different functions provided in the DBMS_XPLAN package.

The arguments for DBMS_XPLAN.DISPLAY are:
• plan table name (default 'PLAN_TABLE')
• statement_id (default null means the last statement inserted into the plan table)
• format, controls the amount of information displayed (default is 'TYPICAL')
To leverage the explain plan functionality you need the appropriate privileges to run the actual statement you are trying to explain. A default PLAN_TABLE exists for every user without the need  to create it beforehand.

SQL> explain plan for
  2    select a.deptno , avg(b.sal) ,c.empno
  3    from dept a, bonus b, emp c
  4    where a.deptno=c.deptno and b.ename=b.ename
  5   group by c.empno,a.deptno;

Explained.

SQL> SELECT * FROM   TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 253314618
----------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |     1 |    30 |     6  (17)| 00:00:01 |
|   1 |  HASH GROUP BY         |         |     1 |    30 |     6  (17)| 00:00:01 |
|   2 |   NESTED LOOPS         |         |     1 |    30 |     5   (0)| 00:00:01 |
|   3 |    MERGE JOIN CARTESIAN|         |     1 |    27 |     5   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL  | BONUS   |     1 |    20 |     2   (0)| 00:00:01 |
|   5 |     BUFFER SORT        |         |    14 |    98 |     3   (0)| 00:00:01 |
|   6 |      TABLE ACCESS FULL | EMP     |    14 |    98 |     3   (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN   | PK_DEPT |     1 |     3 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("B"."ENAME" IS NOT NULL)
   7 - access("A"."DEPTNO"="C"."DEPTNO")

20 rows selected.

DISPLAY_CURSOR Function

In Oracle 10g Release 1 Oracle introduced the DISPLAY_CURSOR function. Rather than displaying an execution plan from the PLAN_TABLE, it displays the actual execution plan used to run a query  stored in the cursor cache. This information is gathered from the V$SQL_PLAN_STATISTICS_ALL,  V$SQL and V$SQL_PLAN views, so the user must have access to these. It accepts three optional  parameters:

sql_id - The SQL_ID of the statement in the cursor cache. The SQL_ID as available from the  V$SQL and V$SQLAREA views, or from the V$SESSION view using the PREV_SQL_ID column. If omitted, the last cursor executed by the session is displayed.
child_number - The child number of the cursor specified by the SQL_ID parameter. If not specified, all cursors for the specified SQL_ID are diaplyed.
format - In addition to the setting available for the DISPLAY function, this function also has  'RUNSTATS_LAST' and 'RUNSTATS_TOT' to display the last and total runtime statistics respectively.
These additional format options require "STATISTICS_LEVEL=ALL".

DISPLAY Function

The DISPLAY function allows us to display the execution plan stored in the plan table. First we explain an SQL statement.

CONN scott/tiger

SQL> select * from table (DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ADVANCED'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID  9r63us1r3wh3c, child number 0
-------------------------------------
  select a.deptno , avg(b.sal) ,c.empno   from dept a, bonus b, emp c
where a.deptno=c.deptno and b.ename=b.ename  group by c.empno,a.deptno

Plan hash value: 253314618

----------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |       |       |     6 (100)|          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY         |         |     1 |    30 |     6  (17)| 00:00:01 |
|   2 |   NESTED LOOPS         |         |     1 |    30 |     5   (0)| 00:00:01 |
|   3 |    MERGE JOIN CARTESIAN|         |     1 |    27 |     5   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL  | BONUS   |     1 |    20 |     2   (0)| 00:00:01 |
|   5 |     BUFFER SORT        |         |    14 |    98 |     3   (0)| 00:00:01 |
|   6 |      TABLE ACCESS FULL | EMP     |    14 |    98 |     3   (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN   | PK_DEPT |     1 |     3 |     0   (0)|          |
----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------
   1 - SEL$1
   4 - SEL$1 / B@SEL$1
   6 - SEL$1 / C@SEL$1
   7 - SEL$1 / A@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "B"@"SEL$1")
      FULL(@"SEL$1" "C"@"SEL$1")
      INDEX(@"SEL$1" "A"@"SEL$1" ("DEPT"."DEPTNO"))
      LEADING(@"SEL$1" "B"@"SEL$1" "C"@"SEL$1" "A"@"SEL$1")
      USE_MERGE(@"SEL$1" "C"@"SEL$1")
      USE_NL(@"SEL$1" "A"@"SEL$1")
      END_OUTLINE_DATA

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("B"."ENAME" IS NOT NULL)
   7 - access("A"."DEPTNO"="C"."DEPTNO")

Column Projection Information (identified by operation id):
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
   1 - "C"."EMPNO"[NUMBER,22], "A"."DEPTNO"[NUMBER,22], AVG("B"."SAL")[22]
   2 - "B"."SAL"[NUMBER,22], "C"."EMPNO"[NUMBER,22],
       "A"."DEPTNO"[NUMBER,22]
   3 - "B"."SAL"[NUMBER,22], "C"."EMPNO"[NUMBER,22],
       "C"."DEPTNO"[NUMBER,22]
   4 - "B"."SAL"[NUMBER,22]
   5 - (#keys=0) "C"."EMPNO"[NUMBER,22], "C"."DEPTNO"[NUMBER,22]
   6 - "C"."EMPNO"[NUMBER,22], "C"."DEPTNO"[NUMBER,22]
   7 - "A"."DEPTNO"[NUMBER,22]
65 rows selected.


Extending DBMS_XPLAN
Adrian Billington has created an "XPlan Utility", available here, to extend the output of DBMS_XPLAN to
 include the execution order of the steps. The following output shows the difference between the default
output and that produced by Adrian's XPlan Utility.

SQL>  EXPLAIN PLAN FOR
  2   SELECT *
  3   FROM   emp e, dept d
  4   WHERE  e.deptno = d.deptno
  5  AND    e.sal=1500
  6  ;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 351108634
---------------------------------------------------------------------------------------------------
| Id  | Pid | Ord | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------

|   0 |     |   5 | SELECT STATEMENT             |         |     1 |   117 |     4   (0)| 00:00:01 |

|   1 |   0 |   4 |  NESTED LOOPS                |         |     1 |   117 |     4   (0)| 00:00:01 |

|*  2 |   1 |   1 |   TABLE ACCESS FULL          | EMP     |     1 |    87 |     3   (0)| 00:00:01 |

|   3 |   1 |   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    30 |     1   (0)| 00:00:01 |

|*  4 |   3 |   2 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("E"."SAL"=1500)
   4 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
   - dynamic sampling used for this statement

About
------
  - XPlan v1.2 by Adrian Billington (http://www.oracle-developer.net/utilities.php)



Thursday, 22 November 2012

'log file sync' wait troubleshooting

 'log file sync' wait

When a user session commits, the session's redo information needs to be flushed from memory to the redo logfile to make it permanent.

At the time of commit, the user session will post the LGWR to write the log buffer (containing the current unwritten redo, including this session's redo information) to the redo log file. When the LGWR has finished writing, it will post the user session to notify it that this has completed. The user session waits on 'log file sync' while waiting for LGWR to post it back to confirm all redo changes have made it safely on to disk.

The time between the user session posting the LGWR and the LGWR posting the user after the write has completed is the wait time for 'log file sync' that the user session will show.
Note that if a sync is ongoing, other sessions that want to commit (and thus flush log information) will also wait for the LGWR to sync and will also wait on 'log file sync'?

What should be collected for initial diagnosis of 'log file sync' waits ?

To initially analyse 'log file sync' waits the following information is helpful:
• AWR report from a similar time frame and period where 'log file sync' waits are not experienced in order to use as a baseline for reasonable performance for comparison purposes
• AWR report when 'log file sync' waits are occurring
Note: The 2 reports should be for between 10-30 minutes each.
• Lgwr trace file
The lgwr trace file will show warning messages for periods when 'log file parallel wait' may be high
What causes high waits for 'log file sync'?
Waits for the 'log file sync' event can occur at any stage between a user process posting the LGWR to write redo information and the LGWR posting back the user process after the redo has been written from the log buffer to disk and the user process waking up.

• Issues affecting LGWR's IO Performance
The primary question we are looking to answer here is "Is LGWR slow in writing to disk?". The following steps can assist determine whether this the case or not.
Compare the average wait time for 'log file sync' to the average wait time for 'log file parallel write'.


Wait event 'log file parallel' write is waited for by LGWR while the actual write
operation to the redo is occurring. The duration of the event shows the time waited
for the IO portion of the operation to occur. For more information on "log
file parallel write"


If the proportion of the 'log file sync' time spent on 'log file parallel write' times is high, then most of the wait time is due to IO (waiting for the redo to be written). The performance of LGWR in terms of IO should be examined. As a rule of thumb, an average time for 'log file parallel write' over 20 milliseconds suggests a problem with IO subsystem.

Recommendations
o Work with the system administrator to examine the filesystems where the redologs are located with a view to improving the performance of IO.
o Do not put redo logs on RAID 5.
o Do not put redo logs on Solid State Disk (SSD)
Although generally, Solid State Disks write performance is good on average, they may endure write peaks which will highly increase waits on 'log file sync'
o Look for other processes that may be writing to that same location and ensure that the disks have sufficient bandwith to cope with the required capacity. If they don't then move the activity or the redo.
o Ensure that the log_buffer is not too big. A very large log_buffer can have an adverse affect as waits will be longer when flushes occur. When the buffer fills up, it has to write all the data into the redo log file and the LGWR will wait unitl the last I/O is completed.
Check LGWR Traces
Even if the average wait for 'log file parallel write' may be in the normal range, there may be peaks where the write time is longer and will therefore influence waits on 'log file sync'. From 10.2.0.4, messages are written in the LGWR trace when a write to the log file takes more than 500 ms. This is quite a high threshold so a lack of messages does not necessarily mean there is no problem. The messages look similar to the following:

*** 2012-11-21 23:18:25.669
Warning: log write elapsed time 1292ms, size 1151KB

*** 2012-11-21 23:18:53.543
Warning: log write elapsed time 556ms, size 741KB

*** 2012-11-21 23:21:29.785
Warning: log write elapsed time 2392ms, size 723KB

*** 2012-11-22 00:24:35.743
Warning: log write elapsed time 618ms, size 2009KB

*** 2012-11-22 00:30:30.525
Warning: log write elapsed time 539ms, size 5943KB

Note: Peaks like the following may not have a high influence on the 'log file parallel wait' if they are far between. However , if 100s of sessions are waiting for the 'log file parallel wait' to complete, the total wait for 'log file sync' can be high as the wait time will be multiplied for thr 100s of sessions. Therefore it is worth investigating the reason for the high peaks in IO for the log writer.

Recommendations
o Work with the system administrator to examine what else is happening at this time that may be causing the peaks in LGWR writing to disk
o Truss of the LGWR process when the slow down is occurring may help identify where time is going

Note: These warnings can be particularly useful for preempting potential issues. Even if a general problem in terms of the average wait time is not been seen, by highlighting extreme peaks of IO performance, a dba can have a useful indicator that LGWR is encountering intermittent issues. These can then be resolved before they cause outages or similar.

Check to see if redo logs are large enough

A 'log file sync' operation is performed every time the redo logs switch to the next log to ensure that everything is written before the next log is started. Standard recommendations are that a log switch should occur at most once every 15 to 20 minutes. If switches occur more frequently than this, then more 'log file sync' operations will occur meaning more waiting for individual sessions.
o Check the time between log file switches in alert.log
Thu Jun 02 14:57:01 2011
Thread 1 advanced to log sequence 2501 (LGWR switch)
Current log# 5 seq# 2501 mem# 0: /opt/oracle/oradata/orcl/redo05a.log
Current log# 5 seq# 2501 mem# 1: /opt/oracle/logs/orcl/redo05b.log
Thu Nov 03 14:59:12 2011
Thread 1 advanced to log sequence 2502 (LGWR switch)
Current log# 6 seq# 2502 mem# 0: /opt/oracle/oradata/orcl/redo06a.log
Current log# 6 seq# 2502 mem# 1: /opt/oracle/logs/orcl/redo06b.log
Thu Nov 03 15:03:01 2011
Thread 1 advanced to log sequence 2503 (LGWR switch)
Current log# 4 seq# 2503 mem# 0: /opt/oracle/oradata/orcl/redo04a.log
Current log# 4 seq# 2503 mem# 1: /opt/oracle/logs/orcl/redo04b.log

o You can also check the average time for log switch in the AWR report

The example above shows that based on the information in AWR, there are 29.98 redo logs switches per hour: ~1 switch every 2 minutes. This is higher than the accepted value of 1 switch every 15-20 minutes and will have an affect on the time foreground process will need to wait for 'log file sync' waits to complete because the overhead of initiating the sync operation more than necessary.

• Excessive Application Commits
In this case the question to answer is "Is the Application Committing too Frequently?".
If it is , then the excessive commit activity can cause performance issues since commits flush redo from the log buffer to the redo logs which can cause waits for 'log file sync'.

To identify a potential high commit rate, if the average wait time for 'log file sync' is much higher than the average wait time for 'log file parallel write', then this means that most of the time waiting is not due to waiting for the redo to be written and thus slow IO is not the cause of the problem. The surplas time is CPU activity and is most commonly contention caused by over committing.

Additionally, if the average time waited on 'log file sync' is low, but the number of waits is high, then the application might be committing too frequently.

Compare the average user commits to user calls using AWR report

 if the user calls/user commits (average user calls per commit) is less than 30, then commits are happening too frequently:

 Recommendations

o If there are lots of short duration transactions, see if it is possible to group transactions together so there are fewer distinct COMMIT operations. Since it is mandatory for each commit to receive confirmation that the relevant REDO is on disk, additional commits can add significantly to the overhead. Although commits can be "piggybacked" by Oracle, reducing the overall number of commits by batching transactions can have a very beneficial effect.
o See if any of the processing can use the COMMIT NOWAIT option (be sure to understand the semantics of this before using it).
o See if any activity can safely be done with NOLOGGING / UNRECOVERABLE options.
























Tuesday, 20 November 2012

CPU WAIT EVENT

Scheduling and Switching

The operating system may spend excessive time scheduling and switching processes. Examine the way in which you are using the operating system, because you could be using too many processes. On NT systems, do not overload your server with too many non-Oracle processes.


Oracle CPU Utilization

This section explains how to examine the processes running in Oracle. Three dynamic performance views provide information on Oracle processes:

V$SYSSTAT shows Oracle CPU usage for all sessions. The statistic "CPU used by this session" shows the aggregate CPU used by all sessions.
V$SESSTAT shows Oracle CPU usage per session. You can use this view to determine which particular session is using the most CPU.
V$RSRC_CONSUMER_GROUP shows CPU utilization statistics on a per consumer group basis, if you are running the Oracle Database Resource Manager.

Reparsing SQL Statements

When Oracle executes a SQL statement, it parses it to determine whether the syntax and its contents are correct. This process can consume significant overhead. Once parsed, Oracle does not parse the statement again unless the parsing information is aged from the memory cache and is no longer available. Ineffective memory sharing among SQL statements can result in reparsing. Use the following procedure to determine whether reparsing is occurring:

Get the parse time CPU and CPU figures used by this session from the "Statistics" section of the estat report or from V$SYSTATS. For example:

SELECT * FROM V$SYSSTAT
WHERE NAME IN('parse time cpu', 'parse time elapsed', 'parse count (hard)');


Now you can detect the general response time on parsing. The more your application is parsing, the more contention exists, and the more time your system spends waiting. If parse time CPU represents a large percentage of the CPU time, then time is being spent parsing instead of executing statements. If this is the case, then it is likely that the application is using literal SQL and not sharing it, or the shared pool is poorly configured.

Query V$SQLAREA to find frequently reparsed statements. For example:

SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS
FROM V$SQLAREA
ORDER BY PARSE_CALLS;


Tune the statements with the higher numbers of parse calls.

If the parse time CPU is only a small percentage of the total CPU used, then you should determine where the CPU resources are going. There are several things you can do to help with this.

Find statements with large numbers of buffer gets, because these are typically heavy on CPU.

The following statement finds SQL statements which frequently access database buffers. Such statements are probably looking at many rows of data.

SELECT ADDRESS, HASH_VALUE, BUFFER_GETS, EXECUTIONS,
BUFFER GETS/EXECUTIONS "GETS/EXEC", SQL_TEXT
FROM V$SQLAREA
WHERE BUFFER_GETS > 50000
AND EXECUTIONS > 0
ORDER BY 3;


This example shows which SQL statements have the most buffer_gets and use the most CPU. The statements of interest are those with a large number of gets per execution, especially if execution is high. It is very beneficial to have an understanding of the application components to know which statements are expected to be expensive.

After candidate statements have been isolated, the full statement text can be obtained using the following query, substituting relevant values for ADDRESS and HASH_VALUE pairs. For example:

SELECT SQL_TEXT
FROM V$SQLTEXT
WHERE ADDRESS='&ADDRESS_WANTED'
AND HASH_VALUe=&HASH_VALUE
ORDER BY piece;


The statement can then be explained (using EXPLAIN PLAN) or isolated for further testing to see how CPU-intensive it really is. If the statement uses bind variables and if your data is highly skewed, then the statement may only be CPU-intensive for certain bind values.

Find which sessions are responsible for most CPU usage. The following statement helps locate sessions which have used the most CPU:

SELECT v.SID, SUBSTR(s.NAME,1,30) "Statistic", v.VALUE
FROM V$STATNAME s, V$SESSTAT v
WHERE s.NAME = 'CPU used by this session'
AND v.STATISTIC# = s.STATISTIC#
AND v.VALUE > 0
ORDER BY 3;


Note:
CPU time is cumulative; therefore, a session that has been connected for several days may appear to be heavier on CPU than one that has only been connected for a short period of time. Thus, it is better to write a script to sample the difference in the statistic between two known points in time, letting you see how much CPU was used by each session in a known time frame.

After any CPU-intensive sessions have been identified, the V$SESSION view can be used to get more information. At this stage, it is generally best to revert to user session tracing (SQL_TRACE) to determine where the CPU is being used.

Trace typical user sessions using the SQL_TRACE option to see how CPU is apportioned amongst the main application statements.

After these statements have been identified, you have the following three options for tuning them:

Rewrite the application so that statements do not continually reparse.
Reduce parsing by using the initialization parameter SESSION_CACHED_CURSORS.
If the parse count is small, the execute count is small, and the SQL statements are very similar except for the WHERE clause, then you may find that hard coded values are being used instead of bind variables. Use bind variables to reduce parsing.



If your system must scan too many buffers in the foreground to find a free buffer, then it wastes CPU resources. To alleviate this problem, tune the DBWn process(es) to write more frequently.

Wait Detection

Whenever an Oracle process waits for something, it records it as a wait using one of a set of predefined wait events. (See V$EVENT_NAME for a list of all wait events). Some of these events can be considered idle events; i.e., the process is waiting for work. Other events indicate time spent waiting for a resource or action to complete. By comparing the relative time spent waiting on each wait event and the "CPU used by this session" (from above), you can see where the Oracle instance is spending most of its time. To get an indication of where time is spent, follow these steps:

Review either the V$SYSTATS view or the wait events section of the AWR report.
Ignore any idle wait events. Common idle wait events include:
Client message
SQL*Net message from client
SQL*Net more data from client
RDBMS IPC message
Pipe get
Null event
PMON timer
SMON timer
Parallel query dequeue
Ignore any wait events that represent a very small percentage of the total time waited.
Add the remaining wait event times, and calculate each one as a percentage of total time waited.
Compare the total time waited with the CPU used by this session figure.
Find the event with the largest wait event time. This may be the first item you want to tune.
Latch Contention

Latch contention is a symptom of CPU problems; it is not usually a cause. To resolve it, you must locate the latch contention within your application, identify its cause, and determine which part of your application is poorly written.

In some cases, the spin count may be set too high. It's also possible that one process may be holding a latch that another process is attempting to secure. The process attempting to secure the latch may be endlessly spinning. After a while, this process may go to sleep and later resume processing and repeat its ineffectual spinning. To resolve this:

Check the Oracle latch statistics. The "latch free" event in V$SYSTEM_EVENT shows how long processes have been waiting for latches. If there is no latch contention, then this statistic does not appear. If there is a lot of contention, then it may be better for a process to go to sleep at once when it cannot obtain a latch, rather than use CPU time by spinning.
Look for the ratio of CPUs to processes. If there are large numbers of both, then many processes can run. But, if a single process is holding a latch on a system with ten CPUs, then reschedule that process so it is not running. But, ten other processes may run ineffectively trying to secure the same latch. This situation wastes, in parallel, some CPU resource.
Check V$LATCH_MISSES, which indicates where in the Oracle code most contention occurs.

VMSTATS

]$ vmstat -a

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free inact active si so bi bo in cs us sy id wa st
1 0 3018448 444680 41001060 54450332 1 1 1363 514 0 0 5 0 93 2 0


FIELD DESCRIPTION FOR VM MODE

Procs
r: The number of processes waiting for run time.
b: The number of processes in uninterruptible sleep.


Memory
swpd: the amount of virtual memory used.
free: the amount of idle memory.
buff: the amount of memory used as buffers.
cache: the amount of memory used as cache.
inact: the amount of inactive memory. (-a option)
active: the amount of active memory. (-a option)

Swap
si: Amount of memory swapped in from disk (/s).
so: Amount of memory swapped to disk (/s).

IO
bi: Blocks received from a block device (blocks/s).
bo: Blocks sent to 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
These are percentages of total CPU time.
us: Time spent running non-kernel code. (user time, including nice time)
sy: Time spent running kernel code. (system time)
id: Time spent idle. Prior to Linux 2.5.41, this includes IO-wait time.
wa: Time spent waiting for IO. Prior to Linux 2.5.41, shown as zero.

$ vmstat -d
disk- ------------reads------------ ------------writes----------- -----IO------
total merged sectors ms total merged sectors ms cur sec
ram0 0 0 0 0 0 0 0 0 0 0
ram1 0 0 0 0 0 0 0 0 0 0
sda 23491424 38630865 1269137454 229607384 640208708 443248801 47228429413 2423012429 0 420137
loop0 0 0 0 0 0 0 0 0 0 0
loop1 0 0 0 0 0 0 0 0 0 0

FIELD DESCRIPTION FOR DISK MODE Reads
total: Total reads completed successfully
merged: grouped reads (resulting in one I/O)
sectors: Sectors read successfully
ms: milliseconds spent reading

Writes
total: Total writes completed successfully
merged: grouped writes (resulting in one I/O)
sectors: Sectors written successfully
  ms: milliseconds spent writing

IO
cur: I/O in progress
s: seconds spent for I/O