1 / 20

Tyson Foods Uses Oracle Index Key Compression under SAP Applications September 19, 2010

minjonet
Download Presentation

Tyson Foods Uses Oracle Index Key Compression under SAP Applications September 19, 2010

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    1. Tyson Foods Uses Oracle Index Key Compression under SAP Applications September 19, 2010

    2. Presenter Background Wolfgang Boedeker Database Administrator: 23 years Oracle: 13 years SAP: 5 years Wolfgang.Boedeker@tyson.com

    3. Tyson Foods Company Profile Founded in 1935 with headquarters in Springdale, Arkansas One of the worlds largest processors and marketers of protein Second-largest food production company listed in the Fortune 500 and is a member of the S&P 500 Provides products and service to customers throughout the United States and more than 90 countries 117,000 Team Members at over 400 facilities and offices around the world

    4. Tyson Foods SAP Profile Using SAP products since 1998 SAP Products: ERP 6.0 Enhancement Pack 3, Netweaver 7, Enhancement Pack 1, SAP EHP 1 for SAP Solution Manager 7.0 SAP Modules: FI/CO, HR, Portal, MM, PP/PI, CRM, SRM, BW, MDM, GTS 5000 users

    5. Tyson Foods - SAP Landscape Virtualized IBM p-Series POWER6 servers 8Gbps SAN using IBM 8700/8300 disk 5 SAP App Servers SAP Central Instance

    6. Tyson Foods SAP ERP Considerations in 2008 Prior to 2008, we implemented table/index partitioning on our larger tables within ERP to help in reduction of physical I/O Growth Rate ERP sized at 4.7TB with 4 years worth of data Growth rate: 110GB/month SAP archiving was not implemented Projected size within 2 years of 7.4TB I/O Rate Oracle Table/Index Partitioning implemented on tables exceeding 20GB

    7. SAP-ORACLE Aha moment! Attended Oracle Open World 2008 Mr. Jan Klokkers (Senior Director, Oracle for SAP Development, Oracle Corporation) presentation on SAP supporting Index Key Compression within ERP. Disk space savings, physical I/O reduction, and logical I/O savings.

    8. Concepts What is Index Key Compression? Uncompressed Compressed Location1 Job1 Bill Location1 Job1 Sue Location1 Job1 Ralph Location1 Job1 Mary Location1 Job1 Dawn Location1 Job1 Don Location1 Job2 Neil Location1 Job2 Mark Location1 Job2 Earnie Location2 Job1 Ann Location2 Job1 Fred Location2 Job1 Betty Location2 Job1 Paul L1J1 Bill L1J1 Sue L1J1 Ralph L1J1 Mary L1J1 Dawn L1J1 Don L1J2 Neil L1J2 Mark L1J2 Earnie L2J1 Ann L2J1 Fred L2J1 Betty L2J1 Paul

    9. Concepts B-Tree Index Key Compression Pros / Cons Additional index keys are stored within an index block. Gain in performance by reduction in physical disk I/O and increase in logical buffer cache I/O. Increase in CPU usage. The performance gain of I/O far out weighs the increase of CPU usage.

    10. Things to think about for Index Key Compression Focus on largest ERP tables? Determine the number of leading columns to compress within an Index? The number of columns within an index? Implementation strategy? Implementation?

    11. Things to think about for Index Key Compression

    12. Things to think about for Index Key Compression Number of compressed columns within an index Index GLPCA~1 (KOKRS, RYEAR, RPRCTR, RVERS, RACCT) KOKRS: 4 RYEAR: 7 RPRCTR: 2,436 RVERS: 1 RACCT: 2,638 Compression of 5 columns=Prefix length: 5 Index GLPCA~0 ( RCLNT, GL_SIRID) RCLNT: 1 GL_SIRID: 650,349,333 Compression of 1 column=Prefix length: 1 650,349,333 rows within GLPCA.650,349,333 rows within GLPCA.

    13. Things to think about for Index Key Compression

    14. Things to think about for Index Key Compression

    15. Things to think about for Index Key Compression Implementation ALTER INDEX "SAPSR3"."GLPCA~0" ON "SAPSR3"."GLPCA" ("RCLNT", "GL_SIRID") REBUILD TABLESPACE "PSAPSR3 NOLOGGING COMPRESS 1 PCTFREE 1 ONLINE / ALTER INDEX "SAPSR3"."GLPCA~1" ON "SAPSR3"."GLPCA" ("KOKRS", "RYEAR", "RPRCTR, "RVERS", "RACCT") REBUILD TABLESPACE "PSAPSR3 NOLOGGING COMPRESS 5 PCTFREE 1 ONLINE / Flip screen to #13Flip screen to #13

    16. SAP ERP: Compression BEFORE / AFTER BEFORE: March 2009 AFTER: September: 2009

    17. IMPROVEMENTS / $AVING$ Database disk savings: Total: 1.5TB - 32% savings Dollar savings assuming 1TB of disk costs $15,000 $112,500 (Prod, QA, Dev, Online Backups, DR Backups) Backups: Reduction in backup/recovery time and media Noticeable performance improvement by our customers

    18. SAP Compression Utilities SAP IND_COMP (SAP Note: 1109743) IX_COMP_OFFLINE_TYPICAL.txt Partitioned tables IX_COMP_ONLINE_TYPICAL.txt Non-partitioned tables SAP BR-Tools version 7.1

    19. Visit the IOUG Booth This Week Located in the User Group Pavilion - Moscone West, 2nd Floor Learn why over 23,000 have joined IOUG and what it can do for you Chat with the IOUG Board of Directors Hear about new regional IOUG BI user communities Find out how to submit an abstract for COLLABORATE 11 IOUG Forum Enter for a chance to win a COLLABORATE 11 registration

    20. Q & A

    21. How to check for Index Column Cardinality select table_name, column_name, num_distinct from dba_tab_columns where table_Name=GLPCA and column_name in (select distinct column_name from dba_ind_columns where table_name=GLPCA' ) order by table_name, column_name /

More Related