1 / 40

The Self-managing Database: Proactive Space and Schema Object Management

Session id: 40170. The Self-managing Database: Proactive Space and Schema Object Management. Amit Ganesh Director, Data, Space and Transaction Processing Oracle Corporation. Agenda. Proactive Space Management Alerts Size estimation and growth trending Segment Advisor and Shrink

riva
Download Presentation

The Self-managing Database: Proactive Space and Schema Object Management

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. Session id: 40170 The Self-managing Database: Proactive Space and Schema Object Management Amit GaneshDirector, Data, Space and Transaction Processing Oracle Corporation

  2. Agenda • Proactive Space Management • Alerts • Size estimation and growth trending • Segment Advisor and Shrink • Proactive Undo Management • Alerts • Auto-sizing • Auto-tuning

  3. Automatic Space Management Oracle9i • No external space fragmentations • Locally Managed Tablespace • No space allocation contention • Automatic Segment Space Management • No Rollback Segments • Automatic Undo Management Application & SQL Management Storage Management System Resource Management Space Management Backup & Recovery Management Database Management Intelligent Infrastructure

  4. Oracle Database 10g– Self-Managing Database Application & SQL Management Storage Management System Resource Management Space Management Backup & Recovery Management Database Management Database Control Intelligent Infrastructure

  5. 10g: Always working for you • Collects continuously • Records periodically to disk • Learns from historical data • Adapts to changing workload • Alerts DBA when problems occur • Advises proactively on problems • Integrated with the Enterprise Manager

  6. Proactive Space Management

  7. Proactive Space Management in 10g $$ Operations Size Estimation Growth Trending Planning Segment Advisor Actions Tablespace Alerts Shrink Add file Re-org/ rebuild Resumable

  8. Object Size Estimation

  9. Tablespace Alerts • Database Generated • Warning, Critical • Out of the box • Tablespaces can be provisioned with more disk space before out-of-space conditions occur

  10. Tablespace Thresholds 97% Critical 85% Warning 97% Critical 85% Warning Locally Managed Tablespace Alert Add files Add Files Resolve Space Problem Free up space Freeing up space

  11. Check every 10 mns In-MemoryCalculation Alert 97% Critical Cleared 85% Warning Cleared Alert MMON Alert Computation • Read only/Offline tablespaces: Do not setup alerts • Temporary tablespace: Threshold corresponds to space currently used by sessions. • Undo tablespace: Threshold corresponds to space used by active and unexpired extents. • AUTOEXTENSIBLE files: Threshold is based on the maximum file size.

  12. Instance1 Instance2 … … 100MB 60MB SCNt1 100MB 60MB SCNt1 File1 File1 … … File2 File2 50MB 10MB SCNt2 50MB 10MB SCNt2 … … Filen Filen File Size Allocated Space Data aggregated through GV$ Change SCN MMON MMON Tablespace Tablespace Size: 150M Allocated: 70M File1 File2 Threshold Alert Tracking

  13. Setting Thresholds Using EM • Changing database default thresholds • Changing a particular tablespace thresholds • Directly in the context of the tablespace • From the Edit Metric Thresholds page

  14. Tablespace Threshold Page

  15. Receiving Alerts in EM

  16. Growth Trend Report • Based on Automatic Workload Repository Data • Indicates past growth trend and predicts future growth pattern

  17. Growth Trend Report

  18. Segment Advisor • Determines whether an object is a good candidate for a shrink operation • Based on unused space that can be released • Considers estimated future space requirements • EM allows you to apply the recommended shrink • Can be invoked at the segment or tablespace level

  19. Reclaimed Space Segment Shrink UnusedSpace UnusedSpace Data HWM Shrink Operation Online and In-place ShrinkOperation HWM

  20. Benefits Online & In-place Faster access ShrunkSegment Better space utilization

  21. Candidate Segments Auto Segment Space ManagedTablespace LOBs Tables Row MovementEnabled IOTs MaterializedViews Indexes

  22. DBA Dependency Maintenance Indexes are maintained Index Shrink Table Shrink Trigger Triggers are not fired

  23. MODIFY PARTITION MODIFY SUBPARTITION MODIFY LOB ALTER TABLE employees ENABLE ROW MOVEMENT; 1 ALTER TABLE employees SHRINK SPACE CASCADE; 2 How Can I Shrink Segments? ALTER … SHRINK SPACE [CASCADE] TABLE INDEX MATERIALIZED VIEW MATERIALIZED VIEW LOG

  24. How Does it Work? ALTER TABLE employees SHRINK SPACE COMPACT; 1 HWM HWM ALTER TABLE employees SHRINK SPACE; 2 HWM

  25. EM Interface

  26. Before Check to see which objects in the tablespace have pockets of wasted space due to deletion: Create a script that looks at DBA_TABLES view to compare the total space allocated for each object (BLOCKS * DB_BLOCK_SIZE) in a tablespace to the estimated space used by the object (AVG_ROW_LEN * NUM_ROWS) (assumes objects have been analyzed) Review script output and identify target objects for reorganization 3. Identify/Create “scratch” tablespace 4. For each object to be reorganized, use the Enterprise Manager Reorg wizard to recreate each object along with its dependencies Oracle10G Launch Segment Advisor to advise on which object(s) to shrink Accept the recommendations to shrink the objects online and in-place Space Management : Before and Now Scenario: Reclaim Wasted Space

  27. Proactive Undo Management

  28. Oracle Database 10g • Auto-sizing Undo Tablespace • Auto-tuning Undo Retention • Proactive Monitoring • Undo Advisor

  29. Auto-sizing Undo Tablespace • A self-learning system • Create Undo tablespace with autoextensible files • Run your workload • Done! (undo tablespace size adapts to the application)

  30. Auto-tuning Undo Retention Oracle 10g: Auto-tuning Before OLTP DSS Large RBS/ UR = 5 hrs Small RBS/ UR = 15 min

  31. Proactive Monitoring: Alerts • Out of Undo Space Alert • Long query warning Alert Out of space Critical Out of space Warning ORA-1555 Failure … 7:00am 7:10 7:20 7:30 7:40

  32. Undo Advisor Automatically analyzes the undo usage to advice optimal: • Undo tablespace size to support your longest running query • Undo tablespace size for a given undo retention period to support Undo based Flashback toolset

  33. Undo Home: Automatic Advice

  34. Undo Advisor: What if?

  35. Summary • Self-management engineered into the core of Oracle Database 10g • Self-management through: • Auto-sizing and size estimation • Built-in statistics gathering • Server-driven Alerts • Automatic Advisers • Auto-tuning to adapt to changing workload • Integration with the Enterprise Manager

  36. Next Steps…. • Recommended sessions • The Self-Managing Database : Automatic Health Monitoring (Thursday, Sept. 11, 11 AM) • Enterprise Manager : Scalable Oracle Management (Thursday, Sept. 11, 1 PM) • Recommended demos and/or hands-on labs • Oracle Database 10g : Manage the Oracle Environment Hands-On Lab • Campground Demo : “The Self-Managing Database: Space Management” • Relevant web sites to visit for more information • http://otn.oracle.com/products/manageability/database/content.html

  37. Reminder: Please complete the OracleWorld online session surveySession # 40170 Thank you.

  38. Q & Q U E S T I O N S A N S W E R S A

More Related