Case Study 4:
Drill down on SQL Performance
1.Case Summary
1.1 Customer Profile
Customer Name: Large Australian Financial Institution
System: Oracle E-Business Suite
System Description:
A large Australian financial institution uses Oracle E-Business to manage the following operations:
-
General Ledger
-
Accounts Payable
-
Human Resources\Payroll
​
1.2 Case Study Explanation
There have been reports that the E-Business concurrent manager is experiencing performance issues with the management of batch jobs. The performance issue is related only to the handling of the concurrent requests and the actual batch jobs are not experiencing performance issues. The application team are looking at tuning the concurrent manager on the application server but would also like the database administrator to investigate database performance for the concurrent manager. The application team have provided three SQL ID’s which are known for handling concurrent manager requests (SQL ID’s are: 1nu2x8pgyrwyk, 2mwvn9xwq1tz3 & 2kh1hpp2uu9r4).
​
1.3 Required Actions\Deliverables
-
Conduct the database performance review to determine and investigate performance metrics for the SQL ID’s provided.
-
The scope of the capacity review is just to investigate the performance of the three SQL ID’s provided by the application team.
-
Provide a recommendation to remediate any observed performance issues.
​
​
2.Review the SQL ID’s Provided
​
Step 1. Enter the login credentials to the database
Step 2. Select “SQL Topology” from the SQL pull down menu
Step 3. Review the SQL
-
Click on the “Retrieve SQL Statistics ” button to display all SQL for the last 24 hours
-
Locate the first SQL provided in the list(1nu2x8pgyrwyk)
There appears to be one glaringly obvious performance metrics which is not quite right. The cluster wait time for this query is 21929 seconds. The total elapsed time is 21961 seconds which means the query is waiting purely on cluster activity.
Step 4. Take a Closer look at Cluster Waits
-
Sort the SQL list by Cluster Wait by clicking on the “Cluster Wait” Radiobutton
Here we can see that the three SQL ID’s provided by the application team are all subject to huge cluster wait times. In fact the elapsed time of every query is pretty much 100% cluster wait time.
Step 5. Timeline of Queries Cluster Wait
-
Select “Historical Performance(SQLStat)” from the SQL pull down menu
-
Enter the SQL ID provided by the application team
-
Click the “Retrieve Single SQL Stat” button
-
Click the “Averages” Radiobutton
Here we can see the queries average elapsed time fluctuates quite drastically.
-
Click the “Comms” Radiobutton
As we have already determined the query is consumed by cluster wait. Here we can see the elapsed time series is identical to the cluster wait time series.
Step 6. Concurrent Manager Interface Recommendations
The concurrent manager connects to the database through a service known as DB_CONC. This service is running in an Active\Active state across the cluster. This means queries which are used to co-ordinate the concurrent manager are being distributed across the cluster resulting in excessive block transfer between the nodes in the cluster. The DB_CONC service needs to be configured to an Active\Passive state to isolate the concurrent manager overhead to a single node and thus remediate the excessive cluster wait times.
3.Speedway v's OEM
Various pieces of Speedway functionality used in this case study are not available within standard products such as OEM. The features shown in this document provide the database administrator with further insight into performance issues by providing richer functionality.
Features List:
1. Interactive SQL Topology matrix. Able to sort SQL operations by various metrics such as:
-
SQL ID
-
Execution Count
-
Elapsed Time
-
CPU Time
-
Number of Sorts
-
Rows Processed
-
Disk I/O
-
I/O Wait Time
-
Cluster Wait Time
-
Application Wait time
-
Concurrent Wait Time
2. Historical time series for cluster waits on specific SQL ID’s