Hello everyone our BW query response time has been gradually slowing down now that we are getting up to 5 years of data. Our BW administrator is looking at implementing partitioning on a few of the large cubes to improve performance. As you already know SAP BW already uses database partitioning on many standard tables. We have not enabled partitioning on our large custom cubes. SAP documentation suggests this is a straightforward change and itu2019s configured inside SAP BW workbench and not in Oracle.
When the BW admin implements partitioning on the standard tables will there be any Oracle performance issues, I should be aware of?
Do I need to do any specific tuning to accommodate these changes?
We are currently running Oracle 188.8.131.52 but are going to be upgrading to 10.2.4 in the New Year.
Any information would be highly appreciated
Thanks for your time and have a great weekend.
There is simply only one recommendation for a big datawarehouse running on ORACLE:
GO FOR IT!
It's the divide and conquer approach that makes your warehouse fly...
(besides improving READ performance on queries due to partition pruning, writes can speed up drastically
if you think of DELETES going into Undo space vs. DROPs or TRUNCATEs of partitions)
SAP BW handles a lot behind the scenes over RSA1. Whats important is to choose the appropriate partitioning criteria
for compressed cubes (for uncompressed cubes it's fixed on REQUESTID).
You have to choose either 0FISCPER or 0CALMONTH wich is defined by the usage of your most-used big queries (i.e. weekly , monthly, quarterly) and calculate the number of necessary partitions by giving a time period from / to.
Make sure you get a good rows per partition ratio by keeping record numbers big in each partition and partition numbers small by giving a maximal partition number
max. partition number = 96 (add 1 for MAXVALUE partition)
from 01.2000 to 12.2007 = 8 * 12 + 1 = 97 partitions (for each month 1 partition)
max. partition number = 24 (add 1 for MAXVALUE partition)
from 01.2000 to 12.2007 = 8 * 3 + 1 = 25 partitions (for each quarter 1 partition)