AWR Report Analysis - How to Read AWR Report? Performance Tuning and Troubleshooting - PT 2
Vložit
- čas přidán 22. 08. 2024
- Hello Friends,
Here with one more topic on.
AWR Report Analysis - How to Read AWR Report? Performance Tuning and Troubleshooting - PT 2:
=============================================================================
Main Steps to Analyze AWR Report
Database Details
Host Configuration
Snap Shot Detail
Load Profile
Instance Efficiency Percentages
Top 5 Timed Foreground Events
Time Model Statistics
Operating System Statistics - Detail
SQL Ordered by Elapsed Time
SQL Ordered by CUP Time
Recommendations before getting an AWR Report.
1. Collect Multiple AWR Reports
2. Stick to Particular Time
3. Split Large AWR Report into Smaller Reports (30 mins)
How to Run?
@?/rdbms/admin/awrrpt.sql;
@?/rdbms/admin/awrrpti.sql
@?/rdbms/admin/awrgrpt.sql ;
@?/rdbms/admin/awrgrpti.sql;
FAQ: How to Use AWR reports to Diagnose Database Performance Issues (Doc ID 1359094.1)
FAQ: Automatic Workload Repository (AWR) Reports (Doc ID 1599440.1)
How To Understand AWR Report / Statspack Report [ID 842884.1]
#AWR #PerformanceTuning #PT #top #vmstat #iostat #free #sar #RAC #exadata #oracle #oracledatabase #oracledba #database #databaseperformanceIssue #databases
Please do follow me and support me on,
Blog: mallik034.blog...
LinkedIn: / mallikarjun-ramadurg-3...
twitter: / mallik034
instagram: / mallik034
CZcams: / @mallik034
Regards,
Mallikarjun Ramadurg
Mobile: +966 543796525
WhatsApp: +91 9880616848
Blog: mallik034.blog...
LinkedIn: / mallikarjun-ramadurg-3...
twitter: / mallik034
Instagram: / mallik034
CZcams: / @mallik034
Thanks a lot for this useful session
Thanks a ton for you efforts!! Perfect explaination...
truly an ace!
Hi Mallikarjun Sir,
This is Nikhil from Belgaum. I'm glad that I came across this video while debugging one of the issue that I've seen in work place. You've created a very good content of many oracle concepts. Great work sir. Keep the good work going.
Between I've some doubts on what's the exact different between DB time and CPU time here.
I see that one of the remote connection process who's loading 5 lakhs of entries for an hours taking more CPU around 20 to 25% on VMWare. Problem is the number of provisioning or inserts are more on hardware server and less on VMWare environment i.e, hardware server has 190 inserts per sec and VMWare is showing 150 inserts per second.
This is what I'm debugging now, system configuration is same for both environment. One thing what I observed on VMWare is one of the oracle remote connection process takes around 22 to 25% CPU most of the time but on hardware the same is very less.
I see that some of the Oracle External procedure call execution time is more on VMWare and less on hardware. How do I take it further for debugging? Can you give me some clue here?
My goal is to achieve at least 170 records insert per second on vmware. It's basic insert statement to the table and in background that table has got on trigger which inserts the data to another table to keep track of the data for auditing.
Thank you very much Sir.
Appreciate your effort on this
It was very nice session and i am still waiting for your further session on PT. I hope you will come with it soon.
Very soon. I will upload other videos too.
Thanks for sharing your knowledge
My pleasure
This is good explanation. where can i find AWR Report Analysis - How to Read AWR Report? Performance Tuning and Troubleshooting - PT 1
So Beautiful and nice
Please come up with wait events - both standalone db and rac gc cr or gc current wait events and help to explain in more details . Thanks , you are making great videos
Sure I will explain in next video
@@Mallik034 please include exadata related wait events as well .. thanks a lot ..
@Girija, I will take one session only for reading RAC AWR report and specifically all RAC related and exadata related information. Stay tuned.
wonderful
Thank you! Cheers!
Very good session, thanks.
Welcome!
Hi sir, Thanks lot for taking good session. I am learning lots of new things from your videos. Can you take take good time and bad time awr report and how to compare it, tune parameters.
Good to know that
Requesting to do 19c installation step by step
Nice session
Thank you 👍
Good explanation
in the Top ten foreground events , what is the threshold value for Wait Avg(ms) ?
Please take more sessions on PT.
Sure noted
Rac wait events and how to proactive and reactive tuning also important.
I will take one session on RAC wait event and complete reading RAC AWR report.
Good session.
Can you please take the session analyze the execution plan.
@Uma shankar, Sure It is in list. I will do in sometime next week or next to next week.
I will first cover on ASH and other PT sessions
Hi Mallik, kindly make a video on base lines if possible, Regards
Sure its noted. Its already in my list.
I will upload video soon
Thanks dear
Hi Mallikarjun, Thanks for the session, could you please correct me on below concerns.
1.If CPU idle time is high means, then DB is idle and doing nothing, in that case how we can say its good.
2.If Sql Execution time is half of the DB time means, rest half time is waiting for something, in that case how we can say its a good sign.
3.Logical read means , retrieving data from Disk , right? in this case , if too many logical reads means, its scanning the complete table, in this case how we can say too many logical reads is good sign.
Kindly help me in understanding the above
1.If CPU idle time is high means, then DB is idle and doing nothing, in that case how we can say its good.
>>> If CPU is idle then DB is expected to be idle.
>>> If the DB is Idle and if you see the CPU is not idle then you need to investigate on leakage where your CPU is utilized
>>> If the BD is not idle and CPU is Idle then you need to check on the DB resource and DBRM and IORM other things
2.If Sql Execution time is half of the DB time means, rest half time is waiting for something, in that case how we can say its a good sign.
>>> You can not have 100% on execution time for each and every query because there are lots of contention on resource availability like IO, CPU, memory and Network. If you get 50% of execution time then query is doing good. Most of the scenario you even not get 30% of execution time for each query
3.Logical read means , retrieving data from Disk , right? in this case , if too many logical reads means, its scanning the complete table, in this case how we can say too many logical reads is good sign.
>>> Physical reads mean reading from disk not logical reads.
>>> logical reads means reading data directly from memory(SGA)
.Logical read means , retrieving data from Disk , right? its reading from the buffer so its good .. if more physical reads then its load on the dbfiles to buffer that's also good we cant take any actions immediately for physical reads as well , because its happens as and when any new query's enter into the database.. if its happing on daily basis then we need to consider that parameter ...
when i extracted the AWR report, it shows me execute to parse is 52% and soft parse % is 99.83, how that will be possible in general when softparse id 99% we should excpect execute to parse should always be higher isnt ?
Can you please explain me how to read alert log files.
Thanks.
This is simple.
I will take one session on reading the alert log and some other trace files.
Hi Mallik
Have you created playlist for PT series ? I don’t see one. Thanks for sharing.
Yes I have. I will take brief explanation video
Hello Admin, Please help zoom in your video
Not able to explain... Just reading awrreport
Thanks for you valuable feedback
Poor explanation , just read whatever written in screen and generally available in most of the documents.
You should work on your explanation skills bro. You are just reading.