I neva seen someone explaining HWM in such a conceptual way... Kudos to ur simplicity...
@dbagenesis
3 жыл бұрын
Most welcome !
@priyatoshsinha1719
4 жыл бұрын
Hi Arun, A) Rebuild index on the reporting table (Only Selects) -> It depends on the data distribution.If data is fragmented,we can reorg the table and rebuild index once . If no further DML's are happening on the table,then no need to rebuild the indexes. B) select * from emp; -> It will be a FTS as we are querying all the data whitout any predicate. Even if predicate is there,it depends how much data needs to be retrieved . If data with predicate is more than 80% of the rows,optimizer will prefer FTS. Thanks
@kodfam
4 жыл бұрын
If you’re selecting all the records then index usage and rebuild are not needed because it will be an overhead on the database. This is because reading the index and scanning the entire table at the end will be expensive as compared to just the full table scan. If the query output is more than 50% full table scan is better than index scan. #dbaChallenge
@samaraviteja
4 жыл бұрын
As per my knowledge and understanding am answering these . Yes , since reporting database is for selecting the contents mainly . Indexes will improve the performance. These are useful . For select * from table , index won’t be used bz it has to be under go full table scan . DB file header Contains metà info about the file and dB block header contains space mgmt info .
@croxzoxcreation2555
2 жыл бұрын
Hlo can you explain me difference of rebuild index and rename index... Do by using rebuild index add extra index from the previous one in a table? And in rename index it change the position of previous index
@cryptoseries3259
2 жыл бұрын
Exactly what you explained in this video about open database forcefully happened to me in my Job. We had to go through the same route to fix the issue! Very good analysis
@dbagenesis
2 жыл бұрын
Thanks for sharing
@shubhamdwivedi1120
4 жыл бұрын
Hi Arun Sir, Thanks for all the knowledge you are sharing with us . so mine answer for #DBAChallenge A2:- We have to rebuild index on reporting server also , because even on reporting server data stored in it changes by time as the changes made to database.
@aaberosmani
3 жыл бұрын
@DBA Genesis - Is this answer correct? Please revert.
@samaraviteja
4 жыл бұрын
You are Awesome Arun 👏🏻👏🏻👏🏻 I guess no one will explain wt is meant by high water mark better than this . I have been following ur videos for 1month . I will send a personal email for queries and other details . Again Kudos to your explanation 🙌🏻🙌🏻🙌🏻Thanks for Sharing the Knowledge.😊😊😊
@bhaskarreddy2777
4 жыл бұрын
Thanks a ton Arun for explaining in such tiny detail. Watching your videos surely giving me lot of knowledge. Take a bow for this great work 🙇♂️ . Looking forward for some more excited content like this.
@dbagenesis
4 жыл бұрын
Most welcome! Kindly help me in my vision to reach all the DBAs on the planet. Let's build the biggest DBA community and spread the word as much as possible.
@amruthamunamarthi7137
4 жыл бұрын
Hi Arun, I'm soo glad I've been a part of this channel... I don't think anyone would explain things like u did, no channel is dedicated to just DBA stuff like yours and these series of dailyDBA videos took it to next level. Keep doing wat Ur doing :) thanks a ton. My question: after we do some kind of performance tuning on the database like index rebuild or tablespace reorg, how do I measure the performance before and after?? How can I be sure that my tuning has made any difference to the performance or not?
@dbagenesis
4 жыл бұрын
PT is a reactive process. So you need to know that your application team is complaining about queries running slow. This gives you some data like xyz query is taking 30 min to execute. You start PT on this one query first and later speak with application team as to how much time it takes to execute the same query. Thats how you gather details that your tuning tasks are in positive direction. All in all, you must have a PT problem OR some stats to check your actions agains!
@PawanKumar-jd4ju
4 жыл бұрын
Where i can purchase this book
@dbagenesis
4 жыл бұрын
All DBA Genesis books can be found here: support.dbagenesis.com/dba-books/
@aniketbanerjee6377
4 жыл бұрын
Indexes on reporting table need not be rebuild as its only required when there is deletes inside table.Also For select * from table , index won’t be used as it doesn't has where clause rather it will go for full table scan Correct me Arun if I am wrong here .
@smartdba5784
4 жыл бұрын
Lot of knowledge gathered for interview
@rajivshivaraman6476
4 жыл бұрын
DB block contain : header, table directory, Row directory, row data, free space
@suryakolla5017
4 жыл бұрын
#dbaChallenge Data block header consists of information, such as the block address and the type of segment.and also information like pct used % and pct free%.
@Plaguer_agent
2 жыл бұрын
first time I am understanding high water mark such a nice and easy way , well explained Arun you are genius and your lectures are very interesting.
@dbagenesis
2 жыл бұрын
It's my pleasure
@croxzoxcreation2555
2 жыл бұрын
Hlo can you explain me difference of rebuild index and rename index... Do by using rebuild index add extra index from the previous one in a table? And in rename index it change the position of previous index?
@j.m.789
4 жыл бұрын
Thanks for the video. I would recommend in the notes putting links/time stamps of the video where the question is asked and answered, so someone can get to the question they want to know about quickly. Thanks!
@dbagenesis
4 жыл бұрын
Good idea J.M. will ask my team to do it!
@zmomdiary2020
2 жыл бұрын
i second to this :)
@venkatwgl856
4 жыл бұрын
Amazing 👌, thanks very much
@kumarc5952
4 жыл бұрын
Hi Arun really amazing videos and good stuff to learn and refresh topics.. pls explain SPM and sql plan baselines
@Plaguer_agent
2 жыл бұрын
on Reporting table index rebuild is not required because there is no DML apply on table. so no shuffling of record as explained in video with using book
@kishorpuri7375
2 жыл бұрын
Well explained. Very easy to understand.
@croxzoxcreation2555
2 жыл бұрын
Hlo can you explain me difference of rebuild index and rename index... Do by using rebuild index add extra index from the previous one in a table? And in rename index it change the position of previous index
@patrickng6606
3 жыл бұрын
Very good job in explaining the high water mark, however regarding why rebuilding indexes explanation I don’t think it is correct, but overall very helpful video thank you
@marbucon
4 жыл бұрын
Hi Arun, 1. I think with a select * from table an normally index will not be used. Only when selecting with a where clause. As databases are cost based (rule based days are over) these days Oracle itself will calculate whether it will need an index. I think if you still want the database using an index even though oracle itself calculates differently then you can use a hint in your query. 2. I think rebuilding an index is useful as it is making queries faster but may I suggest it is good to also sometimes rearrange the data in the table as not only rebuilding the index will speed up a query but also the index clustering (which is one of the criteria for using an index) will be lower which then again lowers the amount of datablocks needed in a query. Maybe Arun can explain about index clustering to those who are not familiar with that.
@dbagenesis
4 жыл бұрын
I agree on both the points mentioned by you!
@soudaiyamohanloganathan8506
3 жыл бұрын
very useful
@dbagenesis
3 жыл бұрын
Keep watching
@AmitKumar-pl4qm
8 ай бұрын
Very nicely and deeply explained..one question when the high water mark is at lower level and the actual data in data block is at higher level, what happens..dies our resource falls short...impact?
@anuragranjan5989
4 жыл бұрын
Index are used when indexed column are used in where Clause ....
@BabitaSingh-rn4ol
10 ай бұрын
Awesome
@bharathkumar-ds8cd
4 жыл бұрын
Good video arun
@harisaim
4 жыл бұрын
When u issue select * from emp: Ans: I think no , as there is no condition clause. What data block header contains ? Scn, data file number,
@mattareddyyarasani5040
4 жыл бұрын
I have a question.regularly I am getting one question from application is that,one of the query is ran fine upto yesterday.but today it is taking more time to fetch records why?.for this what exactly need to check.
@dbagenesis
4 жыл бұрын
Check the data growth in the table. If there is genuinely more data inside the table, it will take time.
@ericmbabid3489
4 жыл бұрын
Run stats on that table for indexes to be updated, and the data growth in the table plays big part too. Make indexes invisible if necessary to test performance on query
@user-fg6mz1rh9b
2 жыл бұрын
I'll try to answer. Hope someone will correct me if I'm wrong. 1)I guess rebuilding index is not necessary if the table is used only for select(which is unlikely); 2)There is data block address and row chaining information stored in data block headers also HWM info; 3)When you issue select * it doesn’t use indexes. You have to use WHERE clause with indexed column to make it happen.
@sukhsaha
3 жыл бұрын
HWM is at segment level, not at individual block level.. at Data Block level it is PCTFREE and PCTUSED only, not HWM.
@amitsahu3743
4 жыл бұрын
Nice explanation sir.. Make some more difficult levels for performance tunning in oracle.
@dbagenesis
4 жыл бұрын
Sure, will be publishing soon
@PawanKumar-jd4ju
4 жыл бұрын
Where i can purchase this book
@shaeakins2670
4 жыл бұрын
You can simply do an explain plan for the select statement and see if its a full table scan or index scan(depends on what column the index is created on)
@santoshmishra-my2gc
4 жыл бұрын
Hi Arun,Doesn't oracle does not shrink the high water mark,because it thinks there will insert again in the block ,so it can avoid this costly operation.Could you please comment if my thought is correct ?
@VineetPundir-humble
4 жыл бұрын
No need to rebuild indexes for report table which is only use for select query purpose..
@Plaguer_agent
2 жыл бұрын
on select * from emp indexes are not use because it is full table scan.
@sonimai123
2 жыл бұрын
datablock header contains the transaction info like locking information on every row that particular data block contains
@suvranshuadhya7680
4 жыл бұрын
Can you please explain 2 term PCT FREE & PCT USED?
Can you explain about oracle top wait events with real time examples?
@dbagenesis
4 жыл бұрын
Sometime in upcoming episodes !!
@VenkyPotla369
6 ай бұрын
How to check waste space in table and how much space it is?
@KL01TVM
Жыл бұрын
2 million count(*) - 0.015 s, deleted 1.5 million - count (*) - 0.15 s , truncated table count(*) - 0.008 s
@sahmed1227
4 жыл бұрын
kindly explain b-tree vs bit map indexes
@dbagenesis
4 жыл бұрын
Picked up for upcoming episodes!
@patrickng6606
3 жыл бұрын
Index will only be used if the where cause reference the index column in certain orders too
@ayoubm7036
3 жыл бұрын
indexes are used when you are accessing a small amount of data , in the exampl we will have a full table scan
@mitusetty8844
4 жыл бұрын
When a query is executed .. in order to optimize the performance of that query indexes are used... in other words it is used to locate the data quickly
@dbagenesis
4 жыл бұрын
Are you sure? for a select * from emp; oracle will use indexes?
@mitusetty8844
4 жыл бұрын
@@dbagenesis hi Arun , for select * from emp ... it wont use index.. rather it performs full tablescan..index will be used if any column has a index specified
@PawanKumar-jd4ju
4 жыл бұрын
DBA Genesis Link for purchase book
@truthhurts9601
4 жыл бұрын
Sir. Respect.
@dbagenesis
4 жыл бұрын
Yes, respect for everyone who is part of our DBA community!
@sinu1241
2 жыл бұрын
Hi Arun, How frequently we should shrink the datafiles ? By looking at which parameter/metrics, we should consider to shrinking the datafile ? We have db in TB size and its not possible to shrink those no of datafiles ? pls advise
@kushsstatus9884
4 жыл бұрын
Hey could you please tell more about space shrinking and block defragment, actually I have answered in an interview 4years before but now it's not in my brain
@bgrsahoo27
3 жыл бұрын
Yes
@rafiuadewale5894
2 жыл бұрын
A DBA is a sole administrator that should be held responsible and to oversee any other activities may he be a developer or whatsoever.
@farazmohammed6862
4 жыл бұрын
Data block header contains high water mark level
@dimithegreatest
4 жыл бұрын
Informative. I have a question for you. Suppose my source DB LOGGING is set to false, and when you try to perform a clone using the RMAN from source backup to destination DB. When I try to perform recovery after the clone, it fails due to out of sync SCN. My questions are 1. What effect l would NOLOGGIN have in this scenario? 2. Will setting the parameter you mentioned to ignore SCN help in my case Thanks
@Jay_Sembekar
4 жыл бұрын
Hi sir,i am a beginner and have one question,there was 2node rac test database and i took down the 1st node then By mistake i took down the crs with VIP and the both nodes goes down,and then i was not able to get session of VIP,which node i took down was successfully up but the 2nd node is still down and there is no any d.bin services are running, how can i take the both nodes up?
@dbagenesis
4 жыл бұрын
How about a quick reboot? Oracle restart service will auto bring up both the nodes DBs.
@ayushtiwari5870
4 жыл бұрын
i created Indexes on table but it created in system tablespace so would it make impact on performance.
@dbagenesis
4 жыл бұрын
Not really
@mohdsaquib1783
3 жыл бұрын
in what conditions index is crashed in reporting tables and needs to be rebuilt even no delete is performed
@navoneelbhattacharyya1957
4 жыл бұрын
Hi Arun, First of all let me tell u , you are doing an amazing job. I have one doubt regarding SQL query tuning, how and when we should modify a query by "with clause" and use /*+materialize*/ hint. please answer this.
@arifansari42
4 жыл бұрын
Hi Arun, What is split brain and symptom of split brain in RAC database???
@dbagenesis
4 жыл бұрын
Picked it up for upcoming episodes
@arifansari42
4 жыл бұрын
Thanks Arun 🙏
@VineetPundir-humble
4 жыл бұрын
Where and how can I learn performance tuning??
@dbagenesis
4 жыл бұрын
Very soon, we are about to launch PT training in the second quarter of 2020
@patrickng6606
3 жыл бұрын
Rebuilding indexes is for benefit of index scanning
@ugfitness2049
2 жыл бұрын
how do i reset high water mark?
@saipavan87
4 жыл бұрын
Regarding your question on "rebuilding indexes on a table which is dedicated for SELECT cmds." --- Yes, we can because index rebuild fasts up SELECT related query performance. There will be a significant improvement in performance wise.
@dbagenesis
4 жыл бұрын
Nope, indexes on reporting table need not be rebuild. Remember, index rebuild must be done when there are deletes inside the table. If a table is just reporting table they you are wasting system resource in rebuilding the index!
@sandeepreddy8282
4 жыл бұрын
Does gathering stale index stats and rebuilding indexes are same?
@dbagenesis
4 жыл бұрын
What do you think?
@sandeepreddy8282
4 жыл бұрын
Both are same.
@anandkumarangaiyan8561
4 жыл бұрын
Hi Arun. My answer for select * from emp; No, it will not take an index scam to fetch a data...
@dbagenesis
3 жыл бұрын
Index are used when you include the indexed column in where clause.
@hailelove1
3 жыл бұрын
Select * from tables not using indexes
@matheenahamed2692
4 жыл бұрын
How to handle the environment where you're only one DBA in that environment?
@dbagenesis
4 жыл бұрын
So whats your specific question? Is your job demanding you to stay more hours? Or your team does not have proper KT? Or you don't know what to do? Any of the above, speak to your manager or boss whom you report to. If you still the only one, the speak to the client whose db you managing.
Пікірлер: 102