SQL Profile vs SQL Plan Management |
Vložit
- čas přidán 8. 07. 2024
- 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: www.dbagenesis.com/
Facebook: / dbagenesis
Instagram: / dbagenesis
Twitter: / dbagenesis - Věda a technologie
If standby db loses or deleted its password file it will impact the shipping of archived to standby from prod . Solution is to copy the password file from prod to standby.
Ya ,Junaid you are right...I also faced same problem 👍
When password file deleted , RFC will stop
ship will stop
I am so addicted to all of your videos, specially in my mind I am happy with some of the concepts are so correct and same as you are explaining and at the same time few of the areas I am correcting as you are giving more and correct clarity.
Thanks Arun for so calm, composed, confident in explaining all these DBA related areas.
Most Awaited topic SQL profile 👍🏻
Thanks Arun for picking the bonus appreciated
Regarding the password file in dataguard. It is not only required during the duplication process but the 'fal_server' parameter requires it to connect to the primary db. FYI : When MRP finds that an archive log is missing during media recovery, it sends the fal_client information to the server identified by fal_server and requests fal_server to resend the file again.
Heartly thanks for Kindle books.. it's asome....
very good explanation about SQL Profile .... Thanks Sir
Yes we have seen that there is gap in log shipping due to the password file corruption on standby database.
Yes, we create bcp the way you explained using rman clone i.e connecting to source db rman and auxiliary instance and yes if password file is lost or not same on both the sides , bcp will be out of sync.
Thanks arun
Nice explanation arun
Thank you.
Nice presentation ......
Hi Arun,
We have seen password corruption in standby causing problem in log shipping from primary. Dest basically gets errors out stating Oracle not available (if done defer/enable)
Arun bro.. I have cleared a technical interview today one and only because of you.. by learning from your videos.. I am so grateful to you..
Keep it up Asha, I love when our DBA community grows.. Congratulations! 🎉
@@dbagenesis Thank you very much 😊
Exactly Junaid Jamal...I face this issue...my syncing process stops...I need to copy the password file from Production to Standby..it started syncing.
Hello Arun,
Can you pls tell what is a base line?
Is there a licensing limit to the number of pdb's for a cdb or number of cdb's?
the scenario related to RMAN catalog upgrade .. I researched on it but didn't find the solution. Could you please provide correct scenario.. some error code will help to narrow down the research.. most common solutions I got was to run upgrade catalog and make sure that version of RMAN client and catalog schema is compatible.
It regarding the sql profile. When the user will change the sql and add another condition in where clause, it will change the sql_id of that sql and the sql profile earlier created on a different sql if will simply not work. Isn’t it?
ORA-02019: connection description for remote database not found in Oracle.
What could be the issue?
Check tns entries.
Please explain partitions
Hi Arun, I have a question ,can we run tunning advisor in standby db ,,, as we have few jobs running on standby db as well..
Yes, you can run it. This is called as remote sql tuning. Read about it .. You will have to use database_link_to parameter
Shipping of archivelogs won't happen and sync will be impacted in case password file is not there.
Is it possible to force an old execution plan for an SQL to be used by the optimizer incase the old plan performed better than current execution plan ?
Yes...use SQL profile
@Arun - You told deleting password file wont impact sync
But what if the SYS password on primary is changed and we don't change it on standby ? - Will it impact sync ?
I was confirming it with other experienced DBA as I was not sure about it. Its mandatory to have it, simple.
If we change sys password at primary n not copy, it breaks replication break
Hi arun
What do u mean by additional statistics inside the SQL Profiles what are that statistics.?
Why not you research same and respond to this comment?
@@dbagenesis I have already check but did not get any specific answer.
U were just blabbering on the sql profile part rather than coming straight to answer. And you didn't even answer it correctly. I could see 2-3 pauses on that part maybe u were googling the answer.
yahoo not google 😂
You are right Mayuresh.... I think if there is change in WHERE clause then the SQL stmt is also changed and it will be treated as a new query by Oracle engine, then there is no point to follow the old SQL query profile.
Secondly its the most common scenario, if you change the password file in Standby DB log file shipping stops and Primary will not be in sync with Standby.
Third if someone ask query is running slow, don't point towards application team without analyzing anything. Here person wants to know what all should be analyzed to find root cause.
Sorry to say Arun.. Your technical round is not cleared.
@@techlearner2447 technical round not clear 😂😂😂
Hi , i have seen your video and feel like you are too aggressive about your words (especially i felt when you said others that they dont know 100%). Always pointing others that they do not know anything. Nobody in this world knows everything including me. You dont know anything what I know and vice versa. so the choice of words is important brother when you are teaching something.