awr报告详解（2）

IO Stats

• Tablespace IO Stats
• File IO Stats

Back to Top

Tablespace IO Stats

• ordered by IOs (Reads + Writes) desc

Tablespace	Reads	Av Reads/s	Av Rd(ms)	Av Blks/Rd	Writes	Av Writes/s	Buffer Waits	Av Buf Wt(ms)
ICCIDAT01	67,408	14	3.76	3.17	160,261	34	6	0.00
UNDOTBS1	10	0	12.00	1.00	57,771	12	625	0.02
TEMP	15,022	3	8.74	7.24	3,831	1	0	0.00
USERS	68	0	5.44	1.00	971	0	0	0.00
SYSAUX	263	0	5.48	1.00	458	0	0	0.00
SYSTEM	32	0	5.94	1.00	158	0	3	23.33
UNDOTBS2	6	0	16.67	1.00	6	0	0	0.00

显示每个表空间的I/O统计。根据Oracle经验，Av Rd(ms) [Average Reads in milliseconds]不应该超过30，否则认为有I/O争用。

Back to IO Stats 
Back to Top

File IO Stats

• ordered by Tablespace, File

Tablespace	Filename	Reads	Av Reads/s	Av Rd(ms)	Av Blks/Rd	Writes	Av Writes/s	Buffer Waits	Av Buf Wt(ms)
ICCIDAT01	/dev/rora_icci01	5,919	1	4.30	3.73	15,161	3	1	0.00
ICCIDAT01	/dev/rora_icci02	7,692	2	4.12	3.18	16,555	4	0	0.00
ICCIDAT01	/dev/rora_icci03	6,563	1	2.59	3.80	15,746	3	0	0.00
ICCIDAT01	/dev/rora_icci04	8,076	2	2.93	3.11	16,164	3	0	0.00
ICCIDAT01	/dev/rora_icci05	6,555	1	2.61	3.31	21,958	5	0	0.00
ICCIDAT01	/dev/rora_icci06	6,943	1	4.03	3.41	20,574	4	0	0.00
ICCIDAT01	/dev/rora_icci07	7,929	2	4.12	2.87	18,263	4	0	0.00
ICCIDAT01	/dev/rora_icci08	7,719	2	3.83	2.99	17,361	4	0	0.00
ICCIDAT01	/dev/rora_icci09	6,794	1	4.79	3.29	18,425	4	0	0.00
ICCIDAT01	/dev/rora_icci10	211	0	5.31	1.00	6	0	0	0.00
ICCIDAT01	/dev/rora_icci11	1,168	0	4.45	1.00	6	0	0	0.00
ICCIDAT01	/dev/rora_icci12	478	0	4.23	1.00	6	0	0	0.00
ICCIDAT01	/dev/rora_icci13	355	0	5.13	1.00	6	0	0	0.00
ICCIDAT01	/dev/rora_icci14	411	0	4.91	1.00	6	0	1	0.00
ICCIDAT01	/dev/rora_icci15	172	0	5.29	1.00	6	0	1	0.00
ICCIDAT01	/dev/rora_icci16	119	0	7.23	1.00	6	0	1	0.00
ICCIDAT01	/dev/rora_icci17	227	0	6.26	1.00	6	0	1	0.00
ICCIDAT01	/dev/rora_icci18	77	0	8.44	1.00	6	0	1	0.00
SYSAUX	/dev/rora_SYSAUX	263	0	5.48	1.00	458	0	0	0.00
SYSTEM	/dev/rora_SYSTEM	32	0	5.94	1.00	158	0	3	23.33
TEMP	/dev/rora_TEMP	3,653	1	5.67	6.61	827	0	0
TEMP	/dev/rora_TEMP2	2,569	1	4.42	6.70	556	0	0
TEMP	/dev/rora_TEMP3	1,022	0	2.50	16.86	557	0	0
TEMP	/dev/rora_TEMP5	7,778	2	12.43	6.46	1,891	0	0
UNDOTBS1	/dev/rora_UNDO0101	10	0	12.00	1.00	57,771	12	625	0.02
UNDOTBS2	/dev/rora_UNDO0201	6	0	16.67	1.00	6	0	0	0.00
USERS	/dev/rora_USERS	68	0	5.44	1.00	971	0	0	0.00

Back to IO Stats 
Back to Top

Buffer Pool Statistics

• Standard block size Pools D: default, K: keep, R: recycle
• Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

P	Number of Buffers	Pool Hit%	Buffer Gets	Physical Reads	Physical Writes	Free Buff Wait	Writ Comp Wait	Buffer Busy Waits
D	401,071	99	15,480,754	213,729	437,340	0	0	634

Back to Top

Advisory Statistics

• Instance Recovery Stats
• Buffer Pool Advisory
• PGA Aggr Summary
• PGA Aggr Target Stats
• PGA Aggr Target Histogram
• PGA Memory Advisory
• Shared Pool Advisory
• SGA Target Advisory
• Streams Pool Advisory
• Java Pool Advisory

Back to Top

Instance Recovery Stats

• B: Begin snapshot, E: End snapshot

	Targt MTTR (s)	Estd MTTR (s)	Recovery Estd IOs	Actual Redo Blks	Target Redo Blks	Log File Size Redo Blks	Log Ckpt Timeout Redo Blks	Log Ckpt Interval Redo Blks
B	0	11	369	2316	5807	1883700	5807
E	0	98	116200	1828613	1883700	1883700	5033355

Back to Advisory Statistics 
Back to Top

Buffer Pool Advisory

• Only rows with estimated physical reads >0 are displayed
• ordered by Block Size, Buffers For Estimate

P	Size for Est (M)	Size Factor	Buffers for Estimate	Est Phys Read Factor	Estimated Physical Reads
D	320	0.10	38,380	1.34	10,351,726
D	640	0.19	76,760	1.25	9,657,000
D	960	0.29	115,140	1.08	8,365,242
D	1,280	0.38	153,520	1.04	8,059,415
D	1,600	0.48	191,900	1.02	7,878,202
D	1,920	0.57	230,280	1.01	7,841,140
D	2,240	0.67	268,660	1.01	7,829,141
D	2,560	0.77	307,040	1.01	7,817,370
D	2,880	0.86	345,420	1.01	7,804,884
D	3,200	0.96	383,800	1.00	7,784,014
D	3,344	1.00	401,071	1.00	7,748,403
D	3,520	1.05	422,180	0.99	7,702,243
D	3,840	1.15	460,560	0.99	7,680,429
D	4,160	1.24	498,940	0.99	7,663,046
D	4,480	1.34	537,320	0.99	7,653,232
D	4,800	1.44	575,700	0.99	7,645,544
D	5,120	1.53	614,080	0.98	7,630,008
D	5,440	1.63	652,460	0.98	7,616,886
D	5,760	1.72	690,840	0.98	7,614,591
D	6,080	1.82	729,220	0.98	7,613,191
D	6,400	1.91	767,600	0.98	7,599,930

Back to Advisory Statistics 
Back to Top

PGA Aggr Summary

• PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory

PGA Cache Hit %	W/A MB Processed	Extra W/A MB Read/Written
87.91	1,100	151

Back to Advisory Statistics 
Back to Top

PGA Aggr Target Stats

• B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval)
• Auto PGA Target - actual workarea memory target
• W/A PGA Used - amount of memory used for all Workareas (manual + auto)
• %PGA W/A Mem - percentage of PGA memory allocated to workareas
• %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
• %Man W/A Mem - percentage of workarea memory under manual control

	PGA Aggr Target(M)	Auto PGA Target(M)	PGA Mem Alloc(M)	W/A PGA Used(M)	%PGA W/A Mem	%Auto W/A Mem	%Man W/A Mem	Global Mem Bound(K)
B	1,024	862	150.36	0.00	0.00	0.00	0.00	104,850
E	1,024	860	154.14	0.00	0.00	0.00	0.00	104,850

Back to Advisory Statistics 
Back to Top

PGA Aggr Target Histogram

• Optimal Executions are purely in-memory operations

Low Optimal	High Optimal	Total Execs	Optimal Execs	1-Pass Execs	M-Pass Execs
2K	4K	1,385	1,385	0	0
64K	128K	28	28	0	0
128K	256K	5	5	0	0
256K	512K	79	79	0	0
512K	1024K	108	108	0	0
1M	2M	7	7	0	0
8M	16M	1	1	0	0
128M	256M	3	2	1	0
256M	512M	1	1	0	0

Back to Advisory Statistics 
Back to Top

PGA Memory Advisory

• When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0

PGA Target Est (MB)	Size Factr	W/A MB Processed	Estd Extra W/A MB Read/ Written to Disk	Estd PGA Cache Hit %	Estd PGA Overalloc Count
128	0.13	4,652.12	2,895.99	62.00	0
256	0.25	4,652.12	2,857.13	62.00	0
512	0.50	4,652.12	2,857.13	62.00	0
768	0.75	4,652.12	2,857.13	62.00	0
1,024	1.00	4,652.12	717.82	87.00	0
1,229	1.20	4,652.12	717.82	87.00	0
1,434	1.40	4,652.12	717.82	87.00	0
1,638	1.60	4,652.12	717.82	87.00	0
1,843	1.80	4,652.12	717.82	87.00	0
2,048	2.00	4,652.12	717.82	87.00	0
3,072	3.00	4,652.12	717.82	87.00	0
4,096	4.00	4,652.12	717.82	87.00	0
6,144	6.00	4,652.12	717.82	87.00	0
8,192	8.00	4,652.12	717.82	87.00	0

Back to Advisory Statistics 
Back to Top

Shared Pool Advisory

• SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
• Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid.

Shared Pool Size(M)	SP Size Factr	Est LC Size (M)	Est LC Mem Obj	Est LC Time Saved (s)	Est LC Time Saved Factr	Est LC Load Time (s)	Est LC Load Time Factr	Est LC Mem Obj Hits
304	0.43	78	7,626	64,842	1.00	31	1.00	3,206,955
384	0.55	78	7,626	64,842	1.00	31	1.00	3,206,955
464	0.66	78	7,626	64,842	1.00	31	1.00	3,206,955
544	0.77	78	7,626	64,842	1.00	31	1.00	3,206,955
624	0.89	78	7,626	64,842	1.00	31	1.00	3,206,955
704	1.00	78	7,626	64,842	1.00	31	1.00	3,206,955
784	1.11	78	7,626	64,842	1.00	31	1.00	3,206,955
864	1.23	78	7,626	64,842	1.00	31	1.00	3,206,955
944	1.34	78	7,626	64,842	1.00	31	1.00	3,206,955
1,024	1.45	78	7,626	64,842	1.00	31	1.00	3,206,955
1,104	1.57	78	7,626	64,842	1.00	31	1.00	3,206,955
1,184	1.68	78	7,626	64,842	1.00	31	1.00	3,206,955
1,264	1.80	78	7,626	64,842	1.00	31	1.00	3,206,955
1,344	1.91	78	7,626	64,842	1.00	31	1.00	3,206,955
1,424	2.02	78	7,626	64,842	1.00	31	1.00	3,206,955

Back to Advisory Statistics 
Back to Top

SGA Target Advisory

SGA Target Size (M)	SGA Size Factor	Est DB Time (s)	Est Physical Reads
1,024	0.25	9,060	9,742,760
2,048	0.50	7,612	7,948,245
3,072	0.75	7,563	7,886,258
4,096	1.00	7,451	7,748,338
5,120	1.25	7,423	7,713,470
6,144	1.50	7,397	7,680,927
7,168	1.75	7,385	7,666,980
8,192	2.00	7,385	7,666,980

Back to Advisory Statistics 
Back to Top

Streams Pool Advisory

No data exists for this section of the report.

Back to Advisory Statistics 
Back to Top

Java Pool Advisory

No data exists for this section of the report.

Back to Advisory Statistics 
Back to Top

Wait Statistics

• Buffer Wait Statistics
• Enqueue Activity

Back to Top

Buffer Wait Statistics

• ordered by wait time desc, waits desc

Class	Waits	Total Wait Time (s)	Avg Time (ms)
data block	3	0	23
undo header	616	0	0
file header block	8	0	0
undo block	7	0	0

Back to Wait Statistics 
Back to Top

Enqueue Activity

• only enqueues with waits are shown
• Enqueue stats gathered prior to 10g should not be compared with 10g data
• ordered by Wait Time desc, Waits desc

Enqueue Type (Request Reason)	Requests	Succ Gets	Failed Gets	Waits	Wt Time (s)	Av Wt Time(ms)
FB-Format Block	14,075	14,075	0	7,033	3	0.43
US-Undo Segment	964	964	0	556	0	0.32
WF-AWR Flush	24	24	0	14	0	9.00
HW-Segment High Water Mark	4,223	4,223	0	37	0	1.22
CF-Controlfile Transaction	10,548	10,548	0	58	0	0.67
TX-Transaction (index contention)	1	1	0	1	0	35.00
TM-DML	121,768	121,761	6	70	0	0.43
PS-PX Process Reservation	103	103	0	46	0	0.65
TT-Tablespace	9,933	9,933	0	39	0	0.54
TD-KTF map table enqueue (KTF dump entries)	12	12	0	12	0	1.42
TA-Instance Undo	18	18	0	13	0	0.38
PI-Remote PX Process Spawn Status	16	16	0	8	0	0.50
MW-MWIN Schedule	3	3	0	3	0	0.67
DR-Distributed Recovery	3	3	0	3	0	0.33
TS-Temporary Segment	14	11	3	3	0	0.33
AF-Advisor Framework (task serialization)	14	14	0	1	0	1.00
JS-Job Scheduler (job run lock - synchronize)	2	2	0	1	0	1.00
UL-User-defined	2	2	0	1	0	1.00
MD-Materialized View Log DDL	6	6	0	2	0	0.00

Back to Wait Statistics 
Back to Top

Undo Statistics

• Undo Segment Summary
• Undo Segment Stats

Back to Top

Undo Segment Summary

• Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
• STO - Snapshot Too Old count, OOS - Out of Space count
• Undo segment block stats:
• uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
• eS - expired Stolen, eR - expired Released, eU - expired reUsed

Undo TS#	Num Undo Blocks (K)	Number of Transactions	Max Qry Len (s)	Max Tx Concurcy	Min/Max TR (mins)	STO/ OOS	uS/uR/uU/ eS/eR/eU
1	219.12	113,405	0	6	130.95/239.25	0/0	0/0/0/13/24256/0

Back to Undo Statistics 
Back to Top

Undo Segment Stats

• Most recent 35 Undostat rows, ordered by Time desc

End Time	Num Undo Blocks	Number of Transactions	Max Qry Len (s)	Max Tx Concy	Tun Ret (mins)	STO/ OOS	uS/uR/uU/ eS/eR/eU
25-Dec 15:18	182,021	74,309	0	5	131	0/0	0/0/0/13/24256/0
25-Dec 15:08	57	170	0	3	239	0/0	0/0/0/0/0/0
25-Dec 14:58	68	31	0	2	229	0/0	0/0/0/0/0/0
25-Dec 14:48	194	4,256	0	4	219	0/0	0/0/0/0/0/0
25-Dec 14:38	570	12,299	0	5	209	0/0	0/0/0/0/0/0
25-Dec 14:28	36,047	21,328	0	6	200	0/0	0/0/0/0/0/0
25-Dec 14:18	70	907	0	3	162	0/0	0/0/0/0/0/0
25-Dec 14:08	91	105	0	3	154	0/0	0/0/0/0/0/0

Back to Undo Statistics 
Back to Top

Latch Statistics

• Latch Activity
• Latch Sleep Breakdown
• Latch Miss Sources
• Parent Latch Statistics
• Child Latch Statistics

Back to Top

Latch Activity

• "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests
• "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
• "Pct Misses" for both should be very close to 0.0

Latch Name	Get Requests	Pct Get Miss	Avg Slps /Miss	Wait Time (s)	NoWait Requests	Pct NoWait Miss
ASM db client latch	11,883	0.00		0	0
AWR Alerted Metric Element list	18,252	0.00		0	0
Consistent RBA	5,508	0.02	0.00	0	0
FOB s.o list latch	731	0.00		0	0
JS broadcast add buf latch	6,193	0.00		0	0
JS broadcast drop buf latch	6,194	0.00		0	0
JS broadcast load blnc latch	6,057	0.00		0	0
JS mem alloc latch	8	0.00		0	0
JS queue access latch	8	0.00		0	0
JS queue state obj latch	218,086	0.00		0	0
JS slv state obj latch	31	0.00		0	0
KCL gc element parent latch	2,803,392	0.04	0.01	0	108	0.00
KJC message pool free list	43,168	0.06	0.00	0	14,532	0.01
KJCT flow control latch	563,875	0.00	0.00	0	0
KMG MMAN ready and startup request latch	1,576	0.00		0	0
KSXR large replies	320	0.00		0	0
KTF sga latch	23	0.00		0	1,534	0.00
KWQMN job cache list latch	352	0.00		0	0
KWQP Prop Status	5	0.00		0	0
MQL Tracking Latch	0			0	94	0.00
Memory Management Latch	0			0	1,576	0.00
OS process	207	0.00		0	0
OS process allocation	1,717	0.00		0	0
OS process: request allocation	73	0.00		0	0
PL/SQL warning settings	226	0.00		0	0
SGA IO buffer pool latch	20,679	0.06	0.00	0	20,869	0.00
SQL memory manager latch	7	0.00		0	1,575	0.00
SQL memory manager workarea list latch	439,442	0.00		0	0
Shared B-Tree	182	0.00		0	0
Undo Hint Latch	0			0	12	0.00
active checkpoint queue latch	7,835	0.00		0	0
active service list	50,936	0.00		0	1,621	0.00
archive control	5	0.00		0	0
begin backup scn array	72,901	0.00	0.00	0	0
business card	32	0.00		0	0
cache buffer handles	331,153	0.02	0.00	0	0
cache buffers chains	48,189,073	0.00	0.00	0	1,201,379	0.00
cache buffers lru chain	891,796	0.34	0.00	0	991,605	0.23
cache table scan latch	0			0	10,309	0.01
channel handle pool latch	99	0.00		0	0
channel operations parent latch	490,324	0.01	0.00	0	0
checkpoint queue latch	671,856	0.01	0.00	0	555,469	0.02
client/application info	335	0.00		0	0
commit callback allocation	12	0.00		0	0
compile environment latch	173,428	0.00		0	0
dml lock allocation	243,087	0.00	0.00	0	0
dummy allocation	134	0.00		0	0
enqueue hash chains	1,539,499	0.01	0.03	0	263	0.00
enqueues	855,207	0.02	0.00	0	0
error message lists	64	0.00		0	0
event group latch	38	0.00		0	0
file cache latch	4,694	0.00		0	0
gcs drop object freelist	8,451	0.19	0.00	0	0
gcs opaque info freelist	38,584	0.00	0.00	0	0
gcs partitioned table hash	9,801,867	0.00		0	0
gcs remaster request queue	31	0.00		0	0
gcs remastering latch	1,014,198	0.00	0.33	0	0
gcs resource freelist	1,154,551	0.03	0.00	0	771,650	0.00
gcs resource hash	3,815,373	0.02	0.00	0	2	0.00
gcs resource scan list	4	0.00		0	0
gcs shadows freelist	795,482	0.00	0.00	0	779,648	0.00
ges caches resource lists	209,655	0.02	0.00	0	121,613	0.01
ges deadlock list	840	0.00		0	0
ges domain table	366,702	0.00		0	0
ges enqueue table freelist	487,875	0.00		0	0
ges group table	543,887	0.00		0	0
ges process hash list	59,503	0.00		0	0
ges process parent latch	908,232	0.00		0	1	0.00
ges process table freelist	73	0.00		0	0
ges resource hash list	862,590	0.02	0.28	0	72,266	0.01
ges resource scan list	534	0.00		0	0
ges resource table freelist	135,406	0.00	0.00	0	0
ges synchronous data	160	0.63	0.00	0	2,954	0.07
ges timeout list	3,256	0.00		0	4,478	0.00
global KZLD latch for mem in SGA	21	0.00		0	0
hash table column usage latch	59	0.00		0	1,279	0.00
hash table modification latch	116	0.00		0	0
job workq parent latch	0			0	14	0.00
job_queue_processes parameter latch	86	0.00		0	0
kks stats	384	0.00		0	0
ksuosstats global area	329	0.00		0	0
ktm global data	296	0.00		0	0
kwqbsn:qsga	182	0.00		0	0
lgwr LWN SCN	6,547	0.18	0.00	0	0
library cache	235,060	0.00	0.00	0	22	0.00
library cache load lock	486	0.00		0	0
library cache lock	49,284	0.00		0	0
library cache lock allocation	566	0.00		0	0
library cache pin	27,863	0.00	0.00	0	0
library cache pin allocation	204	0.00		0	0
list of block allocation	10,101	0.00		0	0
loader state object freelist	108	0.00		0	0
longop free list parent	6	0.00		0	6	0.00
message pool operations parent latch	1,424	0.00		0	0
messages	222,581	0.00	0.00	0	0
mostly latch-free SCN	6,649	1.43	0.00	0	0
multiblock read objects	29,230	0.03	0.00	0	0
name-service memory objects	18,842	0.00		0	0
name-service namespace bucket	56,712	0.00		0	0
name-service namespace objects	15	0.00		0	0
name-service pending queue	6,436	0.00		0	0
name-service request	44	0.00		0	0
name-service request queue	57,312	0.00		0	0
ncodef allocation latch	77	0.00		0	0
object queue header heap	37,721	0.00		0	7,457	0.00
object queue header operation	2,706,992	0.06	0.00	0	0
object stats modification	22	0.00		0	0
parallel query alloc buffer	939	0.00		0	0
parallel query stats	72	0.00		0	0
parallel txn reco latch	630	0.00		0	0
parameter list	193	0.00		0	0
parameter table allocation management	68	0.00		0	0
post/wait queue	4,205	0.00		0	2,712	0.00
process allocation	46,895	0.00		0	38	0.00
process group creation	73	0.00		0	0
process queue	175	0.00		0	0
process queue reference	2,621	0.00		0	240	62.50
qmn task queue latch	668	0.15	1.00	0	0
query server freelists	159	0.00		0	0
query server process	8	0.00		0	7	0.00
queued dump request	23,628	0.00		0	0
redo allocation	21,206	0.57	0.00	0	4,706,826	0.02
redo copy	0			0	4,707,106	0.01
redo writing	29,944	0.01	0.00	0	0
resmgr group change latch	69	0.00		0	0
resmgr:actses active list	137	0.00		0	0
resmgr:actses change group	52	0.00		0	0
resmgr:free threads list	130	0.00		0	0
resmgr:schema config	7	0.00		0	0
row cache objects	1,644,149	0.00	0.00	0	321	0.00
rules engine rule set statistics	500	0.00		0	0
sequence cache	360	0.00		0	0
session allocation	535,514	0.00	0.00	0	0
session idle bit	3,262,141	0.00	0.00	0	0
session state list latch	166	0.00		0	0
session switching	77	0.00		0	0
session timer	1,620	0.00		0	0
shared pool	60,359	0.00	0.00	0	0
shared pool sim alloc	13	0.00		0	0
shared pool simulator	4,246	0.00		0	0
simulator hash latch	1,862,803	0.00		0	0
simulator lru latch	1,719,480	0.01	0.00	0	46,053	0.00
slave class	2	0.00		0	0
slave class create	8	12.50	1.00	0	0
sort extent pool	1,284	0.00		0	0
state object free list	4	0.00		0	0
statistics aggregation	280	0.00		0	0
temp lob duration state obj allocation	2	0.00		0	0
threshold alerts latch	202	0.00		0	0
transaction allocation	211	0.00		0	0
transaction branch allocation	77	0.00		0	0
undo global data	779,759	0.07	0.00	0	0
user lock	102	0.00		0	0

Back to Latch Statistics 
Back to Top

Latch Sleep Breakdown

• ordered by misses desc

Latch Name	Get Requests	Misses	Sleeps	Spin Gets	Sleep1	Sleep2	Sleep3
cache buffers lru chain	891,796	3,061	1	3,060	0	0	0
object queue header operation	2,706,992	1,755	3	1,752	0	0	0
KCL gc element parent latch	2,803,392	1,186	11	1,176	0	0	0
cache buffers chains	48,189,073	496	1	495	0	0	0
ges resource hash list	862,590	160	44	116	0	0	0
enqueue hash chains	1,539,499	79	2	78	0	0	0
gcs remastering latch	1,014,198	3	1	2	0	0	0
qmn task queue latch	668	1	1	0	0	0	0
slave class create	8	1	1	0	0	0	0

Back to Latch Statistics 
Back to Top

Latch Miss Sources

• only latches with sleeps are shown
• ordered by name, sleeps desc

Latch Name	Where	NoWait Misses	Sleeps	Waiter Sleeps
KCL gc element parent latch	kclrwrite	0	8	0
KCL gc element parent latch	kclnfndnewm	0	4	6
KCL gc element parent latch	KCLUNLNK	0	1	1
KCL gc element parent latch	kclbla	0	1	0
KCL gc element parent latch	kclulb	0	1	1
KCL gc element parent latch	kclzcl	0	1	0
cache buffers chains	kcbnew: new latch again	0	2	0
cache buffers chains	kclwrt	0	1	0
cache buffers lru chain	kcbzgws	0	1	0
enqueue hash chains	ksqcmi: if lk mode not requested	0	2	0
event range base latch	No latch	0	1	1
gcs remastering latch	69	0	1	0
ges resource hash list	kjlmfnd: search for lockp by rename and inst id	0	23	0
ges resource hash list	kjakcai: search for resp by resname	0	13	0
ges resource hash list	kjrmas1: lookup master node	0	5	0
ges resource hash list	kjlrlr: remove lock from resource queue	0	2	33
ges resource hash list	kjcvscn: remove from scan queue	0	1	0
object queue header operation	kcbo_switch_q_bg	0	3	0
object queue header operation	kcbo_switch_mq_bg	0	2	4
object queue header operation	kcbw_unlink_q	0	2	0
object queue header operation	kcbw_link_q	0	1	0
slave class create	ksvcreate	0	1	0

Back to Latch Statistics 
Back to Top

Parent Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics 
Back to Top

Child Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics 
Back to Top

Segment Statistics

• Segments by Logical Reads
• Segments by Physical Reads
• Segments by Row Lock Waits
• Segments by ITL Waits
• Segments by Buffer Busy Waits
• Segments by Global Cache Buffer Busy
• Segments by CR Blocks Received
• Segments by Current Blocks Received

Back to Top

Segments by Logical Reads

• Total Logical Reads: 16,648,792
• Captured Segments account for 85.2% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Logical Reads	%Total
ICCI01	ICCIDAT01	ICCICCS_PK		INDEX	1,544,848	9.28
ICCI01	ICCIDAT01	CUSCAD_TMP		TABLE	1,349,536	8.11
ICCI01	ICCIDAT01	ICCIFNSACT_PK		INDEX	1,268,400	7.62
ICCI01	ICCIDAT01	IND_OLDNEWACT		INDEX	1,071,072	6.43
ICCI01	ICCIDAT01	CUID_PK		INDEX	935,584	5.62

Back to Segment Statistics 
Back to Top

Segments by Physical Reads

• Total Physical Reads: 322,678
• Captured Segments account for 64.2% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Physical Reads	%Total
ICCI01	ICCIDAT01	CUID_TMP		TABLE	116,417	36.08
ICCI01	ICCIDAT01	CUMI_TMP		TABLE	44,086	13.66
ICCI01	ICCIDAT01	CUSM_TMP		TABLE	26,078	8.08
ICCI01	ICCIDAT01	CUSVAA_TMP_PK		INDEX	19,554	6.06
ICCI01	ICCIDAT01	CUID		TABLE	259	0.08

Back to Segment Statistics 
Back to Top

Segments by Row Lock Waits

当一个进程予在正被其它进程锁住的数据行上获得排它锁时发生这种等待。这种等待经常是由于在一个有主键索引的表上做大量INSERT操作。

No data exists for this section of the report.

Back to Segment Statistics 
Back to Top

Segments by ITL Waits

No data exists for this section of the report.

Back to Segment Statistics 
Back to Top

Segments by Buffer Busy Waits

No data exists for this section of the report.

Back to Segment Statistics 
Back to Top

Segments by Global Cache Buffer Busy

• % of Capture shows % of GC Buffer Busy for each top segment compared
• with GC Buffer Busy for all segments captured by the Snapshot

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	GC Buffer Busy	% of Capture
SYS	SYSTEM	TSQ$		TABLE	2	100.00

Back to Segment Statistics 
Back to Top

Segments by CR Blocks Received

• Total CR Blocks Received: 4,142
• Captured Segments account for 95.6% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	CR Blocks Received	%Total
SYS	SYSTEM	USER$		TABLE	1,001	24.17
SYS	SYSTEM	TSQ$		TABLE	722	17.43
SYS	SYSTEM	SEG$		TABLE	446	10.77
SYS	SYSTEM	OBJ$		TABLE	264	6.37
SYS	SYSTEM	I_OBJ2		INDEX	174	4.20

Back to Segment Statistics 
Back to Top

Segments by Current Blocks Received

• Total Current Blocks Received: 15,502
• Captured Segments account for 84.8% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Current Blocks Received	%Total
ICCI01	ICCIDAT01	CUSM_TMP		TABLE	5,764	37.18
ICCI01	ICCIDAT01	CUMI_TMP		TABLE	2,794	18.02
ICCI01	ICCIDAT01	CUID_TMP		TABLE	2,585	16.68
SYS	SYSTEM	SEG$		TABLE	361	2.33
SYS	SYSTEM	TSQ$		TABLE	361	2.33

Back to Segment Statistics 
Back to Top

Dictionary Cache Statistics

• Dictionary Cache Stats
• Dictionary Cache Stats (RAC)

Back to Top

Dictionary Cache Stats

• "Pct Misses" should be very low (< 2% in most cases)
• "Final Usage" is the number of cache entries being used

Cache	Get Requests	Pct Miss	Scan Reqs	Pct Miss	Mod Reqs	Final Usage
dc_awr_control	86	0.00	0		4	1
dc_constraints	59	91.53	0		20	1,350
dc_files	23	0.00	0		0	23
dc_global_oids	406	0.00	0		0	35
dc_histogram_data	673	0.15	0		0	1,555
dc_histogram_defs	472	24.36	0		0	4,296
dc_object_grants	58	0.00	0		0	154
dc_object_ids	1,974	6.13	0		0	1,199
dc_objects	955	19.58	0		56	2,064
dc_profiles	30	0.00	0		0	1
dc_rollback_segments	3,358	0.00	0		0	37
dc_segments	2,770	2.56	0		1,579	1,312
dc_sequences	9	33.33	0		9	5
dc_table_scns	6	100.00	0		0	0
dc_tablespace_quotas	1,558	28.50	0		1,554	3
dc_tablespaces	346,651	0.00	0		0	7
dc_usernames	434	0.00	0		0	14
dc_users	175,585	0.00	0		0	43
outstanding_alerts	57	71.93	0		0	1

Back to Dictionary Cache Statistics 
Back to Top

Dictionary Cache Stats (RAC)

Cache	GES Requests	GES Conflicts	GES Releases
dc_awr_control	8	0	0
dc_constraints	88	22	0
dc_histogram_defs	115	0	0
dc_object_ids	143	101	0
dc_objects	253	111	0
dc_segments	3,228	49	0
dc_sequences	17	3	0
dc_table_scns	6	0	0
dc_tablespace_quotas	3,093	441	0
dc_users	8	1	0
outstanding_alerts	113	41	0

Back to Dictionary Cache Statistics 
Back to Top

Library Cache Statistics

• Library Cache Activity
• Library Cache Activity (RAC)

Back to Top

Library Cache Activity

• "Pct Misses" should be very low

Namespace	Get Requests	Pct Miss	Pin Requests	Pct Miss	Reloads	Invali- dations
BODY	105	0.00	247	0.00	0	0
CLUSTER	3	0.00	4	0.00	0	0
INDEX	13	46.15	26	42.31	5	0
SQL AREA	56	100.00	1,857,002	0.02	32	12
TABLE/PROCEDURE	179	35.75	3,477	8.02	63	0
TRIGGER	323	0.00	386	0.00	0	0

Back to Library Cache Statistics 
Back to Top

Library Cache Activity (RAC)

Namespace	GES Lock Requests	GES Pin Requests	GES Pin Releases	GES Inval Requests	GES Invali- dations
BODY	5	0	0	0	0
CLUSTER	4	0	0	0	0
INDEX	26	22	6	17	0
TABLE/PROCEDURE	1,949	285	63	244	0

Back to Library Cache Statistics 
Back to Top

Memory Statistics

• Process Memory Summary
• SGA Memory Summary
• SGA breakdown difference

Back to Top

Process Memory Summary

• B: Begin snap E: End snap
• All rows below contain absolute values (i.e. not diffed over the interval)
• Max Alloc is Maximum PGA Allocation size at snapshot time
• Hist Max Alloc is the Historical Max Allocation for still-connected processes
• ordered by Begin/End snapshot, Alloc (MB) desc

	Category	Alloc (MB)	Used (MB)	Avg Alloc (MB)	Std Dev Alloc (MB)	Max Alloc (MB)	Hist Max Alloc (MB)	Num Proc	Num Alloc
B	Other	136.42		5.25	8.55	24	27	26	26
	Freeable	13.50	0.00	1.50	1.11	3		9	9
	SQL	0.33	0.16	0.03	0.03	0	2	12	10
	PL/SQL	0.12	0.06	0.01	0.01	0	0	24	24
E	Other	138.65		4.78	8.20	24	27	29	29
	Freeable	14.94	0.00	1.36	1.04	3		11	11
	SQL	0.39	0.19	0.03	0.03	0	2	15	12
	PL/SQL	0.18	0.11	0.01	0.01	0	0	27	26

Back to Memory Statistics 
Back to Top

SGA Memory Summary

SGA regions	Begin Size (Bytes)	End Size (Bytes) (if different)
Database Buffers	3,506,438,144
Fixed Size	2,078,368
Redo Buffers	14,696,448
Variable Size	771,754,336

Back to Memory Statistics 
Back to Top

SGA breakdown difference

• ordered by Pool, Name
• N/A value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot

Pool	Name	Begin MB	End MB	% Diff
java	free memory	16.00	16.00	0.00
large	PX msg pool	1.03	1.03	0.00
large	free memory	14.97	14.97	0.00
shared	ASH buffers	15.50	15.50	0.00
shared	CCursor	8.58	8.85	3.09
shared	KQR L PO	8.75	8.80	0.55
shared	db_block_hash_buckets	22.50	22.50	0.00
shared	free memory	371.80	369.61	-0.59
shared	gcs resources	66.11	66.11	0.00
shared	gcs shadows	41.65	41.65	0.00
shared	ges big msg buffers	13.75	13.75	0.00
shared	ges enqueues	7.44	7.56	1.63
shared	ges reserved msg buffers	7.86	7.86	0.00
shared	library cache	10.78	10.93	1.41
shared	row cache	7.16	7.16	0.00
shared	sql area	27.49	28.50	3.67
	buffer_cache	3,344.00	3,344.00	0.00
	fixed_sga	1.98	1.98	0.00
	log_buffer	14.02	14.02	0.00

Back to Memory Statistics 
Back to Top

Streams Statistics

• Streams CPU/IO Usage
• Streams Capture
• Streams Apply
• Buffered Queues
• Buffered Subscribers
• Rule Set

Back to Top

Streams CPU/IO Usage

No data exists for this section of the report.

Back to Streams Statistics 
Back to Top

Streams Capture

No data exists for this section of the report.

Back to Streams Statistics 
Back to Top

Streams Apply

No data exists for this section of the report.

Back to Streams Statistics 
Back to Top

Buffered Queues

No data exists for this section of the report.

Back to Streams Statistics 
Back to Top

Buffered Subscribers

No data exists for this section of the report.

Back to Streams Statistics 
Back to Top

Rule Set

No data exists for this section of the report.

Back to Streams Statistics 
Back to Top

Resource Limit Stats

• only rows with Current or Maximum Utilization > 80% of Limit are shown
• ordered by resource name

Resource Name	Current Utilization	Maximum Utilization	Initial Allocation	Limit
gcs_resources	349,392	446,903	450063	450063
gcs_shadows	400,300	447,369	450063	450063

Back to Top

init.ora Parameters

Parameter Name	Begin value	End value (if different)
audit_file_dest	/oracle/app/oracle/admin/ICCI/adump
background_dump_dest	/oracle/app/oracle/admin/ICCI/bdump
cluster_database	TRUE
cluster_database_instances	2
compatible	10.2.0.3.0
control_files	/dev/rora_CTL01, /dev/rora_CTL02, /dev/rora_CTL03
core_dump_dest	/oracle/app/oracle/admin/ICCI/cdump
db_block_size	8192
db_domain
db_file_multiblock_read_count	16
db_name	ICCI
dispatchers	(PROTOCOL=TCP) (SERVICE=ICCIXDB)
instance_number	1
job_queue_processes	10
open_cursors	800
pga_aggregate_target	1073741824
processes	500
remote_listener	LISTENERS_ICCI
remote_login_passwordfile	EXCLUSIVE
sga_max_size	4294967296
sga_target	4294967296
sort_area_size	196608
spfile	/dev/rora_SPFILE
thread	1
undo_management	AUTO
undo_retention	900
undo_tablespace	UNDOTBS1
user_dump_dest	/oracle/app/oracle/admin/ICCI/udump

Back to Top

More RAC Statistics

• Global Enqueue Statistics
• Global CR Served Stats
• Global CURRENT Served Stats
• Global Cache Transfer Stats

Back to Top

 

Global Enqueue Statistics

Statistic	Total	per Second	per Trans
acks for commit broadcast(actual)	18,537	3.92	3.31
acks for commit broadcast(logical)	21,016	4.45	3.75
broadcast msgs on commit(actual)	5,193	1.10	0.93
broadcast msgs on commit(logical)	5,491	1.16	0.98
broadcast msgs on commit(wasted)	450	0.10	0.08
dynamically allocated gcs resources	0	0.00	0.00
dynamically allocated gcs shadows	0	0.00	0.00
false posts waiting for scn acks	0	0.00	0.00
flow control messages received	0	0.00	0.00
flow control messages sent	2	0.00	0.00
gcs assume cvt	0	0.00	0.00
gcs assume no cvt	9,675	2.05	1.73
gcs ast xid	1	0.00	0.00
gcs blocked converts	7,099	1.50	1.27
gcs blocked cr converts	8,442	1.79	1.51
gcs compatible basts	45	0.01	0.01
gcs compatible cr basts (global)	273	0.06	0.05
gcs compatible cr basts (local)	12,593	2.66	2.25
gcs cr basts to PIs	0	0.00	0.00
gcs cr serve without current lock	0	0.00	0.00
gcs dbwr flush pi msgs	223	0.05	0.04
gcs dbwr write request msgs	223	0.05	0.04
gcs error msgs	0	0.00	0.00
gcs forward cr to pinged instance	0	0.00	0.00
gcs immediate (compatible) converts	2,998	0.63	0.54
gcs immediate (null) converts	170,925	36.16	30.53
gcs immediate cr (compatible) converts	0	0.00	0.00
gcs immediate cr (null) converts	722,748	152.88	129.11
gcs indirect ast	306,817	64.90	54.81
gcs lms flush pi msgs	0	0.00	0.00
gcs lms write request msgs	189	0.04	0.03
gcs msgs process time(ms)	16,164	3.42	2.89
gcs msgs received	1,792,132	379.09	320.14
gcs out-of-order msgs	0	0.00	0.00
gcs pings refused	0	0.00	0.00
gcs pkey conflicts retry	0	0.00	0.00
gcs queued converts	2	0.00	0.00
gcs recovery claim msgs	0	0.00	0.00
gcs refuse xid	0	0.00	0.00
gcs regular cr	0	0.00	0.00
gcs retry convert request	0	0.00	0.00
gcs side channel msgs actual	437	0.09	0.08
gcs side channel msgs logical	21,086	4.46	3.77
gcs stale cr	3,300	0.70	0.59
gcs undo cr	5	0.00	0.00
gcs write notification msgs	23	0.00	0.00
gcs writes refused	3	0.00	0.00
ges msgs process time(ms)	1,289	0.27	0.23
ges msgs received	138,891	29.38	24.81
global posts dropped	0	0.00	0.00
global posts queue time	0	0.00	0.00
global posts queued	0	0.00	0.00
global posts requested	0	0.00	0.00
global posts sent	0	0.00	0.00
implicit batch messages received	81,181	17.17	14.50
implicit batch messages sent	19,561	4.14	3.49
lmd msg send time(ms)	0	0.00	0.00
lms(s) msg send time(ms)	0	0.00	0.00
messages flow controlled	15,306	3.24	2.73
messages queue sent actual	108,411	22.93	19.37
messages queue sent logical	222,518	47.07	39.75
messages received actual	474,202	100.31	84.71
messages received logical	1,931,144	408.50	344.97
messages sent directly	25,742	5.45	4.60
messages sent indirectly	137,725	29.13	24.60
messages sent not implicit batched	88,859	18.80	15.87
messages sent pbatched	1,050,224	222.16	187.61
msgs causing lmd to send msgs	61,682	13.05	11.02
msgs causing lms(s) to send msgs	85,978	18.19	15.36
msgs received queue time (ms)	911,013	192.71	162.74
msgs received queued	1,931,121	408.50	344.97
msgs sent queue time (ms)	5,651	1.20	1.01
msgs sent queue time on ksxp (ms)	66,767	14.12	11.93
msgs sent queued	215,124	45.51	38.43
msgs sent queued on ksxp	243,729	51.56	43.54
process batch messages received	120,003	25.38	21.44
process batch messages sent	181,019	38.29	32.34

Back to Top

Global CR Served Stats

Statistic	Total
CR Block Requests	10,422
CURRENT Block Requests	251
Data Block Requests	10,422
Undo Block Requests	2
TX Block Requests	20
Current Results	10,664
Private results	4
Zero Results	5
Disk Read Results	0
Fail Results	0
Fairness Down Converts	1,474
Fairness Clears	0
Free GC Elements	0
Flushes	370
Flushes Queued	0
Flush Queue Full	0
Flush Max Time (us)	0
Light Works	2
Errors	0

Back to Top

Global CURRENT Served Stats

• Pins = CURRENT Block Pin Operations
• Flushes = Redo Flush before CURRENT Block Served Operations
• Writes = CURRENT Block Fusion Write Operations

Statistic	Total	% <1ms	% <10ms	% <100ms	% <1s	% <10s
Pins	17,534	99.96	0.01	0.03	0.00	0.00
Flushes	77	48.05	46.75	5.19	0.00	0.00
Writes	255	5.49	53.73	40.00	0.78	0.00

Back to Top

Global Cache Transfer Stats

• Immediate (Immed) - Block Transfer NOT impacted by Remote Processing Delays
• Busy (Busy) - Block Transfer impacted by Remote Contention
• Congested (Congst) - Block Transfer impacted by Remote System Load
• ordered by CR + Current Blocks Received desc

		CR				Current
Inst No	Block Class	Blocks Received	% Immed	% Busy	% Congst	Blocks Received	% Immed	% Busy	% Congst
2	data block	3,945	87.20	12.80	0.00	13,324	99.71	0.26	0.04
2	Others	191	100.00	0.00	0.00	2,190	96.48	3.52	0.00
2	undo header	11	100.00	0.00	0.00	2	100.00	0.00	0.00

Back to Top

End of Report