Awr - database wide performance issue. Addm - drill down potential sql queries Ash - unlike awr which is for higher duration like snapshot time (60 mins default). Ash can be use for checking performance of 5 min also
What i guess, awr report is useful for troubleshooting issue that existed for around atleast 30-40% of snapshot duration, where you want to check db_time, elapsed time and top 10 foreground event during that time to see the bottle neck, ADDM is AWR advisor only which is captured for every snapshot in database, which recommends for resolution, it basically recommends if any cursor paramter change, SGA tuning is needed, and in last if i want to be very specific about timeframe, like in my 60 min timeframe snapshot during , i want 10:20:30 to 10:25:00 , what was the issue, what were the statement executed, what were the module, what were the sessions connected, i am going to use ASH report..because ASH report divide snapshot during into samples, so my 1 hour snapshot duration is converted into 3600 samples, my 5 min issue, will not be captured in AWR, because that is an average report for 1 hour(or snapshot duration) Arun Sir, would love to hear ur views :)
1)we normal run the awr when there is bottle neck in database performances and resources usage and wait events then we generate ,Even i don't know when to generate the awr exactly 2)Ash is generated because we need to details analyzsis in seconds then we run the ash report which can't be provide by the awr report . 3)ADDM is used recommendation for database performances which read the information from the Awr report.
Hi Arun, For the question : How to find performance bottleneck of a query? Can we use ASH report if the query is currently being executed (also monitoring it from OEM) and take help of TKprof utility if further investigation is required. I am asking this because for some weird reasons client has not given me DB server access and all I have is 13c OEM and SQL developer at my hand.
I killed one inactive session that was locking other query but inactive session state become from inactive to killed and still locking other query. I waited for around 15 hours but still session was in killed state and locking other query. After that I restarted db then only killed session released. I didn't understand y inactive session didn't release after kill.
You just need to do orakill , And the reason for not releasing the lock is , the session rollbacks the trxns so that it will take time to release ( depends on trxns ) till that it will be in killed state.
I have a question regarding moving Asm disks from 12 Cluster to 19c cluster. I mean dismount disks from 12c. Make those line available to 19c cluster servers using udev and add those disk to create same DG on 19c cluster. Is it possible ?
In our application the active session always remain 55 while value of Inactive session vary. Can you please explain why Active session value not increasing?
Awr and addm I use when I need to check the performance of DB ASH report is used to take report for active session history for a period of time Very useful stuff.. thanks a lot.. i really don't know the use of ASH till date as I am a fresher DBA but to comment you I searched and came to know it
Nice explaination arun, Could you explain If I forgot to create temp tablespace while at the time manual db creation, what happen when user establish a session? I noticed that database_properties , it is showing system, can we say that system is working as temp for a while. Is it right or wrong ?
Active and Inactive session explanation was good.
The way our speaker explains, I love it. We want more videos..
Awr - database wide performance issue. Addm - drill down potential sql queries
Ash - unlike awr which is for higher duration like snapshot time (60 mins default). Ash can be use for checking performance of 5 min also
Feeling confident to watch ur video thanks
All the best
Same feeling 👍
I using ADDM, ADR & ASH nearly 2/3 times in a week
What i guess, awr report is useful for troubleshooting issue that existed for around atleast 30-40% of snapshot duration, where you want to check db_time, elapsed time and top 10 foreground event during that time to see the bottle neck, ADDM is AWR advisor only which is captured for every snapshot in database, which recommends for resolution, it basically recommends if any cursor paramter change, SGA tuning is needed, and in last if i want to be very specific about timeframe, like in my 60 min timeframe snapshot during , i want 10:20:30 to 10:25:00 , what was the issue, what were the statement executed, what were the module, what were the sessions connected, i am going to use ASH report..because ASH report divide snapshot during into samples, so my 1 hour snapshot duration is converted into 3600 samples, my 5 min issue, will not be captured in AWR, because that is an average report for 1 hour(or snapshot duration)
Arun Sir, would love to hear ur views :)
You are right!
@@dbagenesis :)
Thankyou bro really detailed information must appreciate👍
The explanation is very good
Thank you! keep watching.
1)we normal run the awr when there is bottle neck in database performances and resources usage and wait events then we generate ,Even i don't know when to generate the awr exactly
2)Ash is generated because we need to details analyzsis in seconds then we run the ash report which can't be provide by the awr report .
3)ADDM is used recommendation for database performances which read the information from the Awr report.
Hi Arjun,
Requesting you to kindly make one video on tkprof utility to understand how to execute and identify the slownesy issue of query.
sir after a long time new video published i am waiting your every video, very benificial
Hi Arun,
I have following your video, its having very informative, thank you for sharing such knowledge👍
It's my pleasure!
DB/instance Performance issue .. like to see the waiting event and also to find who were contributing those events
Hi Arun,
If possible please restart this daily DBA show.
Hi Arun, For the question : How to find performance bottleneck of a query? Can we use ASH report if the query is currently being executed (also monitoring it from OEM) and take help of TKprof utility if further investigation is required. I am asking this because for some weird reasons client has not given me DB server access and all I have is 13c OEM and SQL developer at my hand.
I killed one inactive session that was locking other query but inactive session state become from inactive to killed and still locking other query.
I waited for around 15 hours but still session was in killed state and locking other query.
After that I restarted db then only killed session released.
I didn't understand y inactive session didn't release after kill.
You just need to do orakill ,
And the reason for not releasing the lock is , the session rollbacks the trxns so that it will take time to release ( depends on trxns ) till that it will be in killed state.
I have a question regarding moving Asm disks from 12 Cluster to 19c cluster. I mean dismount disks from 12c. Make those line available to 19c cluster servers using udev and add those disk to create same DG on 19c cluster. Is it possible ?
Can’t wait for the daily cloud show 💃🏻. Also , what cloud providers do you recommend especially between AWS/AZURE?
Depends on requirement.
In our application the active session always remain 55 while value of Inactive session vary. Can you please explain why Active session value not increasing?
Awr and addm I use when I need to check the performance of DB
ASH report is used to take report for active session history for a period of time
Very useful stuff.. thanks a lot.. i really don't know the use of ASH till date as I am a fresher DBA but to comment you I searched and came to know it
Thanks for sharing
I will generate AWR report to know what are session and which query takes more I/o
Nice explaination arun,
Could you explain If I forgot to create temp tablespace while at the time manual db creation, what happen when user establish a session?
I noticed that database_properties , it is showing system, can we say that system is working as temp for a while. Is it right or wrong ?
Hello sir Good evening,
Can you tell me how to check the Cpu and the memory usage of Database server from SQL prompt
I am awaiting ur daily Cloud show
Very soon
In my environment, sometimes I have noticed contention due to inactive select statement. What does it mean?
Is there any way to change schema name in oracle... Please suggest
Update username from user$ view
In other way by using datapump utility you can the change the schema name while importing !!!!
@@abhilokkumar645how