| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: reduce wait times
statsrep :
STATSPACK report for
DB Name DB Id Instance Inst Num Release OPS Host ---------- ----------- ---------- -------- ---------- ---- ---------- ORACLE 1353180294 orcl 1 8.1.6.0.0 NO SRVLC2000
Snap Length
Start Id End Id Start Time End Time (Minutes)
-------- -------- -------------------- -------------------- -----------
57 67 12-Feb-04 09:16:13 13-Feb-04 09:13:48 1,437.58
Cache Sizes
db_block_buffers: 60000
db_block_size: 4096
log_buffer: 524288
shared_pool_size: 131072000
Load Profile
Per Second Per Transaction
--------------- ---------------
Redo size: 22,452.79 271,889.06
Logical reads: 1,538.52 18,630.55
Block changes: 169.26 2,049.64
Physical reads: 46.39 561.74
Physical writes: 10.17 123.12
User calls: 26.25 317.84
Parses: 7.40 89.65
Hard parses: 0.94 11.34
Sorts: 3.15 38.19
Transactions: 0.08
Rows per Sort: 62.59
Pct Blocks changed / Read: 11.00
Recursive Call Pct: 79.63
Rollback / transaction Pct: 7.61
Instance Efficiency Percentages (Target 100%)
Buffer Nowait Ratio: 100.00
Buffer Hit Ratio: 96.98
Library Hit Ratio: 91.18
Redo NoWait Ratio: 100.00
In-memory Sort Ratio: 99.98
Soft Parse Ratio: 87.35
Latch Hit Ratio: 99.99
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait %
Total
Event Waits Time (cs) Wt
Time
-------------------------------------------- ------------ ------------ -----
--
db file sequential read 278,792 144,794
22.61
db file parallel write 5,523 133,218
20.81
direct path read 151,858 93,521
14.61
db file scattered read 147,833 72,720
11.36
log file parallel write 27,277 62,366
9.74
-------------------------------------------------------------
Wait Events for DB: ORACLE Instance: orcl Snaps: 57 - 67
->cs - centisecond - 100th of a second
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (cs) (ms)
/txn
---------------------------- ------------ ---------- ----------- ----- -----
-
db file sequential read 278,792 0 144,794 5
39.1
db file parallel write 5,523 0 133,218 241
0.8
direct path read 151,858 0 93,521 6
21.3
db file scattered read 147,833 0 72,720 5
20.8
log file parallel write 27,277 0 62,366 23
3.8
SQL*Net more data to client 1,635,284 0 41,457 0
229.6
direct path write 26,321 0 31,958 12
3.7
control file parallel write 28,312 0 24,456 9
4.0
log buffer space 1,392 49 13,969 100
0.2
file open 2,062 0 7,147 35
0.3
log file switch completion 62 23 4,242 684
0.0
log file sync 5,979 0 3,685 6
0.8
enqueue 23 1 2,012 875
0.0
LGWR wait for redo copy 352 0 1,126 32
0.0
control file sequential read 1,180 0 765 6
0.2
latch free 1,637 1,317 399 2
0.2
library cache load lock 118 0 339 29
0.0
log file single write 76 0 263 35
0.0
local write wait 68 0 180 26
0.0
sort segment request 1 1 103 1030
0.0
file identify 76 0 79 10
0.0
log file sequential read 38 0 51 13
0.0
buffer busy waits 23 0 8 3
0.0
refresh controlfile command 15 0 5 3
0.0
SQL*Net break/reset to clien 280 0 2 0
0.0
row cache lock 1 0 0 0
0.0
SQL*Net message from client 2,268,904 0 ########### 562
318.5
SQL*Net more data from clien 10,040 0 581 1
1.4
SQL*Net message to client 2,268,902 0 231 0
318.5
-------------------------------------------------------------
Background Wait Events for DB: ORACLE Instance: orcl Snaps: 57 -
67
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (cs) (ms)
/txn
---------------------------- ------------ ---------- ----------- ----- -----
-
db file parallel write 5,523 0 133,218 241
0.8
log file parallel write 27,277 0 62,366 23
3.8
control file parallel write 28,254 0 24,380 9
4.0
file open 1,520 0 6,571 43
0.2
db file scattered read 6,856 0 2,916 4
1.0
enqueue 23 1 2,012 875
0.0
LGWR wait for redo copy 352 0 1,126 32
0.0
db file sequential read 4,047 0 930 2
0.6
direct path read 456 0 708 16
0.1
control file sequential read 749 0 639 9
0.1
rdbms ipc reply 426 0 342 8
0.1
log file single write 76 0 263 35
0.0
latch free 223 219 89 4
0.0
file identify 76 0 79 10
0.0
direct path write 456 0 61 1
0.1
log file sequential read 38 0 51 13
0.0
rdbms ipc message 169,185 111,326 42,980,131 2540
23.8
pmon timer 28,085 28,022 8,625,646 3071
3.9
smon timer 281 279 8,608,476 #####
0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: ORACLE Instance: orcl Snaps: 57 - 67
Gets % of
Buffer Gets Executes per Exec Total Hash Value
-------------- ------------ ------------ ------ ------------
SQL statement
--
259,128 30,815 8.4 .2 1828225713
SELECT INIEXT,SEXT,MINEXT,MAXEXT,PCTINC,BLOCKS,LISTS,GROUPS,EXTENTS,PCACHE,
T
254,727 3 84,909.0 .2 1257338992
SELECT CAXX_DOCTESTCA.DAT_STP_DEFINITIVA , CAXX_DOCTESTCA.DAT_REGNE ,
CAXX_DOC
113,275 12,670 8.9 .1 114078687
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1
72,243 1,662 43.5 .1 528396678
SELECT TRXX_RAGGR_VARIANTI.LIVELLO FROM
MGXX_ARTTIPOVAR,TRXX_RAGGR_VARIANTI
61,621 13 4,740.1 .0 384623479
SELECT ACXM_ANAGRA.CDA_ANA , ACXM_ANAGRA.DES_RAGSOC,
58,868 1 58,868.0 .0 3627382655
SELECT ACXM_ANAGRA.CDA_ANA , ACXM_ANAGRA.DES_RAGSOC,
54,565 10,913 5.0 .0 1645188330
SELECT SYSDATE FROM DUAL
22,620 472 47.9 .0 1895426909
SELECT A.CDA_CONTO CDA_CONTO FROM CGXX_CONTI A WHERE (A.CDA_CONTO =
(SUBSTR
13,847 71 195.0 .0 4275497931
SELECT DAT_REGNE , PRG_REGNE FROM CGXX_PNTESTATE WHERE DAT_REGNE_COLL =null
AN
11,458 4 2,864.5 .0 3280803004
SELECT lvxx_assolist.cda_listino , lvxx_assolist.prg_assolist ,
9,641 104 92.7 .0 1954667797
SELECT SYS.USER_OBJECTS.OBJECT_NAME , SYS.USER_OBJECTS.OBJECT_TYPE FROM
SYS.US
6,531 44 148.4 .0 3883037416
SELECT /*+ USE_HASH(ACXM_ANAGRA) */
"TRXX_DOCTEST_VUOTI"."QTA_V01_PRE",
4,210 7,704 0.5 .0 1966425544
select text from view$ where rowid=:1
4,197 1,416 3.0 .0 86364777
SELECT 1 UNO FROM CGXX_SALDIPDCPE A WHERE (A.NUM_ESER_CONT = :b1 ) AND
(A.C
3,932 1,314 3.0 .0 3853490000
UPDATE CGXX_SALDIPDCPE SET VAL_TOT_DA_VE=VAL_TOT_DA_VE + :b1
,VAL_TOT_DA_VL=VA
2,743 701 3.9 .0 1007439963
UPDATE MGXX_DOCTEST SET QTA_TOT=DECODE(NVL(QTA_TOT,0) + :b1 ,0, NULL
,NVL(QTA_
2,562 701 3.7 .0 433396950
UPDATE MGXX_DOCPARTITA SET QTA_TOT=DECODE(NVL(QTA_TOT,0) + :b1 ,0, NULL
,NVL(Q
-------------------------------------------------------------
SQL ordered by Reads for DB: ORACLE Instance: orcl Snaps: 57 -
67
Physical Reads % of
Reads Executes per Exec Total Hash Value
-------------- ------------ ------------ ------ ------------
SQL statement
----------------------------------------------------------------------------
--
10,800 71 152.1 .3 4275497931
SELECT DAT_REGNE , PRG_REGNE FROM CGXX_PNTESTATE WHERE DAT_REGNE_COLL =null
AN
2,503 44 56.9 .1 3883037416
SELECT /*+ USE_HASH(ACXM_ANAGRA) */
"TRXX_DOCTEST_VUOTI"."QTA_V01_PRE",
1,936 12,670 0.2 .0 114078687
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1
747 3 249.0 .0 1257338992
SELECT CAXX_DOCTESTCA.DAT_STP_DEFINITIVA , CAXX_DOCTESTCA.DAT_REGNE ,
CAXX_DOC
518 30,815 0.0 .0 1828225713
SELECT INIEXT,SEXT,MINEXT,MAXEXT,PCTINC,BLOCKS,LISTS,GROUPS,EXTENTS,PCACHE,
T
364 104 3.5 .0 1954667797
SELECT SYS.USER_OBJECTS.OBJECT_NAME , SYS.USER_OBJECTS.OBJECT_TYPE FROM
SYS.US
161 13 12.4 .0 384623479
SELECT ACXM_ANAGRA.CDA_ANA , ACXM_ANAGRA.DES_RAGSOC,
108 110 1.0 .0 2550318580
INSERT INTO CGXX_SALDICLIPE (
NUM_ESER_CONT,NUM_ANNO,NUM_MESE,DAT_SAL,CDN_AREA
62 472 0.1 .0 1895426909
SELECT A.CDA_CONTO CDA_CONTO FROM CGXX_CONTI A WHERE (A.CDA_CONTO =
(SUBSTR
45 104 0.4 .0 4076387228
SELECT COUNT ( *) FROM FWPR_PROGRAMMI
43 1,416 0.0 .0 86364777
SELECT 1 UNO FROM CGXX_SALDIPDCPE A WHERE (A.NUM_ESER_CONT = :b1 ) AND
(A.C
41 94 0.4 .0 3959523306
SELECT 1 UNO FROM CGXX_SALDICLIPE A WHERE (A.NUM_ESER_CONT = :b1 ) AND
(A.D
34 102 0.3 .0 1956456513
INSERT INTO CGXX_SALDIPDCPE (
NUM_ESER_CONT,NUM_ANNO,NUM_MESE,DAT_SAL,CDN_AREA
33 1,059 0.0 .0 2477513264
SELECT 1 UNO FROM CGIX_PROGCLIRIG A WHERE (A.CDN_ATT = :b1 ) AND
(A.NUM_ANN
32 196 0.2 .0 295120972
SELECT CDA_UTE_APPL FROM TSUM_UTENTEAPPL WHERE CDN_UTE_APPL = :b1
26 1,031 0.0 .0 3847842894
SELECT 1 UNO FROM CGIX_PROGCLITST A WHERE (A.CDN_ATT = :b1 ) AND
(A.NUM_ANN
19 4 4.8 .0 3280803004
SELECT lvxx_assolist.cda_listino , lvxx_assolist.prg_assolist ,
-------------------------------------------------------------
SQL ordered by Rows for DB: ORACLE Instance: orcl Snaps: 57 - 67
Rows Rows
Processed Executes per Exec Hash Value
-------------- ------------ ------------ ------------
SQL statement
----------------------------------------------------------------------------
--
161,939 44 3,680.4 3883037416
SELECT /*+ USE_HASH(ACXM_ANAGRA) */
"TRXX_DOCTEST_VUOTI"."QTA_V01_PRE",
42,677 12,670 3.4 114078687
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1
30,814 30,815 1.0 1828225713
SELECT INIEXT,SEXT,MINEXT,MAXEXT,PCTINC,BLOCKS,LISTS,GROUPS,EXTENTS,PCACHE,
T
10,913 10,913 1.0 1645188330
SELECT SYSDATE FROM DUAL
2,698 1 2,698.0 3627382655
SELECT ACXM_ANAGRA.CDA_ANA , ACXM_ANAGRA.DES_RAGSOC,
2,697 13 207.5 384623479
SELECT ACXM_ANAGRA.CDA_ANA , ACXM_ANAGRA.DES_RAGSOC,
2,110 7,704 0.3 1966425544
select text from view$ where rowid=:1
1,024 1,416 0.7 86364777
SELECT 1 UNO FROM CGXX_SALDIPDCPE A WHERE (A.NUM_ESER_CONT = :b1 ) AND
(A.C
1,024 1,314 0.8 3853490000
UPDATE CGXX_SALDIPDCPE SET VAL_TOT_DA_VE=VAL_TOT_DA_VE + :b1
,VAL_TOT_DA_VL=VA
1,023 472 2.2 1895426909
SELECT A.CDA_CONTO CDA_CONTO FROM CGXX_CONTI A WHERE (A.CDA_CONTO =
(SUBSTR
862 868 1.0 975567780
SELECT 1 FROM CGXX_DATEBILANC WHERE NUM_ESER_CONT =2004
615 701 0.9 433396950
UPDATE MGXX_DOCPARTITA SET QTA_TOT=DECODE(NVL(QTA_TOT,0) + :b1 ,0, NULL
,NVL(Q
477 701 0.7 1007439963
UPDATE MGXX_DOCTEST SET QTA_TOT=DECODE(NVL(QTA_TOT,0) + :b1 ,0, NULL
,NVL(QTA_
341 472 0.7 3980177971
SELECT A.IND_TIPO_CONTO FROM TP_CONTI A WHERE (A.CDA_CONTO = :b1 )
340 1,662 0.2 528396678
SELECT TRXX_RAGGR_VARIANTI.LIVELLO FROM
MGXX_ARTTIPOVAR,TRXX_RAGGR_VARIANTI
193 193 1.0 912765457
SELECT A.CDN_ATT CDN_ATT FROM CGIX_REGISTRI A WHERE (A.IND_TIPO_REG =
:b1 )
189 518 0.4 3013728279
select privilege#,level from sysauth$ connect by grantee#=prior privilege#
and
-------------------------------------------------------------
Instance Activity Stats for DB: ORACLE Instance: orcl Snaps: 57 -
6
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
CPU used by this session 285,513 3.3 40.1
CPU used when call started 235,785 2.7 33.1
CR blocks created 26,502 0.3 3.7
DBWR buffers scanned 49,814 0.6 7.0
DBWR checkpoint buffers written 418,830 4.9 58.8
DBWR checkpoints 38 0.0 0.0
DBWR free buffers found 46,755 0.5 6.6
DBWR lru scans 578 0.0 0.1
DBWR make free requests 592 0.0 0.1
DBWR revisited being-written buff 0 0.0 0.0
DBWR summed scan depth 49,814 0.6 7.0
DBWR transaction table writes 299 0.0 0.0
DBWR undo block writes 227,884 2.6 32.0
SQL*Net roundtrips to/from client 2,266,907 26.3 318.3
background checkpoints completed 38 0.0 0.0
background checkpoints started 38 0.0 0.0
background timeouts 113,220 1.3 15.9
branch node splits 30 0.0 0.0
buffer is not pinned count 77,268,261 895.8 10,847.7
buffer is pinned count 24,400,557 282.9 3,425.6
bytes received via SQL*Net from c 262,392,001 3,042.1 36,837.3
bytes sent via SQL*Net to client 3,591,757,980 41,641.2 504,247.9
calls to get snapshot scn: kcmgss 886,945 10.3 124.5
calls to kcmgas 23,937 0.3 3.4
calls to kcmgcs 60,899 0.7 8.6
change write time 19,835 0.2 2.8
cleanouts and rollbacks - consist 206 0.0 0.0
cleanouts only - consistent read 25,286 0.3 3.6
cluster key scan block gets 12,137,535 140.7 1,704.0
cluster key scans 2,095,339 24.3 294.2
commit cleanout failures: block l 4,491 0.1 0.6
commit cleanout failures: buffer 1 0.0 0.0
commit cleanout failures: callbac 14 0.0 0.0
commit cleanouts 113,949 1.3 16.0
commit cleanouts successfully com 109,443 1.3 15.4
consistent changes 51,850 0.6 7.3
consistent gets 116,288,813 1,348.2 16,325.8
current blocks converted for CR
cursor authentications 15,972 0.2 2.2
data blocks consistent reads - un 51,832 0.6 7.3
db block changes 14,599,611 169.3 2,049.6
db block gets 16,416,572 190.3 2,304.7
deferred (CURRENT) block cleanout 60,713 0.7 8.5
dirty buffers inspected 798 0.0 0.1
enqueue conversions 3,574 0.0 0.5
enqueue releases 174,292 2.0 24.5
enqueue requests 174,320 2.0 24.5
enqueue timeouts 27 0.0 0.0
enqueue waits 3 0.0 0.0
execute count 1,064,811 12.3 149.5
free buffer inspected 805 0.0 0.1
free buffer requested 3,873,563 44.9 543.8
hot buffers moved to head of LRU 240,755 2.8 33.8
immediate (CR) block cleanout app 25,492 0.3 3.6
immediate (CURRENT) block cleanou 82,631 1.0 11.6
index fast full scans (full) 53 0.0 0.0
Instance Activity Stats for DB: ORACLE Instance: orcl Snaps: 57 -
6
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
leaf node splits 7,701 0.1 1.1
logons cumulative 3,481 0.0 0.5
messages received 43,838 0.5 6.2
messages sent 43,838 0.5 6.2
no buffer to keep pinned count 32,939,949 381.9 4,624.5
no work - consistent read gets 38,181,369 442.7 5,360.3
opened cursors cumulative 538,616 6.2 75.6
parse count (hard) 80,761 0.9 11.3
parse count (total) 638,557 7.4 89.7
parse time cpu 48,669 0.6 6.8
parse time elapsed 59,890 0.7 8.4
physical reads 4,001,287 46.4 561.7
physical reads direct 435,944 5.1 61.2
physical writes 876,986 10.2 123.1
physical writes direct 453,958 5.3 63.7
physical writes non checkpoint 806,497 9.4 113.2
pinned buffers inspected 0 0.0 0.0
prefetched blocks 3,138,717 36.4 440.7
prefetched blocks aged out before 62 0.0 0.0
process last non-idle time 13,410,156,634 155,471.1 1,882,655.7
recovery array read time 0 0.0 0.0
recovery array reads 0 0.0 0.0
recovery blocks read 0 0.0 0.0
recursive calls 8,849,109 102.6 1,242.3
recursive cpu usage 74,532 0.9 10.5
redo blocks written 3,921,054 45.5 550.5
redo buffer allocation retries 1,431 0.0 0.2
redo entries 7,219,116 83.7 1,013.5
redo log space requests 62 0.0 0.0
redo log space wait time 4,242 0.1 0.6
redo ordering marks 37 0.0 0.0
redo size 1,936,665,752 22,452.8 271,889.1
redo synch time 3,691 0.0 0.5
redo synch writes 5,956 0.1 0.8
redo wastage 7,486,424 86.8 1,051.0
redo write time 110,293 1.3 15.5
redo writer latching time 1,126 0.0 0.2
redo writes 27,277 0.3 3.8
rollback changes - undo records a 838 0.0 0.1
rollbacks only - consistent read 26,586 0.3 3.7
rows fetched via callback 15,780,689 183.0 2,215.5
session connect time 13,410,156,634 155,471.1 1,882,655.7
session cursor cache count 172 0.0 0.0
session cursor cache hits 383,952 4.5 53.9
session logical reads 132,705,385 1,538.5 18,630.6
session pga memory 1,926,742,748 22,337.8 270,496.0
session pga memory max 1,930,031,248 22,375.9 270,957.6
session uga memory 17,895,416 207.5 2,512.3
session uga memory max 1,072,384,832 12,432.7 150,552.4
sorts (disk) 50 0.0 0.0
sorts (memory) 271,977 3.2 38.2
sorts (rows) 17,026,787 197.4 2,390.4
summed dirty queue length 318 0.0 0.0
switch current to new buffer
table fetch by rowid 37,295,253 432.4 5,235.9
table fetch continued row 333,300 3.9 46.8
Instance Activity Stats for DB: ORACLE Instance: orcl Snaps: 57 -
6
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
table scan blocks gotten 5,708,705 66.2 801.5
table scan rows gotten 121,829,748 1,412.4 17,103.7
table scans (long tables) 671 0.0 0.1
table scans (short tables) 95,202 1.1 13.4
total file opens 2,062 0.0 0.3
transaction rollbacks 10 0.0 0.0
user calls 2,263,983 26.3 317.8
user commits 6,581 0.1 0.9
user rollbacks 542 0.0 0.1
write clones created in backgroun 68 0.0 0.0
write clones created in foregroun 829 0.0 0.1
-------------------------------------------------------------
Tablespace IO Summary for DB: ORACLE Instance: orcl Snaps: 57 -
67
Avg Read Total Avg
Wait
Tablespace Reads (ms) Writes Waits (ms)
------------------------- ----------- -------- ----------- ---------- ------
--
USER_DATA 261,083 2.4 1,351 0
0.0
TEMPORARY_DATA 168,598 0.0 82,275 0
0.0
ROLLBACK 134 12.8 222,760 0
0.0
INDICI_BI 56,018 13.7 107,043 0
0.0
BUSINESSINTELLIGENCE 43,904 12.9 88,037 0
0.0
SYSTEM 42,162 3.5 1,237 20
3.0
INDICI4 20,472 2.8 2,910 3
6.7
TEMP 4,115 0.0 1,465 0
0.0
INDICI_VARI 2,893 3.8 282 0
0.0
TOOLS 286 5.1 424 0
0.0
-------------------------------------------------------------
File IO Statistics for DB: ORACLE Instance: orcl Snaps: 57 - 67
Tablespace Filename
------------------------ ---------------------------------------------------
-
Reads Avg Blks Rd Avg Rd (ms) Writes Tot Waits Avg Wait
(ms)
-------------- ----------- ----------- -------------- ---------- -----------
--
BUSINESSINTELLIGENCE D:\BUSINESS
43,904 3.6 12.9 88,037 0
INDICI4 D:\INDICI4.DBF
20,472 1.0 2.8 2,910 3
6.7
INDICI_BI F:\INDICI_BI
56,018 1.0 13.7 107,043 0
INDICI_VARI F:\INDICI_VARI
2,893 1.1 3.8 282 0
ROLLBACK D:\ROLLBACK1.ORA
134 1.0 12.8 222,760 0
SYSTEM D:\SYSTEM2.DBF
38 1.0 0.0 38 0
SYSTEM F:\ORACLE\ORADATA\ORACLE\SYSTEM01.DBF
42,124 2.9 3.5 1,199 20
3.0
TEMP D:\TEMP.DBF
4,115 3.4 0.0 1,465 0
TEMPORARY_DATA D:\TEMP01.DBF
116,241 2.5 0.0 55,362 0
TEMPORARY_DATA E:\TEMP01.DBF
52,357 2.5 0.0 26,913 0
TOOLS F:\ORACLE\ORADATA\ORACLE\TOOLS01.DBF
286 1.1 5.1 424 0
USER_DATA E:\USERS01.DBF
261,083 12.3 2.4 1,351 0
-------------------------------------------------------------
Buffer wait Statistics for DB: ORACLE Instance: orcl Snaps: 57 -
67
Tot Wait Avg
Class Waits Time (cs) Time (cs)
------------------ ----------- ---------- ---------
data block 23 8 0
-------------------------------------------------------------
Enqueue activity for DB: ORACLE Instance: orcl Snaps: 57 - 67
Enqueue Gets Waits
---------- ------------ ----------
CF 208 3
-------------------------------------------------------------
Rollback Segment Stats for DB: ORACLE Instance: orcl Snaps: 57 -
67
->A high value for "Pct Waits" suggests more rollback segments may be
required
Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks Extends
------ ------------ ------- --------------- -------- -------- --------
0 286.0 0.00 0 0 0 0
1 10,911.0 0.00 19,417,102 7 0 0
2 67,785.0 0.00 147,950,922 53 2 37
3 17,533.0 0.00 64,180,200 21 0 5
4 46,443.0 0.00 166,146,240 55 0 39
5 7,968.0 0.00 27,174,170 9 0 0
6 70,198.0 0.00 270,991,672 87 7 65
7 5,148.0 0.00 16,081,846 5 0 0
8 4,699.0 0.00 5,841,406 2 0 0
9 1,657.0 0.00 697,978 0 0 0
10 1,363.0 0.00 1,331,584 0 0 0
11 7,974.0 0.00 4,294,612 2 0 0
12 33,279.0 0.00 123,917,432 40 4 24
13 1,303.0 0.00 1,085,310 0 1 0
14 11,706.0 0.00 14,394,292 5 4 0
15 2,619.0 0.00 1,756,088 1 0 0
16 1,383.0 0.00 620,154 0 0 0
17 1,597.0 0.00 1,766,262 1 0 0
18 2,143.0 0.00 2,385,480 1 0 0
19 2,111.0 0.00 2,000,588 0 0 0
20 6,938.0 0.00 924,946 1 0 0
21 1,329.0 0.00 889,780 0 0 0
22 3,940.0 0.00 1,235,206 0 3 0
23 1,352.0 0.00 639,522 0 0 0
24 1,317.0 0.00 584,814 0 0 0
25 3,458.0 0.00 4,772,286 2 0 0
-------------------------------------------------------------
Rollback Segment Storage for DB: ORACLE Instance: orcl Snaps: 57 -
->The value of Optimal should be larger than Avg Active
RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- ----------- --------------- ---------------
0 4,886,528 0 4,886,528
1 53,612,544 7,417,942 51,200,000 53,612,544
2 170,307,584 ########### 51,200,000 170,307,584
3 69,382,144 59,191,038 51,200,000 258,617,344
4 176,615,424 ########### 51,200,000 176,615,424
5 53,612,544 8,596,090 51,200,000 53,612,544
6 53,612,544 ########### 51,200,000 258,617,344
7 53,612,544 4,331,778 51,200,000 53,612,544
8 53,612,544 914,267 51,200,000 53,612,544
9 53,612,544 315,392 51,200,000 53,612,544
10 53,612,544 0 51,200,000 53,612,544
11 53,612,544 5,154,149 51,200,000 53,612,544
12 129,306,624 ########### 51,200,000 154,537,984
13 53,612,544 40,952,875 51,200,000 69,382,144
14 53,612,544 88,603,115 51,200,000 176,615,424
15 53,612,544 10,212,725 51,200,000 53,612,544
16 53,612,544 7,268,749 51,200,000 53,612,544
17 53,612,544 4,046,379 51,200,000 53,612,544
18 22,073,344 315,392 51,200,000 22,073,344
19 53,612,544 0 51,200,000 53,612,544
20 53,612,544 314,982 51,200,000 53,612,544
21 53,612,544 1,169,771 51,200,000 53,612,544
22 53,612,544 98,191,037 51,200,000 129,306,624
23 53,612,544 0 51,200,000 53,612,544
24 53,612,544 0 51,200,000 53,612,544
25 53,612,544 599,244 51,200,000 53,612,544
-------------------------------------------------------------
Latch Activity for DB: ORACLE Instance: orcl Snaps: 57 - 67
->"Pct Misses" should be very close to 0.0
Pct Avg
Pct
Get Get Sleeps Nowait
Nowait
Latch Name Requests Miss /Miss Requests
Miss
------------------------------- ------------ ------ ------ ----------- -----
-
Active checkpoint queue latch 63,519 0.2 0.0 0
Checkpoint queue latch 1,680,418 0.0 0.4 0
Token Manager 1,490 0.0 21,239
0.0
begin backup scn array 693 0.0 0
cache buffer handles 1,004,021 0.0 0
cache buffers chains 231,169,324 0.0 0.0 7,137,351
0.0
cache buffers lru chain 4,503,294 0.0 0.0 3,836,015
0.0
channel handle pool latch 673 0.0 675
0.0
channel operations parent latch 1,346 0.0 675
0.0
dml lock allocation 51,374 0.0 0
dropped object history latch 87 0.0 0
enqueue hash chains 352,207 0.0 0
enqueues 640,708 0.0 0
event group latch 673 0.0 0
job_queue_processes parameter l 1,373 0.0 0
ktm global data 286 0.0 0
latch wait list 498 0.2 0.0 494
0.0
library cache 8,194,087 0.0 0.4 357,488
0.0
library cache load lock 95,892 0.0 0
list of block allocation 53,493 0.0 0
loader state object freelist 482 0.0 0
longop free list 243 0.0 0
messages 425,203 0.6 0.0 0
multiblock read objects 407,264 0.0 2
0.0
ncodef allocation latch 1,373 0.0 0
process allocation 673 0.0 673
0.0
process group creation 1,348 0.0 0
redo allocation 7,300,809 0.0 0.0 0
redo writing 202,299 0.0 0.1 0
row cache objects 12,071,201 0.0 0.5 21,223
0.1
sequence cache 4,638 0.0 0
session allocation 161,557 0.0 0
session idle bit 4,584,544 0.0 0.0 0
session switching 1,373 0.0 0
shared pool 5,444,481 0.0 0.1 0
sort extent pool 85,082 0.0 0
temporary table state object al 4 0.0 0
transaction allocation 73,379 0.0 0
transaction branch allocation 1,373 0.0 0
undo global data 285,174 0.0 0
user lock 16,528 0.0 0
-------------------------------------------------------------
Latch Sleep breakdown for DB: ORACLE Instance: orcl Snaps: 57 -
67
Get Spin &
Latch Name Requests Misses Sleeps Sleeps 1-4
---------------------------- ------------ ----------- ----------- ----------
--
cache buffers chains 231,169,324 6,945 25
6920/25/0/0/
0
messages 425,203 2,339 3
2336/3/0/0/0
row cache objects 12,071,201 1,642 752
1107/319/215
/1/0
library cache 8,194,087 1,426 634
1031/206/142
/47/0
redo allocation 7,300,809 1,345 6
1339/6/0/0/0
shared pool 5,444,481 1,132 138
1048/43/28/1
3/0
Checkpoint queue latch 1,680,418 202 73
144/43/15/0/
0
redo writing 202,299 54 4 51/2/1/0/0
cache buffers lru chain 4,503,294 48 2 47/0/1/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: ORACLE Instance: orcl Snaps: 57 - 67
No Wait
Latch Name Where Misses
Sleeps
------------------------------ -------------------------- ------- ----------
-
Checkpoint queue latch kcbk0rrd: update recovery 0
66
Checkpoint queue latch kcbbwthc: thread checkpoin 0
7
cache buffers chains kcbgtcr: kslbegin 0
22
cache buffers chains kcbchg: kslbegin: call CR 0
1
cache buffers chains kcbget: pin buffer 0
1
cache buffers chains kcbrls: kslbegin 0
1
cache buffers lru chain kcbbiop: lru scan 0
2
library cache kglhdgn: child: 0
334
library cache kglpnal: child: alloc spac 0
73
library cache kglpnal: child: before pro 0
64
library cache kglhdgc: child: 0
23
library cache kglobpn: child: 0
18
library cache kglati 0
5
library cache kgllkdl: child: cleanup 0
3
library cache kgllkdl: child: free pin 0
2
library cache kglnti 0
1
library cache kglpnp: child 0
1
library cache kglupc: child 0
1
messages ksarcv 0
3
redo allocation kcrfwr: redo allocation 0
5
redo allocation kcrfwi: before write 0
1
redo writing kcrfsr 0
3
redo writing kcrfwcr 0
1
row cache objects kqrpre: find obj 0
735
row cache objects kqrpsc: incr stat 0
4
row cache objects kqreqd 0
3
row cache objects kqreqd: rel enqueue 0
2
shared pool kghfrunp: parent clatch: w 0
104
shared pool kghfrunp: alloc: clatch no 0
75
shared pool kghfrunp: alloc: wait 0
37
shared pool kghalo 0
21
shared pool kghfrunp: clatch: wait 0
8
shared pool kghfrunp: clatch: nowait 0
5
shared pool kghalp 0
3
shared pool kghfnd: get next extent 0
2
-------------------------------------------------------------
Buffer Pool Sets for DB: ORACLE Instance: orcl Snaps: 57 - 67
Free Write
Buffer
Set Buffer Consistent Physical Physical Buffer Complete
Busy
Id Gets Gets Reads Writes Waits Waits
Waits
--- ----------- ----------- ----------- ----------- -------- -------- ------
--
3 3,873,568 46,247,910 3,565,329 423,028 0 0
23
-------------------------------------------------------------
Dictionary Cache Stats for DB: ORACLE Instance: orcl Snaps: 57 -
67
->"Pct Misses" should be very low (< 2% in most cases)
->"Cache Usage" is the number of cache entries being used
->"Pct SGA" is the ratio of usage to allocated size for that cache
Get Pct Scan Pct Mod Final
Pct
Cache Requests Miss Requests Miss Req Usage
SGA
---------------------- ----------- ------ -------- ----- -------- ------ ---
-
dc_constraints 126 33.3 0 126 1
33
dc_database_links 0 0 0 0
0
dc_files 0 0 0 0
0
dc_free_extents 1,616,908 1.8 2,442 0.0 28,034 ######
100
dc_global_oids 0 0 0 0
0
dc_histogram_data 0 0 0 0
0
dc_histogram_data_valu 0 0 0 0
0
dc_histogram_defs 29,343 0.3 35 0.0 0 78
93
dc_object_ids 737,147 1.6 0 53 ######
100
dc_objects 231,490 3.7 0 286 8,672
100
dc_outlines 0 0 0 0
0
dc_profiles 673 0.0 0 0 1
14
dc_rollback_segments 20,336 0.0 0 0 37
95
dc_segments 736,612 2.7 0 10,991 ######
100
dc_sequence_grants 0 0 0 0
0
dc_sequences 1,891 0.3 0 1,305 17
94
dc_synonyms 22,103 0.3 0 0 233
100
dc_tablespace_quotas 9,988 0.1 0 9,970 11
33
dc_tablespaces 8,087 0.0 0 0 15
94
dc_used_extents 15,413 74.3 0 11,855 1,705
30
dc_user_grants 47,174 0.0 0 0 24
51
dc_usernames 166,641 0.0 0 0 20
95
dc_users 127,661 0.0 0 0 31
70
-------------------------------------------------------------
Library Cache Activity for DB: ORACLE Instance: orcl Snaps: 57 -
67
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ----------- ------ ----------- ------ ----------- --------
BODY 3,447 0.6 3,447 0.6 2 0
CLUSTER 1,652 0.8 1,703 1.5 0 0
INDEX 75 60.0 75 60.0 0 0
OBJECT 0 0 0 0
PIPE 0 0 0 0
SQL AREA 255,057 28.3 1,419,349 10.8 7,978 104
TABLE/PROCEDURE 441,264 2.7 841,983 5.7 6,219 0
TRIGGER 14,885 0.7 14,885 1.3 90 0
-------------------------------------------------------------
SGA Memory Summary for DB: ORACLE Instance: orcl
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 245,760,000
Fixed Size 70,924
Redo Buffers 532,480
Variable Size 192,397,312
----------------
sum 438,760,716
-------------------------------------------------------------
SGA breakdown difference for DB: ORACLE Instance: orcl Snaps: 57 -
Name Begin value End value Difference
----------------------------------- ------------ ------------ ------------
Checkpoint queue 295,056 295,056 0
DML locks 58,464 58,464 0
KGFF heap 9,812 9,812 0
KGK heap 17,556 17,556 0
KQLS heap 26,793,848 4,435,392 -22,358,456
PL/SQL DIANA 502,596 326,520 -176,076
PL/SQL MPCODE 1,860,720 203,264 -1,657,456
PLS non-lib hp 2,096 2,096 0
SYSTEM PARAMETERS 61,468 61,468 0
State objects 179,624 179,624 0
db_block_buffers 253,920,000 253,920,000 0
db_block_hash_buckets 1,091,160 1,091,160 0
db_files 370,988 370,988 0
dictionary cache 23,217,828 19,641,636 -3,576,192
distributed_transactions- 72,152 72,152 0
enqueue_resources 111,456 111,456 0
event statistics per sess 386,400 386,400 0
file # translation table 65,572 65,572 0
fixed allocation callback 640 640 0
fixed_sga 70,924 70,924 0
free memory 62,723,956 64,431,064 1,707,108
ktlbk state objects 53,928 53,928 0
library cache 34,253,504 21,304,100 -12,949,404
log_buffer 524,288 524,288 0
long op statistics array 50,600 50,600 0
message pool freequeue 231,152 231,152 0
miscellaneous 1,241,112 1,295,860 54,748
processes 80,000 80,000 0
sessions 245,180 245,180 0
sql area 29,871,200 68,916,268 39,045,068
table columns 22,164 18,288 -3,876
table definiti 9,160 640 -8,520
transaction_branches 147,200 147,200 0
transactions 112,392 112,392 0
trigger inform 784 120 -664
-------------------------------------------------------------
init.ora Parameters for DB: ORACLE Instance: orcl Snaps: 57 - 67
End value
Parameter Name Begin value (if
different)
----------------------------- --------------------------------- ------------
--
always_anti_join HASH
always_semi_join hash
background_dump_dest f:\Oracle\admin\oracle\bdump
compatible 8.1.6
control_files f:\Oracle\oradata\oracle\control0
cursor_space_for_time TRUE
db_block_buffers 60000
db_block_lru_latches 4
db_block_size 4096
db_file_multiblock_read_count 64
db_files 1024
db_name oracle
db_writer_processes 2
distributed_transactions 200
global_names TRUE
hash_area_size 131072000
hash_multiblock_io_count 8
instance_name ORCL
java_pool_size 33554432
job_queue_processes 1
large_pool_size 15728640
log_buffer 524288
log_checkpoint_interval 100000
log_checkpoint_timeout 7200
max_dump_file_size 10240
max_enabled_roles 30
open_cursors 2000
optimizer_mode RULE
oracle_trace_collection_name
os_authent_prefix
parallel_max_servers 5
parallel_threads_per_cpu 2
processes 100
query_rewrite_enabled TRUE
remote_login_passwordfile EXCLUSIVE
service_names oracle
session_cached_cursors 1000
shared_pool_size 131072000
sort_area_retained_size 524288
sort_area_size 4194304
sort_multiblock_read_count 8
timed_statistics TRUE
user_dump_dest f:\Oracle\admin\oracle\udump
-------------------------------------------------------------
End of Report
"Ron" <support_at_dbainfopower.com> ha scritto nel messaggio
news:8KydnTxRweplKbbdRVn-hw_at_comcast.com...
>
> Hello gp,
>
> Usually you should not worry about those.
>
> SQL*NET is a time that takes to get message from oracle client - unless
> you have serious network issues or application is very badly designed you
ca
> ignore it.
>
> rdbms ipc - idle event, ignore it.
>
> If you have application performance issues and would like group to
help -
> please post full statspack report.
>
> Regards,
>
> Ron
> DBA Infopower
> http://www.dbainfopower.com
> Standard disclaimer:
> http://www.dbainfopower.com/dbaip_advice_disclaimer.html
>
>
>
> "gp" <gieppetto_at_tiscali.it> wrote in message
> news:c0g9od$1ht$1_at_fata.cs.interbusiness.it...
> > my i reduce this?
> >
> > EVENTO TOTAL_WAITS AVERAGE_WAIT
>
> ------------------------------ ----------- ------------------------------
> > SQL*Net message from client 2793623 56.039294
> > rdbms ipc message 226139 270.80838
> >
> > are there a problem for database performances?
> >
> > thanks.
> >
> >
>
>
Received on Fri Feb 13 2004 - 02:53:45 CST
 |
 |