Recently I read a blog titled "Question to SAP Support "Why is partition pruning not working in my BW on Oracle". Lars as usual has explained the details in easy to understand simple language. Those who're familiar with SAP and database technologies would know that SAP doesn't take full advantage of underlying disk-based RDBMS. This is not something new. However what surprised me was that the bug introduced several years ago while implementing a simple partitioning scheme was not uncovered until recently. Why did it take 9+ years to uncover a simple bug? I wondered. Most probable reason is that SAP customers don't use DB features-even supported by SAP-extensively.
Partitioning Versus non-partitioning:
Let me explain the advantages of partitioning using a simple example. Let us say we've a customer table with 1,000,000 records. When not partitioned, all 1million records would reside in one table. See the diagram below. Top portion shows unpartitioned table whereas bottom portion shows partitioned table with 10 partitions. For the sake of simplicity, let us assume each partition contains the same number of records, 100,000.
The table below shows the range of customer numbers in each partition.
The table below shows the range of customer numbers stored in non-partitioned table.
Let us say, 10 users(U1, U2, U3 etc) try to analyze data stored in customers table. Each one-let us assume- is responsible to analyze 100,000 records in different ranges as shown in table below. If table is not partitioned, each one would read 1,000,000 as indicated in 3rd column. When that table is partitioned, each one would read only 100,000 records due to partition pruning feature.
Bottom Line
The example I used is very simple and I didn't consider other aspects like indexes, different types of partitioning etc. Only purpose of the example was to show:
- How partition pruning works and
- How it helps to reduce the I/O accesses to Storage Area Network(SAN)
Parameters and Patches
In order for databases to perform optimally, keeping the database current with patches and parameter recommendations is critical. In SAP/Oracle world, SAP's recommendations on both parameters/patch level work very well. When this recommendation is not followed-as Lars explains(see below)-, it would lead to severe performance problems in not just BW systems but in any SAP system.
Lars states:
"All in all you've to keep in mind that this really is just a little fancy feature flower on the big wall of system performance.
The *VAST* (!!!) majority of severe performance problems in BW systems are caused by not having implemented the current Oracle patches and the parameter recommendations.And without these points in place, no what so clever feature will make a fast system out of a slow one."
Is partitioning really a little fancy feature? I disagree. I've seen appropriate partitioning strategy solving serious performance issues several times in both BW and non-BW systems. This is not a little fancy feature but an essential feature to manage VLDBs.
In addition, SAP really shouldn't use customers not following SAP's recommendations on parameters/patches as an excuse for not fixing a serious bug such as the one explained in Lars's blog.
Please note partitioning not only reduces i/o but also improves the usage of Oracle memory areas by reading less. This may in some cases improve Buffer Cache hit ratio.
3 Comments
