Thoughts on RDBMS tuning!
Recently I worked with a few consultants from one of the largest software vendors. The objective was to evaluate the performance of their new product(Let us call it Target System). We decided to migrate R/3 QA system from current system(Source) to target system. We documented our test cases to compare the performance of our source system with the target system. They reviewed ST04 and evaluated the most expensive statements from the cache based on time elapsed. After spending a few hours, they recommended new indexes to help speed up the performance.
My thoughts are not in line with their recommendations. And unfortunately we didn’t have time to discuss their recommendations in detail. So I decided to document my thoughts in this blog.
- Note: While documenting my thoughts, I watched Thiru Swamy’s presenation at SIT-NYC. Details here. Even though the presentation discusses DB2 tuning, the process he followed applies to Oracle as well. Does anyone disagree?
They reviewed top 4 SQL statements and made 4 recommendations – one new index per table for 4 tables.
I analyzed their first recommendation: Build an additional index on a Z table. Let us call it Z_table.
The statistics on Z_Table is given below:
||DB has been up since
||Executions||Elapsed Time in Micro Seconds
||Elapsed time in Hours
||Time spent per day
|QA||02/29/2012 – 100+ days||1,540||846,869,461||15 minutes approx||~8 seconds per day|
|Production||03/18/2012 – 85+ days||30,278||14,381,103,998||3.9 hours||~169 seconds per day|
I can’t explain why they even considered optimizing the query accessing Z_Table. Even if we completely eliminate running this query, we would save 169 seconds per day in the production system. Is that worth the efforts?
The consultants recommended we create an index on Z_Table. The index to include 3 columns: Column_A(MANDT), Column_B(VARCHAR2(60) and Column_C(VARCHAR2(3)).
I checked the cardinality of columns to be indexed.
1) Column A –> One value(MANDT=500)
2) Column B –> 6 values.
||# of records|
3) Column C –> 2 values
||# of records
The data distribution for all 3 columns combined: 11 values (1 times 6 times 2 = 12; however no records for one possible combination 1+6+2).
||# of records|
If you’re familiar with DB05 output, see below: (The table – 47MB size – is not buffered. What would be the impact of changing the buffer mode to “Buffered”?)
From Thiru’s presentation, the complexity of DB tuning is shown below.
Based on the information I provided,
- Does anyone believe saving 169 seconds per day is worth the tuning efforts?
- What do you think about turning on SAP’s “Buffering”? The answer is probably “It depends”.
- They didn’t evaluate the index’s impact on DML statements.
- Have you ever tuned Oracle DB based on ST04 cache output alone or use other TOCDEs as Thiru showed inhis presentation? SAP’s buffering(TCODE SE13) is another option we could consider to improve the performance.