slide1 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
The Self-managing Database: Proactive Space and Schema Object Management PowerPoint Presentation
Download Presentation
The Self-managing Database: Proactive Space and Schema Object Management

Loading in 2 Seconds...

play fullscreen
1 / 40

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


  • 185 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'The Self-managing Database: Proactive Space and Schema Object Management' - riva


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
the self managing database proactive space and schema object management

Session id: 40170

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

Amit GaneshDirector,

Data, Space and Transaction Processing

Oracle Corporation

agenda
Agenda
  • Proactive Space Management
    • Alerts
    • Size estimation and growth trending
    • Segment Advisor and Shrink
  • Proactive Undo Management
    • Alerts
    • Auto-sizing
    • Auto-tuning
automatic space management
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

oracle database 10 g self managing database
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

10 g always working for you
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
proactive space management in 10 g
Proactive Space Management in 10g

$$

Operations

Size Estimation

Growth Trending

Planning

Segment

Advisor

Actions

Tablespace

Alerts

Shrink

Add file

Re-org/ rebuild

Resumable

tablespace alerts
Tablespace Alerts
  • Database Generated
  • Warning, Critical
  • Out of the box
  • Tablespaces can be provisioned with more disk space before out-of-space conditions occur
tablespace thresholds
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

alert computation

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.
threshold alert tracking

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
setting thresholds using em
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
growth trend report
Growth Trend Report
  • Based on Automatic Workload Repository Data
  • Indicates past growth trend and predicts future growth pattern
segment advisor
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
segment shrink

Reclaimed Space

Segment Shrink

UnusedSpace

UnusedSpace

Data

HWM

Shrink

Operation

Online and In-place

ShrinkOperation

HWM

benefits
Benefits

Online & In-place

Faster access

ShrunkSegment

Better space utilization

candidate segments
Candidate Segments

Auto Segment Space ManagedTablespace

LOBs

Tables

Row MovementEnabled

IOTs

MaterializedViews

Indexes

dependency maintenance

DBA

Dependency Maintenance

Indexes are maintained

Index

Shrink

Table

Shrink

Trigger

Triggers are not fired

how can i shrink segments

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

how does it work
How Does it Work?

ALTER TABLE employees SHRINK SPACE COMPACT;

1

HWM

HWM

ALTER TABLE employees SHRINK SPACE;

2

HWM

space management before and now
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

oracle database 10 g
Oracle Database 10g
  • Auto-sizing Undo Tablespace
  • Auto-tuning Undo Retention
  • Proactive Monitoring
  • Undo Advisor
auto sizing undo tablespace
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)

auto tuning undo retention
Auto-tuning Undo Retention

Oracle 10g: Auto-tuning

Before

OLTP

DSS

Large RBS/

UR = 5 hrs

Small RBS/

UR = 15 min

proactive monitoring alerts
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

undo advisor
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
summary
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
next steps
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
slide39

Q

&

Q U E S T I O N S

A N S W E R S

A