| 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
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
> -------------------------------------------- ------------ ------------ ---
--Received on Fri Feb 13 2004 - 18:19:43 CST
> --
> 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.
> > >
> > >
> >
> >
>
>
 |
 |