Oracle Database performance analysis using AWR Reports

0:00 for uh oracle database performance tuning optimization uh tidbits or quick session that we have planned so unfortunately we won't be able to cover awr that was of most important uh importance and that's why we asked for another one and a half or you know something like that time to cover remainders alright so today will be all about awr ash without wasting any time click jump into one of the few of the awr reports that i have generated from a few of the systems where we have problems right so this session will be all about like first of all i would like i i would actually start with the introduction what exactly is awr report how frequently it got generated what is the process which actually collects this information for the database so awr is basically a statistics report so it

0:46 stands for automatic workload repository and uh there is this background process called m1 so m1 process is one of the bandwidth process that is within the oracle database that's keep on collecting oracle system statistics which covers user i o system operations weight events and lot of other things so every second it you know it simply go and connects with the database with its memory with this dynamic view and everything and collects that information within a specific pool in the awr repository so that's the physical location of uh of all awr contents that you are actually seeing all right so if we talk about generation of the awr reports uh we discussed this yesterday as well like you will be able to generate the awr report or ash

1:33 reports using your sql developer you only have to enable the dba tab once you enable the dba tab in your sql developer window then automatically you just need to create the connection once connection is established then you can go and you know can generate the awr report so in continuation with what i said uh in last class like it requires few specific privileges the very important privilege is select any dictionary and second privileges which uh which will be really great if you have it is dbms underscore workload underscore repository so these two are the important privileges or permissions once you have that permission to your account that account will be able to generate awr reports as reports and very query lot of things so

2:18 suppose if you have any requirement and you simply have to ask your dba grant this create a you know a monitor account or a sample schema or an account in oracle database and ask your dba to grant these to permission to that specific account select any dictionary and dbms underscore workload underscore repository so once these two permissions will be added to that account you will be able to generate awr reports using that dba tab within the sql developer okay so for example you have that report ready and let's discuss about this production problem you received a complaint from the customer saying okay entire system is low i'm not sure what exactly went wrong but everything was smooth and working fine till two

3:04 hours back or one hour back or 30 minutes back but now everything started you know getting very slow it stalled or freezed i mean we are not even able to do anything usually query takes around 15 minutes but not started taking around 1 hour 30 minutes 40 minutes right so your last elapsed time has been increased and we see a lot of other issues so ultimately this is not specific or relevant to any individual sql statement but this is for the entire oracle database everything is so slow every mod module every program every part of the of the product is working slow so in that scenario of course you your dba will start doing its own investigation but it's always good because you guys are developers you have good understanding a good thought process about your own product so it's always good to actually to ask uh your dbas to provide the awr report

3:50 but yeah along with the ash reports i'll show you what how important ash report is and how it will be helpful to you know to maneuver or to craftly move your analysis and to reach the conclusion right okay about the awr report it was introduced in 2010 uh 200 version 10 sorry oracle 10 i guess before that we have something called stats pack that is almost the look and feel is almost look like awr report except few of the sections were missing but since the inception of 10g especially 10g release one we have this thing called awr report and since then the the product or this feature got very major mature and now we see n number of things coming in pending or added it to the awr reports every with every new

4:38 release of oracle database so if we so for example you have that problem and the customer complained and everything was slow so you generated the awr report using the dba tab of your sql developer and you have opened that awr report in your web browser so this is how the awr looks like this is the you know the overall appearance or the home page the landing page of aws report which provides you some basic information like what is the name of the database right second is the dbid instance and release of the database so this report was generated on 10g release to patch set four database it was a non-rack database no rack no means non-rack database and this second thing is this provides you some begin snap and end snap information so begin snap is

5:24 the point where you actually started collecting the awr report and the end time i mean this will be basically the the uh the probe window or the pro period for example you want to understand what exactly happened from 9 30 on 31st of january 2000 till 10 right so you want to understand what all what exactly happened during this period of almost 30 minutes right so this is the begin snap and the end snap means second is the elapsed time and the most important is the db time so these two are very important metrics for example elapsed time is nothing elapsed time is only the wall clock time of your awr report that means you have generated awr report for almost 30 minutes right so it's a 30 minute long

6:09 period of awr report but db time is what db time is basically a combination or a sum of every operation that has happened into the database for example a query comes it you know it start to you know scan the memory area of your database but due to some reason it started taking time so that time will be counted right and in parallel because database doesn't work serially it works parallelly so within a period of second for example in if you have a very busy system within a fraction of seconds you see thousands of sequels coming and modifying or querying anything in uh from your database so that's how the database works right so it doesn't work serially it works parallely and it depends on the cpu io right and the machine configuration and a lot of other things and cpu count sockets which

6:55 enables or which makes your database more uh efficient and quick and performant right so this this metric db time is of utmost importance because if you do a divide if you do this 15 103.15 by 29.81 let's do the maths 15103.15 divided by 29.81 this will give you 506. what is this this value this figure this means within any within uh this this basically this number is 506.64 is the average number of active sessions what we call as aas in short so average

7:40 number of active session represents at any point at any given point of time during those 29 minutes during those 30 minutes we have these many sessions active in the database and doing some work they they are not it doesn't count the sessions which are idle are not doing work right or inactive this represents the workload how busy your system was at that point of time so during those 30 minutes around every second or every minute of your awr or those 30 minutes we have 500 506 sessions actively doing any work in your database this number uh yeah this one's sorry for the interruption yeah this one does it mean that when a process is taking longer than it

8:27 should take right that is is in that situation the number of average number of active sessions will be more no this average number of active sessions will be more or higher in case when you have sessions which are all started waiting on something and you have subsequent sessions coming which will which also tries to get a get their hands on this on those respective logs for example i uh just give me another five minutes so i'll show you what this aas represents right so just give me a few more time some more time right so this 506 figure this number is of very important this number is very important and the rule of thumb is for example if you have

9:13 32 cpu and you have average number of active sessions coming as 506. so if you divide 32 bit with 506 that means single cpu is what is handling those that that many records for example 506 if we divide with 32 that means single cpu has is handling 15.8 almost 16 average number of active session request that means it's too much right ultimately this uh let me check how many cpus are there in this system uh old report i'm not sure if number of cps okay this is a 16 cpu system and a 506 if you do a divide on 16 so a single cpu is handling 31.6 request on

10:01 almost 32 re active sessions at any given point of time so this is way too much right so that this is what the rule of thumb says okay now coming back so whenever you open you know what i mean guys these are i will be able up now i'm going to unleash you know able to uh discuss about few of the things which even you know some of the dbas don't even understand even you you i've seen a lot of the i've seen a lot of dbas in my career who not even sure how to proceed how to go ahead and how to dissect this awr report they have no idea about this workload information and everything in fact those are the people who have experienced experience more than 10 years or 12 years so the these things are very important are you know this is what i

10:47 learned over the period of time right while doing performance uh investigation on the systems so this is the very important metric in newer releases of oracle you see in another section aes which is doing you know the calculations you need not to calculate this by your own or manually the ais number will be automatically derived for you but those will be in awr reports and 12c release to i guess and usually uh it comes with ash reports as well okay so that's the very first thing i mean you have to check in your awr report but someone can ask me like how i will be able to know like if this aas number is is higher or lower lower i mean we have something some benchmarks right like at this point of time in in uh during our

11:32 weekdays we have uh average number of active session or workload somewhere around 50 or 60 but if your system is you know is always busy and it's always stay like 500 or 550 then it's of no use right so you can say that okay no this is something that is acceptable and we know that our system is busy so it's of i mean this is nothing to be to be alarmed or you know to be afraid of we i know that the system is showing aass 506 or above 500 but this is the normal behavior during this specific this is this is called as a peak time of our system of our database so this is something expected right so you know your system better right and if you want to do that kind of comparative study what you can do you can generate the awr report for the same duration one day ago one

12:20 day you know previously or maybe two days back what was the status what was the workload at that point of time so that will give you some idea to come some something to compare right you generated this report was for 31st of january generated for 30 30th january and see for the same duration 9 30 to 10 what was the average number of active session what was the total db time right so that's how you will be able to do the comparison we'll be able to know if this is something higher lower or this is average or acceptable right any questions now another section is the load profile this is one more important section and this provide you some really good information the first one is the redo size like how much redo was generated

13:05 during that specific period so redo size is as as we discussed last day like redo information is generated when you modify or manipulate anything in your database in case of select you won't see regenerated right so if you do update inserts right and uh deletions uh trunk not truncate uh but alter so if you are modifying anything right so that will incur or generate the redo in your database so it means again how how you can know how you'll be able to understand if this redo size is is is average or if this is okay or not or this is this is really high so for again for that you have to do the comparison comparison uh and there is this another report called as awr compare report right so you need

13:53 not to generate two different report and do the comparison uh right manually but there is this functionality called awr compare report so i'll be explaining about that but yeah let's first take what all sections are of importance so redo size how much redo was regenerated logical reads means the request or blocks which were which were read from the memory not from the disk from the memory so one can ask you ask us a question like if the logical reads are higher then that is something really good because memory scan or memory uh you know traversing is happening so it's not reading anything from the disk because this corporations are always always expensive or costly so if something or some session is doing any scan or a read from the memory so it is

14:38 really good but that's not the case there are few scenarios where you'll see like even the case of logical reads are you know proved to behave for your database and cause some really great fiascos in your system so uh let's not jump into the conclusion again there is always a great scope of getting false positives so you have first need to do your analysis because uh it seems like logical reads is a good figure so every session should read the blocks from the memory because accessing anything from the memory is always quick but one thing comes along with that anything that you ever everything or anything that you do in the memory you need to acquire a lock a latch basically so you have if you want to read anything first you need to acquire a lock and then only oracle

15:24 database will permits that session to scan or to traverse that list which contains information of the blocks so that's why if your system is busy and there is already a shortage of logs or latches then of course logical rates or memory rates are very expensive in fact the if we do a comparison with the with the physical rates physical physical reachment when you are reading something from the desk doing a disk io right read iops so even if you compare it with the physical rates they comes out to be very expensive i'm talking about logical read so that's the case so all right so logical reads uh means when you're reading when your session is reading something from the memory and then comes the block changes i mean almost 300 blocks got got changed during that period block change means

16:10 block contains some information row specific information so if any session comes and modify for example value 10 to 11 that will count as a one one row was changed one block was changed so that's how this counter was increased physical waste when you're doing pios or physical ios when you're reading not from the memory but things are not present in the memory this query is coming for the very first time so it has to actually go and read that those specific number of blocks from the disk so that means physical rates physical rights means simple physical rights mean if you're doing right operations if you're doing an insert if you're doing an update right so that will simply go and write that information into the disk and make the data persistent user calls i mean how many users user i mean the user request were actually found to be found

16:56 running during those 30 minutes passes how many passes happened it could be hard or soft but this is a total count so hard parts hard parts means when this query comes for the very first time and uh it is not sharing anything it has to pass it has to uh you know plan everything by its own because the pa the pre-passed version is not available in the pool or in the memory area of oracle because this is a this is a new entrant this the very first time this sequel is and sql is coming and hitting the database so that's why it will surely go for a hard part there are certain other reasons as well like even after the you know query is running for you know two days but still it's not sharing anything right i will i will be discussing maybe later on next is the

17:43 sorting sorting means for example if you do an aggregate function uh like order by grouped by or doing account so sort count will be higher if you are processing or if you are querying your database uh through those kind of operations right through those sorting operations and then log ons and execute and transactions all right and next comes is the instance efficiency percent percentages target is always 100 but yeah remember guys this is an older approach like this some old school dba still follow this approach like they always first check for the soft pass percentage execute to pass buffer hit and library library hit and they simply declare system pro problematic if they see anything any

18:29 number that is goes beyond 100 because target target is 100 so for example if someone that old school dbsc it's 95.88 i mean buffer it is only ninety five point eight points it simply declares system okay there is definitely some problem but this that's an older approach but nowadays you know the fresh approach which is uh you know circulating among the performance consultants is always rely on weight event approach oracle weight interface rely on that information do not jump into any conclusion only after reading this hit or miss ratios right this is an older approach and system database has been changed a lot since then so you need not to rely on the instance efficiency percentage so that's why i'm not focusing much on this one but yeah it will be really good if

19:15 you know the percentage are close to 100 but that is not any rule of thumb or any you know something hard and fast like okay uh if the soft pass percentage is only 90 and it should be 100 so there is a gap of 10 percent so there's definitely a problem no it's not you have to first analyze right okay next come top five weight events guys this is the most important section within the entire awr report what are the timed events what exactly is this term called event event is for example if there is one session if you remember yesterday we covered we did an exercise a demonstration where one of the sessions updated one record and it forgot and he went on the guy went on lunch and you know uh you know it failed

20:00 he actually failed to do a commit or a rollback or to close the session or to close the puti session right so he simply forgot that and at the same time when the guy was away another one came and he actually tried to modify the same row right and but he because the first session didn't commit anything the second session you know started waiting on an event called as nq tx rowlock contention so this these are in total in oracle we have i guess you know more than 1500 different weight events and that represents specific problems or specific things for example here you see the nq tm row log nq tm contention tm contention is basically nothing it represents the table level log for example you have a

20:46 table if for example i am a db and i have and have every access in the database i can one i can alter i can modify anything in the database so i was actually doing some work and i you know and doing that activity i forgot to to release the lock from the table and then the user session come try to modify something there in the table because the table is already because the entire table is in exclusive lock mode so the second session the user session won't be able to do anything and that session will started getting nqtm contentions and so this time it the the session the the the waiter session the user session doesn't get the row lock contention this time they will get nqtm contention tm contention stands for table level locking or table level contention so if you see that means during those 30

21:33 minutes we have these many total weights i mean uh 1700 000 10 000 lakh 1 lakh hundred 67 twenty three times i mean these many times session came and actually tried to modify anything but they failed to acquire a lock on the table due to some reason and ultimately they have to wait so that's why the weight counter will be increased so in total 487 seconds 487 064 this is this time is quite huge now someone can ask me like how this time will be so high but when this awr report is only 30 minutes how come the you know the second time is okay let me first convert

22:24 okay 135 or how it's possible right so someone can ask me like this awr report is of only 30 minutes how the total time of these weight events is of 135 hours it's not possible right it's possible because if we are talking about database we are not talking about our wall clock time right that has 60 seconds no we are not talking about we are talking about the db clock db clock works differently so for example in 1 db cpu seconds you'll see four for example if you have 16 cpu so within one human wall clock time the db clock time is 16 seconds because within a one second 16 parallel

23:11 executions can happen using those 16 different cpus right so that's how the db cpu or db time or db wall clock is actually works right so that means during those 30 minutes in total of 138 hours were actually weighted by different sessions all together combined but never ever jump on the conclusion only be on the basis of waits and time you always have to pay attention for average waiting milliseconds but so that this number represents out of these many times when we when the session was was on weight the average time that any session spends on this con this event was two nine zero seven milliseconds

23:57 right and the uh the second last column is the percentage of total call time means out of hundred percent db time so this uh this uh this event was responsible for 53.7 percent of the total total call time well in earlier releases of awr you'll see call time in newer releases of awr report you will see as a db time right so in total 53.7 percent of the db time was actually spent only only by the session simply waiting on nqt and tm contention table level law so this is a bigger problem right and what is this application class means well as i told you in total we have more than 1500 of weight events and oracle has actually segregated or pulled them on the basis of weight class for

24:43 example nqtm contention and enqueue rollout contention always comes under application category or application class that means these are more relevant or more specific to application coding right because there is something which is actually holding the database into table level lock mode right so you have to understand that so that's why oracle has clustered all of these weight events for example if we talk about application category i think there are more than 15 or 16 different weight events that comes under this special weight class of application right you see another one called as user i o where the db file sequential rate so if we talk about the user i o weight class we have db file sequential read we have db file scattered read we have you know direct path read direct path right direct path read temp so many others

25:30 right so now come the third and the the second one the second important time to wait event that is enqueue tx rho log contention so so that simply means you have you have something in your database uh which is holding a row level log this is not a table level lock but a row level lock and this is happening that frequent right so that means anytime every time it happens on an average it waits for around 2908 milliseconds well someone can ask me this wait time is in milliseconds so it's way too less right so why to worry but guys we are talking about enqueues we are talking about locks these are memory level locks right so these are very important right these are not like uh your table you know your simply locks

26:15 anything but this is something happening in the memory right so uh this is way too high sometimes and especially because these these weights are always comes in no willing to wait mode they will simply started consuming other resources of your system so it's very dangerous so these two i mean in my entire career i think these are the two most notorious weight events i've seen like because though these two problems are always specific to application team so you know you'll see uh db is simply you know dropping you an email replying okay we are getting nqtm contention or nqtx or contention application team please take a look so that's where you have to check what exactly is causing this robot contention right or table level lock

27:02 so same way this section only provides you top five right it but in real you'll see uh within those 30 periods we have more than 100 of different weight events observed but these are the top five and those are actually ranked on the basis of total call time or the total percentage that they contribute in from the contribution to the db time any questions guys i know this is little little uh you know a little new to you but this is how you know one should proceed and dissect the awr reports on the basis of weight interfaces or weight events any questions hello

27:50 no you can continue okay okay all right perfect ah okay let's move ahead now time model statistics again i will not explain this in because this is more deviation uh okay now the weight class as i told you earlier the oracle actually clustered or they segregated or they combined different weight events into an application into the different weight classes or categories so application class was stopping the charts in total application specific weight events were three zero three six six five right and out of them 99.37 were simply time now timed out and this this metric represents for example i am a session and i try to get my hands on this enqueue uh off specific lock but someone

28:38 else has already acquired or working on that request required a lock and not able to release on time so ultimately i simply said i'll i was actually kicked out or timed out so that will be counted as one so in total out of 100 requests 99.37 was simply time hours i mean sessions were not able to get their hands on time uh in order to modify anything on the tables or rows total wait time is these many seconds eight eight zero 140 average weight is two two eight nine three but this is combined for all application weight classes including nqtm content nqtx rho log contention right next comes is the user io what is a user i o category user io is when for for example i am the session and i am doing something some work right i

29:24 need to do ios right why either read i o or write i o so that actually contributes uh comes into the user i o so in total user i o is though the weights are higher but there are no time and the total wait time is also less and the average weight is only one ms so i can simply ignore it right i simply say okay this is not the problem user ios or user specific queries or sql statements are not a problem the problem is something else we have some application weight class problems so you simply say these two events are very high i mean their average wait time is very high though their count is not that much but if you check the time the total time of those weight events plus the average

30:11 weight and especially this their contribution towards the db time is very high so you simply ask your dba guy boss do not look anywhere else these two are the problem check what is causing nqtm contention and qts to law contention right and i'm sure guys by the end of this session you you simply you know you know a lot of things about the awr report so you you cannot blind you simply don't blindly trust uh what your dba says you you you will be in a position where you simply instruct your dba to take look on the specific sections within the awr so yeah so can you ask one question here yes can you go up i think uh in last year this one

30:57 yes yes so like when will uh to alarm to the devia like what if the percentage would be there like a total call time means like right now is 53.7 okay when we'll tell to dpo like we need to analyze right now like i understand i understand your question any healthy dba i'm sorry i'm so sorry any healthy oracle database if we talk about top five weight events or top 15 top 10 weight events you would always see cpu time topping the charts right so you should always see right now in this system the cpu time was actually moved to a third place third spot but in idle scenario in idle situation when the database is good you'll see cpu time here on rank one position one and

31:43 consuming most of the db time because that is something really expected like every sequel that runs in your database always consumes cpu and i o right so that is something expected so if you see cpu time is there on the on position one on rank one and consuming around 50 60 of the total call time not more than 50 60 i would say because i've seen the system where the cpu consumption of cpu time is 99 or 90 so that is also a problem right so anything that ranges between 50 55 of us total db time or is consumed by cpu time then it's all good system is healthy system is okay and on rank 2 if you see events like db file sequential read so you know what db file sequential it is what it happens or session weight started

32:30 waiting on db file sequential read only in a situation when there are indexes for example you are querying something but and there are respective indexes on the top of it so if session is waiting to read something from the indexes in order to fulfill the request that you've thrown to your database so that means that is okay right so another one is db file scattered read what is dbfile scatter the db file scattered read happens when you see multiple sqls are doing uh you know large full table scans for example you have got this table which has one crore rows and this seek this sql game without the apps with uh because in the absence of indexes uh this sql started scanning every single row out of one crore so

33:16 that was that the session till the time that request is over our reading part is over will started waiting on db file scattered read so that is dangerous right so you can say that the db cpu is top with 53 then comes the db sequential read with for example 40 for example 20 but that is fine because sessions are doing you know uh index scans so that is okay there are certain scenarios i would say where even db file sequential rate can be proved harmful or dangerous right but mostly you'll see nqtm contention nqtx rule log contention right and log file sync right and db file scattered read are some problematic weight events but again guys

34:02 as i told you there is not any hard and fast rule only any rule of thumb okay if we see this specific weight event only then we'll alarm our dbs will update him anything that is with higher call time and with higher wait time this is the most important factor do not go by total waste do not go by anything else always trust time and average weight in ms right for example if you have a cpu coming and topping the charts but the average wait time is only two so that's fine right because and the total time is also less so time seconds average wait time and what's the total contribution to the call time or

34:48 the db time are should be uh you know decisive factors okay okay all right all right so these are you know though we have only checked top five events but as i told you this is a database it it at those 30 minutes you will see some other weight events too but those are only earlier section was only explained in top five so here you will see top five top you know in qtm contention rollout condition bb file sequential cursor pns latch free here is that db file scattered read what is db file scattered readers i told you if session is doing a full table scan on objects then you will see some db file scattered read okay now next is what was the average time i mean the total wait time

35:34 total wait time is only one three three seven seconds i mean in a busy system if uh you know we you see uh you know the total wait time uh in seconds this much i mean that is okay right there is always a case when it's not really possible to uh for every single sql that runs in your database to always do the index scan right there are certain scenarios certain situation when it has to go before the full table scan so that's when the session will start to wait on tv file scattered read nothing to worry that's fine because average wait time ms is only one we are all good we're in good shape right so what else uh you know don't ever worry about the sql net message to client you'll see 62.60 and you know the weights are very high in fact one of the highest there if you talk about weights

36:20 sequel that message to client so the session always started waiting on the sequel net message to client when uh you know for this is this is what uh this is the event what is where the session started waiting on when uh something was actually sent from server to client so for example you are connected to your database through a sql developer using sql network protocol internally so your session when server has something to push back or to send back to you to your sql developer terminal that session will start waiting on sql net message to client so this has nothing to worry so any event that starts with sql net message to client or for client is of no i mean you need not to pay attention on that but yeah there

37:07 are certain uh weight events specific to sql addresses like sequel net message from db link to db link that will comes into the picture only when you are referencing or you only when you are calling any table which is outside your database you have another database on the same mission maybe on a different host and you want to query something from that database right so you are actually not querying from locally domestically you are actually querying from a foreign database which is hosted on a different machine so that's when you'll get some sql net message from db link or two db link weight events so as i told you guys there are more than 1500 in fact 700 800 weight events so it's really not possible to explain about each one of them but yeah the very popular one are nqtm contention nqt extra log contention db file sequential read

37:53 db file scatter read right and log file sync log file and what else latch cache buffer chains right and uh yeah i mean these are some these are some popular ones right although there are a lot of obscure uh weight events which are you know which oracle has added into 12c and there are newer releases but let's not discuss about them okay the what are the now we have discussed about the foreground weight events top five foreground top foreground events but what are the background weight events and what are this log file parallel right so when you're doing your work i mean when your session is doing any work right so there are some internal operations happening beneath your request which are

38:38 which are processed or you know executed by the uh by the by the database itself right so though your session is doing some work but database internally has to do it has to help your session to complete your request right so db for example this one is log file parallel right so this happens when you have lot of redo information in your memory area and you know this log writer process has to write that information physically on the disk on the redo log buffer so then that log writer processor started waiting on log file parallel right right so this is something which has nothing to do with your with your session where it actually querying or do a select or an update but internally when you for example your session is doing an

39:24 update and your session is generating a lot of redo because you'll you are making a lot of changes right so that means at that point of time you have colle you have generated lot of redo information that information cannot reside for a very long time in your memory considering the size of your database and considering how busy your database is so ultimately that has to be moved in the form of archives by the our redo by the log writer process so that's when you know it started the log writer process it's a background process which helps to flush that information from the real log buffer to the disk that's when it started waiting on log file parallel right so these are background weight events but you guys being a dba let's not discuss much about it operating system statistics this is very important metric like what was the load at that

40:10 point of time during those 30 minutes so if you see the load was very low only one one was the total load right and now guys if we talk about what is the load so uh load is what load is a combination of i o weights right and the cpu weights and the yeah this is the combination of these two right so load is plus cpu waste io weights ah okay now what is the idle time what is the bz time these two are another important two metrics busy time is the total amount of time right when the system was busy right and idle time is when the system was doing nothing so if you see the system was busy most of the time one four seven three nine seven nine out of total resources available total time available

40:55 to the system and idle time was only one three eight four though the numbers are little you know almost uh not much difference between the two but yeah still i i would say the system was little busy as compared to the idle time right and what was the i o time i overweight time this much and what was the system this time is when the system is doing internal operation recursive operations right okay what was the user time so user time was very higher so out of the total time this much time was spent on user on user queries when the user was doing some operations right and another one is number of cpus and number of cpu sockets that's how you will be able to know how many cpus you have in your database server okay another one i'm leaving the above two sections but now i'm jumping into the sql statistics

41:40 guys this is the most important part especially for the developers right if they want to know what was happening in the database in terms of sql execution so sql statistics section they the article actually this aw report was divided or uh you know sorted on the basis of elapsed time elapsed time means sequel ordered by elapsed time means this this this part will be uh sorted on the basis of elapsed time what was the total run time uh that was incurred or spent by that specific sql so now if you see this specific section here someone can simply say okay was uh the total uh elapsed time is this much okay this this sequel is problematic no

42:26 this is not but if you see this is the total elapsed time seconds always go for for execution seconds this one means whenever this sql executed on the database how much time it actually took to complete the how it how much time the single execution time it took to complete because if you see 1439.44 seconds how much minutes how many minutes 23 minutes okay okay so so that means total executions were 512 and every time the sql got executed it takes around 23 minutes to complete so this is an expensive sql

43:12 i won't consider this sql which only which only takes 7.63 seconds to complete or this one which only which not even takes seconds only nanoseconds or milliseconds to complete so i won't pay my attention for these two sqls or even for this one this which spends only 22 seconds but my focus will be for this one why it's taking 23 minutes and why what exactly is this sql just click on this sql id a clickable tab it will show you it's actually doing some update on info contra text and doing this is this right okay so total executions were five hundred and twelve so that means this this out i mean if you talk about the cpu time out of the total executions only 58 seconds were spent on the cpu i mean that means this sk this sql

43:59 don't eat much of the cpu it has no love for the cpu but it has some other requirements maybe i o right so this is not a cpu intensive statements but in total the elapsed time for a single execution for the sql is very high in fact if you see the this corner its contribution for the total db time available it was close to 81.33 that's way too high right another one if you talk about this sql in total the executions were five zero four zero zero three i mean this sequel uh comes with a x with a massive workload i mean within a period of thirty minutes this aws are 30 minutes right so within a period of almost 30 minutes this sequel got executed five zero four zero zero three times but the single execution was only 0.00 right so this is basically not in seconds in fact this is an ms right so this is of i mean it's all okay right

44:45 this is no problem there is no problem with it and another one if you check this one if someone asked me what are the top two most expensive sqls on the basis of elapsed time total run time i will say first one is this one another one is this only single execution happened during those 30 minutes and that actually took one two six eight seconds to complete what exactly is this let's see it says this is okay this basically doing some you know some functions applying some functions on this a one request column and then doing some operation then group by so the lot of sorting is expected from this sql so maybe that's why it's taking a lot of time so if you have you got your awr report always go and jump to the elapsed time section sequel ordered by elapsed time and always look for the sequels

45:30 which has higher elapsed per execution seconds so first this one is most expensive sql another one is this one right this is of no use right this is you know okay statements i won't pay my attention for this one and if i ask me what will be the third most expensive sql i will say this this one only 15 executions those this is not cpu intensive but only 15 executions happen but whenever it happens it takes around 979 seconds to complete right and what is this let's click on it it's an update on this one okay fine now you see everything is okay in the elapsed time in terms of elapsed time every single sql that is there in this section are okay right you you didn't notice anything now but the another one another section the

46:17 second section is sql ordered by cpu time how much cpu was consumed by the c by the sequels now again you have to take you have to pay your attention for this tab again cpu per execution seconds how much cpu was spent during a single execution of this specific sql statement if you check this column this one is top right so out of you know uh you know though it's happening only once only single execution happens successfully during those 30 minutes it you know and uh you know it it takes around this much these many seconds and interestingly if you see this was the total elapsed time so it took around one two six eight seconds and out of one two six eight seconds 651.32 was simply spent on

47:05 waiting for cpu or consuming or burning cpus right okay another expensive sequel in terms of cpu is was this one single execution and this actually took around 578 seconds out of the total cpu right so single execution and the cpu per execution seconds was higher any any question guys hello you there guys yes yes okay all right now if you check what was the sequel module what exactly was the program what would actu which executes this query so this report was from bses i did a you know

47:50 an analysis a performance analysis one of the customer way back in 2017 where there was a billing server running on 10g so that at that point of time this was the bill sir was one of the user oracle dch pth if you guys are familiar with the with the bscs so these are some you know some common modules there so dch was there pth for billing right and bill serve so this was the module information okay now next section is sql ordered by gets if you remember i discussed about the buffer reads let me take you to where i was coming from logical reads i mentioned i you know use this term like it's always good to have you know reads directly from the memory instead of reads happening from the disc or physical i o so logical i o logical read

48:38 is always good so sequel gets so this section is all about it so sql ordered by gets means uh it's basically sorted on the basis of sqls which are consuming uh uh you know which are uh using lot of buffer area doing lot of reads from the memory or from the buffer right if we check here gets per execution will be the deciding factor if you want to declare if you want to say okay this one is expensive in terms of gets always read gets per execution how many guess or how many memory reads were memory reads you know happened for a single execution for this sql now if you

49:25 compare this one is the most expensive right only single execution right and these many blocks were actually uh these many memory reads were uh happened during a single execution of this sql and what was this scale the sql was doing a count on this one right so this is again is expensive in terms of memory reads right as i told you why memory reads are expensive they can prove expensive when you are doing some huge amount of memory reads and you have other sessions too who are doing almost same operation right then you they you'll see some shortage of memory level locks right because level locks are needs to be acquired by the session in order to traverse in

50:10 order to travel across you know uh the memory areas right so it's always good to have memory leads but not excessive right so if someone says okay uh okay i have another awr report for that that's fine okay sql ordered wide read reads this section tells you about or updates you about sql which are already on the basis of physical reads like disk io where the disk io is happening i mean sequels are coming they're not seeing they're not able to find that respective block what they're looking for in the memory so ultimately they have to do a i o they have to go to the disk and read that block right so that's when it is count as a pi or physical i o so what are the top sqls which are doing maximum amount

50:56 of physical ios or reading lot of things from the disk okay again this one reads per execution you have to focus seven nine four three these many uh reads or discretes were hap were actually uh executed or happened during a single execution for this sql right this one which starts with this right so in total um i will see what are the common sqls right in these top four sections elapsed time cpu time gets or read so if someone asked me like what what will be you know the most expensive sql in terms of you know memory rates or buffer rates executions and cpu or you

51:42 know elapsed time then i have to pick the one which is common between these four sections and we will have our most expensive statement right there are a lot of other ways as well how you can get this information if not from awr because if i talk about myself i you know i usually not prefer to get this information i you always you know dirty my hands log into the system and do the command line uh you know because i am i still consider myself a you know command line dba so i rarely use gui i prefer to connect with the database and then you know get this information but being developer coming from an application background this will be of huge uh benefit huge uh okay let's not talk about the version count and pass calls and execution well execution i can say i can discuss but

52:29 you know how many executions you know happened in total for the sql and how many rows were processed right so here in this case you can simply you know uh you can rely on the very first column executions like for this sql this x this sql got executed these this many times like five zero four zero zero three so if you see like they're like okay for example you have received a complaint from the from the customer okay system is slow what i should do right and now even dba says okay i see a huge spike in the total workload now i see earlier we have system only 60 busy but now i can see 80 so now this section can be a very helpful sequel order by execution here you can see if there is a spike in total workload or total

53:15 execution for example in you in in general laser usually we have only thousand executions for this sql but now during this 30 minutes we see some five zero four zero zero three number of executions so you see some seventy percent increment in the executions right so there is a definitely a problem there is something which has called this sequel in batch mode maybe from anywhere maybe either end user or in dba or anyone who has privilege to do that right so you need to understand why have you be seen we see a sharp inclination sharp ascend or a sap in the total executions for this sql accordingly you have to uh plan your strategy to move ahead right so this section sql ordered by execution can be very helpful in those situations or scenarios right next move pascal let's leave it version count top yo this

54:03 section is you know where you'll get sql information for all of them like whatever that executed in your system i won't okay let's do it like this way i have few reports okay now as i told you till now we only discussed about awr reports there are few things as well in awr which i would like to cover but again considering the shortage of time i won't be able to explain much about them so let's quickly jump into this thing called ash what is this ash what's the purpose of ash so ash is basically uh you know it's a 10g feature it was

54:48 introduced in 10g along with awr report right it was pretty underutilized i see a lot of dbs a lot of people don't even aware about the term called ash they never ever use it but if you see like the problem with the awr report is you cannot generate an awr report less than 10 minutes so for example if you have a transient variation or a short performance spike that happened only for fraction of seconds or maybe one minute you won't be able to generate the awr report for one minute right because ultimately it will provide your report for 10 minutes and you know who knows the system is very busy and you it simply averaged out that problem you won't be able to see that sharp spike anymore in your aw report because the system is too busy right so that's where ash comes into the picture so ash has

55:33 that right that that ability to generate and report even for few minutes or even for a second so if you want to generate a report for only those problematic two minutes you will be able to do that that's the biggest advantage of generating the ash report so you never get your your weight events averaged out your problems average average hours or maxing out due to the larger time span of the problem so problem happened only for one minute but awr cannot allow you to generate aw report less than 10 minutes minimum period for awr is 10 minutes so ultimately you won't be able to see those problems so ash comes into the picture into the frame so that will uh definitely help you now the other advantage i see like for example you see some weight events right you see some nqtm contention nqtm rollout contention

56:19 but awr doesn't provide you that information what was the sql that was frequently waiting on that specific weight event you you still do not have that information from the awr that's the problem with the awr it provides you information for the overall sql it has noticed are expensive in terms of cpu in terms of elapsed time in terms of logical rate in terms of rates in terms of execution but it doesn't provide you the sql which was responsible for that specific role of contention why what exactly was there in the system which was causing that humongous growth in uh in nqtm roadlock contention or nqtm table level locks right so you still do not have information so ash is the right place always ask your dbas to provide ash as well but in the newer releases of 12c you the moment you generate the awr

57:06 report it always appended at the end of the awr report so now you do not have to ask separately ash reports it comes combined collective reports are there starting from 12 c release one i guess that's at 17 or 16 something okay so coming back to that this is our awr report earlier aw report we actually scan some you know some common sections what all where where to go what to check right but this one is of most important if you see this is not a normal normal aw report this is a rack report right rack report is for example you have an oracle database which is you which is running on a two node rack or a three node rack or a five node rack so that report you have to generate individually on all of your nodes because every node has its own set of sequels running and its own workload

57:53 that is catering to so this rack report is a collective report it's a combined report which provides you the overall interface overall health or face of a database right okay now we have two instances this one or a three and or four let's quickly jump into uh here we go okay now if you see this is a star star means what this is the average of both instance one and instance two like if you check this section this is dedicated for instance one node number one of your rack right what was happening in the node one right and if you talk about this one this one is no what's what all happening in node number two right and if you talk

58:40 about this one star this is an average uh or uh average for both one and two right so if we check node number one during that specific period we i can see that nq tx rollout contention happening quite frequently right and in total though the overall db time was quite less only 8.26 percentage of db time was consumed but if we check the average weights this is not an ms boss this is seconds 18.99 second for for a single weight is way way too high right every other weight event is in milliseconds or even in fact less than that uni seconds but this one is in second this is excessively high 18.99 second almost 90 seconds very high right so the total

59:25 contribution towards the dbv time is though is very less and we have db cpu at the top but as i told you average weight hair average weight will help you right 18.99 seconds is very high so now you simply uh understand okay there is a problem in the database and this nqtx overlap contention is waiting for sessions are waiting on this wait a minute for a very long time on average it started waiting on waiting for 19 to 20 seconds what about node number two instance two here as well nqt extra law contention is coming with in fact with a higher value almost 20 seconds so any session that goes and hits instance 2 there it started waiting for this event for almost 20 seconds so we have a problem in both node 1 and node 2 but

1:00:13 node 2 is the one the severe factor right where we have performance issue or for this application uh this application class nqt external convention very high now what is what will be the next thing you will be able you want to check now you want to check what is the object right what is the object that is that is under this uh or what exactly was the sql now go to the bottom of this report sorry the bottom of the report is this uh this one active session history report ash report now if you check like what was the sql which was waiting on enqueue tx so log contention so nqtx log

1:00:58 contention was here this one was the sql which was waiting on nqtx log contention and what is that click on it this was an update as i told you update delete or insert these kind of manipulations can incur or can bring in qtxo log contention right so this someone fired this update and other sessions which are trying to update the same row but first session didn't committed its work or roll back it or control c or shut down its laptop he actually failed to do that so that's why the second session has to wait on this specific just to other specific wages to maintain the concurrency of the database right so we now we are sure we are our database is slow because we have nqt extra log contention is happening though the db

1:01:44 time is less but the average wait time like whenever session waits on this it simply started waiting on for 20 seconds on an average if it hits on note 2 and if it hits on node 1 almost 18 to 18.5 seconds so it's way too sql where it's actually waiting for so if this is this is one this is the update statement it's modifying some some table called testing master right what else what else and present is there any any section in the report in awr or ash to put any recommendation if the if we will need to increase any memory or yeah of course yes as i told you starting from 12c in fact not even 12c 11g release to patch at four we have got

1:02:30 the awr report along with ash report so ash and addm report has now appended into the uh awr report so now after aws section gets over we have aw ash report section and then after that we have this addm automatic diagnostic uh uh uh what's the full form of idiom automatic diagnostic management something okay adm report is there what is admd provides some automatic recommendations to you right if you go below and check what was the recommendations like it's the first section says top cpu statements cpu usage and undersize uh sga and take a look roloff waits it also considered like we have some massive roll-off

1:03:15 contentions happening right but if we talk about the overall active session of the total history so we've seen the top sql statements were actually consuming most of the time so here you will see let's see what was the it says this is one of the most expensive statement and in fact this is responsible for 16.35 of the total activity and this sql if we talk about this one they spend 99 time of its time on cpu io and cluster weights so this one right so now you can ask your dba guy man please go ahead and check this sql which is coming with this sql id and with this sql text what is the problem why it's taking so much cpu io and cluster weight events so then he has let's wait and wait for his recommendation or his advisor you

1:04:02 know instructions and second most expensive as per adm which is contributing 13.44 for the total activity is this one where it seek the sql spending 100 of its time on cpu ion cluster weight events right uh and in fact if you check uh it provides you some more information like it says full table scan of table partition bv td testing master client def is happening right so now you we are clear like this table which is owned by bbos user and table name is this and its party this is a partition table and its partition name is client underscore dev is where it's spending most of its time because it's taking it's doing a full table scan right so it's consumed 23 percent of the database

1:04:48 time spent on this sql statement so ask your dba guy okay check this this this this sql and i guess this is doing a full table scan on this table and especially for this uh partition right another one is for sql tuning okay let's leave [Music] sql tuning sql tuning yeah this one here if you see it says this update statement this and this sql spend only though it spend only one percent on the cpu ion cluster the total database time spent processing the sql statement with this so that means this sql is the culprit this sql

1:05:35 is responsible for all those nqtso law contentions though we have identified this thing through ash right but you can get this information through addm reports as well right so idm also says this sql was responsible for massive amount of enquire contention variants what next cpu usage it says we have 62.33 cpu used right on instance two why there is a usage mismatch so it says consider adding more cpu okay guys please do not you know blindly rely on addm recommendations you see sometimes it says increase the degree of parallelism by 1000 times and you only have 16 or 32

1:06:21 cpu that's not possible right because this this basically this this works on some oracle predefined heuristics right it it or algorithms right so it simply gets this information from them you can trust about the sequel tuning part and rest of the recommendations but never ever blindly rely on increasing the cpu because you cannot go over the night and you know say ask your customer okay increase the cpu that will help us bro it's not possible you have to procure you have to prove like okay this is the cause why we are asking for the cpu usage you cannot simply say okay radium report is saying increase the cpu or increase the parallelism let's do it no it's not not like that and same is the case with the undersized csga right because if you check about the cpu it's only talking about those 30 minutes what about the rest of the 23 or 30 minutes of the total day right everything was going smooth at that point of time so it's no

1:07:08 you know it's not possible to increase the cpu it's not even a good solution to do that under size sga i mean sgsi is the memory area of the overall sga or system global area is insufficient it says there it's busy by this much right

…