Report for ORADB

[ Users ] [ Datafiles ] [ Rollback ] [ Memory ] [ Pool Sizes ] [ Shared Pool ] [ Buffer Pool ] [ Advices ] [ SysStat ] [ Events ] [ Wait Objects ] [ Invalid Objects ] [ Misc ]

Common Instance Information
Hostname:	buzz
Instance:	ORADB
Version:	9.2.0.1.0
Archiver:	STARTED
ArchivedLogSize:	MB
FileSize (Data+Log)	11,180.00 MB
Startup / Uptime	28.11.2003 16:43 / 46.05 d
Report generated:	13.01.2004 17:53

User Information
Username	Account Status	Lock Date	Expiry Date	Default TS	Temporary TS	Created
SYS	OPEN	-	-	SYSTEM	TEMP	28.10.2003
SYSTEM	OPEN	-	-	SYSTEM	TEMP	28.10.2003
OUTLN	OPEN	-	-	SYSTEM	TEMP	28.10.2003
USER0	OPEN	-	-	DATA_DDB	TEMP	19.11.2003
INLN	OPEN	-	-	SYSTEM	TEMP	19.11.2003
USER1	OPEN	-	-	DATA01	TEMP	01.12.2003
DBSNMP	OPEN	-	-	SYSTEM	TEMP	28.10.2003
WUM	EXPIRED & LOCKED	28.10.2003	-	SYSTEM	TEMP	28.10.2003

Admins
User	Admin Option
SYS	YES
SYSTEM	YES

Data Files
Tablespace	Datafile	Status	Enabled	Size (kB)	Free (kB)	Used (%)	Phys.Reads	Phys.Writes	Avg.I/O Time
DATA01	/opt/oraData/ORADB/v01/data01.dbf	ONLINE	READ WRITE	4,710,400.00	57,280.00	98.78	36,371,076	9,553,609	-967,520,879
INDX01	/opt/oraData/ORADB/v01/indx01.dbf	ONLINE	READ WRITE	2,129,920.00	8,128.00	99.62	202,984,761	18,401,904	1,562,545,745
SYSTEM	/opt/oraData/ORADB/v01/system.dbf	SYSTEM	READ WRITE	153,600.00	11,472.00	92.53	106,514	5,962	0
TEMP	/opt/oraData/ORADB/v01/temp.dbf	ONLINE	READ WRITE	122,880.00	11,472.00	90.66	106,514	5,962	0
UNDOTBS	/opt/oraData/ORADB/v01/undotbs.dbf	ONLINE	READ WRITE	1,894,400.00	1,734,336.00	8.45	27,277	25,679,417	-1,727,836,658

Rollback Segments
Segment	Status	Size (kB)	OptSize (kB)	HWMSize (kB)	Waits	XActs	Shrinks	Wraps	AveShrink	AveActive
SYSTEM	ONLINE	392.00	0.00	392.00	0	0	0	0	0	0
_SYSSMU1$	ONLINE	15,480.00	0.00	227,448.00	2,729	0	1,266	14,904	2,907,551	11,288,223
_SYSSMU10$	ONLINE	15,480.00	0.00	212,088.00	2,584	0	1,259	15,163	2,755,166	10,384,987
_SYSSMU2$	ONLINE	16,504.00	0.00	196,728.00	3,006	0	1,289	15,233	2,589,155	11,821,724
_SYSSMU3$	ONLINE	15,480.00	0.00	192,632.00	3,721	0	1,471	16,629	180,435	11,238,835
_SYSSMU4$	ONLINE	15,480.00	0.00	230,520.00	3,574	0	1,571	17,432	958,636	11,295,682
_SYSSMU5$	ONLINE	16,504.00	0.00	204,920.00	3,027	0	1,284	15,523	2,878,786	11,947,996
_SYSSMU6$	ONLINE	15,480.00	0.00	224,376.00	2,745	0	1,234	14,890	2,743,855	11,182,469
_SYSSMU7$	ONLINE	16,504.00	0.00	196,728.00	3,222	0	1,321	15,455	2,808,473	11,779,619
_SYSSMU8$	ONLINE	16,504.00	0.00	198,776.00	3,664	0	1,439	16,383	298,122	11,845,204
_SYSSMU9$	ONLINE	16,504.00	0.00	221,304.00	3,566	0	1,530	17,103	768,527	11,902,584

Memory Values
Name	Size
Fixed Size	441.94 kB
Variable Size	1,490,944.00 kB
Database Buffers	212,992.00 kB
Redo Buffers	49,292.00 kB
shared_pool_size	114,688.00 kB
shared_pool_reserved_size	5,734.40 kB
large_pool_size	16,384.00 kB
java_pool_size	65,536.00 kB
buffer_pool_keep	0 kB
buffer_pool_recycle	0 kB
global_context_pool_size	0 kB
olap_page_pool_size	32,768.00 kB

Pool Sizes
Pool	Space
Shared_Pool_Size	114,688.00 kB
Shared_Pool_Reserved_Size	5,734.40 kB
Large_Pool_Size	16,384.00 kB
Java_Pool_Size	65,536.00 kB
Sort_Area_Size	64.00 kB
Sort_Area_Retained_Size	0.00 kB
Pool	Free Space
shared pool	97,346.29 kB
large pool	16,384.00 kB
java pool	65,536.00 kB

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	6,769,067	800,824,588	15	0.00	99.99
TABLE/PROCEDURE	50,454	260,044,809	424	0.00	96.69
BODY	630	630	0	0.00	95.71
TRIGGER	15,161	15,161	0	0.00	99.97
INDEX	2,171	2,136	0	0.00	1.84
CLUSTER	869	926	0	0.00	98.85
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			7,446	3	0.04
dc_segments			108,560	2,672	2.46
dc_tablespaces			358,298	10	0.00
dc_tablespace_quotas			2,854	25	0.88
dc_files			979	10	1.02
dc_users			179,306	23	0.01
dc_rollback_segments			471,370	11	0.00
dc_objects			27,501	3,726	13.55
dc_global_oids			4	2	50.00
dc_constraints			7,596	7,589	99.91
dc_object_ids			13,868	3,494	25.19
dc_sequences			6,500,715	334	0.01
dc_usernames			33,226	10	0.03
dc_histogram_defs			7,950	7,489	94.20
dc_table_scns			392	392	100.00
dc_profiles			13,779	1	0.01
dc_user_grants			126,535	21	0.02

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	402,085,999	1,033,967,809	4,832,840,113	0.07

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	2,002	23.5%	496,676,612
DEFAULT	32 M	4,004	19.2%	479,412,056
DEFAULT	48 M	6,006	17.9%	474,148,367
DEFAULT	64 M	8,008	17.0%	470,301,261
DEFAULT	80 M	10,010	15.8%	465,711,940
DEFAULT	96 M	12,012	14.5%	460,273,604
DEFAULT	112 M	14,014	13.0%	454,136,868
DEFAULT	128 M	16,016	11.3%	447,485,568
DEFAULT	144 M	18,018	9.2%	439,220,802
DEFAULT	160 M	20,020	6.9%	429,951,970
DEFAULT	176 M	22,022	4.3%	419,290,920
DEFAULT	192 M	24,024	1.8%	409,227,712
DEFAULT	208 M	26,026	0.0%	402,086,000
DEFAULT	224 M	28,028	-1.6%	395,520,001
DEFAULT	240 M	30,030	-3.2%	389,155,694
DEFAULT	256 M	32,032	-5.1%	381,490,818
DEFAULT	272 M	34,034	-7.3%	372,614,587
DEFAULT	288 M	36,036	-10.1%	361,586,508
DEFAULT	304 M	38,038	-12.8%	350,629,545
DEFAULT	320 M	40,040	-16.6%	335,513,327

PGA Target Advice
The following values are an estimation how changes to the PGA size would affect the performance.
Size	SizeFactor	BytesProcessed	XtraBytesRW	CacheHits	OverAlloc
10,485,760	-50.0%	2,262,507,520	1,809,887,232	56.0%	33
15,728,640	-25.0%	2,262,507,520	1,809,887,232	56.0%	33
20,971,520	0.0%	2,262,507,520	1,528,704,000	60.0%	4
25,165,824	20.0%	2,262,507,520	1,528,704,000	60.0%	0
29,360,128	40.0%	2,262,507,520	1,528,704,000	60.0%	0
33,554,432	60.0%	2,262,507,520	1,528,704,000	60.0%	0
37,748,736	80.0%	2,262,507,520	1,528,704,000	60.0%	0
41,943,040	100.0%	2,262,507,520	1,528,704,000	60.0%	0
62,914,560	200.0%	2,262,507,520	829,679,616	73.0%	0
83,886,080	300.0%	2,262,507,520	440,202,240	84.0%	0
125,829,120	500.0%	2,262,507,520	440,202,240	84.0%	0
167,772,160	700.0%	2,262,507,520	440,202,240	84.0%	0

SYSSTAT Info
Name	Value	Description
Percent DiskSorts (of DiskSorts + MemSorts)	0.00	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.03	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	1,939.00	how often the log file was full and Oracle had to wait for a new file to become available
table fetch continued row	115.00	How many migrated rows did we encounter during this instances life time?

Selected Wait Events (from v$system_event)
Name	Totals	Total WaitTime (s)	Avg Waited (ms)	Timeouts	Description
free buffer waits	20	1,496	74,800.0	8	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	36,920	679	18.4	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	215,580,777	10,467,980	48.6	0	Indicator for I/O problems on index accesses (Consider increasing the buffer cache when value is high)
db file scattered read	23,924,760	3,724,467	155.7	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 first block of Miscellaneous below)
undo segment extension	11,106,882	4,379	0.4	11,106,706	High wait times here could indicate a problem with the extent size, the value of MINEXTENTS, or possibly IO related problems.
enqueue	197	310	1,573.6	0	This type of event may be an indication that something is either wrong with the code or possibly the physical design.
latch free	3,613	3,051	844.5	1,894	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	5,371	14	2.6	8	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.0	0	Higher values for this event indicate that either your ReDo logs are too small or there are not enough log file groups
log file switch completion	1,737	9,812	5,648.8	3	You may consider increasing the number of logfile groups.
log buffer wait	0	0	0.0	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	0	0	0.0	0	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	801,190	39,204	48.9	800,280	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	1,654	496	299.9	0
SQL*Net message to client	520,363,091	110,904	0.2	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	0	0	0.0	0

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
USER1.TAR_SYSDAT_IDX	INDEX	INDX01	physical reads	193,533,789
USER1.TAR_IDX1	INDEX	INDX01	physical writes	15,254,569
USER1.TARVALUE	TABLE	DATA01	physical writes	9,165,685
USER1.TARVALUE	TABLE	DATA01	physical reads	9,010,932
USER1.TAR_IDX1	INDEX	INDX01	physical reads	7,866,339
USER1.TAR_SYSDAT_IDX	INDEX	INDX01	physical writes	1,028,840
USER1.TAR_YDAY_IDX	INDEX	INDX01	physical writes	909,281
USER1.TAR_YDAY_IDX	INDEX	INDX01	physical reads	828,748
USER1.TAR_PK	INDEX	INDX01	physical writes	571,967
USER1.TAR_PK	INDEX	INDX01	physical reads	524,994

Top 10 BufferBusy Objects
Object	Type	TableSpace	Wait Type	Waits
USER1.TARVALUE	TABLE	DATA01	buffer busy waits	239
USER1.TAR_IDX1	INDEX	INDX01	buffer busy waits	4
USER1.TAR_YDAY_IDX	INDEX	INDX01	buffer busy waits	2
USER1.TAR_SYSDAT_IDX	INDEX	INDX01	buffer busy waits	2

Top 10 RowLock Objects
Object	Type	TableSpace	Wait Type	Waits
USER1.TAR_SYSDAT_IDX	INDEX	INDX01	row lock waits	167
USER1.TAR_YDAY_IDX	INDEX	INDX01	row lock waits	137
USER1.TAR_IDX1	INDEX	INDX01	row lock waits	120
USER1.TAR_PK	INDEX	INDX01	row lock waits	107

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	Succ.Requests	Failed Req.	Cum.WaitTime
CF	1,789,551	183	1,789,549	2	2,067
TC	70	14	70	0	1,111

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	Typ	Created	Last DDL
SYS	ALL_REPGROUPED_COLUMN	VIEW	28.10.2003 04:50	28.10.2003 04:50
SYS	ALL_REPPARAMETER_COLUMN	VIEW	28.10.2003 04:51	28.10.2003 04:51
SYS	DBA_REPGROUPED_COLUMN	VIEW	28.10.2003 04:50	28.10.2003 04:50
SYS	DBA_REPPARAMETER_COLUMN	VIEW	28.10.2003 04:51	28.10.2003 04:51
SYS	DBMS_REPCAT_RGT_CUST	PACKAGE BODY	28.10.2003 04:52	28.10.2003 04:52
SYS	DBMS_REPCAT_UTL4	PACKAGE BODY	28.10.2003 04:52	28.10.2003 04:52
SYS	USER_REPGROUPED_COLUMN	VIEW	28.10.2003 04:50	28.10.2003 04:50
SYS	USER_REPPARAMETER_COLUMN	VIEW	28.10.2003 04:51	28.10.2003 04:51
SYS	_ALL_REPCOLUMN_GROUP	VIEW	28.10.2003 04:50	28.10.2003 04:50
SYS	_ALL_REPCONFLICT	VIEW	28.10.2003 04:51	28.10.2003 04:51
SYS	_ALL_REPGROUPED_COLUMN	VIEW	28.10.2003 04:50	28.10.2003 04:50
SYS	_ALL_REPPARAMETER_COLUMN	VIEW	28.10.2003 04:51	28.10.2003 04:51
SYS	_ALL_REPRESOLUTION	VIEW	28.10.2003 04:50	28.10.2003 04:50

Miscellaneous
Name	Value
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)	130,011,594
table scans (long tables)	929
table scans (rowid ranges)	0
table scans (cache partitions)	0
table scans (direct read)	0