Download
1 / 82

Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor - PowerPoint PPT Presentation


  • 125 Views
  • Uploaded on

Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor JF Verrier Pr. Curriculum Developer Oracle Corporation. Oracle Database 10 g : Managing the Self-Managing Database. Objectives.

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 'Steven George Sr. Delivery Manager Mark Fuller Sr. Pr. Instructor Rick Pandya Pr. Instructor' - december


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

Steven GeorgeSr. Delivery Manager

Mark FullerSr. Pr. Instructor

Rick PandyaPr. Instructor

JF VerrierPr. Curriculum Developer

Oracle Corporation


Oracle Database 10g:

Managing the Self-Managing Database


Objectives
Objectives

  • Understanding the self-management capabilities of Oracle Database 10g

  • Customizing the self-management capabilities of Oracle Database 10g


Manageability goal
Manageability Goal

Reduce Administration Cost

  • Automatic versus Manual

  • Intelligence versus Data

Reduce Capital Expenditure

  • Adaptive versus Oversized

  • Integrated versus Third Party

  • ½ Cost

Reduce Failure Cost

  • Preventive versus Corrective

  • Act and Succeed versus Trial and Error


How dbas spend their time
How DBAs Spend Their Time

Install 6%

Create and Configure 12%

Load Data 6%

Manage DatabaseSystem 55%

MaintainSoftware 6%


Database management challenges

SpaceManagement

Backup andRecoveryManagement

StorageManagement

Applicationand SQLManagement

SystemResourceManagement

?

?

?

?

?

Database Management Challenges


Solution self managing database

SpaceManagement

Backup andRecoveryManagement

StorageManagement

Applicationand SQLManagement

SystemResourceManagement

Solution: Self-Managing Database

Enterprise ManagerDatabaseConsole

Monitor

Alert

AutomaticManagement

Fix

Advise

CommonManageabilityInfrastructure


Common manageability infrastructure automatic workload repository
Common Manageability Infrastructure: Automatic Workload Repository

AutomatedTasks

Automatic

Proactive

ServerAlerts

AdvisoryFramework

AutomaticWorkload Repository

Efficient

Data Warehouseof the Database

Direct memoryaccess

Automatic collection of important statistics


Automatic workload repository
Automatic Workload Repository Repository

ADDM finds top problems

MMON

SYSAUX

WR Schema

BG

In-memorystatistics

7:00 a.m.

Snapshot 1

BG

7:30 a.m.

Sevendays

Snapshot 2

AWRStatistics

8:00 a.m.

ASH

8:30 a.m.

FG

Snapshot 3

Snapshot 4

FG

SGA

8:30am

DBA


Statistics level
Statistics Level Repository

STATISTICS_LEVEL

BASIC

TYPICAL

ALL

Turn offall self-tuning capabilities

Recommendeddefault value

Additional statistics for manual SQL diagnostics




Database feature usage metric collection
Database Feature Usage Metric Collection Repository

OracleDatabase 10g

Once a week

DB Feature UsageStatistics

DB High-Water MarkStatistics

MMON

size of largest segment,maximum number of sessions,maximum number of tables,maximum size of the database,

maximum number of data files,…

Advanced Replication, Oracle Streams, AQ,Virtual Private Database,Audit options, …

AWR

DBA_FEATURE_USAGE_STATISTICS

DBA_HIGH_WATER_MARK_STATISTICS

EM Console

HOST CONFIGURATION INFO

EM Repository: ECM


Common manageability infrastructure server alerts
Common Manageability Infrastructure: RepositoryServer Alerts

AutomatedTasks

Automatic

Proactive

ServerAlerts

AdvisoryFramework

AutomaticWorkload Repository

Efficient

Push model

Enabled by default

Timely generation


Server alerts

Oracle Server Repository(SGA)

Server Alerts

Enterprise

Manager

GuidedResolution

AutomaticNotification

ServerAlertsQueue

Server monitors itself

AWR

MMON


Server alerts delivery process

Advanced RepositoryQueue

Server Alerts Delivery Process

SubscribingClients

ConsoleUpdate

ThirdParty

Queue

BG

Push

FG

EM(EMD)

Page ore-mailDBAs


Server generated alert types
Server-Generated Alert Types Repository

Metric-Based

Alert

97% Critical

Cleared

ThresholdAlerts

Alert

85% Warning

Cleared

MMON

ResumableSessionSuspended

Recovery Area Low On Free Space

SnapshotToo Old

Non-ThresholdAlerts

Alert

Alert

Alert

Event-Based


Out of the box alerts
Out-of-the-box Alerts Repository

97% Critical

85% Warning

LocallyManaged

Tablespace

TablespaceSpace Usage

ResumableSessionSuspended

Recovery Area Low On Free Space

SnapshotToo Old




Common manageability infrastructure automated tasks
Common Manageability Infrastructure: RepositoryAutomated Tasks

AutomatedTasks

Automatic

Proactive

ServerAlerts

AdvisoryFramework

AutomaticWorkload Repository

Efficient

Statistics collection task scheduledout-of-the-box

Pre-packaged routinemaintenance tasks

Resource usage controlled


Automatic optimizer statistics collection
Automatic Optimizer Statistics Collection Repository

DBA tracks and gathers statistics

Targetsright objects

Resolvestwo issues

Determinesright samples

Automatic statistics collection


Gather statistics job
Gather Statistics Job Repository

MAINTENANCE_WINDOW_GROUP

WEEKNIGHT_WINDOW

WEEKEND_WINDOW

10pm–6am Mon to Fri

12am Sat to 12am Mon

gather_stats_job

AUTO_TASKS_JOB_CLASS

AUTO_TASKS_CONSUMER_GROUP



D E M O N S T R A T I O N Repository

Creating Automated Tasks


Common manageability infrastructure advisory framework
Common Manageability Infrastructure: RepositoryAdvisory Framework

AutomatedTasks

Automatic

Proactive

ServerAlerts

AdvisoryFramework

AutomaticWorkload Repository

Efficient

Uniform interface

Fully integrated


Advisory framework
Advisory Framework Repository

SQL Tuning

PGA

Buffer Cache

Memory

SGA

ADDM

Access

Shared Pool

Space

Segment Advisor

Undo

AWR

Commondata source

Seamlessintegration


Guided tuning session
Guided Tuning Session Repository

Create an advisor task

Adjust task parameters

EnterpriseManager DatabaseConsole

Perform analysis

No

Yes

Acceptresults?

Implementrecommendations


Advisory central
Advisory Central Repository


Solution self managing database1

Space RepositoryManagement

Backup andRecoveryManagement

StorageManagement

Applicationand SQLManagement

SystemResourceManagement

Solution: Self-Managing Database

Enterprise ManagerDatabaseConsole

Monitor

Alert

AutomaticManagement

Fix

Advise

CommonInfrastructure


Performance monitoring solutions
Performance Monitoring Solutions Repository

SGA

In-memorystatistics

MMON

Snapshots

Alerts

ADDM

ADDMResults

Proactive Monitoring within

Oracle Server

AWR

DBA

ReactiveMonitoring


Addm performance monitoring
ADDM Performance Monitoring Repository

SGA

30 minutes

In-memorystatistics

MMON

Snapshots

ADDM

ADDMResults

EM

AWR

ADDMResults


Addm problem classification system
ADDM Problem Classification System Repository

Buffer Busy

RAC Waits

System Wait

Parse Latches

Concurrency

Buf Cache latches

IO Waits

Non-problem areas

Symptoms

Root Causes




Performance monitoring solutions1
Performance Monitoring Solutions Repository

SGA

In-memorystatistics

MMON

Snapshots

Alerts

ADDM

ADDMResults

Proactive Monitoring within

Oracle Server

AWR

DBA

ReactiveMonitoring


Performance management approach

Host Repository

CPU Bottlenecks

Memory Bottlenecks

Oracle

CPU/Waits

Uses ASH and AWR

SQL

Sessions

Performance Management Approach


Em product layout
EM Product Layout Repository

Enterprise-wide Console Page

Target Databases Page

Database Home Page

Database Performance Page

Drilldowns

SQL

Session




Solution self managing database2

Space RepositoryManagement

Backup andRecoveryManagement

StorageManagement

SystemResourceManagement

Solution: Self-Managing Database

Applicationand SQLManagement

Enterprise ManagerDatabaseConsole

Monitor

Alert

AutomaticManagement

Fix

Advise

CommonInfrastructure


Automate the sql tuning process

I can do Repositoryit for you!

ADDM

Automate the SQL Tuning Process

SQL Workload

DBA

High-loadSQL

SQL Tuning Advisor


Sql tuning advisor overview

Statistics Check Repository

Optimization Mode

Plan Tuning

Optimization Mode

Access Analysis

Optimization Mode

SQL Tuning Advisor

SQL Analysis

Optimization Mode

SQL Tuning Advisor Overview

AutomaticTuning Optimizer

ComprehensiveSQL Tuning

DetectStale or MissingStatistics

Plan Tuning(SQL Profile)

Add MissingIndex RunAccess Advisor

RestructureSQL


Plan tuning flow

Optimizer Repository

(Tuning Mode)

SQL TuningAdvisor

Optimizer

(Normal Mode)

DatabaseUsers

Plan Tuning Flow

create

submit

SQL Profile

use

No application codechange

output

Well-TunedPlan


Sql tuning advisor usage model
SQL Tuning Advisor Usage Model Repository

Automatic Selection

AWR

ADDM

High-load SQL

SQLTuning Advisor

Sources

ManualSelection

AWR

STS

Cursor Cache

Filter/Rank

DBA

Custom


D E M O N S T R A T I O N Repository

Resolving Performance Problems


Solution self managing database3

Space RepositoryManagement

Backup andRecoveryManagement

StorageManagement

Solution: Self-Managing Database

Applicationand SQLManagement

Enterprise ManagerDatabaseConsole

SystemResourceManagement

Monitor

Alert

AutomaticManagement

Fix

Advise

CommonInfrastructure


Overview of automatic shared memory management
Overview of Automatic Shared Memory Management Repository

Shared Pool

Database

Buffer Cache

Redo Log

Buffer

Java Pool

Large Pool

Fixed SGA

SGA

MMAN

Automatically set the optimal size


Benefits of automatic shared memory management
Benefits of Automatic Shared Memory Management Repository

DB_CACHE_SIZE

SHARED_POOL_SIZE

LARGE_POOL_SIZE

JAVA_POOL_SIZE

Total SGA size

SGA_TARGET


Using em to configure automatic shared memory management
Using EM to Configure RepositoryAutomatic Shared Memory Management


D E M O N S T R A T I O N Repository

Automating Memory Management


Solution self managing database4

Backup and RepositoryRecoveryManagement

StorageManagement

SystemResourceManagement

SpaceManagement

Solution: Self-Managing Database

Applicationand SQLManagement

Enterprise ManagerDatabaseConsole

Monitor

Alert

AutomaticManagement

Fix

Advise

CommonInfrastructure


Segment shrink overview
Segment Shrink Overview Repository

Data

UnusedSpace

UnusedSpace

Data

HWM

ShrinkOperation

Online and in-place

Reclaimed Space

Data

HWM


How can i shrink segments
How Can I Shrink Segments? Repository

ALTER … SHRINK SPACE [CASCADE]

TABLE

INDEX

MATERIALIZED VIEW

MATERIALIZED VIEW LOG

MODIFY PARTITION

MODIFY SUBPARTITION

MODIFY LOB

ALTER TABLE employees ENABLE ROW MOVEMENT;

1

ALTER TABLE employees SHRINK SPACE CASCADE;

2


Segment advisor
Segment Advisor Repository



D E M O N S T R A T I O N Repository

Proactively Managing Space



Automatic undo retention tuning
Automatic Undo Retention Tuning Repository

  • Proactive tuning

    • Undo retention is tuned for longest-running query.

    • Query duration information is collected every 30 seconds.

  • Reactive tuning

    • Undo retention is gradually lowered under space pressure.

    • Oldest unexpired extents are used first.

  • Enabled by default


D E M O N S T R A T I O N Repository

Using Automatic Undo Retention Tuning


Undo advisor
Undo Advisor Repository



Solution self managing database5

Space RepositoryManagement

StorageManagement

SystemResourceManagement

Backup andRecoveryManagement

Solution: Self-Managing Database

Applicationand SQLManagement

Enterprise ManagerDatabaseConsole

Monitor

Alert

AutomaticManagement

Fix

Advise

CommonInfrastructure



Optimized incremental backup
Optimized Incremental Backup Repository

  • Optimizes incremental backups

    • Tracks which blocks have changed since last backup

  • Oracle Database 10g has integrated change tracking.

    • New Change Tracking File is introduced.

    • Changed blocks are tracked as redo is generated.

    • Database backup automatically uses changed block list.

List of Changed Blocks

1011001010110

0001110100101

1010101110011

Change

TrackingFile

RedoGeneration

SGA

Redo Log



Flash recovery area space management
Flash Recovery Area Space Management Repository

Disk limit is reached and a new file needs to be written into the Recovery Area.

Oracle deletes files that are nolonger requiredon disk.

Recovery Area

Database filebackup

1

2

Space pressure occurs.

3

4

Warning is issued to user.

RMAN updates

list of files that

may be deleted.

1

2

Backup filesto be deleted


Suggested strategy
Suggested Strategy Repository


Flashback error correction
Flashback Error Correction Repository

Database

Customer

  • Flashback Database

    • Restore database to time

    • Uses flashback logs

  • Flashback Drop

    • Restore dropped table

    • Uses recycle bin

  • Flashback Table

    • Restore all rows in table to time

    • Uses UNDO in database

  • Flashback Transaction Query

    • Query a committed Txn

  • Flashback Versions Query

    • Query changes to rows over time

Order

Order Line


Solution self managing database6

Space RepositoryManagement

Backup andRecoveryManagement

SystemResourceManagement

StorageManagement

Solution: Self-Managing Database

Applicationand SQLManagement

Enterprise ManagerDatabaseConsole

Monitor

Alert

AutomaticManagement

Fix

Advise

CommonInfrastructure


What is automatic storage management
What Is Automatic Storage Management Repository

Database

Tablespace

Segment

ASM solvesmanagement problemsof Oracle databases.

Extent

ASM managesOracle files.

ASM does notreplace existingconcepts.


Hierarchy
Hierarchy Repository

ASMdisk group

Database

ASM file

Data file

Tablespace

Segment

ASM disk

Or

Extent

File system

file orraw device

Allocation unit

Oracleblock

Physicalblock


Asm benefits

Improves Repositoryperformance,scalability,and reliability

ASM Benefits

Reduces the costof managing storage

Reducesadministrationcomplexity

Supports RAC


Asm files
ASM Files Repository

CREATE TABLESPACE sample DATAFILE '+dgroupA';

Database file

Automatic ASM filecreation

1

2

3

4

3

4

1

2

ASM file automatically spread inside disk group dgroupA


Sql statements issued to asm instances
SQL Statements Issued to ASM Instances Repository

ALTER SYSTEMRESTRICTED SESSION

CREATE DISKGROUP

ASM Instance

ALTER DISKGROUP

DROP DISKGROUP



D E M O N S T R A T I O N Repository

Using Automatic Storage Management


How oracle database 10 g dbas spend their time
How Oracle Database 10 Repositoryg DBAs Spend Their Time

Proactive andStrategicPlanning 50+%

Create and Configure 6%

Install 3%

MaintainSoftware 3%

Manage DatabaseSystem 25%

Load Data 3%


Summary
Summary Repository

  • Oracle Database 10g’s self-management capabilities work out-of-the-box.

  • Customization of Oracle Database 10g’s self-management capabilities can be done through Enterprise Manager.

  • Oracle Database 10g is a self-managing database which reduces administration overhead and enables DBAs to become proactive strategists.


Q Repository

&

Q U E S T I O N S

A N S W E R S

A


Reminder – Repositoryplease complete the OracleWorld online session surveyThank you.


ad