Explanation of performance improvement
I am currently performing a database health check report for one of our larger client and noticed a substantial improvement in one of the response time metrics. This is excellent news, however I now need to pinpoint the reason for the improvement. Let me set the scene. The database in question has approximately 2000 concurrent sessions. The application runs on different servers to the database. Over the past 6 months we worked tirelessly to trim down the response times from the app servers to the database. We achieved some good results through a number of changes, the biggest gain coming via identifying a problem with one of the network interface cards on the DB server. At the end of the tuning process the response time for our metric was an average of about 11 seconds, down from about 26 seconds. I noticed that a couple of weeks ago, the response time on the monitor dropped again from 11 seconds to about 3 seconds! This is excellent news, but why. I have looked through the history of changes made in the environments (that we know about anyway - we don't look after the network) and there is one significant piece of work that occurred about the same time as the improvements. Basically, the largest table in the database reached its maximum size so we fragmented this table across three new dbspaces. All other tables are in a single dbspace (not ideal I know, we are working to change this). I was going to explain to the client that the performance improvement was due to the moving of this table. However, what I don't understand is that the query used by the performance metric does not utilize the large, now fragmented table. So, finally to my question... Could the moving and fragmenting of this large table (about 1/3 of the overall database size) account for such a massive response time improvement on a query that does not access the fragmented table? Thanks in advance for any insight or opinions. Regards, Paul
My initial thoughts would be around user authentication and possibly user connectivity. DNS? From: "PAUL RIDDING" <pridding@tt.com.au> To: ids@iiug.org Date: 11/07/2010 09:08 PM Subject: Explanation of performance improvement [21892] Sent by: ids-bounces@iiug.org I am currently performing a database health check report for one of our larger client and noticed a substantial improvement in one of the response time metrics. This is excellent news, however I now need to pinpoint the reason for the improvement. Let me set the scene. The database in question has approximately 2000 concurrent sessions. The application runs on different servers to the database. Over the past 6 months we worked tirelessly to trim down the response times from the app servers to the database. We achieved some good results through a number of changes, the biggest gain coming via identifying a problem with one of the network interface cards on the DB server. At the end of the tuning process the response time for our metric was an average of about 11 seconds, down from about 26 seconds. I noticed that a couple of weeks ago, the response time on the monitor dropped again from 11 seconds to about 3 seconds! This is excellent news, but why. I have looked through the history of changes made in the environments (that we know about anyway - we don't look after the network) and there is one significant piece of work that occurred about the same time as the improvements. Basically, the largest table in the database reached its maximum size so we fragmented this table across three new dbspaces. All other tables are in a single dbspace (not ideal I know, we are working to change this). I was going to explain to the client that the performance improvement was due to the moving of this table. However, what I don't understand is that the query used by the performance metric does not utilize the large, now fragmented table. So, finally to my question... Could the moving and fragmenting of this large table (about 1/3 of the overall database size) account for such a massive response time improvement on a query that does not access the fragmented table? Thanks in advance for any insight or opinions. Regards, Paul ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Perhaps your performance query was being slowed by other users hitting that large table in the same dbspace/chunks and getting read thread contention against that disk(s). Bob ----- Original Message ----- From: "PAUL RIDDING" <pridding@tt.com.au> To: ids@iiug.org Sent: Sunday, November 7, 2010 6:08:03 PM Subject: Explanation of performance improvement [21892] I am currently performing a database health check report for one of our larger client and noticed a substantial improvement in one of the response time metrics. This is excellent news, however I now need to pinpoint the reason for the improvement. Let me set the scene. The database in question has approximately 2000 concurrent sessions. The application runs on different servers to the database. Over the past 6 months we worked tirelessly to trim down the response times from the app servers to the database. We achieved some good results through a number of changes, the biggest gain coming via identifying a problem with one of the network interface cards on the DB server. At the end of the tuning process the response time for our metric was an average of about 11 seconds, down from about 26 seconds. I noticed that a couple of weeks ago, the response time on the monitor dropped again from 11 seconds to about 3 seconds! This is excellent news, but why. I have looked through the history of changes made in the environments (that we know about anyway - we don't look after the network) and there is one significant piece of work that occurred about the same time as the improvements. Basically, the largest table in the database reached its maximum size so we fragmented this table across three new dbspaces. All other tables are in a single dbspace (not ideal I know, we are working to change this). I was going to explain to the client that the performance improvement was due to the moving of this table. However, what I don't understand is that the query used by the performance metric does not utilize the large, now fragmented table. So, finally to my question... Could the moving and fragmenting of this large table (about 1/3 of the overall database size) account for such a massive response time improvement on a query that does not access the fragmented table? Thanks in advance for any insight or opinions. Regards, Paul ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Paul Ridding Wrote: ============================================================================= ...snip... So, finally to my question... Could the moving and fragmenting of this large table (about 1/3 of the overall database size) account for such a massive response time improvement on a query that does not access the fragmented table? Thanks in advance for any insight or opinions. Regards, Paul ============================================================================= Response: Did the page size for the large table change when it was fragmented? If the large table was moved to a different bufferpool, the reduced turnover on the existing bufferpool could explain it.