Service Time and Wait Time Totals for Session

Service Time Totals for Session

Wait Time Totals for Session

Tuning Diagnosis for Session

Tuning Advices for Session

File Statistics Part

File Statistics

Unknown Statistics

Performance tips

Compression

iStatsPackAnalyzer Error Codes Manual

Generic User Manual

Introduction

iStatsPackAnalyzer is a web based tool which analyzes STATSPACK files of Oracle; finds bottlenecks and offers information on how to tune them.

STATSPACK Package

STATSPACK package is an Oracle performance diagnosis package. It's one of the best performance diagnosis resources for Oracle tuning. See the following Metaling notes to get STATSPACK reports:

Note:149113.1 Installing and Configuring StatsPack Package
Note:149121.1 Gathering a StatsPack snapshot
Note:149124.1 Creating a StatsPack performance report

User Interface

The user interface of iStatsPackAnalyzer is as below:

iStatsPackAnalyzer user interface consists of the following items:

Instance

If it's selected, instance level analysis is printed.

Session

If it's selected, session level analysis is printed if STATSPACK file includes session level information.

Tuning advice

If it's selected, tuning advice is printed by depending on the Minimum advice limit option.

This is a limit that determines whether tuning advice appears for an event in iStatsPackAnalyzer output. If event time is less than this parameter, tuning advice will not appear. This prevents iStatsPackAnalyzer from calculating tuning advices for low-impact events.

File name

STATSPACK file name to be uploaded. See the Compression of request section to reduce uploading time.

See STATSPACK Package section to see how STATSPACK files are generated.

STATSPACK File Information

STATSPACK file information consists of the following section:

Instance Information
Snapshot Information
SGA Summary Information
Corrupted (Ignored) Lines

Instance Information

This section shows instance information.

Sample

INSTANCE INFORMATION

Instance Number	Instance Name	DB Name	DB ID	Release	Cluster	Host
----------------	------------	------------	-----------	-----------	--------	------------
1	LIFE	LIFE	4122711171	8.1.7.4.0	NO	life
----------------	------------	------------	-----------	-----------	--------	------------

Column information

Name	Definition	Oracle Availability
Instance Number	Instance number.	8.1.7, 9.0.1, 9.2.0
Instance Name	Instance name.	8.1.7, 9.0.1, 9.2.0
DB Name	Database name.	8.1.7, 9.0.1, 9.2.0
DB ID	Database ID.	8.1.7, 9.0.1, 9.2.0
Release	Oracle release.	8.1.7, 9.0.1, 9.2.0
Cluster	OPS/RAC identifier.	8.1.7, 9.0.1, 9.2.0
Host	Host name.	8.1.7, 9.0.1, 9.2.0

Snapshot Information

This section shows snapshot information.

Sample

SNAPSHOT INFORMATION

Snapshot	Snapshot Time	Sessions
-----------------------	-------------------	---------
Begin Snap: 560	07-Mar-03 14:30:03	48
End Snap: 561	07-Mar-03 15:30:02	48
Elapsed:	59.98 (mins)
-----------------------	-------------------	---------

Column information

Name	Definition	Oracle Availability
Snapshot	Begin Snapshod ID, End Snapshot ID and Elapsed lable.	8.1.7, 9.0.1, 9.2.0
Snapshot Time	Begin-end snapshot dates and snapshot interval time.	8.1.7, 9.0.1, 9.2.0
Sessions	Number of sessions at begin-end snapshots.	8.1.7, 9.0.1, 9.2.0

SGA Summary Information

This section shows SGA summary information.

Sample

SGA SUMMARY INFORMATION

SGA Region	Size
-----------------	----------------
Database Buffers	1,556,480,000
Fixed Size	73,888
Redo Buffers	3,162,112
Variable Size	2,241,544,192
-----------------	----------------
total	3,801,260,192

Column information

Name	Definition	Oracle Availability
SGA Region	SGA region name.	8.1.7, 9.0.1, 9.2.0
Size	Size in bytes.	8.1.7, 9.0.1, 9.2.0

Corrupted (Ignored) Lines

This section shows logically corrupted lines in STATSPACK reports. Values of corrupted lines are not included in analysis results. They are ignored.

Sample

CORRUPTED (IGNORED) LINEs

Errror Code	Explanation	Line
-----------	-------------------------------------	--------------------------------------------------------------------
spa-05 [4]	Unparseable number: "###########"	SQL*Net message from client 8,788,151 0 ########### 212 249.7
spa-05 [4]	Unparseable number: "######"	UBT 168 0 ###### 43.3 242 0 0 0.0
spa-05 [4]	Unparseable number: "######"	UBT /system/oradata/UBT/temp.dbf 163 0 ###### 44.6 237 0 0
-----------	-------------------------------------	--------------------------------------------------------------------

Column information

Name	Definition	Oracle Availability
Error code	iStatsPackAnalyzer error code.	8.1.7, 9.0.1, 9.2.0
Explanation	Error code explanation.	8.1.7, 9.0.1, 9.2.0
Line	Corrupted line.	8.1.7, 9.0.1, 9.2.0

Overall Part

Overall part is the time totals for background and also foreground processes. It doesn't mean the sum of background and foreground processes. Because, time spent in foreground processes may include time spent in background processes.

Overall Service Time and Non-Idle Wait Time Totals

This section shows overall CPU and non-idle wait times of foreground and background processes. It consits of two sections:

Chart Section
Table Section

Chart Section

This section shows Overall CPU and non-idle wait time totals in a chart.

Sample

OVERALL SERVICE TIME AND NON-IDLE WAIT TIME TOTALS

Table Section

This section shows Overall CPU and non-idle wait time totals in a table.

Sample

Event	Foreground	Background
--------------	-----------	-----------
Service	13,926.56	N/A
Non-idle wait	9,241.34	642.39
--------------	-----------	-----------
total	23,167.90	642.39+

Column information

Name	Definition	Oracle Availability
Event	Event name.	8.1.7, 9.0.1, 9.2.0
Foreground	Foreground processes time in seconds.	8.1.7, 9.0.1, 9.2.0
Background	Background processes time in seconds.	8.1.7, 9.0.1, 9.2.0

Note that CPU time for background processes, except SNP processes, are not recorded by Oracle.

Foreground Processes Part

Foreground Processes Part shows tuning information for foreground processes. It consists of the following section:

Service Time Totals for Foreground Processes
Non-Idle Wait Time Totals for Foreground Processes
Idle (Ignorable) Wait Time Totals for Foreground Processes
Tuning Diagnosis
Tuning Advices

Service Time Totals for Foreground Processes

This section shows CPU statistics of foreground processes.

Sample

SERVICE TIME TOTALS FOR FOREGROUND PROCESSES

Event	Total	per Call	per Transaction	per Logon	per Second
----------------------------	-----------	---------	----------------	----------	-----------
execute and fetch time cpu	13,376.15	0.00	0.38	85.20	3.72
parse time cpu	550.41	0.00	0.02	3.51	0.15
----------------------------	-----------	---------	----------------	----------	-----------
total	13,926.56	0.00	0.40	88.70	3.87

Column information

Name	Definition	Oracle Availability
Event	Event name.	8.1.7, 9.0.1, 9.2.0
Total	Service time in seconds.	8.1.7, 9.0.1, 9.2.0
per Call	Service time per call.	8.1.7, 9.0.1, 9.2.0
per Transaction	Service time per transaction.	8.1.7, 9.0.1, 9.2.0
per Logon	Service time per logon.	8.1.7, 9.0.1, 9.2.0
per Second	Service time per second.	8.1.7, 9.0.1, 9.2.0

Non-Idle Wait Time Totals for Foreground Processes

This section shows non-idle wait totals for foreground processes.

Sample

NON-IDLE WAIT TIME TOTALS FOR FOREGROUND PROCESSES

Event	Time	Count	Timeout	Time/Count	Timeout/Count
--------------------------------	-----------	-----------	----------	-----------	--------------
db file sequential read	3,637.39	1,303,618	0	0.003	0.00
SQL*Net break/reset to clien	3,237.65	2,590,337	0	0.001	0.00
log file sync	635.97	36,507	22	0.017	0.00
SQL*Net more data to client	513.72	1,750,724	0	0.000	0.00
db file parallel write	299.41	11,775	0	0.025	0.00
db file scattered read	273.40	88,968	0	0.003	0.00
log file parallel write	257.72	35,585	1	0.007	0.00
latch free	231.64	22,464	18,427	0.010	0.01
SQL*Net message to client	58.98	8,788,167	0	0.000	0.00
log file sequential read	44.49	16,020	0	0.003	0.00
control file parallel write	34.71	1,221	0	0.028	0.00
buffer busy waits	4.51	832	0	0.005	0.00
direct path read	4.36	327	0	0.013	0.00
direct path write	2.58	284	0	0.009	0.00
file open	1.59	3,214	0	0.000	0.00
log file switch completion	1.21	17	0	0.071	0.00
refresh controlfile command	0.64	320	0	0.002	0.00
SQL*Net more data from clien	0.63	32	0	0.020	0.00
control file sequential read	0.34	1,995	0	0.000	0.00
LGWR wait for redo copy	0.16	87	1	0.002	0.00
log file single write	0.12	20	0	0.006	0.00
db file parallel read	0.10	4	0	0.025	0.00
file identify	0.02	40	0	0.001	0.00
--------------------------------	-----------	-----------	----------	-----------	--------------
total	9,241.34	14,652,558	18,451	N/A	N/A

Column information

Name	Definition	Oracle Availability
Event	Event name.	8.1.7, 9.0.1, 9.2.0
Time	Wait time in seconds.	8.1.7, 9.0.1, 9.2.0
Count	Number of wait calls.	8.1.7, 9.0.1, 9.2.0
Timeout	Number of wait timeouts.	8.1.7, 9.0.1, 9.2.0
Time/Count	Time per count.	8.1.7, 9.0.1, 9.2.0
Timeout/Count	Timeout per count.	8.1.7, 9.0.1, 9.2.0

Idle (Ignorable) Wait Time Totals for Foreground Processes

This section has the same meaning like in Non-Idle Wait Time Totals for Foreground Processes section except It shows Idle(Ignorable) waits only.

Sample

IDLE (IGNORABLE) WAIT TIME TOTALS FOR FOREGROUND PROCESSES

Event	Time	Count	Timeout	Time/Count	Timeout/Count
--------------------------------	-----------	-----------	----------	-----------	--------------
SQL*Net message from client	32,008.74	10	0	3,200.874	0.00
--------------------------------	-----------	-----------	----------	-----------	--------------
total	32,008.74	10	0	N/A	N/A

Tuning Diagnosis

This section shows tuning diagnosis information to get tuning advice. It is optional. To see this section in analysis report, Tuning advice option should be selected. If there is no foreground process event which has greater than or equal to Minimum advice limit time, this section is not printed even if Tuning advice option is selected.

This section consits of two sections:

Chart section
Table section

Chart section

Just first 7 events which have highest time costs are shown in the chart. Other events are shown as Others.

Sample

TUNING DIAGNOSIS

Table section

This section shows tuning diagnosis table with tuning advice HTML links. Events are ordered by their event times in descending order.

Sample

Event	Time	Definition	Statistic Diagnosis	Built-in Diagnosis
execute and fetch time cpu	13,376.15	Total CPU time for execute and fetch calls.	Logical-IO statistics Logical-IO advices Consistent mode statistics Consistent mode advices Current mode statistics Current mode advices Block cleanout statistics Block cleanout advices Full scan statistics Full scan advices Table ROWID access statistics Table ROWID access advices	Buffer gets SQL table Buffer gets SQL advices
db file sequential read	3,637.39	Total wait time waited for IO completion of: - Single block read - Multiple blocks sort read for pre-Oracle8	Table ROWID access statistics Table ROWID access advices	Tablespace Read-IO table Datafile Read-IO table Read-IO advices Physical reads SQL table Physical reads SQL advices
log file sync	635.97	Total wait time of processes after COMMIT/ROLLBACK. It consists of 3 parts: - Posting LGWR. - Waiting for LGWR to write redolog buffers to online redolog files. - Posting processes by LGWR after redelog buffers are written to redolog file.	redolog statistics log file sync advices	If redo logfiles are on the same physical disks with the datafiles, Check: Tablespace Read-IO table Datafile Read-IO table Read-IO advices Tablespace Write-IO table Datafile Write-IO table Write-IO advices
parse time cpu	550.41	Total CPU time for parse calls.	Parse statistics Parse advices
SQL*Net more data to client	513.72	Total wait time waited for sending data/messages in multiple SQLNet packages from server process to client, because of: - data/messages don't fit in single SQLNet package.	SQLNet statistics SQLNet more data to client advices
db file scattered read	273.40	Total wait time waited for IO completion of: - Full Table Scan(FTS) - Index Fast Full Scan	db file scattered read statistics Full scan advices	Tablespace Read-IO table Datafile Read-IO table Read-IO advices Physical reads SQL table Physical reads SQL advices
latch free	231.64	Total wait time waited for entering critical sections of Oracle code to prevent data structures from concurent modifications.		Willing to wait latch table No-wait(immediate) latch table Latch miss sources table latch free advices

Column information

Name	Definition	Oracle Availability
Event	Event name.	8.1.7, 9.0.1, 9.2.0
Time	Elapsed time of event in seconds.	8.1.7, 9.0.1, 9.2.0
Definition	Definition of event.	8.1.7, 9.0.1, 9.2.0
Statistic Diagnosis	HTML links for statistics and their tuning advices.	8.1.7, 9.0.1, 9.2.0
Built-in Diagnosis	HTML links for built-in statistic tables and their tuning advices.	8.1.7, 9.0.1, 9.2.0

Tuning Advices

After events are ordered by their event times in Tuning Diagnosis table, Statistics Diagnosis and Built-in Diagnosis information of higher impacted events are printed firstly. While printing for an event, Statistics Diagnosis information of an event are printed first from top links to bottom links, then Built-in Diagnosis information of this event is printed in the same order. Then, Statistics Diagnosis and Built-in Diagnosis of other lower impacted events are printed.

Here is a table which explains the print order (TimeA>=TimeB):

Event	Time	Definition	Statistic Diagnosis	Built-in Diagnosis
eventA	TimeA	DefinitionA	A1 A2	A3 A4
EventB	TimeB	DefinitionB	B1 B2	B3 B4

Print order of Statistics Diagnosis information and Built-in Diagnosis information for the sample Tuning Diagnosis table given above is A1,A2,A3,A4,B1,B2,B3,B4.

Tuning advices section consists of the following sections:

Statistic Diagnosis Information
Built-in Diagnosis Information

Statistic Diagnosis Information

Statistic Diagnosis Information consists of the following sections:

Grouped statistics
Tuning advices text

Grouped Statistics

iStatspackAnalyzer provides some pre-defined grouped statistics.

Sample

The following table shows the grouped statistics related to consistent mode operations.

CONSISTENT MODE STATISTICS

Statistic	Total	per Call	per Transaction	per Logon	per Second
----------------------------	-----------	---------	----------------	----------	-----------
consistent gets	52,889,320	5.15	1,502.58	336,874.66	14,696.38
no work - consistent read gets	38,621,133	3.76	1,097.22	245,994.47	10,731.67
consistent changes	102,448	0.01	2.91	652.54	28.47
data blocks consistent reads - undo records applied	102,434	0.01	2.91	652.45	28.46
CR blocks created	28,442	0.00	0.81	181.16	7.90
immediate (CR) block cleanout applications	23,001	0.00	0.65	146.50	6.39
cleanouts and rollbacks - consistent read gets	17,263	0.00	0.49	109.96	4.80
rollbacks only - consistent read gets	11,300	0.00	0.32	71.97	3.14
cleanouts only - consistent read gets	5,738	0.00	0.16	36.55	1.59
current blocks converted for CR	0	0.00	0.00	0.00	0.00
----------------------------	-----------	---------	----------------	----------	-----------

Column information

Name	Definition	Oracle Availability
Statistic	Statistic name.	8.1.7, 9.0.1, 9.2.0
Total	Statistic value.	8.1.7, 9.0.1, 9.2.0
per Call	Statistic value per call.	8.1.7, 9.0.1, 9.2.0
per Transaction	Statistic value per transaction.	8.1.7, 9.0.1, 9.2.0
per Logon	Statistic value per logon.	8.1.7, 9.0.1, 9.2.0
per Second	Statistic value per second.	8.1.7, 9.0.1, 9.2.0

Tuning advices text

iStatspackAnalyzer provides some pre-defined tuning advices.

Sample

The following table shows the tuning advices text related to consistent mode operations.

CONSISTENT MODE ADVICES

Advice
consistent gets = 52,889,320.00 52,889,320.00 logical IO had been done in consistent mode for statements such as SELECT. Blocks are requested in consistent mode in order to be read. If it's unacceptable, do the followings: - Check missing index(es). - If CPU utilization is low in multiple-CPUs environment, use Parallel Query. - If CPU utilization is high, increase CPU power, or add more CPUs if bottleneck is not only in a single process/thread. no work - consistent read gets = 38,621,132.00 38,621,132.00 logical IO had been done in consistent mode that requires neither block cleanouts nor rollbacks. cleanouts only - consistent read gets = 5,738.00 5,738.00 logical IO had been done in consistent mode that requires only block cleanouts, no rollbacks. Normally, this is not tunable. Next coming process always tries to clean out dirtied blocks. To clean out dirtied blocks, do the followings: - Do Full Table Scan(FTS) against dirtied tables. - Export dirtied tables to /dev/null device (faster). Since both options do FTS, dirtied blocks will be cleaned out during FTS. Note that, as of Oracle 7.3, select count() from table_name* doesn't clean out dirtied blocks. rollbacks only - consistent read gets = 11,300.00 11,300.00 logical IO had been done in consistent mode that requires only rollbacks, no block cleanouts. Normally, this is not tunable. But, If it's unacceptable, don't run many small transactions and long-running queries on the same table at the same time. In order to obtain read-consistent image of the blocks, the queries may need to roll back many modifications done by other transactions. cleanouts and rollbacks - consistent read gets = 17,263.00 17,263.00 logical IO had been done in consistent mode that requires both block cleanouts and rollbacks. CR blocks created = 28,442.00 28,442.00 CURRENT mode blocks had been cloned to create CR(consistent read) mode blocks. Since CURRENT mode blocks are in exclusive mode, they can not be used in CR mode. To access a block in CR mode, a new block is cloned from CURRENT mode blocks to CR mode in share mode.

Advice

consistent gets = 52,889,320.00

52,889,320.00 logical IO had been done in consistent mode for statements such as SELECT. Blocks are requested in consistent mode in order to be read. If it's unacceptable, do the followings:

- Check missing index(es).
- If CPU utilization is low in multiple-CPUs environment, use Parallel Query.
- If CPU utilization is high, increase CPU power, or add more CPUs if bottleneck is not only in a single process/thread.

no work - consistent read gets = 38,621,132.00

38,621,132.00 logical IO had been done in consistent mode that requires neither block cleanouts nor rollbacks.

cleanouts only - consistent read gets = 5,738.00

5,738.00 logical IO had been done in consistent mode that requires only block cleanouts, no rollbacks. Normally, this is not tunable. Next coming process always tries to clean out dirtied blocks. To clean out dirtied blocks, do the followings:

- Do Full Table Scan(FTS) against dirtied tables.
- Export dirtied tables to /dev/null device (faster).

Since both options do FTS, dirtied blocks will be cleaned out during FTS. Note that, as of Oracle 7.3, select count(*) from table_name doesn't clean out dirtied blocks.

rollbacks only - consistent read gets = 11,300.00

11,300.00 logical IO had been done in consistent mode that requires only rollbacks, no block cleanouts. Normally, this is not tunable. But, If it's unacceptable, don't run many small transactions and long-running queries on the same table at the same time. In order to obtain read-consistent image of the blocks, the queries may need to roll back many modifications done by other transactions.

cleanouts and rollbacks - consistent read gets = 17,263.00

17,263.00 logical IO had been done in consistent mode that requires both block cleanouts and rollbacks.

CR blocks created = 28,442.00

28,442.00 CURRENT mode blocks had been cloned to create CR(consistent read) mode blocks. Since CURRENT mode blocks are in exclusive mode, they can not be used in CR mode. To access a block in CR mode, a new block is cloned from CURRENT mode blocks to CR mode in share mode.

Column information

Name	Definition	Oracle Availability
Advice	Tuning advice text.	8.1.7, 9.0.1, 9.2.0

Built-in Diagnosis Information

Built-in Diagnosis Information consists of the specific statistics and their tuning advices. They have different formats and can not be grouped together. For example, the format of enqueue portion in STATSPACK file is different than the format of tablespace portion.

Built-in diagnosis information consists of the following items:

tablespace read-io table
tablespace write-io table
datafile read-io table
datafile write-io table
buffer pool table
buffer wait table
rollback segment table
rollback segment storage table
willing to wait latch table
no-wait(immediate) latch table
latch miss sources table
dictionary cache table
library cache table
tablespace buffer wait table
datafile buffer wait table
buffer gets sql table
physical reads sql table
enqueue table

Tablespace Read-IO Table

This section shows read-IO statistics of tablespaces.

Sample

TABLESPACE READ-IO TABLE

Tablespace	Time	Block	Call	Time/Call (ms)	Block/Call	Time (%)
---------------	-----------	------------	-----------	---------------	-----------	---------
WBDATA	261.01	11,600	9,667	27.00	1.20	54%
WBINDX	120.14	12,321	10,268	11.70	1.20	25%
RBS	23.33	92	92	253.60	1.00	5%
SMINDX	15.48	516	516	30.00	1.00	3%
SMDATA	13.75	922	401	34.30	2.30	3%
SYSTEM	13.65	362	362	37.70	1.00	3%
TEMP	12.47	4,250	154	81.00	27.60	3%
TOOLS	10.51	22	22	477.70	1.00	2%
ARCDATA	4.73	22	22	215.00	1.00	1%
INTRFC	4.58	22	22	208.20	1.00	1%
PSTATS	4.07	163	163	25.00	1.00	1%
---------------	-----------	------------	-----------	---------------	-----------	---------
total	483.73	30,292	21,689	N/A	N/A	100%

Column information

Name	Definition	Oracle Availability
Tablespace	Tablespace name.	8.1.7, 9.0.1, 9.2.0
Time	Physical read time in seconds.	8.1.7, 9.0.1, 9.2.0
Block	Number of physical blocks.	8.1.7, 9.0.1, 9.2.0
Call	Number of physical reads.	8.1.7, 9.0.1, 9.2.0
Time/Call (ms)	Phsysical read time per call in milliseconds.	8.1.7, 9.0.1, 9.2.0
Block/Call	Physical blocks per call.	8.1.7, 9.0.1, 9.2.0
Time (%)	Percentage of time.	8.1.7, 9.0.1, 9.2.0

Tablespace Write-IO Table

This section shows write-IO statistics of tablespaces.

Sample

TABLESPACE WRITE-IO TABLE

Tablespace	Call
---------------	-----------
RBS	250,512
WBINDX	131,097
WBDATA	66,938
SMINDX	2,804
TEMP	552
PSTATS	379
SYSTEM	283
SMDATA	173
ARCDATA	22
INTRFC	22
TOOLS	22
---------------	-----------
total	452,804

Column information

Name	Definition	Oracle Availability
Tablespace	Tablespace name.	8.1.7, 9.0.1, 9.2.0
Call	Number of physical writes.	8.1.7, 9.0.1, 9.2.0

Datafile Read-IO Table

This section shows read-IO statistics of datafiles.

Sample

DATAFILE READ-IO TABLE

Datafile	Tablespace	Time	Block	Call	Time/Call (ms)	Block/Call	Time (%)
------------------------------------------------	---------------	-----------	------------	-----------	---------------	-----------	---------
D:\ORACLE\ORADATA\CSDEVUT2\WBDATA02.DBF	WBDATA	200.63	9,441	7,868	25.50	1.20	41%
D:\ORACLE\ORADATA\CSDEVUT2\WBINDX02.DBF	WBINDX	83.97	9,688	8,074	10.40	1.20	17%
D:\ORACLE\ORADATA\CSDEVUT2\WBDATA01.DBF	WBDATA	60.45	1,978	1,799	33.60	1.10	12%
D:\ORACLE\ORADATA\CSDEVUT2\WBINDX01.DBF	WBINDX	35.98	2,632	2,194	16.40	1.20	7%
D:\ORACLE\ORADATA\CSDEVUT2\SYSTEM01.DBF	SYSTEM	13.65	362	362	37.70	1.00	3%
D:\ORACLE\ORADATA\CSDEVUT2\TEMP01.DBF	TEMP	12.47	4,250	154	81.00	27.60	3%
D:\ORACLE\ORADATA\CSDEVUT2\RBS01.DBF	RBS	11.79	45	45	262.00	1.00	2%
D:\ORACLE\ORADATA\CSDEVUT2\RBS02.DBF	RBS	11.54	47	47	245.50	1.00	2%
D:\ORACLE\ORADATA\CSDEVUT2\TOOLS01.DBF	TOOLS	10.51	22	22	477.70	1.00	2%
D:\ORACLE\ORADATA\CSDEVUT2\SMINDX02.DBF	SMINDX	9.98	458	458	21.80	1.00	2%
D:\ORACLE\ORADATA\CSDEVUT2\SMDATA01.DBF	SMDATA	8.17	708	373	21.90	1.90	2%
D:\ORACLE\ORADATA\CSDEVUT2\SMDATA02.DBF	SMDATA	5.58	207	28	199.30	7.39	1%
D:\ORACLE\ORADATA\CSDEVUT2\SMINDX01.DBF	SMINDX	5.47	58	58	94.30	1.00	1%
D:\ORACLE\ORADATA\CSDEVUT2\ARCDATA01.DBF	ARCDATA	4.73	22	22	215.00	1.00	1%
D:\ORACLE\ORADATA\CSDEVUT2\INTRFC01.DBF	INTRFC	4.58	22	22	208.20	1.00	1%
D:\ORACLE\ORADATA\CSDEVUT2\PSTATS01.DBF	PSTATS	4.07	163	163	25.00	1.00	1%
------------------------------------------------	---------------	-----------	------------	-----------	---------------	-----------	---------
total		483.58	30,103	21,689	N/A	N/A	100%

Column information

Name	Definition	Oracle Availability
Datafile	Datafile name.	8.1.7, 9.0.1, 9.2.0
Tablespace	Tablespace name.	8.1.7, 9.0.1, 9.2.0
Time	Physical read time in seconds.	8.1.7, 9.0.1, 9.2.0
Block	Number of physical blocks.	8.1.7, 9.0.1, 9.2.0
Call	Number of physical reads.	8.1.7, 9.0.1, 9.2.0
Time/Call (ms)	Phsysical read time per call.	8.1.7, 9.0.1, 9.2.0
Block/Call	Physical blocks per call.	8.1.7, 9.0.1, 9.2.0
Time (%)	Percentage of time.	8.1.7, 9.0.1, 9.2.0

Datafile Write-IO Table

This section shows write-IO statistics of datafiles.

Sample

DATAFILE WRITE-IO TABLE

Datafile	Tablespace	Call
------------------------------------------------	---------------	-----------
D:\ORACLE\ORADATA\CSDEVUT2\RBS01.DBF	RBS	150,189
D:\ORACLE\ORADATA\CSDEVUT2\WBINDX02.DBF	WBINDX	110,416
D:\ORACLE\ORADATA\CSDEVUT2\RBS02.DBF	RBS	100,323
D:\ORACLE\ORADATA\CSDEVUT2\WBDATA02.DBF	WBDATA	39,157
D:\ORACLE\ORADATA\CSDEVUT2\WBDATA01.DBF	WBDATA	27,781
D:\ORACLE\ORADATA\CSDEVUT2\WBINDX01.DBF	WBINDX	20,681
D:\ORACLE\ORADATA\CSDEVUT2\SMINDX02.DBF	SMINDX	2,782
D:\ORACLE\ORADATA\CSDEVUT2\TEMP01.DBF	TEMP	552
D:\ORACLE\ORADATA\CSDEVUT2\PSTATS01.DBF	PSTATS	379
D:\ORACLE\ORADATA\CSDEVUT2\SYSTEM01.DBF	SYSTEM	283
D:\ORACLE\ORADATA\CSDEVUT2\SMDATA01.DBF	SMDATA	151
D:\ORACLE\ORADATA\CSDEVUT2\ARCDATA01.DBF	ARCDATA	22
D:\ORACLE\ORADATA\CSDEVUT2\INTRFC01.DBF	INTRFC	22
D:\ORACLE\ORADATA\CSDEVUT2\SMDATA02.DBF	SMDATA	22
D:\ORACLE\ORADATA\CSDEVUT2\SMINDX01.DBF	SMINDX	22
D:\ORACLE\ORADATA\CSDEVUT2\TOOLS01.DBF	TOOLS	22
------------------------------------------------	---------------	-----------
total		452,804

Column information

Name	Definition	Oracle Availability
Datafile	Datafile name.	8.1.7, 9.0.1, 9.2.0
Tablespace	Tablespace name.	8.1.7, 9.0.1, 9.2.0
Call	Number of physical writes.	8.1.7, 9.0.1, 9.2.0

Buffer Pool Table

This section shows buffer pool statistics.

Sample

BUFFER POOL TABLE

Name	Buffer Gets	Consistent Gets	Physical Reads	Physical Writes	Free Buffer Waits	Write Complete Waits	Buffer Busy Waits
-------------	-----------------	----------------	---------------	----------------	------------------	---------------------	------------------
Default	202,798	38,491,332	22,146	378,019	0	0	35,920
Keep	556	45,126,064	509	142	0	0	219
Recycle	1,041,969	52,938,472	2,555	73,794	0	0	3,615
-------------	-----------------	----------------	---------------	----------------	------------------	---------------------	------------------
total	1,245,323	136,555,868	25,210	451,955	0	0	39,754

Column information

Name	Definition	Oracle Availability
Name	Buffer pool name.	8.1.7, 9.0.1, 9.2.0
Buffers	Number of buffers.	9.2.0
Cache Hit Ratio (%)	Cache hit ratio.	9.2.0
Buffer Gets	Number of buffer gets.	8.1.7, 9.0.1, 9.2.0
Consistent Gets	Number of consistent gets.	8.1.7
Physical Reads	Number of physical reads.	8.1.7, 9.0.1, 9.2.0
Physical Writes	Number of physical writes.	8.1.7, 9.0.1, 9.2.0
Free Buffer Waits	Number of free buffer waits.	8.1.7, 9.0.1, 9.2.0
Write Complete Waits	Number of write complete waits.	8.1.7, 9.0.1, 9.2.0
Buffer Busy Waits	Number of buffer busy waits.	8.1.7, 9.0.1, 9.2.0

Buffer Wait Table

This section shows buffer wait statistics.

Sample

BUFFER WAIT TABLE

Name	Time	Count	Time/Count	Time (%)
-------------	--------	--------	-----------	---------
data block	105.38	19,676	0.005	94%
undo block	6.15	19,434	0.000	5%
undo header	0.50	611	0.001	0%
segment header	0.15	51	0.003	0%
-------------	--------	--------	-----------	---------
total	112.18	39,772	N/A	100%

Column information

Name	Definition	Oracle Availability
Name	Name of buffer class.	8.1.7, 9.0.1, 9.2.0
Time	Wait time in seconds.	8.1.7, 9.0.1, 9.2.0
Count	Number of waits.	8.1.7, 9.0.1, 9.2.0
Time/Count	Wait time per count.	8.1.7, 9.0.1, 9.2.0
Time (%)	Percentage of time.	8.1.7, 9.0.1, 9.2.0

Rollback Segment Table

This section shows rollback segment statistics.

Sample

ROLLBACK SEGMENT TABLE

RBS No	Waits	Gets	Waits/Gets	Undo Bytes Written	Wraps	Shrinks	Extends
-------	------------	------------	-----------	-------------------	--------	--------	--------
31	7	79,297	0.00	453,394,996	0	0	0
16	3	12,594	0.00	30,401,326	65	0	0
11	2	11,876	0.00	26,980,624	58	0	0
43	2	11,700	0.00	26,146,140	57	0	0
52	1	16,361	0.00	38,334,434	0	0	0
2	1	13,513	0.00	30,517,614	66	0	0
50	1	12,557	0.00	29,993,880	65	0	0
19	1	12,169	0.00	28,412,860	61	0	0
35	1	12,091	0.00	27,839,604	60	0	0
29	1	11,683	0.00	26,462,412	57	0	0
28	1	11,529	0.00	26,163,556	56	0	0
30	1	11,355	0.00	24,846,344	53	0	0
1	0	14,259	0.00	32,284,814	69	0	0
3	0	13,183	0.00	29,457,518	64	0	0
7	0	12,674	0.00	29,421,182	64	0	0
17	0	12,529	0.00	29,976,130	64	0	0
4	0	12,484	0.00	28,151,458	60	0	0
5	0	12,460	0.00	28,723,534	62	0	0
45	0	12,390	0.00	28,912,162	63	0	0
8	0	12,347	0.00	29,539,560	63	0	0
6	0	12,319	0.00	28,800,250	62	0	0
39	0	12,275	0.00	28,620,346	61	0	0
22	0	12,255	0.00	29,128,872	62	0	0
21	0	12,229	0.00	28,343,612	62	0	0
38	0	12,220	0.00	28,412,606	62	0	0
23	0	12,189	0.00	28,736,864	61	0	0
40	0	12,188	0.00	29,742,112	63	0	0
48	0	12,171	0.00	27,728,024	60	0	0
37	0	12,138	0.00	28,778,764	62	0	0
41	0	12,135	0.00	28,849,960	61	0	0
33	0	12,126	0.00	27,946,414	60	0	0
47	0	12,107	0.00	28,226,102	60	0	0
18	0	12,074	0.00	27,781,542	59	0	0
26	0	12,006	0.00	28,361,842	61	0	0
44	0	11,995	0.00	27,680,296	59	0	0
15	0	11,956	0.00	27,927,454	59	0	0
46	0	11,901	0.00	27,724,684	59	0	0
25	0	11,867	0.00	27,445,942	58	0	0
27	0	11,829	0.00	27,098,268	58	0	0
12	0	11,823	0.00	26,705,796	57	0	0
10	0	11,803	0.00	27,026,176	58	0	0
20	0	11,765	0.00	26,267,922	57	0	0
49	0	11,715	0.00	26,616,374	57	0	0
9	0	11,698	0.00	25,633,088	55	0	0
34	0	11,698	0.00	26,918,856	58	0	0
13	0	11,685	0.00	27,051,290	58	0	0
14	0	11,660	0.00	26,360,998	57	0	0
36	0	11,607	0.00	25,973,108	55	0	0
24	0	11,430	0.00	25,497,370	54	0	0
32	0	11,417	0.00	25,569,272	55	0	0
42	0	11,243	0.00	25,223,424	54	0	0
51	0	11,227	0.00	24,158,282	52	0	0
0	0	63	0.00	0	0	0	0
-------	------------	------------	-----------	-------------------	--------	--------	--------
total	22	699,865	N/A	1,882,296,058	2,983	0	0

Column information

Name	Definition	Oracle Availability
RBS No	Rollback segment number.	8.1.7, 9.0.1, 9.2.0
Waits	Number of waits.	8.1.7, 9.0.1, 9.2.0
Gets	Number of gets.	8.1.7, 9.0.1, 9.2.0
Waits/Gets	Waits per get.	8.1.7, 9.0.1, 9.2.0
Undo Bytes Written	Undo Bytes Written.	8.1.7, 9.0.1, 9.2.0
Wraps	Number of wraps.	8.1.7, 9.0.1, 9.2.0
Shrinks	Number of shrinks.	8.1.7, 9.0.1, 9.2.0
Extends	Number of extends.	8.1.7, 9.0.1, 9.2.0

Rollback Segment Storge Table

This section shows rollback segment storage statistics.

Sample

ROLLBACK SEGMENT STORAGE TABLE

RBS No	Segment Size	Avg Active	Optimal Size	Maximum Size
-------	---------------	---------------	---------------	---------------
0	401,408	8,192	0	401,408
1	8,380,416	523,438	0	8,380,416
2	8,380,416	523,190	0	8,380,416
3	8,380,416	523,007	0	8,380,416
4	8,380,416	523,113	0	8,380,416
5	8,380,416	523,040	0	8,380,416
6	8,380,416	523,131	0	8,380,416
7	8,380,416	522,934	0	8,380,416
8	8,380,416	523,368	0	8,380,416
9	8,380,416	522,459	0	8,380,416
10	8,380,416	522,307	0	8,380,416
11	8,380,416	522,732	0	8,380,416
12	8,380,416	522,178	0	8,380,416
13	8,380,416	522,892	0	8,380,416
14	8,380,416	522,764	0	8,380,416
15	8,380,416	522,703	0	8,380,416
16	8,380,416	523,295	0	8,380,416
17	8,380,416	523,350	0	8,380,416
18	8,380,416	522,955	0	8,380,416
19	8,380,416	522,532	0	8,380,416
20	8,380,416	522,259	0	8,380,416
21	8,380,416	522,987	0	8,380,416
22	8,380,416	522,789	0	8,380,416
23	8,380,416	523,003	0	8,380,416
24	8,380,416	521,606	0	8,380,416
25	8,380,416	522,688	0	8,380,416
26	8,380,416	522,623	0	8,380,416
27	8,380,416	522,587	0	8,380,416
28	8,380,416	522,416	0	8,380,416
29	8,380,416	522,709	0	8,380,416
30	8,380,416	521,966	0	8,380,416
31	1,048,567,808	0	0	1,048,567,808
32	8,380,416	522,258	0	8,380,416
33	8,380,416	522,528	0	8,380,416
34	8,380,416	522,461	0	8,380,416
35	8,380,416	522,609	0	8,380,416
36	8,380,416	522,301	0	8,380,416
37	8,380,416	522,862	0	8,380,416
38	8,380,416	522,862	0	8,380,416
39	8,380,416	523,121	0	8,380,416
40	8,380,416	548,444	0	8,380,416
41	8,380,416	523,208	0	8,380,416
42	8,380,416	522,162	0	8,380,416
43	8,380,416	522,603	0	8,380,416
44	8,380,416	522,578	0	8,380,416
45	8,380,416	523,246	0	8,380,416
46	8,380,416	522,848	0	8,380,416
47	8,380,416	522,807	0	8,380,416
48	8,380,416	522,992	0	8,380,416
49	8,380,416	522,511	0	8,380,416
50	8,380,416	523,497	0	8,380,416
51	8,380,416	521,191	0	8,380,416
52	524,279,808	0	0	524,279,808
-------	---------------	---------------	---------------	---------------

Column information

Name	Definition	Oracle Availability
RBS No	Rollback segment number.	8.1.7, 9.0.1, 9.2.0
Segment Size	Rollback segment size.	8.1.7, 9.0.1, 9.2.0
Avg Active	Average active size.	8.1.7, 9.0.1, 9.2.0
Optimal Size	Optimal size.	8.1.7, 9.0.1, 9.2.0
Maximum Size	Maximum size.	8.1.7, 9.0.1, 9.2.0

Willing to Wait Latch Table

This section shows willing to wait latch statistics.

Sample

WILLING TO WAIT LATCH TABLE

Name	Sleeps	Misses	Gets	Sleeps/Miss (%)	Misses/Get (%)	Spin	Sleep1	Sleep2	Sleep3	Sleep4
--------------------------	-----------	-----------	--------------	----------------	---------------	-----	-------	-------	-------	-------
library cache	199,450	253,701	43,702,832	78.62	0.58	139,118	37,669	69,170	7,744	0
cache buffers chains	14,540	281,600	438,332,606	5.16	0.06	269,949	8,787	2,839	25	0
redo allocation	438	18,353	16,171,919	2.39	0.11	18,014	241	97	1	0
checkpoint queue latch	219	236	3,017,636	92.80	0.01	107	39	90	0	0
cache buffers lru chain	82	1,556	20,537,246	5.27	0.01	1,488	54	14	0	0
row cache objects	59	6,479	7,804,627	0.91	0.08	6,430	39	10	0	0
redo writing	37	382	700,847	9.69	0.05	354	22	4	2	0
session allocation	21	211	1,231,533	9.95	0.02	194	13	4	0	0
session idle bit	16	867	8,150,808	1.85	0.01	854	10	3	0	0
latch wait list	16	45	115,415	35.56	0.04	34	6	5	0	0
undo global data	15	382	1,156,659	3.93	0.03	370	9	3	0	0
messages	11	128	1,145,119	8.59	0.01	118	9	1	0	0
transaction allocation	8	48	719,095	16.67	0.01	41	6	1	0	0
enqueue hash chains	6	134	1,424,675	4.48	0.01	128	6	0	0	0
enqueues	4	220	2,053,293	1.82	0.01	216	4	0	0	0
shared pool	4	127	1,014,373	3.15	0.01	125	1	0	1	0
list of block allocation	2	30	599,075	6.67	0.01	28	2	0	0	0
dml lock allocation	1	36	944,039	2.78	0.00	35	1	0	0	0
cache buffer handles	0	0	2,265,156	0.00	0.00	0	0	0	0	0