Hi,
From our DB Customer, it's Complaint that why database monitoring graphs are rising from the last 2-3 months. For that purpose right now I've generated two Statspack reports, i.e., 12am-8am and 3pm-11pm just to compare and see if there is any difference during the day and night activities.
Below are the sections of Statspack report - individually and also for the comparison between two time periods. Please have a look and suggest what is wrong and what to do next, and please ask me to put more section(s) of the Statspack because it doesn't look great to paste all the report.
DB (11.2.0.2), OS (RHL). crontab bakup db all at 5am,statspack purge at 6am,clearn traces at 7am. graphical monitoring says CPU load on few of its cores, I/o traffic, disk traffic,and disk operations rising.
Thanks a lot in advance.
Regards,
## Day time
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 12131 07-Mar-12 15:00:01 260 10.7
End Snap: 12147 07-Mar-12 23:00:03 245 10.8
Elapsed: 480.03 (mins) Av Act Sess: 0.5
DB time: 256.24 (mins) DB CPU: 216.33 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 3,168M Std Block Size: 8K
Shared Pool: 6,464M Log Buffer: 6,848K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 0.5 0.0 0.00 0.00
DB CPU(s): 0.5 0.0 0.00 0.00
Redo size: 93,191.8 2,928.7
Logical reads: 21,211.6 666.6
Block changes: 747.5 23.5
Physical reads: 21.8 0.7
Physical writes: 8.1 0.3
User calls: 1,830.4 57.5
Parses: 834.4 26.2
Hard parses: 9.4 0.3
W/A MB processed: 23.0 0.7
Logons: 0.2 0.0
Executes: 991.4 31.2
Rollbacks: 2.4 0.1
Transactions: 31.8
## Evening/night time
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 12148 08-Mar-12 00:00:01 243 10.8
End Snap: 12164 08-Mar-12 08:00:00 234 10.5
Elapsed: 479.98 (mins) Av Act Sess: 0.4
DB time: 185.89 (mins) DB CPU: 171.47 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 3,168M Std Block Size: 8K
Shared Pool: 6,464M Log Buffer: 6,848K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 0.4 0.0 0.00 0.00
DB CPU(s): 0.4 0.0 0.00 0.00
Redo size: 63,514.7 2,071.1
Logical reads: 9,065.9 295.6
Block changes: 564.1 18.4
Physical reads: 183.2 6.0
Physical writes: 6.8 0.2
User calls: 1,336.0 43.6
Parses: 602.3 19.6
Hard parses: 15.4 0.5
W/A MB processed: 34.4 1.1
Logons: 0.1 0.0
Executes: 813.5 26.5
Rollbacks: 1.4 0.0
Transactions: 30.7
## Day time
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 11,048 76.7
buffer busy waits 150,385 832 6 5.8
enq: TX - row lock contention 418 554 1326 3.8
log file sync 874,634 413 0 2.9
enq: UL - contention 108,721 337 3 2.3
-------------------------------------------------------------
## Evening/night time
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 8,937 81.2
log file sync 885,751 537 1 4.9
log file parallel write 962,442 412 0 3.7
db file scattered read 118,060 341 3 3.1
RMAN backup & recovery I/O 55,892 176 3 1.6
## Day time
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
buffer busy waits 150,371 0 832 6 0.2 5.8
enq: TX - row lock contentio 418 0 554 1326 0.0 3.8
log file sync 874,627 0 413 0 1.0 2.9
enq: UL - contention 108,721 0 337 3 0.1 2.3
latch: cache buffers chains 151,306 0 160 1 0.2 1.1
control file sequential read 1,978,844 0 136 0 2.2 .9
db file sequential read 253,914 0 128 1 0.3 .9
SQL*Net more data to client 14,350,767 0 113 0 15.7 .8
latch: In memory undo latch 63,481 0 81 1 0.1 .6
db file scattered read 12,711 0 31 2 0.0 .2
## Evening/night time
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
log file sync 885,742 0 537 1 1.0 4.9
db file scattered read 116,736 0 339 3 0.1 3.1
db file sequential read 358,704 0 149 0 0.4 1.4
control file sequential read 1,987,744 0 138 0 2.3 1.3
SQL*Net more data to client 5,700,506 0 48 0 6.5 .4
enq: TX - row lock contentio 12 0 21 1723 0.0 .2
buffer busy waits 3,622 0 18 5 0.0 .2
SQL*Net break/reset to clien 80,892 0 11 0 0.1 .1
direct path read 1,751 0 10 6 0.0 .1
enq: UL - contention 2,374 0 7 3 0.0 .1
## Day time
^LSQL ordered by CPU DB/Inst: TPA/tpa Snaps: 12131-12147
-> Total DB CPU (s): 12,980
-> Captured SQL accounts for 47.2% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
429.37 267,384 0.00 3.3 428.94 106,272,989 2466708206
Module: itoscrsbatch.exe
SELECT CS.DATABASEID , CS.TYPE_OF_DIFFERENTIATION_1 , CS.TYPE_OF
_DIFFERENTIATION_2 , CS.VALID_FROM , CS.VALID_TO , ABS(:b1 - CS
.VALID_FROM ) + ABS(:b1 - CS.VALID_TO ) BOOKING_DIFF , CR
O.SERVICE_TYPE , CRO.SERVICE_CODE , CRO.SERVICE_CATEGORY FROM CA
298.22 1,147,685 0.00 2.3 284.01 3,436,406 56536927
Module: itoscrsbatch.exe
SELECT mandant,leistungsart_code,bezeichnung_txt_id,einzelverkau
f_intern_kz,einzelverkauf_extern_kz,stornierbar_kz,hin_rueck_kz,
tag_nacht_kz,termin_art_kz,preis_einheit_kz,preis_zeit_kz,saison
_zustieg_abhaengig_kz,zahlung_sofort,teilbelegung_kz,personen_mi
288.08 1,147,699 0.00 2.2 285.87 1,151,388 701852296
Module: itoscrsbatch.exe
SELECT mandant,typ,leistung_code,kurz_bezeichnung,bezeichnung_1_
txt_id,bezeichnung_2_txt_id,e_kategorie,option_frist,teilnehmer_
min,teilnehmer_max,termin_art_kz,hinweis_kz,e_zu_ausstieg_kz,e_a
visierung_sprache,e_waehrung_ek,prosp_id,storno_vk_id,storno_ek_
252.32 974,294 0.00 1.9 251.11 1,953,413 792302957
Module: itoscrsbatch.exe
SELECT mandant,leigrp_detailliert_id,package_code,package_name,l
eiart_id,leigrp_id,prospekt_code,prosp_id,typ,leistung_code,leis
tungsart_code,leiart_ek_preiscode_id,leiart_vk_preiscode_id,leis
t_ek_preiscode_id,leist_vk_preiscode_id,e_kategorie,e_waehrung_e
240.21 588,782 0.00 1.9 240.90 5,257,306 3933669001
Module: XMLServer_sol@ocean-app1 (TNS V1-V3)
SELECT /*+ INDEX (vacancy_price_fit vacancy_price_fit_perf_i)
*/ DISTINCT VP.LEIART_ID , VP.CUSTOMER_PRICEGROUP_ID , VP.PRICE_
TYPE , VP.PRICE_UNIT_FLAG , DECODE(VP.PRICE_TYPE , :b1 , NULL
, VP.CATERING ) CATERING_COL , VP.VALID_FROM , VP.VALID_TO , VP
228.95 277 0.83 1.8 229.28 38,383,929 637757003
Module: timer.exe
update TimerActionHistory set FinishedAt=SysDate,Status=:b0,Exi
tCode=:b1,OutputLog=:b2 where DatabaseID=:b3
228.80 277 0.83 1.8 229.11 38,377,757 1628429523
Module: timer.exe
SELECT ROWID FROM PROTOKOLLDATEN D WHERE REFID_1 = :B1 AND PROTO
KOLLKOPF_ID IN (SELECT DATABASEID FROM PROTOKOLLKOPF WHERE IDENT
IFIER = :B2 )
## Evening/night time
^LSQL ordered by CPU DB/Inst: TPA/tpa Snaps: 12148-12164
-> Total DB CPU (s): 10,288
-> Captured SQL accounts for 27.3% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
435.90 833,800 0.00 4.2 439.14 12,301,522 3933669001
Module: XMLServer_sol@ocean-app1 (TNS V1-V3)
SELECT /*+ INDEX (vacancy_price_fit vacancy_price_fit_perf_i)
*/ DISTINCT VP.LEIART_ID , VP.CUSTOMER_PRICEGROUP_ID , VP.PRICE_
TYPE , VP.PRICE_UNIT_FLAG , DECODE(VP.PRICE_TYPE , :b1 , NULL
, VP.CATERING ) CATERING_COL , VP.VALID_FROM , VP.VALID_TO , VP
397.82 458,807 0.00 3.9 412.99 35,074,404 500334621
Module: XMLServer_sol@ocean-app1 (TNS V1-V3)
SELECT mandant,lgrp_id,leiart_id,kont_limit,kont_id,verkauft,opt
ionen,anfragen,out_allotment,kundengruppe_id,no_of_adults,single
_sale_flag,DatabaseId FROM kontlimit WHERE (mandant=:"SYS_B_0")
AND (((leiart_id IS NULL ) AND (kundengruppe_id>:"SYS_B_1")) AND
271.17 274,929 0.00 2.6 273.19 825,769 2897690632
Module: XMLServer_sol@ocean-app1 (TNS V1-V3)
SELECT mandant,kunden_kontlimit_gruppe_id,distrib_type_name,Data
baseId FROM distrib_type_vw WHERE (mandant=:"SYS_B_0") AND (data
baseid>:"SYS_B_1") ORDER BY distrib_type_name
264.44 327 0.81 2.6 270.95 44,798,481 637757003
Module: timer.exe
update TimerActionHistory set FinishedAt=SysDate,Status=:b0,Exi
tCode=:b1,OutputLog=:b2 where DatabaseID=:b3
214.57 2,735,488 0.00 2.1 211.67 5,470,867 4178735858
Module: XMLServer_sol@ocean-app1 (TNS V1-V3)
SELECT mandant,waehrungscode,name_txt_id,schluessel,einheit,sort
_kz,komma_kz,ekp_kalk,ekp_ueber,vkp_kalk,vkp_ueber,vkp_buch,waeh
rungscode_num,exponent_of_currency,DatabaseId FROM waeh WHERE (m
andant=:"SYS_B_0") AND (waehrungscode=:"SYS_B_1")
209.53 259 0.81 2.0 216.02 35,368,069 1628429523
Module: timer.exe
SELECT ROWID FROM PROTOKOLLDATEN D WHERE REFID_1 = :B1 AND PROTO
KOLLKOPF_ID IN (SELECT DATABASEID FROM PROTOKOLLKOPF WHERE IDENT
IFIER = :B2 )
201.65 1,781,338 0.00 2.0 207.18 9,345,450 2568209753
Module: XMLServer_sol@ocean-app1 (TNS V1-V3)
SELECT DISTINCT CATERING FROM VACANCY_PRICE_FIT VP WHERE VP.LE
IART_ID = :b1 AND VP.LEIZU_ID_PERF = NVL(:b2 , 0 ) AND VP.C
USTOMER_PRICEGROUP_ID_PERF = NVL(:b3 , 0 ) AND VP.PRICE_TYPE
= :b4 AND ( ( VP.BERECH_BEZUG_KZ = :b5 AND VP.VALID_FROM <=
146.28 2,880 0.05 1.4 146.38 0 1591924588
Module: collectd@db01 (TNS V1-V3)
select round(sum(decode(METRIC_NAME, :"SYS_B_0", value)),:"SYS_
B_1") as DATABASE_WAIT_TIME_RATIO, round(sum(decode(METRIC_NAME,
:"SYS_B_2", value)),:"SYS_B_3") as DATABASE_CPU_TIME_RATIO, :"S
YS_B_4" AS DB_EFFICIENCY from SYS.V_$SYSMETRIC where METRIC_NA