Gather optimizer statistics
This is pretty automatic.
Manage the Automatic Workload Repository
AWR is the name of the repository owner by SYSMAN and stored in SYSAUX tablespace. This repository contains e set of snapshots of statistics gathered by default every 60 minutes and retained for 7 days. Gather interval and retention are modifiable with pl/sql procedure MODIFY_SNAPSHOT_SETTINGS of the package DBMS_WORKLOAD_REPOSITORY. Obviously this can easily made with EM Console
Use the Automatic Database Diagnostic Monitor (ADDM)
This is an helper that analizes AWR and can give you suggestions. It populates same dictionary views:
There are some “ADVISORS” part of the ADDM:
- SQL Tuning Advisor: helps you to find critical or improvable statements
- SQL Access Advisor: gives you suggestion about possible indexes or materialized view that can improve performance of a statement or of a set of statements.
- Memory Advisor: controls SGA usage and performance
- MTTR Advisor: this checks checkpointing attivity, sintetically estimates MTTR. Check FAST_START_MTTR_TARGET parameter
- Segment Advisor
- Undo Advisor
Set warning and critical alert thresholds
You can define some baselines and on those baselines are based warning and critical thresholds.