Hi Friends,
This is the third and final part of this blogpost series, I would strongly recommend to go through the Part-I & Part-II of this series if you have directly landed to this post.
In Part-II, we identified the SQL statements which were the main contributors for USER I/O. In this post we will learn how to reduce the DB_Time considerably. In short, let try to explore the opportunities to reduce the
Where,
DB_Time = CPU Time + Non-Idle Wait time ( If you are new to these matrices, I would recommend you to get quick web trainings from Craig Shallahamer from OraPub )
We will use SQL Tuning Advisor to find out for some advice.
1) Below screenshot is again of the ASH Analytics home screen for my database.
As we can see there are a couple of SQL Statements that cause the major part of the activity. You should see top SQL id.
Select top three SQL's,
Please Note: Ignore below screenshot which has top five selected and in the following screenshots you will find some other SQL-id's, The reason is that my main main is to show you the way you can use OEM to diaognise performance problems. So, it takes lot of time & effort to get these screenshots created arranged and post in self explanatory format.
The number of SQLs to tune is different, because by the time i start creating remaining screenshots, the short spikes had gone out of the ASH window and So will be showing the navigation with any top 3 SQL's available for me from then. d
Click Tune "Schedule SQL Tuning Advisor"
2) Review job detail and for easier identification you can change the name too.
Click Submit
3) The tuning task will run for 5 to 10 minutes. You can see the progress and the cumulative benefits on the screen.
4) As we can see Tuning advisor has given tuning recommendations for us in the form of SQL profiles & indexes that we should investigate further.
Click on Show all results.
5) This constitutes the “Fix” or the solution for the problem identified as part of the Find-Fix-Validate performance methodology.
SQL Tuning Advisor has now given us advice to create SQL Profiles & create indexes. We will now implement the advice that will give us most benefits. But since we are not allowed to jeopardize current performance then we need to validate that our advices are risk free to implement. This will be done with SPA Quick Check.
Note:- The largest benefit comes by creating SQL Profiles. New indexes have almost as high a benefit. But indexes will definitely cause performance penalty during insert and updates and can also cause other SQL to regress.
Let’s start with SQL profiles. Will we be able to gain what SQL Tuning Advisor have predicted?
Click on “Validate All Profiles with SPA”
6) Enterprise Manager has now created a SQL Performance Analyzer Task for validating the performance.
Click on the SPA Task
7) The SPA Task will run for about 1 minute when “Last Run Status” is “completed click on the Name.
8) There are four trials executed, first and second are only comparing execution plans, third and forth are full executions of regressed SQL statements (subset of the workload)
Let’s see the comparison result from the third and fourth trial.
Click on the glasses for the second comparison report.
9) As we can see the performance improvement is not in line with what the SQL Tuning Advisor recommended.
The benefit by SQL Profile is just 4% as compared to 99% Per SQL Tuning Advisor's recommendations,Now we have validated the fix in real time in the same database, So if the Validation was in line with SQL Tuning Advisor's recommendations we can blindly implement the SQL profiles.
As I said in the beginning my aim is to show you the complete navigation and options to use for diagonizing the performance issues and not the actual results because of 2 reasons.
**I am not running this on a prod db,
** and while creating screenshots as I navigate, the ASH analytics window keeps moving, and even the window I select may just have a spike, which is not a performance issue at all.
However, let’s implement the SQL Profiles. (Which I should not do according to my validation result :) )
Click on the breadcrumb for Advisor Central.
10 ) Click on "Advisor Central" breadcrum & In the next screen, Identify your tuning task and click on the name
Click on SQL Profile
11) Click on Implement All SQL Profiles
12) Check “Implement the new profile(s) with forced matching” and click Yes.
13) We have now implemented our new profiles. Let’s see if this had any impact on the workload.
Go to Performance -> ASH Analytics to see if the graph has come down, which means the performance issue was identified-validated & fixed on the fly.
14) Similarly, below screenshots give the navigation of how to go by the Index recommendations.
Hope you have gone through all the three parts of this blogpost, if not please read Part-I & Part-II without fail.
HAPPY LEARNING!
This is the third and final part of this blogpost series, I would strongly recommend to go through the Part-I & Part-II of this series if you have directly landed to this post.
In Part-II, we identified the SQL statements which were the main contributors for USER I/O. In this post we will learn how to reduce the DB_Time considerably. In short, let try to explore the opportunities to reduce the
Where,
DB_Time = CPU Time + Non-Idle Wait time ( If you are new to these matrices, I would recommend you to get quick web trainings from Craig Shallahamer from OraPub )
We will use SQL Tuning Advisor to find out for some advice.
1) Below screenshot is again of the ASH Analytics home screen for my database.
As we can see there are a couple of SQL Statements that cause the major part of the activity. You should see top SQL id.
Select top three SQL's,
Please Note: Ignore below screenshot which has top five selected and in the following screenshots you will find some other SQL-id's, The reason is that my main main is to show you the way you can use OEM to diaognise performance problems. So, it takes lot of time & effort to get these screenshots created arranged and post in self explanatory format.
The number of SQLs to tune is different, because by the time i start creating remaining screenshots, the short spikes had gone out of the ASH window and So will be showing the navigation with any top 3 SQL's available for me from then. d
Click Tune "Schedule SQL Tuning Advisor"
2) Review job detail and for easier identification you can change the name too.
Click Submit
3) The tuning task will run for 5 to 10 minutes. You can see the progress and the cumulative benefits on the screen.
4) As we can see Tuning advisor has given tuning recommendations for us in the form of SQL profiles & indexes that we should investigate further.
Click on Show all results.
5) This constitutes the “Fix” or the solution for the problem identified as part of the Find-Fix-Validate performance methodology.
SQL Tuning Advisor has now given us advice to create SQL Profiles & create indexes. We will now implement the advice that will give us most benefits. But since we are not allowed to jeopardize current performance then we need to validate that our advices are risk free to implement. This will be done with SPA Quick Check.
Note:- The largest benefit comes by creating SQL Profiles. New indexes have almost as high a benefit. But indexes will definitely cause performance penalty during insert and updates and can also cause other SQL to regress.
Let’s start with SQL profiles. Will we be able to gain what SQL Tuning Advisor have predicted?
Click on “Validate All Profiles with SPA”
6) Enterprise Manager has now created a SQL Performance Analyzer Task for validating the performance.
Click on the SPA Task
7) The SPA Task will run for about 1 minute when “Last Run Status” is “completed click on the Name.
8) There are four trials executed, first and second are only comparing execution plans, third and forth are full executions of regressed SQL statements (subset of the workload)
Let’s see the comparison result from the third and fourth trial.
Click on the glasses for the second comparison report.
9) As we can see the performance improvement is not in line with what the SQL Tuning Advisor recommended.
The benefit by SQL Profile is just 4% as compared to 99% Per SQL Tuning Advisor's recommendations,Now we have validated the fix in real time in the same database, So if the Validation was in line with SQL Tuning Advisor's recommendations we can blindly implement the SQL profiles.
As I said in the beginning my aim is to show you the complete navigation and options to use for diagonizing the performance issues and not the actual results because of 2 reasons.
**I am not running this on a prod db,
** and while creating screenshots as I navigate, the ASH analytics window keeps moving, and even the window I select may just have a spike, which is not a performance issue at all.
However, let’s implement the SQL Profiles. (Which I should not do according to my validation result :) )
Click on the breadcrumb for Advisor Central.
10 ) Click on "Advisor Central" breadcrum & In the next screen, Identify your tuning task and click on the name
Click on SQL Profile
11) Click on Implement All SQL Profiles
12) Check “Implement the new profile(s) with forced matching” and click Yes.
13) We have now implemented our new profiles. Let’s see if this had any impact on the workload.
Go to Performance -> ASH Analytics to see if the graph has come down, which means the performance issue was identified-validated & fixed on the fly.
14) Similarly, below screenshots give the navigation of how to go by the Index recommendations.
Hope you have gone through all the three parts of this blogpost, if not please read Part-I & Part-II without fail.
HAPPY LEARNING!
Hi
ReplyDeleteThank you for giving very valuable information on Oracle Enterprise Manager