SQL Profile vs SQL Plan Management | #dailyDBA 21

In this episode of daily DBA, I pick up 5 important #DBA related questions and give my answers! Do not forget to checkout BONUS QUESTION at the end of the video! 00:00 Intro 03:06 What is materilized view and how can I purge the MV logs? 05:43 If locks are coming and releasing itself within 2-3 seconds, in this situation what we can do in oracle so locks does not occur even for few seconds. 07:34 Hi Arun, 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? 10:36 Scenario: we get complaint 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 ran sql tuning advisory and fix sql profiles forcing optimizer to choose index. Finally issue got resolved query performance increased. Later, one user runs the same sql modifying the WHERE clause which fetches 80% of the records associated in the same table. Now what will optimizer do? Will it still use the same sql profiles which is fixed to it or it will skip sql profiles and chooses different plan? 15:27 What is better option for sql performance: SQL Plan Management or SQL Profiles or Hint to improvise performance of a particular sql? Or, do you advice the application team to modify their SQLs and use logical operator instead of BETWEEN, AND, OR instead of IN? 25:13 Bonus: Hi Arun, 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 DBA's are scared of or the situation is very challenging. Website: https://www.dbagenesis.com/ Facebook: https://www.facebook.com/dbagenesis/ Instagram: https://www.instagram.com/dbagenesis/ Twitter: https://twitter.com/DbaGenesis