Slow Running Query Tips |
Vložit
- čas přidán 21. 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
0:41 I want to know about redolog status for following scenario. We have 3 redolog groups(1,2 and 3) each group having 2 members(a andb). Q:When we are executing some DML operations by that time how the LGWR will write changes to online redologs?
whether it is writing to 1a, 1b, 2a, 2b....
or
will it write 1a and 1b simultaneously ?
03:11 How to find most fragmented tables and indexes that are caused of high I/O and how to get rid of them?
04:07 What is better to use when creating a new database: Automatic Shared memory Management or Automatic Memory Management.
07:07 Difference between temporary table and global temporary table?
08:17 Can you pls give us some tips on what steps to perform when a query is slow?
Bonus Question
13:41 What are the best practices for DBAs who are planning to move their DEV & test database to Cloud?
Website: www.dbagenesis.com/
Facebook: / dbagenesis
Instagram: / dbagenesis
Twitter: / dbagenesis - Věda a technologie
Great video, great help! which one comes first: the AWR report or the user trace?
Request to include the following topics in Daily DBA series:
- Performance Tuning with the help of AWR Report ( like, is there CPU load, memory issue etc )
- EM Cloud Control 12c tips to observe and administer databases
noted!~
@@dbagenesis Thanks for your consideration :)
Hi Arun... You have explained in a great way about to tune slow running queries. Thanks a lot..
My pleasure! Keep Watching.
Could you please explain LRU in buffer cache in real time example.
Sir Can you Please Explain how the Fiddler Tool is used ?
Hi arun how to check application long running query with parameters or bind variables please help me
What is the difference between CPU and load average which shows in uptime command..
I have observed many time that CPU was normal but my load average was increased to 60.
Below is a good read:
serverfault.com/questions/667078/high-cpu-utilization-but-low-load-average
Why when I run query on database it is running faster than when I make same query as report using report builder 6i it is running too slow ... also some queries run too slow when I use condition to retrieve data for month but when I use same query to retrieve one year runs so fast also using report builder
could you please explain how to decide the value given to parallel option used in data pump exports & imports
Depends on how many export dump files you would like to have. If you have give parallel = 10 for 10 GB export, you might end up with 10 export dump files.
Try to decide via how many export dump files you need.
Should depend on how many cpus you have on the server. You dont want to set parallel value way above the number of cpus you got. Also consider whether the server hosts single or multiple DBs. As you would not want to use all CPUs and affect other DBs
@Arun Say suppose..If oracle gather the table stats and it's like 2weeks behind to till date..then will it require to gather the stats manually to till date? How will it perform in such cases..
Think about it in this way, what if there was no change to the table, then you don't even need to gather stats :P
Else, you can use DBMS_stats package to gather stats.
How to recover only some deleted records for a table?When Flashback is disable? Can you share high level steps?
Except when you want to use FLASHBACK DATABASE, the FLASHBACK must be enabled. Else, even if its OFF, you can use FLASHBACK on tables.
Could you please explain what is the difference between SGA_Target, PGA_Target and Memory_Target?
Just use MEMORY_TARGET and forget about rest. Read more about MEMORY_TARGET on google
Most of Qs are simple and answers can easily be found in docs/blogs. It would help people if you take some more adv Qs/topics. Same applies most of videos.
Sure! if you are finding it difficult to get answers to your challenging DBA questions.. Send them onto support@dbagenesis.com and we shall shoot an advance QnA show for you!
If their is blocking sessions in database and in 1 session multiple dml statements are going on and session can't be killed manual or it's prohibited by db to killed then how we will resolve the blocking?
You need to speak to application team to understand what is the session 1 doing. Is it necessary for session 1 to run dmls back to back or session 2 can wait until session 1 is finished... etc...etc...etc...
I am taking export using datapump for tables my export is get hang in database when i check it is showing defining state? But same i am running on other server export is running fine.
? What could be issue?
Needs more investigation!
I have refreshed all schema in a database. When I compile the packages, few packages still shows invalid. How to make them valid? Invalid packages around 80 to 100.
Try running utlrp.sql script
Sir, As committed and uncommitted changes both are flushed to redo log files, during the time of instance recovery how the DB will come to know which one is committed or uncommitted?
smon will clean up the uncommitted statements
Is there any difference between Explain plan and execution plan?
Can you please explain this??
It's one and the same.
Hi Arun, what is the difference between ASM Striping and ASM Rebalancing?
Both are same but used in different context.
ASM STRIPING:
=============
If there are two disks inside a diskgroup, data will be evenly spread (stored) on both disks.
ASM REBALANCING:
=================
If there are two disks inside a diskgroup and you add a third diskgroup, then data will be evenly distributed on all the three disks.
What is meant by stale stats on a table ?how to resolve? kindly explain sir.
When stats are not gathered for a long time, old stats become stale (means waste). You just run gather schema/table stats (DBMS_STATS) package.
Hi arun my question is I want to export a schema in db of 100GB but the space that i have at os level is not more that 20GB in all mount points. How will i do that?
You can use PARALLEL option to generate small dump files of 10 GB and keep moving files to other disk as they get generated!
@@dbagenesis one more on this that how will I specify the size of dumpfile because specifying the parallel parameter will only create the no. Of dumpfile that i defined in that parameter?
Great sir.. Thank you so much😊👍
Most welcome
Hi Sir,
i am Rohan, currently working as oracle DBA since last 20 months
I am very much concerned about the feature of DBAs in comming years, can you please suggest should i change the technology or i can learn cloud along with this, whether aws, azure or oracle cloud which will be better.
Please suggest Sir
Regards,
Rohan
I am not sure why so many DBAs are scared of the role change! Even if everything is automated, few things cannot be automated:
- Installation of Oracle
- Provisioning of Oracle servers in cloud or physical
- Designing the application backend
- Creating application users and granting/revoking access
- Debugging sql queries to tuning purpose
- Setting up replication from physical to cloud
and the list goes on!
Yes, its time to learn cloud. Start with AWS and then with Oracle cloud. Thats enough for now.
Thanks
Welcome!
Waiting for next episode sir
we are back :P
So beautiful so elegant just looking like a wow
I want to restore a table using rman does it recover using level 0 or level 1 can you share high level steps?
Table? you can restore tablespace / datafile / database from rman.
@@dbagenesis From 12c onwards you new feature which allow you restore a single table if required for that what backup to be used for restoration? L0 or L1.
Good content
Thank you!
Great peoples only share knowledge. 🙏
hi... very good quesion ... even answering is good knowledgeable but.... pls dnt stand and answer.. or moving... .kindly use white board... back of you...
Hi Arun , u r doing great job, i have one que - when u say u have three group which further have two members a & b and process is like when first group is full it will start writing in group 2 and then group 3 but what will happen if all group is full bcoz we know that the size assign to redolog is nearly 300mb or nearby ...plz let me know @dbagenesis