​Effective Performance Tuning: Picking the Low Hanging Fruit
In this article we shall concentrate on 5 simple and yet very effective techniques of performance tuning:
​
-
Execution Plan Stability
-
Index Usage
-
Top Consumers
-
Missing and Stale Statistics
-
SGA Hit Ratios
The first step in the tuning cycle is to review the key performance indicators for each of the above metrics. For this article we have used the health check function of "VITALS" to report on the key metrics.
​
1. Execution Plan Stability
In the below illustration we can see all SQL operations impacted by plan instability. The list is sorted by elapsed time so we can start work on the worst offenders first.
Illustration 1: VITALS showing SQL OPS affected by plan instability (Sorted by elapsed time)
Whilst some SQL operations may flip-flop from plan to plan it does not necessarily mean a sub-optimal plan is in circulation. We need to review the performance of each plan to determine the optimum execution. In the above illustration SQL ID: 9q7k9nbpvk8pv is shown to have 3 different execution plans. As per the illustration below it is quite clear which is the optimum execution plan.
Illustration 2: Speedway showing optimal execution plan
Now that we have identified the optimum execution plan we must work on ensuring it is used. We can do this is one of two ways. The first method is to review the statistics and ensure they are up to date. Any missing or stale statistics will more often than not produce suboptimal execution plans. However it is common for statistics to become frequently stale throughout normal database operations so plan stability must be enforced through the use of SQL profiles or baselines. The below illustration shows just how easy statistics can be thrown off by data manipulation.
Illustration 3: VITALS showing stale statistics
2. Index Usage
Every form of data manipulation in a table incurs a subsequent index update. This means any index not being used by SQL operations are inducing unnecessary overhead for data manipulation. Consider dropping any index not in use but take into account the below circumstances:
​
-
Whilst the index may not be used it may still be required to enforce referential integrity (Check before dropping).
-
If the index is in an UNUSABLE state then it would not have been used. ENABLE the index and check its usage pattern again in say 30 days.
-
For an extra level of quality assurance consider making the index INVISIBLE before eventually dropping it.
Illustration 4: VITALS listing all indexes not being used
3. Top Consumers
Reducing your databases resource consumption leads to an efficient use of resources and done effectively can reduce the frequency of provisioning licensable components such as CPU.
By identifying resource intensive operations we can plan an approach to resource reduction. The below VITALS health check has identified and sorted resource consumption for various categories as follows:
​
-
Top Physical I/O (By Object)
-
Top Logical I/O (By Query)
-
Top CPU (By Query)
-
Top Physical I/O (By Query)
-
Top Sorts (By Query)
-
Top Elapsed Time (By Query)
Illustration 5: VITALS listing of database top consumers
4. Missing and Stale Statistics
Oracle’s automated scheduled maintenance routine includes functionality for the frequent collection of object statistics. However relying on the maintenance window to complete a statistics collection for all objects in the database may cause problems. Sometimes the maintenance window is too short to collect all the required information leaving you with stale and\or missing statistics.
We can rectify stale and missing statistics by first identifying them as per below:
Illustration 6: VITALS listing of missing and stale statistics
5. SGA Hit Ratios
Hit ratios in the buffer cache give us an idea of our logical to physical I/O ratio. Ideally we want high hit ratios and thus avoid slow I/O activity. Under normal circumstances you should try and achieve the below SGA hit ratios:
​
-
Data Dictionary: The data dictionary cache hit ratio should be > 90% and misses should be < 15%. If not within these thresholds consider increasing the SHARED_POOL_SIZE.
-
Library Cache(Summary): The library cache hit ratio should be > 90% and misses should be < 1%. If not within these thresholds consider increasing the SHARED_POOL_SIZE.
-
Library Cache(Detail): GETHITRATIO and PINHITRATIO should be more than 90%.
-
Shared Pool: %FREE should be > 0.5. REQUEST_FAILURES,REQUEST_MISSES should be 0 or near 0. If not within these thresholds consider increasing SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE.
-
Buffer Cache: The buffer cache hit ratio should be >90%. If not within this threshold consider increasing DB_CACHE_SIZE.
Illustration 7: VITALS listing of SGA hit ratios