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
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!
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.
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...
@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.
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?
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?
@@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?
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.
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.
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
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.
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
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
hi... very good quesion ... even answering is good knowledgeable but.... pls dnt stand and answer.. or moving... .kindly use white board... back of you...
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?
@@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.
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.
Great video, great help! which one comes first: the AWR report or the user trace?
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!
Hello Sir, how to check which query is generating more archive
Could you please explain LRU in buffer cache in real time example.
Great sir.. Thank you so much😊👍
Most welcome
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
Hi arun how to check application long running query with parameters or bind variables please help me
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...
@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.
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!
Sir Can you Please Explain how the Fiddler Tool is used ?
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?
Good content
Thank you!
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
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.
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
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
Great peoples only share knowledge. 🙏
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.
Thanks
Welcome!
Is there any difference between Explain plan and execution plan?
Can you please explain this??
It's one and the same.
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.
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
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
hi... very good quesion ... even answering is good knowledgeable but.... pls dnt stand and answer.. or moving... .kindly use white board... back of you...
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
So beautiful so elegant just looking like a wow
Waiting for next episode sir
we are back :P
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.