Tuning Excessive IO 11g

8/12/2019 Tuning Excessive IO 11g

1/8

Determining detail of poorly-executing SQL statements (SQL scripts used are listedat the end)

There are three methods of locking in execution plans; outlines, SQL pro les, and !aselines(ne" in ##g)$ %utlines can !e created on any instance, !ut if you "ant to lock an executionplan !ased on data in the &' ta!les, you need to use SQL pro les$ ou can also use SQLpro les to use plans that are located on other nodes in a &* system di+erent from yourlocation$

The rst step in the process is to nd the o+ending SQL$ sually, this can !e found through% . (if a/aila!le) or the follo"ing script can !e executed$ 0ay attention to the &123L4%(a/erage logical 45%s) column6

SQL7 8acti/e3s9l

S4D 0 %2 SQL34D *:4LD 0L& 3:&S:31&L < *S &123L4% &12304% &123 T4. SQL3T ?@ ADB* Thin #fdgd@f>Cpxt> E#=EF>>G#F ?F@FEFG @$=@ $ @ $ select%1 3.&T*:3T 0 , length(%1 3.&T*:3

0&TT ) as .&T*:3L 2T:, %1 34S3L%*&L, %

1 3D STH% from S4T 3%1 4D "here sit

e3code I 6# and %1 3*% T 3*%D I 6@ an

d %1 3.&T*:30&TT I 6E and %1 3.&T*

:3T 0 I J J

=>> ADB* Thin ?psxcpnx"rx@c E?#>@=F?? @? FG# @#>$GF $ K $ # selectmax(tE$&*T41 S Q * ) as &00D&T

&3&*T41 S Q from ( select 0 %13%BA *T3

4D, GML 1 L as *&L*L 1 L from 0 %14S4%

4 2%BA *T a start "ith 0 %13%BA *T34D I

(select %' 30 %13%BA *T34D from 0 %14

S4% 4 2%BA *T "here 0 %13%BA *T34D I 6# )

connect !y %* *L 0 4% %' 30 %13%BA

*T34D I 0 %13%BA *T34D union select 0

%13%BA *T34D, # -L 1 L as *&L*L 1 L fr

om 0 %14S4% 4 2%BA *T start "ith 0 %13%B


2/8

A *T34D I 6@ connect !y %* *L 0 4% 0

%13%BA *T34D I %' 30 %13%BA *T34D) t#

left outer Noin S 14* 4 ST t@ on t#$0 %

13%BA *T34D I t@$0 %13%BA *T34D and t@$S

14* 3D O34D I 6E left outer Noin S 14

* &00D&T&4 ST tE on t@$S 14* 4D I tE$S

14* 4D and t@$S 14* 3D O34D I tE$S 14

* 3D O34D left outer Noin S 14* &00D&T&

1 t= on t@$S 14* 4D I t=$S 14* 4D an

d t@$S 14* 3D O34D I t=$S 14* 3D O34D

and tE$&003D&T&3*L&SS34D I t=$&003D&T&3*

L&SS34D and tE$*L&SS4 ST& * &. I t=$*L

&SS4 ST& * &. and tE$&*T41 S Q * I

t=$&00D&T&S QP left outer Noin S 14*

&00D&T&D O t> o

>=#K ADB* Thin ?psxcpnx"rx@c E?#>@=F?? @? FG# @#>$GF $ K $ #

=G=? ADB* Thin d!augd/dGF+= #@ EKGFE> >@?@##G G$=? $ G $ S L *T!$*% T %L3 T4T 34D, !$*% D3D&T&,

!$L%24*&L3 &. O %. *% T %L30%4 T3 S S a

L OT % T A%4 L S T3 &. S ! % a$*%

T %L30%4 T34D I 6# & D a$S 14* D O34D

I 6@ & D a$* 3T 0 34D I J L S TJ & D

a$*% T %L3 T4T 34D I !$*% T %L3 T4T 34D

': !$ &BL D I J J

=F>K ADB* Thin dkc@k!NFkdsuF = F#@E> @F @K@?#? > G$F= $ $=? selectt#$0 %13%BA *T34D, *alcLe/el as

0 %1 STL 1 L , t@$S 14* 3D O34D, t@$S

14* 4D, t>$app3data3class3name, tE$&00


3/8

3D&T&3*L&SS34D as &00D&T&3*L&SS34D , tE$

*L&SS4 ST& * &. , tE$*L&SS% D as &00D

&T&3*L&SS% D , tE$&*T41 S Q * as &0

0D&T&3&*T41 S Q ,t=$&00D&T& from ( sele

ct 0 %13%BA *T34D, GML 1 L as *&L*L 1 L

from 0 %14S4% 4 2%BA *T a start "ith 0

%13%BA *T34D I (select %' 30 %13%BA *T

34D from 0 %14S4% 4 2%BA *T "here 0 %13%

BA *T34D I 6# ) connect !y %* *L 0 4%

%' 30 %13%BA *T34D I 0 %13%BA *T34Du

nion select 0 %13%BA *T34D, # -L 1 L as

*&L*L 1 L from 0 %14S4% 4 2%BA *T sta

rt "ith 0 %13%BA *T34D I 6@ connect !y

%* *L 0 4% 0 %13%BA *T34D I %' 30 %13

%BA *T34D) t# left outer Noin S 14* 4 S

T t@ on t#$0 %13%BA *T34D I t@$0 %13%BA

*T34D and t@$S 14* 3D O34D I 6E left o

uter Noin S 14* &00D&T&4 ST tE on t@$S

14* 4D I tE$S 14* 4D and t@$S 14* 3D

O34D I tE$S 14* 3D O34D left outer Noin

S 14* &00D&T&1 t= on t@$S 14* 4D I

t=$S 14* 4D and

=F G ADB* Thin dkc@k!NFkdsuF = F#@E> @F @K@?#? > G$F= $ $=?

=F#@ ADB* Thin dkc@k!NFkdsuF = F#@E> @F @K@?#? > G$F= $ $=?

F ro"s selected$

Sessions =F>K, =F G, and =F#@ seem to !e the ones "ith the most L4%s and !ased on theSQL34D column, they are all running the same SQL statement$


4/8

ext, "e need to see if this SQL statement is running the most e cient SQL plan$ Taking theSQL34D /alue from the pre/ious script, execute this next oneR

SQL7 8a"r3plan3stats$s9l

nter /alue for s9l3id6 dkc@k!NFkdsuF

SQL34D 0L& 3:&S:31&L < *S T4. &123 T4. &123L4% %'S30 %* SS D

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

dkc@k!NFkdsuF #@GK= G>?E #EE,#?? #,F>F$> $ #E =KF$= ?>F@?G#

dkc@k!NFkdsuF = F#@E> @F #@@,GEG #@?, @>$# #$ @> >#=$? ?#E@GE#

Orom the results here, you can see that there are t"o di+erent plans in play at the presenttime, !ut neither is signi cantly !etter than the other$

Let s look at the history of the plans to see "hen changes occurredR

SQL7 8a"r3plan3change$s9l

nter /alue for s9l3id6 dkc@k!NFkdsuF

nter /alue for instance3num!er6 #

S &034D %D B 24 34 T 1&L3T4. SQL34D 0L& 3:&S:31&L < *S &123 T4.&123L4%

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

>F=F # @K-&0 - G @$ $= $=@F &. dkc@k!NFkdsuF = F#@E> @F G# $ #? ?E=$@

>F=K # @K-&0 - G E$ $ F$GF &. dkc@k!NFkdsuF ?@ $ #F ?>G$#

>F=G # @K-&0 - G =$ $# $FFE &. dkc@k!NFkdsuF # K $ #> ?#?$K

>F> # @K-&0 - G >$ $#?$F?G &. dkc@k!NFkdsuF #G= $ #> >FG$F

>F># # @K-&0 - G ?$ $E=$FE@ &. dkc@k!NFkdsuF @EG $ #? >F=$@

RRRRRRRR$$( esults condensed)

>G@K # >-.& - G @$ $@=$K?> 0. dkc@k!NFkdsuF #,# $ #@ ==#$K

>G@G # >-.& - G E$ $E=$K@? 0. dkc@k!NFkdsuF #,@ $ #> >#F$#

>GE # >-.& - G =$ $=>$EE> 0. dkc@k!NFkdsuF #,==? #$#K? =G?$F

>GE# # >-.& - G >$ $ @$ #= 0. dkc@k!NFkdsuF #,=F? ?$=K? =?@$>

>GE@ # >-.& - G ?$ $##$=E= 0. dkc@k!NFkdsuF F@ #K=$>=G >>#$G

>GEE # >-.& - G F$ $@#$EGE 0. dkc@k!NFkdsuF #, ?= E$=#F >?E$#


5/8

>GE= # >-.& - G K$ $EE$G#G 0. dkc@k!NFkdsuF #>? EG$??# =>K$@

>GE> # >-.& - G G$ $EG$# E 0. dkc@k!NFkdsuF > #=@$@?= >=#$?

>GE? # >-.& - G # $ $ E$ KF 0. dkc@k!NFkdsuF @F @??$K>E =G?$=

>GEF # >-.& - G ##$ $ F$# K 0. dkc@k!NFkdsuF EE? @#$=F@ ?@K$#

>GEK # ?-.& - G #@$ $#E$#E &. dkc@k!NFkdsuF #?K =@$K?> >KF$E

>GEG # ?-.& - G #$ $# $?## &. dkc@k!NFkdsuF ?@ ##?$E#= =K>$

>G= # ?-.& - G @$ $#?$K? &. dkc@k!NFkdsuF EK #KG$GFG >F?$

>G=# # ?-.& - G E$ $@K$GKK &. dkc@k!NFkdsuF #E# >=$?G@ ?@ $E

>G=@ # ?-.& - G =$ $ K$EE= &. dkc@k!NFkdsuF #?F =E$#FF ?>E$=

>G=E # ?-.& - G >$ $##$? &. dkc@k!NFkdsuF @E= E $F> >KG$?

>G== # ?-.& - G ?$ $ K$@#K &. dkc@k!NFkdsuF @G@ @=$FK= >F?$?

>G=> # ?-.& - G F$ $@ $GK? &. dkc@k!NFkdsuF EFF #G$#F> >G=$#

>G=? # ?-.& - G K$ $EE$> &. dkc@k!NFkdsuF =#F @>$EKE >>?$E

&s you can see, things looked ok until around =0. yesterday$ &t that point, this 9uerystarted struggling$ Based on this, "e should see if anything else is a+ecting itsperformance$ This is also a good script to run if the system is suddenly experiencing a spikein "aits$ se the default arguments "hen running this script to get all possi!le output$

SQL7 8sessions3"aiting$s9l

nter /alue for username6

nter /alue for sid6

nter /alue for ignore3e/ent6

S &. S4D S 4&L ST&T S SQL34D 1 T S *% DS34 3'&4TBL%*P4 23S SS4%

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

DBS .0 >#> @E &*T41 !ka#?g>ysh?9g Streams &Q6 "aiting for messag =

S* 30 %1 =F G @F=F@ &*T41 dkc@k!NFkdsuF read !y other session # >=#K

S* 30 %1 =F#@ #KFF? &*T41 dkc@k!NFkdsuF read !y other session # >=#K

S* 30 %1 =F>K @=@G> &*T41 dkc@k!NFkdsuF read !y other session # >=#K

S* 30 %1 >=#K ?E#EF &*T41 ?psxcpnx"rx@c SQLU et message to client # >EF?

S* 30 %1 >=># EF=?# 4 &*T41 SQLU et !reak5reset to client =@K


6/8

S S => F =##? &*T41 #s!k>Caggu=?y read !y other session # >=#K

S S =GG= @EFEK &*T41 !tNcx9Nr#rnuG SQLU et message to client

Orom these results, "e see that all three of the sessions that are executing the 9uery "here

our focus "as are !eing !locked !y a completely di+erent session (>=#K)$ Based on theS *% DS34 3'&4T column /alue, these sessions ha/e !een !locked for a!out @G hours,"hich is around the time the &123 T4. /alue from the a"r3plan3change$s9l script Numpeddramatically for this SQL 9uery$

So, in conclusion, a change of SQL plan "as not necessary$ %nce the !locking session "askilled, the sessions resumed normal processing$

Script listing

--&*T41 3SQL$SQLset pagesiCe GGGset lines #>>col username format a#Ecol prog format a# trunccol s9l3text format a># trunccol sid format GGGGcol child for GGGGGcol a/g3etime format G,GGG$GGGGcol a/g3pio format GGG,GGG$Gcol a/g3lio format GGG,GGG$G

!reak on s9l3text skip #select sid, su!str(program,#,#G) prog, !$s9l3id, s9l3child3num!er child,plan3hash3/alue, executions execs,disk3reads5decode(n/l(executions, ), ,#,executions) a/g3pio,!u+er3gets5decode(n/l(executions, ), ,#,executions) a/g3lio,(elapsed3time5decode(n/l(executions, ), ,#,executions))5# a/g3etime,s9l3textfrom /Vsession a, /Vs9larea !"here status I J&*T41 Jand username is not nulland a$s9l3address I !$addressand a$s9l3hash3/alue I !$hash3/alue

and audsid WI S S3*% T


7/8

col a/g3lio for GGG,GGG,GGG$Gcol a/g3ro"s for GGG,GGG,GGG$Gcol !egin3inter/al3time for aEcol node for GGGGG!reak on plan3hash3/alue on startup3time skip #select s9l3id, plan3hash3/alue, sum(execs) execs, sum(etime) etime,sum(etime)5sum(execs) a/g3etime, sum(rose)5sum(execs) a/g3ro"s,sum(pio)5sum(execs) a/g3pio, sum(lio)5sum(execs) a/g3liofrom (select ss$snap3id, ss$instance3num!er node, !egin3inter/al3time, s9l3id,plan3hash3/alue,n/l(executions3delta, ) execs,elapsed3time3delta5# etime,(elapsed3time3delta5decode(n/l(executions3delta, ), ,#,executions3delta))5#a/g3etime,ro"s3processed3delta rose,(ro"s3processed3delta5decode(n/l(executions3delta, ), ,#,executions3delta))a/g3ro"s,!u+er3gets3delta lio,(!u+er3gets3delta5decode(n/l(executions3delta, ), ,#,executions3delta)) a/g3lio,disk3reads3delta pio,(disk3reads3delta5decode(n/l(executions3delta, ), ,#,executions3delta)) a/g3piofrom DB&3:4ST3SQLST&T S, DB&3:4ST3S &0S:%T SS"here s9l3id I n/l(JXs9l3idJ,s9l3id)and plan3hash3/alue like n/l(JXplan3hash3/alueJ,plan3hash3/alue)and ss$snap3id I S$snap3idand ss$instance3num!er I S$instance3num!erand executions3delta 7 )group !y s9l3id, plan3hash3/alueorder !y >5

--&' 30L& 3*:& 2 $SQL

set lines #>>col execs for GGG,GGG,GGGcol a/g3etime for GGG,GGG$GGGcol a/g3lio for GGG,GGG,GGG$Gcol !egin3inter/al3time for aEcol node for GGGGG!reak on plan3hash3/alue on startup3time skip #select ss$snap3id, ss$instance3num!er node, !egin3inter/al3time, s9l3id,plan3hash3/alue,n/l(executions3delta, ) execs,(elapsed3time3delta5decode(n/l(executions3delta, ), ,#,executions3delta))5#a/g3etime,(!u+er3gets3delta5decode(n/l(!u+er3gets3delta, ), ,#,executions3delta)) a/g3liofrom DB&3:4ST3SQLST&T S, DB&3:4ST3S &0S:%T SS


8/8

"here s9l3id I n/l(JXs9l3idJ,J=d9s@k>tynk?#J)and ss$snap3id I S$snap3idand ss$instance3num!er I S$instance3num!erand executions3delta 7 order !y #, @, E5

--S SS4% S3'&4T4 2$SQL

set lines #>>set pagesiCe GGGcol e/ent for aE trunccol username for a@>col sid for GGGGGselect username, sid, serial , status, s9l3id, e/ent, seconds3in3"ait,!locking3session

from /Vsession"here username like n/l(JXusernameJ,username)and sid like n/l(JXsidJ,sid)and e/ent not like n/l(JXignore3e/entJ,JSQLU et message from clientYJ)order !y username, sid, serial5

Documents

Tuning Excessive IO 11g