awr warehouse

34

Upload: charles-sam

Post on 13-Apr-2018

250 views

Category:

Documents


0 download

TRANSCRIPT

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 1/35

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 2/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

<?@ ?2%5("A95!"#$%&' )*+, -./" 012" !%,3425 6"7"2 8"$".3, 93,"#:

B2'26 @; <)(%5$2C56&"% D&%53)"%0 E%"7A3) F262'5G56)1%2345 H6)5%#%&95 F262'5%

C2G%2) @"$D2)2I295 H6'&655%&6'5I2$

C5#)5GI5% ,J0 ,-./

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 3/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

C2L5 M2%I"% C)2)5G56)N(5 L"44"K&6' &9 &6)56757 )" "A)4&65 "A% '565%24 #%"7A3) 7&%53O"6; P) &9 &6)5&6L"%G2O"6 #A%#"959 "64$0 267 G2$ 6") I5 &63"%#"%2)57 &6)" 26$ 3"6)%23)3"GG&)G56) )" 754&=5% 26$ G2)5%&240 3"750 "% LA63O"624&)$0 267 9("A47 6")&6 G2Q&6' #A%3(29&6' 753&9&"69; N(5 75=54"#G56)0 %5452950 267 OG&6' "L 2LA63O"624&)$ 7593%&I57 L"% 1%2345R9 #%"7A3)9 %5G2&69 2) )(5 9"45 7&93%5O"6

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 4/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

<'5672

<?@ 1=5%=&5K

P6)%"7A3&6' <?@ ?2%5("A95

D5#4"$G56)

<?@ ?2%5("A95 S95 !2959

!A9)"G5% !295 C)A7$T 5U2$

;

<

=

>

?

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 5/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

<'5672

<?@ 1=5%=&5K

P6)%"7A3&6' <?@ ?2%5("A95

D5#4"$G56)

<?@ ?2%5("A95 S95 !2959

!A9)"G5% !295 C)A7$T 5U2$

;

<

=

>

?

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 6/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

H="4AO"6 "L E5%L"%G2635 NA6&6' F5)("7"4"'$ 

• 

E%5V.-' @5452959

 – 

WXT UCN<N Y HCN<N Y CZ[ N%235 –

 W\;]T ?2&) 5=56)9 &69)%AG56)2O"6

 – W^;. _ J;.T CN<NCE<!`

•  1%2345 .-'aT

 – H6(26357 N&G5V?2&) F"754

 – 

D2)2I295 N&G5 bDU N&G5c U2957 F5)("7"4"'$

 – d5K 24K2$9V"6 #5%L"%G2635 K2%5("A95T <A)"G2O3 ?"%Q4"27 @5#"9&)

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 7/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

•  <A)"G2O3 3"4453O"6 "L 7I #5%L"%G2635 9)2O9O39

I$ FF1d I23Q'%"A67 #%"3599

•  <?@ 962#9(")9T D2)2 3"4453)57 I$ FF1d 942=595=5%$ ("A% "6 )(5 ("A% L%"G G5G"%$

 – 

!"6e'A%2I45 3"4453O"6 L%5fA563$

 – 

F26A24 962#9(") 3%52O"6

 – 

<?@ I2954&659T 62G57 962#9(")9 )" 32#)A%5%5#%5956)2O=5 K"%Q4"27 b6") 2g53)57 I$ %5)56O"6 #5%&"7c

•  E5%9&9)57 A675% ?@Mh 267 ?@P

CiC<Sj )2I459#235

•  U$ 75L2A4)0 562I4570 3"4453)57 ("L"% ^ 72$9k

•  !"6)56)9 233599&I45 =&2 DU<lMPC

•  W"4AG5 "L 72)2 3"6)%"4457 I$ CN

<A)"G2O3 ?"%Q4"27 @5#"9&)"%$ b<?@c

U""9) <7G&6

E%"7A3O=&)$

H4&G&62)5 E5

U"m45653Q9

["K5% PN F2

!"9)9

D54&=5% M&'(

C5%=&35 Automatic WorkloadRepository Snapshots

Performance &Management

 Advisors

HighLoad

SQL

RACIssues

IO / CPUIssues

Self-DiagnosticEngine

k \ 72$9

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 8/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

•  <?@ C)2O9O39

 –  !"A6)5% 9)2O9O39 5;'; 9599&"6 4"'&324 %5279

 – 

W24A5 9)2O9O390 5;'; 4"'"69 3A%%56)

 –  N&G5 C)2O9O390 5;'; DU OG5

 –  F5)%&390 5;'; DU U4"3Q n5)9 E5% No6

 –  C2G#4570 5;'; <3O=5 C599&"6 M&9)"%$

•  1#5%2O6' C$9)5G b1Cc 9)2O9O39

 – 

!ES A92'5 b&634A7&6' !ES 3"A6)c –  F5G"%$ A92'5 b&634A7&6' #2'&6'c

 –  W&5K2I45 =&2 Wh1CCN<N 267 DU<lMPCNl1CCN<N

•  ?2&) !429959 Y H=56)9

 –  H=56)9T C599&"69 K2&O6' L"% 5o)5%6

b42)3(59c0 95%=&359 bP1c0 A95% b&745c –

 

!429959T ["'&324 '%"A#&6' "L 5=56)9 2##4&32O"60 3"GG&)c 

•  <3O=5 C599&"6 M&9)"%$ b<CMc

 –  92G#459 "L 3A%%56) 9)2)5 "L 5=5%$ 29599&"6 5=5%$ 953"67

 – 

<?@ 962#9(")9 32#)A%5 92G#45 "L ("A%

•  N"# CZ[

 –  U$ =2%&"A9 3%&)5%&2T H42#957 N&G50 !C(2%57 F5G"%$0 267 W5%9&"6 !"A6

 –  <77&O"624 %542)57 G5)2 72)2T CZ[ NCZ[ E42690 U&67 N$#590 U&67 W24A590

H6=&%"6G56)

<?@ D2)2

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 9/35!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

•  <?@VI2957 #5%L"%G2635 27=&9"%9

 – 

<A)"G2O3 D2)2I295 D&2'6"9O39 F"6&)"%b<DDFcT DU N&G5 I2957 27=&9"% )" #&6#"&6)%"") 32A95 "L #5%L"%G2635 I"m45653Q9

 –  <A)" CZ[ NA6&6' Y CZ[ NA6&6' <7=&9"%T (&'(4"27 CZ[ &756Oe32O"6 267 2A)"G2O3 )A6&6'

•  <?@VI2957 "A)V"LV)(5VI"o %5#"%)9

 –  <?@ %5#"%)T 244 9)2O9O39 "=5% 2 962#9(") %26'5

 –  !"G#2%5 E5%&"7 <?@T !"G#2%&9"6 "L )K"962#9(") #5%&"79 )" 3"G#2%5 7&g5%5635 &6#5%L"%G2635 A9&6' DU OG5 29 I29&9

 –  <CM %5#"%)T D5)2&457 2624$9&9 "L <CM 72)2 24"6'9599&"60 CZ[0 OG5 7&G569&"69;

•  F26A24 2624$9&9 "L =2%&"A9

#5%L"%G2635 –  C$9)5G! WhCiCN<N

 –  C599&"6! WhCHCCN<N

 –  C5%=&35! D59&'62)57 &6 NdC

 –  CZ[ C)2)5G56)! WhCZ[CN<N

 –  C5'G56)! WhCHnCN<N

 – 

F"7A45V<3O"6! P69)%AG56) –  !4&56) PD! N%A5 567 A95% #%"

E5%L"%G2635 D&2'6"9O39

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 10/35!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

<'5672

<?@ 1=5%=&5K

P6)%"7A3&6' <?@ ?2%5("A95

D5#4"$G56)

<?@ ?2%5("A95 S95 !2959

!A9)"G5% !295 C)A7$T E2$E24

;

<

=

>

?

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 11/35!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

<?@ ?2%5("A95 V UA9&6599 D%&=5%9•

  <A)"G2O3 ?"@5#"9&)"%$ b<?9)2672%7 L"% #5

7&2'6"9O39 L"% 72)2I2959 9&635

•  D5L2A4) %5)56O72$9 #%5=56)9 7)5%G #5%L"%G2bq!"G#2%5 #5%

)(&9 fA2%)5%R9 I429) fA2%)5%R9rc

• 

P63%529&6' <?@#5%&"7 &63%52953"9) &6 3%&O324 #56=&%"6G56)9

CRMFinance Supply Chain

0%

30%

60%

90%

120%

0%

30%

60%

90%

120%

0%

30%

60%

90%

120%

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 12/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

<?@ ?2%5("A95 _ s52)A%59

•  !56)%24 K2%5("

L"% 4"6' )5%G <%5)56O"6

•  M&9)"%&324 267 "962#9(")9 3"44572)2I2959 562IK2%5("A95

• 

HN[ t"I9 G"=59L%"G 9"A%35 72<?@ K2%5("A9

•  @5)56O"6 #5%&"L"% K55Q90 G"6L"%5=5% b75L2A4

C"A%35 D2)2I295 . ["27 <?@ C62#9(")9&6)" @5#"9&)"%$

@"&32.# +A8 8"$4,%3425

D2)2I295 . C62#9(")9

C"A%35 D2)2I295 .

C"A%35 D2)2I295 ]

+BC%&%,32.342, 1," D%,342%E.# +A8 2"$423, %& ,"7"2.# B.3.F.," B%.'&4,GE:".312",

E5%L"%G2635 M"G5

<CM <624$O39

<?@ @5#"%)

!"G#2%5 E5%&"7 <DDF

!"G#2%5 E5%&"7 @5#"%)

D2)2I295 , C62#9(")9

D2)2I295 ] C62#9(")9

uu

D2)2I295 6 C62#9(")9

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 13/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

B"I &6 HF B"I C5%=#A449 e45 L%"G C"AN2%'5) 267 )(56 #<?@ ?2%5("A95 ND&%53)"%$

<?@ C62#9(") HN[

1%2345 !"6e756O24 _ P6)5%6

DUFC B"I "6 C"A%35

D2)2I295 )" 7&%53)"%$

s&624 &6)

0412E" H.2'"3

+A<?@ 962#9(") 5o#"%)

<?@ 962#9(") 5o#"%)

<?@ 962#9(") 5o#"%)

I&3"2$2%," -.&.'"2

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 14/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

IJ32.E3 F5 0412E" ).3.F.,"

• 

DUFC C3(57A45% B"I d2G5TFnFNl!<?lHjN@<!N

•  s%5fA563$

 – ] ("A% P6)5%=24 bL"% &6&O24 95)A#c

 – ,/ ("A% &6)5%=24 b"6'"&6'c

• 

C)"# 5o)%23)9 &L #%"I45G9563"A6)5%570 5;'; e45 9$9)5G LA44

H2.&,:"2 F5 I- +'"&3

• 

@A69 .,(% &6)5%=249 L%9"A%35 )2%'5) &9 2775?2%5("A95

•  <'56) )" <'56) D&%53)

•  !45269 A# 2v5% 9A335

•  @5V2m5G#)9 )%269L5%9A#"6 L2&4A%59 267 )(5

Ho)%23) 267 N%269L5%

1%2345

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 15/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

•  !<?lCE<!HlSC<nHT E"#A42)59 %59"A%353"69AG#O"6 729(I"2%7 L"% <?@;

•  !<?lE@1EH@NPHC T P6L"%G2O"6 2I"A)4"32O"6 "L 7AG# e4590 &6)5%=240 %5)56O"6OG50 5)3; L"% <?@ ?2%5("A95;

•  !<?lE@PWln@<dNC T W&5K #%&=&45'59K&)(&6 )(5 HF !"69"45

• 

!<?l[1<Dl?1@`H@C T 164$ A957 7A%&6'26 23)A24 HN[ 4"27 #%"3599 )" )(5 <?@?2%5("A95;

•  !<?lC@!lDUC T F2&6 &6L"7IR90 =5%9&"60 HN[ 9)2)A90 5)

•  !<?lC@!lDUlPdCN<d!HC&6L"%G2O"6 2I"A) 9"A%35 7

•  !<?l[1<DlFHN<D<N< T <4"3240 429) 4"270 5)3;

• 

!<?l[1<DlH@@1@C T E"#5%%"%9 =&5K &6 )(5 3"69"45;

•  !<?lDUPDlF<EEPdn T S972)2 I5)K556 H6)5%#%&95 F?2%5("A95 267 D2)2I295

<?@ ?2%5("A95 C3(5G2 1It53)9

1%2345

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 16/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

<'5672

<?@ 1=5%=&5K

P6)%"7A3&6' <?@ ?2%5("A95

D5#4"$G56)

<?@ ?2%5("A95 S95 !2959

!A9)"G5% !295 C)A7$T E2$E24

;

<

=

>

?

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 17/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

!"6e'A%&6' <?@ ?2%5("A95

1%2345 !"6e756O24

• 

CA##"%)57 DU =5%9&"69T.,;.;-;, Y ..

•  DU !%5756O249T CiC

•  C)"%2'5T CiC<Sj )2I459#235

• 

D5L2A4) %5)56O"6 #5%&"7T s"%5=5% b32K55Q90 G"6)(9 "% $52%9c

•  N%23Q "=5%244 9#235 AO4&p2O"6

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 18/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

!"6e'A%&6' <?@ 3"4453O"6 &6)" <?@ ?2%5("A95

• 

E%5V3"6e'A%5 72)2I295 bDU< #%&=&4b92=5 962#9(")9c 3%5756O249 L"% 9"

•  <77 9"A%35 72)2I295 =&2 +EG4&, G

2I"=5 9A##"%)57c

•  D5e65 962#9(") 3"4453) &6)5%=24 b,/

•  K$#4.B 962#9(")9 =&2 &GG57&2)5 %

"6 26$ 9"A%35 )2%'5) &6 <?@ ?2%5

•  )%,.F#" "% I&.F#" &6 )(5 5=56) "L #

1%2345 !"6e756O24

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 19/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

<'5672

<?@ 1=5%=&5K

P6)%"7A3&6' <?@ ?2%5("A95

D5#4"$G56)

<?@ ?2%5("A95 S95 !2959

!A9)"G5% !295 C)A7$T E2$E24

;

<

=

>

?

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 20/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

E5%L"%G2635 D&2'6"9O39 K&)( <?@ ?2%5("A95

•  <44 <?@ L52)A%5

"6 4"6' )5%G <?•  E5%L"%G263

• 

<?@ %5#"%)

• 

<CM 2624$O

•  !"G#2%5 E5

• 

!"G#2%5 E5

• 

P6)5'%2)57 952GHF SP

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 21/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

["6' N5%G !"G#2%2O=5 <624$9&9 "L K"%Q4"27

•  M"K (29 G$ P81 K"%Q4"27 3(26'57 $52% )" $52%x

 – 

!(26'59 )" "=5%244 4"27

 – !(26'59 )" )(5 3(2%23)5% "L )(5 4"27 b%5279 =9; K%&)59c

1%2345 !"6e756O24 _ P6)5%6

-

w----

.-----

.w----

,-----

,w----

]-----

]w----

/-----

L1&" ;MN <M;=

4l%5279l9 %527l&"#9lG2o %527l&"#9l7&%53) %527l&"#9l7&%53)lG2o

-

.-----

,-----

]-----

/-----

w-----

X-----

L1&" ;MN <M;>

4l%5279l9 %527l&"#9lG2o %527l&"#9l7&%53) %527l&"#9

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 22/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

E5%L"%G2635 <624$9&9 "L DU9 "6 C(2%57 P6L%29)%A3)

• 

?(&3( %59"A%35 A92''%52)59) =2%&2O"69 2

•  ?(&3( 72)2I2959 "6("9) '565%2)59 )(5 (4"27x

•  C("A47 )(595 72)2I2&6)" 757&32)57 95%=5

1%2345 !"6e756O24

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 23/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

M"K !A%%56) <?@ ZA5%&59 !(26'5 &6 )(5 <?@ ?2

L%"G 7I2l(&9)l9$9lOG5lG"754 9)G0 7I2l(&9)l962#9(") 90 '=h#2%2G5)5% #0 BF,&C$OE.P

K(5%5 9)G;9)2)l62G5 &6 byDU !ESy0yI23Q'%"A67 3#A OG5yc

.&B RSAI8TCO3.2'"3Q&.C"UV WXBF&.C"W

.&B ,OBF%BV CO&"PQBF%B  267 9;962#l&7 z 9)G;962#l&7

267 9;7I&7 z 9)G;7I&7 267 9;&69)2635l6AGI5% z 9)G;&69)2635l6AGI5%

267 #;62G5 z y3#Al3"A6)y 267 #;&69)l&7 z 9;&69)2635l6AGI5%c

1%2345 !"6e756O24

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 24/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

<'5672

<?@ 1=5%=&5K

P6)%"7A3&6' <?@ ?2%5("A95

D5#4"$G56)

<?@ ?2%5("A95 S95 !2959

!A9)"G5% !295 C)A7$T E2$E24

;

<

=

>

?

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 25/35

 About ebay inc and me (Samrat Roy)

ebay inc is a global commerce platform and paymen

• 

Senior Engineer in ebay inc’s Database Engineerin

• 

Working with Oracle Database for more than 10 ye

• Helped create and maintain the Custom AWR/DB m

database 

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 26/35

Production Environment

• Mostly 11g RAC, ASM and Exadata

• 

One of the busiest OLTP database• 500+ database instances, from 10 – 130 TB each

• 5-10K processes per instance

• Execs 100K/sec, Redo 5-10MB/sec

• 

Growing workload 40% year-on-year• EM 12c monitors 100+ db targets including 5 criticadatabases running payment system, user data app

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 27/35

Business and DBA Requirement for custom AWR WBusiness Problem:

•  Find and fix database performance degradation proactively and post upgrade

•  Proactively add capacity (storage/compute) in a timely planned manner

•  Compare application schemas between stage and production

which when translated to DBA problem statements and solutions :

•  Monitor and capture W-o-W, (Week-over-Week) and M-o-M (Month-over-Month) deviation for DB metrics, ena

•  W-o-W deviation weekly and on-demand

•  SQL profiling for W-o-W deviation in execs, lio, pio, cpu, elapsed time

•  Daily ASH trend for significant wait events and W-o-W comparison

•  Daily W-o-W “Busy-ness” report from Time Model statistics for all databases

•  Track storage growth to generate storage TTL (Time to Live), compute add or storage uplift for Primary as well

• 

Follow GoldenGate trail from source to each downstream db•  Schema comparison report between stage and production db

•  Compare AWR data pre/post upgrade for new database wholly or partially (moving only few schemas)

 Also answer DBA type questions for ongoing operations, for e.g:

•  When table A was dropped, renamed or what column was added and when

•  Table “busyness” by time of day for scheduling table maintenance across both Production and Active DataGua

•  Compare Initialization parameter, patch, compute, storage between Current Primary and Switchover Target

• 

Global Database Metadata: Version, Type, Role, CPU, Memory, Storage (Primary/Standby, etc.)

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 28/35

Track health across all dbs W-o-W

Time Model Stats - WoW

======================================== 

DBNAME NAME PMDAY VALUE(Secs) % diff

PCT% 

---------------- --------------------------- -------------------- ----------------------

 

DB_A_LIVE DB time 09-09-2014 TUESDAY 32182100 -49%

09-16-2014 TUESDAY 46089640 43%

09-23-2014 TUESDAY 55498930 20%

sql execute elapsed time 09-09-2014 TUESDAY 26196035 -50%

09-16-2014 TUESDAY 36871712 42%

09-23-2014 TUESDAY 49219127 31%

DB_B_LIVE DB time 09-09-2014 TUESDAY 43825650 2%

09-16-2014 TUESDAY 44263906 1%

09-23-2014 TUESDAY 43821266 -1%

sql execute elapsed time 09-09-2014 TUESDAY 35060520 3%

09-16-2014 TUESDAY 35411125 1%

09-23-2014 TUESDAY 35410117 -0.1%

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 29/35

Track top waits and SQL profile across all d

SQL Profile Trend report - WoW============================== 

DBNAME DAY SQL_ID Ex% Buf% Elp% Cpu% Phy% Ex# Buf# P-------------------------- --------------- ----- ---- ------- ------- --------

DB_A_LIVE 09-09-2014 TUE dktb2hv6v9d46 .30 3.27 2.39 2.40 3.74 82 1

fbhuck3ryu014 1.79 2.89 1.92 1.67 1.15 6 2 1

09-16-2014 TUE 1kuyk21zwn1ac 9.50 9.01 15.02 9.37 8.00 9 1

dktb2hv6v9d46 .50 4.10 3.89 3.20 4.54 83 3

DBNAME EVENT_NAME time:HH WAITTIME WAITPCT---------------- ------------------------------- ------------ --- --------------------------------- ---------DB_A_LIVE enq: TX - row lock contention 09-SEP-2014 TUE 5943 23%

16-SEP-2014 TUE 41771 68%23-SEP-2014 TUE 80101 92%

Streams miscellaneous event 09-SEP-2014 TUE 18358 71%

16-SEP-2014 TUE 17956 29%

DBNAME EVENT_NAME time:HH WAITTIME WAITPCT

---------------- -------------------------------------------- --------------- ------------------------------DB_B_LIVE db file sequential read 09-SEP-2014 TUE 40 6%

16-SEP-2014 TUE 32 3%

23-SEP-2014 TUE 28 5%

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 30/35

Further drill down on sql_id

Turns out SQL is a “SELECT-FOR-UPDATE” causing high enqueue waits impacting oveexecute elapsed time and higher CPU time

@sqlbyid

Enter value for sql_id : 1kuyk21zwn1ac 

/* sample sql for sql_id 1kuyk21zwn1ac*/SELECT * from A FOR UPDATE;

SNAP_ID DBNAME EXECS_DELTA AVG_BG AVG_IO AVG_ROW•

 

---------- ------------------------------ ----------- ---------- ---------- ---------------------------------------

118140 DB_A_LIVE 9256 100.4 .7 7.6

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 31/35

Future

• OEM AWR Warehouse being considered

• May use for performance tracking and integrated dashboard

• 

Will need to extend the AWR Warehouse for functionality like ADG performanc

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 32/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

<?@ ?2%5("A95 @59"A%359

• 

CA##"%) F29)5% d")5 "6 <?@ ?2%5("A95 D"3 PDT .J-\/]]w;.

• 

1%2345 D2)2I295 .,3 D"3AG56)2O"6T , a E5%L"%G2635 NA6&6' !(2#)5% .-

•  1%2345 [52%6&6' [&I%2%$ =&75"T <624$p5 ["6'V)5%G E5%L"%G2635H6)5%#%&95 D2)2I2959 S9&6' <?@ ?2%5("A95

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 33/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

C2L5 M2%I"% C)2)5G56)N(5 #%5357&6' &9 &6)56757 )" "A)4&65 "A% '565%24 #%"7A3) 7&%53O"6; P) &9 &6)&6L"%G2O"6 #A%#"959 "64$0 267 G2$ 6") I5 &63"%#"%2)57 &6)" 26$ 3"6)%23)3"GG&)G56) )" 754&=5% 26$ G2)5%&240 3"750 "% LA63O"624&)$0 267 9("A47 6")&6 G2Q&6' #A%3(29&6' 753&9&"69; N(5 75=54"#G56)0 %5452950 267 OG&6' "L 2LA63O"624&)$ 7593%&I57 L"% 1%2345R9 #%"7A3)9 %5G2&69 2) )(5 9"45 7&93%5O"6

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 34/35

!"#$%&'() + ,-./0 1%2345 2678"% &)9 2:4&2)59; <44 %&'()9 %595%=57; >

7/23/2019 Awr Warehouse

http://slidepdf.com/reader/full/awr-warehouse 35/35