0D%P0D(#T location #triping dis$ space over sufficient dis$s can starve for , increasing 2log file sync3 "aits. After all, these system calls and I?O calls must use can also be paged out. This can lead to slo"er response from -> too.
About sql_trace parameter SQL_TRACE enables or disables the #- trace facility. #etting this parameter to true provides
information on tuning that you can use to improve performance. Kou can change the value using the DBMS_SYSTEM pac$age
o" to turn on the #- trace, 8MU event in Oracle database and trcsess, t$prof utility Tracing is a important part for Oracle database tuning. This "ill help capture all the problem statement , "ait event "hich are responsible for slo" running of the session. Oracle has provided has many &uic$est "ays to trace the local session, other user session and format the trace to ma$e readable.
-et us ta$e some loo$ at ho" to turn on #- trace, 8MU event in Oracle database and trcsess, t$prof utlity
#- Trace ,8MU event If you "ant to trace in local session,here are the steps to turn on #- trace
!ormal trace
alter session set s&l0trace 1 true4 Z To put trace on alter session set s&l0trace 1 false4 Z To put trace off (ull le/el with wait e/ent %nd bind trace
alter session set events 1 28MU trace name conte)t forever, level 8534 To put tracing off alter session set events 1 28MU trace name conte)t off34 Same as !ormal trace
e)ec DB%#0#(##IO/.set0s&l0trace's&l0trace 19 T>(+4 e)ec DB%#0#(##IO/.set0s&l0trace's&l0trace 19 =A-#(+4 If you "ant to trace in other running session,here are the steps !ormal trace
e)ecute dbms0system.set0s&l0trace0in0session '2sid3,3serial3,true+4 Z To put tracing on e)ecute dbms0system.set0s&l0trace0in0session '2sid3,3serial3,true+4 Z To put tracing off (ull le/el with wait e/ent %nd bind trace
e)ecute dbms0system.set0ev'2sid3,3serial3,8MU,85,33+4 To put trace off
e)ecute dbms0system.set0ev'2sid3,3serial3,8MU,,33+4 Kou must have seen using 85, in various statement above.They are tracing levels.Oracle Tracing has tracing levels .ere are the valid values
/o trace. -i$e s"itching s&l0trace off. The e&uivalent of regular s&l0trace
5 It Provides e)ecution path,ro" counts,smallest flat file M
The same as 5, but "ith the addition of bind variable values The same as 5, but "ith the addition of "ait events
N >egular Trace plus database operation timings that the #- "aited to have done in order to complete. =or e)ampleE dis$ access timings. The same as 5, but "ith both bind variable values and "ait events
85
>egular trace "ith the both the "ait and bind information.
There are other "ays to do the tracing also. ere are some of these 8+ O>AD(B This re&uires login as sysdba
oradebug setospid 8888 Z Debug session "ith the specified Oracle process id
oradebug setorapid 8888 Z Debug session "ith the specified O# process oradebug event 8MU trace name conte)t forever, level M4 oradebug event 8MU trace name conte)t off4 Z This disable the trace oradebug close0trace Z This closes the trace file Oradebug T>A<(=I-(0/A%(4 5+ ith Oracle 8g the #- tracing options have been e)tended using the DB%#0%O/ITO> pac$age (C(<T( dbms0monitor.session0trace0enable
hich is similar A-T(> #(##IO/ #(T ([(/T# 28MU trace name conte)t forever, level 534 (C(<T( dbms0monitor.session0trace0enable 'binds19true+4 hich is similar A-T(> #(##IO/ #(T ([(/T# 28MU trace name conte)t forever, level M34 (C(<T( dbms0monitor.session0trace0enable '"aits19true+4 hich is similar A-T(> #(##IO/ #(T ([(/T# 28MU trace name conte)t forever, level N34 (C(<T( dbms0monitor.session0trace0enable'2sid3,3serialR3+ hich is similar e)ecute dbms0system.set0ev'2sid3,3serial3,8MU,5,33+4 (C(<T( dbms0monitor.session0trace0enable '2sid3,3serialR3,binds19true+4 hich is similar e)ecute dbms0system.set0ev'2sid3,3serial3,8MU,M,33+4
(C(<T( dbms0monitor.session0trace0enable '2sid3,3serialR3,"aits19true+4 hich is similar e)ecute dbms0system.set0ev'2sid3,3serial3,8MU,N,33+4 There are many other functionality available in dbms0monitor. e can enable trace based on client identifier and multiple "ays How to identif. the Trace files
e can identify the trace files using the spid of the session. Also the trace file "ill contain sid,serialR pair at the start of the trace file. Belo" &uery can be used to find the local session trace file select c.value 77 2?3 77 d.instance0name 7730ora03 77 a.spid 77 2.trc3 trace from v\process a, v\session b, v\parameter c, v\instance d "here a.addr 1 b.paddr and b.audsid 1 userenv'2sessionid3+ and c.name 1 2user0dump0dest3 ? There is another easy "ay to identify the trace file "hich is called trace identifier alter session set tracefile0identifer13O>A<34 This is identifier Example trace file
Trace file ?app?oracle?T(#T?diag?rdbms?test?T(#T?trace?T(#T0ora08;5W0ID(/.trc Oracle Database 88g (nterprise (dition >elease 88.5..M. F UMbit Production ith the Partitioning, >eal Application
#unO#85
/ode nameE sun85>eleaseE W.8[ersionE eneric08MMMNN*88 %achineE sunMu Instance nameE T(#T >edo thread mounted by this instanceE 8 Oracle process numberE 55
ni) process pidE 8;5W, imageE oracleV #unO#85 666 58;*8*58 85E5NEWU.MU8 666 #(##IO/ IDE';NXW.8X;M8+ 58;*8*58 85E5NEWU.MU8666 <-I(/T IDE'+ 58;*8*58 85E5NEWU.MU8 666 #(>[I<( /A%(E'T(#T+ 58;*8*58 85E5NEWU.MU8 666 %OD-( /A%(E'#TA/DA>DVsunO#85'T/# [8*[;++ 58;*8*58 85E5NEWU.MU8 666 A
<-O#( RWEc1,e1W,dep18,type1;,tim1NNUUNS88;NW; 111111111111111111111PA>#I/ I/ <>#O> RM len15; dep18 uid18X; oct1; lid18X; tim1NNUUNS88;NU;M hv1;WXWWS5MW8 ad13;aeea;da] s&lid13WWdcXUXajydh;] #(-(
oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off1X5 $)sbbbfp1ffffffffXdUXXbb bln155 avl18 flg18 value1 (C(< RMEc1,e1;XX,p1,cr1,cu1,mis1,r1,dep18,og18,plh15N5SXWU8,tim1NNUUNS88;NSXN =(T< RMEc1,e15U,p1,cr1;,cu1,mis1,r1,dep18,og18,plh15N5SXWU8,tim1NNUUNS88;SW <-O#( RMEc1,e15,dep18,type1;,tim1NNUUNS88;S88U 111111111111111111111 PA>#I/ I/ <>#O> RW len18S8 dep18 uid18X; oct1; lid18X; tim1NNUUNS88;S;N hv1;;;;N;W ad13;bedfeMN] s&lid13X&sXf)NS8SMu8] #(-(
BindR5 oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off1MN $)sbbbfp1ffffffffXdUX;baN bln155 avl1M flg18 value188 BindR; oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off1X5 $)sbbbfp1ffffffffXdUX;bc bln155 avl18 flg18 value1 (C(< RWEc1,e1;XX,p1,cr1,cu1,mis1,r1,dep18,og18,plh15N5SXWU8,tim1NNUUNS88;SU5M =(T< RWEc1,e15U,p1,cr1M,cu1,mis1,r18,dep18,og18,plh15N5SXWU8,tim1NNUUNS88;SUS5 <-O#( RWEc1,e1X,dep18,type1;,tim1NNUUNS88;SX;S111111111111111111111PA>#I/ I/ <>#O> RM len1;WU dep18 uid18X; oct1; lid18X; tim1NNUUNS88;SSW5 hv15MUNXN;8N5 ad13McXeM;SN] s&lid13"m"sjyS$dS5f3 #(-(
oacflg1; fl5185U8 frm18 csi1NX8 si:185N off1 $)sbbbfp1ffffffffXdUX;bWN bln185N avl15; flg1W value1P(>0#(<>ITK0P>O=I-(0ID (C(< RMEc1,e18UM,p1,cr1,cu1,mis1,r1,dep18,og18,plh18;XMSNWMN8,tim1NNUUNS88MSX =(T< RMEc1,e15N,p1,cr1;,cu1,mis1,r18,dep18,og18,plh18;XMSNWMN8,tim1NNUUNS88M8X8 <-O#( RMEc1,e15,dep18,type1;,tim1NNUUNS88M58N 111111111111111111111 PA>#I/ I/ <>#O> RW len15; dep18 uid18X; oct1; lid18X; tim1NNUUNS88M5S hv1;WXWWS5MW8 ad13;aeea;da] s&lid13WWdcXUXajydh;] #(-(
BindR5 oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off1MN $)sbbbfp1ffffffffXdUX;baN bln155 avl1M flg18 value1W;;M BindR; oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off1X5 $)sbbbfp1ffffffffXdUX;bc bln155 avl18 flg18 value1 (C(< RWEc1,e1;5W,p1,cr1,cu1,mis1,r1,dep18,og18,plh15N5SXWU8,tim1NNUUNS88MWSS =(T< RWEc1,e18S,p1,cr1;,cu1,mis1,r1,dep18,og18,plh15N5SXWU8,tim1NNUUNS88MUWS<-O#( RWEc1,e18,dep18,type1;,tim1NNUUNS88MX8 111111111111111111111 PA>#I/ I/ <>#O> RM len18S8 dep18 uid18X; oct1; lid18X; tim1NNUUNS88MNM; hv1;;;;N;W ad13;bedfeMN] s&lid13X&sXf)NS8SMu83#(-(
$)sbbbfp1ffffffffXdUX;bXN bln155 avl1; flg1W value158SS BindR8 oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off15M $)sbbbfp1ffffffffXdUX;bS bln155 avl15 flg18 value1N BindR5 oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off1MN $)sbbbfp1ffffffffXdUX;baN bln155 avl1M flg18 value188 BindR; oacdty15 m)l155'58+ m)lc1 mal1 scl1 pre1 oacflg1; fl5185U8 frm1 csi1 si:1 off1X5 $)sbbbfp1ffffffffXdUX;bc bln155 avl18 flg18 value1 (C(< RMEc1,e1;5M,p1,cr1,cu1,mis1,r1,dep18,og18,plh15N5SXWU8,tim1NNUUNS88M88W5=(T< RMEc1,e18X,p1,cr1M,cu1,mis1,r18,dep18,og18,plh15N5SXWU8,tim1NNUUNS88M85N <-O#( RMEc1,e1X,dep18,type1;,tim1NNUUNS88M85WU
t$prof utility The trace files obtained from above method is in ra" form "hich can be converted into more readable format using t$prof utility 'Transient !ernel P>O=ile utility+
t$prof sageE t$prof tracefile outputfile e)plain1 J table1 J print1 J insert1 J sys1 J sort1 J table1schema.tablename se 2schema.tablename3 "ith 2e)plain13 option. e)plain1user?pass"ord print1integer
-ist only the first 2integer3 #- statements.
aggregate1yes7no insert1filename -ist #- statements and data inside I/#(>T statements. sys1no
T!P>O= does not list #- statements run as user #K#.
record1filename >ecord non*recursive statements found in the trace file. "aits1yes7no sort1option
>ecord summary for any "ait events found in the trace file. #et of :ero or more of the follo"ing sort optionsE
prscnt number of times parse "as called prscpu cpu time parsing prsela elapsed time parsing
prsds$ number of dis$ reads during parseprs&ry number of
buffers for consistent read during parseprscu number of buffers for current read during parseprsmis number of misses in library cache during parse
e)ecnt number of e)ecute "as called e)ecpu cpu time spent e)ecuting e)eela elapsed time e)ecuting e)eds$ number of dis$ reads during e)ecute e)e&ry number of buffers for consistent read during e)ecute e)ecu number of buffers for current read during e)ecute e)ero" number of ro"s processed during e)ecute
e)emis number of library cache misses during e)ecute fchcnt number of times fetch "as called fchcpu cpu time spent fetching fchela elapsed time fetching fchds$ number of dis$ reads during fetch fch&ry number of buffers for consistent read during fetch fchcu number of buffers for current read during fetch fchro" number of ro"s fetched userid userid of user that parsed the cursor Some examples t$prof file.trc file.t)t sys1no e)plain1userid?pass"ord sort1prsela,e)eela,fchela t$prof file.trc file.t)t sys1no e)plain1userid?pass"ord sort1prsela,e)eela,fchela t$prof file.trc file.t)t sys1no t$prof file.trc file.t)t sys1no e)plain1userid?pass"ord sort1prsela,e)eela,fchela Example content of tkprof file
T!P>O=E >elease 88.5..M. F Production on Tue @an 8X 8ME85EM8 58;
1 number of times O
cpu
1 cpu time in seconds e)ecuting
elapsed 1 elapsed time in seconds e)ecuting dis$ &uery
1 number of physical reads of buffers from dis$ 1 number of buffers gotten for consistent read
current 1 number of buffers gotten in current mode 'usually for update+
ro"s
1 number of ro"s processed by the fetch or e)ecute call
66666666666666666666666666666666666666666666666666666666666666666666666666666666 #- IDE U"N58sggrtys) Plan ashE 5;5WXXUXXW#(-(
=>O% =/D0#(>0D(#!TOP0OB@(
(>( #(>0ID 1 Eb8 A/D APP-I
count
cpu
elapsed
dis$
&uery
current
ro"s
ZZ* ZZ ZZF ZZZ* ZZZ* ZZZ* ZZZ* ZZZ* Parse
8
()ecute
.
8
=etch
5
. .
.
.
.
M
8
ZZ* ZZ ZZF ZZZ* ZZZ* ZZZ* ZZZ* ZZZ* total
M
.
.
M
8
%isses in library cache during parseE Optimi:er modeE A--0>O# Parsing user idE 8X; 'APP#+ /umber of plan statistics capturedE 8 >o"s '8st+ >o"s 'avg+ >o"s 'ma)+ >o" #ource Operation ZZZ* ZZZ* ZZZ* ZZZZZZZZZZZZZZZZZ 8
8
8 #O>T O>D(> BK 'cr1M pr1 p"1 time1 us cost1U si:e1M8 card18+
8
8
8
8
8
8
TAB-( A<<(## BK I/D(C >OID =/D0#(>0D(#!TOP0OB@(
p"1 time1 us cost1W si:e1M8 card18+ 8
8
8
I/D(C >A/( #0D(#!TOP0OB@(
time1 us cost1; si:e1 card1;+'object id ;;WSU+
>o"s
()ecution Plan
ZZ* ZZZZZZZZZZZZZZZZZ #(-(
8
TAB-( A<<(## %OD(E A/A-K_(D 'BK I/D(C >OID+ O=
2=/D0#(>0D(#!TOP0OB@(
I/D(C %OD(E A/A-K_(D '>A/( #
2=/D0#(>0D(#!TOP0OB@(
Times %a). ait Total aited
ZZZZZZZZZZZZZ* aited ZZZ* ZZZZ #-6/et message to client
W
#-6/et message from client
. W
.
.
.66666666666666666666666666666666666666666666666666666666666666666666666666666666 #- IDE 5XUut5yXy"&u) Plan ashE ;NWU885W5N select object0name, icon0name from fnd0des$top0objects call
count
cpu
elapsed
dis$
&uery
current
ro"s
ZZ* ZZ ZZF ZZZ* ZZZ* ZZZ* ZZZ* ZZZ* Parse
8
()ecute =etch
8 ;
. . .
.
. .
U
MX
ZZ* ZZ ZZF ZZZ* ZZZ* ZZZ* ZZZ* ZZZ* total
W
.
.
U
MX
%isses in library cache during parseE Optimi:er modeE A--0>O# Parsing user idE 8X; 'APP#+ /umber of plan statistics capturedE 8 >o"s '8st+ >o"s 'avg+ >o"s 'ma)+ >o" #ource Operation ZZZ* ZZZ* ZZZ* ZZZZZZZZZZZZZZZZZ MX
MX
MX TAB-( A<<(## =-- =/D0D(#!TOP0OB@(
cost15 si:e188XW card1MX+ >o"s
()ecution Plan
ZZ* ZZZZZZZZZZZZZZZZZ #(-(
Times %a). ait
Total aitedZZZZZZZZZZZZZ* aited ZZZ* ZZZZ
#-6/et message to client #-6/et message from client
M
. M
.
. .
66666666666666666666666666666666666666666666666666666666666666666666666666666666
trcsess utlity hen using shared server sessions, many processes are involved. The trace pertaining to the user session is scattered across different trace files belonging to different processes. This ma$es it difficult to get a complete picture of the life cycle of a session. The trcsess utility consolidates trace output from selected trace files based on several criteria trcsess output1output0file0nameJ
session1session0idJ clientid1client0idJ service1service0nameJ action1action0nameJ module1module0nameJ trace0filesJ trcsess output1main.trc service1T(#T 6trc
After the consolidate trace file had been generated you can e)ecute t$prof on it.
o" to run s&l tuning e)pert for particular s&l *d in the
D(<-A>( my0tas$0name [A><A>5';+4 B(I/ my0tas$0name E1 DB%#0#-T/(.<>(AT(0T/I/0TA#!' s&l0id 19 , scope 19 2
B(I/ DB%#0#-T/(.(C(<T(0T/I/0TA#!' tas$0name 19 2e)ample *83+4 end4 ? 4et the summar. of recommendation #(-(
How to accept the profile recommended b. the s0l tuning expert D(<-A>( my0s&lprofile0name [A><A>5';+4 B(I/ my0s&lprofile0name E1 DB%#0#-T/(.A<<(PT0#-0P>O=I-( ' tas$0name 193e)ample *8], name 19 2my0s&l0profile3+4 (/D4 ?
5 Performance Terms explained
hat is -ogical I?O and Physical I?O in Oracle Database %any times "e get confused "ith -ogical I?O and Physical I?O in Oracle Database. I am trying to clear all the doubt about "hat is -ogical I?O and Physical I?O in Oracle Database What is "ogical 67O It is the data bloc$ accessed from Buffer
• • • • • •
What is Ph.sical 67O • • •
• • • •
it is data bloc$ re&uested from Physical dis$s or Operation system It could be from Operation system cache Data bloc$s must be placed into memory in order to be accessed to satisfy the ac&uisition of ro"s for a &uery3s result set Its statistics in captured in physical reads statistics This stats is clearly visible as physical reads in Autotrace e)ecuted e can find this for statement from pr in ra" trace dat This stats is clearly visible as dis$s in t$prof output
89S+SST%T
se the follo"ing &uery 'or similar+ to get the information from this dictionary vie"E select name, value from v\sysstat "here name in '2consistent gets3,3db bloc$ gets3,3physical reads3+4 Which is better logical 67O and ph.sical 67O in terms of performance A physical I?O is not good for performance of &ueries . henever a physical I?O ta$es place, Oracle tries to read the data bloc$ from the dis$ "hich "ill be slo". The goal hence is to avoid physical I?O as far as possible. A -ogical I?O is considered better for performance '"hen compared to Physical I?O+ because the reads happen from the memory as the data bloc$ is pre*fetched from the dis$. #o no" Oracle does not need to go to dis$ to fetch bloc$s for your &uery results. But it is important to note that e)cess of -ogical reads 'I?O+ is also not good or recommended. There are many reasons for that 8+ a logical read might have resulted in a physical read to fetch the data bloc$ into the buffer cache. 5+ every time a bloc$ is read from the cache, a loc$ or latch is ac&uired on the cache and hence higher logical reads could mean that there are high chances of buffer cache contention. #o our goal should be to access least number of logical I?O for &ueries inorder to improve its performance
o" to find session generating lots of redo To find sessions generating lots of redo, you can use either of the follo"ing methods. Both methods e)amine the amount of undo generated. hen a transaction generates undo, it "ill automatically generate redo as "ell.how to find session generating lots of redo 8+ uery [\#(##0IO. This vie" contains the column B-OO% v\session s, v\sess0io i M (>( s.sid 1 i.sid W O>D(> BK W desc, 8, 5, ;, M4 >un the &uery multiple times and e)amine the delta bet"een each occurrence of B-OA< 5+ uery [\T>A/#A
The &uery you can use isE #-9 #(-(
o" to calculate IOP# of an oracle database What is 6OPS IOP# 'Input?Output Operations Per #econd, pronounced eye*ops+ is a common performance measurement used to benchmar$ computer storage devices li$e hard dis$ drives 'DD+, solid state drives '##D+, and storage area net"or$s '#A/+ How to calculate 6OPS of an oracle database In Oracle database "e measure ho" much of such re&uests are generated by the database system. e have to chec$ "hether our hard"are are capable of processing the re&uest generated by the database system in order to maintain the performance.IOP# is calculated in Oracle database as the sum of Physical >ead Total IO >e&uests Per #ec and Physical rite Total IO >e&uests Per #ecThese values "ill be available in the table, dba0hist0sysmetric0summary
ere is a &uery on How to calculate 6OPS of an oracle database brea$ on report compute sum of [alue on report select %(T>I<0/A%(,avg'A[(>A(+ as [alue from dba0hist0sysmetric0summary "here %(T>I<0/A%( in '2Physical >ead Total IO >e&uests Per #ec3,3Physical rite Total IO >e&uests Per #ec3+ Some important terms related to 6OPS in metrics Physical read IO requests F /umber of read re&uests for application activity 'mainly buffer
cache and direct load operation+ "hich read one or more database bloc$s per re&uest. This is a subset of physical read total IO re&uests statistic. Physical read total IO requests F /umber of read re&uests "hich read one or more database
bloc$s for all instance activity including application, bac$up and recovery, and other utilities.
Physical write IO requests F /umber of "rite re&uests for application activity 'mainly buffer
cache and direct load operation+ "hich "rote one or more database bloc$s per re&uest. Physical write total IO requests F /umber of "rite re&uests "hich "rote one or more database
bloc$s from all instance activity including application activity, bac$up and recovery, and other utilities. physical read total multi block requests – /umber of large read re&uests "hich read multi
database bloc$s for all instance activity physical write total multi block requests */umber of large "rite re&uests "hich "rite multi
database bloc$s for all instance activity physical read total bytes *Total bytes read "hich read one or more database bloc$s for all
instance activity including application, bac$up and recovery, and other utilities. physical write total bytes FTotal bytes "rite "hich read one or more database bloc$s for all instance activity including application, bac$up and recovery, and other utilities. To calculate small readsE #mall >eads 1 Total >eads F -arge >eads #mall rites 1 Total rites F -arge rites These metrics can be used ta$en at t"o point of time can also be used to calculate IOP# for small read,"rire,large "rite,large read , total bytes per sec etc
hat is DB time and Average Active sessions, Active session in oracle In this section "e "ill be defining the terms line Database time, Action session and Average active session Database Time, or DB Time, is defined by Oracle as the total time by foreground sessions e)ecuting database calls. This includes
Performance -esson Ehat is a Bind [ariable hat is a Bind [ariableQ *It is a placeholder for a value *In P-?#- it is called a local or global variable. D(<-A>( l0empl0id D(PT.emp0idYTKP(4 B(I/ select first0name from D(PT "here id 1 l0empl0id4 (/D4 *in #-6Plus it is a variable variable a number B(I/ Ea 1 84 dbms0output.put0line'a+4 (/D4* #&l "ithout binds #(-(
o" to create ADD% tas$ and chec$ its report In this post, I "ill e)plain o" to create ADD% tas$ and chec$ its report e are using start snapshot as S And end snapshots as SW B(I/ Z
Z #et the start and end snapshots. DB%#0AD[I#O>.set0tas$0parameter ' tas$0name 19 2S0SW0A>0#/AP#OT3, parameter 19 2#TA>T0#/AP#OT3, value 19 S+4 DB%#0AD[I#O>.set0tas$0parameter ' tas$0name 19 2S0SW0A>0#/AP#OT3, parameter 19 2(/D0#/AP#OT3, value 19 SW+4 Z ()ecute the tas$. DB%#0AD[I#O>.e)ecute0tas$'tas$0name 19 2S0SW0A>0#/AP#OT3,+4 (/D4 ? Z Display the report. #(T -O/ 8 #(T PA(#I_( W #(-(
Oracle A#'Active #ession istory+ seful &ueries %cti/e Session Histor. 'A#+ "as introduced in Oracle 8g. It samples the activity of each active database session every second. The data is held in a buffer in memory in the database.The design goal is to $eep about an hour 'your mileage "ill vary+. If a session is not active it "ill not be sampled. The in*memory buffer is e)posed via a vie" called v\active0session0history. hen an A> snapshot is ta$en, 8 ro" in 8 from the A# buffer is copied do"n into the A> repository. It can also be flushed to dis$ bet"een snapshots "hen the buffer reaches UUY full, so there is no missed data.The data is stored in >\0A
alter system set “_ash_enable”=False; I "ant to emphasi:e that if the session is not active it "ill not be sampled. Kou can actually set a parameter 0ash0enable0all 1 T>( to force all sessions, including idle sessions, to be
sampledSome important points in this regard 8+Oracle has provided a pac$age called DB%#0APP-I
SELECT sql_id,COUNT(!,"OUN#(COUNT(!$SU%(COUNT(!! O&E"(!, '! CTLO)# F"O% *+a-ti+e_sessi.n_hist.ry /0E"E sam1le_time 2 S3S#)TE 4 5$'6 )N# sessi.n_ty1e = 78)C9:"OUN# :"OU 83 sql_id O"#E" 83 COUNT(! #ESC; SELECT sql_id,COUNT(!,"OUN#(COUNT(!$SU%(COUNT(!! O&E"(!, '! CTLO)# F"O% *+a-ti+e_sessi.n_hist.ry /0E"E sam1le_time 2 S3S#)TE 4 5$'6 )N# sessi.n_ty1e = 7FO"E:"OUN# :"OU 83 sql_id O"#E" 83 COUNT(! #ESC; ;+ To find out the wait e/ents for which this session
SELECT sam1le_time, e+ent,
=>O% gv\active0session0history (>( sample0time 9 #K#DAT( F 8?5M A/D session0type 1 2BAO/D3 >OP BK s&l0id O>D(> BK
W+ #ost 67O intensi/e s0l in last : hour #(-(
U+"ocking information in last : min
col event for a55
col bloc$0type for a8N col objn for a8N col otype for a8 col fn for SS col sid for SSSS col bsid for SSSS col lm for SS col p; for SSSSS col bloc$n for SSSSS select to0char'sample0time,3E%I3+ st, substr'event,,5+ event, a.session0id sid, mod'a.p8,8U+ lm, a.p5, a.p;, nvl'o.object0name,ash.current0objR+ objn, substr'o.object0type,,8+ otype, <>>(/T0=I-(R fn, <>>(/T0B-O
all0objects o "here event li$e 2en&E TCY3 and o.object0id '`+1 a.<>>(/T0OB@R and sample0time 9 sysdate F M?'U65M+ Order by sample0time ?
X+ top s0ls spent more on cpu7wait7io select
ash.#-0ID , sum'decode'a.session0state,3O/
How To 4enerate %SH Report
Oracle Provide a standard script just li$e A> to analy:e the A#% data I "ill e)plain ho" to generate A#'Active #ession istory+ reports from #-P-#. The same can be done using (nterprise %anager also.
#cript -ocation is same as other standard script \O>A<-(0O%(?rdbms?admin? #-9 V\O>A<-(0O%(?rdbms?admin?ashrpt.s&l
All about A> ' Automatic or$load >epository+ What is %WR?
Automatic or$load >epository 'A>+ is a collection of persistent system performance statistics o"ned by the #K# user. It resides in #K#AC tablespace. By default snapshot are generated once every Umin and maintained for X days.A> report are used to investigate performance and other issues. Script %/ailabe in 9OR%2"E;HO#E7rdbms7admin awrrpt.sql EDepending on the reasons for collecting the report, the default can be used, or for a more focused vie", a short 8*8W minute snapshot could be used.awrrpti.sql EDisplays statistics for a range of snapshot Ids on a specified database and instance. awrsqrpt.sql EDisplays statistics of a particular #- statement for a range of snapshot Ids. >un this report to inspect or debug the performance of a particular #- statement. awrsqrpi.sql EDisplays statistics of a particular #- statement for a range of snapshot Ids on a specified #-. awrddrpt.sql E
-seful Operations on %WR
a+ o" to %odify the a"r snapshot settings. B(I/ DB%#0O>!-OAD0>(PO#ITO>K.modify0snapshot0settings' retention 19 58U, Z %inutes '58U 1 8W Days+. Z
b+ Dropping the A> snaps in rangeE B(I/ DB%#0O>!-OAD0>(PO#ITO>K.drop0snapshot0range' 'lo"0snap0id19M, igh0snap0id19N+4 (/D4 ?
c+
Oracle DBAE anganaly:e and system state dump %any times Oracle database get hangs due to loc$s or latching issue and /obody can login to the system. It becomes critical and And sually "e do shutdo"n abort to immediately resolve the issue. But it is good to ta$e system state dump?hanganaly:e at 8 min interval to analy:e the loc$ing or latching issue after"ards before doing shutdo"n abort so that "e can find the fi) for the problem and avoid that in future.Oracle #upport also usually as$ for these "hen the database hangs for some reason Here are the steps to take s.stem state dump
In >A< system this need to be e)ecuted on all the instance #ynta) for system state dumpE -ogon to s&lplus as sysdba #-9 oradebug setmypid #-9 oradebug unlimit #-9 oradebug dump systemstate 8 .. ait at least 8 min #-9 oradebug dump systemstate 8 .. ait at lease 8 min #-9 oradebug dump systemstate 8 #imilarly anganaly:e is a good utility to analysis. Here are the steps
#ynta) for hangaanaly:e for single instance -ogon to s&lplus as sysdba oradebug setmypid4 oradebug unlimit4 oradebug hanganaly:e ;4 #ynta) for hanganaly:e for >A< -ogon to s&lplus as sysdba oradebug setmypid4 oradebug unlimit4 oradebug setinst all oradebug *g def hanganaly:e ; #ometimes it is not possible to even login "ith s&lplus ? as sysdba at the time of hang, In that situation "e can use belo" command to enter "ith 88g on"ards s&lplus *prelim ? as sysdba
Oracle ait (vents That (veryone #hould !no" Part *8 This is part I in series of Oracle ait (vents That (veryone #hould !no" ere are some of the
I am trying to e)plain fe" common Oracle "ait events ,there causes and resolutions en0ueue
The process is "aiting on an en&ueue 'a loc$ you can see in v\loc$+. This commonly occurs "hen one user is trying to update a ro" in a table that is currently being updated by another user. The bloc$ers can be find out by using follo"ing &uery select 6 from dba0"aiters librar. cache pin The process "ants to pin an object in memory in the library cache for e)amination, ensuring no other processes can update the object at the same time. This happens "hen you are compiling or parsing a P-?#- object or a vie". Avoid compiling P-?#- object or vie" at high usage time to avoid this "ait event
librar. cache load lock The process is "aiting for the opportunity to load an object or a piece of an object into the library cache. 'Only one process can load an object or a piece of an object at a time.+ latch free The process is "aiting for a latch held by another process. 'This "ait event does not apply to processes that are spinning "hile "aiting for a latch4 "hen a process is spinning, it is not "aiting.+.-atches are light"eight seriali:ation devices used to coordinate multiuser access to shared data structures, objects, and files. -atches are loc$s designed to be held for e)tremely short periods of time for e)ample, the time it ta$es to modify an in*memory data structure. They are used to protect certain memory structures, such as the database bloc$ buffer cache or the library cache in the shared pool. -atches are typically re&uested internally in a 2"illing to "ait3 mode. This means that if the latch is not available, the re&uesting session "ill sleep for a short period of time and retry the operation later. Other latches may be re&uested in an 2immediate3 mode, "hich is similar in concept to a #(-(
Oracle ait (vents That (veryone #hould !no" part*II This is part II in series of Oracle ait (vents That (veryone #hould !no" control file se0uential read The process is "aiting for bloc$s to be read from a control file. This happens generally • • • •
ma$ing a bac$up of the controlfiles sharing information 'bet"een instances+ from the controlfile reading other bloc$s from the controlfiles reading the header bloc$
If this is major "aiting event, it means control file location need to changed to faster dis$ location
control file parallel write The process has issued multiple I?O re&uests in parallel to "rite bloc$s to all control files, and is "aiting for all of the "rites to complete. log buffer space The process is "aiting for space to become available in the log buffer '#pace becomes available only after -> has "ritten the current contents of the log buffer to dis$.+ This typically happens "hen applications generate redo faster than -> can "rite it to dis$.
This can also happen, if the I?O to dis$ "here redo logs are located is slo" There should be no log buffer space "aits as such in the database.
A slo" -> process can introduce log file sync "aits "hich ma$es the user to e)perience "ait times during commit or rollbac$. The log file parallel "rite and log file sync "ait events are interrelated and must be dealt simultaneously.
e must try to allocate the redo logs to high performance dis$'#olid state dis$+. Also "e should try to reduce the load on -> by reducing commits in the applications. The manual hotbac$up piece can also introduce stress in the system by generating lot of redo stuff,#o avoid that during pea$ time #ometimes -> is starving for
Oracle ait (vents That (veryone #hould !no" Part *III -ets get going "ith the Third part db file scattered read The process has issued an I?O re&uest to read a series of contiguous bloc$s from a data file into the buffer cache, and is "aiting for the operation to complete. This typically happens during a full table scan or full inde) scan.
e should chec$ if the &uery should be using full table scan. %a$e sure optimi:er stats are p to date. se Partition pruning to reduce number of bloc$s visited If an &uery that has been running fine for a "hile suddenly cloc$s a lot of time on the db file scattered read event and there hasn3t been a code change, you might "ant to chec$ to see if one or more inde)es has been dropped or become unusable. db file se0uential read The process has issued an I?O re&uest to read one bloc$ from a data file into the buffer cache, and is "aiting for the operation to complete. This typically happens during an inde) loo$up or a fetch from a table by >OID "hen the re&uired data bloc$ is not already in memory. Do not be misled by the confusing name of this "ait eventL
e should be chec$ing if the right inde)es are being used. A "rong inde) can ma$e the &uery perform badly.%a$e sure optimi:er stats are p to date. db file parallel read The process has issued multiple I?O re&uests in parallel to read bloc$s from data files into memory, and is "aiting for all re&uests to complete. The documentation says this "ait event occurs only during recovery, but in fact it also occurs during regular activity "hen a process batches many single bloc$ I?O re&uests together and issues them in parallel. 'In spite of the
name, you "ill not see this "ait event during parallel &uery or parallel D%-. In those cases "ait events "ith PC in their names occur instead.+ db file parallel write The process, typically DB>, has issued multiple I?O re&uests in par allel to "rite dirty bloc$s from the buffer cache to dis$, and is "aiting for all re&uests to complete. direct path read< direct path write The process has issued asynchronous I?O re&uests that bypass the buffer cache, and is "aiting for them to complete. These "ait events typically involve sort segments.
#- statements "ith functions that re&uire sorts, such as O>D(> BK, >OP BK, /IO/, DI#TI/
The shared pool latch is used to protect critical operations "hen allocating and freeing memory in the shared pool.
o" to find "hich sid is doing full table scan %any times the 1er?.rman-e .? the database
-.lAmn r.
Optimi=er
/e" Optimi:er =eature "ith 88g !ew Optimi=er (eature with ::g
8+ Invisible inde)es ith 88g release,inde) can be made invisible. They "ill be maintained all the time and can be made visible any time alter inde) ! invisible4 Optimi:er "ill not use the inde) if it is invisible alter inde) ! visible If "ant one &uery to use invisible inde),"e can set this parameter in the session optimi:er0use0invisible0inde)1true Then the &uery in the session "ill use the invisible inde) 5+()tented stats on the table If "e use any function on the predicate in the &uery,the optimi:er is not able to get the selectivity properly.ith 88g "e can create e)tented stats on the column for the function .
e)ec dbms0stats.create0e)tented0stats'o"nname 19 2O/3,tab0name19 2TAB3 ,e)tension19 2func'col+3+4 Then optimi:er "ill be able to find the correct selectivity
o" to use Optimi:er hints What is Optimi=er? hen a &uery is submitted in Oracle server. optimi:er attempts to generate the best e)ecution plan for a #- statement. The best e)ecution plan is defined as the plan "ith the lo"est cost among all considered candidate plans. The cost computation accounts for factors of &uery e)ecution such as I?O,
The best method of e)ecution depends on myriad conditions including ho" the &uery is "ritten, the si:e of the data set, the layout of the data, and "hich access structures e)ist. The optimi:er determines the best plan for a #- statement by e)amining multiple access methods, such as full table scan or inde) scans, and different join methods such as nested loops and hash joins. What is a Optimi=er Hint Q Optimi:er ints are clause that used in s&l &ueries to force the optimi:er to ta$e particular route li$e using inde),nested loop join.S.ntax to use hint
?6` ^ int name96? Some of commonl. used hint
#(-(
[arious @oins %ethod in Oracle Performance is $ey for O-TP system and "e should have good information about @oins %ethod in Oracle to increase performance for s&l &ueries ere are the @oins %ethod in Oracle available in Oracle database
!ested "oops *=or each ro" in the first ro" source access all the ro"s from the second ro" source. *Best for O-TP type transactions *it "ill be =astest if ro"s returned from first table are small *The optimi:er first determine the driving table and designates it as the outer loop.This is the driving ro" source. It produces a set of ro"s for driving the join condition. The ro" source can be a table accessed using inde) scan or full table scan. The ro"s can be produced from any other operation too. =or e)ample the output from a /ested -oop @oin can be used as a ro" source. *The optimi:er designate other table as inner -oop.This is iterated for every ro" returned from the outer loop. This is an access operation on a table and ideally should be an inde) scan. *Operation performed by I//(> table is repeated for every ro" returned in OT(> table *If the optimi:er is choosing to use some other join method, you can use the #(0/-'A B+ hint, "here A and B are the aliases of the tables being joined.
Plan ZZZZZZZZZZZZZZZZ* #(-(
()ecution Plan %(>( @OI/ #O>T @OI/ TAB-( A<<(## =-- O= O(0O>D(>0-I/(#0A-#O>T @OI/ TAB-( A<<(## =-- O= O(0(AD(># Hash $oin *#mallest table is passed over and a hashing algorithm is applied to each ro" to create a hash table in memory. *#econd table is passed over and the same hashing algorithm applied to chec$ for matches 'ie. joins+ *=aster than sort merge joinE sort operation re&uired by sort merge join can be e)pensive if tables are large and not in any matching order *Apply the #(0A# hint to instruct the optimi:er to use a hash join "hen joining t"o tables together. *(nsure that hash0area0si:e is large enough to hold the smaller table in memory. Other"ise, Oracle must "rite to the T(%P tablespace, slo"ing do"n the hash join
Plan ZZZZZZZZZZZZZZZZ* #(-(
Plan ZZZZZZZZZZZZZZZZ* #(-(
#O>T /I( %(>( @OI/ T(#IA/ /(#T(D -OOP# TAB-( A<<(## BK I/D(C >OID O(0O>D(>0-I/(#0A-I/D(C >A/( #D(>0-I/(#0A--0/8 TAB-( A<<(## BK I/D(C >OID O(0(AD(># I/D(C >A/( ##0/8 #O>T @OI/ I/D(C =A#T =-- ##0/8 Statistics
=ind inde)es and assigned columns for a table It is very common that you "ant to $no" ? see the available inde) on a table. And also you sometime "ant to $no" the columns "hich are assigned to the inde)es. =ollo"ing vie"s "ould be helpful in these cases dba0ind0columns all0ind0columns set pagesi:e W verify off echo off col table0name head 2Table /ame3 format a5 col inde)0name head 2Inde) /ame3 format a5W col column0name head 2
o" Table %onitoring and #TATI#TI<#0-([(- parameter are connected from 8g on"ards *efore Oracle :5g *Automated collection of statistics for objects that had become stale "as controlled by the setting of the %O/ITO>I/ flag on table. Depending on the %O/ITO>I/ flag, the AT(>0#TAT#0@OB job collected AT(> (%PTK and AT(> #TA-( on the flagged objects. :5g onwards *The %O/ITO>I/ and /O%O/ITO>I/ $ey"ords are deprecated and "ill be ignored.Table*monitoring feature is no" controlled by the #TATI#TI<#0-([(- parameter.
hen #TATI#TI<#0-([(- is set to BA#I<, monitoring is disabled on the table. hen #TATI#TI<#0-([(- is set to TKPI
Oracle TutorialE o" to chec$ #tale statistics #tats can become stale "hen the data in the table changes substantially.p to date statistics are important to generate good e)ecution plans o" Oracle decide if the stats has become stale #tats are considered stale "hen R'I/#(>T# ` PDAT(# ` D(-(T(#+ 91 8Y of /%0>O# from dba0tablesE Parameter setting re&uired to Trac$ the table changes Prior to Oracle 8g, automated collection of statistics for objects that had become stale "as controlled by the setting of the %O/ITO>I/ flag on table. Depending on the %O/ITO>I/ flag, the AT(>0#TAT#0@OB job collected AT(> (%PTK and AT(> #TA-( on the flagged objects. In 8g the %O/ITO>I/ and /O%O/ITO>I/ $ey"ords are deprecated and "ill be ignored.Table*monitoring feature is no" controlled by the #TATI#TI<#0-([(- parameter. hen #TATI#TI<#0-([(- is set to BA#I<, monitoring is disabled on the table. hen #TATI#TI<#0-([(- is set to TKPI
=O> $ in Obj-ist.=I>#T..Obj-ist.-A#T -OOP dbms0output.put0line'Obj-ist'i+.o"nname 77 2.3 77 Obj-ist'$+.Obj/ame 77 2 2 77 Obj-ist'$+.ObjType 77 2 2 77 Obj-ist'$+.partname+4 (/D -OOP4 (/D4 ? The belo" s&l can also be used to find out insert,updates,deletes select u.TI%(#TA%P, t.last0analy:ed, u.table0name, u.inserts, u.updates, u.deletes, d.num0ro"s, decode'num0ro"s,,3Table #tats indicate /o >o"s3, nvl'TO0<A>'''.inserts`u.deletes`u.updates+?d.num0ro"s+ 6 8,3SSS.SS]+ ,3/ull [alue in #(>0TAB0%ODI=I
#tarting "ith Oracle88g, the staleness threshold can be set using the #TA-(0P(><(/T statistics preference. This can be set globally using DB%#0#TAT#.#(T0-OBA-0P>(=# or at the table level using DB%#0#TAT#.#(T0TAB-(0P>(=#.
o" to find table "here statistics are loc$ed Kou can use the belo" &uery on o" to find table "here statistics are loc$ed select o"ner, table0name, stattype0loc$ed from dba0tab0statistics "here stattype0loc$ed is not null4
/o" once "e find out the objects,"e can use belo" &ueries to unloc$ them e)ec dbms0stats.unloc$0schema0stats'2schema0o"ner3+4 e)ec dbms0stats.unloc$0table0stats'2table0o"ner3,3table0name3+4 4eneral Performance topics
s&l performance tuning #&l statement are "ritten to retrive ?fetch data from the database. e "ant our s&l statement to running fast and providing the results in secs. A poorly design s&l could slo" do"n the entire database operation and bring the entire operation to halt. It is much harder to "rite efficient #- than it is to "rite functionally correct #-. s&l performance tuning can signficantly improve a system3s health and performance. The $ey to tuning #- is to minimi:e the data it accesess to provide the result. e can minimi:e the data it accesses to provide the result through optimal search path. A simple e)ample "ould be select 6 from dept "here emp18 /o" this &uery "ill have to search the entire table dept to find out the data "here emp18. #o it has to access the full table /o" if "e create the inde) to emp column, then it can just access the inde) and get the result. #o here it access the less data ere the general tips for s&l performance tuning 8+ =irst you must have all the re&uired tools for s&l tuning .Kou must have good information on tracing,formating the trace, e)plain plan,reading th e)plain plan. ood $no"legde about the various join method availabe in Oracle and ho" to use them efficently 8+ >ead less data and be I?O efficient. The more data you read for the s&l statement, the more latches it need to ac&uire and it slo"s do"n the performance. so it must be doing fe"er logical reads al"ays rite sensible s&l statement "here proper filters . Do chec$ about the number of ro"s in various tables involved and find out the best method to create the s&l statement 5+ se good inde)es BTree inde)es and Bitmap inde)es can be used to increased the performance of the &ueries if the data returned is less than 8Y. But "e must be careful "hile creating the inde) as it need to maintained for insert,update and delete operation also. #o creating an inde) create overhead over many things. #o "e must carefull e)amine the effect of creating the inde). ;+ Avoid s&l "hich disable the usage of inde)
#- that Disables Inde)es =unctions ' to0char'+, to0date'+, etc. + move the function to the constant?bind variable side Type
?6` #(0/- 'table alias8 table alias 5+6? as$s optimi:er to use the /ested -oop @oin for the t"o tables specified Avoid unneccsary hint and use them "ith care
s&l performance tuning some &uestions Here are some of s0l performance tuning examination 0uestions
8+ follo"ing an A/A-K_( command, "hich of the follo"ing data dictionary vie"s "ill contain statistics on the number of ro"s in a table a dbms0ro"s b dba0table0ro"s c dbs0tables d dbs0statistics #olution 'c+ 5+ histograms are useful because they allo" the optimi:er to have a better idea of ho" the data in an inde)ed columns is a sorted b named c entered d distributed #olution 'd+ ;+ the optimi:er mode can be set at "hich of the follo"ing levels a instance b session c statement d all of the above #olution 'd+ M+ the ability to store predefined e)ecution plans so that the optimi:er $no"s in advance "hich e)ecution plan is best is called a stored outline b materiali:ed vie"s c hash cluster d inde) organi:ed tables #olution 'a+ W+ "hich of the follo"ing types of inde)es stores the inde)ed columns values associated ro" id as binary string a b*tree inde) b reverse $ey inde)
c bitmap inde) d bit"ise inde) #olution 'c+ U+ "hich of the follo"ing factors "ould not ma$e a column a good candidate for b*tree inde) a the data in the column has lo" cardinality b the column fre&uently used in s&l statement "here clases c most &ueries on the table return only small portion of all ro" d none of the above #olution 'a+ X+ the process of preparing a statement for e)ecution is called a caching b hashing c parsing d none of the above #olution 'c+ N+ finding a statement already cached in the library cache is referred as a cache hit b cache miss c cache match d cache parse #olution 'a+ S+ to determine if a ne" statement is a match for an e)isting statement already in the shared pool, oracle compares each statement a result set b security c e)ecution plan d hashed value #olution 'd+ 8+ in order for t"o statements to result in a cache hit, "hich of the follo"ing must be true a the statements must use the same case F either upper , lo"er , or mi)ed b the statements must be issued against the same table c the statements must be on the same number of lines d all of the above must be true #olution 'd+ 88+ "hich dynamic data dictionary vie" contains information about the library cache hit ratio a v\ro"cache b v\librarycache c v\dictionarycache d all of the above