SQL Profile vs SQL Plan Management | #dailyDBA 21

hiren I want you to know from your personal experience what is one of the rarest of rare crisis situations a DBA can experience in real time like site crash or anything that hardly any DBA sees in their entire career which most of the DBAs are scared of or the situation is very challenging out on the site and today's episode I'm actually starting this with very sad news actually I got to know about Kobe Bryant I used to follow that guy and he was one of the amazing guys out there whether it is about the basketball game or whether it's about the way he used to run businesses and also about the way he used to conduct himself like his discipline levels used to always inspire me and I guess even we're not Cooley the Indian cricket team captain even he used to look up to Kobe Bryant for the level of fitness and kind of discipline he used to have and this news that Kobe Bryant passed away in the helicopter crash I mean it's pretty sad news for this morning I'm not sure when this show will go live but trust me this news is real bad news and when I heard about this news I was like I mean I used to follow this guy for a long time I used to watch his interviews I used to watch his game I loved basketball and the way the sky used to like win the game I used to play on the court off the court the bay used to conduct everything the way he was training his daughter I mean honestly no words but may his soul rest in peace for most of you who do not know about Kobe Bryant I would request you to watch some of his motivational videos or just type in YouTube like Kobe Bryant and you will get to know about the way he was on and off the court I mean you would love the way he actually motivates people to encourage and push towards the limits and I believe like if you pick up any one video of Kobe Bryant you will be surprised to know it gives hell lot of motivation so for all the DBAs who do not know about Kobe Bryant I would request you to once google it up try to watch his videos on YouTube you would love those videos and in case if you are fan of basketball like me then you were definitely fall in love with this guy now that being said let's start our today's episode and I would personally want to dedicate this episode to Kobe Bryant and let us start over today's episode with the first question of the day what is materialized view and how can i purge the MV logs so there is one funny thing I believe like in one of the interviews I asked this same question to one of the DBS like what is metallized view and the best answer I got a late was materialized view is a view which has some material and I had to tell him like get out of the office metallized views metallized views are views which takes space so when you create a view it's like a stored query but an MB materialized views are also known as MV so MV is a view which actually occupies space so view is only a query so whenever you query from a view whatever the stored query inside the view will be executed and the user will get the results but when it comes to mV it's a view which is of course a stored query but the results are actually taking space on the disk now beauty about MV is you can refresh the MV from the source or the base or the underlying query as and when you want right so let's take you create an MV on the employee table you are adding multiple employees to the employee table then you can refresh the MB from the employee table to get those new records into the MP that's the beauty about MP materialized view materialized view the word material stands for taking space like it's taking space on the disk right so that's why it's known as mV now whenever you try to refresh the MV automatically these MV logs are deleted so you do not have to purge those logs manually so whenever you refresh a materialized view definitely the records related to the MV refresh or the data related to the metallized view refresh will be stored into logs which is also called as MV logs these MV locks are Auto cleared whenever you run the MV refresh command so anytime you are refreshing your metallized views it will automatically clear those logs so you need not worry about MV logs or what happens regarding the MV logs now if this was an interview question then I believe like the interviewer is testing you an interviewer is trying to know whether you really know about materialized views and MV logs now that being said let's move on to the next question if locks are coming and releasing itself within 2 to 3 seconds in this situation what we can do in Oracle so locks does not occur even for few seconds like what's your problem if Oracle is like implementing locked strategy by the way if you are asking this question I can 100% say that you do not know about database asset properties and you do not know about why locks happen inside the database now if the locks are happening inside the database does it impact your database performance is there a problem inside your database and if the database locks are appearing for 2 to 3 seconds then so what's the problem I mean I don't see there is a problem and this question or there is a problem with the database and if locks are coming and releasing within two three seconds it's very normal inside the database very normal nothing to worry about the database and there is nothing that you can tune over here until unless you really know how locks work or how the application is working I think you should go ahead and read about database acid properties and inside the acid properties you will understand why locking of the data is important before updating the data or trying to modify any data inside the database once you have that knowledge then you will understand the value of locks what is the importance of having locks inside the database and why your Oracle database is having locks all right now if the locks are inside the database for like two to three seconds it's very normal all right that being said let's move on to the next question higher rule what happens if password file is deleted from the standby server will there be an impact on standby sync with primary how will we fix the issue guys I mean the question is smart enough but I always tend to go back to the basics and try to build my analogy so let's try to understand why we need a password file password file is required for you to connect to the instance and start the instance or stop the instance remotely now understand this if this is the primary job of password file so you need a password file in order to connect remotely and start the instance so when you are performing a switchover you would definitely want to stop the instance open the instance shut down one instance or put it into the mount mode right so for this activity you would definitely need the password file right on a basic level or on a very ground level of course you need to have the password file but I think my question to you would be if in case in your environment the password file was deleted did you see an impact on the database sink because as far as I believe there won't be any impact on the database sink Radim a sink is not using any kind of remote authentication now you might have a doubt like then why do we create the password file password file is actually created at the beginning of Arman cloning because you have primary server and you want to clone the primary server onto the standby so while you connect the Arman to the target database and also to the auxilary that is your standby or the database that will be your standby so for that purpose you need the password file and that's the reason we create the password file initially but later on I don't think there is any impact of the password file on to the data guard sync now that being said I would request all the experience DBAs in case if you have experienced something like this please put on your comments about this I would love to know about it but as far as I believe no it won't have any impact on the sync of the data guard yes it will have an impact somewhere when you're trying to switch over perform failover those kind of situations or if you are using DGM GRL utility which tries to connect to the standby database remotely or if you are running the utility on standby then it will use the password file to connect to the primary database so in those cases of course you need the password file but as far as this sink is concerned I don't think there is an impact of password file on to the data guard sink so let's hear from all our DBS across the globe I would want to see your answers into the comments of this video that being said let's move on to the next question that we have scenario we get complained that one SQL is taking long time for execution when investigated and found that SQL is using full table scan where it is fetching only 20% records so we're an SQL tuning advisor and fixes kill profiles forcing optimizer to choose index great finally issue cut result query performance increased amazing later one user runs the same query modifying the where clause which fetches 80% of the records associated in the same table awesome now what will optimize and do will it still use the same SQL profiles which is fixed to it or it will skip SQL profiles and choose different plan Wow I mean what an amazing question it is 100% to do with the Oracle performance tuning and guys remember like anything that you deal or any such questions that comes in mind or if you encounter these kind of scenarios I always recommend you to follow an approach that is to go back to basics like for this question I would like to go back to basics and try to start building the answer upwards so that you all understand how things work inside the database now assume this inside the database when an SQL query is to be executed who is the decision-maker it's the optimizer right so what are the things that influence the optimizer to generate different plans like the table stats these schema stats the type of indexes that are being used the type of data the query the type of query the type of joins the type of where clause how complex your where clauses and so on right now for this question assume this optimizer is at the top post level which actually decides the execution plan okay so decision maker is the optimizer I am talking only in regards to this question now when it comes to SQL profiles SQL profiles are not SQL execution plans you need to understand the difference SQL profiles will store extra statistical information related to that particular SQL or the table but Oracle needs an execution plan in order to execute a query and execution can only be generated in this case by the optimizer so what is the influence of SQL profiles on the way optimizer decides the execution plan as I mentioned earlier you need to know that SQL profiles are additional statistics regarding the table okay so when you have accurate or additional statistics regarding the table apart from the other statistics that you have inside the database it actually helps optimizer generate more accurate execution plans all right so to answer this question what will optimizer do will it still use the same SQL profiles which is fixed to it or it will skip SQL profiles and choose a different plan now in this case where you have SQL profiles still the optimizer has the highest authority to go for a different execution plan based on the user query where I guess the user is getting 80% of the records so the decision-maker is still the optimizer now we need to once again understand what is the difference between SQL profiles and the optimizer execution plan execution plan is the way a query will be executed inside the database write SQL profiles on the other hand it contains additional statistical information related to the particular table now when you have these additional statistical information it allows the optimizer to generate more and more accurate execution plan now that being said still the optimizer has the authority to change the execution plan according to the kind of SQL that is given to the optimizer so the beauty about using SQL profiles is it still allows optimizer to be more flexible in choosing the execution plan so optimizer is still the number one decision-maker when it comes to execute the particular SQL even though you have SQL profiles I hope this answer helps you so let's move on to the next question what does better option for SQL performance SQL plan or SQL profiles or Hanks to improve performance of a particular SQL or do you advise the application team to modify their SQL and use logical operator instead of between an or instead of in see guys like everyone who's listening to this video this is not about a particular thing that you can repair to improve the performance of a query it's everything right so let's take if I tell you to get a book from library it's like everything like the way you drive to the library the way you approach to the library and then how you get inside the library how much time you spend in speaking at the reception of the library then it's about you walking to the right Alvira to pick up the book and then you have to look at the book and then get the book get it checked out put it into the car get back home like if you take any simple scenario right just to get a book from a library it's about everything how efficiently can you perform each step okay so to answer this question everything is important right for the database performance it's not only about just one thing if you implement and everything will be fine it's about everything okay so for example if somebody is staying very far from the library for them it's a good choice to go in carb rather than by cycle now if somebody is living just beside the library for them it's very easy to just walk right now depending on your database the kind of sql's the kind of database design it depends like you cannot or I cannot give you just one solution like okay you just implement SQL profiles and things will be fine or you have to use SQL hands in order to improve the database performance it depends on the particular situation scenario database type the number of records that you are trying to how the data is stored inside the database what type of the tablespace whether it is locally managed or dictionary managed everything comes into consideration while improving the database performance now when it comes to answering your question something like this I guess I'll choose a generic approach to answer this question see you have to start the performance tuning from the application team because that's where the constant sql's are coming right so if you can ask the application team to improve their SQL to use bind variables instead of literals like that's one thing we already discussed in one of my previous episodes so always ask the application team to use bind variables instead of literals that's one thing you would need to also work with the application team in order to tune all the SQL that are coming inside the database because once you tune these sql's at least at the application level then for a longer period of time these skills do not change until unless there is an upgrade in the application or until unless there is some other change in the code of the application development so it's better like at the initial stage of the application development if you can be part or if you as a DBA can be part of the application development and guide the sql's to be in a good way or guide the application team to write the SQL syn a good way then it will be more efficient in future I know it doesn't happen like you won't have control on the development team but I think it's the job of the database architect who has to get it done now that being said I think one more very important thing that happens when it comes to application development and once again this has to be taken care by the database architect is to make sure that the application is avoiding the locks and the dead locks at the initial level so you need to make sure like one session is not blocking the other session and the code is written well so that it allows efficient working inside the database also some applications are written so bad that they tend to create lot of inactive Sessions inside the database so the users will join the database or user sessions are created inside the database and later on I mean they're not terminated from the database now as a DBA of course you have your own measures in order to terminate the inactive sessions that's a separate case but what I'm trying to say is the performance management starts from the application theme right so you as a DBA need to help the application team in order to build a great application where SQL czar tune you are using the bind variables instead of literals you are trying to reduce the number of locks that the application creates you're trying to reduce the dead locks and also you need to make sure that the application is not creating unnecessary inactive sessions inside the database because all this impact the database performance and you can only control it at the application level you won't be able to control at the DB level of course you have your own measures but it's always good if this has already been taken care at the application level so when it comes to performance tuning it starts with the application team now let's come to the Oracle database now to answer your question right to the point where you asked what is the better option for SQL performance SQL plan management or SQL profiles or hints to improve performance of a particular SQL I would always choose SQL profiles the reason being I am NOT a fan of Oracle hints because it actually tricks the database optimizer and I really don't like to use hints inside the database I mean I am completely against it now when it comes to SQL profiles and this is what I would recommend or I would go for the first thing when it comes to database level performance tuning and I also mentioned about this earlier the beauty about SQL profiles is it stores additional statistics about the table and that helps optimizer generate accurate plan now the beauty is still the optimizer stays at the top level while deciding the execution plan for that particular SQL query now that being said when it comes to SQL plan management I think I mean I would like to go for SQL plan management only when I know that the baseline plan that I have is the best plan ever but the biggest problem is like while your database is growing while your database is having more data and your database is getting more SQL queries the optimizer might generate a better plan when compared to the SQL profile management in those kind of cases I think sometimes you have another competitor to the optimizer an optimizer is not allowed to be the highest or the single most decision-maker when it comes to execution plan because SQL plan management also stores SQL plan while compared to SQL profiles SQL profile stores statistics it does not store execution plan so these statistics when given to the optimizer optimizer might generate a better plan so comparatively as I mentioned earlier I would love to go with SQL profiles that would be the first thing inside the database if I want to improve the performance or if I want to choose the option between SQL profile management escape land management and hence SQL profile management number one SQL plan management I might go for SQL plan management only when I know that the baseline plan is the optimal plan for the SQL execution I always avoid Oracle hints because I am NOT a fan of SQL hints now to sum up this answer I would like to tell you once again the performance tuning starts at the application level then you have to drill down to the database level now if your application is secured I guess definitely your database performance will be amazing and that is what is most important to all the BBS now that being said guys I think we have these questions for the day I think I enjoyed some of the questions and as I always say send your questions doubts confusions to support a debate in CENTCOM and by the way guys I am still working on something important which I wanted to hear so I am asking my team can we have something like where all the DB is if somebody wants to book a call with me they can just click schedule a call and then we get on one on one call and try to help you with your career doubts conclusions or any other questions having a schedule mechanism or pathetic fall would be one of the greatest changes or one of the greatest movements towards building this big is being a community and I really mean it now that being said let's move on to the most exciting part and that's the bonus question and let me tell you this I actually like this bonus question and I have chosen it wantedly for this episode so let's quickly jump onto the bonus question [Music] alright I'm back and I have this very long bonus question from one of our dBase I guess you all can see my writing it's not that good but let me first read out this question hi Aaron I wanted to know from your personal experience what is one of the rarest of rare crisis situation a DBA can experience in real time like site crash or anything that hardly any DBA sees in their entire career which most of the DBAs are scared of or the situation is very challenging I mean I like the question but the challenge or the challenging situation depends from DBA to DBA like I mean something that's easy for me might be challenging for other libya's or something that's easy for you might be challenging for the other DBA so I really can't talk about what is challenging for you independence and I guess in your question you gave the answer like what could be the rarest of the rarest thing that a DBA can encounter is the site failure right and it does happen so if there is a site failure and let me give you this thing okay so if there is a site failure you are the owner of the database team and you are at home sleeping your team is trying to contact you your phone is switched off because of battery dead I think this would be the rarest of the rarest like example that I can give you where I mean anything can happen in real time you cannot really say that something that is particular to a situation or something that demands special attention I mean as I mentioned earlier like it depends challenge is like what level of understanding you have or what is challenging for you might not be challenging for others now that being said I will try to recollect couple of issues that other DBS created in my environment and where I had to kind of like jump in and resolve those issues and I will not be giving the answers right away on this video for these problems because I want you to take this as a DBA challenge and try to put on your comments below this video as to how would you solve these kind of rarest problems that are created by jr. DBAs in my team so one of the jr. debates what they did is they upgraded the database schema or the recovery Catalog schema all right so if you all are familiar with Armen catalog so upgrading the Armen catalog database is a separate thing and upgrading the Armen catalog schema is a separate thing alright now that being said we upgraded the Armen catalog database but we did not upgrade the schema now this guy I mean we know when to upgrade this schema but this junior DBA thought like okay we have upgraded the database so we are supposed to upgrade the schema also and he upgraded the schema and you know what happened like seven to eight hundred databases backup started failing and we were getting alerts after alerts after alert like when I saw the alert email my question was there might be a problem with the storage or there might be problem with the network team or there might be problem with the storage vendor or there might be problem with the Armen backups I mean I couldn't figure out the issue but later on when I spoke to my team then this June a DBA was telling like oh you know what I ran this command I was like wow great job so I'm not giving you all the answer how I solve this issue I was able to solve the issue within 45 minutes so I want you all to put on your comments how would you solve this kind of situation apart from this of course we have so many other challenges that come up every single day I guess the most challenging part would be when you have to restore a database and you're trying to recover the database you are not getting the archives that is one of the challenging issues I guess I discussed in one of our previous episodes about how to forcefully open the database right now also apart from this there are other challenges that come at the design level all right so how do you implement Golden Gate in a situation where you have one server on the physical one on the cloud and then the cloud server is not on the public network and you need a VPN connection to have a private network connection to the cloud server these are genuine problems I think that's where technically I work and because we have to give these kind of solutions to the clients so for me the challenges is to build these kind of solutions ok so if you talk about anything in terms of developing a solution for the clients those would be very challenging tasks when compared to DB activities like in DB activities what could be the worst thing that can happen like database crash the entire site failure you have to perform a switchover or like at that site failure where there is no dr you have a backup at a separate location so you need to use armin cloning and then recreate the database in a separate site and make the database available i think those are the rarest of the rarest because you don't see site failure every now and then right you don't see like buildings crashing or you don't see cities getting flooded right or earthquakes crashing all the buildings no it doesn't happen that frequent and but when it happens it does make an impact and the DBAs need to be on their toes so for all the DBS who are listening I will tell you one thing it's not about how well you write a backup strategy as a dbx your responsibility to know like how do recover a database because it might happen any time any given point of time there might be a crash and if there is a crash you as a debate you cannot write an email to support our debate Genesis comm I know I'm promoting my email address but still so you cannot write an email to support and eBay Genesis comm saying like hey Arun can you help me our databases crash no you cannot dig well when there is fire you need to practice Armen recoveries when there is no disaster occurring when you are sitting nicely in your office chair and when you say that okay today we don't have any alerts and today we have no work so in those kind of situations I request all of you like take time create a test VM try to simulate Arman scenarios if you don't know Arman scenarios I will give you scenarios I will give you hundreds of scenarios that you try to come up with these solutions you will learn a lot with those scenarios that being said I think to answer this question once again challenge depends on what is hard or what is easy or what is tough for you I can't tell like what is tough for what is easy for you like for me the biggest strength my biggest strength when it comes to Oracle databases and data guard and Armen recoveries I just love those zones and for me anything in those zones I would be like killing or nailing it right so for me it doesn't seem like a challenge when it has to recover or it has to perform with the database recovery or to configure a data guard but I am not sure about you like in your case what might be the issue right and that being said guys I think I love this bonus question and I know you all would also love this bonus question I request all of you to put on the comments as to how will you solve that problem that junior DB created in my office and I would see you all in the comment section I think my voice is pretty high so I would see you all in the comment section till then take care bye bye I'll see you all in the comments and also in the neck five [Music]

Channel: DBA Genesis

Views: 8,954

Rating: 4.891892 out of 5

Keywords:

Id: KbiUVHQ6uRQ

Length: 33min 56sec (2036 seconds)

Published: Wed Jan 29 2020