On Wed, 3 Nov 2004 02:59:18 +0800, Alex wrote
(in article <ltlfo0l9tq4nshae11h9t8bb84rnnjf1f7_at_news-east.newscene.com>):
why are you running the database inside VMware?
> 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 NO
> knvmdb1
>
> 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 447
> 18.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
> Rollback per transaction %: 0.04 Rows per Sort:
> 25.02
>
> 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
> ~~~~~~~~~~~~~~~~~~
> % Total
> 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 74
> 1.85
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 2
>
> 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
>
>
>
> --------------------------------------- 5
>
> 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
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> S_B_05" and TfZRefnr = OpdRefnr and TfErf > ( select NVL(max(
> 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
> = :B1 AND DOZDOFREFNR = 802078
> 47,701 15 3,180.1 2.4 3.65 13.33
> 2495542607
> Module: XXXXLAN.EXE
> SELECT USERENV('SESSIONID') SESSIONID ,OPHOREFNR HOREFNR ,OPREFN
> R OPREFNR ,OPDATUM OPDATUM ,OPOSREFNR OPVOSREFNR ,OPDREFNR OPDRE
> FNR ,TO_NUMBER(OPDNR) OPDNR ,OPDDFKHFAREFNR OPDDFKHFAREFNR ,'X'
> OPVDIAGNOSE ,'Y' OPVTHERAPIE ,OPDVON OPDVON ,OPDBIS OPDBIS ,OSCO
> DE OPERATEUR ,'A' ANAESTHESIST ,OPDEINGRIFF OPDEINGRIFF ,0 OPPRE
> 41,739 3,931 10.6 2.1 3.44 9.22
> 4012599953
> Module: XXXX404.EXE
> SELECT DOZDOWREFNR FROM OPPOPPLAN, HOHOSPITALISATION, DOZPLAN WH
> ERE OPPHOREFNR = HOREFNR AND DOZZREFNR = OPPREFNR AND HOREFNR =
> :B1 AND DOZDOFREFNR = 802078
> 35,577 11,859 3.0 1.8 21.07 145.15
> 3258239061
> Module: XXXX404.EXE
> Select * from DOWWerte Where DOWRefnr = :"SYS_B_0"
> 34,388 3,834 9.0 1.8 19.11 117.11
> 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(+)
> 29,732 4 7,433.0 1.5 5.89 70.71
> 801783664
> Module: XXXXLAN.EXE
> SELECT USERENV ('SESSIONID') ,HOREFNR ,OPPREFNR ,OPPDATUM ,SAAL
> ,ZEIT ,SAAL ,STATION ,AUFNAHMENUMMERC ,PAREFNR ,PATIENT ,PAGEBDA
>
>
>
> --------------------------------------- 6
>
> 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
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> T ,PAGESCHLECHT ,ANMELDEDATUM ,DAUER ,DIAGNOSE ,FACHBEREICH ,KHF
> AREFNR ,THERAPIE ,WARTEDAUER ,OPFREIGABE ,NA ,UEB ,PATBEF ,NOTFA
> LLSTATUS ,FREIGABESTATUS ,MON ,PLSTATUS ,GEPLASTATUS ,PRMEDDATUM
> 29,204 2,086 14.0 1.5 5.98 13.70
> 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)
> 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
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 7
>
> 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
>
>
>
> --------------------------------------- 8
>
> 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
> --------------- ------------ -------------- ------ -------- ---------
> ----------
> , :"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")
> 170 113 1.5 5.9 1.34 14.62
> 2922040086
> Module: XXXXLAN.EXE
> UPDATE MCCAPP.OPPOPPLAN_STATS SET ST_OPPREFNR = :B21 ,ST_OPPHORE
> FNR = :B20 ,ST_OPPSTATUS = :B19 ,ST_OPPPLANVON = :B18 ,ST_OPPPLA
> NDAUER = :B17 ,ST_OPPEINBESTELLDATUM = :B16 ,ST_OPPORDER = :B15
> ,ST_OPPAFKHFAREFNR = :B14 ,ST_OPPAFKHKSREFNR = :B13 ,ST_OPPDFKHF
> AREFNR = :B12 ,ST_OPPDFKHKSREFNR = :B11 ,ST_OPPKHSTREFNR = :B10
> 94 4 23.5 3.3 1.62 19.61
> 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
> 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
>
>
>
> --------------------------------------- 9
>
> 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
> --------------- ------------ -------------- ------ -------- ---------
> ----------
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 10
>
> 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
> = :B1 AND DOZDOFREFNR = 802078
> 3,919 10,882 2.8 0.00 0.02
> 1278998217
> Module: XXXX404.EXE
> Select * from vSYSBEW Where BEWHORefnr = :"SYS_B_0" order by BEW
> Datum desc
> 3,918 417 0.1 0.00 0.01
> 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
> 3,834 3,814 1.0 0.00 0.03
> 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(+)
> 3,783 1,694 0.4 0.00 0.01
> 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)
>
>
>
> --------------------------------------- 11
>
> 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,
>
>
>
> --------------------------------------- 12
>
> 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
> ------------ --------------- ---------------- ----------- ----------
> ----------
> :B1 )
> 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
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 13
>
> SQL ordered by Parse Calls for DB: XXXX Instance: XXXX Snaps: 14
> -15
> -> End Parse Calls Threshold: 1000
> % Total
> Parse Calls Executions Parses Hash Value
> ------------ ------------ -------- ----------
> 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
>
>
>
> --------------------------------------- 14
>
> SQL ordered by Parse Calls for DB: XXXX Instance: XXXX Snaps: 14
> -15
> -> End Parse Calls Threshold: 1000
> % Total
> Parse Calls Executions Parses Hash Value
> ------------ ------------ -------- ----------
> Module: XXXX404.EXE
> Select * from DOWWerte Where DOWRefnr = :"SYS_B_0"
> 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"
>
>
>
> --------------------------------------- 15
>
> SQL ordered by Parse Calls for DB: XXXX Instance: XXXX Snaps: 14
> -15
> -> End Parse Calls Threshold: 1000
> % Total
> Parse Calls Executions Parses Hash Value
> ------------ ------------ -------- ----------
> 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
>
>
>
> --------------------------------------- 16
>
> SQL ordered by Parse Calls for DB: XXXX Instance: XXXX Snaps: 14
> -15
> -> End Parse Calls Threshold: 1000
> % Total
> Parse Calls Executions Parses Hash Value
> ------------ ------------ -------- ----------
> select * from dtaetzzuordnung where DTAETZETRefnr = :"SYS_B_0"
> and DTAETZDTARefnr = :"SYS_B_1" and ( DTAETZHoStatus is null or
> DTAETZHOStatus = :"SYS_B_2" ) and ( DTAETZValidFrom is null or
> DTAETZValidFrom < :"SYS_B_3" ) and ( DTAETZValidTil is null or D
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 17
>
> 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
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 18
>
> 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
> = :"SYS_B_0" order by DOFDOBRefnr, DOFOrder
> 22 43 3118491741
> Module: XXXXLAN.EXE
> Select DOWWerte.* from DOWWerte, DOFFelder , DOBBereiche , DOGGr
> uppen Where DOWDOFRefnr = DOFRefnr and DOFDOBRefnr = DOBRefnr an
> d DOBGruppe = DOGRefnr and DOGRefnr = :"SYS_B_0" order by DOWDO
> FRefnr, DOWOrder
> 22 45 3692404552
> Module: XXXXLAN.EXE
> Select DOFHDOFRefnr from DOFHDOFHilfe , DOFFelder, DOBBereiche W
> here DOFHDOFRefnr = DOFRefnr and DOFDOBRefnr = DOBRefnr and DOBG
> ruppe = :"SYS_B_0"
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 19
>
> Instance Activity Stats for DB: XXXX Instance: XXXX Snaps: 14 -15
> Statistic Total per Second
> per 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.0
> 0.0
>
>
>
> --------------------------------------- 20
>
> Instance Activity Stats for DB: XXXX Instance: XXXX Snaps: 14 -15
> Statistic Total per Second
> per 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
> ############
>
>
>
> --------------------------------------- 21
>
> Instance Activity Stats for DB: XXXX Instance: XXXX Snaps: 14 -15
> Statistic Total per Second
> per 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.0
> 0.0
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 22
>
> 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)
> -------------- ------- ------ ------- ------------ -------- ----------
> ------
> INDX
> 729 1 46.0 1.0 1,551 2
> 2 95.0
> USR
> 608 1 54.0 1.1 627 1
> 1 0.0
> UNDO1
> 3 0 30.0 1.0 466 1
> 3 23.3
> STATSPACK
> 87 0 69.5 1.0 216 0
> 0 0.0
> TEMP
> 84 0 11.9 15.8 55 0
> 0 0.0
> SYSTEM
> 117 0 63.8 1.0 17 0
> 0 0.0
> TOOLS
> 1 0 30.0 1.0 1 0
> 0 0.0
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 23
>
> 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
> TEMP
> /opt/oracle/OPD/product/oracle/dbs/temp02.dbf
> 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
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 24
>
> 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 6
>
> -------------------------------------------------------------
> Instance 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 Timeout
> Interval
> (s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks
> Redo Blks
> - ----- ----- ---------- ---------- ---------- ---------- ----------
> ----------
> B 46 23 1846 10042 10000 18432 36371
> 10000
> E 46 23 2061 10523 10000 18432 37712
> 10000
>
> -------------------------------------------------------------
> Buffer Pool Advisory for DB: XXXX Instance: XXXX End Snap: 15
> -> Only rows with estimated physical reads >0 are displayed
> -> ordered by Block Size, Buffers For Estimate
> Size for Size Buffers for Est Physical
> Estimated
> P Estimate (M) Factr Estimate Read Factor Physical
> Reads
> --- ------------ ----- ---------------- -------------
> ------------------
> 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
> 157,277
>
> -------------------------------------------------------------
> Buffer wait Statistics for DB: XXXX Instance: XXXX Snaps: 14 -15
> -> ordered by wait time desc, waits desc
> 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)
> - --------- --------- ---------- ---------- ------ ------ ------
> ----------
> B 100 6 341.5 2.3 .7 100.0 .0
> 5,120
> E 100 6 353.4 2.2 .6 100.0 .0
> 5,120
>
> -------------------------------------------------------------
> PGA Aggr Target Histogram for DB: XXXX Instance: XXXX Snaps: 14 -15
> -> Optimal Executions are purely in-memory operations
> 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
>
> -------------------------------------------------------------
> PGA Memory Advisory for DB: XXXX Instance: XXXX End Snap: 15
> -> When using Auto Memory Mgmt, minimally choose a
> pga_aggregate_target value
> 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
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 26
>
> 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)
> -- ------------ ------------ ----------- ----------- -------------
> ------------
> TX 2,994 2,994 0 6 205.67
> 1
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 27
>
> 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
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 28
>
> 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
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 29
>
> 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 Segment Stats for DB: XXXX Instance: XXXX Snaps: 14 -15
> -> ordered by Time desc
> 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
> ------------ ------------ -------- ------- -------- ------- ------
> -------------
> 02-Nov 11:59 228 115,938 30 2 0 0
> 0/0/0/0/0/0
> 02-Nov 11:49 374 114,165 148 2 0 0
> 0/0/0/0/0/0
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 30
>
> 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
> ------------------------ -------------- ------ ------ ------
> ------------ ------
> active checkpoint queue 491 0.0 0
> 0
> alert log latch 8 0.0 0
> 0
> archive control 484 0.0 0
> 0
> archive process latch 24 0.0 0
> 0
> cache buffer handles 19,680 0.0 0.8 0
> 0
> cache buffers chains 3,319,469 0.0 2.3 0
> 2,658 0.1
> cache buffers lru chain 5,451 0.6 1.0 2
> 375 1.9
> channel handle pool latc 109 0.0 0
> 0
> channel operations paren 31,151 0.0 0.5 0
> 0
> checkpoint queue latch 26,823 0.1 0.7 1
> 2,822 0.9
> child cursor hash table 48,886 0.0 0.2 0
> 0
> Consistent RBA 2,560 0.0 0
> 0
> dml lock allocation 10,644 0.1 0.4 0
> 0
> DMON Work Queues Latch 30 0.0 0
> 0
> dummy allocation 109 0.0 0
> 0
> enqueue hash chains 27,486 0.1 0.5 1
> 0
> enqueues 14,860 0.1 0.7 0
> 0
> event group latch 62 0.0 0
> 0
> FAL request queue 2 0.0 0
> 0
> FIB s.o chain latch 398 0.0 0
> 0
> FOB s.o list latch 643 0.0 0
> 0
> hash table column usage 85 0.0 0
> 24,565 0.0
> job_queue_processes para 15 0.0 0
> 0
> ktm global data 3 0.0 0
> 0
> lgwr LWN SCN 2,563 0.0 0
> 0
> library cache 1,157,384 6.5 0.5 1639
> 11,259 19.0
> library cache load lock 766 0.0 0
> 0
> library cache pin 615,693 0.3 0.3 16
> 0
> library cache pin alloca 396,326 0.4 0.5 24
> 0
> list of block allocation 121 0.8 1.0 0
> 0
> loader state object free 18 0.0 0
> 0
> message pool operations 12,742 0.0 0
> 0
> messages 23,966 0.3 0.9 3
> 0
> mostly latch-free SCN 2,565 0.1 0.0 0
> 0
> multiblock read objects 40 0.0 0
> 0
> ncodef allocation latch 39 0.0 0
> 0
> object stats modificatio 444 0.0 0
> 0
> post/wait queue 4,233 0.0 1.0 0
> 2,993 0.0
> process allocation 62 3.2 2.0 1
> 62 0.0
> process group creation 109 0.0 0
> 0
> redo allocation 24,162 0.1 0.7 1
> 0
> redo copy 0 0
> 19,055 0.9
> redo writing 8,704 0.0 1.0 0
> 0
> Request id generation la 45 0.0 0
> 0
> row cache enqueue latch 67,763 0.0 0.0 0
> 0
> row cache objects 70,662 0.0 0.4 0
> 75 0.0
> RSM process latch 60 0.0 0
> 0
> RSM SQL latch 1,440 0.0 0
> 0
> sequence cache 378 0.3 1.0 0
> 0
> session allocation 8,740 0.1 0.7 0
> 0
>
>
>
> --------------------------------------- 31
>
> 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
> ------------------------ -------------- ------ ------ ------
> ------------ ------
> session idle bit 532,314 0.0 0.3 0
> 0
> session switching 156 0.0 0
> 0
> session timer 320 0.0 0
> 0
> shared pool 697,770 3.2 0.4 217
> 0
> sim partition latch 0 0
> 6 0.0
> simulator hash latch 128,041 0.0 0
> 0
> simulator lru latch 260 0.0 0
> 962 7.5
> sort extent pool 53 0.0 0
> 0
> SQL memory manager latch 31 0.0 0
> 252 0.0
> SQL memory manager worka 22,563 0.0 1.0 0
> 0
> transaction allocation 134 0.0 0
> 0
> transaction branch alloc 39 0.0 0
> 0
> undo global data 9,473 0.0 0
> 0
> user lock 240 0.0 0
> 0
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 32
>
> 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,649
> 13993/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
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 33
>
> 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
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 36
>
> 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
>
>
>
> --------------------------------------- 37
>
> 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
>
>
>
> --------------------------------------- 38
>
> 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
>
>
>
> --------------------------------------- 39
>
> 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,623
> 16064/12037/
>
> 1801/303/0
> library cache 1 251,589 15,143 10,160
> 6387/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
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 40
>
> 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
> ---------- ---------- -------------------- ---------- -----
> ------------ -------
> 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
> 106,912 5.18
>
> -------------------------------------------------------------
> Top 5 Physical Reads per Segment for DB: XXXX Instance: XXXX Snaps:
> 14 -15
> -> End Segment Physical Reads Threshold: 1000
> Subobject Obj.
> Physical
> Owner Tablespace Object Name Name Type
> Reads %Total
> ---------- ---------- -------------------- ---------- -----
> ------------ -------
> 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
> 173 7.94
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 41
>
> 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
> ---------- ---------- -------------------- ---------- -----
> ------------ -------
> MCCAPP INDX I_EVERF INDEX
> 3 60.00
> MCCAPP USR BEWBEWEGUNG TABLE
> 1 20.00
> MCCAPP USR HOHOSPITALISATION TABLE
> 1 20.00
>
> -------------------------------------------------------------
> Top 5 Row Lock Waits per Segment for DB: XXXX Instance: XXXX Snaps:
> 14 -15
> -> End Segment Row Lock Waits Threshold: 100
>
> Row
> Subobject Obj.
> Lock
> Owner Tablespace Object Name Name Type
> Waits %Total
> ---------- ---------- -------------------- ---------- -----
> ------------ -------
> 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
> 2 5.71
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 42
>
> 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 0
> 21
>
> -------------------------------------------------------------
> Library Cache Activity for DB: XXXX Instance: XXXX Snaps: 14 -15
> ->"Pct Misses" should be very low
> 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 2
> 0
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 43
>
> 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
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 44
>
> SGA Memory Summary for DB: XXXX Instance: XXXX Snaps: 14 -15
> SGA regions Size in Bytes
> ------------------------------ ----------------
> 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
> value % Diff
> ------ ------------------------------ ----------------
> ---------------- -------
> 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
> 133,120 0.00
>
> -------------------------------------------------------------
>
>
>
> --------------------------------------- 45
>
> 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 1200
>
>
>
> --------------------------------------- 46
>
> init.ora Parameters for DB: XXXX Instance: XXXX Snaps: 14 -15
> End
> value
> Parameter Name Begin value (if
> different)
> ----------------------------- ---------------------------------
> --------------
> 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 auto
>
> -------------------------------------------------------------
> End of Report
>
>
Received on Tue Nov 02 2004 - 15:40:09 CST
