Case Study 2:
Disk I/O and the KEEP pool
1.Case Summary
1.1 Customer Profile
Customer Name: Large Australian Financial Institution
System: Highly Concurrent Trading Platform(Calypso)
System Description:
Calypso FX is an integrated front-to-back office Foreign Exchange & Commodities platform.
​
1.2 Case Study Explanation
The customer has reported that daily online transaction processing for all trading activity is experiencing intermittent performance degradation. Data common to all trading activity is located in a table called BO_MESSAGE so first assumptions are to check the performance of queries on this table.
​
1.3 Required Actions\Deliverables
-
Conduct investigation into queries on the BO_MESSAGE table using the Speedway performance management software.
-
The scope of the performance investigation is limited to activity and performance metrics related to the BO_MESSAGE table. The criteria of information provided should focus only on performance related matters for the BO_MESSAGE table.
-
Provide evidence for the poor query performance and recommend a method to remediate the performance degradation reported by the customer.
​
​
2.Problem Investigation
First of all let’s look at some important information on the BO_MESSAGE table which could impact query performance.
​
Step 1. Enter the login credentials to the database
Step 2. Select “Object” from the Browser pull down menu
Step 3. Review the BO_MESSAGE table statistics
-
Enter BO_MESSAGE into the “Object Name” field
-
Click “Load/Filter” button
-
Click on the “Statistics” tab
Speedway tells us exactly how much data manipulation has occurred on the table since the last statistics collection. The data manipulation statistics look pretty good so there should be no problem with the optimiser picking the correct execution plan.
Step 4. Review the general structure of the table
-
Click on the “Table:Structure” tab
The structure of the table also looks pretty good except for one non-default setting. The table has been setup to keep its blocks in the KEEP pool. Based on the small amount of data manipulation already determined and the fact that blocks should be readily available in the keep pool the customer should not be seeing any bad performance degradation.
Step 5. Keep Pool Analysis
-
Select “Buffer Cache Content” from the Instance pull down menu
By clicking the “Retrieve” button we pull back information about the contents of the buffer cache. We can below that the BO_MESSAGE table only has .19% of blocks in memory. This is highly odd as this is a very active table so we should have a lot more blocks in memory. The fact that the table has been declared to use the KEEP pool also means the majority of blocks should reside in memory.
Step 6. I/O Analysis of the query running on the BO_MESSAGE table
-
Select “Historical Performance(SQLStat)” from the SQL pull down menu
-
Enter the SQL ID of the query running over the BO_MESSAGE table
-
Click the “Retrieve Single SQL Stat” button
Click on the “Averages” and I/O Radiobuttons.
If we have minimal blocks in the KEEP pool then we can only assume that any queries on the BO_MESSAGE table will be subject to performance degradation due to disk activity. Below we can see the I/O profile for one of the top queries on the BO_MESSAGE table. As we can clearly see the average I/O wait times per execution are really impacting the queries performance.
Click on the “General” Radiobutton.
In fact the I/O wait times look to be responsible for the majority of the queries elapsed time.
Step 7. Look at the KEEP Pool Configuration
So far we have determined that the queries performance is being degraded due to I/O wait. However this should not be the case due to the way the table should interact with the SGA(KEEP pool). Let’s tack a look at the KEEP pool configuration.
SQL> show parameters db_keep_cache_size
NAME TYPE VALUE
------------------------------------ -------------------------------- --------
db_keep_cache_size big integer 0
Well the problem is now quite apparent. The KEEP pool has not been setup. Essentially a value of zero disables the KEEP pool. This explains the I/O wait time incurred by queries on the BO_MESSAGE table.
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 adminstrator with further insight into performance issues by providing richer functionality.
Features List:
-
Content of Buffer Cache
-
Table\data manipulation volume since last statistics collection
-
Timeline series of SQL performance metrics )
