Troubleshooting Oracle database using ASH

0:04 all right guys so this is the another slide that i've prepared for you again this is exclusively and only and only for analysis how to use awr and ash methodologies and techniques to troubleshoot okay these are few questions that i've intentionally added to it like i'm using the standard version can i generate the awr and ash reports no it's not possible because you have to purchase the oracle tuning and diagnostic pack and that requires separate license if you want i mean instead of awr you can generate stats pack because that's now completely free but if you want to make use of awr and ash reports they are licensed feature you have to purchase tuning and diagnostic pack

0:51 so another question is like i am a developer and i do not have direct access to the server can i generate it by my own yes using dba tab you will be able to generate the awr report and reports by your own even if you are a developer but again that actually requires you know a couple of privileges added to your your application account for example if you are not connected with the dba account then of course it's not possible and unless you get some select any dictionary and a few other permissions for awr advisories until unless those privileges added to your application or custom schema you won't be able to generate awr adm even if you have uh you know a dba tab added right and what are the use cases where these

1:37 reports can be helpful so when you have a problem when your system is slow when uh when you're seeing some really strange problems and so it's basically uh give a clear picture of the database what's what all is happening in the db at the time when you have generated the awr report right so uh next question is like any performance implication of these reports generating every 10 minutes or so no there is not any performance implication because there is this m1 light process that actually samples or collects data for awr report per snap interval and per minute for the ash right any difference that is between awr and ash that is generated on 10g and 12c and above well

2:22 the though the interface or the the the graphics or look and feel this looks almost the same but of course there is a uh though these these two reports awr and nash reports are exactly the same provides you some database metrics but uh the ash provides you more in-depth uh anal in-depth statistics of the weight events that is there on the sql id and session specific information which somehow missing in the case of awr reports another thing is like for example you have faced the problem that happened on the database only for few fraction of seconds for example only for 15 seconds so and your awr report you cannot go below 10 minutes right so the minimum what you can generate an awr report is 10 minutes right so but what about the problem that

3:09 happened on this busy system only for few seconds only for 15-20 seconds so if you go and generate awr report for 10 minutes then there will be big problem right so you your status your stats will get averaged out or you won't get that clear image of the state of the database at that during those 15 seconds right so it's good to generate the ash reports because ash reports give you the flexibility to generate the ash report even for a two second period or a one second period if you want to check what happened in the db during that problematic one second or two second this is what you can achieve using ash report but that's somehow not possible in the case of aw report that's some that's that's a huge difference in between the two okay any question

3:58 okay now let's move to the next slide so someone has uh you know uh complained and dropped an email or maybe called you and they said like uh some of the queries are uh intermittently or you know um every now and then performing very slow right and then you check the oracle enterprise manager graph the performance page what you see is the concurrency weight class is is very very high enable the pointer first like for example you have got around 98 cpu right and in fact this this concurrency specific weight is has actually breached the maximum of cpus as well so the earlier instance it

4:45 almost touched the maximum cpus but the other one is goes this this peak this mountain is has actually hit close to 150 um right so almost 150 average active sessions were found in the database waiting on the concurrency class weight event right and and now customer also reported you like the performance get back to normal by itself like they they did nothing at the application and then in fact no dba help they took but it actually comes and stays there for a few seconds or few minutes and then automatically disappears right so that is really strange and bizarre problem so they want to

5:30 understand why it's that so first of course you should go and generate the awr report right because aw report here if you go and check the the sys because this because the the best part with the with the awr report is like it also provides you information about the os statistics right like how many cpus are there what is the version of your uh operating system how many cores are there how many sockets the memory available so this also gives you a clear picture of your entire system where you are actually doing the troubleshooting right okay but as i told you earlier awr of course is not at all a good choice for a shorter spikes like if your problem is there for 15 20 seconds or only for a few seconds a few

6:16 fraction of seconds then of course guys it's not possible uh to get a clear picture of the system for those uh particular seconds of problem using awr report that is the problem right okay if you check this particular snip or snap that i've used so this is basically from here 2011 right from 1630 and 1700 so we've got a 30 minute snap interval right and uh if you do a divide of 5941.33 with 30. 13 you basically get this figure called as almost uh 198. so around 200 active sessions

7:02 which are basically waiting on something right what is that something you are still not sure because you just opened the awr report you have no idea why so many sessions are there actively doing work and always waiting on concurrency event right but now using these two numbers you get some idea okay system has really going through a lot of stress a lot of work it has to do and process and the figure is re really really very high you know is more than double if you compare it with the cpu size right so uh next thing as i told you and we discussed few minutes back like you should go and check the timed foreground events so

7:47 can someone please tell me now because we just covered that part of top five and top ten foreground weight events so what is the biggest problem here where we should start from anyone so the first one uh the first one probably the which is causing 25 percent of the db time 71 per head all right absolutely correct friends absolutely right this event which is a latch cache buffer chains orange sometimes we performance uh consultants also called it as a cbc cbc so latch on cbc this particular weight event uh

8:33 this uh is actually with average weight time of 28 ms right 28 milliseconds and has contributed 71.55 of the total db time talking about the latch as we discussed earlier latch is basically very very fast uh in nature right so it's not like a lock like for example if you want you can acquire a lock for days and weeks right so this that will definitely cause some other problem but yeah if you have a latch that is waiting for even for this long like 28 ms because in the latching world 28 ms is way way too high because these latches are very

9:18 very fast right because these are used by oracle to prevent you multiple processes executing the same piece of code at the same time and to basically prevent some deadlocks right so uh i mean you cannot subsequently acquire a latch at a level that is equal or to less than the level right so what i mean to say for example i am a session and i have an exclusive latch right so then another session comes and try or try to get it its hand on that particular latch only so the second session will you know go on wait right they will go and wait on any particular latch event but if we talk about this particular event latch cache buffer

10:05 chains basically protects the db buffer cache architecture so uh if you talk about the db buffer cache it basically contains some you know some some lists some link lists basically right so a list based architecture is there with uh with the front and an end right so let me show you a figure i have for you for the cbc is the cbc or the cache buffer chain so for example you've got a parent latch right and you've got a child latch so this parent latch called as cachet buffer chain right and it is it has two separate latches which are which is basically the child latches

10:51 right and it it prevents these three memory structure like for example the latch one this cache buffer chain latch one is responsible to protect chain one of blocks and chain two of blocks right are you getting me no search no no okay where is the confusion uh this is the buffer uh absolutely yes absolutely right so let's assume notepad sorry not notepad let me open paint

11:45 all right okay so you've got an sga right and within the sga you got a db buffer cache right okay great right so within the db buffer cache what is what is what is the purpose of having db buffer cache by the way why we have db buffer cache it will uh put the block in the buffer cache means whatever to read it from the disk right so whatever processing whatever modification that you actually do you always need to do it in the db buffer cache area so it's basically a this is what you know we see the classical architecture right of oracle like you always have to keep a

12:30 block for example if you have picked a block from the disk you have kept it over here and do all of the modifications and everything once your modification is done you do a commit automatically db writer will come and write the dirty block into the disk right and you will see the persistent image of the block correct but internally what happens is this structure this this meshy structure is divided like this like you've got one two three four five basically positions so each of these blocks which you actually keep here in the db buffer cache are basically maintained in the form of a list an lru list least recently used list okay so this list this buffer cache location one two three four five so

13:16 these are basically physical blocks which are kept over here right and then chain two is from one to seven the length of these chains is different right the first chain has only got five elements five blocks the another chain chain two has got seven blocks third chain has got six blocks right so imagine just you know this entire mesh kind of a structure of a db buffer cache internally it's it's actually divided into the these kind of lru list or chains right so for example if you have fetched a block from the disk through an i o and then you kept it in the chain two in the position two of this chain two okay

14:05 hello yeah yeah so far you guys are good right yep okay so you you have placed that particular buffer or block into this chain to in db buffer cache right but again in order to scan and look out for a free space or a free buffer area in this list you have to first and ask this parent and it's cbc latch can i go and can i get a exclusive latch so that i can go and traverse this list if there is any free buffer available or expired state block available or not right for example it says the parent cbc buffer

14:52 cache latch updates the session yes you can i have free buffer i have free latch go for it so first it will go to latch one it will check okay if there is any free buffer where i can go and keep my block for processing right so it will go and check uh chain one it starts the traversal okay it when it reaches this location one it says okay this is busy there is already one active block which is processed by some session then it reaches block two three four five no one is free here it reaches chain two right first is we the second is very third okay yeah this is free in fact the block which is kept it uh kept here is now is an expired state right and uh the db

15:38 buffer uh will come any time and will uh move it to the to the uh disc right so it will you know uh reserve this space this slot for him for himself for further process so that's how i mean that's just an example right so in order to process or in fact uh let's assume uh you have a session that uh you you're basically a second session right the session which executed just few minutes back executed the same sequel right and those required blocks are still present in the memory so before oracle optimizer asks or server process asks the the you know to read from the disk because

16:25 discretes are always expensive right memory reads are always faster correct so before they go and do the disk io it will always prefer and try to look if the blocks are 100 or partially available there in the buffer cache or not right so in order to do that check or that scan or the traversal of these chains in order to look out for that particular block in question they need to go and acquire the latch right and the latch that is for the buffer cache called as cache buffer chains correct okay right so now if you want to search for the required buffer or a block you first need to ask if the latch is free or if i can get an

17:11 exclusive lock so that i can go and traverse and travel around these these these list in order to search the required block or a buffer correct so that is the importance of the latches there in the memory because they actually protect the structure the shared structure in the memory correct any question or any doubt now no okay all right so now when we now when we know like uh every now and then or very frequently you know uh process need to search or scan the uh the uh the db buffer cache

17:56 area right so that means this request should be very very fast so that's what i said the term in the latching world latch weights of even 28 ms is a very very huge amount of time usually that weight on latch happens only in you know in milliseconds in sorry in microseconds and less than that right they're very very fast but if somehow this latch weight is is in ms or in fact if it's in seconds then you are in a big trouble guys right that means there is a very big problem simmering right and you have to immediately take care of it right okay so that is why because the oracle has

18:43 kept this weight event latch cache buffer chains into concurrency class because in order to maintain the shared memory structure concurrency uh this weight uh i mean this this latch needs to acquire correct same way yeah so what means we can suspect the reason behind this is because a particular table is heated so much or the number of transaction is very high understand this is just the very first slide that i've opened right so i have another 10 slides to go just bear with me for another few few minutes maybe 25 30 you got answer for all of your questions buy your own right so this is just the start right you just generated the awr from that problematic system now you just begin

19:29 your analysis because you cannot go and directly jump into the sequel right you first need to understand what is the weight and everything right before you you you comment on anything as i told you earlier guys again the troubleshooting on databases is always comes with a great pain and risk because it always and you know is actually full of getting false positives and false negatives so if you feel like okay this latch free is the problem or maybe you know this user direct path right is the primary problem where you will be wrong right so you're actually passing wrong information to the team and for your own right and you're doing a wrong analysis of the system correct so that is why be hundred and ten percent sure go slow but yeah strong okay all right so as i said in the latching environment

20:16 in latching world and latching universe so waiting even for 28 ms is a very very high amount of time right okay i've actually intentionally copied one more snippet here like if you go and check this snap it says nqtx row lock contention so it's basically happens on the table right a transaction a session is there right which is trying to modify something but it haven't committed or roll back so the session is still active and then acquired a row in an exclusive mode now the second session comes and try to modify or manipulate the same row then the second session will go in a weight state and it will start waiting on nqtx rollout contention right

21:01 and why i purposely use this screenshot here because if you take a look the number of weights for nqtx rule contention is very very low right but the average wait time is very high 472 ms and it's it's it's basically responsible for 35.2 percent of the total call time or db time right and is an application class or category weight event right so this is just an example let's focus here on this particular snap what we have primarily used so after seeing this you can think is this happening due to cpu i mean if your cpu cpu short or if this is happening due to cpu starvation or what so accordingly you have to move ahead then next thing you have to check is the host

21:48 cpu section like if you go and see the load average begin and load average end now let me go back a bit if you remember like this snap is from 16 30 to 17 right so this is a 30 minute long awr report so right at the beginning of 1630 the average load average was 43 and at the end i mean at the end of those 30 minutes 35.83 load was there and out of it around 40 load was there due to user sessions and system which which is hardly three percent by the way what is user percentage and system percentage from the load

22:33 that is caused by the user i o and that is system i o system i o means what is system i o because with what are this means by the oracle background processor not only by the oracle background process remember we are talking about the host cpu we are not talking about the db cpu here right so this is basically the system i o three percent means like out of the total uh busy uh or the total load only three percent was consumed in the system i o system i o means when you know there are some recursive and internal operation that is that happens continuously by the operating system for internal scheduling mechanism reading semaphores right doing some extended locations for

23:18 os level blocks and so many other stuff that happens by uh through the operating system by its own right and that actually has nothing to do with the with the database or any software that you have installed on the operating system right for example oracle but this is something by its own like for example system uh himself uh actually used only three percent of the total i mean contributing only three percent of the total load which was observed during those 30 minutes right and 57.7 percent was idle so we have got a smart amount of a good number of idle cpu or the load available on the system like i mean plenty of idle cpu available here so only for this 30 minutes of period 30

24:03 minutes of awr uh period so more than 50 of the total cpu is idle so i think that means that clearly answers the last question could it be cpu starvation or is this happening due to scheduling latency no this is not happening due to a cpu otherwise you would have seen a different figure here something really less maybe in few zeros or maybe one or two three percent as an id percentage right so nothing is there from the cpu and cpu is absolutely fine so we've got a lot of free and spare cpu cycles to be consumed by the by the processors right and one more thing there is a problem with the load average in linux right this load average in linux also includes

24:49 uh the disk i o right the the time that you actually spends or reading or doing input output from the disk so this this this figure basically on linux operating system clubs that that that period too and in in completer uh or uh or proper unix systems like solaris aix and hp ux this load always contributed to the cpu only only the cpu but nowadays you know most of our installations happens on linux so you know we have to bear with this fact and we have to understand like it's actually contributing uh not only cpu but it also accounted for the disk i o that your process actually

25:36 waits okay yep next is the operating system statistics so again the thing that we have finalized uh just a few few uh just in the last slide so we have got ample of i uh you know idle time available lot of cpu cycles or resources available in that context so yeah a lot of cpu cycle idle cpu cycles available so yeah this is not a cpu problem let's move ahead now another section is the operating system status 6 in detail so for example uh uh we though this awr report was generated for 30 minutes but the awr uh interval on the database was for 15 minutes right so automatically your database will do the divide or will do

26:23 the uh we'll do the division right so you'll see from 1630 to 1645 this was the total system usage details and from 1645 to 17 during those 15 minutes this was this was the status now if you take a look last two columns are percentage of i o weight and percentage of i i o don't you think like they're i mean they are actually in the wrong column i mean percentage of i o uh or one percentage idle should be 50.70 and 64.72 and iowa 8 should be 0.38 and 0.25 this is basically an awr report back right so that is why we are actually

27:09 seeing a wrong information presented in a wrong column so i o weight column should have 0.33 and 0.25 and idle percentage should be 50.70 and 64.72 this is a flawed matrix and that is why i sometimes do not rely on it though they you know both oracle and rhl has actually fixed this problem but yeah if you're running on an older version especially on this oracle database version less than 11g so then you might face this problem within the awr report where you're seeing some wrong information in the operating system statistics column especially for this iot and idle weight okay now next important thing like let's first start with this sequel ordered by elapsed time

27:55 right uh okay before that let me ask you guys a question breathe a bit in between okay guys yes can someone please tell me which is the uh which is the sequel which is uh which actually took very high elapsed time the fourth one 2.16

28:43 okay anyone else are you sure yeah it has less execution in the execution lab stamp per second i would like to listen from each one of you shreya please [Music] yeah total number of execution is also different so per execution time it's taking 2.16 so i think but you're also saying 2.16 is the is the most expensive sequel in terms of the elapsed time money what is your take it is the number of execution will be the number of uh the higher count means

29:28 if it is the elapsed time and we say the execution column from this particular from this particular area of the awr report which one is you are you going to pick is the most expensive first one yeah okay uh list of you guys also go with the first one you go with the first one suck it [Music] you you're also going with the first one right you're saying yes or no suck it hello

30:14 is the most expensive and shubham and shreya are saying no to the one with the 2.16 is the most expensive actually you guys are correct 2.16 the query which is there with the elapsed time per execution of 2.16 second is the most expensive right do not go with the overall elapsed time when you're checking the sequel ordered by elapsed time right because if you go and take a look look at the executions exact executions are way way too high as compared to this one right only 3194 execution there were no tests happening during that particular period of time but with each execution it actually took 2.16 and if you check the elapsed time per execution in seconds column

31:01 none of the query is close to the average execution time of this query right so that is why this query and what is this query this is doing some kind of an update so this is the most expensive query and you have to troubleshoot you have to start your analysis if you are seeing like very expensive queries right on the basis of this elapsed time per execution column not from elapsed time total seconds because this this this section this this output is sorted on the basis of total elapsed time second that's why it's always catches people live but actually the problem is not here the problem you should always go and check from elapsed time per second execution that's in single execution single runtime details okay let's go back

31:46 all right now this was this uh screenshot i've captured for that for that problematic system right so as per this the screenshot which is basically a snip from sql ordered by elapsed time so they are basically sorted on the total on the basis of total elapsed time spent right if you go and check elapsed time per executions of all of these sequels are almost same right 16.36 16.70 and this and that and what what diff makes different the other queries is the total db time so you should start first with the with this particular sql id like

32:33 why it's taking 36.70 of the total db time though the average elapsed per execution in seconds is almost same with the rest of the queries right but this is something that actually makes different with the rest of the other sequels right and if you go and check this sequel is actually not cpu intensive also why out of the total one three zero eight forty two elapsed time for seven almost eight thousand execution the total cpu time spent is only nineteen six forty three i mean that means this sequel is not cpu intensive at all there is something else that is bothering this sequel and is it really this sequel only

33:18 can you really figure out i mean if that event latch cache buffer chain is happening all because of this query it could be due to this or it could be due to all of them oh it could be due to this right am i right yeah yeah correct all right so that is why uh it's important to you know keep on keep keep doing your analysis and next you have to check because what this event says even says latch cache buffer change so you have to draw an imaginary picture in your mind when it says about latch that means it's talking about something in the memory in the sga

34:04 right and talking about the cache buffer chains you need to understand it first what is catching for a chain or a cvc now you got an idea that means something is there in the db buffer cache and what db buffer cache is used for for memory scans right for if we talk about the memory scans then you have to go and jump directly into the sql ordered by gets gets is basically used to represent the buffer gets or the memory scans whenever anything that that that goes through the memory scans right so that means the problem latch cache buffer change because that problem is related with the memory scans or with the db buffer cache internal structure that means something wrong is happening in the memory and if you talk about the

34:50 memory that means if you want to check the sequels which are going for higher number of memory scans that means you have to go and check sql ordered by gets that represents buffer gets right there is another one called a sql ordered by reads that means disk reads so for example if you had got event called as db file scattered read or maybe db file sequential read right in that case basically you should go and try to look for you know sequel ordered by uh by reads yes

35:39 okay all right so now you got the weight event you have checked the os statistics everything looks okay in terms of os you've got the event and you've checked the elapsed time as well so there are multiple queries which are there with almost same elapsed per second but yeah you have you're suspecting if this is responsible for latch cache buffer chain weight event or not but again you are only using awr report you have no idea right if that's the one who is responsible for it but what else you can get from the awr report is check the sequel ordered by guess right so what it says it says the total buffer gets is very high right for this particular sqlid this particular sqlid 7g is the same one which is there and topping the

36:26 charts for sql not by elapsed time right but almost almost same right with little different we have got another one as well right so again even after seeing that i mean you still do not have the clear picture i mean if is this the sequel which is responsible primarily responsible for uh latch cache of a chain weight event or there is something else right let's check because this is about the latch so we have got a latch sleep breakdown within the awr reports so what latch sleep breakdown statistics provides you is basically the miss and the sleep ratio and the spin gets so let's let me first of all explain you

37:12 what exactly is that right so for latch as i told you latch basically uh uh like for example let me take the help of that screenshot make things little easy for you right okay this is the internal architecture right you have a session which is now want to scan a particular block from the memory right and you want to acquire a latch cache buffer chain so ah for example you go and because there is a the number of latches are limited right if your database is very very busy and your database goes through a lot of memory reads or you know db buffer cache scans then that means uh they might hit the time when

37:59 the latches are insufficient or there is a shortage of latches because there is there are already previous sessions who already acquired all of the available latches and now your session comes they are not free right so what they will do it will count it as a miss because you are failed to get your hand on a last the very first attempt right so what you what what is the default behavior of latch uh is like it will go and do a spin basically it wait for few quick fraction of uh cs centi seconds or maybe less than my microseconds basically right and then it again tries to get if the latch is free or not or if they're latch available so that i can go

38:44 and modify or scan this particular list right again it says no it's still busy i do not have latch to provide you it again goes for a spin right then basically there is a limit god called as spin gets right rs which is controlled by spin underscore count parameter right and this parameter if i am remembering correctly so this parameter is default to 2000 so that means almost 2000 time it actually tries on a very short period of time just to look out if you know if this latch is free if i can get my hands on the latch so that i can process the blower or i can traverse the list so in that case what will happen once that limit is crossed then it will go for a

39:30 sleep it will go for some time to sleep and that sleep is when the time when you know operating system help the process to to be in a sleep state so that is why you know it's important to check the latch sleep breakdown so it this statistic clearly says this latch called as cache buffer chains got these many requests which is very very high and most of them are actually missed right and because it also goes to sleep state many of the times and also goes through the spin gets that means of course there is a problem i mean sessions are not able to immediately get the latch that is pretty much clear from the stat right

40:16 are you guys sleepy no it's only one man i mean your voice is breaking for me is breaking for you only or for all of you guys uh no no but it is not for me okay it's good for you okay fine all right so this last sleep breakdown yeah so you can go and check latch sleep latch statistics this awr report has latched activity lastly breakdown latch mis source and mutex let's not discuss about mutex and pins here because that will actually make you more uh confused right so let's go this is a different awr report by the way but i'm just taking you to the last sleep breakdown

41:02 it basically that's how i mean there are different kind of latch so in our particular case like for this particular awr report problematic aw report where we see lot of large cache of a chain event so this yes this is clearly visible it actually goes through a lot of get requests and lot of misses happening and a lot of spin gets a lot of sleeps happening that means uh very less number of time people are i mean sessions are getting uh the required cash buffer chains in order to do the scanner traversal or checking of the scans in the db buffer cache but again till this point we do not know who and why does cause this contention like what is the sequel id and what is the sequel text which actually caused this cache above for

41:47 change so let's move ahead next is the latch miss source right so basically this provides you information about the division of latch cache buffer chains right because there are multiple but if we talk about latches latches again even the oracle database itself is just a piece of a code right it's a basically a a big smart intelligent software which is again uh made up of log thousands and mini million line of codes right so accordingly within that particular code of oracle there is this latch definition mentioned again in form of a code and in the kernel code basically so this if you check the latch miss sources like what is that particular part of that latch which is frequently

42:33 missed by the sessions so it says kcbg tcr again these are some short forms which are used by the oracle software right oracle code so kcbg tcr is nothing it's just a representation of consistent read logical input io function right and then kcb rls is for fast release rls stands for release right k stands for kernel so this is just providing information like this sketch cache buffer chain latch is if this that's a full program or a code in that particular code of a latch g tcr that's a fast path or cr pin is basically going for maximum number of sleeps

43:18 followed by fast release right so now you got little idea like it's basically doing something for consistent reading right and that is why it struck next now comes you know the hero of this this presentation right the hero is ash because so far you have you got no idea about the contributors the sequel the source sequel right now uh again i think i already discussed about ash like how exactly it generates the data like how exactly it samples it and how it actually pushes into v dollar active session history and then move it after 60 minutes into active session uh db active session history so let's not talk about it

44:03 so but yeah the important point is like ash links it helps you to bind the link the relationship together facts about the weight event and the sql id so the relationship like okay this is the sql id and if this is waiting on this particular weight event and that was this username and that was a block and etc so that's that's that is something which is now provided by the ash report so now let's move ahead now if you check this is the section of the ash report it says top sql with top row sources so it says this sql id with this particular sql id and this using this plan hash value and got sampled these many times by the way uh just for information guys though the ash basically samples every second

44:51 of data into v dollar active session history that's basically the cast version of this this view but it's it actually pushes only one out of ten uh snaps or samples from that v dollar active session uh history cache review only because just to save some space otherwise if it actually keeps a track of the historical ash samples the table size will go tremendously and you will see the cissock table space size is very huge so just in order to save some time it always prefer to have to push only once snap per 10 snaps or samples from the v dollar active session history view to db underscore active session history right

45:37 are you guys confused a little confused with the last thing means what is that please help uh this ash means we get to know the sample of executions here ash is what for example you are doing your work you connected with the database and seconds uh is tickling seconds is passing so automatically the m1 process is now actually collecting the statistics every second from the database for all of the sessions those are active and doing some work or waiting right and push that information and save that information into a into cachet in a view called as we dollar active underscore session underscore history let me help you

46:31 okay so this is the dba uh uh v dollar active session history right and there is this another view called as db underscore underscore active underscore session disco history which is exactly the same but the only thing is like it actually helps to keep a historical information inside of it right that's it okay hello yeah okay okay there you go that is the historical version of the same view

47:17 db underscore his underscore active session h3 and the cache version which contain only an hour long data is the v dollar active underscore session underscore history okay now coming back to the ash do not get confused ash is the report the ash report gets its detail directly from these views these views act dba hist active session history and we dollar active session history that's it if you are looking something from the history then it always go and queries db his active session history if you're searching something which is fresh and still available in the cache then probably go and look out something there in the read all the active session history that's it okay yeah all right so now talking about the top

48:02 sequel with the top row source that is again a section in the ash report so it says 7g 2h this particular id which is using this particular plan hash value got sampled five four three times four times and responsible for 40.85 percent of the total activity in the database during those 30 minutes and yes it's waiting on latch cache buffer chains and in fact 20.83 percent of this particular row source where it's actually applying some filter right so it's basically uh it's basically causing the problem so yeah but if you check rest of the four sql all of them are waiting on latch cache buffer chains right so now you get an idea this is not that single sql

48:48 which was causing problem or trouble to the database but in fact it all of these five sequels all of them are actually waiting of course this is the primary source this is the leading contributor or primary contributor for this particular debate event on cache buffer chains but not only this one we have got others as well correct so now i'm not going to discuss about the solution for the cbc because you have to devise your own plan your strategy look out for sequels which are there in sql order ordered by reads and try to look do some sql tuning advisor try to see if replacing an index maybe a better index can prevent something or you can also go and try to

49:33 look if cpu is the problem but fortunately we are not facing the cpu starvation problem but yeah sometime i've seen like if the cpu starvation is the case so you see some cbc specific weight events too right okay now let's move to the next one next slide which says uh uh like if we talk about this latch cache buffer chain event like this is about the 99.98 of the total activity it says p1 value p2 value and p3 value so you need to understand basically what is that so let me help you with that uh where we are right now okay

50:26 these okay even in the awr report sometimes you see p1 p2 and p3 values are you familiar with these columns yeah uh this object i write absolutely right not only not always the object id it depends basically depend on the event like if we talk about the latch cache of a chain the p1 p2 and p3 represent value represent what latch cache buffer change here is the latch cache buffer chains it says the p1 value is the address so that's basically this six eight four seven two four one seven six zero is the logical address the

51:11 memory address of that particular latch or that particular code basically right so this p1 parameter represents for cache buffer chains means this this value this particular number is pointing to the latch address right the next is p2 is the number right the latch number 144 correct and the p3 is basically is why basically the reason sometime but you usually receive zero in case of the latch events so yeah the p1 and p2 if you really want to dig in deep the analysis so what you can do you can take help of 1 p 2 parameters like p 3 is the is the latch sorry p 1 is the latch address like this

51:57 and is p 2 is the number like for example we have got other uh uh other events too just a second uh let's try for db file okay now talking about the db file scattered read for example what does the p1 p2 p3 parameter signifies for db file scattered read it says the first parameter p1 is the file number next is the block number and then it shows you the total number of blocks involved that's it right so shivam i think i've answered your question right so it's not the same for all of them so it depends the on the weight event only so for example latch cache of a chain the p1 is the address p2 is the number and for db scattered

52:45 rate the p1 is the file number and then this p2 is the block number correct so now if you check the top event p1 p2 p3 values here if you see out of the total 99.98 activity right uh this is what it is this is just the representation like what i just explained like the first p1 is the address p2 is the number and 0 is the price that's it move to the next section so who exactly is blocking us right what is causing the problem then right so it says blocking sid was 5914 and serial 31561 instance one and but unfortunately this is responsible for only one percent of the total activity so out of the 100 activity we only be able to understand or judge

53:33 about this particular session only no other records is present in the ash report so now we are really confused right because we are not sure like who blocked us for 99 of the rest of the weights right for rest of the 99 percent cashier buffer chain weight events who all sids are responsible correct so let's move ahead now if you go and check this next section uh here it basically shows more information it says 99.98 is held in shared basically what it says it's ash report incorrectly labeled unknown blocker because here for 99.98 of this particular latch weight

54:19 the the ash report is not sure about the sid right so it usually mention it as a health share but it should ideally mention it as an unknown right so this is a problem by the way i think this is rectified now in the latest version of oracle database otherwise in previously 11g release one or at least two as well so it's you always see held shared but in fact this is representing like 99.98 of the total latch weight on this particular event is unknown he's not sure about the blocking sids correct so only this sid five nine one four three one five six this one five nine one four three one five six is known to us and that is really sad actually right but yeah we will devise a strategy like

55:06 today to discuss about it right okay next is like next is the sample weight sample weight is out of the total weight events how many weight event count is less than 10 ms sort of the total weight on latch cache of a chain 161 samples are there with event weight of less than 10 ms right but yes this is the blocker basically because this uh the wait times that is between 10 ms to 100 ms that's where maximum number of samples are weighted on right but sometime the weights are really really too high because take a look like there are a few events instances of waiting on latch cash shape of a chain which is even beyond one second and as i told you

55:52 in latch universe the weight of second is huge huge huge i mean it's humongous it's it's extremely huge it's extremely high right so you of course as we already saw we are already in a in a very difficult situation so again i'm not leading you to a solution here right because this is up to you how you develop your solution from this point there are a lot of things what you can do but again you always it's important to understand the weight event and and prepare your strategy based around the event that is causing the problem right and then identify the sql ids and everything right uh okay okay i think thank you very much guys so this is the

56:38 end of this particular slide uh anything else you want to