Report for oradb

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

Common Instance Information
Hostname:	sv2001
Instance:	ORADB
Version:	9.0.1.0.0
Archiver:	STARTED
ArchivedLogSize:	2,699.99 MB
FileSize (Data+Log)	2,822.10 MB
Startup / Uptime	13.05.2003 15:26 / 176.49 d
Report generated:	06.11.2003 03:09

User Information
Username	Account Status	Lock Date	Expiry Date	Default TS	Temporary TS	Created
SYS	OPEN	-	-	SYSTEM	TEMP	03.07.2002
SYSTEM	OPEN	-	-	SYSTEM	TEMP	03.07.2002
OUTLN	OPEN	-	-	SYSTEM	TEMP	03.07.2002
DBSNMP	OPEN	-	-	SYSTEM	TEMP	03.07.2002
USER	OPEN	-	-	TNG	TEMP	18.02.2003
TOOLS	OPEN	-	-	TNG	TEMP	18.02.2003
TRANSPORT	OPEN	-	-	TNG	TEMP	23.04.2003
INLN	OPEN	-	-	SYSTEM	TEMP	04.07.2002
UTIL	OPEN	-	-	TNG	TEMP	24.03.2003
SPECIAL	OPEN	-	-	TNG	TEMP	23.04.2003
ORAXS	OPEN	-	-	TNG	TEMP	24.03.2003
ORAXZ	OPEN	-	-	TNG	TEMP	24.04.2003
TERM	OPEN	-	-	TNG	TEMP	14.05.2003
DIRECT	OPEN	-	-	TNG	TEMP	26.05.2003
CTN	OPEN	-	-	MARKTAB	TEMP	13.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
MARKINX	/opt/oracle/oraData/ORADB/v02/MARKINX.dbf	ONLINE	READ WRITE	524,288.00	502,144.00	4.22	803	3152	1084725621
MARKTAB	/opt/oracle/oraData/ORADB/v02/MARKTAB.dbf	ONLINE	READ WRITE	1,048,576.00	1,027,648.00	2.00	489	1983	383038759
TNG	/opt/oracle/oraData/ORADB/v01/tng.dbf	ONLINE	READ WRITE	450,560.00	386,880.00	14.13	805578	470087	680998567
SYSTEM	/opt/oracle/oraData/ORADB/v01/system01.dbf	SYSTEM	READ WRITE	204,904.00	112.00	99.95	55156	52652	-1580357513
TOOLS	/opt/oracle/oraData/ORADB/v02/tools01.dbf	ONLINE	READ WRITE	1,024.00	960.00	6.25	12	18	-984450757
UNDOTBS	/opt/oracle/oraData/ORADB/v01/undotbs01.dbf	ONLINE	READ WRITE	327,680.00	326,336.00	0.41	576	515529	694221631
USERS	/opt/oracle/oraData/ORADB/v02/users01.dbf	ONLINE	READ WRITE	25,600.00	25,536.00	0.25	12	18	-984450757

Rollback Segments
Segment	Status	Size (kB)	OptSize (kB)	HWMSize (kB)	Waits	XActs	Shrinks	Wraps	AveShrink	AveActive
SYSTEM	ONLINE	392.00	.00	392.00	0	0	0	0	0	0
_SYSSMU1$	ONLINE	120.00	.00	27,768.00	74	0	242	1599	1099488	3188926
_SYSSMU10$	ONLINE	120.00	.00	17,400.00	41	0	237	1610	907549	757348
_SYSSMU2$	ONLINE	120.00	.00	30,840.00	72	0	240	1637	1280136	904492
_SYSSMU3$	ONLINE	120.00	.00	20,600.00	56	0	218	1502	822807	1514893
_SYSSMU4$	ONLINE	120.00	.00	27,768.00	35	0	237	1615	1174394	1354970
_SYSSMU5$	ONLINE	120.00	.00	27,640.00	70	0	220	1469	1387277	3674807
_SYSSMU6$	ONLINE	120.00	.00	19,448.00	48	0	227	1595	890365	2507994
_SYSSMU7$	ONLINE	120.00	.00	22,520.00	66	0	250	1692	1125646	648053
_SYSSMU8$	ONLINE	120.00	.00	22,520.00	27	0	230	1507	1113257	1578491
_SYSSMU9$	ONLINE	120.00	.00	23,544.00	70	0	259	1717	1094122	514761

Memory Values
Name	Size
Fixed Size	273.05 kB
Variable Size	163,840.00 kB
Database Buffers	65,536.00 kB
Redo Buffers	520.00 kB
shared_pool_size	65,536.00 kB
shared_pool_reserved_size	3,276.80 kB
large_pool_size	1,024.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

Pool Sizes
Pool	Space
Shared_Pool_Size	65,536.00 kB
Shared_Pool_Reserved_Size	3,276.80 kB
Large_Pool_Size	1,024.00 kB
Java_Pool_Size	65,536.00 kB
Sort_Area_Size	512.00 kB
Sort_Area_Retained_Size	0.00 kB
Pool	Free Space
shared pool	9,847.12 kB
java pool	59,812.00 kB

Shared Pool Information
Library Cache
If one of the following conditions is *NOT* met this indicates that SHARED_POOL_SIZE may have to be increased: (reloads/pins)*100 < 1 gethitratio > 0.9
NameSpace	Gets	Pins	Reloads	GetHitRatio
SQL AREA	8957367	68238146	174286	95.56
TABLE/PROCEDURE	2243720	9116894	22109	99.16
BODY	191527	194822	1744	99.90
TRIGGER	472118	475871	3316	99.81
INDEX	5219	5238	6	70.63
CLUSTER	31123	28191	0	99.31
OBJECT	0	0	0	100.00
PIPE	0	0	0	100.00
JAVA SOURCE	33	33	0	90.91
JAVA RESOURCE	0	0	0	100.00
JAVA DATA	1067	12087	10	98.59

Row Cache
If Ratio = (getmisses/gets)*100 > 15, SHARED_POOL_SIZE may have to be increased:
Parameter		Gets	GetMisses	Ratio
dc_free_extents		236608	78	0.03
dc_used_extents		70	70	100.00
dc_segments		2268698	3427	0.15
dc_tablespaces		1153177	15	0.00
dc_tablespace_quotas		23155	20	0.09
dc_files		2214	7	0.32
dc_users		1302933	184	0.01
dc_rollback_segments		1123388	12	0.00
dc_objects		1352850	13700	1.01
dc_global_oids		32	4	12.50
dc_constraints		18298	8390	45.85
dc_object_ids		3707062	11521	0.31
dc_sequences		38401	189	0.49
dc_usernames		780636	72	0.01
dc_database_links		124883491	89	0.00
dc_histogram_defs		749292	23274	3.11
table scns		231	231	100.00
dc_profiles		29692	1	0.00
dc_user_grants		226080	160	0.07

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	3922101	3213115877	83921615	0.00

SYSSTAT Info
Name	Value	Description
DiskSorts / MemorySorts	0.00	Higher values are an indicator to increase SORT_AREA_SIZE
summed dirty queue length / write requests	104,053.00	If this value is > 100, the LGWR is too lazy -- so you maywant to decrease DB_BLOCK_MAX_DIRTY_TARGET
free buffer inspected / free buffer requested	0.01	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
redo log space requests	95.00	how often the log file was full and Oracle had to wait for a new file to become available
table fetch continued row	60,748,067.00	How many migrated rows did we encounter during this instances life time? If the number is markable, you may have to analyse your tables: ANALYZE TABLE tablename COMPUTE STATISTICS; SELECT num_rows,chain_cnt FROM dba_tables WHERE table_name='tablename'; utlchain.sql then may help you to automatically eliminate migration (correct PCTFREE before running that!).

Selected Wait Events (from v$system_event)
Name	Totals	Total WaitTime	Avg Waited	Timeouts	Description
db file sequential read	323,948	32,559	0	0	Indicator for I/O problems on index accesses (Consider increasing the buffer cache when value is high)
db file scattered read	542,952	44,753	0	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)
latch free	25,250	25,232	1	9,768
LGWR wait for redo copy	993	61	0	26
log file switch (checkpoint incomplete)	0	0	0	0	Higher values indicate that either your ReDo logs are too small or there are not enough log file groups
log file switch completion	87	1,234	14	3	You may consider increasing the number of logfile groups.
log buffer wait	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.

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. If you had many db file scattered reads above and now find some entries with segment type = table in here, these may need some\|more\|better\|other indices. Use Statspack or Oracle Enterprise Manager Diagnostics Pack to find out more. Other things that may help to avoid some of the db file read* wait events are: Tune the SQL statements used by your applications and users (most important!) Re-Analyze the schema to help the optimizer with accurate data e.g. with dbms_stats Stripe objects over multiple disk volumes Pin frequently used objects Increase the buffer caches
Owner	Segment Name	Segment Type

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
DIRECT	INSERT_USER	PROCEDURE	28.05.2003 10:44	28.05.2003 12:27
UTIL	/179f813b_IllegalNomChangeExce	JAVA CLASS	24.03.2003 01:37	24.03.2003 01:37
UTIL	/867432a1_LimitNotSetException	JAVA CLASS	04.08.2003 04:26	04.08.2003 04:26
UTIL	/d69ea820_IllegalOrderChangeEx	JAVA CLASS	04.08.2003 04:26	04.08.2003 04:26
UTIL	PDV_BESTAND	VIEW	05.09.2003 12:54	05.09.2003 12:54
UTIL	PDV_TRANSAKTION	VIEW	05.09.2003 12:54	05.09.2003 12:54
UTIL	PUFFER_DECIDE_AIRL_ENTRY	TRIGGER	05.09.2003 01:05	26.09.2003 07:38
UTIL	PUFFER_DECIDE_ERR_BRL	TRIGGER	05.09.2003 01:05	05.09.2003 01:05
UTIL	TO_DOCSETTLEMENT	PROCEDURE	26.09.2003 07:38	26.09.2003 07:38
UTIL	TO_SETTLEMENT	FUNCTION	26.09.2003 07:38	26.09.2003 07:38
UTIL	UTIL_PUFFER_DECIDE	PACKAGE BODY	26.09.2003 07:38	26.09.2003 07:38
UTIL	Z_MDW	VIEW	05.09.2003 01:05	05.09.2003 01:05
SPECIAL	DECIDE_UMSATZ_BRL	TRIGGER	24.04.2003 03:58	24.04.2003 03:58
TERM	/867432a1_LimitNotSetException	JAVA CLASS	22.05.2003 01:10	06.08.2003 02:16
TERM	/d69ea820_IllegalOrderChangeEx	JAVA CLASS	22.05.2003 01:10	06.08.2003 02:16

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)	17179567
table scans (long tables)	94919
table scans (rowid ranges)	56
table scans (cache partitions)	0
table scans (direct read)	0
If there are tables that will for sure need more extents shortly, we can reduce I/O overhead allocating some extents for them in advance, using "ALTER TABLE tablename ALLOCATE EXTENT". Here are some candidates, having less than 10 percent free blocks left:
ORAXZ.ORAXZTRANABST	0.00%
ORAXZ.ORAXZAUSZUGABST	0.00%
CTN.DDNCOF	3.75%
CTN.EPOSRF	9.38%
CTN.FEHLMF	3.75%