advanced compression at conagra foods: fantastic solution
TRANSCRIPT
dd
1
ConAgra Foods Inc
wwwconagrafoodscom
Industry
Food industry
Workforce
26000
Oracle Products
and Services
Oracle Database 11202
for SAP with Advanced
Compression option
Key benefits
bull Cost savings
bull better performance
bull reduced data growth
bull optimized backup
SAP
ERP ECC 60
(with a CI and 6 application
servers) and others
approx 3000 users
Other Infrastructure
Cisco UCS B230 M2
Blade Servers SUSE Linux
Veritas Cluster for HA
Hitachi SAN Disk Array
ldquoBy implementing Oracle Database 11g Advanced Compression we were able to avoid making
an additional unplanned disk purchase extending the life of the disk we had put in place for a
couple more years OAC helped us reduce our databases rate of growth cut our backup times
in half improve DB response times and save us moneyrdquo
ndash R E N E V A N A
Lead Database Administrator ConAgra Foods Inc
One of North Americarsquos leading food makers counters the challenges associated with data growth
with database compression technology from Oracle
A D V A N C E D C O M P R E S S I O N A T C O N A G R A F O O D S
F A N T A S T I C S O L U T I O N ndash I N V E S T M E N T S A V O I D E D
A N D P E R F O R M A N C E I M P R O V E D
ConAgra Foods Inc is one of North Americarsquos At ConAgra Foods SAP ECC is the core system leading food makers Its brands are found in used for our manufacturing business the entire 97 percent of Americarsquos households Consumers process revolves around it So anytime we can buy them in grocery convenience mass decide to make a change to our environment merchandise and club stores The company we run it up through the landscape before sells nearly 13 million packages of food pro- promoting it into production This gives us a ducts every day including 4 million frozen chance to see how it affects the database hosts food packages ConAgra is a Fortune 500 disks and response times as well as from the company with net sales totaling 13+ billion frontend applications US dollars in 2012 and employs more than 26000 people
Alarming data storage growthFor any company trying to foresee the future
At the start of 2012 ConAgra Foods realizedcan be a challenging process Especially when that it was growing faster than what was initishyit comes to accurately determining the size of ally forcasted for a 3 year period in their SAPa database or storage capacities over a period ERP ECC environment ldquoOur ECC databaseof two or three years It becomes more than a was growing at an alarming rate exceedingtrivial matter and in some cases it is simply projected growth rate estimates and jumpingimpossible For everytime that you make a 3 TB in a 9-10 month period Our currentchange to the system it must be throughly storage environment was capped at 10 TB oftested and validated from an infrastructure SSD with 96 TB in use by DBrdquo says Reneperspective an application prespective as well Vana Lead Database Administrator at ConAgra as from a value prespective Foods
2 Advanced Compression at ConAgra Foods
How could they overcome this challenging trend of increasing data with a limited amount of resources One solution of course would be to order an additional 10TB of new SSDs and let the database continue to grow But this approach wasnrsquot even considered as Vana exshyplains ldquothere was no plan in the current budget to buy any additional disk until the next fiscal yearrdquo
Starting with a workable action plan
Given the situation ConAgra sought and found an alternative implement Oraclersquos 11g Advanshyced Compression for SAP Oracle Advanced Compression is a beneficial and innovative way of overcoming data growth challenges while meeting the demand for more space For exshyample it allows IT managers to effectively handle or manage larger volumes of data with limited IT budgets
ConAgra Foods has long been a customer of Oracle using the Oracle database as a safe stable and highly effective pillar of its infrashystructure both for its SAP and non-SAP enshyvironments Over the last 3 years the total number of users in the ECC system has grown to over 3000 increasing the load and demand for quicker response times In a period of roughly 4 months between September 2011 and January 2012 the database grew by more than 14 TB This above average increase in database growth was due to an unexpected change in business direction that could not have been foreseen by the IT department More specifically the business and marketing goals required more data to be kept online and accessible in real time
As ConAgra Foods DBA Manager Jenny Powers explains ldquowe had to take rapid and effective action to be on the safe side We were running the risk of exceeding our storage capashycity limit for ECC use of 10 TBrdquo Alternative ways of solving the problem were then quickly investigated and discussed A direction and a plan needed to be devised to bring the datashybase growth problem under control The focal point of the plan should be to significantly decreased volume of data while reducing the
current database foot print and spending a minimal amount of capital in the process This can be accomplished by implementing Oracle Advanced Compression
First ConAgra Foods decided to request an additional 5 TB of SSD swaping out the old 10TB of SSD for 15TB of new SSD to relieve growth limitation in the short term and to provide space to reorganize and compress data Secondly start a pilot test to see how much they could actually benefit from implementing compression on an older 6TB copy of the SAP ECC database This would help set expectashytions and develop the processes and procedures to solve the problem within the newly added additional 5 TB of disk Thirdly by implemenshyting Advanced Compression ConAgra could further reduce the need to buy any additional disk and extend the life the disk they already had in place
Pilot Test Results
Total Database Size before and after compression
For the pilot test we used BRSPACE (version 72) tables and indexes were compressed in accordance to SAP Notes (1431296 1464156 646681 541538 46272 771191) In addition we broke out largest table space (9 TB) into smaller sets of Data and Index tablespaces (Data Classes win SAP) taking into account the table names roles and functionality This would make it easier to reshyorganize the next time around reducing the need for such large chunks of free space All the lower landscapes were reorgrsquod and compressed
3
first before moving into production giving us the opportunity to evaluate the impact on the application and environment Some of the key things we watched for included dead locks DB latch contention increases Disk IO and CPU load
Tests confirmed high expectations
We went into our OAC pilot test with two main objectives reduce the current database size by at least 40 and find a way to reduce the rapid database growth rates Both of these objectives were achieved and exceeded based on our initial estimates ldquoThe ECC database size and disk space usage was reduced by more than 60 percent Our initial expectations were
exceeded we were very impressedrdquo reported both DBA Lead Vana and DBA Manager Powers Based on their findings and results Oracle Advanced Compression was then imshyplemented in the SAP ECC environment all the way up through production
SQL Performance Metrics
Sample Query used to Validate IO affects of Advanced Compression SELECT FROM SAPR3ZSD_ORDER WHERE MANDT=100 AND ZZCSR between 0000060019 and 0000060030 AND VKORG=1000 AND VTWEG=10 AND VBTYP=C AND ZZERDAT_O gt 20110720
Disk reads buffer gets time waited IO wait time all showed decreases
Table and Indexes before and after Compression used in query
4 Advanced Compression at ConAgra Foods
Other DB Performance Metrics observed
From a SAP Metric perspective we saw the ldquoDB Timerdquo wait go from 56 down to around 38 We attributed this change to a combination of the new storage array consistshying of the 15TB of SSD and compression Where the disk change out got us a 10 boost and the Oracle compression an additional 8-10 of the ldquoDB Timerdquo
Our ldquoAverage Response Time per Dialog Steprdquo dropped from 500ms to 435ms after just imshyplementing compression
Our backup times went from 562hrs for a full backup using rman across 8 channels down to around 2hrs after releasing the free space (green bar dropping in chart below) back to the OS
After implementing Advanced Compression
in Production
Hardware and Software profile
ndash Hitachi SAN disk Array bull 15TB of SSDs mounted as oracleR3P
ndash Cisco UCS B230 M2 blade servers bull Number of CPUs (20) - Intel(R) Xeon(R)
CPU E7- 2870 240GHz bull SUSE Linux Enterprise Server 11 (x86_64)
263212 07 (64-bit)
By having the DBA team ReOrg in chunks of 300GB to 1000GB of data every weekend and starting in July We were able to complete the project by DST weekend only taking off a couple weekends to attend Oracle Open World The DBA team was able to reclaim over 5TB of disk space previously held by the database and watch the Database drop from 96TB in size down to 42TB leaving us with about 10TB of free disk space for future growth over the coming years
bull RAM 128GB bull 64G of HugePages Reserved for Oracle bull 10G E connectivity
ndash Veritas Cluster for HA (4 node Cluster) ndash Veritas NetBackup for Oracle - Release 65 ndash Oracle 11202 PSU 4
bull 48G SGA (42G DB_Cache 4G Shared Pool)
bull 8K block size bull 93TB Database
ndash SAP ECC 60 with a CI and 6 App Servers bull SAP Components (sd mm pm pp qm
ps wm)
5
Advanced Compression at ConAgra Foods
Based on the results we have seen in our ECC environment we are on in the process of im-
Overall satisfaction with the results
ConAgra Foods is very satisfied with the plementing Oracle Advanced Compression inimprovements gained from using Oracle our SAP BW Solution Manager SEM and Advanced Compression ldquoby implementing APO environments Then work our way throughOracle compression technology we have been our non-SAP databases dataware houses andable to avoid making any additional large Peoplesoft environmentsinvestments in storage while being able to
see some additional benefits in system perforshymance and reduced backup timesrdquo reports ConAgra Foods DBA Lead Vana
BRSPACE command examples
NoPrompt ldquo-c forcerdquo ReOrg table ldquoVBOXrdquo and compress indexes Only ldquo-f tbreorgrdquo brspace -u -c force -f tbreorg -s PSAPSR3_OLD -t ldquoVBOXrdquo -n PSAPSR3 -ic ora_proc -p 4 -e 8
Noprompt ldquo-c forcerdquo ReOrg and Compress Tables Indexes and LOBs if possible ldquo-c ctablobindrdquo EXCLude tables in list from table compression ldquo-p reorgEXCLtabrdquo ldquo-p 4rdquo Run 4 table reorg threads ldquo- e 8rdquo Rebuild indexes with a parallelism of 8 each Skip SAP Compressed Tables (ldquo-SCTrdquo) LOB compress ldquo-lc lowrdquo brspace -u -p reorgEXCLtab -c force -f tbreorg -c ctablobind -s PSAPSR3_OLD -t ldquordquo -n PSAPSR3 -ic ora_proc -i PSAPSR3 -p 4 -e 8 ndashSCT
To see temporary dbms_redefinition objs during rebld use select ownersegment_namesegment_typetablespace_namebytes10241024 as MB from dba_segments where (segment_name like $ OR segment_name like MLOG$ OR segment_name like I_SNAP$$ OR segment_name = SNAP$)
In another command window STOP and CLEANUP temporary objects do brspace -c force -f tbreorg -a stop brspace -c force -f tbreorg -a cleanup (OR try) brspace -u -f tbreorg -t ldquordquo -a cleanup or use table name wild card like ldquoVBOXrdquo
Before compression After compression Change
Total database size 6155 TB 2303 TB - 62
Part-I Tblspcs 863 GB 382 GB - 56
Part-II PSAPSR3 5230 GB 1858 GB - 64
Some impressive results from the pilot test of Oracle Database 11g Advanced Compression run at ConAgra Foods (Source ConAgra Foods Inc)
2 Advanced Compression at ConAgra Foods
How could they overcome this challenging trend of increasing data with a limited amount of resources One solution of course would be to order an additional 10TB of new SSDs and let the database continue to grow But this approach wasnrsquot even considered as Vana exshyplains ldquothere was no plan in the current budget to buy any additional disk until the next fiscal yearrdquo
Starting with a workable action plan
Given the situation ConAgra sought and found an alternative implement Oraclersquos 11g Advanshyced Compression for SAP Oracle Advanced Compression is a beneficial and innovative way of overcoming data growth challenges while meeting the demand for more space For exshyample it allows IT managers to effectively handle or manage larger volumes of data with limited IT budgets
ConAgra Foods has long been a customer of Oracle using the Oracle database as a safe stable and highly effective pillar of its infrashystructure both for its SAP and non-SAP enshyvironments Over the last 3 years the total number of users in the ECC system has grown to over 3000 increasing the load and demand for quicker response times In a period of roughly 4 months between September 2011 and January 2012 the database grew by more than 14 TB This above average increase in database growth was due to an unexpected change in business direction that could not have been foreseen by the IT department More specifically the business and marketing goals required more data to be kept online and accessible in real time
As ConAgra Foods DBA Manager Jenny Powers explains ldquowe had to take rapid and effective action to be on the safe side We were running the risk of exceeding our storage capashycity limit for ECC use of 10 TBrdquo Alternative ways of solving the problem were then quickly investigated and discussed A direction and a plan needed to be devised to bring the datashybase growth problem under control The focal point of the plan should be to significantly decreased volume of data while reducing the
current database foot print and spending a minimal amount of capital in the process This can be accomplished by implementing Oracle Advanced Compression
First ConAgra Foods decided to request an additional 5 TB of SSD swaping out the old 10TB of SSD for 15TB of new SSD to relieve growth limitation in the short term and to provide space to reorganize and compress data Secondly start a pilot test to see how much they could actually benefit from implementing compression on an older 6TB copy of the SAP ECC database This would help set expectashytions and develop the processes and procedures to solve the problem within the newly added additional 5 TB of disk Thirdly by implemenshyting Advanced Compression ConAgra could further reduce the need to buy any additional disk and extend the life the disk they already had in place
Pilot Test Results
Total Database Size before and after compression
For the pilot test we used BRSPACE (version 72) tables and indexes were compressed in accordance to SAP Notes (1431296 1464156 646681 541538 46272 771191) In addition we broke out largest table space (9 TB) into smaller sets of Data and Index tablespaces (Data Classes win SAP) taking into account the table names roles and functionality This would make it easier to reshyorganize the next time around reducing the need for such large chunks of free space All the lower landscapes were reorgrsquod and compressed
3
first before moving into production giving us the opportunity to evaluate the impact on the application and environment Some of the key things we watched for included dead locks DB latch contention increases Disk IO and CPU load
Tests confirmed high expectations
We went into our OAC pilot test with two main objectives reduce the current database size by at least 40 and find a way to reduce the rapid database growth rates Both of these objectives were achieved and exceeded based on our initial estimates ldquoThe ECC database size and disk space usage was reduced by more than 60 percent Our initial expectations were
exceeded we were very impressedrdquo reported both DBA Lead Vana and DBA Manager Powers Based on their findings and results Oracle Advanced Compression was then imshyplemented in the SAP ECC environment all the way up through production
SQL Performance Metrics
Sample Query used to Validate IO affects of Advanced Compression SELECT FROM SAPR3ZSD_ORDER WHERE MANDT=100 AND ZZCSR between 0000060019 and 0000060030 AND VKORG=1000 AND VTWEG=10 AND VBTYP=C AND ZZERDAT_O gt 20110720
Disk reads buffer gets time waited IO wait time all showed decreases
Table and Indexes before and after Compression used in query
4 Advanced Compression at ConAgra Foods
Other DB Performance Metrics observed
From a SAP Metric perspective we saw the ldquoDB Timerdquo wait go from 56 down to around 38 We attributed this change to a combination of the new storage array consistshying of the 15TB of SSD and compression Where the disk change out got us a 10 boost and the Oracle compression an additional 8-10 of the ldquoDB Timerdquo
Our ldquoAverage Response Time per Dialog Steprdquo dropped from 500ms to 435ms after just imshyplementing compression
Our backup times went from 562hrs for a full backup using rman across 8 channels down to around 2hrs after releasing the free space (green bar dropping in chart below) back to the OS
After implementing Advanced Compression
in Production
Hardware and Software profile
ndash Hitachi SAN disk Array bull 15TB of SSDs mounted as oracleR3P
ndash Cisco UCS B230 M2 blade servers bull Number of CPUs (20) - Intel(R) Xeon(R)
CPU E7- 2870 240GHz bull SUSE Linux Enterprise Server 11 (x86_64)
263212 07 (64-bit)
By having the DBA team ReOrg in chunks of 300GB to 1000GB of data every weekend and starting in July We were able to complete the project by DST weekend only taking off a couple weekends to attend Oracle Open World The DBA team was able to reclaim over 5TB of disk space previously held by the database and watch the Database drop from 96TB in size down to 42TB leaving us with about 10TB of free disk space for future growth over the coming years
bull RAM 128GB bull 64G of HugePages Reserved for Oracle bull 10G E connectivity
ndash Veritas Cluster for HA (4 node Cluster) ndash Veritas NetBackup for Oracle - Release 65 ndash Oracle 11202 PSU 4
bull 48G SGA (42G DB_Cache 4G Shared Pool)
bull 8K block size bull 93TB Database
ndash SAP ECC 60 with a CI and 6 App Servers bull SAP Components (sd mm pm pp qm
ps wm)
5
Advanced Compression at ConAgra Foods
Based on the results we have seen in our ECC environment we are on in the process of im-
Overall satisfaction with the results
ConAgra Foods is very satisfied with the plementing Oracle Advanced Compression inimprovements gained from using Oracle our SAP BW Solution Manager SEM and Advanced Compression ldquoby implementing APO environments Then work our way throughOracle compression technology we have been our non-SAP databases dataware houses andable to avoid making any additional large Peoplesoft environmentsinvestments in storage while being able to
see some additional benefits in system perforshymance and reduced backup timesrdquo reports ConAgra Foods DBA Lead Vana
BRSPACE command examples
NoPrompt ldquo-c forcerdquo ReOrg table ldquoVBOXrdquo and compress indexes Only ldquo-f tbreorgrdquo brspace -u -c force -f tbreorg -s PSAPSR3_OLD -t ldquoVBOXrdquo -n PSAPSR3 -ic ora_proc -p 4 -e 8
Noprompt ldquo-c forcerdquo ReOrg and Compress Tables Indexes and LOBs if possible ldquo-c ctablobindrdquo EXCLude tables in list from table compression ldquo-p reorgEXCLtabrdquo ldquo-p 4rdquo Run 4 table reorg threads ldquo- e 8rdquo Rebuild indexes with a parallelism of 8 each Skip SAP Compressed Tables (ldquo-SCTrdquo) LOB compress ldquo-lc lowrdquo brspace -u -p reorgEXCLtab -c force -f tbreorg -c ctablobind -s PSAPSR3_OLD -t ldquordquo -n PSAPSR3 -ic ora_proc -i PSAPSR3 -p 4 -e 8 ndashSCT
To see temporary dbms_redefinition objs during rebld use select ownersegment_namesegment_typetablespace_namebytes10241024 as MB from dba_segments where (segment_name like $ OR segment_name like MLOG$ OR segment_name like I_SNAP$$ OR segment_name = SNAP$)
In another command window STOP and CLEANUP temporary objects do brspace -c force -f tbreorg -a stop brspace -c force -f tbreorg -a cleanup (OR try) brspace -u -f tbreorg -t ldquordquo -a cleanup or use table name wild card like ldquoVBOXrdquo
Before compression After compression Change
Total database size 6155 TB 2303 TB - 62
Part-I Tblspcs 863 GB 382 GB - 56
Part-II PSAPSR3 5230 GB 1858 GB - 64
Some impressive results from the pilot test of Oracle Database 11g Advanced Compression run at ConAgra Foods (Source ConAgra Foods Inc)
3
first before moving into production giving us the opportunity to evaluate the impact on the application and environment Some of the key things we watched for included dead locks DB latch contention increases Disk IO and CPU load
Tests confirmed high expectations
We went into our OAC pilot test with two main objectives reduce the current database size by at least 40 and find a way to reduce the rapid database growth rates Both of these objectives were achieved and exceeded based on our initial estimates ldquoThe ECC database size and disk space usage was reduced by more than 60 percent Our initial expectations were
exceeded we were very impressedrdquo reported both DBA Lead Vana and DBA Manager Powers Based on their findings and results Oracle Advanced Compression was then imshyplemented in the SAP ECC environment all the way up through production
SQL Performance Metrics
Sample Query used to Validate IO affects of Advanced Compression SELECT FROM SAPR3ZSD_ORDER WHERE MANDT=100 AND ZZCSR between 0000060019 and 0000060030 AND VKORG=1000 AND VTWEG=10 AND VBTYP=C AND ZZERDAT_O gt 20110720
Disk reads buffer gets time waited IO wait time all showed decreases
Table and Indexes before and after Compression used in query
4 Advanced Compression at ConAgra Foods
Other DB Performance Metrics observed
From a SAP Metric perspective we saw the ldquoDB Timerdquo wait go from 56 down to around 38 We attributed this change to a combination of the new storage array consistshying of the 15TB of SSD and compression Where the disk change out got us a 10 boost and the Oracle compression an additional 8-10 of the ldquoDB Timerdquo
Our ldquoAverage Response Time per Dialog Steprdquo dropped from 500ms to 435ms after just imshyplementing compression
Our backup times went from 562hrs for a full backup using rman across 8 channels down to around 2hrs after releasing the free space (green bar dropping in chart below) back to the OS
After implementing Advanced Compression
in Production
Hardware and Software profile
ndash Hitachi SAN disk Array bull 15TB of SSDs mounted as oracleR3P
ndash Cisco UCS B230 M2 blade servers bull Number of CPUs (20) - Intel(R) Xeon(R)
CPU E7- 2870 240GHz bull SUSE Linux Enterprise Server 11 (x86_64)
263212 07 (64-bit)
By having the DBA team ReOrg in chunks of 300GB to 1000GB of data every weekend and starting in July We were able to complete the project by DST weekend only taking off a couple weekends to attend Oracle Open World The DBA team was able to reclaim over 5TB of disk space previously held by the database and watch the Database drop from 96TB in size down to 42TB leaving us with about 10TB of free disk space for future growth over the coming years
bull RAM 128GB bull 64G of HugePages Reserved for Oracle bull 10G E connectivity
ndash Veritas Cluster for HA (4 node Cluster) ndash Veritas NetBackup for Oracle - Release 65 ndash Oracle 11202 PSU 4
bull 48G SGA (42G DB_Cache 4G Shared Pool)
bull 8K block size bull 93TB Database
ndash SAP ECC 60 with a CI and 6 App Servers bull SAP Components (sd mm pm pp qm
ps wm)
5
Advanced Compression at ConAgra Foods
Based on the results we have seen in our ECC environment we are on in the process of im-
Overall satisfaction with the results
ConAgra Foods is very satisfied with the plementing Oracle Advanced Compression inimprovements gained from using Oracle our SAP BW Solution Manager SEM and Advanced Compression ldquoby implementing APO environments Then work our way throughOracle compression technology we have been our non-SAP databases dataware houses andable to avoid making any additional large Peoplesoft environmentsinvestments in storage while being able to
see some additional benefits in system perforshymance and reduced backup timesrdquo reports ConAgra Foods DBA Lead Vana
BRSPACE command examples
NoPrompt ldquo-c forcerdquo ReOrg table ldquoVBOXrdquo and compress indexes Only ldquo-f tbreorgrdquo brspace -u -c force -f tbreorg -s PSAPSR3_OLD -t ldquoVBOXrdquo -n PSAPSR3 -ic ora_proc -p 4 -e 8
Noprompt ldquo-c forcerdquo ReOrg and Compress Tables Indexes and LOBs if possible ldquo-c ctablobindrdquo EXCLude tables in list from table compression ldquo-p reorgEXCLtabrdquo ldquo-p 4rdquo Run 4 table reorg threads ldquo- e 8rdquo Rebuild indexes with a parallelism of 8 each Skip SAP Compressed Tables (ldquo-SCTrdquo) LOB compress ldquo-lc lowrdquo brspace -u -p reorgEXCLtab -c force -f tbreorg -c ctablobind -s PSAPSR3_OLD -t ldquordquo -n PSAPSR3 -ic ora_proc -i PSAPSR3 -p 4 -e 8 ndashSCT
To see temporary dbms_redefinition objs during rebld use select ownersegment_namesegment_typetablespace_namebytes10241024 as MB from dba_segments where (segment_name like $ OR segment_name like MLOG$ OR segment_name like I_SNAP$$ OR segment_name = SNAP$)
In another command window STOP and CLEANUP temporary objects do brspace -c force -f tbreorg -a stop brspace -c force -f tbreorg -a cleanup (OR try) brspace -u -f tbreorg -t ldquordquo -a cleanup or use table name wild card like ldquoVBOXrdquo
Before compression After compression Change
Total database size 6155 TB 2303 TB - 62
Part-I Tblspcs 863 GB 382 GB - 56
Part-II PSAPSR3 5230 GB 1858 GB - 64
Some impressive results from the pilot test of Oracle Database 11g Advanced Compression run at ConAgra Foods (Source ConAgra Foods Inc)
4 Advanced Compression at ConAgra Foods
Other DB Performance Metrics observed
From a SAP Metric perspective we saw the ldquoDB Timerdquo wait go from 56 down to around 38 We attributed this change to a combination of the new storage array consistshying of the 15TB of SSD and compression Where the disk change out got us a 10 boost and the Oracle compression an additional 8-10 of the ldquoDB Timerdquo
Our ldquoAverage Response Time per Dialog Steprdquo dropped from 500ms to 435ms after just imshyplementing compression
Our backup times went from 562hrs for a full backup using rman across 8 channels down to around 2hrs after releasing the free space (green bar dropping in chart below) back to the OS
After implementing Advanced Compression
in Production
Hardware and Software profile
ndash Hitachi SAN disk Array bull 15TB of SSDs mounted as oracleR3P
ndash Cisco UCS B230 M2 blade servers bull Number of CPUs (20) - Intel(R) Xeon(R)
CPU E7- 2870 240GHz bull SUSE Linux Enterprise Server 11 (x86_64)
263212 07 (64-bit)
By having the DBA team ReOrg in chunks of 300GB to 1000GB of data every weekend and starting in July We were able to complete the project by DST weekend only taking off a couple weekends to attend Oracle Open World The DBA team was able to reclaim over 5TB of disk space previously held by the database and watch the Database drop from 96TB in size down to 42TB leaving us with about 10TB of free disk space for future growth over the coming years
bull RAM 128GB bull 64G of HugePages Reserved for Oracle bull 10G E connectivity
ndash Veritas Cluster for HA (4 node Cluster) ndash Veritas NetBackup for Oracle - Release 65 ndash Oracle 11202 PSU 4
bull 48G SGA (42G DB_Cache 4G Shared Pool)
bull 8K block size bull 93TB Database
ndash SAP ECC 60 with a CI and 6 App Servers bull SAP Components (sd mm pm pp qm
ps wm)
5
Advanced Compression at ConAgra Foods
Based on the results we have seen in our ECC environment we are on in the process of im-
Overall satisfaction with the results
ConAgra Foods is very satisfied with the plementing Oracle Advanced Compression inimprovements gained from using Oracle our SAP BW Solution Manager SEM and Advanced Compression ldquoby implementing APO environments Then work our way throughOracle compression technology we have been our non-SAP databases dataware houses andable to avoid making any additional large Peoplesoft environmentsinvestments in storage while being able to
see some additional benefits in system perforshymance and reduced backup timesrdquo reports ConAgra Foods DBA Lead Vana
BRSPACE command examples
NoPrompt ldquo-c forcerdquo ReOrg table ldquoVBOXrdquo and compress indexes Only ldquo-f tbreorgrdquo brspace -u -c force -f tbreorg -s PSAPSR3_OLD -t ldquoVBOXrdquo -n PSAPSR3 -ic ora_proc -p 4 -e 8
Noprompt ldquo-c forcerdquo ReOrg and Compress Tables Indexes and LOBs if possible ldquo-c ctablobindrdquo EXCLude tables in list from table compression ldquo-p reorgEXCLtabrdquo ldquo-p 4rdquo Run 4 table reorg threads ldquo- e 8rdquo Rebuild indexes with a parallelism of 8 each Skip SAP Compressed Tables (ldquo-SCTrdquo) LOB compress ldquo-lc lowrdquo brspace -u -p reorgEXCLtab -c force -f tbreorg -c ctablobind -s PSAPSR3_OLD -t ldquordquo -n PSAPSR3 -ic ora_proc -i PSAPSR3 -p 4 -e 8 ndashSCT
To see temporary dbms_redefinition objs during rebld use select ownersegment_namesegment_typetablespace_namebytes10241024 as MB from dba_segments where (segment_name like $ OR segment_name like MLOG$ OR segment_name like I_SNAP$$ OR segment_name = SNAP$)
In another command window STOP and CLEANUP temporary objects do brspace -c force -f tbreorg -a stop brspace -c force -f tbreorg -a cleanup (OR try) brspace -u -f tbreorg -t ldquordquo -a cleanup or use table name wild card like ldquoVBOXrdquo
Before compression After compression Change
Total database size 6155 TB 2303 TB - 62
Part-I Tblspcs 863 GB 382 GB - 56
Part-II PSAPSR3 5230 GB 1858 GB - 64
Some impressive results from the pilot test of Oracle Database 11g Advanced Compression run at ConAgra Foods (Source ConAgra Foods Inc)
5
Advanced Compression at ConAgra Foods
Based on the results we have seen in our ECC environment we are on in the process of im-
Overall satisfaction with the results
ConAgra Foods is very satisfied with the plementing Oracle Advanced Compression inimprovements gained from using Oracle our SAP BW Solution Manager SEM and Advanced Compression ldquoby implementing APO environments Then work our way throughOracle compression technology we have been our non-SAP databases dataware houses andable to avoid making any additional large Peoplesoft environmentsinvestments in storage while being able to
see some additional benefits in system perforshymance and reduced backup timesrdquo reports ConAgra Foods DBA Lead Vana
BRSPACE command examples
NoPrompt ldquo-c forcerdquo ReOrg table ldquoVBOXrdquo and compress indexes Only ldquo-f tbreorgrdquo brspace -u -c force -f tbreorg -s PSAPSR3_OLD -t ldquoVBOXrdquo -n PSAPSR3 -ic ora_proc -p 4 -e 8
Noprompt ldquo-c forcerdquo ReOrg and Compress Tables Indexes and LOBs if possible ldquo-c ctablobindrdquo EXCLude tables in list from table compression ldquo-p reorgEXCLtabrdquo ldquo-p 4rdquo Run 4 table reorg threads ldquo- e 8rdquo Rebuild indexes with a parallelism of 8 each Skip SAP Compressed Tables (ldquo-SCTrdquo) LOB compress ldquo-lc lowrdquo brspace -u -p reorgEXCLtab -c force -f tbreorg -c ctablobind -s PSAPSR3_OLD -t ldquordquo -n PSAPSR3 -ic ora_proc -i PSAPSR3 -p 4 -e 8 ndashSCT
To see temporary dbms_redefinition objs during rebld use select ownersegment_namesegment_typetablespace_namebytes10241024 as MB from dba_segments where (segment_name like $ OR segment_name like MLOG$ OR segment_name like I_SNAP$$ OR segment_name = SNAP$)
In another command window STOP and CLEANUP temporary objects do brspace -c force -f tbreorg -a stop brspace -c force -f tbreorg -a cleanup (OR try) brspace -u -f tbreorg -t ldquordquo -a cleanup or use table name wild card like ldquoVBOXrdquo
Before compression After compression Change
Total database size 6155 TB 2303 TB - 62
Part-I Tblspcs 863 GB 382 GB - 56
Part-II PSAPSR3 5230 GB 1858 GB - 64
Some impressive results from the pilot test of Oracle Database 11g Advanced Compression run at ConAgra Foods (Source ConAgra Foods Inc)