iostat: Reports Central Processing Unit (CPU) statistics and input/output statistics for the entire system, adapters, tty devices, disks and CD-ROMs.
Syntax
iostat [ -s ] [ -a ] [ -d | -t ] [ PhysicalVolume ... ] [ Interval [ Count ] ]
Description
The iostat command is used for monitoring system input/output device loading by observing the time the physical disks are active in relation to their average transfer rates. The iostat command generates reports that can be used to change system configuration to better balance the input/output load between physical disks and adapters.
The first report generated by the iostat command provides statistics concerning the time since the system was booted. Each subsequent report covers the time since the previous report. All statistics are reported each time the iostat command is run. The report consists of a tty and CPU header row followed by a row of tty and CPU statistics. On multiprocessor systems, CPU statistics are calculated system-wide as averages among all processors.
If the -s flag is specified, a system-header row is displayed followed by a line of statistics for the entire system. The hostname of the system is printed in the system-header row.
If the -a flag is specified, an adapter-header row is displayed followed by a line of statistics for the adapter. This will be followed by a disk-header row and the statistics of all the disks/CD-ROMs connected to the adapter. Such reports are generated for all the disk adapters connected to the system.
A disks header row is displayed followed by a line of statistics for each disk that is configured. If the PhysicalVolume parameter is specified, only those names specified are displayed.
If the PhysicalVolume parameter is specified, one or more alphabetic or alphanumeric physical volumes can be specified. If the PhysicalVolume parameter is specified, the tty and CPU reports are displayed and the disk report contains statistics for the specified drives. If a specified logical drive name is not found, the report lists the specified name and displays the message Drive Not Found. If no Logical Drive Names are specified, the report contains statistics for all configured disks and CD-ROMs. If no drives are configured on the system, no disk report is generated. The first character in the PhysicalVolume parameter cannot be numeric.
The Interval parameter specifies the amount of time in seconds between each report. The first report contains statistics for the time since system startup (boot). Each subsequent report contains statistics collected during the interval since the previous report. The Count parameter can be specified in conjunction with the Interval parameter. If the Count parameter is specified, the value of count determines the number of reports generated at Interval seconds apart. If the Interval parameter is specified without the Count parameter, the iostat command generates reports continuously.
The iostat command is useful in determining whether a physical volume is becoming a performance bottleneck and if there is potential to improve the situation. The % utilization field for the physical volumes indicates how evenly the file activity is spread across the drives. A high % utilization on a physical volume is a good indication that there may be contention for this resource. Since the CPU utilization statistics are also available with the iostat report, the percentage of time the CPU is in I/O wait can be determined at the same time. Consider distributing data across drives if the I/O wait time is significant and the disk utilization is not evenly distributed across volumes.
Note: Some system resource is consumed in maintaining disk I/O history for the iostat command. Use the sysconfig subroutine, or the System Management Interface Tool (SMIT) to stop history accounting.
Reports
The iostat command generates four types of reports, the tty and CPU Utilization report, the Disk Utilization report, the System throughput report and the Adapter throughput report.
tty and CPU Utilization Report
The first report generated by the iostat command is the tty and CPU Utilization Report. For multiprocessor systems, the CPU values are global averages among all processors. Also, the I/O wait state is defined system-wide and not per processor. The report has the following format:
Column Description
tin Shows the total number of characters read by the system for all ttys.
tout Shows the total number of characters written by the system to all ttys.
% user Shows the percentage of CPU utilization that occurred while executing at the user level (application).
% sys Shows the percentage of CPU utilization that occurred while executing at the system level (kernel).
% idle Shows the percentage of time that the CPU or CPUs were idle and the system did not have an outstanding disk I/O request.
% iowait Shows the percentage of time that the CPU or CPUs were idle during which the system had an outstanding disk I/O request.
This information is updated at regular intervals by the kernel (typically sixty times per second). The tty report provides a collective account of characters per second received from all terminals on the system as well as the collective count of characters output per second to all terminals on the system.
Methods Used to Compute CPU Disk I/O Wait Time
Operating system version 4.3.3 and later contain enhancements to the method used to compute the percentage of CPU time spent waiting on disk I/O (wio time).The method used in AIX 4.3.2 and earlier versions of the operating system can, under certain circumstances, give an inflated view of wio time on SMPs. The wio time is reported by the commands sar (%wio), vmstat (wa) and iostat (% iowait).
The method used in AIX 4.3.2 and earlier versions is as follows: At each clock interrupt on each processor (100 times a second per processor), a determination is made as to which of the four categories (usr/sys/wio/idle) to place the last 10 ms of time. If the CPU was busy in usr mode at the time of the clock interrupt, then usr gets the clock tick added into its category. If the CPU was busy in kernel mode at the time of the clock interrupt, then the sys category gets the tick. If the CPU was not busy, a check is made to see if any I/O to disk is in progress. If any disk I/O is in progress, the wio category is incremented. If no disk I/O is in progress and the CPU is not busy, the idle category gets the tick. The inflated view of wio time results from all idle CPUs being categorized as wio regardless of the number of threads waiting on I/O. For example, systems with just one thread doing I/O could report over 90 percent wio time regardless of the number of CPUs it has.
The method used in AIX 4.3.3 and later is as follows: The change in operating system version 4.3.3 is to only mark an idle CPU as wio if an outstanding I/O was started on that CPU. This method can report much lower wio times when just a few threads are doing I/O and the system is otherwise idle. For example, a system with four CPUs and one thread doing I/O will report a maximum of 25 percent wio time. A system with 12 CPUs and one thread doing I/O will report a maximum of 8 percent wio time. NFS client reads/writes go through the VMM, and the time that biods spend in the VMM waiting for an I/O to complete is now reported as I/O wait time.
Disk Utilization Report
The second report generated by the iostat command is the Disk Utilization Report. The disk report provides statistics on a per physical disk basis. The report has a format similar to the following:
% tm_act Indicates the percentage of time the physical disk was active (bandwidth utilization for the drive).
Kbps Indicates the amount of data transferred (read or written) to the drive in KB per second.
tps Indicates the number of transfers per second that were issued to the physical disk. A transfer is an I/O request to the physical disk. Multiple logical requests can be combined into a single I/O request to the disk. A transfer is of indeterminate size.
Kb_read The total number of KB read.
Kb_wrtn The total number of KB written.
Statistics for CD-ROM devices are also reported.
For large system configurations where a large number of disks are configured, the system can be configured to avoid collecting physical disk input/output statistics when the iostat command is not executing. If the system is configured in the above manner, the first Disk report displays the message Disk History Since Boot Not Available instead of the disk statistics. Subsequent interval reports generated by the iostat command contain disk statistics collected during the report interval. Any tty and CPU statistics after boot are unaffected. If a system management command is used to re-enable disk statistics keeping, the first iostat command report displays activity from the interval starting at the point that disk input/output statistics were enabled.
System Throughput Report
This report is generated if the -s flag is specified. This report provides statistics for the entire system. This report has the following format:
Kbps Indicates the amount of data transferred (read or written) in the entire system in KB per second.
tps Indicates the number of transfers per second issued to the entire system.
Kb_read The total number of KB read from the entire system.
Kb_wrtn The total number of KB written to the entire system.
Adapter Throughput Report
This report is generated if the -a flag is specified. This report provides statistics on an adapter-by-adapter basis.This report has the following format:
Kbps Indicates the amount of data transferred (read or written) in the adapter in KB per second.
tps Indicates the number of transfers per second issued to the adapter.
Kb_read The total number of KB read from the adapter.
Kb_wrtn The total number of KB written to the adapter.
Disk Input/Output History
To improve performance on, the collection of disk input/output statistics has been disabled. To enable the collection of this data, enter:
chdev -l sys0 -a iostat=true
To display the current settings, enter:
lsattr -E -l sys0 -a iostat
If the collection of disk input/output history is disabled, the first disk report of iostat output displays the message Disk History Since Boot Not Available instead of disk statistics. As before, subsequent interval reports generated by the iostat command contain disk statistics collected during the report interval.
Flags
-a Displays the adapter throughput report.
-d The -d flag is exclusive of the -t flag and displays only the disk utilization report.
-s Displays the system throughput report.
-t The -t flag is exclusive of the -d flag and displays only the tty and cpu usage reports.
Notes:
* The -s and -a flags can both be specified to display both the system and adapter throughput reports.
* If the -a flag is specified with the -t flag, the tty and CPU report is displayed, followed by the adapter throughput report. Disk Utilization reports of the disks connected to the adapters, will not be displayed after the Adapter throughput report.
* If the -a flag is specified with the -d flag, tty and CPU report will not be displayed. If Physical Volume parameter is specified, the Disk Utilization Report of the specified Physical volume will be printed under the corresponding adapter to which it belongs.
Examples
1. To display a single history since boot report for all tty, CPU, and Disks, enter:
iostat
2. To display a continuous disk report at two second intervals for the disk with the logical name disk1, enter:
iostat -d disk1 2
3. To display six reports at two second intervals for the disk with the logical name disk1, enter:
iostat disk1 2 6
4. To display six reports at two second intervals for all disks, enter:
iostat -d 2 6
5. To display six reports at two second intervals for three disks named disk1, disk2, disk3, enter:
iostat disk1 disk2 disk3 2 6
6. To print the System throughput report, enter:
iostat -s
7. To print the Adapter throughput report, enter:
iostat -a
8. To print the System and Adapter throughput reports, with only the tty and CPU report (no disk reports), enter:
iostat -sat
9. To print the System and Adapter throughput reports with the Disk Utilization reports of hdisk0 and hdisk7, enter
iostat -sad hdisk0 hdisk7
File
/usr/bin/iostat Contains the iostat command.
Oracle 10 DBA Interview questions and answers
1. Is the following SQL statement syntactically correct? If not, please rewrite it correctly.
SELECT col1 FROM tableA WHERE NOT IN (SELECT col1 FROM tableB);
Ans. SQL is incorrect.
Correct SQL : SELECT col1 FROM tableA WHERE col1 NOT IN (SELECT col1 FROM tableB);
2. What is a more efficient way to write this query, to archive the same set?
Ans: SELECT col1 from tableA minus SELECT col1 from tableB
3.How would you determine that the new query is more efficient than the original query?
Ans: Run explain plan on both query and see the result .
4.How can we find the location of the database trace files from within the data dictionary?
Ans: Generally trace file on the database server machine is located in one of two locations:
1. If you are using a dedicated server connection, the trace file will be generated in the directory specified by
the USER_DUMP_DEST parameter.
2.If you are using a shared server connection, the trace file will be generated in the directory specified by the
BACKGROUND_DUMP_DEST parameter.
you can run sqlplus>SHOW PARAMETER DUMP_DEST
or
select name, value
from v$parameter
where name like '%dump_dest%'
5. What is the correct syntax for a UNIX endless WHILE loop?
while :
do
commands
done
6. Write the SQL statement that will return the name and size of the largest datafile in the database.
SQL> select name,bytes from v$datafile where bytes=(select max(bytes) from v$datafile);
7. What are the proper steps to changing the Oracle database block size?
cold backup all data files and backup controlfile to trace, recreate your database
with the new block size using the backup control file, and restore. It may be easier
with rman. You can not change datbase block size on fly.
8. Using awk, write a script to print the 3rd field of every line.
Ans:
awk '{print }'
awk '{print $3}
awk '{print $3}
9.Under what conditions, is a nested loop better than a merge join?
Ans:
Optimizer uses nested loop when we are joining tables containing small number of rows with an efficient driving
condition.
It is important to have an index on column of inner join table as this table is probed every time for a new value
from outer table.
Optimizer may not use nested loop in case:
1. No of rows of both the table is quite high
2. Inner query always results in same set of records
3. The access path of inner table is independent of data coming from outer table.
merge join is used to join two independent data sources. They perform better than nested loop when the volume of
data is big in tables
but not as good as hash joins in general.
10.Which database views would you use to ascertain the number of commits a user's session has performed?
Joining V$SESSTAT ,V$STATNAME
select * from V$SESSTAT a ,V$STATNAME b where b.CLASS=a.STATISTIC# and b.NAME='user commits' and a.sid=
11.What does the #!bin/ksh at the beginning of a shell script do? Why should it be there?
Ans: On the first line of an interpreter script, the "#!", is the name of a program which should be used to
interpret the contents of the file.
For instance, if the first line contains "#! /bin/ksh", then the contents of the file are executed as a korn shell
script.
12.What command is used to find the status of Oracle 10g Clusterware (CRS) and the various components it manages
(ONS, VIP, listener, instances, etc.)?
Ans:
$ocrcheck
13.Describe a scenario in which a vendor clusterware is required, in addition to the Oracle 10g Clusterware.
If you choose external redundancy for the OCR and voting disk, then to enable redundancy, the disk subsystem must be configurable for RAID mirroring/vendor clusterware. Otherwise, your system may be vulnerable because the OCR and voting disk are single points of failure.
14.How would you find the interconnect IP address from any node within an Oracle 10g RAC configuration?
using oifcfg command.
se the oifcfg -help command to display online help for OIFCFG. The elements of OIFCFG commands, some of which are
optional depending on the command, are:
*nodename—Name of the Oracle Clusterware node as listed in the output from the olsnodes command
*if_name—Name by which the interface is configured in the system
*subnet—Subnet address of the interface
*if_type—Type of interface: public or cluster_interconnect
You can use OIFCFG to list the interface names and the subnets of all of the interfaces available on the local node
by executing the iflist keyword as shown in this example:
oifcfg iflist
hme0 139.185.141.0
qfe0 204.152.65.16
You can also retrieve specific OIFCFG information with a getif command using the following syntax:
oifcfg getif [ [-global | -node nodename] [-if if_name[/subnet]] [-type if_type] ]
To store a new interface use the setif keyword. For example, to store the interface hme0, with the subnet
139.185.141.0, as a global interface (to be used as an interconnect for all of the RAC instances in your cluster),
you would use the command:
oifcfg setif -global hme0/139.185.141.0:cluster_interconnect
For a cluster interconnect that exists between only two nodes, for example rac1 and rac2, you could create the cms0
interface with the following commands, assuming 139.185.142.0 is the subnet addresses for the interconnect on rac1
and rac2 respectively:
oifcfg setif -global cms0/139.185.142.0:cluster_interconnect
Use the OIFCFG delif command to delete the stored configuration for global or node-specific interfaces. A specific
node-specific or global interface can be deleted by supplying the interface name, with an optional subnet, on the
command line. Without the -node or -global options, the delif keyword deletes either the given interface or all of
the global and node-specific interfaces on all of the nodes in the cluster. For example, the following command
deletes the global interface named qfe0 for the subnet 204.152.65.0:
oifcfg delif -global qfe0/204.152.65.0
On the other hand, the next command deletes all of the global interfaces stored with OIFCFG:
oifcfg delif -global
15.What is the Purpose of the voting disk in Oracle 10g Clusterware?
Voting disk record node membership information. Oracle Clusterware uses the voting disk to determine which instances are members of a cluster. The voting disk must reside on a shared disk. For high availability, Oracle recommends that you have a minimum of three voting disks. If you configure a single voting disk, then you should use external mirroring to provide redundancy. You can have up to 32 voting disks in your cluster.
16.What is the purpose of the OCR in Oracle 10g Clusterware?
Ans: Oracle Cluster Registry (OCR) is a component in 10g RAC used to store the cluster configuration information. It is a shared disk component, typically located in a shared raw volume that must be accessible to all nodes in the cluster.
The daemon OCSSd manages the configuration info in OCR and maintains the changes to cluster in the registry.
17. In Oracle Streams archived log downstream capture, which database view can be used to determine which archived
logs are no longer needed by the capture process?
Ans: V$ARCHIVE_DEST_STATUS
SELECT col1 FROM tableA WHERE NOT IN (SELECT col1 FROM tableB);
Ans. SQL is incorrect.
Correct SQL : SELECT col1 FROM tableA WHERE col1 NOT IN (SELECT col1 FROM tableB);
2. What is a more efficient way to write this query, to archive the same set?
Ans: SELECT col1 from tableA minus SELECT col1 from tableB
3.How would you determine that the new query is more efficient than the original query?
Ans: Run explain plan on both query and see the result .
4.How can we find the location of the database trace files from within the data dictionary?
Ans: Generally trace file on the database server machine is located in one of two locations:
1. If you are using a dedicated server connection, the trace file will be generated in the directory specified by
the USER_DUMP_DEST parameter.
2.If you are using a shared server connection, the trace file will be generated in the directory specified by the
BACKGROUND_DUMP_DEST parameter.
you can run sqlplus>SHOW PARAMETER DUMP_DEST
or
select name, value
from v$parameter
where name like '%dump_dest%'
5. What is the correct syntax for a UNIX endless WHILE loop?
while :
do
commands
done
6. Write the SQL statement that will return the name and size of the largest datafile in the database.
SQL> select name,bytes from v$datafile where bytes=(select max(bytes) from v$datafile);
7. What are the proper steps to changing the Oracle database block size?
cold backup all data files and backup controlfile to trace, recreate your database
with the new block size using the backup control file, and restore. It may be easier
with rman. You can not change datbase block size on fly.
8. Using awk, write a script to print the 3rd field of every line.
Ans:
awk
awk '{print $3}
awk '{print $3}
9.Under what conditions, is a nested loop better than a merge join?
Ans:
Optimizer uses nested loop when we are joining tables containing small number of rows with an efficient driving
condition.
It is important to have an index on column of inner join table as this table is probed every time for a new value
from outer table.
Optimizer may not use nested loop in case:
1. No of rows of both the table is quite high
2. Inner query always results in same set of records
3. The access path of inner table is independent of data coming from outer table.
merge join is used to join two independent data sources. They perform better than nested loop when the volume of
data is big in tables
but not as good as hash joins in general.
10.Which database views would you use to ascertain the number of commits a user's session has performed?
Joining V$SESSTAT ,V$STATNAME
select * from V$SESSTAT a ,V$STATNAME b where b.CLASS=a.STATISTIC# and b.NAME='user commits' and a.sid=
11.What does the #!bin/ksh at the beginning of a shell script do? Why should it be there?
Ans: On the first line of an interpreter script, the "#!", is the name of a program which should be used to
interpret the contents of the file.
For instance, if the first line contains "#! /bin/ksh", then the contents of the file are executed as a korn shell
script.
12.What command is used to find the status of Oracle 10g Clusterware (CRS) and the various components it manages
(ONS, VIP, listener, instances, etc.)?
Ans:
$ocrcheck
13.Describe a scenario in which a vendor clusterware is required, in addition to the Oracle 10g Clusterware.
If you choose external redundancy for the OCR and voting disk, then to enable redundancy, the disk subsystem must be configurable for RAID mirroring/vendor clusterware. Otherwise, your system may be vulnerable because the OCR and voting disk are single points of failure.
14.How would you find the interconnect IP address from any node within an Oracle 10g RAC configuration?
using oifcfg command.
se the oifcfg -help command to display online help for OIFCFG. The elements of OIFCFG commands, some of which are
optional depending on the command, are:
*nodename—Name of the Oracle Clusterware node as listed in the output from the olsnodes command
*if_name—Name by which the interface is configured in the system
*subnet—Subnet address of the interface
*if_type—Type of interface: public or cluster_interconnect
You can use OIFCFG to list the interface names and the subnets of all of the interfaces available on the local node
by executing the iflist keyword as shown in this example:
oifcfg iflist
hme0 139.185.141.0
qfe0 204.152.65.16
You can also retrieve specific OIFCFG information with a getif command using the following syntax:
oifcfg getif [ [-global | -node nodename] [-if if_name[/subnet]] [-type if_type] ]
To store a new interface use the setif keyword. For example, to store the interface hme0, with the subnet
139.185.141.0, as a global interface (to be used as an interconnect for all of the RAC instances in your cluster),
you would use the command:
oifcfg setif -global hme0/139.185.141.0:cluster_interconnect
For a cluster interconnect that exists between only two nodes, for example rac1 and rac2, you could create the cms0
interface with the following commands, assuming 139.185.142.0 is the subnet addresses for the interconnect on rac1
and rac2 respectively:
oifcfg setif -global cms0/139.185.142.0:cluster_interconnect
Use the OIFCFG delif command to delete the stored configuration for global or node-specific interfaces. A specific
node-specific or global interface can be deleted by supplying the interface name, with an optional subnet, on the
command line. Without the -node or -global options, the delif keyword deletes either the given interface or all of
the global and node-specific interfaces on all of the nodes in the cluster. For example, the following command
deletes the global interface named qfe0 for the subnet 204.152.65.0:
oifcfg delif -global qfe0/204.152.65.0
On the other hand, the next command deletes all of the global interfaces stored with OIFCFG:
oifcfg delif -global
15.What is the Purpose of the voting disk in Oracle 10g Clusterware?
Voting disk record node membership information. Oracle Clusterware uses the voting disk to determine which instances are members of a cluster. The voting disk must reside on a shared disk. For high availability, Oracle recommends that you have a minimum of three voting disks. If you configure a single voting disk, then you should use external mirroring to provide redundancy. You can have up to 32 voting disks in your cluster.
16.What is the purpose of the OCR in Oracle 10g Clusterware?
Ans: Oracle Cluster Registry (OCR) is a component in 10g RAC used to store the cluster configuration information. It is a shared disk component, typically located in a shared raw volume that must be accessible to all nodes in the cluster.
The daemon OCSSd manages the configuration info in OCR and maintains the changes to cluster in the registry.
17. In Oracle Streams archived log downstream capture, which database view can be used to determine which archived
logs are no longer needed by the capture process?
Ans: V$ARCHIVE_DEST_STATUS
Subscribe to:
Post Comments (Atom)
Blog Archive
-
▼
2008
(277)
-
▼
October
(14)
- Using Function-based Indexes for Performance
- Choosing Composite Indexes
- Choosing Columns and Expressions to Index
- Finding Locks and Lock Holders
- TM enqueue
- HW enqueue
- ST enqueue
- TX enqueue
- sar - Top 20 DBA unix commands
- iostat - Top 20 DBA unix commands
- vmstat - Top 20 DBA Unix commands
- Find how much memory is in your unix machine/server?
- Difference between Session, Connection and Process
- Moving data files
-
▼
October
(14)
Posts
No comments:
Post a Comment