Slow Running Query Tips |

Подписаться 37 тыс.

Просмотров 20 тыс.

50% 1

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

Наука

Опубликовано:

30 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 57

@saadkhan45 4 года назад

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

@dbagenesis 4 года назад

noted!~

@saadkhan45 4 года назад

@@dbagenesis Thanks for your consideration :)

@ganeshhelaskar3811 4 года назад

Could you please explain LRU in buffer cache in real time example.

@naveenkumar-kw2ch 2 года назад

Hi arun how to check application long running query with parameters or bind variables please help me

@ztech4634 Год назад

Great video, great help! which one comes first: the AWR report or the user trace?

@himanshugarg4846 4 года назад

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?

@dbagenesis 4 года назад

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...

@vinny_vlogs4262 4 года назад

@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..

@dbagenesis 4 года назад

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.

@shehbazphulmamdi3655 4 года назад

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.

@dbagenesis 4 года назад

Below is a good read: serverfault.com/questions/667078/high-cpu-utilization-but-low-load-average

@vishalkunden4318 4 года назад

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?

@dbagenesis 4 года назад

Needs more investigation!

@matheenahamed2692 4 года назад

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.

@dbagenesis 4 года назад

Try running utlrp.sql script

@durgasagar45678 3 года назад

Hi Arun... You have explained in a great way about to tune slow running queries. Thanks a lot..

@dbagenesis 3 года назад

My pleasure! Keep Watching.

@MrSuperIbro 4 года назад

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

@bhargavreddy682 4 года назад

could you please explain how to decide the value given to parallel option used in data pump exports & imports

@dbagenesis 4 года назад

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.

@gingzabala6507 2 года назад

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

@himanshugarg4846 4 года назад

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 4 года назад

You can use PARALLEL option to generate small dump files of 10 GB and keep moving files to other disk as they get generated!

@himanshugarg4846 4 года назад

@@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?

@vickypatekar9532 3 года назад

Sir Can you Please Explain how the Fiddler Tool is used ?

@vishalkunden4318 4 года назад

How to recover only some deleted records for a table?When Flashback is disable? Can you share high level steps?

@dbagenesis 4 года назад

Except when you want to use FLASHBACK DATABASE, the FLASHBACK must be enabled. Else, even if its OFF, you can use FLASHBACK on tables.

@rajatsanwal9919 3 года назад

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?

@jebathannavis5785 Год назад

smon will clean up the uncommitted statements

@AnujSainiSearchEngineWall 4 года назад

Could you please explain what is the difference between SGA_Target, PGA_Target and Memory_Target?

@dbagenesis 4 года назад

Just use MEMORY_TARGET and forget about rest. Read more about MEMORY_TARGET on google

@pratheekkonidena 4 года назад

Hi Arun, what is the difference between ASM Striping and ASM Rebalancing?

@dbagenesis 4 года назад

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.

@devilsgaming5915 4 года назад

What is meant by stale stats on a table ?how to resolve? kindly explain sir.

@dbagenesis 4 года назад

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.

@rohanprakash6635 3 года назад

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

@dbagenesis 3 года назад

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.

@suvranshuadhya7680 4 года назад

Is there any difference between Explain plan and execution plan? Can you please explain this??

@dbagenesis 4 года назад

It's one and the same.

@bt2gr8k72 3 года назад

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.

@dbagenesis 3 года назад

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!

@srividyathanigaimailai6208 Год назад

Great sir.. Thank you so much😊👍

@dbagenesis Год назад

Most welcome

@mayurrahate 2 года назад

Thanks

@dbagenesis 2 года назад

Welcome!

@vishalkunden4318 4 года назад

I want to restore a table using rman does it recover using level 0 or level 1 can you share high level steps?

@dbagenesis 4 года назад

Table? you can restore tablespace / datafile / database from rman.

@vishalkunden4318 4 года назад

@@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.

@mahmudurkhan5754 4 года назад

Waiting for next episode sir

@dbagenesis 4 года назад

we are back :P

@bharathkumar-ds8cd 4 года назад

Great peoples only share knowledge. 🙏

@kalyantheindian954 2 года назад

Good content

@dbagenesis Год назад

Thank you!

@BabitaSingh-rn4ol 6 месяцев назад

So beautiful so elegant just looking like a wow

@swarnad2547 2 года назад

hi... very good quesion ... even answering is good knowledgeable but.... pls dnt stand and answer.. or moving... .kindly use white board... back of you...

@vinitdarne1348 8 месяцев назад

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