| 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
report from a small period :
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)
-------- -------- -------------------- -------------------- -----------
77 78 16-Feb-04 15:23:59 16-Feb-04 16:24:09 60.17
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: 1,649.48 2,625.50
Logical reads: 5,123.92 8,155.79
Block changes: 10.52 16.75
Physical reads: 307.17 488.93
Physical writes: 10.90 17.35
User calls: 82.59 131.46
Parses: 20.92 33.31
Hard parses: 3.98 6.34
Sorts: 4.23 6.73
Transactions: 0.63
Rows per Sort: 67.91
Pct Blocks changed / Read: 0.21
Recursive Call Pct: 63.20
Rollback / transaction Pct: 3.44
Instance Efficiency Percentages (Target 100%)
Buffer Nowait Ratio: 100.00
Buffer Hit Ratio: 94.01
Library Hit Ratio: 87.59
Redo NoWait Ratio: 100.00
In-memory Sort Ratio: 99.94
Soft Parse Ratio: 80.97
Latch Hit Ratio: 100.00
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait %
Total
Event Waits Time (cs) Wt
Time
-------------------------------------------- ------------ ------------ -----
--
db file sequential read 102,587 19,678
45.66
db file scattered read 89,625 16,815
39.02
log file parallel write 2,325 1,315
3.05
log file sync 2,266 1,288
2.99
direct path read 7,342 904
2.10
-------------------------------------------------------------
Wait Events for DB: ORACLE Instance: orcl Snaps: 77 - 78
->cs - centisecond - 100th of a second
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (cs) (ms)
/txn
---------------------------- ------------ ---------- ----------- ----- -----
-
db file sequential read 102,587 0 19,678 2
45.2
db file scattered read 89,625 0 16,815 2
39.5
log file parallel write 2,325 0 1,315 6
1.0
log file sync 2,266 0 1,288 6
1.0
direct path read 7,342 0 904 1
3.2
direct path write 1,967 0 854 4
0.9
control file parallel write 1,190 0 814 7
0.5
SQL*Net more data to client 68,531 0 600 0
30.2
db file parallel write 167 0 484 29
0.1
file open 151 0 122 8
0.1
db file parallel read 391 0 112 3
0.2
latch free 316 244 64 2
0.1
control file sequential read 167 0 21 1
0.1
SQL*Net break/reset to clien 41 0 1 0
0.0
refresh controlfile command 5 0 1 2
0.0
SQL*Net message from client 298,044 0 12,802,283 430
131.4
SQL*Net more data from clien 1,750 0 108 1
0.8
SQL*Net message to client 298,047 0 43 0
131.4
-------------------------------------------------------------
Background Wait Events for DB: ORACLE Instance: orcl Snaps: 77 -
78
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (cs) (ms)
/txn
---------------------------- ------------ ---------- ----------- ----- -----
-
log file parallel write 2,325 0 1,315 6
1.0
control file parallel write 1,170 0 791 7
0.5
db file parallel write 167 0 484 29
0.1
db file scattered read 138 0 53 4
0.1
db file sequential read 187 0 24 1
0.1
latch free 4 4 2 5
0.0
control file sequential read 15 0 1 1
0.0
rdbms ipc message 10,395 4,678 1,811,184 1742
4.6
pmon timer 1,180 1,173 361,078 3060
0.5
smon timer 11 11 337,923 #####
0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: ORACLE Instance: orcl Snaps: 77 - 78
Gets % of
Buffer Gets Executes per Exec Total Hash Value
-------------- ------------ ------------ ------ ------------
SQL statement
--
176,616 3 58,872.0 1.0 3627382655
SELECT ACXM_ANAGRA.CDA_ANA , ACXM_ANAGRA.DES_RAGSOC,
172,632 1 172,632.0 .9 2276598688
SELECT V_SALD.CDA_CONTO , V_SALD.CDA_CLI_FOR ,
V_SALD.D
94,803 1 94,803.0 .5 4029615244
SELECT MGXX_DOCTEST.CDA_POLO_NUM,
MGXX_DOCTEST.CDA_TIPO_DOC_NUM,
92,692 17,834 5.2 .5 2085632044
select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
90,881 1 90,881.0 .5 911428239
SELECT COUNT ( *) FROM CGXX_PNTESTATE , CGXX_PNTSTIVA WHERE
CGXX_PNTSTIVA.NUM_
66,495 32,207 2.1 .4 2091761008
select condition from cdef$ where rowid=:1
54,162 4 13,540.5 .3 1780808824
SELECT DISTINCT LVXX_SCAGLRLIS.VAL_PREZZO , TRXX_ART_VAR.CDA_VARIANTE ,
TRXX_A
54,162 4 13,540.5 .3 2394440935
SELECT DISTINCT LVXX_SCAGLRLIS.VAL_PREZZO , TRXX_ART_VAR.CDA_VARIANTE ,
TRXX_A
54,162 4 13,540.5 .3 2749387899
SELECT DISTINCT LVXX_SCAGLRLIS.VAL_PREZZO , TRXX_ART_VAR.CDA_VARIANTE ,
TRXX_A
54,162 4 13,540.5 .3 2028032800
SELECT DISTINCT LVXX_SCAGLRLIS.VAL_PREZZO , TRXX_ART_VAR.CDA_VARIANTE ,
TRXX_A
36,222 1 36,222.0 .2 1913771185
select round(sum(bytes)/1024/1024, 2), sum(extents), sum(bytes) from
21,829 967 22.6 .1 1536916657
select
con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
18,054 1 18,054.0 .1 345563966
SELECT DISTINCT LVXX_SCAGLRLIS.VAL_PREZZO , TRXX_ART_VAR.CDA_VARIANTE ,
TRXX_A
18,054 1 18,054.0 .1 1179905738
SELECT DISTINCT LVXX_SCAGLRLIS.VAL_PREZZO , TRXX_ART_VAR.CDA_VARIANTE ,
TRXX_A
18,054 1 18,054.0 .1 3865337864
SELECT DISTINCT LVXX_SCAGLRLIS.VAL_PREZZO , TRXX_ART_VAR.CDA_VARIANTE ,
TRXX_A
18,054 1 18,054.0 .1 3796597771
SELECT DISTINCT LVXX_SCAGLRLIS.VAL_PREZZO , TRXX_ART_VAR.CDA_VARIANTE ,
TRXX_A
18,054 1 18,054.0 .1 738326387
SELECT DISTINCT LVXX_SCAGLRLIS.VAL_PREZZO , TRXX_ART_VAR.CDA_VARIANTE ,
TRXX_A
-------------------------------------------------------------
SQL ordered by Reads for DB: ORACLE Instance: orcl Snaps: 77 -
78
Physical Reads % of
Reads Executes per Exec Total Hash Value
-------------- ------------ ------------ ------ ------------
SQL statement
----------------------------------------------------------------------------
--
6,855 1 6,855.0 .6 1913771185
select round(sum(bytes)/1024/1024, 2), sum(extents), sum(bytes) from
6,691 8 836.4 .6 3883037416
SELECT /*+ USE_HASH(ACXM_ANAGRA) */
"TRXX_DOCTEST_VUOTI"."QTA_V01_PRE",
5,034 6 839.0 .5 4275497931
SELECT DAT_REGNE , PRG_REGNE FROM CGXX_PNTESTATE WHERE DAT_REGNE_COLL =null
AN
2,725 1 2,725.0 .2 1445060368
SELECT AGXX_PROVVTRAN.IND_TIPO_DOC_PROVV , MGXX_DOCTEST.DAT_DOC
,
2,104 1 2,104.0 .2 801146736
update AGXX_PROVVTRAN SET IND_STATO_MOV ='GC' , DAT_MOD =TO_DATE('16-02-2004
0
1,787 1 1,787.0 .2 4027429493
SELECT CGXX_PNTESTTRAN.PRG_MOV ,
CGXX_PNTESTTRAN.IND_PROVE_REGNE
1,599 1 1,599.0 .1 2276598688
SELECT V_SALD.CDA_CONTO , V_SALD.CDA_CLI_FOR ,
V_SALD.D
1,342 1 1,342.0 .1 911428239
SELECT COUNT ( *) FROM CGXX_PNTESTATE , CGXX_PNTSTIVA WHERE
CGXX_PNTSTIVA.NUM_
405 994 0.4 .0 199702406
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,
i.property,i.p
398 1 398.0 .0 4029615244
SELECT MGXX_DOCTEST.CDA_POLO_NUM,
MGXX_DOCTEST.CDA_TIPO_DOC_NUM,
293 914 0.3 .0 114078687
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1
210 680 0.3 .0 2216582187
select
ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0),audit$
159 17,834 0.0 .0 2085632044
select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
116 19 6.1 .0 1895426909
SELECT A.CDA_CONTO CDA_CONTO FROM CGXX_CONTI A WHERE (A.CDA_CONTO =
(SUBSTR
71 128 0.6 .0 3111103299
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$
wh
66 1 66.0 .0 2106907845
SELECT TEST_FV_V1.CDA_ANA,
TEST_FV_V1.ANAGRA_DESTINATARIO_DES_R
55 1,103 0.0 .0 395844583
select
name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(
-------------------------------------------------------------
SQL ordered by Rows for DB: ORACLE Instance: orcl Snaps: 77 - 78
Rows Rows
Processed Executes per Exec Hash Value
-------------- ------------ ------------ ------------
SQL statement
----------------------------------------------------------------------------
--
432,345 8 54,043.1 3883037416
SELECT /*+ USE_HASH(ACXM_ANAGRA) */
"TRXX_DOCTEST_VUOTI"."QTA_V01_PRE",
32,207 32,207 1.0 2091761008
select condition from cdef$ where rowid=:1
19,595 17,834 1.1 2085632044
select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
17,834 967 18.4 1536916657
select
con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
17,766 1,103 16.1 395844583
select
name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(
8,097 3 2,699.0 3627382655
SELECT ACXM_ANAGRA.CDA_ANA , ACXM_ANAGRA.DES_RAGSOC,
3,970 914 4.3 114078687
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1
2,925 1,553 1.9 4195740643
select pos#,intcol#,col#,spare1 from icol$ where obj#=:1
2,896 724 4.0 1829459778
SELECT agxx_param_det.CDA_TIPO_PARAM_AGE ,
agxx_tipo_param.CDA_TI
2,890 2,890 1.0 1645188330
SELECT SYSDATE FROM DUAL
1,759 44 40.0 3068398092
SELECT "SCAMBI"."MGXX_RIGAUBLOMA"."CDA_LOTTO_SOGG"
,"SCAMBI"."MGXX_RIGAUBLOMA"
1,553 994 1.6 199702406
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,
i.property,i.p
1,448 724 2.0 2544553836
SELECT agxx_param_det.CDA_TIPO_PARAM_AGE ,
agxx_tipo_param.CDA_TI
1,410 1 1,410.0 2276598688
SELECT V_SALD.CDA_CONTO , V_SALD.CDA_CLI_FOR ,
V_SALD.D
1,404 1,404 1.0 4108375872
select rowid, PRG_ULTIMO FROM ACXX_PROGRESSIVI WHERE CDA_TABELLA =
'AGXX_MOV
1,400 1,400 1.0 491820568
SELECT AGXX_BASEIMP.CDA_BASEIMP , AGXX_BASEIMP.IND_TIPO_CALC ,
1,400 1,400 1.0 2930257105
SELECT IND_TIPO_CALC , DEV_FORMULA FROM AGXX_BASEIMP WHERE CDA_BASEIMP
='NETTO
-------------------------------------------------------------
Instance Activity Stats for DB: ORACLE Instance: orcl Snaps: 77 -
7
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
CPU used by this session 84,122 23.3 37.1
CPU used when call started 78,458 21.7 34.6
CR blocks created 317 0.1 0.1
DBWR buffers scanned 39,802 11.0 17.6
DBWR checkpoint buffers written 56 0.0 0.0
DBWR checkpoints 0 0.0 0.0
DBWR free buffers found 39,016 10.8 17.2
DBWR lru scans 212 0.1 0.1
DBWR make free requests 214 0.1 0.1
DBWR revisited being-written buff 0 0.0 0.0
DBWR summed scan depth 39,802 11.0 17.6
DBWR transaction table writes 20 0.0 0.0
DBWR undo block writes 253 0.1 0.1
SQL*Net roundtrips to/from client 297,662 82.5 131.2
background checkpoints completed 0 0.0 0.0
background checkpoints started 0 0.0 0.0
background timeouts 4,757 1.3 2.1
branch node splits 1 0.0 0.0
buffer is not pinned count 7,283,502 2,017.6 3,211.4
buffer is pinned count 7,725,354 2,140.0 3,406.2
bytes received via SQL*Net from c 42,305,431 11,719.0 18,653.2
bytes sent via SQL*Net to client 160,761,966 44,532.4 70,882.7
calls to get snapshot scn: kcmgss 128,099 35.5 56.5
calls to kcmgas 2,544 0.7 1.1
calls to kcmgcs 251 0.1 0.1
change write time 42 0.0 0.0
cleanouts and rollbacks - consist 86 0.0 0.0
cleanouts only - consistent read 121 0.0 0.1
cluster key scan block gets 696,521 192.9 307.1
cluster key scans 193,681 53.7 85.4
commit cleanout failures: block l 45 0.0 0.0
commit cleanout failures: buffer 0 0.0 0.0
commit cleanout failures: callbac 0 0.0 0.0
commit cleanout failures: cannot 0 0.0 0.0
commit cleanouts 5,013 1.4 2.2
commit cleanouts successfully com 4,968 1.4 2.2
consistent changes 1,104 0.3 0.5
consistent gets 18,416,274 5,101.5 8,120.1
current blocks converted for CR
cursor authentications 3,593 1.0 1.6
data blocks consistent reads - un 1,096 0.3 0.5
db block changes 37,987 10.5 16.8
db block gets 81,063 22.5 35.7
deferred (CURRENT) block cleanout 3,627 1.0 1.6
dirty buffers inspected 185 0.1 0.1
enqueue conversions 1,795 0.5 0.8
enqueue releases 11,844 3.3 5.2
enqueue requests 11,842 3.3 5.2
enqueue timeouts 2 0.0 0.0
enqueue waits 0 0.0 0.0
execute count 122,977 34.1 54.2
free buffer inspected 195 0.1 0.1
free buffer requested 1,085,296 300.6 478.5
hot buffers moved to head of LRU 74,458 20.6 32.8
immediate (CR) block cleanout app 207 0.1 0.1
immediate (CURRENT) block cleanou 532 0.2 0.2
Instance Activity Stats for DB: ORACLE Instance: orcl Snaps: 77 -
7
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
index fast full scans (full) 8 0.0 0.0
leaf node splits 72 0.0 0.0
logons cumulative 246 0.1 0.1
logons current
messages received 3,546 1.0 1.6
messages sent 3,546 1.0 1.6
no buffer to keep pinned count 5,947,314 1,647.5 2,622.3
no work - consistent read gets 4,757,355 1,317.8 2,097.6
opened cursors cumulative 45,222 12.5 19.9
opened cursors current
parse count (hard) 14,377 4.0 6.3
parse count (total) 75,539 20.9 33.3
parse time cpu 7,455 2.1 3.3
parse time elapsed 8,597 2.4 3.8
physical reads 1,108,899 307.2 488.9
physical reads direct 24,969 6.9 11.0
physical writes 39,353 10.9 17.4
physical writes direct 38,306 10.6 16.9
physical writes non checkpoint 39,059 10.8 17.2
pinned buffers inspected 4 0.0 0.0
prefetched blocks 891,325 246.9 393.0
prefetched blocks aged out before 50 0.0 0.0
process last non-idle time 2,591,041,041 717,739.9 1,142,434.3
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 512,032 141.8 225.8
recursive cpu usage 1,462 0.4 0.6
redo blocks written 13,461 3.7 5.9
redo buffer allocation retries 0 0.0 0.0
redo entries 20,622 5.7 9.1
redo log space requests 0 0.0 0.0
redo log space wait time 0 0.0 0.0
redo ordering marks 0 0.0 0.0
redo size 5,954,632 1,649.5 2,625.5
redo synch time 1,288 0.4 0.6
redo synch writes 2,249 0.6 1.0
redo wastage 716,436 198.5 315.9
redo write time 1,370 0.4 0.6
redo writer latching time 0 0.0 0.0
redo writes 2,325 0.6 1.0
rollback changes - undo records a 27 0.0 0.0
rollbacks only - consistent read 231 0.1 0.1
rows fetched via callback 1,448,230 401.2 638.6
session connect time 2,591,041,041 717,739.9 1,142,434.3
session cursor cache count 1,115 0.3 0.5
session cursor cache hits 44,427 12.3 19.6
session logical reads 18,497,337 5,123.9 8,155.8
session pga memory 152,741,308 42,310.6 67,346.3
session pga memory max 153,929,032 42,639.6 67,869.9
session uga memory 2,617,508 725.1 1,154.1
session uga memory max 89,301,248 24,737.2 39,374.5
sorts (disk) 9 0.0 0.0
sorts (memory) 15,248 4.2 6.7
sorts (rows) 1,036,066 287.0 456.8
summed dirty queue length 1 0.0 0.0
Instance Activity Stats for DB: ORACLE Instance: orcl Snaps: 77 -
7
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
switch current to new buffer
table fetch by rowid 6,120,757 1,695.5 2,698.8
table fetch continued row 13,588 3.8 6.0
table scan blocks gotten 1,608,304 445.5 709.1
table scan rows gotten 24,027,374 6,655.8 10,594.1
table scans (long tables) 214 0.1 0.1
table scans (short tables) 7,763 2.2 3.4
total file opens 151 0.0 0.1
transaction rollbacks 1 0.0 0.0
user calls 298,145 82.6 131.5
user commits 2,190 0.6 1.0
user rollbacks 78 0.0 0.0
write clones created in backgroun 0 0.0 0.0
write clones created in foregroun 0 0.0 0.0
-------------------------------------------------------------
Tablespace IO Summary for DB: ORACLE Instance: orcl Snaps: 77 -
78
Avg Read Total Avg
Wait
Tablespace Reads (ms) Writes Waits (ms)
------------------------- ----------- -------- ----------- ---------- ------
--
USER_DATA 175,059 1.8 124 0
0.0
INDICI4 11,101 2.4 1,583 0
0.0
TEMPORARY_DATA 6,550 0.0 3,340 0
0.0
SYSTEM 8,324 2.6 23 0
0.0
TEMP 2,728 0.0 1,083 0
0.0
INDICI_VARI 986 3.3 35 0
0.0
ROLLBACK 34 14.7 266 0
0.0
BUSINESSINTELLIGENCE 259 3.9 37 0
0.0
TOOLS 101 5.8 176 0
0.0
INDICI_BI 54 5.9 48 0
0.0
-------------------------------------------------------------
File IO Statistics for DB: ORACLE Instance: orcl Snaps: 77 - 78
Tablespace Filename
------------------------ ---------------------------------------------------
-
Reads Avg Blks Rd Avg Rd (ms) Writes Tot Waits Avg Wait
(ms)
-------------- ----------- ----------- -------------- ---------- -----------
--
BUSINESSINTELLIGENCE D:\BUSINESS
259 1.1 3.9 37 0
INDICI4 D:\INDICI4.DBF
11,101 2.2 2.4 1,583 0
INDICI_BI F:\INDICI_BI
54 1.0 5.9 48 0
INDICI_VARI F:\INDICI_VARI
986 1.2 3.3 35 0
ROLLBACK D:\ROLLBACK1.ORA
34 1.0 14.7 266 0
SYSTEM F:\ORACLE\ORADATA\ORACLE\SYSTEM01.DBF
8,324 2.3 2.6 23 0
TEMP D:\TEMP.DBF
2,728 3.1 0.0 1,083 0
TEMPORARY_DATA D:\TEMP01.DBF
5,293 2.5 0.0 2,708 0
TEMPORARY_DATA E:\TEMP01.DBF
1,257 2.5 0.0 632 0
TOOLS F:\ORACLE\ORADATA\ORACLE\TOOLS01.DBF
101 1.0 5.8 176 0
USER_DATA E:\USERS01.DBF
175,059 5.9 1.8 124 0
-------------------------------------------------------------
Rollback Segment Stats for DB: ORACLE Instance: orcl Snaps: 77 -
78
->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 12.0 0.00 0 0 0 0
1 205.0 0.00 43,876 0 0 0
2 199.0 0.00 48,962 0 0 0
3 241.0 0.00 54,144 0 0 0
4 197.0 0.00 36,046 0 0 0
5 184.0 0.00 33,710 0 0 0
6 177.0 0.00 36,960 1 0 0
7 198.0 0.00 29,960 1 0 0
8 396.0 0.00 574,034 0 0 0
9 240.0 0.00 49,162 0 0 0
10 224.0 0.00 36,148 0 0 0
11 248.0 0.00 52,980 0 0 0
12 210.0 0.00 23,784 0 0 0
13 275.0 0.00 56,832 0 0 0
14 233.0 0.00 42,254 0 0 0
15 225.0 0.00 35,672 0 0 0
16 210.0 0.00 29,924 0 0 0
17 221.0 0.00 35,100 0 0 0
18 226.0 0.00 42,098 0 0 0
19 227.0 0.00 41,762 0 0 0
20 302.0 0.00 86,832 0 0 0
21 250.0 0.00 56,478 0 0 0
22 209.0 0.00 38,684 0 0 0
23 210.0 0.00 48,788 0 0 0
24 218.0 0.00 23,178 0 0 0
25 419.0 0.00 278,312 0 0 0
-------------------------------------------------------------
Rollback Segment Storage for DB: ORACLE Instance: orcl Snaps: 77 -
->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 54,778,974 51,200,000 157,691,904
2 179,769,344 ########### 204,800,000 179,769,344
3 179,769,344 68,512,983 256,000,000 258,617,344
4 53,612,544 39,428,195 204,800,000 176,615,424
5 22,073,344 10,571,610 20,480,000 53,612,544
6 53,612,544 37,727,722 256,000,000 258,617,344
7 6,303,744 8,464,940 5,120,000 53,612,544
8 6,303,744 ########### 5,120,000 176,615,424
9 6,303,744 37,231,582 5,120,000 69,382,144
10 6,303,744 90,429,546 5,120,000 176,615,424
11 12,611,584 98,345,833 10,240,000 129,306,624
12 53,612,544 35,422,272 153,600,000 154,537,984
13 53,612,544 18,293,149 71,680,000 69,382,144
14 205,000,704 ########### 204,800,000 261,771,264
15 22,073,344 10,212,725 20,480,000 53,612,544
16 12,611,584 6,856,856 10,240,000 53,612,544
17 12,611,584 4,284,176 10,240,000 53,612,544
18 6,303,744 98,199,926 5,120,000 129,306,624
19 6,303,744 853,969 5,120,000 53,612,544
20 6,303,744 3,504,601 5,120,000 53,612,544
21 6,303,744 1,367,776 5,120,000 53,612,544
22 154,537,984 ########### 153,600,000 261,771,264
23 6,303,744 914,267 5,120,000 53,612,544
24 6,303,744 8,526,346 5,120,000 53,612,544
25 6,303,744 ########### 5,120,000 145,076,224
-------------------------------------------------------------
Latch Activity for DB: ORACLE Instance: orcl Snaps: 77 - 78
->"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 3,620 0.0 0
Checkpoint queue latch 20,509 0.0 1.3 0
Token Manager 151 0.0 1,194
0.0
begin backup scn array 1,245 0.0 0
cache buffer handles 87,119 0.0 0
cache buffers chains 27,538,687 0.0 0.0 2,106,129
0.0
cache buffers lru chain 1,454,962 0.0 0.2 1,085,028
0.0
channel handle pool latch 128 0.0 125
0.0
channel operations parent latch 256 0.0 125
0.0
dml lock allocation 6,842 0.0 0
dropped object history latch 15 0.0 0
enqueue hash chains 25,505 0.0 0
enqueues 44,372 0.0 0
event group latch 128 0.0 0
job_queue_processes parameter l 60 0.0 0
ktm global data 11 0.0 0
latch wait list 151 0.0 152
0.0
library cache 959,038 0.0 0.5 15,188
0.0
library cache load lock 6,552 0.0 0
list of block allocation 5,151 0.0 0
loader state object freelist 72 0.0 0
longop free list 171 0.0 0
messages 25,015 0.0 0.0 0
multiblock read objects 279,566 0.0 0.0 0
ncodef allocation latch 60 0.0 0
process allocation 128 0.0 128
0.0
process group creation 253 0.0 0
redo allocation 26,105 0.0 0
redo writing 17,375 0.0 0.0 0
row cache objects 1,024,259 0.0 0.7 1,194
0.0
sequence cache 705 0.0 0
session allocation 25,933 0.0 0
session idle bit 604,923 0.0 0.0 0
session switching 60 0.0 0
shared pool 923,904 0.0 0.1 0
sort extent pool 4,597 0.0 0
temporary table state object al 2 0.0 0
transaction allocation 7,667 0.0 0
transaction branch allocation 60 0.0 0
undo global data 9,211 0.0 0
user lock 3,742 0.0 0
-------------------------------------------------------------
Latch Sleep breakdown for DB: ORACLE Instance: orcl Snaps: 77 -
78
Get Spin &
Latch Name Requests Misses Sleeps Sleeps 1-4
---------------------------- ------------ ----------- ----------- ----------
--
shared pool 923,904 385 27
367/13/3/2/0
library cache 959,038 380 196
248/80/42/10
/0
row cache objects 1,024,259 122 84
64/33/24/1/0
cache buffers chains 27,538,687 91 3 88/3/0/0/0
cache buffers lru chain 1,454,962 10 2 8/2/0/0/0
Checkpoint queue latch 20,509 3 4 0/2/1/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: ORACLE Instance: orcl Snaps: 77 - 78
No Wait
Latch Name Where Misses
Sleeps
------------------------------ -------------------------- ------- ----------
-
Checkpoint queue latch kcbk0rrd: update recovery 0
4
cache buffers chains kcbgtcr: kslbegin 0
3
cache buffers lru chain kcbbiop: lru scan 0
1
cache buffers lru chain kcbzar: KSLNBEGIN 0
1
library cache kglhdgn: child: 0
104
library cache kglpnal: child: alloc spac 0
33
library cache kglpnal: child: before pro 0
32
library cache kglhdgc: child: 0
5
library cache kglobpn: child: 0
5
library cache kglati 0
2
library cache kglic 0
2
library cache kgldti: not under latch 0
1
row cache objects kqrpre: find obj 0
82
row cache objects kqreqd 0
1
row cache objects kqreqd: rel enqueue 0
1
shared pool kghalo 0
10
shared pool kghfrunp: parent clatch: w 0
9
shared pool kghfrunp: alloc: wait 0
8
shared pool kghfrunp: alloc: clatch no 0
6
shared pool kghfrunp: clatch: nowait 0
3
shared pool kghfnd: min scan 0
2
shared pool kghfnd: get next extent 0
1
-------------------------------------------------------------
Buffer Pool Sets for DB: ORACLE Instance: orcl Snaps: 77 - 78
Free Write
Buffer
Set Buffer Consistent Physical Physical Buffer Complete
Busy
Id Gets Gets Reads Writes Waits Waits
Waits
--- ----------- ----------- ----------- ----------- -------- -------- ------
--
3 1,085,322 7,759,882 1,083,949 1,047 0 0
0
-------------------------------------------------------------
Dictionary Cache Stats for DB: ORACLE Instance: orcl Snaps: 77 -
78
->"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 0 0 0 1
33
dc_database_links 0 0 0 0
0
dc_files 0 0 0 0
0
dc_free_extents 47,962 0.1 23 0.0 52 4,452
99
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 4,065 0.5 10 0.0 0 129
97
dc_object_ids 101,113 0.1 0 17 ######
100
dc_objects 38,476 0.5 0 59 9,042
100
dc_outlines 0 0 0 0
0
dc_profiles 128 0.0 0 0 1
14
dc_rollback_segments 792 0.0 0 0 37
95
dc_segments 114,864 0.1 0 69 ######
100
dc_sequence_grants 0 0 0 0
0
dc_sequences 228 0.0 0 159 5
29
dc_synonyms 3,365 1.0 0 0 216
100
dc_tablespace_quotas 44 4.5 0 38 11
34
dc_tablespaces 165 0.0 0 0 15
94
dc_used_extents 38 60.5 0 38 2,618
95
dc_user_grants 5,255 0.0 0 0 24
51
dc_usernames 6,534 0.0 0 0 20
95
dc_users 21,373 0.0 0 0 31
78
-------------------------------------------------------------
Library Cache Activity for DB: ORACLE Instance: orcl Snaps: 77 -
78
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ----------- ------ ----------- ------ ----------- --------
BODY 427 0.7 425 0.7 0 0
CLUSTER 661 0.6 682 1.2 0 0
INDEX 13 53.8 13 53.8 0 0
OBJECT 0 0 0 0
PIPE 0 0 0 0
SQL AREA 31,138 40.7 129,031 21.0 1,588 39
TABLE/PROCEDURE 57,508 1.0 112,858 2.9 1,472 0
TRIGGER 1,372 1.1 1,372 2.7 22 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: 77 -
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 21,624,784 17,482,592 -4,142,192
PL/SQL DIANA 308,528 301,888 -6,640
PL/SQL MPCODE 461,928 392,760 -69,168
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 17,534,152 17,725,080 190,928
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 85,015,908 89,726,632 4,710,724
ktlbk state objects 53,928 53,928 0
library cache 27,984,732 35,426,384 7,441,652
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,361,220 1,377,256 16,036
processes 80,000 80,000 0
sessions 245,180 245,180 0
sql area 26,225,736 18,093,792 -8,131,944
table columns 20,264 20,532 268
table definiti 7,748 7,048 -700
transaction_branches 147,200 147,200 0
transactions 112,392 112,392 0
trigger defini 20,664 18,708 -1,956
trigger inform 620 480 -140
-------------------------------------------------------------
init.ora Parameters for DB: ORACLE Instance: orcl Snaps: 77 - 78
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:M7WdnTzAlKK_9bDd4p2dnA_at_comcast.com...
>
> Hello Gp.
>
> Thanks for posting statspack report.
>
> As Niall mention 24 hours is too broad time period to get really
valuable
> info for performance tuning.
>
> Since we don't know the baseline (and if database really experiencing
> performance problems) it is hard to provide 100% correct analysis.
>
> Overall, Latches and I/O times seems to be okay. Below is some SQL that
I
> think should be investigated:
>
> 84,909 BG/ execution
>
> 254,727 3 84,909.0 .2 1257338992
> SELECT CAXX_DOCTESTCA.DAT_STP_DEFINITIVA , CAXX_DOCTESTCA.DAT_REGNE
> ,CAXX_DOC
>
> 3,000 rows exec:
>
> 161,939 44 3,680.4 3883037416
> SELECT /*+ USE_HASH(ACXM_ANAGRA) */ "TRXX_DOCTEST_VUOTI"."QTA_V01_PRE",
>
> Also you may want to investigate why do you have lot's of hard parses
(over
> 10%) and if you can identify and investigate / tune table scan SQL ( table
> scans (long tables) is 671).
>
> Group: Please feel free to add.
>
> 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:c0i34i$plh$1_at_fata.cs.interbusiness.it...
> >
> > 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
> > ->ms - millisecond - 1000th of a second (unit often used for disk IO
> > timings)
> >
> > 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 Mon Feb 16 2004 - 09:27:20 CST
 |
 |