Report for oradb

[ TableStats ][ Datafiles ][ DBWR ][ LGWR ] [ Pool Sizes ] [ Shared Pool ] [ Buffer Pool ] [ Advices ] [ SysStat ] [ Events ] [ Buffer Waits ] [ FreeList Contention ] [ Wait Objects ] [ Enqueue Waits ] [ Invalid Objects ]

Common Instance Information
Hostname:	buzz
Instance:	oradb
Version:	9.2.0.5.0
Archiver:	STOPPED
ArchivedLogSize:	MB
FileSize (Data+Log)	2,270.00 MB
Startup / Uptime	18.10.2005 13:58 / 2.93 d
Report generated:	21.10.2005 12:20

Table Statistics
Statistic	Value	Comment
table scan blocks gotten / table scan rows gotten	0.03	This ratio (blocks scanned per scanned row) should get close to 0 with acceptable table scan blocks gotten if you are not using LONG objects frequently. If it is high, you probably should: Check missing index(es) on statements doing Full Table Scan(FTS). If Statements have to do FTS, reorganize tables used in FTS to reset High Water Mark(HWM). Because, in FTS, Oracle scans table from bottom of table to top of table(HWM) even if there are just a few rows in table. This makes problems on frequently inserted/deleted tables since `INSERT` increases HWM, but `DELETE` does not decrease HWM. After reorganization, the number of scanned blocks will be reduced.
table fetch continued row / table fetch by rowid	0.00	The chained-fetch-ratio indicates the average chained/migrated rows in multiple blocks, which are accessed by a single ROWID. This ratio should be as low as possible to access a row in a single block. If it is high, find the chained/migrated rows increase `PCTFREE`, decrease `PCTUSED` storage parameters of tables which have many chained/migrated rows frequently used reorganize these tables (e.g. `ALTER TABLE..MOVE`, or `EXP` / `IMP`)
If we have many full table scans, we may have to optimize `DB_FILE_MULTI_BLOCK_READ_COUNT`. Beneath the statistic below, we need the block count of the largest table to find the best value. A common recommendation is to set `DB_FILE_MULTI_BLOCK_READ_COUNT` to the highest possible value for maximum performance, which is 32 (256k) in most environments. The absolute maximum of 128 (1M) is mostly only available on raw devices.
table scans (short tables)	1,176,572
table scans (long tables)	486
table scans (rowid ranges)	0
table scans (cache partitions)	0
table scans (direct read)	0

Data Files
Alerts and warnings (highlighted table cells) indicate a write enabled non-autoextensible data file filled > 85% / 95%
Tablespace	Datafile	Status	Enabled	Size	Free	Used	Phy.Reads	Phy.Writes	Avg.I/O Time
`DATA`	/database/oradb/data/data_1.dbf	ONLINE	READ WRITE	500.0 M	143.3 M	71.35 %	127,357	736	0.000
`INDEX1`	/database/oradb/index/index_1.dbf	ONLINE	READ WRITE	300.0 M	255.0 M	15.00 %	5,175	835	0.000
`MONITORING`	/database/oradb/data/monitoring_1.dbf	ONLINE	READ WRITE	10.0 M	6.4 M	36.17 %	1,167	867	0.000
`SYSTEM`	/database/oradb/data/system_1.dbf	SYSTEM	READ WRITE	250.0 M	76.3 M	69.50 %	2,305	1,135	0.000
`TEMP`	/database/oradb/temp/temp_1.dbf	ONLINE	READ WRITE	300.0 M	276.6 M	7.79 %	3,111	2,528	0.000
`TOOLS`	/database/oradb/data/tools_1.dbf	ONLINE	READ WRITE	500.0 M	201.2 M	59.76 %	6,959	17	0.000
`UNDO_TS`	/database/oradb/rbs/undo_ts01.dbf	ONLINE	READ WRITE	350.0 M	341.5 M	2.43 %	41	4,954	0.000

Database Writer Statistics
Statistic	Value	Comment
DBWR checkpoint buffers written / hour	1.46	Number of buffers written per hour for checkpoints
DBWR transaction table writes / hour	0.03	Number of rollback segment headers written per hour by DBWR. This statistic indicates how many "hot" buffers were written, causing a user process to wait while the write completed.
DBWR undo block writes / hour	1.13	Number of rollback segment blocks written per hour by DBWR
DBWR revisited being-written buffer / hour	0.00	Number of times per hour that DBWR tried to save a buffer for writing and found that it was already in the write batch. This statistic measures the amount of "useless" work that DBWR had to do in trying to fill the batch. (If the same buffer from different sources is considered for adding to the write batch, then all but the first attempt will be "useless" because the buffer is already marked as being written.)
DBWR buffers scanned / DBWR lru scans	0.00	Average number of buffers scanned per scan
DBWR summed scan depth / DBWR lru scans	0.00	Average scan depth
free buffer inspected / free buffer requested	0.00	If this value is to high, you may need to increase your buffer cache.
dirty buffers inspected / DBWR buffers scanned	0.00	This ratio should be as low as possible. If this value is high, it indicates DBWR is not writing dirty buffers efficiently. High ratio may cause write complete wait wait event.
pinned buffers inspected / DBWR buffers scanned	0.00	This ratio should be as low as possible. If this value is high, it indicates high amount of pinned(busy) buffers encountered during free buffer search. This may cause free buffer waits wait event by reducing the possibility of finding free buffer in the LRU list, and then may cause DBWR to perform large batch write to make clean buffers available at the tail of LRU list. This may also increase physical IO if aged out blocks are needed to be re-accessed. A possible solution is to increase the `DB_BLOCK_BUFFERS` (Oracle 8) / `DB_CACHE_SIZE` (Oracle 9+).
DBWR free buffers found / DBWR make free requests	0.00	This ratio shows the average reusable buffers, and should be as high as possible. If it is low, it indicates lack of free space to use. In this case, check the followings: If dirty buffers inspected is high, it indicates DBWR is not writing dirty buffers efficiently. If pinned buffers inspected is high, increase `DB_BLOCK_BUFFERS` / `DB_CACHE_SIZE`.

Log Writer Statistics

Statistic Value Comment

redo log space requests 31 The active logfile had been full, and Oracle had waited for disk space to be allocated for the redolog entries by performing log switches. High values indicate high amount of log switches.

redo log space requests / h

0.44

This number should be as low as possible. When encountering high values here, the reason could be e.g. one of these:

too small log files
not enough redo log groups
too many checkpoints / log file switches (also indicated by high redo wastage, see below)

redo log space wait time 98 Total wait time waiting for completion of redo log space requests in 1/10 ms. High values cause log file switch... related wait events. If there are many processes waiting for log switch completion, it is possible to see log buffer space wait event after log switch is completed. Since redo generation is disabled during log switch, there can be high volume of redo generation after log switch. This may cause log buffer space wait event.

redo log space wait time / redo log space requests

0.32

ms/request. If this ratio is high, check the followings:

Increase the size of redolog files and/or add new redolog groups
Ensure that log switches occurring not more frequent than around all 20-30 minutes

redo wastage percentage 27.57% Percentage of redo bytes written "unnecessarily" (redo wastage describes the log buffer blocks had been needed to be flushed out to disk before they were completely full, which does not mean a problem: high values just indicate high LGWR activity). Naturally, this should be very low; if it exceeds 20..30% plus you have many log writer wait events, you should check for unnecessary checkpoints/log switches.

redo synch writes 108,794 Changes to redolog buffer had been flushed out to disk immediately such as COMMIT / ROLLBACK.

redo synch time 1,001,282 Elapsed time of all redo synch writes in 1/10 ms. High values cause log file sync wait event.

redo synch time / redo synch writes

920.35

Milliseconds per write. If this ratio is high, check the followings:

Do not set LOG_BUFFER to value higher than 1Mb. High LOG_BUFFER parameter may cause log file sync wait event. This impacts COMMIT / ROLLBACK response time, and possibly DBWR performance.
Reduce COMMIT / ROLLBACK frequency.
If there are other redolog related wait events, check them. They may indirectly cause log file sync wait event.

redo buffer allocation retries / h 0.53 Number of retries per hour necessary to allocate space in the redo buffer. Retries are needed either because the redo writer has fallen behind or because an event such as a log switch is occurring.

redo buffer allocation retries / redo blocks written 0.00 should be less than 0.01 - larger values indicate that the LGWR is not keeping up. If this happens, tuning the values for LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT (or, with Oracle 9i, FAST_START_MTTR_TARGET) can help to improve the situation.

redo blocks written / redo writes 3.07 Number of blocks per write

redo emergence 29.1 K/min How much redo information is written on average during the snapshot interval given.

redo time used 0.03% Rate of time spent for writing redo information during the snapshot interval given. This value should be close to 0%.

redo size 120.1 M Total amount of redo generated

Related Information

Parameter	Value
log_checkpoint_interval	0
log_checkpoint_timeout	0
log_checkpoint_mttr_target
log_buffer	512.0 K

LogGroup	Members	Size
1	2	10.0 M
2	2	10.0 M
3	2	10.0 M

WaitEvent	Waits	WaitTime	AvgWaitTime	Timeouts
LGWR wait for redo copy	234	1	0.004	0
log file switch (checkpoint incomplete)	0	0	0.000	0
log file switch (archiving needed)	0	0	0.000	0
log file switch completion	31	2:37	3.129	0
log file parallel write	114,047	3:43:24	0.086	113,984
log file single write	76	12	0.158	0
log buffer wait	0	0	0.000	0
log buffer space	9	4:01	27.778	1

Pool Sizes
Pool	Space
Shared_Pool_Size	48.00 M
Shared_Pool_Reserved_Size	2.38 M
Large_Pool_Size	0.00 K
Java_Pool_Size	32.00 M
Sort_Area_Size	0.06 M
Sort_Area_Retained_Size	0.00 K
Pool	Free Space
shared pool	5.7 M
java pool	32.0 M

Shared Pool Information
Library Cache
The following cases are indicators that `SHARED_POOL_SIZE` may have to be increased: RPP (100*reloads/pins) > 1 gethitratio < 90%
NameSpace	Gets	Pins	Reloads	RPP	GetHitRatio
SQL AREA	146,524	2,546,594	3,016	0.12	98.17%
TABLE/PROCEDURE	77,420	1,216,667	311	0.03	98.21%
BODY	5,072	5,082	8	0.16	98.88%
TRIGGER	203	235	16	6.81	89.66%
INDEX	886	953	0	0.00	84.09%
CLUSTER	2,776	1,500	0	0.00	99.60%
OBJECT	0	0	0	0.00	100.00%
PIPE	0	0	0	0.00	100.00%
JAVA SOURCE	0	0	0	0.00	100.00%
JAVA RESOURCE	0	0	0	0.00	100.00%
JAVA DATA	0	0	0	0.00	100.00%

Row Cache
If Ratio = (getmisses/gets)*100 > 15, `SHARED_POOL_SIZE` may have to be increased:
Parameter			Gets	GetMisses	Ratio
dc_free_extents			1,835	182	9.92%
dc_used_extents			180	180	100.00%
dc_segments			23,857	653	2.74%
dc_tablespaces			691,921	8	0.00%
dc_tablespace_quotas			124	1	0.81%
dc_files			5,480	3	0.05%
dc_users			741,970	34	0.00%
dc_rollback_segments			24,673	19	0.08%
dc_objects			25,592	1,217	4.76%
dc_global_oids			35	10	28.57%
dc_object_ids			63,597	1,064	1.67%
dc_sequences			185	24	12.97%
dc_usernames			15,225	19	0.12%
dc_database_links			212	2	0.94%
dc_histogram_defs			32,594	2,620	8.04%
dc_profiles			2,630	2	0.08%
dc_user_grants			18,238	32	0.18%

Buffer Pool Statistics
Ratio = physical_reads/(consistent_gets+db_block_gets) should be < 0.9:
Pool	physical_reads	consistent_gets	db_block_gets	Ratio
DEFAULT	628,008	10,648,657	663,299	0.06

DB Cache Advice
The following values are an estimation how changing the size of a given buffer would affect the amount of physical reads.
Pool	Size	Buffers	Estd.PhyRd Factor	Estd.PhyRds
DEFAULT	16 M	1,985	638.6%	4,638,174
DEFAULT	32 M	3,970	420.9%	3,271,005
DEFAULT	48 M	5,955	136.6%	1,485,823
DEFAULT	64 M	7,940	63.9%	1,029,463
DEFAULT	80 M	9,925	17.8%	739,869
DEFAULT	96 M	11,910	12.3%	705,342
DEFAULT	112 M	13,895	6.0%	665,366
DEFAULT	128 M	15,880	1.7%	638,550
DEFAULT	144 M	17,865	0.7%	632,678
DEFAULT	160 M	19,850	0.0%	628,008
DEFAULT	176 M	21,835	-0.3%	626,381
DEFAULT	192 M	23,820	-0.8%	622,701
DEFAULT	208 M	25,805	-1.3%	619,659
DEFAULT	224 M	27,790	-2.0%	615,485
DEFAULT	240 M	29,775	-3.3%	607,419
DEFAULT	256 M	31,760	-8.5%	574,377
DEFAULT	272 M	33,745	-15.0%	533,906
DEFAULT	288 M	35,730	-21.2%	494,850
DEFAULT	304 M	37,715	-35.2%	406,691
DEFAULT	320 M	39,700	-54.0%	288,816

Shared Pool Advice
The following values are an estimation how changes to the shared pool size would affect the Library Cache (LC).
Size	SizeFactor	LC Size	Objects	LC Time Saved	TimeFactor	Object Hits
32.0 M	-33.3%	34.0 M	3,859	1:32:49	-0.3%	370,018
48.0 M	0.0%	49.0 M	5,931	1:32:55	0.0%	371,407
64.0 M	33.3%	65.0 M	7,671	1:32:16	1.1%	373,378
80.0 M	66.7%	80.0 M	9,513	1:32:22	1.4%	374,475
96.0 M	100.0%	97.0 M	11,528	1:32:22	1.4%	374,626

PGA Target Advice
The following values are an estimation how changes to the PGA size would affect the performance. A nonzero value for OverAlloc means that Size for `PGA_TARGET_FOR_ESTIMATE` is not large enough to run the work area workload.
Size	SizeFactor	BytesProcessed	XtraBytesRW	CacheHits	OverAlloc
12.0 M	-75.0%	1.7 G	554.9 M	76.0%	46
24.0 M	-50.0%	1.7 G	381.1 M	82.0%	1
36.0 M	-25.0%	1.7 G	197.8 M	90.0%	0
48.0 M	0.0%	1.7 G	81.1 M	96.0%	0
57.6 M	20.0%	1.7 G	79.7 M	96.0%	0
67.2 M	40.0%	1.7 G	79.7 M	96.0%	0
76.8 M	60.0%	1.7 G	79.7 M	96.0%	0
86.4 M	80.0%	1.7 G	79.7 M	96.0%	0
96.0 M	100.0%	1.7 G	79.7 M	96.0%	0
144.0 M	200.0%	1.7 G	79.7 M	96.0%	0
192.0 M	300.0%	1.7 G	47.2 M	97.0%	0
288.0 M	500.0%	1.7 G	0.0 K	100.0%	0
384.0 M	700.0%	1.7 G	0.0 K	100.0%	0

PGA Workarea Usage
Target is, if possible, to have only optimal executions (i.e. process everything in memory) or at least eliminate the multipass executions. Crosscheck with the PGA Target Advices above (especially to minimize OverAlloc).
Statistic	Value
workarea executions - optimal	79,042
workarea executions - onepass	46
workarea executions - multipass	0

SYSSTAT Info
Name	Value	Description
Percent DiskSorts (of DiskSorts + MemSorts)	0.01	Should be less than 5% - higher values are an indicator to increase `SORT_AREA_SIZE`, but you of course have to consider the amount of physical memory available on your machine.
summed dirty queue length / write requests		If this value is > 100, the LGWR is too lazy -- so you may want to decrease `DB_BLOCK_MAX_DIRTY_TARGET`
free buffer inspected / free buffer requested	0.00	Increase your buffer cache if this value is too high
redo buffer allocation retries / redo blocks written	0.00	should be less than 0.01 - larger values indicate that the LGWR is not keeping up. If this happens, tuning the values for `LOG_CHECKPOINT_INTERVAL` and `LOG_CHECKPOINT_TIMEOUT` (or, with Oracle 9i, `FAST_START_MTTR_TARGET`) can help to improve the situation.
redo log space requests	31.00	how often the log file was full and Oracle had to wait for a new file to become available
table fetch continued row	896.00	How many migrated rows did we encounter during this instances life time?
Chained-Fetch-Ratio	0.03%	Percentage of fetched "continued rows" per "rowid" fetch. If this value is very low, do not care about the previous value ;)

Selected Wait Events (from v$system_event)
Name	Totals	Total WaitTime	Avg Waited	Timeouts	Description
free buffer waits	0	0	0.000	0	This wait event occurs when the database attemts to locate a clean block buffer but cannot because there are too many outstanding dirty blocks waiting to be written.
buffer busy waits	792	1:39	0.049	0	`buffer busy waits` indicate contention for a buffer in the SGA. You may need to increase the `INITRANS` parameter for a specific table or index if the event is identified as belonging to either a table or index.
db file sequential read	24,040	1:18:37	0.194	0	Indicator for I/O problems on index accesses (Consider increasing the buffer cache when value is high)
db file scattered read	100,980	1:03:10	0.038	0	Indicator for I/O problems on full table scans (On increasing `DB_FILE_MULTI_BLOCK_READ_COUNT` if this value is high see the table stats above (if enabled))
undo segment extension	0	0	0.000	0	High wait times here could indicate a problem with the extent size, the value of MINEXTENTS, or possibly IO related problems.
enqueue	40	10:15	15.375	2	This type of event may be an indication that something is either wrong with the code or possibly the physical design.
latch free	52	25	0.481	36	Latch free waits can occur for a variety of reasons including library cache issues, OS process intervention, and so on.
LGWR wait for redo copy	234	1	0.004	0	This only needs your attention when many timeouts occur. A large amount of waits/wait times does not necessarily indicate a problem - normally it just says that LGWR waited for incomplete copies into the Redo buffers that it intends to write.
log file switch (checkpoint incomplete)	0	0	0.000	0	Higher values for one of these events indicate that either your ReDo logs are too small or there are not enough log file groups
log file switch (archiving needed)	0	0	0.000	0
log file switch completion	31	2:37	3.129	0	You may consider increasing the number of logfile groups.
log buffer wait	0	0	0.000	0	If this value is too high, log buffers are filling faster than being emptied. You then have to consider to increase the number of logfile groups or to use larger log files.
log buffer space	9	4:01	27.778	1	This event frequently occurs when the log buffers are filling faster than LGWR can write them to disk. The two obvious solutions are to either increase the amount of log buffers or to change your Redo log layout and/or IO strategy.
log file parallel write	114,047	3:43:24	0.086	113,984	Indicator for Redo log layout and/or IO strategy As the wait times on these events become higher, you will notice additional Wait Events such as `log buffer space`, `log file switch (archiving needed)`, etc.
log file single write	76	12	0.158	0
SQL*Net message to client	1,942,122	5:14	0.000	0	These wait events occur when the Database unexpectedly looses Net8 connectivity with a remote client or Database. Frequent occurences of these events could indicate a networking issue.
SQL*Net message to dblink	105	0	0.000	0

Buffer Wait Ratios
Class/Name	Count	Time	Ratio
Buffer Busy Waits
data block	692	29	2,386.21
undo header	93	0	0.00
segment header	4	1	400.00
undo block	3	0	0.00

FreeList Contention
Owner	Table	AvgRowLen	PctUsed	PctFree	FreeLists	AvgFreeSpace	BlockSize
No affected tables found.

Objects causing Wait Events
On the following segments we noticed one of the events `buffer busy waits`, `db file sequential read`, `db file scattered read` or `free buffer waits` at the time the report was generated.

Top 10 IO Objects
Object	Type	TableSpace	Wait Type	Waits
ONION.INF	TABLE	DATA	physical reads	374,577
ONION.PWA	TABLE	DATA	physical reads	152,197
ONION.SYS_LOB0000012058C00002$$	LOB	DATA	physical reads	70,860
ONION.SYS_LOB0000012058C00002$$	LOB	DATA	physical reads direct	70,856
ONION.BEA	TABLE	DATA	physical reads	12,761
ONION.BED	TABLE	DATA	physical reads	10,011
ONION.INF_BACKUP	TABLE	DATA	physical reads	9,755
PERFSTAT.STATS$SQL_SUMMARY	TABLE	TOOLS	physical reads	9,650
PERFSTAT.STATS$LATCH	TABLE	TOOLS	physical reads	7,222
PERFSTAT.STATS$SYSSTAT	TABLE	TOOLS	physical reads	6,222

Top 10 BufferBusy Objects
Object	Type	TableSpace	Wait Type	Waits
ONION.PRO	TABLE	DATA	buffer busy waits	635
ONION.SLG	TABLE	DATA	buffer busy waits	6
ONION.AUF	TABLE	DATA	buffer busy waits	3
ONION.PWA	TABLE	DATA	buffer busy waits	3
ONION.XPKPRO_H	INDEX	INDEX1	buffer busy waits	3
ONION.XPKPWA	INDEX	INDEX1	buffer busy waits	3
ONION.XPKSLG	INDEX	INDEX1	buffer busy waits	2
ONION.PRO_H	TABLE	DATA	buffer busy waits	2
ONION.XPKSTS	INDEX	INDEX1	buffer busy waits	1
ONION.XPKSKW	INDEX	INDEX1	buffer busy waits	1

Top 10 RowLock Objects
Object	Type	TableSpace	Wait Type	Waits
ONION.XPKZTL	INDEX	INDEX1	row lock waits	6
ONION.XPKAUF	INDEX	INDEX1	row lock waits	3
ONION.XPKPRO_H	INDEX	INDEX1	row lock waits	2
ONION.XPKPRO	INDEX	INDEX1	row lock waits	2
ONION.XPKBAB	INDEX	INDEX1	row lock waits	1

Enqueue Waits
The following queues caused waits during the recent uptime of this instance. Ordered by cumulative wait time (desc)
Queue	Total Requests	Total Waits	PctWaits	Succ.Requests	Failed Req.	PctFailed	Cum.WaitTime
TM	895,594	33	0.00	895,593	0	0.00	3.361
TX	111,897	2	0.00	111,897	0	0.00	3.328
SQ	188	3	1.60	188	0	0.00	0.007

Invalid Objects
The following objects may need your investigation. These are not necessarily problem indicators (e.g. an invalid view may automatically re-compile), but could be:
Owner	Object	Type	Created	Last DDL
SYS	DBMS_PCLXUTIL	PACKAGE BODY	15.05.2003 05:30	29.10.2004 02:32
SYS	DBMS_PRVTAQIP	PACKAGE BODY	15.05.2003 05:31	29.10.2004 02:32
SYS	DBMS_SNAPSHOT	PACKAGE BODY	15.05.2003 05:31	29.10.2004 02:32
SYS	UTL_RECOMP	PACKAGE BODY	06.07.2004 03:30	03.11.2004 02:03