Hi Friends,
This is the second part of the blogpost series, I would strongly recommend to go through the Part-I of this series if you have directly landed to this post.
In this Part-II, I will be explaining on how to use the ASH Analytics + SQL Performance Analyzer(SPA) together and quickly arrive at the story that can be used to explain your management about the database performance issues.
Not restricting us with only stories, We can further give birth to validated solutions/fix for the performance issue....Yes I said...."VALIDATED SOLUTION/FIX". This will be in Part-III
Now let's see how to quickly identify the performance issues and drill down in few seconds & few navigation. Just follow the screenshots and the orange boxes.
1) You would have noticed the same screenshot was in the tail end of Part-I
Just proving that this is the sequential post ;)
This is the ASH Analytics screen, and I will be concentrating on the highlighted portion of this ASH graph.
Ideally you should be hihglighting on some flat plateaus instead of spikes that lasts for short duration. I am highlighting this, because, while creating the screenshots, I was not patient enough to wait for a actual performance issue to come up with graphs at top. But yes, you can try on your own on the performance issues to see if it really works for you.
2) Click on the "Load Map" and the "Wait Class, Wait Event" on the drop down, You can see during the highlighted period, "db file sequential read" is the top wait event, For the moment let's not look at What is consuming CPU (We don't have CPU issue's) nor we are interested about idle wait events(So ignore the big GREEN box), We are worried only about the non-idle wait events always.
3) Click on the BROWN box, to drill down to the "Modules, Action" that is contributing to the USER I/O and doing db sequential reads.
4) Ignore Other's BOX, Concentrate on the SYSADMIN module click on the box.
Note:- Other's BOX constitutes very badly or zero instrumented modules, So no much details.
5) Drill down to the SYSADMIN module (Look at the filter marked in black box).
6) From the drop down, Select "Wait Class, SQL_ID" to get the SQL_ID's from SYSADMIN mudule that contributed to "db sequential read" wait event.
7) Wow, now you have the WAIT EVENT -> MODULE/ACTION -> SQL_ID relationship chain and all the sql_id's that is contributing to the performance issues, Pick the top SQL_ID's with bigger boxes for further analysis and fix.
8) By the time I reached this screen, You can see my actual highlighted section came to the center.
and notice the top sql_id's , those were indeed in the the LOAD MAP findings we did in previous steps.
This is all about using ASH Analytics + SPA to efficiently find the underlying SQL_IDs that contributed to the performance issue.
Continue reading the upcoming Part-III
HAPPY LEARNING!
This is the second part of the blogpost series, I would strongly recommend to go through the Part-I of this series if you have directly landed to this post.
In this Part-II, I will be explaining on how to use the ASH Analytics + SQL Performance Analyzer(SPA) together and quickly arrive at the story that can be used to explain your management about the database performance issues.
Not restricting us with only stories, We can further give birth to validated solutions/fix for the performance issue....Yes I said...."VALIDATED SOLUTION/FIX". This will be in Part-III
Now let's see how to quickly identify the performance issues and drill down in few seconds & few navigation. Just follow the screenshots and the orange boxes.
1) You would have noticed the same screenshot was in the tail end of Part-I
Just proving that this is the sequential post ;)
This is the ASH Analytics screen, and I will be concentrating on the highlighted portion of this ASH graph.
Ideally you should be hihglighting on some flat plateaus instead of spikes that lasts for short duration. I am highlighting this, because, while creating the screenshots, I was not patient enough to wait for a actual performance issue to come up with graphs at top. But yes, you can try on your own on the performance issues to see if it really works for you.
2) Click on the "Load Map" and the "Wait Class, Wait Event" on the drop down, You can see during the highlighted period, "db file sequential read" is the top wait event, For the moment let's not look at What is consuming CPU (We don't have CPU issue's) nor we are interested about idle wait events(So ignore the big GREEN box), We are worried only about the non-idle wait events always.
3) Click on the BROWN box, to drill down to the "Modules, Action" that is contributing to the USER I/O and doing db sequential reads.
4) Ignore Other's BOX, Concentrate on the SYSADMIN module click on the box.
Note:- Other's BOX constitutes very badly or zero instrumented modules, So no much details.
5) Drill down to the SYSADMIN module (Look at the filter marked in black box).
6) From the drop down, Select "Wait Class, SQL_ID" to get the SQL_ID's from SYSADMIN mudule that contributed to "db sequential read" wait event.
7) Wow, now you have the WAIT EVENT -> MODULE/ACTION -> SQL_ID relationship chain and all the sql_id's that is contributing to the performance issues, Pick the top SQL_ID's with bigger boxes for further analysis and fix.
8) By the time I reached this screen, You can see my actual highlighted section came to the center.
and notice the top sql_id's , those were indeed in the the LOAD MAP findings we did in previous steps.
This is all about using ASH Analytics + SPA to efficiently find the underlying SQL_IDs that contributed to the performance issue.
Continue reading the upcoming Part-III
HAPPY LEARNING!
No comments:
Post a Comment
Thanks for you valuable comments !