Statspack analysis
Peter Laursen
Users complain about a recent drop in response times. I did a statspack
report. Would someone take a peek and see if there is something unusual in
it?
Snaps were made about an hour apart. During that hour i used one of the
application functions, that users report are now suddenly slower. This
function basicly runs one sql which then used 99,5% of total reads in that
hour.
Another SQL selecting from dba_data_files, dba_extents (counting free space
in tablespaces?) is using 73% of total buffer gets.
TIA
Peter Laursen
STATSPACK report for
DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
HBMS 3705332073 hbms 1 8.1.7.4.1
Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 2 30-Mar-06 15:04:04 20
End Snap: 5 30-Mar-06 16:12:45 20
Elapsed: 68.68 (mins)
Cache Sizes
~~~~~~~~~~~
db_block_buffers: 256000 log_buffer: 32768
db_block_size: 4096 shared_pool_size: 52428800
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 1,269.13 3,242.46
Logical reads: 5,230.88 13,364.20
Block changes: 5.05 12.89
Physical reads: 12.62 32.23
Physical writes: 16.69 42.64
User calls: 9.73 24.87
Parses: 4.34 11.08
Hard parses: 0.03 0.06
Sorts: 0.62 1.57
Logons: 0.07 0.18
Executes: 5.27 13.48
Transactions: 0.39
% Blocks changed per Read: 0.10 Recursive Call %: 44.42
Rollback per transaction %: 15.07 Rows per Sort: #######
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.76 In-memory Sort %: 99.88
Library Hit %: 99.59 Soft Parse %: 99.42
Execute to Parse %: 17.77 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 94.21 % Non-Parse CPU: 99.93
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 74.56 77.80
% SQL with executions>1: 72.30 72.01
% Memory for SQL w/exec>1: 79.56 76.84
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait %
Total
Event Waits Time (cs) Wt
Time
-------------------------------------------- ------------ ------------ -------
direct path write 263 828
26.13
control file sequential read 15,788 795
25.09
direct path read 681 279
8.80
log file sync 1,624 264
8.33
refresh controlfile command 1,588 261
8.24
-------------------------------------------------------------
Wait Events for DB: HBMS Instance: hbms Snaps: 2 -5
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (cs) (ms)
/txn
---------------------------- ------------ ---------- ----------- ------ ------
direct path write 263 0 828 31
0.2
control file sequential read 15,788 0 795 1
9.8
direct path read 681 0 279 4
0.4
log file sync 1,624 0 264 2
1.0
refresh controlfile command 1,588 0 261 2
1.0
log file parallel write 2,220 0 183 1
1.4
db file sequential read 241 0 177 7
0.1
control file parallel write 1,350 0 158 1
0.8
SQL*Net more data to client 626 0 76 1
0.4
db file parallel write 88 0 75 9
0.1
file open 16 0 63 39
0.0
log buffer space 12 0 7 6
0.0
latch free 8 8 2 3
0.0
SQL*Net break/reset to clien 36 0 1 0
0.0
LGWR wait for redo copy 12 0 0 0
0.0
SQL*Net message from client 29,586 0 3,897,453 1317
18.3
SQL*Net message to client 29,587 0 9 0
18.3
SQL*Net more data from clien 64 0 4 1
0.0
-------------------------------------------------------------
Background Wait Events for DB: HBMS Instance: hbms Snaps: 2 -5
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (cs) (ms)
/txn
---------------------------- ------------ ---------- ----------- ------ ------
control file sequential read 4,764 0 243 1
3.0
log file parallel write 2,221 0 183 1
1.4
control file parallel write 1,350 0 158 1
0.8
db file parallel write 88 0 75 9
0.1
LGWR wait for redo copy 12 0 0 0
0.0
rdbms ipc message 10,077 4,026 2,188,013 2171
6.2
pmon timer 1,341 1,341 412,059 3073
0.8
smon timer 13 13 399,362 ######
0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: HBMS Instance: hbms Snaps: 2 -5
-> 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
Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
15,790,342 779 20,270.0 73.3 3097108327
select a.tablespace_name,a.bytes_full,b.bytes_total,a.bytes_full
/b.bytes_total status, b.bytes_total-a.bytes_full free from (sel
ect tablespace_name,sum(bytes) bytes_full from dba_extents where
tablespace_name='HBMSTAB' group by tablespace_name) a, (select
tablespace_name,sum(bytes) bytes_total from dba_data_files where
1,333,334 1 1,333,334.0 6.2 1996628613
-- Der er bøvl med nedenstående SQL. Hvis udkommenterede del tag
es med, så kører den i en evighed. select dpLev.navn Levera
ndør, dpejer.navn Ejer, dpnet.navn Net, :startdato startd
ate, :slutdato slut, forbrug.aftagenummer Aftagenummer, s
um(forbrug.forbrugmd) Sumafregnet, sum(balanceafregnet.balfor
1,066,510 67 15,918.1 4.9 701734066
SELECT DISTINCT TYPE, MEDDELELSE, MODTAGER, AFSENDER FROM EDIEL_
QUEUE WHERE AFSENDELSESTIDSPUNKT<=SYSDATE AND NVL(BEHANDLES, 0)
= 0 AND TYPE='UTILMD'
1,066,510 67 15,918.1 4.9 3716316431
SELECT DISTINCT TYPE, MODTAGER, AFSENDER, MAALEPUNKTS_ID, SVARST
ATUS, MEDDELELSE FROM EDIEL_QUEUE WHERE AFSENDELSESTIDSPUNKT
<=SYSDATE AND NVL(BEHANDLES, 0) = 0 AND TYPE='MSCONS'
887,196 34 26,094.0 4.1 409177823
select * from ( ( select afrq.kald, afrq.aktoert
ype, afrq.lev_ean, afrq.net_ean, afrq.bal_e
an, afrq.transaktions_id, afrq.maalepunkts_id,
okonomisys.INTERFACE, okonomisys.OKONOMISYS_RECNUM,
afrq.FORBRUG_START, afrq.FORBRUG_Slut, af
370,975 5 74,195.0 1.7 2874588177
SELECT data_provider_edi.data_provider_recnum, A.EDI_MELDING_
RECNUM, B.OPDATERET_DATO, B.MELDING_DATO, A.AFSENDER,
A.ROWID, B.EDI_OVERSIGT_RECNUM, B.BEHANDLET, DECODE(B.BE
HANDLET, 0, 'Nej', 1, 'Ja', 2, 'Fejl', '???') BEHANDLET_TEKST,
B.MELDING_TYPE, DECODE(B.MELDING_TYPE, 1, 'MSCONS', 2, 'DELFOR
200,836 23 8,732.0 0.9 2793185813
SELECT * FROM EDI_OVERSIGT WHERE INDGAAENDE_MELDING=1 AND BEHAND
LET=0 AND MELDING_TYPE IN (1,2,3,4,6,7) ORDER BY OPDATERET_DATO
200,836 23 8,732.0 0.9 4025480556
SELECT COUNT(EDI_OVERSIGT_RECNUM) ANTAL FROM EDI_OVERSIGT WHERE
INDGAAENDE_MELDING=1 AND BEHANDLET=0 AND MELDING_TYPE IN (1,2,3,
4,6,7)
144,517 6 24,086.2 0.7 4127083191
SELECT data_provider_edi.data_provider_recnum, A.EDI_MELDING_
RECNUM, B.OPDATERET_DATO, B.MELDING_DATO, A.AFSENDER,
SQL ordered by Gets for DB: HBMS Instance: hbms Snaps: 2 -5
-> 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
Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
A.ROWID, B.EDI_OVERSIGT_RECNUM, B.BEHANDLET, DECODE(B.BE
HANDLET, 0, 'Nej', 1, 'Ja', 2, 'Fejl', '???') BEHANDLET_TEKST,
B.MELDING_TYPE, DECODE(B.MELDING_TYPE, 1, 'MSCONS', 2, 'DELFOR
105,808 778 136.0 0.5 26831302
SELECT COUNT(GENBEREGN_RECNUM) ANTAL FROM GENBEREGN
92,344 679 136.0 0.4 3202179961
SELECT COUNT(*) ANTAL FROM GENBEREGN
89,670 35 2,562.0 0.4 487356895
select s.run_datetime, s.scheduled_recnum, s.status, p.*,
sysdate from scheduled s, program p where (s.status = 0 or s.st
atus = 3) and run_datetime < sysdate and s.program_recnum = p.pr
ogram_recnum
28,335 2 14,167.5 0.1 3376831664
BEGIN statspack.snap; END;
27,551 242 113.8 0.1 710407842
select 'RecordsExists', 1 from dual where exists(select * from k
ontinuer_forbrug where aftagenummer_recnum = :AFTAGENUM_RECNUM a
nd behandlet <> 5) UNION select 'afl_datoExists',1 from dual w
here exists(select * from kontinuer_forbrug where aftagenummer_r
ecnum = :AFTAGENUM_RECNUM and afl_dato_arm = :PERIODSTART and b
26,209 3 8,736.3 0.1 625421128
INSERT INTO STATS$SQLTEXT ( HASH_VALUE,TEXT_SUBSET,PIECE,SQL_TEX
T,ADDRESS,COMMAND_TYPE,LAST_SNAP_ID ) SELECT ST1.HASH_VALUE,SS.
TEXT_SUBSET,ST1.PIECE,ST1.SQL_TEXT,ST1.ADDRESS,ST1.COMMAND_TYPE,
SS.SNAP_ID FROM V$SQLTEXT ST1,STATS$SQL_SUMMARY SS WHERE SS.S
NAP_ID = :b1 AND SS.DBID = :b2 AND SS.INSTANCE_NUMBER = :b3 A
24,790 67 370.0 0.1 651078757
select dp.data_provider_recnum, dp.navn, dp.kortna
vn, dpe.ean, dpee.ftp_server_adr, dpee.ftp_
server_user, dpee.ftp_server_pwd, dpee.ftp_server_
ud_mappe, dpee.ftp_server_ind_mappe, dpee.ftp_serv
er_passive, dpee.ftp_server_sletfil from data_provider_
19,691 679 29.0 0.1 2202409292
select count(*) from afregningsform_skift
14,280 1 14,280.0 0.1 1889222938
BEGIN statspack.snap(i_snap_level=>10, i_modify_parameter=>'true
'); END;
11,494 2 5,747.0 0.1 1949042064
SELECT * FROM EDIEL_QUEUE WHERE TYPE = :TYPE AND NVL(MEDDELELSE,
'NA') = NVL(:MELDING, 'NA') AND MODTAGER = :MODTAGER AND AFSEND
SQL ordered by Gets for DB: HBMS Instance: hbms Snaps: 2 -5
-> 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
Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
-------------------------------------------------------------
SQL ordered by Reads for DB: HBMS Instance: hbms Snaps: 2 -5
-> End Disk Reads Threshold: 1000
Physical Reads Executions Reads per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
51,751 5 10,350.2 99.5 2874588177
SELECT data_provider_edi.data_provider_recnum, A.EDI_MELDING_
RECNUM, B.OPDATERET_DATO, B.MELDING_DATO, A.AFSENDER,
A.ROWID, B.EDI_OVERSIGT_RECNUM, B.BEHANDLET, DECODE(B.BE
HANDLET, 0, 'Nej', 1, 'Ja', 2, 'Fejl', '???') BEHANDLET_TEKST,
B.MELDING_TYPE, DECODE(B.MELDING_TYPE, 1, 'MSCONS', 2, 'DELFOR
105 242 0.4 0.2 710407842
select 'RecordsExists', 1 from dual where exists(select * from k
ontinuer_forbrug where aftagenummer_recnum = :AFTAGENUM_RECNUM a
nd behandlet <> 5) UNION select 'afl_datoExists',1 from dual w
here exists(select * from kontinuer_forbrug where aftagenummer_r
ecnum = :AFTAGENUM_RECNUM and afl_dato_arm = :PERIODSTART and b
73 6 12.2 0.1 4127083191
SELECT data_provider_edi.data_provider_recnum, A.EDI_MELDING_
RECNUM, B.OPDATERET_DATO, B.MELDING_DATO, A.AFSENDER,
A.ROWID, B.EDI_OVERSIGT_RECNUM, B.BEHANDLET, DECODE(B.BE
HANDLET, 0, 'Nej', 1, 'Ja', 2, 'Fejl', '???') BEHANDLET_TEKST,
B.MELDING_TYPE, DECODE(B.MELDING_TYPE, 1, 'MSCONS', 2, 'DELFOR
50 105 0.5 0.1 365454555
select cols,audit$,textlength,intcols,property,flags,rowid from
view$ where obj#=:1
26 2 13.0 0.1 1949042064
SELECT * FROM EDIEL_QUEUE WHERE TYPE = :TYPE AND NVL(MEDDELELSE,
'NA') = NVL(:MELDING, 'NA') AND MODTAGER = :MODTAGER AND AFSEND
ER = :AFSENDER AND NVL(BEHANDLES, 0) = 0 AND ((:KORREKTUR IS NUL
L) OR (:KORREKTUR=SVARSTATUS)) AND AFSENDELSESTIDSPUNKT<=SYSDAT
E ORDER BY FORBRUG_START
20 87 0.2 0.0 586793284
INSERT INTO KONTINUER_FORBRUG(AFTAGENUMMER_RECNUM, TOTALFORBRUG,
MARKEDSFORBRUG, PERIODE_START_arm, AFL_DATO_arm, QUALITYCODE,ME
SSAGECODE,REASON, EDI_READING, BEHANDLET) VALUES (:AftagenummerR
ECNUM, :TOTFORBRUG, :MARKEL, :PERSTART, :AFLDATO, :QUALITYCODE,:
MESSAGECODE,:REASON, :EDIDATA, -1) RETURNING KONTINUER_FORBRUG_R
6 4 1.5 0.0 4287897799
INSERT INTO EDI_OVERSIGT_INDHOLD(EDI_OVERSIGT_RECNUM, LOKATION_I
D, LOKATION_AGENT, PRODUKT_KODE, PRODUKT_AGENT,
MAALE_ENHED, VALUTA, PERIODE_START_CET, PERIODE
_SLUT_CET, TRANSACTIONS_ID, STA
TUS_CODE, STATUS_REASON) VALUES(:HOVEDRECNUM, :LID, :LAGENT, :PK
3 203 0.0 0.0 3697330621
Begin :lNull := NULL; If :InterfaceT_RecNum = 0 Then :I
nterfaceT_RecNum := :lNull; End If; If :AftageNummer_RecNum
= 0 Then :AftageNummer_RecNum := :lNull; End If; INSERT
INTO LogT (InterfaceT_RecNum, LogT_AppName,
LogT_Function, LogT_TimeStamp_Arm, LogT_Status,
3 203 0.0 0.0 4104279598
INSERT INTO LOGT ( INTERFACET_RECNUM,LOGT_APPNAME,LOGT_FUNCTION,
SQL ordered by Reads for DB: HBMS Instance: hbms Snaps: 2 -5
-> End Disk Reads Threshold: 1000
Physical Reads Executions Reads per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
LOGT_TIMESTAMP_ARM,LOGT_STATUS,LOGT_REVIEWED,LOGT_TEXT,LOGT_COMM
ENT,LOGT_DEBUGINFO,AFTAGENUMMER_RECNUM ) VALUES ( :b1,:b2,:b3,:
b4,:b5,:b6,:b7,:b8,:b9,:b10 )
2 3 0.7 0.0 1428100621
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece fr
om idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
1 8 0.1 0.0 1737259834
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_
obj#, d_owner#, nvl(property,0) from dependency$,obj$ where d_ob
j#=:1 and p_obj#=obj#(+) order by order#
1 290 0.0 0.0 2566287105
INSERT INTO INTERFACET ( INTERFACET_NAME,INTERFACET_TIMESTAMP_AR
M,INTERFACET_METHOD,INTERFACET_PARAMETERLIST,INTERFACET_LASTORDE
RED_ARM,INTERFACET_LASTORDEREDBY,INTERFACET_TRANSID,INTERFACEPAR
AM_RECNUM ) VALUES ( :b1,:b2,:b3,:b4,SYSDATE,:b5,:b6,:b7 ) RE
TURNING INTERFACET_RECNUM INTO :b1
1 290 0.0 0.0 2810298842
Begin :lNull := NULL; If :InterfaceT_TransID = 0 Then :
InterfaceT_TransID := :lNull; End If; INSERT INTO InterfaceT
(InterfaceT_Name, InterfaceT_TimeStamp_Arm,
InterfaceT_Method, InterfaceT_ParameterList,
InterfaceT_LastOrdered_Arm, InterfaceT_LastOr
1 3 0.3 0.0 3111103299
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece fr
om idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
0 778 0.0 0.0 26831302
SELECT COUNT(GENBEREGN_RECNUM) ANTAL FROM GENBEREGN
0 2 0.0 0.0 78333327
select DPE.data_provider_recnum from data_provider_edi DPE,
edi_agent ea where (ea.unb_prefix || DPE.ean) = :EAN
and ea.edi_agent_recnum = DPE.edi_agent_recnum
-------------------------------------------------------------
SQL ordered by Executions for DB: HBMS Instance: hbms Snaps: 2 -5
-> End Executions Threshold: 100
Executions Rows Processed Rows per Exec Hash Value
------------ ---------------- ---------------- ------------
3,291 3,286 1.0 2303881272
SELECT Data_Provider_RecNum FROM Data_Provider_EDI WHERE EAN
= :EAN
1,899 1,899 1.0 2250157270
SELECT AktoerType FROM Data_Provider WHERE Data_Provider_Rec
Num = :AktorRecNum
1,488 1,488 1.0 352114812
SELECT ACTION FROM SUMMERTIME WHERE SUMMERTIME_START <= :b1
AND :b1 < SUMMERTIME_END
1,006 990 1.0 538993685
SELECT Aftagenummer_RecNum FROM Aftagenummer WHERE Aftagenumm
er = :Aftagenummer
779 779 1.0 123865565
Insert Into DBFriPlads Values ( sysdate, :DBFriPlad
s_MB, :DBFriPlads_Pct, :DBFriPlads_FlagKode )
779 779 1.0 2482441449
Delete From DBFriPlads
779 779 1.0 2859784296
Select * From DBOpsaetning
779 779 1.0 3097108327
select a.tablespace_name,a.bytes_full,b.bytes_total,a.bytes_full
/b.bytes_total status, b.bytes_total-a.bytes_full free from (sel
ect tablespace_name,sum(bytes) bytes_full from dba_extents where
tablespace_name='HBMSTAB' group by tablespace_name) a, (select
tablespace_name,sum(bytes) bytes_total from dba_data_files where
778 778 1.0 26831302
SELECT COUNT(GENBEREGN_RECNUM) ANTAL FROM GENBEREGN
690 690 1.0 805219155
SELECT USER FROM DUAL
690 690 1.0 3614774371
SELECT SYSDATE FROM DUAL
679 679 1.0 2202409292
select count(*) from afregningsform_skift
679 679 1.0 3202179961
SELECT COUNT(*) ANTAL FROM GENBEREGN
647 647 1.0 1425443843
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,
order$=:6,cache=:7,highwater=:8,audit$=:9 where obj#=:1
518 516 1.0 1990225685
SELECT * FROM Afregningsform WHERE AftageNummer_RecNum = :Af
tageNummer_RecNum AND :ADate BETWEEN Periode_Start_Arm AND (
SQL ordered by Executions for DB: HBMS Instance: hbms Snaps: 2 -5
-> End Executions Threshold: 100
Executions Rows Processed Rows per Exec Hash Value
------------ ---------------- ---------------- ------------
Periode_Slut_ARM - 1/86400)
377 377 1.0 3415900478
SELECT INTERFACET_CSQ.NEXTVAL FROM DUAL
290 290 1.0 1586015216
SELECT InterfaceParam_RecNum FROM InterfaceParam WHERE Inter
faceParam_List = :List
290 290 1.0 2566287105
INSERT INTO INTERFACET ( INTERFACET_NAME,INTERFACET_TIMESTAMP_AR
M,INTERFACET_METHOD,INTERFACET_PARAMETERLIST,INTERFACET_LASTORDE
RED_ARM,INTERFACET_LASTORDEREDBY,INTERFACET_TRANSID,INTERFACEPAR
AM_RECNUM ) VALUES ( :b1,:b2,:b3,:b4,SYSDATE,:b5,:b6,:b7 ) RE
TURNING INTERFACET_RECNUM INTO :b1
290 290 1.0 2707783677
SELECT INTERFACET_SEQ.NEXTVAL FROM DUAL
290 290 1.0 2810298842
Begin :lNull := NULL; If :InterfaceT_TransID = 0 Then :
InterfaceT_TransID := :lNull; End If; INSERT INTO InterfaceT
(InterfaceT_Name, InterfaceT_TimeStamp_Arm,
InterfaceT_Method, InterfaceT_ParameterList,
InterfaceT_LastOrdered_Arm, InterfaceT_LastOr
286 617 2.2 3013728279
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with (grantee#=:1 or grantee#=
1) and privilege#>0
285 285 1.0 1005331575
select user# from sys.user$ where name = 'OUTLN'
283 0 0.0 215825094
ALTER SESSION SET NLS_LANGUAGE= 'DANISH' NLS_TERRITORY= 'DENMARK
' NLS_CURRENCY= 'Kr' NLS_ISO_CURRENCY= 'DENMARK' NLS_NUMERIC_CHA
RACTERS= ',.' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'RR-MM-
DD' NLS_DATE_LANGUAGE= 'DANISH' NLS_SORT= 'DANISH' TIME_ZONE= '
+02:00' NLS_DUAL_CURRENCY = '¿' NLS_TIME_FORMAT = 'HH24:MI:SSXFF
242 329 1.4 710407842
select 'RecordsExists', 1 from dual where exists(select * from k
ontinuer_forbrug where aftagenummer_recnum = :AFTAGENUM_RECNUM a
nd behandlet <> 5) UNION select 'afl_datoExists',1 from dual w
here exists(select * from kontinuer_forbrug where aftagenummer_r
ecnum = :AFTAGENUM_RECNUM and afl_dato_arm = :PERIODSTART and b
242 242 1.0 1414728989
select * from aftagenummer where aftagenummer = :aftagenummer
203 203 1.0 532312518
SELECT LOGT_SEQ.NEXTVAL FROM DUAL
203 203 1.0 625411663
SQL ordered by Executions for DB: HBMS Instance: hbms Snaps: 2 -5
-> End Executions Threshold: 100
Executions Rows Processed Rows per Exec Hash Value
------------ ---------------- ---------------- ------------
SELECT LOGT_CSQ.NEXTVAL FROM DUAL
203 203 1.0 3697330621
Begin :lNull := NULL; If :InterfaceT_RecNum = 0 Then :I
nterfaceT_RecNum := :lNull; End If; If :AftageNummer_RecNum
= 0 Then :AftageNummer_RecNum := :lNull; End If; INSERT
INTO LogT (InterfaceT_RecNum, LogT_AppName,
LogT_Function, LogT_TimeStamp_Arm, LogT_Status,
203 203 1.0 4104279598
INSERT INTO LOGT ( INTERFACET_RECNUM,LOGT_APPNAME,LOGT_FUNCTION,
-------------------------------------------------------------
Instance Activity Stats for DB: HBMS Instance: hbms Snaps: 2 -5
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
background checkpoints completed 0 0.0 0.0
background checkpoints started 0 0.0 0.0
background timeouts 4,059 1.0 2.5
branch node splits 0 0.0 0.0
buffer is not pinned count 15,847,799 3,845.6 9,825.1
buffer is pinned count 182,937,966 44,391.6 113,414.7
bytes received via SQL*Net from c 6,286,029 1,525.4 3,897.1
bytes sent via SQL*Net to client 7,683,011 1,864.4 4,763.2
calls to get snapshot scn: kcmgss 70,534 17.1 43.7
calls to kcmgas 2,192 0.5 1.4
calls to kcmgcs 317 0.1 0.2
change write time 35 0.0 0.0
cleanouts and rollbacks - consist 0 0.0 0.0
cleanouts only - consistent read 130 0.0 0.1
cluster key scan block gets 6,236,659 1,513.4 3,866.5
cluster key scans 4,454,260 1,080.9 2,761.5
commit cleanout failures: block l 0 0.0 0.0
commit cleanout failures: buffer 0 0.0 0.0
commit cleanout failures: callbac 5 0.0 0.0
commit cleanout failures: cannot 0 0.0 0.0
commit cleanouts 4,455 1.1 2.8
commit cleanouts successfully com 4,450 1.1 2.8
consistent changes 21 0.0 0.0
consistent gets 19,528,541 4,738.8 12,107.0
CPU used by this session 251,998 61.2 156.2
CPU used when call started 251,998 61.2 156.2
CR blocks created 12 0.0 0.0
cursor authentications 60 0.0 0.0
data blocks consistent reads - un 21 0.0 0.0
db block changes 20,798 5.1 12.9
db block gets 2,027,906 492.1 1,257.2
DBWR checkpoint buffers written 1,984 0.5 1.2
DBWR checkpoints 0 0.0 0.0
DBWR revisited being-written buff 0 0.0 0.0
DBWR transaction table writes 18 0.0 0.0
DBWR undo block writes 954 0.2 0.6
deferred (CURRENT) block cleanout 3,305 0.8 2.1
enqueue conversions 0 0.0 0.0
enqueue releases 897,706 217.8 556.5
enqueue requests 897,705 217.8 556.5
execute count 21,737 5.3 13.5
free buffer inspected 0 0.0 0.0
free buffer requested 2,004 0.5 1.2
hot buffers moved to head of LRU 5,724 1.4 3.6
immediate (CR) block cleanout app 130 0.0 0.1
immediate (CURRENT) block cleanou 661 0.2 0.4
index fast full scans (full) 0 0.0 0.0
leaf node splits 166 0.0 0.1
logons cumulative 286 0.1 0.2
logons current
messages received 3,916 1.0 2.4
messages sent 3,916 1.0 2.4
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 14,032,268 3,405.1 8,699.5
opened cursors cumulative 17,873 4.3 11.1
parse count (hard) 104 0.0 0.1
Instance Activity Stats for DB: HBMS Instance: hbms Snaps: 2 -5
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
parse count (total) 17,874 4.3 11.1
parse time cpu 179 0.0 0.1
parse time elapsed 190 0.1 0.1
physical reads 51,992 12.6 32.2
physical reads direct 51,751 12.6 32.1
physical writes 68,771 16.7 42.6
physical writes direct 66,787 16.2 41.4
physical writes non checkpoint 68,568 16.6 42.5
prefetched blocks 0 0.0 0.0
process last non-idle time 325,964,277,021 ############ ############
recovery array read time 0 0.0 0.0
recovery array reads 0 0.0 0.0
recovery blocks read 0 0.0 0.0
recursive calls 32,056 7.8 19.9
recursive cpu usage 344 0.1 0.2
redo blocks written 11,494 2.8 7.1
redo buffer allocation retries 12 0.0 0.0
redo entries 10,893 2.6 6.8
redo log space requests 0 0.0 0.0
redo log space wait time 0 0.0 0.0
redo size 5,230,088 1,269.1 3,242.5
redo synch time 264 0.1 0.2
redo synch writes 1,969 0.5 1.2
redo wastage 480,192 116.5 297.7
redo write time 351 0.1 0.2
redo writer latching time 0 0.0 0.0
redo writes 2,221 0.5 1.4
rollback changes - undo records a 41 0.0 0.0
rollbacks only - consistent read 12 0.0 0.0
rows fetched via callback 1,040,637 252.5 645.2
session connect time 325,964,277,021 ############ ############
session logical reads 21,556,451 5,230.9 13,364.2
session pga memory 46,788,432 11,353.7 29,007.1
session pga memory max 56,102,644 13,613.8 34,781.6
session uga memory max 30,859,836 7,488.4 19,132.0
sorts (disk) 3 0.0 0.0
sorts (memory) 2,532 0.6 1.6
sorts (rows) 7,308,175 1,773.4 4,530.8
SQL*Net roundtrips to/from client 28,731 7.0 17.8
switch current to new buffer
table fetch by rowid 88,449,765 21,463.2 54,835.6
table fetch continued row 9,084 2.2 5.6
table scan blocks gotten 3,458,809 839.3 2,144.3
table scan rows gotten 2,950,100 715.9 1,829.0
table scans (long tables) 1 0.0 0.0
table scans (short tables) 23,174 5.6 14.4
total file opens 16 0.0 0.0
transaction rollbacks 1 0.0 0.0
transaction tables consistent rea 0 0.0 0.0
transaction tables consistent rea 0 0.0 0.0
user calls 40,115 9.7 24.9
user commits 1,370 0.3 0.9
user rollbacks 243 0.1 0.2
write clones created in foregroun 2 0.0 0.0
-------------------------------------------------------------
Tablespace IO Stats for DB: HBMS Instance: hbms Snaps: 2 -5
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
TEMP
922 0 ###### 56.1 525 0 0
0.0
RBS
0 0 0.0 972 0 0
0.0
HBMSTAB
235 0 7.2 1.0 609 0 0
0.0
SYSTEM
6 0 13.3 1.0 403 0 0
0.0
-------------------------------------------------------------
File IO Stats for DB: HBMS Instance: hbms Snaps: 2 -5
->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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
HBMSTAB D:\EG\DATABASE\HBTAB01.DBF
16 0 1.9 1.0 0 0 0
D:\EG\DATABASE\HBTAB03.DBF
4 0 5.0 1.0 0 0 0
D:\EG\DATABASE\HBTAB04.DBF
2 0 25.0 1.0 2 0 0
D:\EG\DATABASE\HBTAB06.DBF
6 0 10.0 1.0 0 0 0
D:\EG\DATABASE\HBTAB07.DBF
33 0 10.9 1.0 0 0 0
D:\EG\DATABASE\HBTAB08.DBF
31 0 8.1 1.0 0 0 0
D:\EG\DATABASE\HBTAB09.DBF
94 0 5.2 1.0 315 0 0
D:\EG\DATABASE\HBTAB10.DBF
33 0 6.1 1.0 156 0 0
D:\EG\DATABASE\HBTAB11.DBF
16 0 14.4 1.0 136 0 0
RBS D:\EG\DATABASE\RBS01.DBF
0 0 222 0 0
D:\EG\DATABASE\RBS02.DBF
0 0 750 0 0
SYSTEM D:\EG\DATABASE\SYSTEM01.DBF
6 0 13.3 1.0 403 0 0
TEMP D:\EG\DATABASE\TEMP02.DBF
922 0 ###### 56.1 525 0 0
-------------------------------------------------------------
Buffer Pool Statistics for DB: HBMS Instance: hbms Snaps: 2 -5
-> Pools D: default pool, K: keep pool, R: recycle pool
Free Write
Buffer
Buffer Consistent Physical Physical Buffer Complete
Busy
P Gets Gets Reads Writes Waits Waits
Waits
- ----------- ------------- ----------- ---------- ------- -------- ----------
D 1,999 14,491,418 241 1,984 0 0
0
-------------------------------------------------------------
Rollback Segment Stats for DB: HBMS Instance: hbms Snaps: 2 -5
->A high value for "Pct Waits" suggests more rollback segments may be
required
Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks Extends
------ ------------ ------- --------------- -------- -------- --------
0 18.0 0.00 0 0 0 0
2 6,963.0 0.00 432,902 0 0 0
3 2,086.0 0.00 73,216 0 0 0
4 6,939.0 0.00 403,618 1 0 0
5 3,589.0 0.00 71,498 0 0 0
6 6,706.0 0.00 68,206 0 0 0
7 5,402.0 0.00 412,064 0 0 0
8 8,262.0 0.00 69,714 0 0 0
9 6,700.0 0.00 65,836 0 0 0
10 3,588.0 0.00 65,550 0 0 0
-------------------------------------------------------------
Rollback Segment Storage for DB: HBMS Instance: hbms Snaps: 2 -5
->Optimal Size should be larger than Avg Active
RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 794,624 0 794,624
2 134,213,632 2,956,441 134,217,728 134,213,632
3 134,213,632 3,169,284 134,217,728 134,213,632
4 134,209,536 10,580,475 134,217,728 134,209,536
5 134,213,632 3,562,851 134,217,728 134,213,632
6 87,027,712 955,636 134,217,728 87,027,712
7 101,707,776 10,334,209 134,217,728 101,707,776
8 134,213,632 955,636 134,217,728 134,213,632
9 134,213,632 986,044 134,217,728 134,213,632
10 134,213,632 964,930 134,217,728 134,213,632
-------------------------------------------------------------
Latch Activity for DB: HBMS Instance: hbms Snaps: 2 -5
->"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
Pct
Get Get Slps NoWait
NoWait
Latch Name Requests Miss /Miss Requests
Miss
----------------------------- -------------- ------ ------ ------------ ------
active checkpoint queue latch 1,429 0.0 0
cache buffer handles 360,610 0.0 0.0 0
cache buffers chains 38,268,048 0.0 0.0 1,212
0.0
cache buffers lru chain 15,756 0.0 0.0 1,207
0.0
channel handle pool latch 572 0.0 0
channel operations parent lat 858 0.0 0
checkpoint queue latch 14,406 0.0 0.0 0
dml lock allocation 4,391 0.0 0
enqueue hash chains 1,795,340 0.0 0.0 0
enqueues 3,585,124 0.0 0.0 0
event group latch 286 0.0 0
file number translation table 3 0.0 0
job_queue_processes parameter 64 0.0 0
ktm global data 13 0.0 0
library cache 390,235 0.0 0.0 0
library cache load lock 120 0.0 0
list of block allocation 4,414 0.0 0
loader state object freelist 6 0.0 0
longop free list 2 0.0 0
messages 24,897 0.0 0.0 0
ncodef allocation latch 64 0.0 0
process allocation 286 0.0 286
0.0
process group creation 572 0.0 0
redo allocation 15,881 0.1 0.1 0
redo writing 14,708 0.7 0.0 0
row cache objects 5,447,385 0.0 0.0 0
sequence cache 4,401 0.0 0
session allocation 20,672 0.0 0
session idle bit 83,266 0.0 0
session switching 64 0.0 0
shared pool 55,436 0.0 0.0 0
sort extent pool 99 0.0 0
Token Manager 16 0.0 0
transaction allocation 8,865 0.0 0
transaction branch allocation 64 0.0 0
undo global data 52,813 0.0 0
user lock 1,140 0.0 0
-------------------------------------------------------------
Latch Sleep breakdown for DB: HBMS Instance: hbms Snaps: 2 -5
-> ordered by misses desc
Get Spin &
Latch Name Requests Misses Sleeps Sleeps
1->4
-------------------------- -------------- ----------- ----------- ------------
cache buffers chains 38,268,048 104 4
102/0/2/0/0
redo writing 14,708 100 2 98/2/0/0/0
redo allocation 15,881 17 2 15/2/0/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: HBMS Instance: hbms Snaps: 2 -5
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait
Waiter
Latch Name Where Misses Sleeps
Sleeps
------------------------ -------------------------- ------- ---------- -------
cache buffers chains kcbgtcr: kslbegin 0 4
4
redo allocation kcrfwi: before write 0 2
0
redo writing kcrfsr 0 2
0
-------------------------------------------------------------
Parent Latch Statistics DB: HBMS Instance: hbms Snaps: 2 -5
-> only latches with sleeps are shown
-> ordered by name
Get Spin &
Latch Name Requests Misses Sleeps Sleeps 1->4
------------------------ -------------- ----------- ---------- ------------
redo allocation 15,763 15 2 13/2/0/0/0
redo writing 14,687 100 2 98/2/0/0/0
-------------------------------------------------------------
Child Latch Statistics DB: HBMS Instance: hbms Snaps: 2 -5
-> only latches with sleeps are shown
-> ordered by name, gets desc
Child Get
Latch Name Num Requests Misses Sleeps
---------------------- ------ -------------- ----------- ----------
Spin &
Sleeps 1->4
-------------
cache buffers chains 408 150,008 2 2
1/0/1/0/0
cache buffers chains 1798 9,573 1 2
0/0/1/0/0
-------------------------------------------------------------
Dictionary Cache Stats for DB: HBMS Instance: hbms Snaps: 2 -5
->"Pct Misses" should be very low (< 2% in most cases)
->"Cache Usage" is the number of cache entries being used
->"Pct SGA" is the ratio of usage to allocated size for that cache
Get Pct Scan Pct Mod Final
Pct
Cache Requests Miss Requests Miss Req Usage
SGA
---------------------- ------------ ------ -------- ----- -------- ------ ----
dc_constraints 0 0 0 252
98
dc_database_links 0 0 0 0
0
dc_files 10,127 0.0 0 0 13
62
dc_free_extents 65 0.0 0 0 6
60
dc_global_oids 0 0 0 0
0
dc_histogram_data 0 0 0 0
0
dc_histogram_data_valu 0 0 0 0
0
dc_histogram_defs 299 30.4 0 0 205
99
dc_object_ids 699 0.0 0 0 850
100
dc_objects 914 2.6 0 0 1,513
100
dc_outlines 0 0 0 0
0
dc_profiles 286 0.0 0 0 1
20
dc_rollback_segments 286 0.0 0 0 12
86
dc_segments 892,027 0.0 0 0 1,219
100
dc_sequence_grants 0 0 0 4
80
dc_sequences 647 0.0 0 647 76
85
dc_synonyms 190 3.7 0 0 438
98
dc_tablespace_quotas 24 0.0 0 6 6
26
dc_tablespaces 891,241 0.0 0 0 4
40
dc_used_extents 0 0 0 25
78
dc_user_grants 13,008 0.0 0 0 29
62
dc_usernames 849 0.1 0 0 23
79
dc_users 13,907 0.0 0 0 30
68
ifs_acl_cache_entries 0 0 0 0
0
-------------------------------------------------------------
Library Cache Activity for DB: HBMS Instance: hbms Snaps: 2 -5
->"Pct Misses" should be very low
Get Pct Pin Pct
Invali-
Namespace Requests Miss Requests Miss Reloads
dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 19 5.3 19 5.3 0
0
CLUSTER 61 0.0 53 0.0 0
0
INDEX 0 0 0
0
OBJECT 0 0 0
0
PIPE 0 0 0
0
SQL AREA 14,702 0.6 59,893 0.4 3
0
TABLE/PROCEDURE 1,638 2.7 4,865 1.2 0
0
TRIGGER 1,372 0.0 1,372 0.0 0
0
-------------------------------------------------------------
SGA Memory Summary for DB: HBMS Instance: hbms Snaps: 2 -5
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 1,048,576,000
Fixed Size 75,804
Redo Buffers 77,824
Variable Size 94,031,872
----------------
sum 1,142,761,500
-------------------------------------------------------------
SGA breakdown difference for DB: HBMS Instance: hbms Snaps: 2 -5
Pool Name Begin value End value
Difference
----------- ------------------------ -------------- -------------- -----------
java pool free memory 32,768 32,768
0
large pool free memory 614,400 614,400
0
shared pool character set object 43,964 43,964
0
shared pool db_block_buffers 34,816,000 34,816,000
0
shared pool db_block_hash_buckets 4,358,216 4,358,216
0
shared pool db_handles 50,000 50,000
0
shared pool dictionary cache 1,915,832 1,960,360
44,528
shared pool DML locks 96,000 96,000
0
shared pool enqueue_resources 66,240 66,240
0
shared pool event statistics per ses 395,600 395,600
0
shared pool fixed allocation callbac 960 960
0
shared pool free memory 23,751,708
20,725,356 -3,026,352
shared pool KGFF heap 12,420 12,420
0
shared pool KGK heap 2,772 2,772
0
shared pool KQLS heap 4,262,732 4,273,508
10,776
shared pool ktlbk state objects 53,928 53,928
0
shared pool latch nowait fails or sl 37,632 37,632
0
shared pool library cache 6,911,432 7,449,792
538,360
shared pool long op statistics array 50,600 50,600
0
shared pool message pool freequeue 124,552 124,552
0
shared pool miscellaneous 555,464 592,640
37,176
shared pool PLS non-lib hp 2,096 2,096
0
shared pool PL/SQL DIANA 3,277,152 3,289,332
12,180
shared pool PL/SQL MPCODE 690,592 729,148
38,556
shared pool processes 80,800 80,800
0
shared pool sessions 247,940 247,940
0
shared pool simulator trace entries 40,000 40,000
0
shared pool sql area 10,975,732 13,357,524
2,381,792
shared pool State objects 130,384 130,384
0
shared pool SYSTEM PARAMETERS 63,384 63,384
0
shared pool table columns 53,060 53,060
0
shared pool table definiti 22,336 22,496
160
shared pool transactions 112,392 112,392
0
shared pool trigger defini 101,668 101,668
0
shared pool trigger inform 720 720
0
shared pool view columns d 24,288 24,288
0
db_block_buffers 1,048,576,000 1,048,576,000
0
fixed_sga 75,804 75,804
0
log_buffer 66,560 66,560
0
-------------------------------------------------------------
init.ora Parameters for DB: HBMS Instance: hbms Snaps: 2 -5
End value
Parameter Name Begin value (if
different)
----------------------------- --------------------------------- --------------
compatible 8.1.0
control_files d:\eg\Database\hbmsora1.ctl, c:\e
db_block_buffers 256000
db_block_size 4096
db_file_multiblock_read_count 32
db_files 100
db_name HBMS
dml_locks 800
global_names TRUE
java_pool_size 32768
large_pool_size 614400
log_archive_dest E:\Oracle\Archive
log_archive_format arch%S.arc
log_archive_start TRUE
log_buffer 32768
log_checkpoint_interval 12000
max_dump_file_size 1024000
open_cursors 300
optimizer_mode RULE
processes 100
remote_login_passwordfile SHARED
remote_os_authent TRUE
rollback_segments rbs1, rbs2, rbs3, rbs4, rbs5, rbs
shared_pool_size 52428800
sort_area_size 10485760
timed_statistics TRUE
-------------------------------------------------------------
End of Report
Jonathan Lewis
"Peter Laursen" <p...@REMOVEedbgruppenTHIS.dk> wrote in message
news:442c039a$0$11696$ba62...@nntp02.dk.telia.net...
> Hi,
>
> Users complain about a recent drop in response times. I did a statspack
> report. Would someone take a peek and see if there is something unusual in
> it?
>
> Snaps were made about an hour apart. During that hour i used one of the
> application functions, that users report are now suddenly slower. This
> function basicly runs one sql which then used 99,5% of total reads in that
> hour.
> Another SQL selecting from dba_data_files, dba_extents (counting free
> space in tablespaces?) is using 73% of total buffer gets.
>
> TIA
> Peter Laursen
>
One thing that is clear is that you are doing
a reasonable amount of work in the hour and
a quarter - v$sysstat shows
61 centisecond per second CPU.
(of course, we don't know how many CPUs
you have - but maybe it's just the one).
But the most anomalous figures are:
> enqueue releases 897,706 217.8
> 556.5
> enqueue requests 897,705 217.8
> 556.5
coupled with calls on v$rowcache
> dc_segments 892,027 0.0 0 0 1,219
> dc_tablespaces 891,241 0.0 0 0 4
At a guess, you have an object that is growing (and possibly shrinking)
very rapidly in a tablespace declared with a very small extent size, I
can't think of any other reason why these figures might be so extreme.
--
Regards
Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Peter Laursen
> 61 centisecond per second CPU.
> (of course, we don't know how many CPUs
> you have - but maybe it's just the one).
its 4 cpus's, but the server also hosts a number of automated programs that
access the database.
> But the most anomalous figures are:
>> enqueue releases 897,706 217.8 556.5
>> enqueue requests 897,705 217.8 556.5
> coupled with calls on v$rowcache
>> dc_segments 892,027 0.0 0 0 1,219
>> dc_tablespaces 891,241 0.0 0 0 4
>
> At a guess, you have an object that is growing (and possibly shrinking)
> very rapidly in a tablespace declared with a very small extent size, I
> can't think of any other reason why these figures might be so extreme.
Hmm, could that be in temp tablespace? I think its a locally managed uniform
1M or 4M extents. I will look at it tomorrow. How could I find out what
object that is growing and/or shrinking fast?
TIA
Peter Laursen
Jonathan Lewis
It could be a temporary tablespace - but only
if you have a permanent tablespace with very
small extents assigned as the temporary tablespace;
and it's not really consistent with your statistics
and parameters for sorting.
Perhaps the next place to look is x$ksqst (logged
on as SYS) to find out which lock type (ksqsttyp
is the really busy one. You'll have to do take a
couple of snapshots of this object at a critical
period to get some useful numbers. (In 9i, this
x$ is exposed as v$enqueue_stat - but you don't
have that in 8i).
oracle_doc
--------------- ------------ -------------- ------- ------------
15,790,342 779 20,270.0 73.3 3097108327
select a.tablespace_name,a.bytes_full,b.bytes_total,a.bytes_full
/b.bytes_total status, b.bytes_total-a.bytes_full free from (sel
ect tablespace_name,sum(bytes) bytes_full from dba_extents where
tablespace_name='HBMSTAB' group by tablespace_name) a, (select
tablespace_name,sum(bytes) bytes_total from dba_data_files where
1,333,334 1 1,333,334.0 6.2 1996628613
-- Der er bøvl med nedenstående SQL. Hvis udkommenterede del tag
es med, så kører den i en evighed. select dpLev.navn Levera
ndør, dpejer.navn Ejer, dpnet.navn Net, :startdato startd
ate, :slutdato slut, forbrug.aftagenummer Aftagenummer, s
um(forbrug.forbrugmd) Sumafregnet, sum(balanceafregnet.balfor
Clearly, these two SQLs are your main problem.
About the first query - it does not look like a recursive query (one
fired by SYS) to me because the SQL is using a literal like -
tablespace_name='HBMSTAB
Recursive SQLs are always bound.
Do you have any job that's running regularly and could fire this SQL?
Or may be some monitoring tool? It was executed 779 times in 68 mins 68
secs -- average interval for execution is 5.32 secs.
oracle_doc
You can do so by querying -
select sql_text from v$sqltext where hash_value =3097108327 order by
piece ;
(or, alternately find out the hash_value after querying V$SQLAREA for
the SQL doing most "buffer_gets" and then find the full_text)
This structure looks odd to me -
select
a.tablespace_name,a.bytes_full,b.bytes_total,a.bytes_full/b.bytes_total
status, b.bytes_total-a.bytes_full free from
(select tablespace_name,sum(bytes) bytes_full from dba_extents where
tablespace_name='HBMSTAB' group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes_total from dba_data_files
If we know tablespace name - the scalar sub-queries can just be -
select sum(bytes) bytes_full from dba_extents where
tablespace_name='HBMSTAB'
rather than
select tablespace_name,sum(bytes) bytes_full from dba_extents where
Peter Laursen
"oracle_doc" <nil...@nilendu.com> skrev i en meddelelse
news:1143833455.8...@v46g2000cwv.googlegroups.com...
Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
15,790,342 779 20,270.0 73.3 3097108327
select a.tablespace_name,a.bytes_full,b.bytes_total,a.bytes_full
Do you have any job that's running regularly and could fire this SQL?
Or may be some monitoring tool? It was executed 779 times in 68 mins 68
secs -- average interval for execution is 5.32 secs.
the full query is here
http://www.orafaq.com/usenet/comp.databases.oracle.server/2002/09/26/1124.htm
It has been part of an application tool for many years. I dont know why it
would suddenly become a problem, but executing it 10 times per minute is
surely overkill :-)
Do you think this sql could take up so many resources that it could slow
down all other database queries?
TIA
Peter Laursen
Jonathan Lewis
This looks like some sort of home-grown (or 3rd party)
query to check whether freespace in the tablespace is
close to the limit.
Is it possible that you've made a recent structural change
to the tablespace that could be related - e.g. changed
data files to autoextent, rebuilt the tablespace as an
LMT with small extent size ...