Oracle Database 10g - Proactive Space & Schema Object Management 2003.pdf

(693 KB) Pobierz
Oracle7 Server
The Self-Managing Database:
Proactive Space & Schema
Object Management
An Oracle White Paper
Nov. 2003
The Self-Managing Database: Proactive Space &
Schema Object Management
The Self-Managing Database: Proactive Space & Schema Object Management Page 2
The Self-Managing Database: Proactive Space &
Schema Object Management
INTRODUCTION
Space management is one of the most time consuming tasks for database
administrators. According to a survey conducted by Oracle, DBAs currently
spend approximately 20% of their time in performing space management
operations . Fortunately, the Oracle Database10g automatically manages its
space consumption, alerts administrators on potential space problems, and
recommends possible solutions. This paper details the solutions that enable
administrators to maintain optimal space distribution and, address space related
problems in an easy and timely manner. The paper covers the various
components of Tablespace Management, Segment Management and Undo
Management that make the Oracle Database 10g a self-managing database.
PROACTIVE TABLESPACE MANAGEMENT
Overview
The Proactive Tablespace Management (PTM) capability in the Oracle Database
10g brings efficient and powerful space monitoring, notification and space
trending to the Oracle Database. Prior to Oracle Database 10g, the tools
available for monitoring and setting up notifications regularly polled the
database to monitor its space usage. Querying space usage information requires
collecting data about the state of the database – state that is constantly changing
in a production system. Because such queries are inherently expensive, the
space monitoring tools typically run them infrequently, once a day or once every
couple of hours. When they are run, the queries steal CPU, IO and memory
(especially the buffer cache) resources away from critical business activity in the
production system. It’s a health check that is either late or hurts the health of the
system or worse, both!
With PTM, Oracle Database 10g introduces a non-intrusive and timely health
check of space in the database server. And best of all, PTM is available by
default, causes no measurable performance impact, and is uniformly available
across all tablespace types. Also, the same functionality is available both
through Enterprise Manager (EM) as well as SQL. PTM is available for use with
locally managed tablespaces. The health check is performed incrementally as
The Self-Managing Database: Proactive Space & Schema Object Management Page 3
 
space is allocated and freed up in the database server. This guarantees
immediate availability of space usage information whenever the user needs it. In
addition to computing it on-the-fly, space usage information is assembled
(across nodes in RAC) and alerts are pushed proactively every 10 minutes.
Fig 1: Viewing and Setting Alert Thresholds Using Enterprise Manager
Tablespace Alerts
The Oracle Database 10g provides the complete lifecycle around Tablespace
Alerts right into the database kernel Alert conditions are automatically
computed and a notification is sent to the administrator if required. Once the
underlying problem is fixed, any outstanding alerts are automatically cleared
and moved to alert history.
Alerts are pushed into the server side table that holds alerts (available through
DBA_OUTSTANDING_ALERTS ) and are also made available through EM.
When an alert is cleared, it is archived in the alerts history table available
through DBA_ALERT_HISTORY.
Notification is performed using server generated alerts mechanism, which is yet
another new functionality in the Oracle Database 10g. The alerts are triggered
when certain space related events occur in the database. For example when the
space usage threshold of a tablespace is crossed, an alert is raised. Another
example of an alert is when a Resumable session encounters an out of space
situation. An alert is sent instantaneously to the DBA to take corrective
measures. The DBA may choose to get paged with the alert information and add
The Self-Managing Database: Proactive Space & Schema Object Management Page 4
 
space to the tablespace to allow the suspended operation to continue from where
it left off.
The database comes with a default set of alert thresholds. The DBA may
override the default for a given tablespace or set a new default for the entire
database through EM. The DBA may invoke the same functionality directly
using DBMS_SERVER_ALERT.SET_THRESHOLD procedure. Also, current
thresholds can be viewed using EM or through the
DBMS_SERVER_ALERT.GET_THRESHOLD procedure.
Fig 2: Tablespace threshold levels
Tablespace thresholds are defined in terms of the fullness of the tablespace as a
percentage. Critical and warning thresholds are two thresholds that apply to
tablespaces. The threshold values can be viewed and modified using Enterprise
Manager or the DBMS_SERVER_ALERTS package. When the tablespace
space utilization crosses either of these two limits, appropriate alerts are raised.
Conversely, when the space utilization falls below these thresholds, any
corresponding outstanding alert is cleared
The user can specify the critical or warning threshold values. If the user does not
specify a value, then a default of 85% and 97% are chosen for warning and
threshold values respectively. For databases migrating from previous releases,
the threshold values will be NULL – this indicates that tablespace threshold
alerts will not be raised until the DBA explicitly enables them. The database
wide default can be reset or modified when desired.
The Self-Managing Database: Proactive Space & Schema Object Management Page 5

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin