Webinar-06 - Performance Tuning || Profiling in Oracle || Oracle Database Performance Tuning

okay hello everyone uh good evening uh good morning good good afternoon whatever like wherever you are joining so today's uh agenda is going to be on performance tuning this is our webinar six and uh you know uh if you see this agenda we'll start with uh you know uh how to identify the queries and which our query is running how to fetch whether query is progressing or not and then how to get the hash value of the query and then how to get what is the text what is the sql text it's running and then how to get the explain plan of that query and then you know how to run aw report and then how to review that and then after you doing all this you know what are the remedies what are the solutions for those sql queries which are running long and then you know some of them are gather stats and some of them are profiling and then at the end you know we'll go with some of the uh you know common issues after your post db upgrades right so uh one of the common question is when it comes to the performance training how to start where to start what to look right so you know without knowing these three points you know you cannot uh you you may go in the wrong direction right so i unfortunately had not uh you know prepared any of these slides but i do have one of my document which i always used to prefer so i'll just go through that right so let's start with uh you know query uh somebody uh from the application team they comes and they'll tell one of my queries you know i'm not able to hear your voice so you know location just disconnect and try connecting back because you know everyone are able to hear my voice you might be some issue with your mic or you know your phone right so uh you know application team will come back to you and they will say that my query is running very long uh you know it is it is it's some kind of abnormalities i'm seeing it today so can you look into it so that's a common question uh you know any db any application guy i will come and you know will complain about that right so i i hope my voice is audible to everyone so if yes just say comment here right okay okay good okay the moment of application team or whatever the end user comes and complain you uh my query is running uh you know very weird and it's supposed to run by 15 minutes and now it's running from almost 30 minutes and almost it's running from last one hour whatever query it is the moment they uh you know come and complain uh first thing you know you you need to check it out at your database what all queries are running right movement right right right at that time right so you know this particular sql you can fetch it out again uh you know this is the if you are familiar with the command prompt you can use it or else if you are using uh you know uh double sql developer those are like much more advanced features and users are friendly you can use those uh basically developer artwork and you know fetch it out uh this is a simple output sample output i gave here the run it and then it list out what all the sessions are running and then you know based upon that you can decide which particular session uh we know they are complaining about and what is that uh query is about whether it's the select or whether it's update or whether it is a truncate what kind of query it is you just try to analyze that query and then once you get that particular uh you know sid and then the more informal hash value and sql id and you know much more information about the long running uh you know jobs you can see how from how much time it is running and everything will get it with this particular uh sequel that is uh you know uh from these these two are the views uh vidalia station on session and then we do the sql uh area these are the two uh you know with the help of uh combining those two you can get uh no particular syntax what it is doing and from how much time it's running and you know what is the hash value what is the sql id and what is the ssid everything will get it so note it down once you note it down and then you know run this particular uh you know view that is uh session stats and uh you know stat name so using these two uh we dollar uh dynamic views you can get whether the query is progressing or not by running you know you can you can check the percentage row you know that that value will give you whether it's a progressing or not get the row fetched how much row it is fetching for every minute or every two minutes so that indicates whether the query is progressing or the query is struck or you know it is waiting for some resource or like you know you will get it so as long as this is progressing and you are good like you know query is progressing um as usual all right so that that's the other thing you have to verify the moment you run it it will ask you for the uh s id that s id you have got it from the first step right so you just pass that particular sid and then you know you can monitor this how much of the the rows it is fetching so as long as it is increasing and then you know fine your query is running fine so once you identify that and then uh get the sql hash value so you can get the sql hash value using a vidalization and you can pass that particular sid again you got that sid from uh you know the step number one and and then you'll get the hash value the moment you get the hash value and then you know you can get the sql text again you know it will give you detail output of that sql text and then you know once you get this hash value and once you get this sql text and then you can fetch the execution plan explain plan for that uh you know using this uh sql plan and then you can pass that hash value and then you can pass that particular yes id so you know it will give you that execution plan right so once you get your execution plan and then start analyzing the execution plan so i will explain you how to read the execution plan in a minute uh you know and then check it out uh what is your cost of you know whether you know cost is more or cost is less you can compare it to your previous history and then you know you can say whether the query is really doing good or whether the query is doing bad because sometimes what happens uh you know yesterday the query ran for uh you know suppose 10 minutes today query is running from those 20 minutes there might be huge data on the table level uh we know that application team has to confirm or there may be change in the data at the table level that application team has to confirm on that right so you our job is to check for the uh the cost how much how much cost it is uh utilizing and then you know analyze the um your execution plan what are the index it has used and uh whether it's going for a full table scale full table scan or whether it is going for index scan and then you know based upon past history you can you can analyze it and then you can conclude whether whether query is really doing good or whether it's query is really doing bad so by reading this execution plan so once you decide you know once you read your execution plan and then you can generate your awr report why you want to generate awr report because by looking at your execution plan you cannot conclude whether your queries are really doing good or bad because you know at the same time there might be other session or other top sqls right the moment you run awr report you will get to know how is the load on your database how is the load on your server right so run your deploy report and you know read that aw report for top 8 event and top sqls and you know top ios uh and then how much gets how much reads and you know how much execution iteration is going on everything you can read it from the aw report i already covered one detailed uh session on aww report in my youtube channel if somebody missed it so it's good to go for that video because i covered most of the things on that so definitely i will take one more session on aw report in upcoming days so you can review your aw report and compare with your execution plan so then uh by doing those two you can decide whether uh you know what are the best solution to fix that particular the the after reading after reading your execution plan you decided oh the the query is you know not really doing good you know my cost is very much too much and uh you know it is not using my index it is not going for a full table scan you know you decide it after reading all those and after reading your network sorry uh aw report uh you know you can take appropriate solution uh there are many solutions you can think of by reading by doing aw report and by the just at looking at your execution plan so i'll explain you how to read this uh particular uh explain plan and then aw report uh because i have so many so much content here i'll just uh brief first at this iteration and then we'll go indeed details like solutions comes you know you can sometimes you may need to gather stats and you know sometimes you may need to gather dictionary stats and sometimes you may need to do a table move or table string and sometimes you may need to rebuild index and then you know finally go for the profiling these are the ultimate five of you know uh solutions you can whichever best fits for your current scenario you can you can uh choose it appropriately and directly don't jump into the profiling because profiling is not recommended by oracle uh if you tag any particular execution plan by using profiling then you know in future if there is a good execution plan also you know it will not be used or whatever the tag that will be used so we'll go each one of them in detail so so any doubts here sagar is asking one question here plan hash value and sql plan hash value are two different things plan hash value and sql plan hash value oh what is uh sql plan hash value plan hash value so i did not get that there's no two things as far as i know only plan hash value uh sql plan hash means what like i we know sqlid sql plan hash means i never come across sorry i i might have missed i might have not known to that as far as i know sql uh sqlid and then the plan hash value right so okay uh now a question comes here how to read this particular uh execution plan so that i will directly go into this particular uh you know execution plan this is one of the execution plan for one of the customers so how to read this particular execution plan you have to first go to the innermost loop so it will start with outermost loop here and you have to go all the way down you have to start with the innermost uh loop that is innermost loop is this one this one you can see here right index range scan so it is going with the index ring scan here and then you can go one step above and then going by table access by index row id and then you can go one step above table access by storage full and it will go for nested loops and then you'll use the hash join and then hash group by and then you know you have to start reading your table from the innermost so if you see here it is start with the index range scan here so this is my index and it is making use of index and then it is going up all the way top and then it doing insert statement so and then you have to look at your cost uh what is the cost now if you see here right this is 77 297 this is cost is going to be huge like you know 100 cpu so you know there's something wrong with this particular uh you know execution plan here okay if i go if i show one more uh if you if you observe here right plan hash value this is ending with 2 5 4 and then if you if i come back here the plan lan hash value ending with 141 and it is also doing the same thing insert statement and for the same table and if you see here uh you know this particular right this this particular uh index range scan that is using u1 this particular index it's using u1 and if you if i go back here this particular innermost it is using the n1 this gl code combination n1 and here you can see gl code combination u1 this is a unique index here and this is uh you know that the name you don't worry about the naming convention so it is it is based upon the the name what you've given right so if you observe here it is using n1 here and it is using u1 here and if you see cost value here it is 215k and if you if i go back here the cost value here is uh if i convert into k it is 77k and then uh again like i'll extend you by looking at cost value again you cannot decide whether it is doing good or not so we'll go in detail that's what i wanted to give you brief introduction here you know how you can read you have to start with the innermost and you can see it is using index scan here and this is the index it is using and then it will go one step above and in this particular table it is using uh again one more uh this on this gel code combination table and on this table it is using this n1 index and then gl uh reveal it is using uh this particular table with the table access storage full and then gl reveal uh currencies it is using table access full and then if i go here uh if it is using here innermost it is using index range scan and then that index is gl j line u1 and here there's no if you see here it is uh index range scan it is using after the you know this gel code combination the same table and same insert table and you know it is taking a two plan wish value and you know it is using two different indexes uh because this particular table has a two indexes and u one and n one based upon the business it is defined decided but whatever inside you are doing it is using a two particular hash value so how uh you know the question comes here how i got these two different uh you know hash value and these two different execution plan right so that that will come into you know we'll discuss it now so any questions so far so whatever i covered this is the basic you have to start with whenever you uh whenever any concerns you start fetching water i'll repeat once again you run this particular query and uh check what all running on your particular database what are running on your database at that moment by running this particular query the moment you find out which particular sql is running long and in which particular sql the the business team or application team they are pointing out you can take that particular sid and then based upon that sid you can you can you can observe whether it is a progressing or whether it is a struck or whether it is slow progressing right by looking at this number of rows how much how many number of rows it is fetching by make use by reading that you can see whether the query is doing good or not and then uh with with that particular sid you can get the plan sql hash value and then the moment you get the sql hash value using that you can get the sql text excel once you get the sql text and then you can get the uh you know you know explain plan for that particular run and then by reading your execution plan that is a cost one is the cost and one is what what table it is what index it is using whether it's going for a index scan or a full table scan what kind of scan it is going you just review those two and then once uh review those two once you make it note of that and then run the aw report and then make it out what all running on your database whether it whether that query that whatever whatever sql hash value or the uh whatever the sql plan whatever running whether that is coming under top for you top five weight to ends on top for you sql ids or top io iou end rate if if any of those top five event if this particular sql whatever your business team is complaining if that is coming on the top for you then you have to look into uh you know tuning uh tuning that particular query or you know you have to start taking the appropriate action suppose if that is not coming under top five well top five sql or top five weight wins so then that query is not uh you know bad at all uh there may be some other top sql so what are the top ten sqls you are seeing in aw report those are consuming more resource on your database so that is the reason you know you are this particular sql is you know running very slow you have to you know ask the business team what are these top sqls and why these are running and why they are running for longer time and then you have to ask them to look into those and then this this particular sql is running fine because you are seeing that number of rows are fetching a number of rows are increasing so then if you are seeing this particular sql coming under top 5 sqls then you have to start looking into or tuning that so what are the solution remedies you are seeing the gather stats on the tables whatever the tables are involved on those start gathering the stats and start rebuilding the index and start uh do the maintenance like you know table move or table sync and then uh other option like this is particularly comes you know automatically oracle has to do it but sometimes you know i seen it gathering the dictionary stats and gathering the fixed object stats me fix these kind of issues so you know on every weekend or you know you know monthly you have to do the gather dictionary chats on the gather fixed object chat so that will uh you know improve your performance of any queries not particularly this one this will improve the performance of your database so these are the few recommendations i i used to follow so that that's about it and then i'll now come to the sql profiling so you have done sql rebuilding index and table also you're doing and gathers dictionary stats and you know stats on the table everything is perfectly fine and everything's working fine and then still you are seeing that performance issue then the ultimate like final option is to go for profiling so how the profiling works right so before i go into profiling so i'll take up some questions here uh in explain plan how how to sorry how to know the inner most uh first one to check innermost first one to check means as i said right uh the moment you know okay i'll show you that always the execution plan you know that how you can read you have to start from the innermost because that is the inner uh uh what i can say uh the sub query the sub query will run first and you have to start with the inner most reading this one by looking at this one it is a table access on that table access it is going with the uh you know index range scan and then by looking at the index range scan we can check it out whether this particular index is you know valid or because in that table you might be having many indexes for example you can there's a logic behind creating index right this n1 and you can see this is the e1 so there are two index build on this particular table but the logic behind that index is different so you have to think you have to start from here and you have to by directly looking at this execution plan and if you see here okay it is looking okay it is if you if you start it from here you can see right okay it's a gl reveal currencies and then if you start looking into this particular table you know you will it will be it will it is going to mislead you right so you have to always start with the innermost that is how you need to read your execution plan and then uh just compare with the previous execution plan whether the innermost it is using injection uh indexing scan and that's the index here and here index in scan it is using u1 so and then you can that is a starting point you have to look okay why it is a change here and then you can fix that and then proceed further and then one more query here can some sql text having the different sql id no actually sql text sql id is going to be common whether you can here select star from m and select star from m with the capital or with the lower letter or you can make it e smaller and mp and larger whatever combination of sql you write it the sql id is going to be same but you know this particular plan hash value is going to change for example if you write a select star from employee with a lower case your plan hash value will get changed but execution plan sorry the sql id is remain same and the same query if you write it in capital letter select start from m or selection for employee with the capital letter and again hash value will get changed but sql ids remain same so i'll explain it in a few minutes will the sql id change when internal or bind variables are changed uh again as i said right one of the main concern like it is it is especially for the developers who are writing the query uh it's always recommended to use the blind value right so because when you write a query with you know which helps are you know using the blind variables so what happens the sql id is going to remain same it is not going to change it because you know you are so you are giving the example like select try employee where employee id and then you can use some blind uh blind variable there and then the sql id is going to same for that because you are just you know giving it a variable all the employee ids you are giving it as a variable how many times it runs it uses the same sql id right okay having said that we'll start into the uh profiling because that is very much interesting for everyone okay one more query is how to generate uh x plain plan for previous query okay so i'm coming to that uh uh you know i'm going to answer for that one so the sql profiling uh two more most important sqls you know given by oracle i'll give you this particular mos document this is awr sql history uh this this is a particular sql uh this is again provided by oracle i'll give you a mass id for this particular sql and then um and then what's the one more thing i noted down here okay this one co e x f r underscore sql for file data sql these are the two sqls uh given by oracle i'll uh after this session i'm going to share these two sqls and uh mass notes what happens you have to run this particular sql given by oracle what it the moment you run this one it will ask for the sql id you have already noted down your sql id from the previous this one right sql test sql hash value and then by doing all this you already got your sid you already got your sql ids you know all all you have already handy you have to run this particular sql id and then you have to it will ask you to pass that sql id i'll show you that uh awr sql history and then the moment you run it it will ask you to enter the sql id the moment you give the sql id it will give you all the past execution plan see for example this is my the world execution plan this is one execution plan it listed here and it is using n1 and and at down again it lists out this one more execution plan you can see it is using u1 and this is a plan hash value and you can see this is a planner sheet there is a difference in the plan hash value and there's a cost also you can see it's a 215k and this cost is 77k there's a huge difference here with these two execution plan so then you decided which one which one is best for you and then again you can see here execution it went for 53 iteration and this one it went for 14 iteration and then you can read this on that ilo pio and cpu you can see cpu 6075 and you can see cpu is 1.8 because it's a 14 time execution and then this is the 53 time execution and this is the elapsed time 15 uh in terms of minutes at 15 000 584 and you can see it's a 2.32 it again depends uh how much data you're fetching and all so again one of the most interesting thing is the execution how much time it is going to execute if it is a multiple time it is executing you have to check with the application team why you are using like what is the logic behind that why this many times this many iteration it is going to execute right so these are questions you have to ask by by looking at this then you will decide okay uh this particular 14141 is looking good for me and then i know i want to take that one so for that you can see right this is the one more sql given by oracle again so you have to run this particular uh you know sql i will again as i said i will share these two sqls and there's a particular mask document for this whatever process i'm making you have to run it the moment you run it it'll ask for the uh you know sql id you can see sql id is same whatever i'm showing it here sql ids remain same only the plan hash value is getting changed so i'm giving the same sql id here the moment i give the sql id it'll ask for the what is the hash value plan has shown you so you decided the the plan hash value ending with this 141 and cost is 215k and then this is good for me and because this is using u1 and this is using like 14 uh time of execution whereas compared with this one it is uh you know 53 time execution so that is the bad and it is consuming more cpu and more relax time and it's a bad at all and then now i want to peck this particular sql so you have to give that particular hash value you can see the ending with one for one and then moment you give this particular hash value and it will generate the profile by its own by name everything it will give by its own you can see all this it is doing all this and finally it will give you this particular output message execute coe xfr sql profile and then this is the profile name it will give this is a sql id underscore this is a hash value data sql the moment it will give this particular uh you know profile you just uh run that particular sql in your sql session you just run it and then it will it'll you know it'll give this particular output message the pl sql processor completed successfully and then you know this this is output it's completed and then if you go back to your uh uh your db your sql profiles and then verify that particular profile is pegged and then you know what any cart if if this particular insert statement runs again it is uh it is going to fetch this particular execution plan because you you have you know expect this particular execution plan to the any upcoming query with the same insert statement on these particular tables it will go and fetch this particular uh execution plan and it will use it whatever is irrespective of whatever data whatever you know change in data whatever happens it will use only this particular execution plan it will never change but one of the oracle recommendation is always uh you know never ever use this particular sql profiling because what happens like now you are in a 12c like for example 12 one and then you did some patching and you did some upgradation and all still you know you it will be using same execution plan but when you do the patching and when you do the bug fixes there might be you know good execution plan might have created and you know that that will run much lesser than lesser of less the time what this sql is using right so that is the reason you know what required will uh no not recommend to use this particular uh profiling but your as long as you know if you raise a sr and then you know decided to go with sql profiling as a time being temporary solution you know you can go it so i have seen it uh you know i've seen i use it for many customers so you know this is a temporary as a workaround you can you can use it always so that is for the uh profiling any question so far so and then i will have some more topics here okay uh is the same what you do dbms sql doing except land profile yes so if you use the dbms sql tune that's the same thing whatever uh sagar your server you are telling that the same thing uh whatever this particular sql you know that's again these are given by oracle there's a particular mod document how to use if you are familiar with dbms sql tune you can go for that and if you are not familiar of the dbms queries you can always use these two uh sql this these are the simple two sqls you just need to run it and one of the thing is this aw or sql h3 is very very important the moment you run it whatever the world execution plan it list out and then you can just compare it what is the current execution plan and what is the world execution plan what is it different and then you know just make a comparison out of that and then take whatever you feel it's a good execution plan and create a profile and then peg it so that's it about this profiling okay one more question uh where that two sql scripts are available uh no it it not comes with your oracle binaries you have to download it uh from the oracle mos note i'll give that particular mass note and also i'll give those two sqls okay so i'll quickly uh cover one more topic uh uh on my on okay if you don't know this is my blog spot molecular trip your blogspot.com a lot of topics i uploaded there you can go through that so this is uh you know it's a year back i believe it is in february uh uh arguably end of jan for one of the customer you know when i when i did a upgradation from when they did upgradation from 11 to 4 to 12 2 they had some performance issues and none of the queries are running fine so then uh you know uh i just uh you know went into the analyzing how how they did the upgradation and then you know one few of the recommend how i approached for uh that particular uh issue this i i gave on a detailed step there uh what i did uh you know they did upgradation what they did they just downloaded the uh 12.2 binaries and they directly upgraded the database 11204 to 12c uh they are not up and they are not uploaded any of the patch on this oracle binaries so remember when you install your any of the new oreo column either 11g or 12 or 19c it will be always in a base version so based versions will be having a lot of bugs so the moment you upload the movement you install any work alone the best recommendation is to apply whatever the latest uh the psu or the cpu patch available so i just verified there were no patch applied on that particular 12c home so i went ahead and then applied the latest patch available uh for that particular oracle loom that's the one thing and then second thing what i observed after the database upgrade uh you know they are not done that that you know dictionary stats are the fixed object chart as i said this is one of the best recommendations so so many customers when i do when i do this dictionary stats and gather fixed subjects at the moment i do these two most of the issues are you know getting fixed that is what i observed so far so this is one of the best recommendation i will recommend for anybody know frequently or weekly or you know monthly better to do this fixed object stat and the dictionary starts so i have seen some of the army and queries also getting hanged they're not taking a lot of times when you're running like carmen backs up and recoveries and if you want to or track how much our main admin backup and recoveries are processed so those queries are getting hanged so you know to fix two hours to fix for that you know you have to do this gather stats and fix objective the moment you do it all the argument queries and all you know will go and it will fetch data very fast this is the second uh fix i did it for that customer and then uh third thing what i did the third section three you can see right table move so there are like around 15 to 20 uh the major table uh which are like you know uh having data in terms of uh uh i can say terabyte like one terabyte two terabyte of tables uh that's a data warehouse database and it is having around uh uh two terabyte uh or each table will be of like around 800 gb and like uh 800 gb or one terabyte size of cables and you know what i did like i did a table move uh what a weekend table more and table string on on those major table around 20 table whatever they're given so i did like uh these are like tables you can see like move parallel no logging and you know multi-table and you know no parallel logging i i did a move parallel logging and no parallel logging and then you know i did it for all the tables or whatever like current 20 tables and then after that uh what i did like fourth action item i did a rebuild index on those particular tables uh this is the syntax you can use alter index index name uh coil s and alter index index name and rebuild parallel no logging and then ultra index index name no parallel logging so this is a syntax you can use it and after i rebuild index on that finally what i did uh one of the the stats right collect stats uh with hundred percent i did the collect stat with hundred percent on those uh measure tables uh after doing all this all of suddenly whatever the queries they are facing around uh around eight to nine queries all the reporting queries were like you know having a very bad impact then the moment i did this one you know ultimately like all the queries are started uh you know fetching data very fast and you know it was a huge improvement in the performance so this is basic recommendations like you know there are so many uh apart from that there are so many uh tuning advisor and all available but these are the basic ground like homework you have to do it before you are touching into any performance uh you know the tuning sql advisor and all before going to that this is a basic ground work anyone has to do it when they do the database upgrade or whatever kind of migrations are the patchings so you know that this is for one of the customer i did it so that is for the today's topic uh whatever i wanted to cover so any questions so far i can see a few of them uh to the same tbs uh tbs you mean tape backup uh okay what is this one more question what is okay well i missed so many questions here let's see i will come back come from the top uh how to resolve high cpu memory utilization okay yeah again uh power like performance tuning there are so many areas uh you know you can tune at your database side cpu memory so many thing but for the today's topic why what i wanted to cover uh how you can profile your sqls and what are your how you can monitor your current running and how to get this this is a basic agenda i wanted to take it today uh definitely uh uh i will come come up with few more topics on the on this uh you know tuning this os parameter like memory and io stats uh you know vm stats and uh and netstat there are so many parameters you can here to tune your database server side also so definitely i'll take one more session but i already covered one session on the database performance tuning where i explained about uh how you can read your uh all this memory parameter io parameter netstat uh vm stat saw report in my youtube channel i already covered detailed explanation on that you just go through that uh it explain how to read it how to how to read all these parameters but how to tune uh definitely i will come up with one more session definitely in next coming webinar we'll take this one how to tune this database parameters like memory io network parameter like you know network parameter when it comes to the rack the tuning interconnects it the communication between the rack interconnects that is ges and gcs so tuning will comes into picture when it comes to that network part in the rack so definitely i will take one more session probably next uh coming up webinar or next webinar we'll take on that uh what is the reason for what uh okay was asking where are those two skipped okay that i already answered sometimes i'm not able to see sql id at the database level sql id you are not able to see a database uh no no query will run without sql id whatever query you run it definitely there should be sql id so you have to check what sql you are running or you can ping me what sql you are running to check the sql id i'll check it out how to decide index scan is better than the full table scan okay very interesting question swati um see when when you are okay when you are uh fetching the data like you know select start from employee where employee id selector from department where department id whatever you are running whatever query you are running and if your query is returning five percent of the data of your entire table or five percent of the records of entire table better to go with the index scan right so if you are getting the five percent of your uh output data of your entire table and if it is if you are getting the 20 more than 25 percent of your entire table better to go with the full table scan for example i have 100 records 100 records of table where i want to do like selection from employee where employed equal to one it is returning only one record in that case i have to go with the index ring scan and if i go with the full table scan there is no point in that right so the suppose i will extend one more query select start from employee where employee id is greater than greater than 20 greater than 20 means i will get 21 to 100 i am getting 80 records so that time you know better to go with the full table scan so that is again you know that decided by the optimizer whether to go to index scan or the full table scan based upon the how much data it is returning out of that sql okay so how uh to the same tbs where it will move same tps what is the tbs i did not get that the question is i am not able to understand it is so you can rephrase it again sagar can voracle plan base is better than profiling a plan base your plan base means you are talking about the baseline baseline okay the baseline is one more interesting topic so baseline is also i'll cover one more topic on the on the baseline so again i said right like profiling never ever suggest i'll never ever suggest profiling for any of the customer until and unless i will run out of all the ideas so profiling always i'll keep it as a last option so i will go with the all this alternative option based either baseline or stats or index or no i will try it out all the options if nothing is worked out i will go with the profiling so the performance issue fixed when you did all the steps yeah definitely what i said like in the moment i did those those are the groundwork uh the moment you do any patching or any uh you know migration or upgradation or whatever you do of the patching and you know table move table shrink and index and gather stats gather dictionary charts and gather fixed objective these are like ground rule you have to do it the moment you do it after the doing only you have to release your database for the the production news or the application use without doing that if you release your database that is no use at all as if you are not done anything because you know after you do the migration or upgradations are you know these are like ground homework you have to do it so those will fix a lot of issues and then after that also if you if they're coming back with some of the performance issues then you dig into uh tuning all these advisor like you know using sql plans and what it is using what earlier it was using and then there's so many so many tools like relay and capture and relay so many options you can use it but you have to do this work first before getting into that performance of tuning okay table space no yeah table move in the sense i am not moving my table to different table space table move in the sense what happens if you have a data inside your table tablespace you have done lot of insert update delete right your data will be scattered across entire uh you know table space it is not uniquely it is not uniformly uh arranged in your uh you know your blocks uh it is not uniformly arranged on your data blocks it's not informally arranged on your segments or you know extends when you do the table move all your blocks data blocks will be arranged sequentially and informally and then it will reduce the space unnecessary space which are like for example if i have like data block one is used data block 2 is empty and data block 3 is used so unnecessarily there is one data block in between of data block 1 and 2. when you do that table move that unnecessary used block will be removed and data block 2 will be kept next to that data block one that is arranging your blocks uniformly so that you know when you do the select or insert update the query will be much faster that is the reason you know we have to do the regular table move activity so i recommend to do tableau table new activity table sync and index uh uh this scan and all uh on every weekly weekly weekend as a weekend activity as a weekend maintenance activity you're better to do this table more activity when you're doing a lot of insert update delete on the table it's always recommended to do the table move activity on on on weekly basis as a maintenance activity okay johnny is asking one more question in react scenario how uh how the fixed and dictionary stats uh it's the same whatever it is whether it's whether it's a stand-alone or the rack it is applicable so you have to run that fixed object chats and dictionary object chats on at least on monthly basis that will solve your questions so a lot of issues okay so one more question um parallel logging and no logging can you please explain okay so when you do the table move activity and all right there a lot of logs will be generated when when you do this uh no logging it will not generate any log if you see here right what i'm doing table move parallel with no logging it will not generate any logs so unnecessarily why you want to generate the logs which are not title used like i'm talking about this arca logs and all right so unnecessarily uh you know i don't want to generate the logs when i'm doing the table move if it's a terabyte of table when you do the with logging it will generate a lot of forklogs right so when you do this no logging it will do the table move without uh generating any locks and then after that you can enable the logging no parallel logging right so that that's the point here and why does optimize just changes the plan okay yeah good question why does optimus changes the plan so again a very interesting question now you brought it here anan see um i'll tell you with respect to this only so what i told here like this is one particular execution plan and this is one more particular exam plan which is got changed why it got changed there might be new data in the table for example this particular table is about ebs table and in the ebs you might be aware that there's a journal table this gl stands for general tables and there's a code combination because they will take a code from different different sources because ebs is a you know kind of unique source it will be attached to any of your uh many of the application right like either peoplesoft or the or the any many functional areas right abs is the ultimately it will hold the all the journal entry general data the source data will be coming from the different different uh sources the data will be coming from the different different sources when this particular execution plan has ran so data came from the uh peoplesoft so peoplesoft data will be different and then my ebs their abs table will be having that data belongs to peoplesoft and that time you know this is the execution plan and the next week when in the same gl table and data came from the you can assume that uh something some other application i can say you know finance application or some some third party application again that data also comes into gl table that's a general ledger right so that is a different source so there's a change in the data incoming data right so there's a change in the codes and you know those things and then obviously your optimizer will change its behavior and then it will generate the new execution plan thinking this might be the best but ultimately that may be wrong so it will go for a toss then you know you will see that performance impact again uh other scenarios uh the index whatever index you are using that index might be uh you know uh invisible or that index might be uh something wrong with the index or uh some some table uh there are a lot of dml happened at the table like some lot of truncation lot of load happened on the table a lot of data change on the table so there might be a change in the plan so there are many scenarios you may see that change in the plan right so that's uh that's about the change in plan and then uh there's a huge question here what is the performance you fixed when you did all this listed a few out of them already answered okay uh why the plan hash value changes plan ash value yeah again i said no so lan hash value of plan actually it left to uh it's an internal mechanism uh with the moment you run like as i already expanded select star from employee if you do the lower case oracle internally generate a unique value for that considering that particular sequence and then that is a that hash value is stacked to that particular whatever syntax we are used and then i can use uh select star from employee with capital letter and then with particular to that syntax and that way of execution again the hash value will be get generated for that that is the internal mechanism even i i'm not sure why it change internally but let me i will i'll do some research and i'll get back to you again it but but the point here is your sql id will never change only the plan hash value will get changed but plan hash value is get change means internally that your execution plan is going to get changed uh okay so how sql ids are same for the queries with the different customers different customers how sql no it is not going to same for any customer right it is same across your database if you go to the other database with the same same sqls your sql id will never be same select start from employee if you run it here your sql id is different same selector from imply you run it on the other database your sql id will be different uh okay so how to decide we need to rebuild index in a database index in any database now again i said right like if you are fetching only five percent of the data and uh and you know if it is still using index uh the valid index but you are still it is taking a lot of time so and then that's the point you have to thinking of your rebuilding index and you have to check whether index is uh first thing is whether the index is uh visible or not sometimes index will be unusable and sometimes index will be uh uh okay what i can say i am not getting that particular word um invalid so if it is indexed in your lead you have to rebuild your index so that's about the other point uh could you please put some light on high water mark and lower temperature okay again this is the internal concept unknown like uh sorry um and i'm not able to pronounce okay fine so uh this one right high watermark and low watermark again like one of the reason doing the table move to bring down like high water mark and low water mark um again that that is a different concept again i will uh i will uh you know cover it in next topic again just to give some idea on that for example you have a block a like you have one block and then if you are inserted one record inside that block and then that block is uh suppose it is filled eighty percent and then eighty percent is your uh uh you know you can decide high water mark and low water how much data i can grow uh you know for example if it's 100 block and then you can decide 90 percent is a high water mark and then you know i cannot grow more than that and then you insert a record and you go up to 90 of that inside that block and then later for some point if you delete some of the data inside that particular row and then it will come down to uh you know come down to 50 and then your high water mark will not come down until unless if you don't do the table move activities and all those uh that's why you know you have to do the table moment so definitely i will take one more session on this high watermark and lower mark so that is going to be uh you know internal topics so most of them are like most of us are like not aware we will not worry about that high water mark and low water mark but when it really comes to um the tuning internal parts so definitely we sometimes we need to increase that high water mark and then when it comes to the transaction logs and row locks that time you know this high watermark and all comes into picture so definitely i'll take one session on that okay table string is recommended yes stable sync is recommended so i also seen recently for one of the customers so i did lot of index rebuild table move and so many activities on on few of the tables but still you know i have not seen any improvement in the performance the moment i did this table shrink so i have seen a very very drastic improvement in the queries so definitely i will recommend table sync okay so he is asking some of the basic questions unlocking and just okay locking latching you know when i cover this locking latching that is where it is this transaction lock and roll lock and uh deadlocks and this high water mark and low water mark so everything i'll because these all are correlated topics once i take this topic definitely i will i will cover these topics i will plan it for upcoming days but sorry for right now i don't have any sessions okay so so many questions i think we are almost on that time so please share the profiling script so definitely after the session i hope you all are in our what's up our telegram group so definitely i'll share everything in our telegram group so any any last question last last few minutes how to start performance tuning like basics for fun tuning like basics again for phone tuning is a question because uh you know when you will get confused uh what is performance tuning at the end of the session right because you you have to start at your whether you have to think of whether right to start data for consuming tuning at my database side or i have to start my performance tuning at osi all right to start my training at sql tuning right so again uh it's a it's a it's really like uh you know huge area so definitely you have to start by you know area wise right you have to start with the database performance tuning you start with like os analysis network analysis and when it comes to the rack how internal node communication works you just start with that and once you are familiar with all those areas like os and network and those things i like gather stats and these are basic ground okay whatever i explained so once you are very much comfortable and you know in and out of that you just jump into this the sql tunings and you know tuning advisor and profiling and then once that is done and then look into some kind of you know application side uh troubleshooting and also let's start with the database uh performance tunings uh like you know gather stats dictionary stats fix objects out how it works and then you know uh and then again you should ask right like when to stat when how to gather stats on the table when to start when to start my gather stats on a table when to start gather stats on a schema those are basic questions you have to thoroughly understand right like when there's a 20 change in your data 20 dml operation on your table you know then you have to start your uh gather stats you have to do those kind of unless you start with that area and then jump into the sql tuning and profiling and all uh some good results so definitely i will check it out i have some resource i will after this session i will share a lot of material related to performance tuning uh in our channel in our telegram group you can refer it from there uh sql contents okay last question that is what additional strategic does sql profile contain sql profile sql profile uh okay you you do this one like i will share these two script and you try it for your any of the development environment uh you you try to uh run this particular sql and found out what are the execution plan for that particular query and try to generate your sql fan like by running this one you try to generate your sql profile it will generate see this is sql profile is generated and then uh read this particular sql profile what till it contains and then uh run that one it will peg your sql profile in your database so you will read this one you will get to know what and all it contains one first thing is it will fix your plan hash value and it will it will stick to your execution plan and you know it will consider it internally it will consider some of the current statistics of your particular table so you know again that is once you peg your sql that is fixed whatever change you do in your table it will never ever that changes will get into your profile right so that's it for the today session so i can take last question from ashwini how to correlate weight events with the different sections in the awr wait even there are so many weight events right uh you can you can check uh you know what are the top of weight events uh you know definitely like you know when you're dealing with which kind of environment right whether it's a rack database or whether it's a standalone database again when in your react database whether it's a data warehouse database or you know if there's a oltp database again your weight events will be you know it varies and you know like again wait even depends there's so many way to inside you can one of the common inter questions please explain what are the weight events what are the top five way to inside like if we can talk about like uh you know especially for rack there's the gc of you know buffer weight and you know log buffer weight and uh and so many weight and i can recollect right now if you ask also so you have to start with digging and first consider which particular area you want to look into especially on the weight events whether you are dealing with your data warehouse or whether you are dealing with a wealthy period if you are dealing with a data warehouse most of the time you will be uh you know data warehouse is mean for your select queries and when you are selecting you know one of the common issues jc buffer bit rate uh these are the common weight events you will see it and when it comes to the oltp and you know if the name itself subject lot of transactions you will be doing and you can see a lot of weight event especially for your uh you know db buffer cache and then log buffer cache and you know sequential read and you know scatter rates there's so many uh and the weight events you can see that those are like you know you start digging into that once you decide which environment you're working and which whether it's a rack or standalone so you just start like i already told you like i covered uh basics of aw report in my youtube channel you just go through that you know that has so many topics which talks about the basics of aw report and then from there you can you can start digging into what what exactly you need right so that's about the today's session i believe so we are almost on the time so any uh you know any last questions if no then we can we can wind up okay so fine then so thank you everyone for joining this session definitely this uh session will be available in my youtube channel you can refer it out if anybody missed you can you can you can take it from there thank you thanks everyone bye

Channel: Mallik034

Views: 55,917

Keywords: Performace Tuning, SQL ID, Plan Hash Value, PT, SBP, 19c, Database, Exadata, RAC, ASM, Oracle

Id: L6ypcJlTZeM

Length: 57min 57sec (3477 seconds)

Published: Sun Feb 28 2021