Re: ORACLE Maintenance

In article <2fuq18$nh3_at_linus.mitre.org>, dagmar_at_indian.mitre.org (Dagmar A. Bogan) writes:
> I am working on a white paper identifying maintenance issues for an ORACLE
> RDBMS (versions 6 & 7) and I would be interested in hearing about your
> thoughts, ideas, and any problems that you may have encountered. Thanx in
> advance.
>
> ******************************************************************************
> The opinions expressed herein do not necessarily reflect those of The MITRE
> Corporation.
> ******************************************************************************
>
> ******************************************************************************
> Dagmar Anne Bogan *
> Software Engineering Center *
> The MITRE Corporation * phone: 703.883.6093
> M/S Z676 * fax: 703.883.6991
> 7525 Colshire Drive * email: dagmar_at_indian.mitre.org
> McLean, Virginia 22102-3481 *
> ******************************************************************************
>

In my opinion, the biggest issues with Oracle maintenance are:

1. Keeping database objects in single (or as few as possible) extents.
2. Monitoring database activity to locate inefficient SQL in applications.
3. Regular index rebuilds to ensure minimum 'height'.
4. Monitoring database activity to locate inefficient SQL in applications.
5. Regular ANALYZE runs to ensure 'current' statistics for the cost based optimiser.
6. Monitoring database activity to locate inefficient SQL in applications.
7. Reducing free-space fragmentation.
8. Monitoring database activity to locate inefficient SQL in applications.
9. Maintaining physical sort order of crucial tables. Optional, but we do it on a couple of big tables for extra speed.
10. Monitoring database activity to locate inefficient SQL in applications.
11. Balancing I/O across as many disk devices as possible within available resources.
12. Monitoring database activity to locate inefficient SQL in applications.
13. Tuning the O/S to work with the characteristics of Oracle processes, Oracle users, and Oracle databases.

Anyone care to expand on the list...

-- 
Bruce...        pihlab_at_cbr.hhcs.gov.au

"If you swallow a live frog first thing in the morning ...
 Nothing worse will happen to either of you for the rest of the day."

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of                                      * 
*          Health, Housing, Local Government & Community Services *
* Canberra, Australia                             (W) 06-289-7056 *
*******************************************************************
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************