les_15-性能调整小结 ( 24 ).pptVIP

Performance Tuning: Summary Objectives After completing this lesson, you should be able to do the following: List best practices identified throughout the course Summarize the performance tuning methodology Necessary Initialization Parameters with Little Performance Impact Important Initialization Parameters with Performance Impact Notes only page Sizing Memory Initially As an initial guess for memory allocation: Leave 20% of available memory to other applications. Leave 80% of memory to the Oracle instance. For OLTP: For DSS: Database High Availability: Best Practices Use SPFILE. Multiplex redo logs. Use resumable space allocation. Create at least two control files. Enable Flashback Database. Undo Tablespace: Best Practices Use Automatic Undo Management. UNDO_RETENTION: Oracle Database 10g Release 1: Set it to your flashback requirement. Oracle Database 10g Release 2: Do not set it. Undo tablespace size: Initial size: Small with AUTOEXTEND enabled Steady state: Fix size using the Undo Advisor and add a 20% safe margin. Notes only page Temporary Tablespace: Best Practices Locally managed temporary tablespaces use a uniform extent. Extent size should be: 1 MB to 10 MB extent size: For DSS, OLAP applications involving huge work areas Large temporary LOBs are predominant. 64 KB or multiple less than 1 MB: Small global temporary tables are predominant. OLTP Temporary tablespace group increases addressability from TB to PB. Notes only page General Tablespace: Best Practices Use locally managed tablespaces with auto-allocate extents policy. Use Automatic Segment Space Management (ASSM). Use online segment shrink to eliminate internal fragmentation. Periodically review the results of the Automatic Segment Advisor. Monitor tablespace space usage using server-generated alerts. Size of extents matter more than the number of extents in the segments. Internal Fragmentation Considerations Watch for: Bad choices of PCTFREE and PCTUSED for heap segments Bad choices of PCTVERSI