Chapter 13 Database Maintenance

Use and Manage Optimizer Statistics (in data dictionary about database objects)
Optimizer use statistic to choose the execution plan for best performance.
Analyze Table: analyze table table_name compute statistics;
object statistic: DBA_TABLES view
column statistic: DBA_TAB_COLUMNS view
index statistic: DBA_INDEXES view,
Analyze Index: analyze index index_name compute statistics;
index statistic: INDEX_STATS view

Recommended:
EXECUTE DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name');

DBMS_STATS.GATHER_SCHEMA_STATS(
OWNNMAE,
CASCADE, DBMS_STATS.AUTO_CASCADE including indexes as well
ESTIMATE_PERCENT, DBMS_STATS.AUTO_SAMPLE_SIZE
DEGREE, DBMS_STATS.AUTO_DEGREE
NO_INVALIDATE, DBMS_STATS.AUTO_INVALIDATE
GRANULARITY, 'AUTO'
METHOD_OPT, 'FOR ALL COLUMNS SIZE AUTO'
OPTIONS 'GATHER STALE'
)

Object statistics are not real time; they are static until refreshed by a new analysis.

Maintenance window:
4 hours every weekday night at 2200
20 hours weekend morning at 0600

STATISTICS_LEVEL=BASICTYPICAL (default)ALL

BASIC: all statistics off

TYPICAL: (automatic object analysis daily in the maintenance window)
instance statistics goes to AWR by MMON
object statistics goes to data dictionary by DBMS_STATS

ALL: (include TYPICAL plus the following statistics, impact performance)
operating system statistics
SQL execution statistics


Use and Manage the Automatic Workload Repository
AWR exists as a set of tables and other objects owned by SYSMAN in the SYSAUX tablespace.
AWR snapshot once an hour. Flushing the statistics in SGA to the AWR by MMON.
AWR information includes dynamic perofrmance views V$ (instance) and DBA views from data dictionary (history of object statistics) (database).
AWR data kept 8 days by default.

Creating baselines must be done by DBA.
A baseline is a pair of snapshots that will be kept/stored indefinitely until deliberately dropped.
A baseline is a stored set of metrics that can be used for comparisons across time.
The metrics derived from the baseline can then be compared with the metrics derived from current activity levels.

EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(-
>Start_Snap_id=>m,End_Snap_id=>n, Baseline_Name=>'baseline_name');

EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(-
>Retention=>43200, Interval=>30);
Default Retention is 8 days, Interval is 1 hour. Now Retention is 30 days, Interval is 30 minutes.


Use the Advisory Framework
ADDM report generated automatically whenever an AWR snapshot is taken.
ADDM report highlight problems and suggest/recommend solutions and advisors.
ADDM report is purged by default after 30 days.
ADDM report cover the period between current snapshot and the previous one by default.
ADDM is the starting point for performance analysis and problem resolution.

The Memory Advisor
Shared Pool Advisor
Java Pool Advisor
Streams Pool Advisor
Database Buffer Cache Advisor
PGA Advisor
SGA Advisor
MEMORY_TARGET: overall memory advisor

The SQL Advisor
SQL Access Advisor
SQL Tuning Advisor
SQL Repair Advisor

The Automatic Undo Advisor: to recommend undo tablespace size based on the undo data generation and max query length.

The Mean Time to Recover (MTTR) Advisor: to estimate how long the period of downtime will be based on the current workload.

The Data Recovery Advisor

The Segment Advisor: to recommend segment reorganization.

Scheduler, AutoTasks:
Linked with Resource Manger, not more than 25% resource can be used for the following autotasks:
Gathering optimizer statistics
Running the Segment Advisor
Running the SQL Tuning Advisor


Manage Alerts and Tresholds
Alerts are raised by the MMON process.
Stateful alerts are based on conditons that persist and can be fixed.
Stateless alerts are based on events; they happen and are gone.

To configure the Alert system, you set thresholds for metrics. V$METRICNAME
The thresholds are stored in the AWR.

Alert queue is a table of message that other processes can read.

View alerts in DBA_OUTSTANDING_ALERTS.
Once cleared, they will be in DBA_ALERT_HISTORY.
Stateless alerts go straight to the history view.

DBMS_SERVER_ALERT.SET_THRESHOLD(-
METRICS_ID
WARNING_OPERATOR,
WARNING_VALUE,
CRITICAL_OPERATOR,
CRITICAL_VALUE, etc.

The default notification mechanism for stateful alerts is nothing more than displaying them in the Enterprise Manager on the database home page, and writing them to the DBA_OUTSTANDING_ALERTS view.

Other notification method:
SMTP Email
OS Command
PL/SQL
SNMP

1. Configure notification method
2. Create rule to catch the event, set the metric to monitor
3. Subscribe to the rule