| 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 -> SOS - the day after migrating to Oracle 9.2
Hi,
2 days ago we migrated an Oracle 8.1.7.4 instance running on Linux to 9.2.0.5 running on Linux inside a VMWare virtual Machine.
We had a lot of performance problems a year ago and so the database was tuned with a few parameters an since 9 Month running fine even with a gain of twice the sessions in the meantime (~ 800 at peak time).
Mainly these parameters are:
cursor_sharing = force
optimizer_mode = first rows optimizer_index_caching = 20 optimizer_index_caching = 90
Now the migrated database was running fine in the morning until 300 sessions and got unusable slow above 400 sessions.
We had to lock out users to even get a statspack in a decent time.
Here's the statspack.
Has any experienced Oracle user some hints what to look for.
Anything that might explain why it doesn't scale?
Any bottleneck? I'm very thankful for any hint.
This statspack was taken during warm up so I consider the cpu / latch
free related to parsing at least that is what I saw at the old
instance as well after starting up.
Thanks
Alex
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster
Host
------------ ----------- ------------ -------- ----------- -------
XXXX 3835427802 XXXX 1 9.2.0.5.0 NOknvmdb1
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- ---------
-------------------
Begin Snap: 14 02-Nov-04 11:44:45 432
17.5
End Snap: 15 02-Nov-04 11:59:46 44718.0
Elapsed: 15.02 (mins)
Cache Sizes (end)
Buffer Cache: 352M Std Block Size: 8K
Shared Pool Size: 272M Log Buffer: 32K
Load Profile
~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Redo size: 8,699.96
2,885.04
Logical reads: 2,167.41
718.75
Block changes: 39.25
13.02
Physical reads: 3.21
1.06
Physical writes: 4.67
1.55
User calls: 294.04
97.51
Parses: 66.00
21.89
Hard parses: 4.50
1.49
Sorts: 11.19
3.71
Logons: 0.07
0.02
Executes: 96.19
31.90
Transactions: 3.02
% Blocks changed per Read: 1.81 Recursive Call %:
33.45
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 99.97
Buffer Hit %: 99.92 In-memory Sort %: 99.99
Library Hit %: 97.34 Soft Parse %: 93.18
Execute to Parse %: 31.38 Latch Hit %:
98.61
Parse CPU to Parse Elapsd %: 17.41 % Non-Parse CPU:
80.61
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 80.55 80.83
% SQL with executions>1: 30.06 30.89
% Memory for SQL w/exec>1: 66.56 68.59
Top 5 Timed Events
Event Waits Time (s)Ela Time
latch free 49,022 1,945 48.68 CPU time 990 24.77 log file sync 2,985 731 18.29 control file parallel write 276 118 2.95 db file sequential read 1,511 741.85
Wait Events for DB: XXXX Instance: XXXX Snaps: 14 -15
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s)
(ms) /txn
---------------------------- ------------ ---------- ---------- ------
--------
latch free 49,022 1,353 1,945
40 18.0
log file sync 2,985 229 731
245 1.1
control file parallel write 276 0 118
427 0.1
db file sequential read 1,511 0 74
49 0.6
LGWR wait on LNS 12,577 1,377 28
2 4.6
LGWR-LNS wait on channel 4,989 1,372 27
5 1.8
SQL*Net more data to client 44,471 0 26
1 16.4
LNS wait on SENDREQ 2,582 0 20
8 1.0
wait list latch free 675 0 17
26 0.2
log file sequential read 13 0 7
514 0.0
db file scattered read 23 0 4
154 0.0
log file switch completion 5 1 3
550 0.0
SQL*Net break/reset to clien 226 0 2
8 0.1
log file parallel write 2,575 2,544 1
1 0.9
enqueue 6 0 1
200 0.0
LGWR wait for redo copy 132 88 1
7 0.0
library cache pin 3 0 1
271 0.0
control file sequential read 428 0 0
1 0.2
LNS wait on LGWR 3,734 0 0
0 1.4
buffer busy waits 6 0 0
44 0.0
ARCH wait on SENDREQ 15 0 0
4 0.0
log file single write 2 0 0
25 0.0
db file parallel write 261 0 0
0 0.1
direct path read 44 0 0
0 0.0
async disk IO 13 0 0
0 0.0
direct path write 16 0 0
0 0.0
SQL*Net message from client 222,063 0 126,434
569 81.7
SQL*Net message to client 222,079 0 19
0 81.7
SQL*Net more data from clien 293 0 0
0 0.1
-------------------------------------------------------------
--------------------------------------- 3
Background Wait Events for DB: XXXX Instance: XXXX Snaps: 14 -15 -> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s)
(ms) /txn
---------------------------- ------------ ---------- ---------- ------
--------
control file parallel write 275 0 119
431 0.1
LGWR wait on LNS 12,503 1,365 27
2 4.6
LGWR-LNS wait on channel 4,968 1,365 27
5 1.8
LNS wait on SENDREQ 2,565 0 19
8 0.9
latch free 346 5 17
50 0.1
log file sequential read 13 0 7
514 0.0
log file parallel write 2,562 2,531 1
1 0.9
LGWR wait for redo copy 133 88 1
7 0.0
LNS wait on LGWR 3,708 0 0
0 1.4
control file sequential read 365 0 0
1 0.1
db file sequential read 1 0 0
122 0.0
ARCH wait on SENDREQ 15 0 0
4 0.0
log file single write 2 0 0
25 0.0
db file parallel write 261 0 0
0 0.1
direct path read 12 0 0
0 0.0
async disk IO 13 0 0
0 0.0
direct path write 12 0 0
0 0.0
rdbms ipc message 8,647 3,109 7,161
828 3.2
pmon timer 327 285 873
2671 0.1
smon timer 3 3 727
###### 0.0
-------------------------------------------------------------
--------------------------------------- 4
SQL ordered by Gets for DB: XXXX Instance: XXXX Snaps: 14 -15
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources
used by
all SQL statements called within the PL/SQL code. As individual
SQL
statements are also reported, it is possible and valid for the
summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
Hash Value
233,922 78 2,999.0 12.0 54.68 302.45
416322329
Module: XXXX404.EXE
select ErscheinenimOP OPBeginn , ANA_AusEnd OPEnde , vSYSOP.*, v
SYSOPD.* ,vSYSHO.*,vSYSPA.* from vSYSOP,OPZeiten, vSYSOPD ,vSYSH
O,vSYSPA Where PaRefnr = HoPaRefnr and HoRefnr = OPHoRefnr and
OPRefnr=OPDOPRefnr(+) and ZPTOPRefnr = OPRefnr and OPDatum = TO_
DATE(:"SYS_B_0", :"SYS_B_1") and not exists(Select * from OPPOPP
230,699 77 2,996.1 11.8 52.12 253.39
3982673634
Module: XXXXLAN.EXE
select ErscheinenimOP OPBeginn , ANA_AusEnd OPEnde , vSYSOP.*, v
SYSOPD.* ,vSYSHO.*,vSYSPA.* from vSYSOP,OPZeiten, vSYSOPD ,vSYSH
O,vSYSPA Where PaRefnr = HoPaRefnr and HoRefnr = OPHoRefnr and
OPRefnr=OPDOPRefnr(+) and ZPTOPRefnr = OPRefnr and OPDatum = TO_
DATE(:"SYS_B_0", :"SYS_B_1") and not exists(Select OPPRefnr from
215,066 45 4,779.2 11.0 2.16 5.52
3692404552
Module: XXXXLAN.EXE
Select DOFHDOFRefnr from DOFHDOFHilfe , DOFFelder, DOBBereiche W
here DOFHDOFRefnr = DOFRefnr and DOFDOBRefnr = DOBRefnr and DOBG
ruppe = :"SYS_B_0"
180,468 153 1,179.5 9.2 64.33 275.04
1764971635
Module: XXXXLAN.EXE
select * from OPPOPPlan,vSYSHO,vSYSPA Where PaRefnr = HoPaRefnr
and HoRefnr = OPPHoRefnr and OPPDatum = TO_DATE(:"SYS_B_0", :"S
YS_B_1") order by OPPOPRefnr desc
121,380 17 7,140.0 6.2 4.28 13.62
4099158281
Module: XXXX404.EXE
SELECT BRREFNR, BRBEREFNR, BRREREFNR, BRKHFAREFNR, BROREFNR FROM
BRBENUTZERRECHTE WHERE BRBEREFNR = :B1 UNION SELECT BGBRREFNR B
RREFNR, BGBRBGREFNR BRBEREFNR, BGBRREREFNR BRREREFNR, BGBRKHFARE
FNR BRKHFAREFNR, BGBROREFNR BROREFNR FROM BGBRGRUPPENRECHTE, BGB
EGRUPPEN, BEBGZBEBGZUORDNUNG WHERE BGBRBGREFNR = BGREFNR AND BGR
94,050 38 2,475.0 4.8 3.46 9.95
2576925126
Module: XXXXLAN.EXE
Select distinct PEPersonal.* from PEPersonal, PzPersonalFunktio
nsZuordnung Where PzPERefnr = PERefnr and PzPfRefnr = :"SYS_B
_0" and (PeAustritt is null or PeAustritt >= TO_DATE(:"SYS_B_1",
:"SYS_B_2")) and (PeEintritt is null or PeEintritt <= TO_DATE(:
"SYS_B_3", :"SYS_B_4")) order by PEName
70,729 2 35,364.5 3.6 6.86 59.30
264794915
Module: Iris.exe
SELECT OpdRefnr MsgIdRefnr, :"SYS_B_00" MsgIdRefnr2 FROM
OPDetail WHERE StdErf > TO_DATE(:"SYS_B_01", :"SYS_B_02") and
( AndBitValue(OPDStatus, :"SYS_B_03" ) = :"SYS_B_04" ) and
( not exists ( select * from TfTransfer where TfRtRefnr = :"SY
SQL ordered by Gets for DB: XXXX Instance: XXXX Snaps: 14 -15
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources
used by
all SQL statements called within the PL/SQL code. As individual
SQL
statements are also reported, it is possible and valid for the
summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
Hash Value
68,082 3,926 17.3 3.5 16.95 61.38
2766527151
Module: XXXX404.EXE
SELECT DBMS_LOB.SUBSTR (DOZMEMO, 254, 1) DOZWTEXT FROM OPPOPPLAN
, HOHOSPITALISATION, DOZPLAN WHERE OPPHOREFNR = HOREFNR AND DOZZ
REFNR = OPPREFNR AND HOPAREFNR = :B1 AND DBMS_LOB.SUBSTR (DOZMEM
O, 254, 1) IS NOT NULL AND DOZDOFREFNR = 802079
52,950 3,918 13.5 2.7 10.64 52.18
3939574822
Module: XXXX404.EXE
SELECT DBMS_LOB.SUBSTR (DOZMEMO, 254, 1) DOZWTEXT FROM AEANAESTH
ESIE, HOHOSPITALISATION, DOZANA WHERE AEHOREFNR = HOREFNR AND DO
ZZREFNR = AEREFNR AND HOPAREFNR = :B1 AND DBMS_LOB.SUBSTR (DOZME
MO, 254, 1) IS NOT NULL AND DOZDOFREFNR = 802079
50,370 3,922 12.8 2.6 4.07 31.60
3407474218
Module: XXXX404.EXE
SELECT DOZDOWREFNR FROM AEANAESTHESIE, HOHOSPITALISATION, DOZANA
WHERE AEHOREFNR = HOREFNR AND DOZZREFNR = AEREFNR AND HOPAREFNR
SQL ordered by Gets for DB: XXXX Instance: XXXX Snaps: 14 -15
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources
used by
all SQL statements called within the PL/SQL code. As individual
SQL
statements are also reported, it is possible and valid for the
summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
Hash Value
28,595 3,783 7.6 1.5 7.53 22.42
166574945
Module: XXXXLAN.EXE
SELECT INIWERT FROM INITABELLE WHERE INIAPREFNR = :B3 AND UPPER(
INISECTION) = UPPER(:B2) AND UPPER(INIEINTRAG) = UPPER(:B1) AND
SQL ordered by Reads for DB: XXXX Instance: XXXX Snaps: 14 -15 -> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
1,450 1 1,450.0 50.2 50.78 270.93
2522684317
Module: SQL*Plus
BEGIN statspack.snap; END;
1,332 1 1,332.0 46.1 7.17 43.52
4043595143
Module: SQL*Plus
INSERT INTO STATS$SQL_SUMMARY ( SNAP_ID , DBID , INSTANCE_NUMBER
, TEXT_SUBSET , SHARABLE_MEM , SORTS , MODULE , LOADED_VERSIONS
, FETCHES , EXECUTIONS , LOADS , INVALIDATIONS , PARSE_CALLS ,
DISK_READS , BUFFER_GETS , ROWS_PROCESSED , COMMAND_TYPE , ADDRE
SS , HASH_VALUE , VERSION_COUNT , CPU_TIME , ELAPSED_TIME , OUTL
497 2 248.5 17.2 7.09 58.41
373175487
Module: MCCCRW70.EXE
SELECT HOAUFNAHMENUMMER, PANAME||' '||PAVORNAME||', '||TO_CHAR(P
AGEBDAT,'dd.mm.yyyy')||' '||PAGESCHLECHT AS PATIENT, SUBSTR(REP
_ALLG_FKT.GET_KHSTCODE( HOKHSTREFNR ),1,10) AS STATION, SUBSTR(R
EP_ALLG_FKT.GET_KHFACODE( OPDDFKHFAREFNR ),1,10) AS FACHBEREICH,
OPDATUM, SUBSTR(REP_HYGIENE_FKT.GET_DIAG( OPDREFNR ),1,254) AS
434 24 18.1 15.0 4.61 31.02
3391886428
Module: MCCCRW70.EXE
SELECT DBMS_LOB.SUBSTR(DTZTEXT,80,1) DIAGTH FROM DTZDTZUORDNUNG
WHERE DTZZREFNR = :B1 AND DTZDTAREFNR = 2 AND DTZRTREFNR = 5 AND
DTZORDER IN (1,2,3)
296 4 74.0 10.2 5.89 70.71
801783664
Module: XXXXLAN.EXE
SELECT USERENV ('SESSIONID') ,HOREFNR ,OPPREFNR ,OPPDATUM ,SAAL
,ZEIT ,SAAL ,STATION ,AUFNAHMENUMMERC ,PAREFNR ,PATIENT ,PAGEBDA
T ,PAGESCHLECHT ,ANMELDEDATUM ,DAUER ,DIAGNOSE ,FACHBEREICH ,KHF
AREFNR ,THERAPIE ,WARTEDAUER ,OPFREIGABE ,NA ,UEB ,PATBEF ,NOTFA
LLSTATUS ,FREIGABESTATUS ,MON ,PLSTATUS ,GEPLASTATUS ,PRMEDDATUM
281 103 2.7 9.7 3.57 22.36
4037781946
Module: KomMCCISH.exe
INSERT INTO HOHOSPITALISATION ( HOREFNR, HOPAREFNR, HOAUFNAHMENU
MMER, HOAUFNAHMENUMMERC, HOAUFNAHMEDATUM, HOAUFNAHMEZEIT, HOENTL
ASSUNGSDATUM, HOENTLASSUNGSZEIT, HOSTATUS, HOKHFAREFNR, HOKHSTRE
FNR, HOKHZINR, HOAUFNAHMEART, HOEINWARZT, HOHAARZT, HOGEPLENTLAS
SUNG, HOENTLASSUNGNACH, HOENTLNR, HOVNKASSENNR, HOZAHLSTATUS, HO
236 2 118.0 8.2 6.86 59.30
264794915
Module: Iris.exe
SELECT OpdRefnr MsgIdRefnr, :"SYS_B_00" MsgIdRefnr2 FROM
OPDetail WHERE StdErf > TO_DATE(:"SYS_B_01", :"SYS_B_02") and
( AndBitValue(OPDStatus, :"SYS_B_03" ) = :"SYS_B_04" ) and
( not exists ( select * from TfTransfer where TfRtRefnr = :"SY
S_B_05" and TfZRefnr = OpdRefnr and TfErf > ( select NVL(max(
227 131 1.7 7.9 1.62 12.92
712900952
Module: KomMCCISH.exe
Select DTDiagnoseTherapie.* from DTDiagnoseTherapie Where (DTTy
p = :"SYS_B_0" or (DTTyp = :"SYS_B_1" and (((AndBitValue(DTFlags
SQL ordered by Reads for DB: XXXX Instance: XXXX Snaps: 14 -15 -> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
70 3,926 0.0 2.4 16.95 61.38
2766527151
Module: XXXX404.EXE
SELECT DBMS_LOB.SUBSTR (DOZMEMO, 254, 1) DOZWTEXT FROM OPPOPPLAN
, HOHOSPITALISATION, DOZPLAN WHERE OPPHOREFNR = HOREFNR AND DOZZ
REFNR = OPPREFNR AND HOPAREFNR = :B1 AND DBMS_LOB.SUBSTR (DOZMEM
O, 254, 1) IS NOT NULL AND DOZDOFREFNR = 802079
68 47 1.4 2.4 0.61 7.45
3536317380
Module: MCCOP.EXE
INSERT INTO DOZWERTE2 ( DOZREFNR, DOZZREFNR, DOZDOBREFNR, DOZDOF
REFNR, DOZDOWREFNR, DOZDOW2REFNR, DOZWLONG, DOZWDOUBLE, DOZWFIXE
D, DOZWTEXT, DOZWDATUM, DOZMEMO, DOZDOGZREFNR, DOZERF, DOZERFUSE
R, DOZAKT, DOZAKTUSER ) VALUES ( :B15, :B14, :B13, :B12, :B11, :
B10, :B9, :B8, :B7, :B6, :B5, EMPTY_CLOB(), :B4, :B2, :B3, :B2,
62 104 0.6 2.1 0.64 5.13
1755624391
Module: KomMCCISH.exe
INSERT INTO CLCOMLOG ( CLREFNR, CLPAREFNR, CLHOREFNR, CLCOMMODUL
, CLCOMVERSION, CLCOMINFO, CLERRORLEVEL, CLMESSAGETYP, CLMESSAGE
, CLINFO, CLERF ) VALUES ( :B9, :B8, :B7, :B6, :B5, :B4, :B3, :B
2, EMPTY_CLOB(), EMPTY_CLOB(), :B1 )
59 98 0.6 2.0 0.72 4.63
4048837770
Module: KomMCCISH.exe
UPDATE PAPATIENT SET PAPATID = :B52, PANAME = :B51, PAVORNAME =
:B50, PAGEBDAT = :B49, PAGEBNAME = :B48, PAGEBORT = :B47, PAANRE
FNR = :B46, PATITEL = :B45, PASTRASSE = :B44, PAPLZ = :B43, PAOR
T = :B42, PALACODE = :B41, PAGESCHLECHT = :B40, PATELPRIV = :B39
, PATELGESCH = :B38, PAANCODE = :B37, PAFAMSTAND = :B36, PAKONCO
52 102 0.5 1.8 1.80 4.31
1602335311
Module: KomMCCISH.exe
SELECT * FROM DTZDTZuordnung WHERE DTZHoRefnr = :"SYS_B_0" AND
DTZZRefnr = :"SYS_B_1" AND DTZRtRefnr = :"SYS_B_2" AND DTZDTARef
SQL ordered by Reads for DB: XXXX Instance: XXXX Snaps: 14 -15 -> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
SQL ordered by Executions for DB: XXXX Instance: XXXX Snaps: 14 -15
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
11,859 11,859 1.0 0.00 0.01
3258239061
Module: XXXX404.EXE
Select * from DOWWerte Where DOWRefnr = :"SYS_B_0"
3,937 0 0.0 0.00 0.02
1079167046
Module: XXXX404.EXE
Select * from PAAPAAngehoerige Where PAAPARefnr = :"SYS_B_0"
3,931 4 0.0 0.00 0.00
4012599953
Module: XXXX404.EXE
SELECT DOZDOWREFNR FROM OPPOPPLAN, HOHOSPITALISATION, DOZPLAN WH
ERE OPPHOREFNR = HOREFNR AND DOZZREFNR = OPPREFNR AND HOREFNR =
:B1 AND DOZDOFREFNR = 802078
3,930 3,930 1.0 0.00 0.01
3285383209
Module: XXXX404.EXE
SELECT (:B3 + :B2 + :B1) MAFLAGS FROM DUAL
3,926 576 0.1 0.00 0.02
2766527151
Module: XXXX404.EXE
SELECT DBMS_LOB.SUBSTR (DOZMEMO, 254, 1) DOZWTEXT FROM OPPOPPLAN
, HOHOSPITALISATION, DOZPLAN WHERE OPPHOREFNR = HOREFNR AND DOZZ
REFNR = OPPREFNR AND HOPAREFNR = :B1 AND DBMS_LOB.SUBSTR (DOZMEM
O, 254, 1) IS NOT NULL AND DOZDOFREFNR = 802079
3,922 4 0.0 0.00 0.01
3407474218
Module: XXXX404.EXE
SELECT DOZDOWREFNR FROM AEANAESTHESIE, HOHOSPITALISATION, DOZANA
WHERE AEHOREFNR = HOREFNR AND DOZZREFNR = AEREFNR AND HOPAREFNR
SQL ordered by Executions for DB: XXXX Instance: XXXX Snaps: 14 -15
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
2,906 0 0.0 0.00 0.00
4175479141
Module: XXXXLAN.EXE
SELECT INIWERT FROM INITABELLE WHERE INIAPREFNR = :B4 AND INIKHF
AREFNR = :B3 AND UPPER(INISECTION) = UPPER(:B2) AND UPPER(INIEIN
TRAG) = UPPER(:B1)
2,872 6 0.0 0.00 0.00
1943967685
Module: XXXXLAN.EXE
SELECT INIWERT FROM INITABELLE WHERE INIAPREFNR = -1 AND INIKHFA
REFNR = :B3 AND UPPER(INISECTION) = UPPER(:B2) AND UPPER(INIEINT
RAG) = UPPER(:B1)
2,862 2,862 1.0 0.00 0.02
1680024556
Module: XXXXLAN.EXE
SELECT :B1 INIWERT FROM DUAL
2,086 1,362 0.7 0.00 0.01
2947017836
Module: XXXXLAN.EXE
SELECT INIWERT FROM INITABELLE WHERE INIAPREFNR = -1 AND UPPER(I
NISECTION) = UPPER(:B2) AND UPPER(INIEINTRAG) = UPPER(:B1) AND (
INIKHFAREFNR <= 0 OR INIKHFAREFNR IS NULL)
1,570 1,052 0.7 0.00 0.01
573740048
Module: XXXXLAN.EXE
SELECT :B2 REFNR, :B1 SYSTIME FROM DUAL
846 846 1.0 0.00 0.01
25484747
Module: XXXXLAN.EXE
SELECT NULL INIWERT FROM DUAL
750 750 1.0 0.00 0.01
2687988480
Module: XXXX404.EXE
select * from PEPersonal Where PERefnr = :"SYS_B_0"
750 4,278 5.7 0.00 0.02
4039954774
Module: XXXX404.EXE
Select * from PzPersonalFunktionsZuordnung Where PzPeRefnr = :"
SYS_B_0"
532 1,976 3.7 0.00 0.04
4143084494
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with grantee#=:1 and privilege
#>0
450 450 1.0 0.00 0.01
2591988057
Module: XXXXLAN.EXE
SELECT MAX(EVREFNR) FROM MCCEVEVENTLOG
448 428 1.0 0.01 0.06
3599210780
Module: XXXXLAN.EXE
INSERT INTO MCCEVEVENTLOG ( EVREFNR, EVERF, EVTYP, EVNR, EVTEXT,
EVAPPREFNR, EVAPREFNR, EVUSERID, EVZREFNR, EVRTREFNR, EVHOREFNR
) VALUES ( :B11, :B10, :B9, :B8, :B7, :B6, :B5, :B4, :B3, :B2,
SQL ordered by Executions for DB: XXXX Instance: XXXX Snaps: 14 -15
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
409 409 1.0 0.00 0.03
3511995769
Module: XXXX404.EXE
Select * from DOWWerte Where DOWRefnr = :"SYS_B_0"
395 0 0.0 0.00 0.02
1833928056
Module: XXXXLAN.EXE
SELECT :B1 FROM DUAL
380 342 0.9 0.01 0.06
3346129690
Module: XXXXLAN.EXE
UPDATE INITABELLE SET INIWERT = :B5, INIAKT = SYSDATE WHERE INIA
PREFNR = :B4 AND INISECTION = :B3 AND INIEINTRAG = :B2 AND INIKH
FAREFNR = :B1
307 307 1.0 0.00 0.01
3192642115
Module: XXXX404.EXE
Select * from HoHospitalisation Where HoRefnr = :"SYS_B_0"
307 307 1.0 0.00 0.01
3332421090
Module: XXXX404.EXE
Select * from PaPatient Where PaRefnr = :"SYS_B_0"
307 460 1.5 0.00 0.01
3478396805
Module: XXXX404.EXE
Select * from vSYSBEW Where BEWHORefnr = :"SYS_B_0" order by B
EWDatum
274 274 1.0 0.00 0.01
2293415029
Module: XXXXLAN.EXE
SELECT SYSDATE FROM DUAL
265 0 0.0 0.00 0.02
1296439601
Module: XXXX404.EXE
SELECT 1 RET FROM DUAL
253 554 2.2 0.00 0.02
622928909
Module: KomMCCISH.exe
Select * from vSYSBEW Where BEWHORefnr = :"SYS_B_0" order by BEW
Datum, BEWID
247 245 1.0 0.00 0.01
3665763022
update sys.col_usage$ set equality_preds = equality_preds
+ decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoi
n_preds + decode(bitand(:flag,2),0,0,1), nonequijoin_preds
SQL ordered by Parse Calls for DB: XXXX Instance: XXXX Snaps: 14 -15
-> End Parse Calls Threshold: 1000
% Total
11,858 11,859 19.94 3258239061
Module: XXXX404.EXE
Select * from DOWWerte Where DOWRefnr = :"SYS_B_0"
3,937 3,937 6.62 1079167046
Module: XXXX404.EXE
Select * from PAAPAAngehoerige Where PAAPARefnr = :"SYS_B_0"
3,930 3,930 6.61 3285383209
Module: XXXX404.EXE
SELECT (:B3 + :B2 + :B1) MAFLAGS FROM DUAL
3,919 3,919 6.59 1278998217
Module: XXXX404.EXE
Select * from vSYSBEW Where BEWHORefnr = :"SYS_B_0" order by BEW
Datum desc
3,834 3,834 6.45 3519270266
Module: XXXX404.EXE
Select * from vSYSPA, vSYSHO, vSYSHOS , PAAGArbeitgeber Where Pa
Refnr = HoPaRefnr and HoRefnr = :"SYS_B_0" AND HORefnr=HOSHORef
nr(+) AND PaPaagRefnr=PaagRefnr(+)
2,862 2,862 4.81 1680024556
Module: XXXXLAN.EXE
SELECT :B1 INIWERT FROM DUAL
2,546 0 4.28 1296726926
Module: MCCANA.EXE
SELECT NULL FROM DUAL
1,570 1,570 2.64 573740048
Module: XXXXLAN.EXE
SELECT :B2 REFNR, :B1 SYSTIME FROM DUAL
846 846 1.42 25484747
Module: XXXXLAN.EXE
SELECT NULL INIWERT FROM DUAL
750 750 1.26 2687988480
Module: XXXX404.EXE
select * from PEPersonal Where PERefnr = :"SYS_B_0"
750 750 1.26 4039954774
Module: XXXX404.EXE
Select * from PzPersonalFunktionsZuordnung Where PzPeRefnr = :"
SYS_B_0"
532 532 0.89 4143084494
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with grantee#=:1 and privilege
#>0
409 409 0.69 3511995769
SQL ordered by Parse Calls for DB: XXXX Instance: XXXX Snaps: 14 -15
-> End Parse Calls Threshold: 1000
% Total
395 395 0.66 1833928056
Module: XXXXLAN.EXE
SELECT :B1 FROM DUAL
307 307 0.52 3192642115
Module: XXXX404.EXE
Select * from HoHospitalisation Where HoRefnr = :"SYS_B_0"
307 307 0.52 3332421090
Module: XXXX404.EXE
Select * from PaPatient Where PaRefnr = :"SYS_B_0"
307 307 0.52 3478396805
Module: XXXX404.EXE
Select * from vSYSBEW Where BEWHORefnr = :"SYS_B_0" order by B
EWDatum
265 265 0.45 1296439601
Module: XXXX404.EXE
SELECT 1 RET FROM DUAL
253 253 0.43 622928909
Module: KomMCCISH.exe
Select * from vSYSBEW Where BEWHORefnr = :"SYS_B_0" order by BEW
Datum, BEWID
182 182 0.31 2069551092
Module: MCCOP.EXE
Select count(*) anz from vSYSAE Where AeOPRefnr = :"SYS_B_0"
170 170 0.29 2223195045 SELECT value FROM v$parameter WHERE name = 'spfile'
167 167 0.28 4071391993
Module: KomMCCISH.exe
SELECT * FROM vSYSBEW WHERE BewHoRefnr = :"SYS_B_0" And BEWID >
:"SYS_B_1" ORDER BY BEWID, BewRefnr
153 153 0.26 1764971635
Module: XXXXLAN.EXE
select * from OPPOPPlan,vSYSHO,vSYSPA Where PaRefnr = HoPaRefnr
and HoRefnr = OPPHoRefnr and OPPDatum = TO_DATE(:"SYS_B_0", :"S
YS_B_1") order by OPPOPRefnr desc
153 153 0.26 3547921727 SELECT value,type FROM v$parameter WHERE name = :1
148 148 0.25 4016369706
Module: MCCFrame403.exe
Select count(*) as Anzahl from DOGKHFAZDOGFachbereich where DO
GKHFAZDOGRefnr = :"SYS_B_0" And DOGKHFAZKHFARefnr = :"SYS_B_1"
SQL ordered by Parse Calls for DB: XXXX Instance: XXXX Snaps: 14 -15
-> End Parse Calls Threshold: 1000
% Total
146 146 0.25 7093442
Module: MCCFrame403.exe
select HoPaRefnr from HoHospitalisation where HoRefnr = :"SYS
_B_0"
139 3,783 0.23 166574945
Module: XXXXLAN.EXE
SELECT INIWERT FROM INITABELLE WHERE INIAPREFNR = :B3 AND UPPER(
INISECTION) = UPPER(:B2) AND UPPER(INIEINTRAG) = UPPER(:B1) AND
(INIKHFAREFNR <= 0 OR INIKHFAREFNR IS NULL)
137 137 0.23 3457996012
Module: XXXX404.EXE
Select * from DOFFelder Where DOFRefnr = :"SYS_B_0"
133 133 0.22 1529576336
Module: MCCFrame403.exe
select AWRHoRefnr from AWRAufwachraum where AWRRefnr = :"SYS_
B_0"
131 131 0.22 712900952
Module: KomMCCISH.exe
Select DTDiagnoseTherapie.* from DTDiagnoseTherapie Where (DTTy
p = :"SYS_B_0" or (DTTyp = :"SYS_B_1" and (((AndBitValue(DTFlags
, :"SYS_B_2")) ) <> :"SYS_B_3"))) and (DTCode = :"SYS_B_4" or DT
Code = :"SYS_B_5" or DTCode = :"SYS_B_6" ) and ( DTDTKRefnr = :
"SYS_B_7")
127 127 0.21 3722595467
Module: MCCFrame403.exe
SELECT * FROM ( SELECT 1 ERRREFNR ,1 ERRVERSION ,1 ERRNR ,DECODE
(:B1,1,'Sie haben kein Personal dokumentiert',NULL) ERRTEXT ,1 E
RRSTATUS , NULL ERRDOF FROM DUAL UNION ALL SELECT 2 ERRREFNR ,1
ERRVERSION ,2 ERRNR ,DECODE(:B2,1,'Sie haben keinen AVB dokument
iert',NULL) ERRTEXT ,1 ERRSTATUS , NULL ERRDOF FROM DUAL UNION A
126 126 0.21 3376395278
Module: XXXXLAN.EXE
SELECT VALUE FROM SYS.NLS_DATABASE_PARAMETERS WHERE PARAMETER =
:"SYS_B_0"
122 122 0.21 541736520
Module: XXXX404.EXE
BEGIN MCCGETMAFLAGSPKG.MCCGETMAFLAGS( :1,302984, 643755 ); END;
121 121 0.20 3468666020
select text from view$ where rowid=:1
119 119 0.20 3360804353 select default$ from col$ where rowid=:1
114 114 0.19 2451618215 Module: KomMCCISH.exe
SQL ordered by Parse Calls for DB: XXXX Instance: XXXX Snaps: 14 -15
-> End Parse Calls Threshold: 1000
% Total
SQL ordered by Sharable Memory for DB: XXXX Instance: XXXX Snaps:
14 -15
-> End Sharable Memory Threshold: 1048576
Sharable Mem (b) Executions % Total Hash Value
---------------- ------------ ------- ------------
29,138,950 40 8.7 1967510969
Module: XXXX404.EXE
select AWRRefnr from AWRAufwachraum Where AWROPRefnr = :"SYS_B_0
" order by AWRBeginnDatum, AWRBeginnZeit
18,811,399 22 5.6 1523607439
Module: MCCANA.EXE
Select count(*) Anzahl from vSYSOPD Where OPDOPRefnr = :"SYS_B_
0"
1,558,888 78 0.5 115366336
Module: XXXX404.EXE
select * from PEAAbwesenheit Where ( TO_DATE(:"SYS_B_0", :"S
YS_B_1") BETWEEN PEAVon and PEABis) or ( PEAVon BETWEEN TO_DATE(
:"SYS_B_2", :"SYS_B_3") and TO_DATE(:"SYS_B_4", :"SYS_B_5"))
1,460,443 4 0.4 2056588158
Module: XXXX404.EXE
SELECT DISTINCT * FROM ( Select PARefnr, PAName, PAVorname, PAPA
TId, PAGebDat, PAGeschlecht, PATemp, PAStorno from vSYSPAL, vSYS
HOL WHERE PARefnr = HOPaRefnr AND NVL(HOTemp, :"SYS_B_0") = :"S
YS_B_1" AND ( HOKHFARefnr = :"SYS_B_2" ) AND NVL(PATemp, :"S
YS_B_3") = :"SYS_B_4" AND upper(Paname) like :"SYS_B_5" AND upp
SQL ordered by Version Count for DB: XXXX Instance: XXXX Snaps: 14
-15
-> End Version Count Threshold: 20
Version
Count Executions Hash Value
-------- ------------ ------------
426 40 1967510969
Module: XXXX404.EXE
select AWRRefnr from AWRAufwachraum Where AWROPRefnr = :"SYS_B_0
" order by AWRBeginnDatum, AWRBeginnZeit
285 22 1523607439
Module: MCCANA.EXE
Select count(*) Anzahl from vSYSOPD Where OPDOPRefnr = :"SYS_B_
0"
36 78 115366336
Module: XXXX404.EXE
select * from PEAAbwesenheit Where ( TO_DATE(:"SYS_B_0", :"S
YS_B_1") BETWEEN PEAVon and PEABis) or ( PEAVon BETWEEN TO_DATE(
:"SYS_B_2", :"SYS_B_3") and TO_DATE(:"SYS_B_4", :"SYS_B_5"))
22 42 1512220596
Module: XXXXLAN.EXE
Select DOBBereiche.* from DOBBereiche , DOGGruppen Where DOBGrup
pe = DOGRefnr and DOGRefnr = :"SYS_B_0" order by DOBOrder
22 42 2648006256
Module: XXXXLAN.EXE
Select DOFFelder.* from DOFFelder , DOBBereiche , DOGGruppen Whe
re DOFDOBRefnr = DOBRefnr and DOBGruppe = DOGRefnr and DOGRefnr
Instance Activity Stats for DB: XXXX Instance: XXXX Snaps: 14 -15
Statistic Total per Secondper Trans
active txn count during cleanout 152 0.2 0.1 background checkpoints completed 1 0.0 0.0 background checkpoints started 1 0.0 0.0 background timeouts 1,354 1.5 0.5 branch node splits 1 0.0 0.0 buffer is not pinned count 1,124,851 1,248.5 414.0 buffer is pinned count 33,766,788 37,477.0 12,428.0 bytes received via SQL*Net from c 32,593,771 36,175.1 11,996.2 bytes received via SQL*Net from d 0 0.0 0.0 bytes sent via SQL*Net to client 181,517,253 201,462.0 66,808.0 bytes sent via SQL*Net to dblink 0 0.0 0.0 calls to get snapshot scn: kcmgss 196,193 217.8 72.2 calls to kcmgas 3,281 3.6 1.2 calls to kcmgcs 2,677 3.0 1.0 change write time 1,660 1.8 0.6 cleanout - number of ktugct calls 231 0.3 0.1 cleanouts and rollbacks - consist 27 0.0 0.0 cleanouts only - consistent read 24 0.0 0.0 cluster key scan block gets 2,834 3.2 1.0 cluster key scans 2,057 2.3 0.8 commit cleanout failures: block l 0 0.0 0.0 commit cleanout failures: buffer 1 0.0 0.0 commit cleanout failures: callbac 5 0.0 0.0 commit cleanout failures: cannot 1 0.0 0.0 commit cleanouts 9,748 10.8 3.6 commit cleanouts successfully com 9,741 10.8 3.6 Commit SCN cached 0 0.0 0.0 commit txn count during cleanout 150 0.2 0.1 consistent changes 158 0.2 0.1 consistent gets 1,917,700 2,128.4 705.8 consistent gets - examination 682,468 757.5 251.2 CPU used by this session 98,996 109.9 36.4 CPU used when call started 98,698 109.5 36.3 CR blocks created 105 0.1 0.0 cursor authentications 858 1.0 0.3 data blocks consistent reads - un 155 0.2 0.1 db block changes 35,368 39.3 13.0 db block gets 35,139 39.0 12.9 DBWR buffers scanned 0 0.0 0.0 DBWR checkpoint buffers written 2,867 3.2 1.1 DBWR checkpoints 1 0.0 0.0 DBWR free buffers found 0 0.0 0.0 DBWR lru scans 0 0.0 0.0 DBWR make free requests 0 0.0 0.0 DBWR revisited being-written buff 0 0.0 0.0 DBWR summed scan depth 0 0.0 0.0 DBWR transaction table writes 12 0.0 0.0 DBWR undo block writes 457 0.5 0.2 deferred (CURRENT) block cleanout 5,702 6.3 2.1 DFO trees parallelized 0 0.0 0.0 dirty buffers inspected 0 0.0 0.0 enqueue conversions 284 0.3 0.1 enqueue releases 13,544 15.0 5.0 enqueue requests 13,551 15.0 5.0 enqueue timeouts 0 0.0 0.0 enqueue waits 6 0.00.0
Instance Activity Stats for DB: XXXX Instance: XXXX Snaps: 14 -15
Statistic Total per Secondper Trans
exchange deadlocks 0 0.0 0.0 execute count 86,666 96.2 31.9 free buffer inspected 3 0.0 0.0 free buffer requested 2,585 2.9 1.0 hot buffers moved to head of LRU 987 1.1 0.4 immediate (CR) block cleanout app 52 0.1 0.0 immediate (CURRENT) block cleanou 1,665 1.9 0.6 index fast full scans (direct rea 0 0.0 0.0 index fast full scans (full) 2 0.0 0.0 index fast full scans (rowid rang 0 0.0 0.0 index fetch by key 423,571 470.1 155.9 index scans kdiixs1 354,729 393.7 130.6 leaf node splits 120 0.1 0.0 leaf node 90-10 splits 6 0.0 0.0 logons cumulative 62 0.1 0.0 messages received 5,454 6.1 2.0 messages sent 5,454 6.1 2.0 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 877,091 973.5 322.8 opened cursors cumulative 59,516 66.1 21.9 Parallel operations not downgrade 0 0.0 0.0 parse count (failures) 68 0.1 0.0 parse count (hard) 4,055 4.5 1.5 parse count (total) 59,468 66.0 21.9 parse time cpu 19,197 21.3 7.1 parse time elapsed 110,234 122.4 40.6 physical reads 2,889 3.2 1.1 physical reads direct 1,340 1.5 0.5 physical writes 4,207 4.7 1.6 physical writes direct 1,340 1.5 0.5 physical writes non checkpoint 2,799 3.1 1.0 pinned buffers inspected 0 0.0 0.0 prefetched blocks 69 0.1 0.0 prefetched blocks aged out before 0 0.0 0.0 process last non-idle time 68,162,349,014 75,651,885.7 ############ PX local messages recv'd 0 0.0 0.0 PX local messages sent 0 0.0 0.0 queries parallelized 0 0.0 0.0 recursive calls 133,170 147.8 49.0 recursive cpu usage 19,187 21.3 7.1 redo blocks written 16,993 18.9 6.3 redo buffer allocation retries 4 0.0 0.0 redo entries 18,863 20.9 6.9 redo log space requests 5 0.0 0.0 redo log space wait time 282 0.3 0.1 redo size 7,838,660 8,700.0 2,885.0 redo synch time 76,882 85.3 28.3 redo synch writes 2,746 3.1 1.0 redo wastage 613,692 681.1 225.9 redo write time 10,201 11.3 3.8 redo writer latching time 103 0.1 0.0 redo writes 2,542 2.8 0.9 rollback changes - undo records a 93 0.1 0.0 rollbacks only - consistent read 79 0.1 0.0 rows fetched via callback 177,952 197.5 65.5 session connect time 68,162,349,014 75,651,885.7############
Instance Activity Stats for DB: XXXX Instance: XXXX Snaps: 14 -15
Statistic Total per Secondper Trans
session logical reads 1,952,835 2,167.4 718.8 session pga memory 7,454,012 8,273.0 2,743.5 session pga memory max 13,351,776 14,818.8 4,914.2 session uga memory 3,793,040 4,209.8 1,396.0 session uga memory max 13,022,508 14,453.4 4,793.0 shared hash latch upgrades - no w 354,177 393.1 130.4 shared hash latch upgrades - wait 43 0.1 0.0 sorts (disk) 1 0.0 0.0 sorts (memory) 10,085 11.2 3.7 sorts (rows) 252,394 280.1 92.9 SQL*Net roundtrips to/from client 220,451 244.7 81.1 SQL*Net roundtrips to/from dblink 0 0.0 0.0 summed dirty queue length 0 0.0 0.0 switch current to new buffer 193 0.2 0.1 table fetch by rowid 17,090,797 18,968.7 6,290.3 table fetch continued row 50 0.1 0.0 table scan blocks gotten 27,229 30.2 10.0 table scan rows gotten 761,136 844.8 280.1 table scans (direct read) 0 0.0 0.0 table scans (long tables) 0 0.0 0.0 table scans (rowid ranges) 0 0.0 0.0 table scans (short tables) 1,618 1.8 0.6 transaction rollbacks 18 0.0 0.0 transaction tables consistent rea 0 0.0 0.0 transaction tables consistent rea 0 0.0 0.0 user calls 264,929 294.0 97.5 user commits 2,716 3.0 1.0 user rollbacks 1 0.0 0.0 workarea executions - multipass 0 0.0 0.0 workarea executions - onepass 1 0.0 0.0 workarea executions - optimal 14,784 16.4 5.4 write clones created in backgroun 0 0.0 0.0 write clones created in foregroun 16 0.00.0
Tablespace IO Stats for DB: XXXX Instance: XXXX Snaps: 14 -15
->ordered by IOs (Reads + Writes) desc
Tablespace
Av Av Av Av Buffer
Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s
Waits Wt(ms)
File IO Stats for DB: XXXX Instance: XXXX Snaps: 14 -15
->ordered by Tablespace, File
Tablespace Filename
Av Av Av Av Buffer
Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s
Waits Wt(ms)
INDX /opt/oracle/OPD/oradata/XXXX/INDX_01.dbf
420 0 49.2 1.0 716 1
0
/opt/oracle/OPD/oradata/XXXX/INDX_02.dbf
302 0 41.3 1.0 807 1
2 95.0
/opt/oracle/OPD/oradata/XXXX/INDX_03.dbf
7 0 60.0 1.0 28 0
0
STATSPACK /opt/oracle/OPD/oradata/XXXX/STATSPACK.dbf
87 0 69.5 1.0 216 0
0
SYSTEM /opt/oracle/OPD/oradata/XXXX/system01.dbf
117 0 63.8 1.0 17 0
0
84 0 11.9 15.8 55 0 0
TOOLS /opt/oracle/OPD/oradata/XXXX/tools01.dbf
1 0 30.0 1.0 1 0
0
UNDO1 /opt/oracle/OPD/oradata/XXXX/UNDO1_01.dbf
1 0 60.0 1.0 177 0
3 23.3
/opt/oracle/OPD/oradata/XXXX/UNDO1_02.dbf
1 0 20.0 1.0 136 0
0
/opt/oracle/OPD/oradata/XXXX/UNDO1_03.dbf
1 0 10.0 1.0 153 0
0
USR /opt/oracle/OPD/oradata/XXXX/USR_01.dbf
218 0 49.0 1.1 195 0
1 0.0
/opt/oracle/OPD/oradata/XXXX/USR_02.dbf
209 0 59.9 1.3 194 0
0
/opt/oracle/OPD/oradata/XXXX/USR_03.dbf
181 0 53.1 1.0 238 0
0
Buffer Pool Statistics for DB: XXXX Instance: XXXX Snaps: 14 -15 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
Free
Write Buffer
Number of Cache Buffer Physical Physical Buffer
Complete Busy
P Buffers Hit % Gets Reads Writes Waits
Waits Waits
D 44,044 99.9 1,953,044 1,612 2,866 0 0 6Instance Recovery Stats for DB: XXXX Instance: XXXX Snaps: 14 -15 -> B: Begin snapshot, E: End snapshot
-------------------------------------------------------------
Targt Estd Log File Log Ckpt Log Ckpt MTTR MTTR Recovery Actual Target Size TimeoutInterval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks
Redo Blks
- ----- ----- ---------- ---------- ---------- ---------- ----------
Size for Size Buffers for Est Physical
Estimated
P Estimate (M) Factr Estimate Read Factor Physical
Reads
--- ------------ ----- ---------------- -------------157,277
------------------
D 32 .1 4,004 16.27 4,133,929 D 64 .2 8,008 8.77 2,227,866 D 96 .3 12,012 6.73 1,711,236 D 128 .4 16,016 3.99 1,013,402 D 160 .5 20,020 2.78 706,557 D 192 .5 24,024 2.08 528,578 D 224 .6 28,028 1.69 428,646 D 256 .7 32,032 1.27 323,508 D 288 .8 36,036 1.13 287,448 D 320 .9 40,040 1.08 273,473 D 352 1.0 44,044 1.00 254,153 D 384 1.1 48,048 0.92 233,436 D 416 1.2 52,052 0.85 216,988 D 448 1.3 56,056 0.81 206,437 D 480 1.4 60,060 0.75 191,218 D 512 1.5 64,064 0.71 180,083 D 544 1.5 68,068 0.68 172,804 D 576 1.6 72,072 0.65 165,524 D 608 1.7 76,076 0.63 160,687 D 640 1.8 80,080 0.62
Tot Wait Avg
Class Waits Time (s) Time (ms)
------------------ ----------- ---------- ---------
data block 3 0 63
undo block 1 0 60
undo header 2 0 5
-------------------------------------------------------------
--------------------------------------- 25
PGA Aggr Target Stats for DB: XXXX Instance: XXXX Snaps: 14 -15 -> B: Begin snap E: End snap (rows dentified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only
in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used - amount of memory used for all Workareas (manual
+ auto)
-> %PGA W/A Mem - percentage of PGA memory allocated to workareas
-> %Auto W/A Mem - percentage of workarea memory controlled by Auto
Mem Mgmt
-> %Man W/A Mem - percentage of workarea memory under manual
control
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
97.9 219 5
%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A
Global Mem
Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem
Bound(K)
- --------- --------- ---------- ---------- ------ ------ ------
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass
Execs
------- ------- -------------- ------------- ------------ ------------
8K 16K 13,674 13,674 0 0
16K 32K 436 436 0 0
32K 64K 270 270 0 0
64K 128K 78 78 0 0
128K 256K 2 2 0 0
256K 512K 2 2 0 0
512K 1024K 26 26 0 0
1M 2M 28 28 0 0
4M 8M 1 0 1 0
where Estd PGA Overalloc Count is 0
Estd Extra Estd PGA Estd
PGA
PGA Target Size W/A MB W/A MB Read/ Cache
Overalloc
Est (MB) Factr Processed Written to Disk Hit %
Count
---------- ------- ---------------- ---------------- --------
----------
13 0.1 9,437.7 5,649.8 63.0
620
25 0.3 9,437.7 5,649.8 63.0
619
50 0.5 9,437.7 3,048.9 76.0
367
75 0.8 9,437.7 1,891.6 83.0
317
100 1.0 9,437.7 118.5 99.0
259
120 1.2 9,437.7 55.8 99.0
201
140 1.4 9,437.7 28.9 100.0
125
160 1.6 9,437.7 24.2 100.0
81
180 1.8 9,437.7 10.9 100.0
29
200 2.0 9,437.7 0.0 100.0
0
300 3.0 9,437.7 0.0 100.0
0
400 4.0 9,437.7 0.0 100.0
0
600 6.0 9,437.7 0.0 100.0
0
800 8.0 9,437.7 0.0 100.0
0
Enqueue activity for DB: XXXX Instance: XXXX Snaps: 14 -15
-> Enqueue stats gathered prior to 9i should not be compared with 9i
data
-> ordered by Wait Time desc, Waits desc
Avg Wt
Wait
Eq Requests Succ Gets Failed Gets Waits Time (ms)
Time (s)
Rollback Segment Stats for DB: XXXX Instance: XXXX Snaps: 14 -15
->A high value for "Pct Waits" suggests more rollback segments may be
required
->RBS stats may not be accurate between begin and end snaps when
using Auto Undo
managment, as RBS may be dynamically created and dropped as needed
Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks
Extends
0 4.0 0.00 0 0 0 0
11 517.0 0.00 165,624 0 0 0
12 671.0 0.00 840,664 1 0 1
13 658.0 0.00 196,978 0 0 0
14 500.0 0.00 146,788 1 0 0
15 814.0 0.00 294,074 0 0 0
16 676.0 0.00 240,616 0 0 0
17 521.0 0.00 183,288 0 0 0
18 801.0 0.00 288,528 0 0 0
19 672.0 0.00 233,440 0 0 0
20 527.0 0.00 202,660 0 0 0
Rollback Segment Storage for DB: XXXX Instance: XXXX Snaps: 14 -15 ->Optimal Size should be larger than Avg Active RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- --------------- --------------- ---------------
0 745,472 8,192
745,472
11 6,283,264 320,724
7,331,840
12 7,528,448 810,704
10,477,568
13 6,545,408 6,916,368
39,837,696
14 6,479,872 592,231
7,331,840
15 6,283,264 420,731
10,477,568
16 8,577,024 798,258
10,477,568
17 5,365,760 707,737
7,331,840
18 7,462,912 789,172
9,428,992
19 6,414,336 698,484
9,428,992
20 6,414,336 614,406
6,414,336
Undo Segment Summary for DB: XXXX Instance: XXXX Snaps: 14 -15
-> Undo segment block stats:
-> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired
reUsed
-> eS - expired Stolen, eR - expired Released, eU - expired
reUsed
Undo Undo Num Max Qry Max Tx Snapshot Out of
uS/uR/uU/
TS# Blocks Trans Len (s) Concurcy Too Old Space
eS/eR/eU
---- -------------- ---------- -------- ---------- -------- ------
9 602 230,103 148 2 0 0 0/0/0/0/0/0
Undo Num Max Qry Max Tx Snap Out of
uS/uR/uU/
End Time Blocks Trans Len (s) Concy Too Old Space
eS/eR/eU
Latch Activity for DB: XXXX Instance: XXXX Snaps: 14 -15 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get
requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait
Pct
Get Get Slps Time
NoWait NoWait
Latch Requests Miss /Miss (s)
Requests Miss
Latch Activity for DB: XXXX Instance: XXXX Snaps: 14 -15 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get
requests
->"Pct Misses" for both should be very close to 0.0
Pct Avg Wait
Pct
Get Get Slps Time
NoWait NoWait
Latch Requests Miss /Miss (s)
Requests Miss
Latch Sleep breakdown for DB: XXXX Instance: XXXX Snaps: 14 -15 -> ordered by misses desc
Get
Spin &
Latch Name Requests Misses Sleeps
Sleeps 1->4
-------------------------- -------------- ----------- -----------
------------
library cache 1,157,384 75,202 37,165
42914/28064/
3690/534/0
shared pool 697,770 22,234 8,64913993/7866/3
47/28/0
library cache pin 615,693 1,689 535
1154/535/0/0
/0
library cache pin allocati 396,326 1,506 694
819/680/7/0/
0
cache buffers chains 3,319,469 583 1,334
0/0/0/0/0
messages 23,966 82 70
14/66/2/0/0
session idle bit 532,314 65 18
0/0/0/0/0
cache buffers lru chain 5,451 34 34
1/32/1/0/0
redo allocation 24,162 34 24
11/22/1/0/0
enqueue hash chains 27,486 32 17
15/17/0/0/0
row cache objects 70,662 25 10
16/8/1/0/0
child cursor hash table 48,886 21 4
17/4/0/0/0
checkpoint queue latch 26,823 19 14
5/14/0/0/0
enqueues 14,860 12 8
4/8/0/0/0
dml lock allocation 10,644 7 3
4/3/0/0/0
session allocation 8,740 7 5
2/5/0/0/0
cache buffer handles 19,680 6 5
1/5/0/0/0
SQL memory manager workare 22,563 3 3
0/3/0/0/0
channel operations parent 31,151 2 1
0/0/0/0/0
process allocation 62 2 4
0/0/2/0/0
list of block allocation 121 1 1
0/1/0/0/0
redo writing 8,704 1 1
0/1/0/0/0
sequence cache 378 1 1
0/1/0/0/0
post/wait queue 4,233 1 1
0/1/0/0/0
Latch Miss Sources for DB: XXXX Instance: XXXX Snaps: 14 -15
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait
Waiter
Latch Name Where Misses Sleeps
Sleeps
cache buffer handles kcbzgs 0 5 5 cache buffers chains kcbgtcr: kslbegin excl 0 615 597 cache buffers chains kcbgtcr: fast path 0 549 386 cache buffers chains kcbrls: kslbegin 0 89 236 cache buffers chains kcbchg: kslbegin: bufs not 0 26 2 cache buffers chains kcbnew 0 16 0 cache buffers chains kcbbxsv 0 14 2 cache buffers chains kcbgcur: kslbegin 0 12 47 cache buffers chains kcbnlc 0 4 39 cache buffers chains kcbibr 0 3 0 cache buffers chains kcbzgb: scan from tail. no 0 2 0 cache buffers chains kcbzwb 0 2 0 cache buffers chains kcbchg: kslbegin: call CR 0 1 6 cache buffers chains kcbget: pin buffer 0 1 13 cache buffers lru chain kcbzgb: wait 0 24 31 cache buffers lru chain kcbzgb: posted for free bu 0 6 1 cache buffers lru chain kcbgtcr:CR Scan:KCBRSKIP 0 3 0 cache buffers lru chain kcbbic2 0 2 3 channel operations paren ksrwait() 0 1 0 checkpoint queue latch kcbklbc: Link buffer into 0 6 0 checkpoint queue latch kcbkllrba: compute lowest 0 5 6 checkpoint queue latch kcbbxsv: move to being wri 0 3 5 child cursor hash table kksfbc: new child 0 3 3 child cursor hash table kksheqd: delete enqueue 0 1 0 dml lock allocation ktaiam 0 3 2 enqueue hash chains ksqgtl3 0 12 11 enqueue hash chains ksqrcl 0 5 6 enqueues ksqgel: create enqueue 0 6 5 enqueues ksqdel 0 2 1 library cache kglpndl: child: before pro 0 6,295 5,511 library cache kgllkdl: child: cleanup 0 5,308 1,204 library cache kglupc: child 0 4,804 3,625 library cache kglic 0 4,249 2,033 library cache kglhdgn: child: 0 3,473 9,675 library cache kglpndl: child: after proc 0 2,181 207 library cache kglpin: child: heap proces 0 1,718 449 library cache kglpnc: child 0 1,616 7,815 library cache kglhdgc: child: 0 1,569 822 library cache kglobpn: child: 0 1,149 425 library cache kglpnp: child 0 971 1,597 library cache kgldti: 2child 0 416 225 library cache kgldte: child 0 0 393 333 library cache kglhfr: child 0 349 405 library cache kglpin 0 301 324 library cache kglget: child: KGLDSBRD 0 185 749 library cache kglobld 0 44 336 library cache kglget: child: KGLDSBYD 0 12 43 library cache kglnti 0 12 18 library cache kgldtld: 2child 0 8 6 library cache kglati 0 6 0 library cache kglini: child 0 5 7 library cache kglrtl 0 1 1 library cache pin kglpndl 0 219 154
--------------------------------------- 34
Latch Miss Sources for DB: XXXX Instance: XXXX Snaps: 14 -15
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait
Waiter
Latch Name Where Misses Sleeps
Sleeps
library cache pin kglpnal: child: alloc spac 0 129 175 library cache pin kglupc 0 86 62 library cache pin kglpnc: child 0 80 115 library cache pin kglpnp: child 0 20 22 library cache pin kgldnp: child 0 1 7 library cache pin alloca kglpnal 0 490 351 library cache pin alloca kgllkdl 0 175 330 library cache pin alloca kglpndl 0 26 12 library cache pin alloca kglpnp 0 3 1 list of block allocation ktlabl 0 1 1 messages ksaamb: after wakeup 0 32 23 messages ksarcv 0 23 14 messages ksarcv: after wait 0 15 33 post/wait queue ksliwat:add:nowait 0 1 0 process allocation ksuapc 0 2 4 process allocation ksucrp 0 2 0 redo allocation kcrfwr 0 21 13 redo allocation kcrfwi: more space 0 2 9 redo allocation kcrfwi: before write 0 1 2 redo writing kcrfwint: rba scn pair 0 1 0 row cache objects kqrpre: find obj 0 6 10 row cache objects kqrpfl: not dirty 0 4 0 sequence cache kdnssd 0 1 0 session allocation ksucri 0 4 3 session allocation ksuxds: KSUSFCLC not set 0 1 1 session idle bit ksupuc: set busy 0 16 11 session idle bit ksupuc: clear busy 0 2 7 shared pool kghupr1 0 5,235 7,167 shared pool kghalo 0 2,121 1,026 shared pool kghfrunp: clatch: nowait 0 1,445 0 shared pool kghfrunp: clatch: wait 0 664 1,364 shared pool kghfrunp: alloc: wait 0 583 99 shared pool kghalp 0 285 266 shared pool kghfre 0 275 57 shared pool kghfen: not perm alloc cla 0 148 33 shared pool kghfru 0 4 3 SQL memory manager worka qesmmIRefreshBound 0 2 1 SQL memory manager worka qesmmIRegisterWorkArea 0 1 0
-------------------------------------------------------------
--------------------------------------- 35
Parent Latch Statistics DB: XXXX Instance: XXXX Snaps: 14 -15
-> only latches with sleeps are shown
-> ordered by name
Get Spin
&
Latch Name Requests Misses Sleeps Sleeps
1->4
------------------------ -------------- ----------- ----------
------------
cache buffer handles 20,973 7 4
3/4/0/0/0
child cursor hash table 52,910 16 4
12/4/0/0/0
dml lock allocation 10,062 7 3
4/3/0/0/0
enqueues 16,410 14 12
2/12/0/0/0
messages 22,148 84 75
10/73/1/0/0
process allocation 75 2 4
0/0/2/0/0
redo writing 8,095 2 2
0/2/0/0/0
row cache enqueue latch 73,958 3 1
2/1/0/0/0
sequence cache 408 1 1
0/1/0/0/0
session allocation 9,897 10 9
1/9/0/0/0
Child Latch Statistics DB: XXXX Instance: XXXX Snaps: 14 -15 -> only latches with sleeps/gets > 1/100000 are shown -> ordered by name, gets desc
Child Get
Spin &
Latch Name Num Requests Misses Sleeps
Sleeps 1->4
---------------------- ------- ------------ ----------- ----------
------------
cache buffers chains 13 38,124 11 34
0/0/0/0/0
cache buffers chains 269 35,071 13 36
0/0/0/0/0
cache buffers chains 627 29,444 1 6
0/0/0/0/0
cache buffers chains 192 26,454 13 25
0/0/0/0/0
cache buffers chains 320 23,833 2 1
0/0/0/0/0
cache buffers chains 946 23,660 7 9
0/0/0/0/0
cache buffers chains 170 22,171 11 27
0/0/0/0/0
cache buffers chains 576 21,630 53 132
0/0/0/0/0
cache buffers chains 186 20,625 57 106
0/0/0/0/0
cache buffers chains 990 18,615 9 19
0/0/0/0/0
cache buffers chains 938 17,210 25 80
0/0/0/0/0
cache buffers chains 91 17,080 8 17
0/0/0/0/0
cache buffers chains 426 16,935 5 7
0/0/0/0/0
cache buffers chains 343 16,834 2 4
0/0/0/0/0
cache buffers chains 88 16,208 3 5
0/0/0/0/0
cache buffers chains 323 15,691 2 12
0/0/0/0/0
cache buffers chains 463 15,676 7 11
0/0/0/0/0
cache buffers chains 388 15,223 2 8
0/0/0/0/0
cache buffers chains 40 15,125 3 7
0/0/0/0/0
cache buffers chains 704 14,557 7 7
0/0/0/0/0
cache buffers chains 673 14,182 8 28
0/0/0/0/0
cache buffers chains 338 14,173 1 2
0/0/0/0/0
cache buffers chains 334 13,799 1 3
0/0/0/0/0
cache buffers chains 83 13,701 1 2
0/0/0/0/0
cache buffers chains 361 13,276 12 33
0/0/0/0/0
cache buffers chains 75 13,054 6 12
0/0/0/0/0
cache buffers chains 42 12,772 1 2
0/0/0/0/0
cache buffers chains 1007 12,610 1 2
0/0/0/0/0
cache buffers chains 329 12,258 1 2
0/0/0/0/0
cache buffers chains 324 12,226 5 20
0/0/0/0/0
cache buffers chains 86 12,147 19 62
0/0/0/0/0
cache buffers chains 339 11,827 1 1
0/0/0/0/0
cache buffers chains 359 11,583 8 18
0/0/0/0/0
cache buffers chains 900 10,846 2 2
0/0/0/0/0
cache buffers chains 92 10,493 8 22
0/0/0/0/0
cache buffers chains 111 10,187 18 41
0/0/0/0/0
cache buffers chains 90 10,023 2 1
0/0/0/0/0
cache buffers chains 51 9,672 7 17
0/0/0/0/0
cache buffers chains 71 9,667 15 40
0/0/0/0/0
cache buffers chains 84 9,536 1 2
0/0/0/0/0
cache buffers chains 89 9,405 5 7
0/0/0/0/0
cache buffers chains 53 9,266 2 13
0/0/0/0/0
cache buffers chains 231 9,215 5 7
0/0/0/0/0
cache buffers chains 79 8,841 4 3
0/0/0/0/0
cache buffers chains 178 8,676 1 2
0/0/0/0/0
cache buffers chains 507 8,665 1 6
0/0/0/0/0
cache buffers chains 711 8,619 5 16
0/0/0/0/0
cache buffers chains 87 8,535 2 4
0/0/0/0/0
cache buffers chains 166 8,332 1 2
0/0/0/0/0
cache buffers chains 468 8,220 3 9
0/0/0/0/0
cache buffers chains 725 7,971 1 2
0/0/0/0/0
cache buffers chains 570 7,885 1 2
0/0/0/0/0
cache buffers chains 168 7,816 1 2
0/0/0/0/0
Child Latch Statistics DB: XXXX Instance: XXXX Snaps: 14 -15 -> only latches with sleeps/gets > 1/100000 are shown -> ordered by name, gets desc
Child Get
Spin &
Latch Name Num Requests Misses Sleeps
Sleeps 1->4
---------------------- ------- ------------ ----------- ----------
------------
cache buffers chains 1005 7,698 1 1
0/0/0/0/0
cache buffers chains 466 7,419 2 12
0/0/0/0/0
cache buffers chains 353 7,143 2 4
0/0/0/0/0
cache buffers chains 145 7,134 2 3
0/0/0/0/0
cache buffers chains 268 7,103 2 4
0/0/0/0/0
cache buffers chains 679 7,068 5 11
0/0/0/0/0
cache buffers chains 693 6,746 1 1
0/0/0/0/0
cache buffers chains 360 6,620 5 16
0/0/0/0/0
cache buffers chains 395 6,181 2 3
0/0/0/0/0
cache buffers chains 6 6,092 3 1
0/0/0/0/0
cache buffers chains 350 5,963 2 6
0/0/0/0/0
cache buffers chains 347 5,748 2 5
0/0/0/0/0
cache buffers chains 94 5,552 1 5
0/0/0/0/0
cache buffers chains 514 5,255 1 2
0/0/0/0/0
cache buffers chains 265 5,254 1 2
0/0/0/0/0
cache buffers chains 109 5,206 6 23
0/0/0/0/0
cache buffers chains 174 5,079 3 8
0/0/0/0/0
cache buffers chains 1 5,078 3 1
0/0/0/0/0
cache buffers chains 64 5,076 3 9
0/0/0/0/0
cache buffers chains 98 4,859 3 1
0/0/0/0/0
cache buffers chains 1015 4,855 1 5
0/0/0/0/0
cache buffers chains 935 4,802 1 1
0/0/0/0/0
cache buffers chains 78 4,750 1 2
0/0/0/0/0
cache buffers chains 517 4,736 2 3
0/0/0/0/0
cache buffers chains 538 4,735 2 2
0/0/0/0/0
cache buffers chains 540 4,343 2 9
0/0/0/0/0
cache buffers chains 283 4,271 2 4
0/0/0/0/0
cache buffers chains 76 4,238 1 3
0/0/0/0/0
cache buffers chains 85 4,089 1 1
0/0/0/0/0
cache buffers chains 105 4,008 2 8
0/0/0/0/0
cache buffers chains 469 3,992 1 6
0/0/0/0/0
cache buffers chains 972 3,856 1 6
0/0/0/0/0
cache buffers chains 366 3,815 2 5
0/0/0/0/0
cache buffers chains 346 3,625 1 6
0/0/0/0/0
cache buffers chains 509 3,578 1 4
0/0/0/0/0
cache buffers chains 286 3,574 1 2
0/0/0/0/0
cache buffers chains 74 3,472 1 5
0/0/0/0/0
cache buffers chains 68 3,470 3 8
0/0/0/0/0
cache buffers chains 39 3,391 2 6
0/0/0/0/0
cache buffers chains 380 3,369 2 2
0/0/0/0/0
cache buffers chains 672 3,327 1 3
0/0/0/0/0
cache buffers chains 461 3,234 3 10
0/0/0/0/0
cache buffers chains 518 3,209 1 2
0/0/0/0/0
cache buffers chains 285 3,182 1 4
0/0/0/0/0
cache buffers chains 385 3,160 1 4
0/0/0/0/0
cache buffers chains 384 3,087 2 4
0/0/0/0/0
cache buffers chains 467 3,076 1 4
0/0/0/0/0
cache buffers chains 73 3,053 2 6
0/0/0/0/0
cache buffers chains 447 3,036 1 2
0/0/0/0/0
cache buffers chains 141 3,013 1 2
0/0/0/0/0
cache buffers chains 974 3,000 4 12
0/0/0/0/0
cache buffers chains 99 2,971 2 10
0/0/0/0/0
cache buffers chains 117 2,961 1 2
0/0/0/0/0
Child Latch Statistics DB: XXXX Instance: XXXX Snaps: 14 -15 -> only latches with sleeps/gets > 1/100000 are shown -> ordered by name, gets desc
Child Get
Spin &
Latch Name Num Requests Misses Sleeps
Sleeps 1->4
---------------------- ------- ------------ ----------- ----------
------------
cache buffers chains 288 2,959 2 6
0/0/0/0/0
cache buffers chains 446 2,845 1 3
0/0/0/0/0
cache buffers chains 456 2,843 3 14
0/0/0/0/0
cache buffers chains 464 2,822 1 2
0/0/0/0/0
cache buffers chains 146 2,777 2 5
0/0/0/0/0
cache buffers chains 473 2,751 1 6
0/0/0/0/0
cache buffers chains 646 2,740 2 7
0/0/0/0/0
cache buffers chains 239 2,715 1 2
0/0/0/0/0
cache buffers chains 462 2,697 1 2
0/0/0/0/0
cache buffers chains 885 2,684 2 3
0/0/0/0/0
cache buffers chains 898 2,683 1 2
0/0/0/0/0
cache buffers chains 127 2,630 1 2
0/0/0/0/0
cache buffers chains 291 2,624 2 4
0/0/0/0/0
cache buffers chains 132 2,595 1 1
0/0/0/0/0
cache buffers chains 661 2,561 1 2
0/0/0/0/0
cache buffers chains 413 2,559 2 6
0/0/0/0/0
cache buffers chains 356 2,510 1 5
0/0/0/0/0
cache buffers chains 149 2,509 2 6
0/0/0/0/0
cache buffers chains 183 2,483 1 2
0/0/0/0/0
cache buffers chains 431 2,464 1 5
0/0/0/0/0
cache buffers chains 370 2,442 3 11
0/0/0/0/0
cache buffers chains 591 2,419 3 7
0/0/0/0/0
cache buffers chains 280 2,374 1 2
0/0/0/0/0
cache buffers chains 355 2,304 1 2
0/0/0/0/0
cache buffers chains 428 2,244 1 2
0/0/0/0/0
cache buffers chains 453 2,238 2 7
0/0/0/0/0
cache buffers chains 660 2,175 2 4
0/0/0/0/0
cache buffers chains 869 2,156 1 1
0/0/0/0/0
cache buffers chains 995 2,138 2 4
0/0/0/0/0
cache buffers chains 434 2,136 1 5
0/0/0/0/0
cache buffers chains 365 2,132 1 8
0/0/0/0/0
cache buffers chains 147 2,118 1 2
0/0/0/0/0
cache buffers chains 472 2,115 1 2
0/0/0/0/0
cache buffers chains 415 1,978 1 1
0/0/0/0/0
cache buffers chains 136 1,859 1 6
0/0/0/0/0
cache buffers chains 888 1,858 2 4
0/0/0/0/0
cache buffers chains 261 1,843 1 6
0/0/0/0/0
cache buffers chains 680 1,809 1 2
0/0/0/0/0
cache buffers chains 220 1,801 1 2
0/0/0/0/0
cache buffers chains 409 1,799 2 10
0/0/0/0/0
cache buffers chains 997 1,708 1 6
0/0/0/0/0
cache buffers chains 217 1,666 1 2
0/0/0/0/0
cache buffers chains 896 1,653 1 3
0/0/0/0/0
cache buffers chains 659 1,641 2 11
0/0/0/0/0
cache buffers chains 967 1,629 1 2
0/0/0/0/0
cache buffers chains 250 1,543 1 2
0/0/0/0/0
cache buffers chains 875 1,500 1 2
0/0/0/0/0
cache buffers chains 695 1,482 2 4
0/0/0/0/0
cache buffers chains 495 1,471 1 2
0/0/0/0/0
cache buffers chains 547 1,385 1 3
0/0/0/0/0
cache buffers chains 688 1,377 1 1
0/0/0/0/0
cache buffers chains 1020 1,319 1 1
0/0/0/0/0
cache buffers chains 556 1,290 1 2
0/0/0/0/0
Child Latch Statistics DB: XXXX Instance: XXXX Snaps: 14 -15 -> only latches with sleeps/gets > 1/100000 are shown -> ordered by name, gets desc
Child Get
Spin &
Latch Name Num Requests Misses Sleeps
Sleeps 1->4
---------------------- ------- ------------ ----------- ----------
------------
cache buffers chains 187 1,232 1 2
0/0/0/0/0
cache buffers chains 853 1,211 1 3
0/0/0/0/0
cache buffers chains 644 1,061 1 2
0/0/0/0/0
cache buffers chains 546 947 2 6
0/0/0/0/0
cache buffers chains 582 932 1 7
0/0/0/0/0
cache buffers chains 596 771 1 4
0/0/0/0/0
cache buffers lru chai 3 6,073 62 59
5/55/2/0/0
checkpoint queue latch 5 3,979 14 12
2/12/0/0/0
checkpoint queue latch 6 3,789 4 3
1/3/0/0/0
enqueue hash chains 2 15,651 23 13
10/13/0/0/0
enqueue hash chains 1 12,387 13 11
2/11/0/0/0
library cache 3 558,373 38,979 19,237
22836/13494/
2283/366/0
library cache 2 404,179 30,205 16,62316064/12037/
1801/303/0
library cache 1 251,589 15,143 10,1606387/7565/10
17/174/0
library cache pin 3 296,218 992 344
649/342/1/0/
0
library cache pin 2 219,047 719 301
419/299/1/0/
0
library cache pin 1 130,184 306 108
198/108/0/0/
0
library cache pin allo 3 179,297 789 343
451/333/5/0/
0
library cache pin allo 2 149,359 591 310
284/304/3/0/
0
library cache pin allo 1 85,017 317 178
139/178/0/0/
0
post/wait queue 3 1,828 2 2
0/2/0/0/0
redo allocation 1 23,691 47 38
11/34/2/0/0
redo copy 4 2 2 2
0/2/0/0/0
redo copy 3 1 1 1
0/1/0/0/0
row cache objects 12 40,672 1 1
0/1/0/0/0
row cache objects 16 12,950 1 1
0/1/0/0/0
row cache objects 9 2,031 1 1
0/1/0/0/0
row cache objects 14 534 1 1
0/1/0/0/0
session idle bit 1 320,030 45 14
0/0/0/0/0
session idle bit 2 230,063 18 3
0/0/0/0/0
shared pool 1 734,151 24,466 10,343
14718/9214/4
80/54/0
SQL memory manager wor 6 359 1 1
0/1/0/0/0
SQL memory manager wor 66 355 1 1
0/1/0/0/0
SQL memory manager wor 14 345 1 1
0/1/0/0/0
undo global data 1 3,333 2 3
0/1/1/0/0
undo global data 3 3,268 1 1
0/1/0/0/0
Top 5 Logical Reads per Segment for DB: XXXX Instance: XXXX Snaps: 14 -15
-> End Segment Logical Reads Threshold: 10000
Subobject Obj.
Logical
Owner Tablespace Object Name Name Type
Reads %Total
---------- ---------- -------------------- ---------- -----106,912 5.18
------------ -------
MCCAPP USR OPMAIN TABLE 371,440 18.01 MCCAPP INDX DOFFELDER_COMB INDEX 222,768 10.80 MCCAPP INDX I_BEBGZ_BE INDEX 141,056 6.84 MCCAPP INDX I_OPP_OPREFNR INDEX 112,512 5.46 MCCAPP USR OPPOPPLAN TABLE
Subobject Obj.
Physical
Owner Tablespace Object Name Name Type
Reads %Total
---------- ---------- -------------------- ---------- -----173 7.94
------------ -------
MCCAPP INDX DTZDTZUORDNUNG_COMB INDEX 429 19.69 MCCAPP USR HOHOSPITALISATION TABLE 286 13.13 MCCAPP USR PAPATIENT TABLE 175 8.03 MCCAPP USR DTZDTZUORDNUNG TABLE 173 7.94 MCCAPP INDX I_HO_AUFN INDEX
Top 5 Buf. Busy Waits per Segment for DB: XXXX Instance: XXXX
Snaps: 14 -15
-> End Segment Buffer Busy Waits Threshold: 100
Buffer
Subobject Obj.
Busy
Owner Tablespace Object Name Name Type
Waits %Total
---------- ---------- -------------------- ---------- -----1 20.00
------------ -------
MCCAPP INDX I_EVERF INDEX 3 60.00 MCCAPP USR BEWBEWEGUNG TABLE 1 20.00 MCCAPP USR HOHOSPITALISATION TABLE
Row
Subobject Obj.
Lock
Owner Tablespace Object Name Name Type
Waits %Total
---------- ---------- -------------------- ---------- -----2 5.71
------------ -------
MCCAPP INDX PK_HO INDEX 17 48.57 MCCAPP INDX PK_EV INDEX 6 17.14 MCCAPP INDX OPMAIN_COMB INDEX 4 11.43 MCCAPP INDX PK_PA INDEX 3 8.57 MCCAPP INDX I_HOPAREFNR INDEX
Dictionary Cache Stats for DB: XXXX Instance: XXXX Snaps: 14 -15
->"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
Cache Requests Miss Reqs Miss Reqs
Usage
dc_histogram_data 1,310 0.0 0 0 139 dc_histogram_defs 5,993 6.8 0 0 3,734 dc_object_ids 17,264 0.0 0 0 1,102 dc_objects 1,179 3.4 0 0 867 dc_profiles 61 0.0 0 0 1 dc_rollback_segments 135 0.0 0 0 41 dc_segments 3,684 0.1 0 1 1,117 dc_sequences 16 0.0 0 16 5 dc_table_scns 79 0.0 0 0 2 dc_tablespace_quotas 1 0.0 0 1 1 dc_tablespaces 856 0.0 0 0 9 dc_user_grants 1,100 0.0 0 0 19 dc_usernames 304 0.0 0 0 7 dc_users 3,598 0.0 0 021
Get Pct Pin Pct
Invali-
Namespace Requests Miss Requests Miss Reloads
dations
BODY 498 0.2 498 0.2 0 0 CLUSTER 24 0.0 30 0.0 0 0 SQL AREA 38,588 9.0 254,756 3.1 524 0 TABLE/PROCEDURE 49,864 0.2 66,155 0.8 269 0 TRIGGER 254 0.0 254 0.8 20
Shared Pool Advisory for DB: XXXX Instance: XXXX End Snap: 15 -> Note there is often a 1:Many correlation between a single logical object
in the Library Cache, and the physical number of memory objects associated
with it. Therefore comparing the number of Lib Cache objects (e.g. in
v$librarycache), with the number of Lib Cache Memory Objects is invalid
Estd
Shared Pool SP Estd Estd Estd Lib LC Time
Size for Size Lib Cache Lib Cache Cache Time Saved Estd Lib Cache
Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem
Obj Hits
----------- ----- ---------- ------------ ------------ -------
144 .5 129 67,869 647,279 1.0 2,969,109
176 .6 161 90,415 647,288 1.0 2,976,479
208 .8 192 104,325 647,295 1.0 2,979,176
240 .9 223 114,139 647,304 1.0 2,982,278
272 1.0 254 126,412 647,316 1.0 2,985,421
304 1.1 285 136,572 647,318 1.0 2,986,073
336 1.2 288 137,451 647,318 1.0 2,986,176
368 1.4 288 137,451 647,318 1.0 2,986,176
400 1.5 288 137,451 647,318 1.0 2,986,176
432 1.6 288 137,451 647,318 1.0 2,986,176
464 1.7 288 137,451 647,318 1.0 2,986,176
496 1.8 288 137,451 647,318 1.0 2,986,176
528 1.9 288 137,451 647,318 1.0 2,986,176
560 2.1 288 137,451 647,318 1.0 2,986,176
SGA Memory Summary for DB: XXXX Instance: XXXX Snaps: 14 -15
SGA regions Size in Bytesvalue % Diff
------------------------------ ----------------
Database Buffers 369,098,752 Fixed Size 452,372 Redo Buffers 143,360 Variable Size 385,875,968 ---------------- sum 755,570,452
-------------------------------------------------------------
SGA breakdown difference for DB: XXXX Instance: XXXX Snaps: 14 -15 Pool Name Begin value End
------ ------------------------------ ----------------133,120 0.00
---------------- -------
java free memory 33,550,336 33,550,336 0.00 java memory in use 4,096 4,096 0.00 large free memory 16,745,160 16,745,160 0.00 large session heap 32,056 32,056 0.00 shared DG Broker heap 28,504 28,504 0.00 shared dictionary cache 1,614,976 1,614,976 0.00 shared enqueue 1,233,852 1,233,852 0.00 shared errors 160,336 155,632 -2.93 shared event statistics per sess 14,914,200 14,914,200 0.00 shared FileOpenBlock 10,517,016 10,517,016 0.00 shared fixed allocation callback 228 228 0.00 shared free memory 65,273,664 64,330,244 -1.45 shared joxs heap init 216 216 0.00 shared KGK heap 6,956 6,956 0.00 shared KGLS heap 2,976,636 3,213,172 7.95 shared KQR L SO 6,144 6,144 0.00 shared KQR M PO 2,878,572 3,023,524 5.04 shared KQR M SO 71,248 71,248 0.00 shared KQR S PO 330,488 330,488 0.00 shared KQR S SO 6,672 6,672 0.00 shared KQR X PO 7,296 7,296 0.00 shared library cache 66,091,016 66,490,144 0.60 shared miscellaneous 51,066,008 51,309,056 0.48 shared MTTR advisory 59,676 59,676 0.00 shared parameters 9,539,480 9,368,332 -1.79 shared PLS non-lib hp 2,068 2,068 0.00 shared PL/SQL DIANA 1,236,148 1,356,316 9.72 shared PL/SQL MPCODE 56,587,992 54,963,632 -2.87 shared PL/SQL SOURCE 2,068 2,068 0.00 shared PX subheap 10,948 10,948 0.00 shared sessions 3,195,900 3,195,900 0.00 shared sim memory hea 193,988 193,988 0.00 shared sql area 43,990,552 45,585,444 3.63 shared table definiti 3,064 4,824 57.44 shared transaction 1,432,376 1,432,376 0.00 shared trigger defini 3,688 6,836 85.36 shared trigger inform 1,128 1,128 0.00 shared trigger source 412 412 0.00 shared type object de 2,628 2,628 0.00 shared 1M buffer 2,098,176 2,098,176 0.00 buffer_cache 369,098,752 369,098,752 0.00 fixed_sga 452,372 452,372 0.00 log_buffer 133,120
init.ora Parameters for DB: XXXX Instance: XXXX Snaps: 14 -15
End
value
Parameter Name Begin value (if
different)
archive_lag_target 0 background_dump_dest /opt/oracle/OPD/admin/XXXX/bdump compatible 9.2.0.4 control_files /opt/oracle/OPD/oradata/XXXX/cont core_dump_dest /opt/oracle/OPD/admin/XXXX/cdump cursor_sharing SIMILAR db_block_size 8192 db_cache_size 369098752 db_domain WORLD db_file_multiblock_read_count 128 db_file_name_convert , db_files 400 db_name XXXX dg_broker_start TRUE fal_client (DESCRIPTION=(ADDRESS_LIST = (ADD fal_server (DESCRIPTION=(ADDRESS_LIST = (ADD fast_start_mttr_target 300 hash_join_enabled TRUE instance_name XXXX java_pool_size 33554432 job_queue_processes 5 large_pool_size 16777216 lock_name_space 65_XXXX log_archive_dest log_archive_dest_1 location=/opt/oracle/OPD/admin/MC log_archive_dest_10 log_archive_dest_2 service="(DESCRIPTION=(ADDRESS_LI log_archive_dest_3 log_archive_dest_4 log_archive_dest_5 log_archive_dest_6 log_archive_dest_7 log_archive_dest_8 log_archive_dest_9 log_archive_dest_state_1 ENABLE log_archive_dest_state_2 ENABLE log_archive_format XXXXarch%t_%s.dbf log_archive_max_processes 2 log_archive_min_succeed_dest 1 log_archive_start TRUE log_archive_trace 0 log_buffer 32768 log_checkpoint_interval 10000 log_checkpoint_timeout 1800 log_file_name_convert , max_enabled_roles 30 open_cursors 300 optimizer_index_caching 95 optimizer_index_cost_adj 1 optimizer_max_permutations 2000 optimizer_mode FIRST_ROWS os_authent_prefix parallel_max_servers 10 pga_aggregate_target 104857600 processes 1200different)
--------------------------------------- 46
init.ora Parameters for DB: XXXX Instance: XXXX Snaps: 14 -15 End value Parameter Name Begin value (if
query_rewrite_enabled TRUE query_rewrite_integrity TRUSTED remote_login_passwordfile EXCLUSIVE service_names XXXX session_cached_cursors 0 sga_max_size 755570452 shared_pool_reserved_size 50000000 shared_pool_size 285212672 sort_area_retained_size 1048576 sort_area_size 2048000 standby_archive_dest /opt/oracle/OPD/admin/XXXX/arch standby_file_management AUTO star_transformation_enabled true statistics_level TYPICAL timed_statistics TRUE undo_management AUTO undo_retention 43200 undo_tablespace UNDO1 user_dump_dest /opt/oracle/OPD/admin/XXXX/udump workarea_size_policy autoEnd of Report Received on Tue Nov 02 2004 - 12:59:18 CST
-------------------------------------------------------------
 |
 |