Fix SQL Plan in Oracle |
Vložit
- čas přidán 21. 07. 2024
- In this episode of daily DBA, I pick up 6 important #DBA related questions and give my answers! Do not forget to checkout BONUS QUESTION at the end of the video!
00:00 Intro
02:13 Client is upgrading OS from OEL 6 to OEL 7. They want us to upgrade the DB also from 11g to 12c. How should I proceed further?
09:01 How to fix the plan for an sql in database?
12:46 How to mining data using redo log files?
16:00 I generally see ORA-19804: cannot reclaim 67108864 bytes disk space from 10737418240 bytes limit. Error in all the production database backups. What to do?
17:54 What is use of snapshot controlfile. How rman uses snapshot controlfile with recovery catalog and without recovery catalog.
21:06 Can you please explain about types of protection modes in dataguard and their purpose?
Bonus Question
26:28 What are the prerequisits for becoming a DBA?
Website: www.dbagenesis.com/
Facebook: / dbagenesis
Instagram: / dbagenesis
Twitter: / dbagenesis - Věda a technologie
Hi Arun, Loved the way you describe the questions,thanks.
As per my suggestion for bigger(in TB) size database migration data guard is good option & also datagurd is free with enterprise edition bundle license. we can save downtime in migration using dataguard. Please clear if i am wrong.
Appriciate your efforts
Hi Arun, thankyou so much for sharing the amazing knowledge 😊
My pleasure 😊
Hi Arun,In Oracle 12c database in my prod database FRA is not releasing Flashback Logs(after retention window over) ,what would be the issue? Nothing major in alerting ,it's causing issue in alarming alerts via OEM
one if the archive log deleted on Primary database without backp . How to restore the archive log . is there any impact to the DR database
If the application side slow so what I have to do
If database size is more than 50TB then? also when we upgrade the OS what are the components need to take the backup.
What do you think about what components backup you must take?
Sir please try to talk about Data guard vs golden gate.
Because in the next couple of weeks we need to setup a new server installed on 12cR2.
Have you looked at below video: czcams.com/video/89jYzLNddHs/video.html
Can you tell me how cursors work actually in shared sql area...I am confused why and when the same sql Id will be having its child cursors and version counts??
Sometimes when I try to migrate the base line from one environmnt to another it is confusing me which plan do i actually need to migrate because I see two r three plans for the same sql id but with its child cursors having different sql plans.
Picked up your question for upcoming episode!
High version count and child cursors for the same query could point to an application design issue. You may have designed application to use literals instead of bind variables. In your scenario there’s a bit of work to be done at application design level first.
QUESTION: HOW TO CHOSE WHEN TO DO INDEXING ON A TABLE AND ON WHICH ATTRIBUTE/PARAMETER OF TABLE?
Create index on columns which application team uses in where clause.
Difference between integrated capture and classic capture in golden gate? In integrated capture do we need to create the logminer database in source database or Oracle uses logminer mechanism ?
Classic capture (resides outside DB) directly reads from the redo/archive logs, Integrated Capture is registered within the Oracle db and GG reads using internal Stream/Logminer APIs to read the logs and writes the changes as LCRs.
Hi Renny, we try not to allow viewers to promote other website courses. If you do so, we will be banning you from commenting on any of our videos. Request you to maintain our channel guidelines!
Though we will never stop you to share your knowledge and expertise with fellow DBAs. All the best!
Good one, picked it up for upcoming episode !!
Thank you sir for picking up.
Your effort is pretty awesome
Thanks
Hi arun,
In our dataguard setup, one of database having 1 archivelog gap all the time, will added standby redolog also and started recovery in real time apply but still 1 log gap is there. what the reason behind this..?
Please confirm SRL's and ORL's having same size on both primary and standby databases.
@@srinivasaraov6343 Both logs having same size.
Depends, looks like it needs to be investigated. You will have to look into both primary and standby alert logs to identify whats going on.
Also check, sometimes when you enable MRP, you can delay the log apply. See if you have something like that when you enable MRP.
Hi sir
Can you explain SQL tuning advisor how to run and how to analyze
You can utilise DBMS_SQLTUNE package for tuning your query or can get the help of OEM. I'd still suggest to go through manual steps ;)
3 tasks had to be done,
DBMS_SQLTUNE_*create_tuning_task(
DBMS_SQLTUNE_*execute_tuning_task(
DBMS_SQLTUNE_*report_tuning_task(
Sure, have a look at below article:
support.dbagenesis.com/knowledge-base/automatic-sql-tuning-in-oracle/
Sir, when database will use full table scan and when they use the index? Is there anything I need to enable for full table a scan?
There are many criteria's on which index is not being used (I presume u mean to say index present for the column but still not been used). Gathering statistics on the table or baselining a plan for the query if index was already being used might help.
You could try using index within hint to see if the index is being utilised as well, For ex, select /*+ INDEX (TABLE Index_name) */ * from table where column=condition;
Many reasons, so when optimizer is going for full table scan, that means there could be couple of reasons for IGNORING THE INDEX:
1. Index needs to be rebuild
2. Full table scan is less costly compared to Index scan
You do not need to enable anything, its just if you want to force Index scan when query is going for full table scan, then you use HINTS. And, vice versa as well.
@@nishanthajay thank you sir
@@dbagenesis thank you sir
1.u can collect table&&indexes statistics and Histogram. to be a DBA ,2.u should know which is best execution plan access the table with lowest cost from the cbo 3.When necessary using hint /+ */ to stabilize the execution plan. good luck.
Sir,
Why we go database mount restrict before dropping database
What do you think why Oracle implemented it?
So that except user who have restrict privelege cant access database.
Sir, how to fix plan for SQL?
There are 1000 ways, depends on type of SQL, performance problem and what you are trying to achieve.
You have wasted more than 2 minutes in the beginning
I do have plans to waste more time in future.. Stop watching our videos and watch some prank videos instead