Slow Running Query Tips | #dailyDBA 27

by the way this one will be a very quick show like I have to the point questions and I will be answering to the point alright guys welcome back to the next daily DB episode today actually we were planning to cancel our show but then I thought like why not still do this show because I have those five questions for our new episode right now handy and I'm going to give the answers right away so let me start off with the first question of the day and i 100% believe this will be the shortest daily DV episode today so let's start so the first question I have is I want to know about real dog status for following scenario we have three rule of groups one two and three each group having two members NB when we are executing some DML operations by that time how the log writer will write changes to online read laws whether it will write 2 1 a 1 b 2 a 2 b or will it right 2 1 a and 1 B simultaneously amazing question I think this confusion even I had when I started my career like whether the log writer will write from one member to the another member or is it like members which are multiplexed or is it the groups which are multiplexed I think I had this doubt but let me clear this doubt and it's a very simple way okay so you have riddle of groups write minimum Oracle recommends you have to have two groups and each group should have minimum two members so log writer will write from one group to another group in round robin fashion like first group it will start writing to first row first read a long row and then it will move on to the second redo log group and then it will move on to the third raided Law Group so how many members you have in each group log writers will write simultaneously to all those redo log members let us take an example so you have Group one okay so Group one is two members a and B so log writer will write first group 1 that means the member a and member B had exact copies okay so they are like multiplexed copies for each other so if you lose member B so you still have member a right so log reader will first try to prove one and it will write to all the reader lock members simultaneously and then it will move to group 2 and in group 2 if there are two three members whatever your read aloud members are it will simultaneously right onto those groups right the same page log writer will jump to group 3 group 4 whatever number of groups you have so remember this like the redo log soil log writer log writer will always write to redo log groups one by one and inside a log group whatever redo have members you have all those members acts as multiplex copies I think that will help you so let's quickly move on to the second question of the day how to find most fragmented tables and indexes that are caused of high i/o and how to get rid of them I would not answer this question because we have a great article on our support website so what I would do is I would put the link below this video go ahead look at that article you can go to support our deep a Genesis calm and type in like fragmentation or table fragmentation or Oracle fragmentation you will get the article and right away you can actually use that article to find out what is the actual space used by the table and what is the wasted space it gives you the exact percentage and when you have that percentage that's when you decide whether you want to perform a defragmentation inside Oracle in order to save more space I think the best part is you all should go and check that article I'll put the link below this video and meanwhile let us jump on to the next question of the day what is better to use when creating a new database automatic shared memory management or automatic memory management guys the automatic share memory management a SMM was like 10g it was introduced in 10g version but a mmm automatic memory management was introduced in Oracle 11g right now I want to tell this thing to all the DBS like you are not the person who decides most of the time most of the times or 99% of the times it's your application vendor like if you are using sa PSAP will have guidelines that your Oracle should have these kind of parameters or these kind of configuration then only it will start working with the Oracle database now whether it is any other database with sorry whether it is any other application let's take PeopleSoft or any other vendor out there they will give you as a DBA recommendations on what parameters you should have inside the database and 99.9 percent of the times you have to follow those parameters in order for you to work better on that application right or the application will work better with the Oracle database when you have set those parameters that is more important so these kind of questions will come to me because i 100% understand you guys are not in the situation or you do not deal with the clients so you might not know these kind of questions but what happens is as a DBA architect when you deal with clients you have to check what the application vendor has to say before even you can deploy the database right I will give you an example let's say we have our man okay in Arman when we are trying to configure the admin backups on to the tapes okay so what happens in this situation the tape backup company or and yeah we can say that the tape vendor or the tape library whoever provides the tape library right so the tape library winter will have certain parameters that you need to set in the Arman in order for the Arman to work with the tape library so it's like the question I mean it seems to me like this like which is the best config I can't say which is best depending on the application so most of the times it's the application that defines what parameters you need to set inside the Oracle database and that's how it works in real time and I guess so which one is better I mean I can't saved right away but depends and of course if you are talking in normal sense then I will definitely go with automatic memory management because it's the enhanced parameter inside Oracle database so why would you not want to use it right definitely you will use it correct so that being said let's move on to the next question of the day difference between temporary table and global temporary table I guess this is a simple question because there is no difference inside Oracle there is no differentiation between global temporary table and normal temporary table so whenever you create a temporary table you have to use the global keyword but this table or the temp table will still be private to the user who created this table so you need not worry about it as far as I know in the Microsoft SQL Server you have different like global temporary table is different and normal temporary table is different so they have like private temporary table and they also have global temporary table but I guess with Oracle database you don't have that kind of issue so whatever table you create or the temporary table you create by default it is the normal template able but definitely you have to use the global keyword while creating a temporary tablespace sorry temporary table even though it is for private usage of that particular user that being said I think let's move on to the last question of the day and guys this is my favorite question by the way can you please give us some tips on what steps to perform when a query is slow guys when a query is slow you need to understand and think of it in this way like see if a query is slow I mean I personally take it in two phases okay like phase one is like normal checks phase two is lik Phase two is like high-level steps alright so what is this phase one so let's take your application team is complaining and they're saying query is running slow so then immediately there are some small checks that you need to perform and while you are performing those small checks 99% of the times like most of the issues are resolved right there so what are those checks like you have to check what is the usage of the undo you have to check what is the usage of the temp tablespace you have to check are there any database level locks that are slowing down the query you have to check the CPU usage inside the database right so you need to check all this stuff and also you have to check like if any armed and backups are running inside the database and later on you have to also check what is the load on the database whether there is another application or there is a batch load that is going on inside the database it depends these are like general checks like quick checks like in case if I am sitting on a system and if I get this kind of question what I would do is I would right away check what are the status of all these stuff like what all I just mentioned but what is the phase two phase two is high level critical like you have to check when was the table stats gathered okay table starts is very important so depending on the table spats your query execution depends also optimize that picks up the best plan based on the table stats that are available next it is about the indexes we need to look at what indexes have been used by the query and do we need to rebuild the indexes right next is the joint order like how tables are being joined most of the times when I sit on any performance tuning issue I look at the joint order of multiple queries how they are being joined I will give you an example I - stop right here I'll go to example and then come back ok listen to me carefully so what happens is let's take better two queries ok so the first query is resulting 100 records and this is joined over to another query which results in only two values ok so ultimately the output will be only two values just an assumption so now what happens when query is being executed so first you are throwing 100 records into the execution out of those 100 records only two records will be filtered I mean let us just understand on a generic level okay so two pair is being joined first query pushing 100 records second query will push only two records or that will be the output so ultimate output is only two records right so that means those 98 records were wasted it's wasted processing what if you reverse the game so if you put the second query first and it is driving two records into the system or it is fetching two records and it is being joined on to another table which will automatically fetch two records so ultimately like this is the perfect processing example right so this depends on how the queries are being joined so there is one concept called as driving table I think you guys can take this as a DBA challenge and read about driving table inside the Oracle joints this is very important okay let's come back so as I mentioned you need to check table starts you need to check indexes you need to check on the join order and then you also get the execution plan of the query you must also check what are the recursive calls or any other like details inside the execution plan that needs your attention then you have to get into trace files so you need to generate the user trace for the query and see where Oracle is wasting time while executing that query like is it like parsing or is it like execution or is it like fetch phase what are those SQL execution phases where Oracle is wasting time and then you need to also look at the wait events inside the Oracle database so all these are my ways of attacking a slow SQL like I feel like there are two phases you can't just right away get into the execution plan you can't just right away get into the a table reports I would suggest you all to start with basic like start with small tasks and then move into the phase two so divide it into two phases phase 1 phase 2 phase 1 you check all these small things inside the database phase 2 you check the big things inside the database all right guys I hope you all enjoyed the show and I believe like it's time for our most exciting part and that is the bonus question I have an exciting bonus question but I would personally say this question right on which I just answered was even more exciting than the bonus question so let's jump onto the other side of the video welcome back guys so I have this amazing question which is related to cloud and I have chosen this one for the bonus question so let me read out of this question for all of you what are the best practices for DBS who are planning to move their dev and test databases to the cloud I guess amazing question and the most important part if you are planning to move your databases to cloud the first one is encryption try to use the cloud encryption that is available and you can also encrypt your data within inside the database right that is the first thing second thing is you have to mask the data so all the SSN numbers and the important details important numbers inside the database you have to mask it and the third most important thing which I would recommend is do not enable sharing of data within the organization like if your database is in the cloud don't enable unnecessary sharing of the data within the other applications which is not required and also make sure you try to close all the sample schemas and their passwords you try to lock those accounts or probably delete those accounts if you don't need it and try to secure your database try to run the listener on a non default port even if your database is in the cloud these are my personal recommendations guys I think I am getting late today I am actually planning to leave early home so let's see you all in the next episode till then take care bye bye but don't forget to send your queries to support and engage NSS comm I would meet you all in the comment section bye for now [Music]

Channel: DBA Genesis

Views: 10,387

Rating: 4.8204083 out of 5

Keywords:

Id: DzRCxzy7fF8

Length: 15min 36sec (936 seconds)

Published: Sat Feb 08 2020